Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
MySQL importing error
Hi, I am exporting TXP and other site data from an older server running MySQL 4.0.26 to TXD, which runs MySQL 4.1.18.
I get this error on importing:
CREATE TABLE txp_priv(
id int( 8 ) NOT NULL AUTO_INCREMENT ,
priv varchar( 128 ) NOT NULL default ‘’,
1tinyint( 1 ) NOT NULL default ‘0’,
2tinyint( 1 ) NOT NULL default ‘0’,
3tinyint( 1 ) NOT NULL default ‘0’,
4tinyint( 1 ) NOT NULL default ‘0’,
5tinyint( 1 ) NOT NULL default ‘0’,
6tinyint( 1 ) NOT NULL default ‘0’,
PRIMARY KEY ( id )
) TYPE = MYISAM ;
- - 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 ’1 tinyint(1) NOT NULL default ‘0’, 2 tinyint(1) NOT NULL default ‘0’, 3 tiny’ at line 4
Perhaps someone can help.
Offline
#2 2006-03-25 23:38:42
- zem
- Developer Emeritus
- From: Melbourne, Australia
- Registered: 2004-04-08
- Posts: 2,579
Re: MySQL importing error
You need to use mysqldump’s -Q option when backing up.
Alex
Offline
Re: MySQL importing error
In case Zems solution did not work.
I had the same error getting my 1and1 database dump to import at Textdrive. The use slanty quotes option that Zem mentions did not help.
- updating my old phpmyadmin install at 1and1 hosting, and re-exporting, then…
- SSHing and importing the database from the command line as the database was running into the phpmyadmin database size limit.
Of course that was with a huge drupal [shudders] database.
Hope this helps
Offline
Re: MySQL importing error
Hi, the database is getting backed up fine on TXD servers using the script on the TXD knowledge base:
/usr/local/mysql/bin/mysqldump -h$MYSQL_HOST -q -u$MYSQL_USER -p$MYSQL_PASSWORD $DBNAME | gzip > $DBNAME-$TIMESTAMP.sql.gz
http://help.textdrive.com/index.php?pg=kb.page&id=113
The backup is fine, its importing that’s the problem. I use phpMyAdmin.
Should I update phpMyAdmin on the old server then?
Offline
Re: MySQL importing error
While on this topic, wondering what others use to move an old website over to a new one. I have one site with 600mb of data and 4 databases which are large.
Normally I copy the files over to my localhost with Transmit for backup, then copy from the old server to the new one. Ditto for the databases. This takes a long time and if there is an error one has to do it again. Painful. Anyone have a better way?
Offline
#6 2006-03-26 10:33:50
- zem
- Developer Emeritus
- From: Melbourne, Australia
- Registered: 2004-04-08
- Posts: 2,579
Re: MySQL importing error
J, again: you need to use the -Q option, otherwise mysqldump won’t quote column names correctly. That’s why you’re getting the syntax error.
Alex
Offline
Re: MySQL importing error
Alex, I don’t quite get what you mean…the dump is being saved with the -q option, as per the code I posted above. At least, that’s what I think its doing.
Do you mean the exporting, or the importing?
Offline
Re: MySQL importing error
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
--quick, -qThis option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.
--quote-names, -QQuote database, table, and column names within ‘`’ characters. If the ANSI_QUOTES SQL mode is enabled, names are quoted within ‘“’ characters. This option is enabled by default. It can be disabled with —skip-quote-names, but this option should be given after any option such as —compatible that may enable —quote-names.
Offline
Re: MySQL importing error
Hi Sencer,
The old host runs MySQL 4.0.26, not 5.0. TXD is running 4.1.18 on the server I am using.
I managed to get the import working, by upgrading phpMyAdmin on the old server to the same version as TXD is running.
All ok, with the exception that Norwegian characters are showing up with big nasty ? marks in Safari. Yuck. I know this is something to do with the exporting from phpMyAdmin but I don’t know how to fix it.
Really need help with this!
Offline
Re: MySQL importing error
It doesn’t matter. The options are the same: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html (this is the manual page for 3.23, 4.0, and 4.1 – and both options have been around since 3.23)
What I was trying to point out was that -Q is different from -q, because you were referring to the latter, while alex was referring to the former.
Offline
Re: MySQL importing error
I think I am ok now with the export and import, or at least, all the data is in place and I am checking the integrity.
However, my main question now is what to do with Norwegian characters, since I have had this problem before and don’t know how to fix it. It always happens when exporting then importing.
All ? question marks in Safari instead of the actual Norwegian character.
Last edited by jstubbs (2006-03-26 19:45:37)
Offline
Re: MySQL importing error
There’s multiple things that may have gone wrong. Please post your high-level diagnostics:
http://textpattern.net/wiki/index.php?title=Unicode_Support#Diagnostics
Did you cpy over all files from the old install (including config.php), or did you do a fresh install on the new server? If you did the latter, config-php will look different, and can thus cause the symptoms you are describing in conjunction with a pre-mysql4.1-backup.
Offline