[solved] importing a mysql db

[Dr_Stein]

I am trying to move a mysql db from one machine to another.

both boxes have 4.0.25-r2 on them.

It's an old pMachine db, and it's working fine on the old host.

On the new host, when I run mysql, I get this:

jnichols@box ~/blah $ mysql -uroot -p(password) tktblog < tktblog.sql
ERROR 1064 at line 195: 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 'default char(3) NOT NULL default 'yes',
PRIMARY KEY (id)
) T
jnichols@box ~/blah $

Line 195 is pretty boring..

191 --
192 -- Table structure for table `pm_mailinglist`
193 --
194
195 CREATE TABLE pm_mailinglist (
196 id int(10) unsigned NOT NULL auto_increment,
197 email varchar(100) NOT NULL default '',
198 weblog char(3) NOT NULL default 'no',
199 default char(3) NOT NULL default 'yes',
200 PRIMARY KEY (id) 201 ) TYPE=MyISAM;

--

I have no idea why this isn't working. My mysql foo is weak. I asked on IRC (not in #gentoo) and turkeys just tell me to Google for it. Problem with Google these days is that there is so much irrelevant information about other versions of mysql that it'll take me ages to find the answer. My Googling & reading mysql docs have brought me no results.

Both versions of mysqldump are:
"mysqldump Ver 9.11 Distrib 4.0.25, for pc-linux-gnu (i686)"

If I can just take the stuff from /var/lib/mysql, I am willing to do that. There are just 2 small databases that I need to extract off of this old machine.

Otherwise.. anyone know a solution to this?

[heartburn]

I think "DEFAULT" is a sql keyword, as in:

[Mad Merlin]

Indeed, the previous poster got the idea. You need `default` (note those are backticks, not regular single quotes) rather than just default, as it is a keyword. You can have mysqldump generate quoted fields rather than unquoted ones, I believe it's the default, but I could be wrong. It might depend on what compatibility mode it's used in, as I know other databases use different quotes, I don't know if ANSI SQL specifies any particular quoting style.

[heartburn]

Thanks Mad Merlin!

So, given Mad Merlin's insight, I'd do this:

1) Check the mysqldump man pages to see if there's an option to quote identifiers

If so, use it. If Not:

1) TEMPORARILY change the name of the "default" field in the pm_mailinglist table to something like "OIKJFOIGHRPOIUGHPIDUFHIUDGHP".

2) Generate your SQL file with mysqldump.

3) Change the name of the field back to "default" in the original database so it continues to work.

4) Replace all occurences of "OIKJFOIGHRPOIUGHPIDUFHIUDGHP" in your mysqldump file to `default` (backticks)

5) Cross your fingers.

6) Run your script.

[heartburn]

I just checked the mysqldump docs. You want to use the "-Q" (--quote-names) option to quote identifiers. That's all you should need to do. I wonder why that's not the default.

[Dr_Stein]

Wow. I never in a million years would have thought of that.

I re-did the mysqldump with the -Q flag, and was able to import the database successfully.

Thanks!