Re: mysqldump file fails to recreate database
>> It's the first KEY definition it doesn't like. If >> I comment out "KEY field(field)," it works, except >> of course then the field called 'field' is not set >> as an index. ... Daniel> I'm pretty sure 'field' is a reserved word. Daniel> ... My feeling is that MySQL shouldn't allow Daniel> reserved words / characters like this, Daniel> whether they are in quotes or not. You can Daniel> continue to use KEY field(field) if you must Daniel> by using the -Q option with mysqldump, to Daniel> quote fieldnames. But I wouldn't. That was indeed the problem. Furthermore, I agree with your assessment. Using -Q is a kluge to mask brokenness. However, I started working in early October for a company that has a large legacy of data and Perl code in which certain templates are used. We have fields named 'field' in tables through our databases. It would be a tedious, though doable task to fix them all. What is *not* fixable as easily is the 150,000 lines or so of Perl code that creates and manipulates this stuff, or the data itself, in cases where we have columns whose contents consist of lists of field names, some of which may include "field". All that stuff would instantly break for all our clients if we were to start monkeying with it. So although I may be annoyed with the programmer who originally began using the reserved word "field" for column names back when MySQL was not as picky about it, at least I have a viable workaround for now. I also added -Q to the call to mysqldump in my database backup script. Thank you to those who responded. You've been very helpful. -- Lynn David Newton Phoenix, AZ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump file fails to recreate database
I've got a large test database and a recent backup made with mysqldump. I've been deleting records from it and would like to get back where I started. The easiest way is to drop database, create database, and then input from the mysqldump backup file. To automate it and make it quick I do it like this: mysqladmin -u rradmin --password=xx drop rrtest2 mysqladmin -u rradmin --password=xx create rrtest2 mysql -u rradmin --password=xx --database=rrtest2 < DATA.sql Reloading the DB from DATA.sql gives me this error message: ERROR 1064 at line 679: You have an error in your SQL syntax near 'field(field), KEY type(type), KEY tsrc(tsrc) ) TYPE=MyISAM' at line 10 The whole table definition in question looks like this: CREATE TABLE fnames ( field varchar(40) NOT NULL default '', type varchar(10) NOT NULL default '', fieldorder int(4) default '0', longdesc varchar(200) default NULL, required int(1) default '0', tsrc varchar(40) NOT NULL default '', visible int(1) default '0', misc blob, KEY field(field), # <= here's the problem KEY type(type), KEY tsrc(tsrc) ) TYPE=MyISAM; It's the first KEY definition it doesn't like. If I comment out "KEY field(field)," it works, except of course then the field called 'field' is not set as an index. The same exact problem occurs in two places in the mysqldump file. With them commented out the DB reloads. Perhaps some later version of mysql since this database was originally created came declares that one shouldn't have a field called 'field', or in which a field called 'field' shouldn't be an index (key)? Just a theory. Surely someone on this esteemed list of experts knows. -- Lynn David Newton Phoenix, AZ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
