Error 1166 - database backup/restore problems...

[Toibs]

Guys,

Just after a little assistance if i may...

I'm trying to move a site over from one server to another, following the age old process of copying all the files over, backing up the database, and then restoring it using "mysql -p -h localhost newdb < backupdb.sql" (newdb is in fact a completely new database, completely blank...)

All sites have worked ok up until now

However there had to be one.....

Getting the following :
ERROR 1166 (42000) at line 122755: Incorrect column name 'votetotal '

Have done a database repair/optimise successfully, however i dont see this listed in the list of tables on either box.however everything else is there...

Typically enough i thought "well i'll let it carry on restoring as it seems this table isnt used", but it stops there and doesnt finish the other tables.

Anyone any advice on how i can fix this problem at all please?? i still have full access to both machines...

Thx
Paul

[Steve Machol]

Don't use the -h option. That is only needed when accessing dbs on a remote server, and even then it often fails.

What are the last 5 lines of your backup file?

[Toibs]

Hi Steve,

There's 5 blank lines, then :

### VBULLETIN DATABASE DUMP COMPLETED ###

[Steve Machol]

The backup should be complete then. Have you tried restoring without the -h option?

[Toibs]

Nope - sorry Steve - Still the same problem...

ERROR 1166 (42000) at line 123484: Incorrect column name 'votetotal '

:(

Any more ideas?? ;)

Paul

[Steve Machol]

What is the complete error and what are you doing when you get it?

[Toibs]

Ok. I'm trying to do a backup of the database, and restore on a new server. All the other sites on the box have migrated successfully - it's just this one causing the problem.

I can export the SQL no problems, however it's when i try to import the sql into the new database is when the problem occurs.

it gets about 1/2 way through and comes up with

Getting the following :
ERROR 1166 (42000) at line 122755: Incorrect column name 'votetotal '

and the restore finishes at that point... obviously with only half a database as it hasnt completed...

Paul

[Marco van Herwaarden]

What versions of MySQL are you using on the old and the new server?

How did you make the backup?
How are you trying to restore?

PS It looks like you have a columnname with a space in the end. You could try renaming that column on the source database to remove that space.

[Toibs]

Old server :

Server TypeLinux
Web ServerApache v1.3.33
PHP4.3.10
PHP Max Post Size8.00 MB
PHP Maximum Upload Size2.00 MB
MySQL Version4.0.16-standard
MySQL Packet Size1.00 MB

New server :
Server TypeLinux
Data Usage93.81 MB
Web ServerApache v2.0.52
Index Usage37.60 MB
PHP4.3.9
PHP Max Post Size8.00 MB
PHP Maximum Upload Size2.00 MB
PHP Memory Limit8.00 MB
MySQL Version4.1.12
MySQL Packet Size1.00 MB

I have noted the space at the end. I removed it in the backup, however it then tells me there's already a field of the same name....

As per the first post :

I'm backing up using the backup function in Vb (All fields). It's only around 43 Meg in size and it's on a server local to me, so doesnt time out. To restore: mysql -p -h localhost newdb < backupdb.sql" (newdb is in fact a completely new database, completely blank...)


Paul

[Scott MacVicar]

Can you check the column definition within the file, if its large I sugges you use nano and scroll to the correct page using page down. Probably on line 122755 ish.

According to google it could be an error in the export option on 4.0.16 so just paste the table definition.

[Toibs]

here's the group in fact....

DROP TABLE IF EXISTS games;
CREATE TABLE `games` (
`shortname` varchar(100) NOT NULL default '',
`gameid` int(11) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`descr` text NOT NULL,
`file` varchar(100) NOT NULL default '',
`width` smallint(4) NOT NULL default '550',
`height` smallint(4) NOT NULL default '400',
`miniimage` varchar(100) NOT NULL default '',
`stdimage` varchar(100) NOT NULL default '',
`gamesettings` int(10) NOT NULL default '0',
`highscorerid` int(10) unsigned default NULL,
`highscore` int(50) unsigned default NULL,
`votetotal ` smallint(5) unsigned NOT NULL default '0',
`votenum ` smallint(5) unsigned NOT NULL default '0',
`votetotal` smallint(5) unsigned NOT NULL default '0',
`votenum` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`gameid`)
) TYPE=MyISAM;

Paul

[Scott MacVicar]

As I suspected you see you have two set sof votenum and votetotal, one with spaces and one without.

What do the INSERT statements reference after it?

[Toibs]

Think i can see where you're going with this.... Will have a play tomorrow and keep you posted :)

Paul

[Scott MacVicar]

It goes along the lines of removing the one that isn't used by the insert statements ;)

[Toibs]

It goes along the lines of removing the one that isn't used by the insert statements ;)

Funilly from what i can see, neither are!!

[Scott MacVicar]

how odd, can you post a sample insert statement so I can see :)

[Marco van Herwaarden]

In addition to what Scott asks, if you still have access to the old database, could you list the table definition from there (not from the backup file!).

[Scott MacVicar]

That was my next question, work out if its a bug in the MySQL Backup feature or if its just plain wierdness with that one table.

[Marco van Herwaarden]

Sorry that i've stolen your next question Scott. :D