Moving to new host - mysql database problem [Installation Troubleshooting]

1

Moving to new host - mysql database problem

2004/12/14 12:17
rudycash
Just popping in
Posts: 70
Since: 2004/2/12

Help- I'm in the process of moving my sites to a new host. I'm trying to export my old database and import it on the new server. I've done this before and restored XOOPS sites from back up etc.

However when I try and import the database I get this message

Quote:

SQL-query:

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
--http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 14, 2004 at 12:26 PM
-- Server version: 4.0.22
-- PHP Version: 4.3.9
--
-- Database: `docuwedd_docuwedd`
--
-- --------------------------------------------------------
--
-- Table structure for table `xoops_avatar`
--
-- Creation: Feb 01, 2004 at 05:59 PM
-- Last update: Dec 14, 2004 at 11:28 AM
-- Last check: Dec 14, 2004 at 11:28 AM
--
DROP TABLE IF EXISTS `xoops_avatar` ;

CREATE TABLE IF NOT EXISTS `xoops_avatar` (
`avatar_id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`avatar_file` varchar( 30 ) NOT NULL default '',
`avatar_name` varchar( 100 ) NOT NULL default '',
`avatar_mimetype` varchar( 30 ) NOT NULL default '',
`avatar_created` int( 10 ) NOT NULL default '0',
`avatar_display` tinyint( 1 ) unsigned NOT NULL default '0',
`avatar_weight` smallint( 5 ) unsigned NOT NULL default '0',
`avatar_type` char( 1 ) NOT NULL default '',
PRIMARY KEY ( `avatar_id` ) ,
KEY `avatar_type` ( `avatar_type` , `avatar_display` )
) TYPE = MYISAM AUTO_INCREMENT =1

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'avatar_id` mediumint(8) unsigned NOT NULL auto_increment,
`av

Any ideas - am I exporting incorrectly or importing incorrectly???

2

Re: Moving to new host - mysql database problem

2004/12/14 13:39
Anonymous
Posts: 0
Since:

I have a MySQL problem too. I have been using three XOOPS pages on the same server ... now I use only one ... Can I delete the entries in MySQL that I don't use anymore ?

3

Re: Moving to new host - mysql database problem

2004/12/14 13:53
JMorris
XOOPS is my life!
Posts: 2722
Since: 2004/4/11

@rudycash,

What version of MySQL does the new server run? There are some incompatabilities between 4.0.x and 4.1.7. I don't know much about MySQL, but I've had similar problems before. Quite honestly, I had to tinker until I got it. Sure wish I remembered how I got it to work.

@Bahattee,

If you are referring to having three seperate installs of XOOPS on one DB with three seperate table prefixes (i.e., xoops_avatars), the answer is yes, you can dump the two unused sets. My hosting package only has one DB for my account. I've run up to 10 seperate installs of XOOPS on my server with no problem and dumped 9 table sets with no problem.

Hope this helps.

4

Re: Moving to new host - mysql database problem

2004/12/14 14:01
Anonymous
Posts: 0
Since:

Amm ... I had a phpBB installed on this server too.

In phpMyAdmin I can see on the left something like this:

dev_avatar
dev_avatar_user_link
...
phpbb_auth_access
phpbb_....
...
and so on for rapscena, vboth and xoops.

How can I know what is named the database that I am using now ... I know that phpbb and dev are not ...

And how can I delete old entries ?

5

Re: Moving to new host - mysql database problem

2004/12/14 14:15
JMorris
XOOPS is my life!
Posts: 2722
Since: 2004/4/11

Quote:

How can I know what is named the database that I am using now ... I know that phpbb and dev are not ...

For the site you are using now, did you change the DB prefix during installation? If not, then you can dump everything that doesn't have the xoops_ prefix (assuming you're only wanting to keep your XOOPS install). Since I don't know what your convention is for naming your db prefixes, I can't really say which you should keep. As a rule, I keep a flat text file on my computer that details such things to help me keep track of all my installs.

Important: Before you go dumping tables, do yourself a favor and backup your DB. Just in case you accidentally delete a needed table

As far as how to dump the tables you don't want, that's easy....

Go to phpMyAdmin
Select the DB name in the left frame (assuming all tables are in the same DB)
Select the checkbox next to every table you wish to delete
Scroll to the bottom of the page and in the checkbox that says "With selected:", select "Drop"
phpMyAdmin will refresh to a confirmation page, just click yes
You're done

6

Re: Moving to new host - mysql database problem

2004/12/14 14:23
RVirtue
Quite a regular
Posts: 246
Since: 2004/8/4 9

rudycash:

Is it possible that the export/import involves two different versions of phpMyAdmin. If so, I've noticed that the comment character change from single octothorp (#) to double dash (--) somtimes seems to confuse older versions. Getting rid of the intial comments in the .SQL file (or changing them back to the older comment character) prior to importing might be worth a try.

7

Re: Moving to new host - mysql database problem

2004/12/15 15:05
rudycash
Just popping in
Posts: 70
Since: 2004/2/12

Thanks - How would I do that?

Quote:

Getting rid of the intial comments in the .SQL file (or changing them back to the older comment character) prior to importing might be worth a try.

8

Re: Moving to new host - mysql database problem

2004/12/15 15:48
RVirtue
Quite a regular
Posts: 246
Since: 2004/8/4 9

In the .SQL dump file that you posted in your opening message, the following lines were created by the phpMyAdmin export process as informational comments:


-- phpMyAdmin SQL Dump

-- version 2.6.0-pl3

-- http://www.phpmyadmin.net

-- 

-- Host: localhost

-- Generation Time: Dec 14, 2004 at 12:26 PM

-- Server version: 4.0.22

-- PHP Version: 4.3.9

-- 

-- Database: `docuwedd_docuwedd`

-- 

-- --------------------------------------------------------

-- 

-- Table structure for table `xoops_avatar`

-- 

-- Creation: Feb 01, 2004 at 05:59 PM

-- Last update: Dec 14, 2004 at 11:28 AM

-- Last check: Dec 14, 2004 at 11:28 AM

--

Those comments have no operational purpose and can be deleted or, using any plain text editor, you can replace the comment characters (change -- to #) at the beginning of each line.

I've seen the XOOPS module installer "choke" if the newer phpMyAdmin v2.6.x comments are included in the .SQL file that it uses. (It appears to expect the older # comment character.) But this is just a suggestion for you to try. I don't know enough details about the exact process you're following to offer any "guaranteed" solution.

Stats
Goal:	$100.00
Due Date:	May 31
Gross Amount:	$0.00
Net Balance:	$0.00
Left to go:	$100.00

Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Re: Moving to new host - mysql database problem

Login

Search

Recent Posts

Re: Rebuilding and Updating a 20 years old Xoops Website from 2.0.18? to 2.5.11

Rebuilding and Updating a 20 years old Xoops Website from 2.0.18? to 2.5.11

Re: NewBB v 5.1.0 b 6

Re: NewBB v 5.1.0 b 6

NewBB v 5.1.0 b 6

Re: XOOPS 2.5.11 search user is not working

Re: oledrion

Re: oledrion

Re: oledrion

Re: oledrion

Who's Online

Donat-O-Meter

Latest GitHub Commits

{{ record.sha.slice(0, 7) }} - {{ record.commit.message | truncate }}