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
Re: mysqldump file fails to recreate database
Lynn David Newton wrote: 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. I had one like this yesterday. I'm pretty sure 'field' is a reserved word. The problem arises because some GUIs back-quote (`) all field / index names, which MySQL then can recognize as names. My feeling is that MySQL shouldn't allow reserved words / characters like this, whether they are in quotes or not. You can continue to use KEY field(field) if you must by using the -Q option with mysqldump, to quote fieldnames. But I wouldn't. Does anyone know the logic behind allowing reserved words when inside quotes like this? It clearly causes problems - especially for people using GUIs - and these people will be the least able to diagnose the problem. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - 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
RE: mysqldump file fails to recreate database
My feeling is that MySQL shouldn't allow reserved words / characters like this, whether they are in quotes or not. ... Does anyone know the logic behind allowing reserved words when inside quotes like this? It clearly causes problems - especially for people using GUIs - and these people will be the least able to diagnose the problem. Is that a problem with the GUI application, a problem with a user not knowing enough, or a problem with MySQL? I'd say it is not a problem with MySQL, but with either the user or the GUI. A few years ago when I was first starting out using MySQL I had a table with a bad column name, when we upgraded the name no longer worked in queries because MySQL became a little bit stricter in regards to reserved words and I didn't know to back-tick escape them. That was my fault for picking a bad column name, it came from lack of knowledge. I should have never used the column name and had I known more I wouldn't have. It is not the software's job to teach me, it is my job to learn, and from that experience I learned what reserved words are and that it is not a great idea to use them. I'm sure MySQL allows use of reserved words in order to be more compliant with other RDBMS. MS SQL will allow a column to be named 'field' and be indexed without even emitting an angry beep. Let's say I have an application written using MS SQL and I decide I want to convert to MySQL. If I have to change all of my column and key names I am going to be pretty angry and will be on this list complaining about what a shoddy product MySQL is when compared to MS SQL. That would hardly encourage more people to start using this great application. I don't think I should be limited in what I can do because of the lack of knowledge of other people. MySQL should not be written considering the lowest common denominator. Features, reliability, stability, performance and compatibility are far more important factors in designing a top-notch product if you ask me. There are plenty of places for beginners to gain knowledge about the product: this list; the site; web tutorials; and books, to name a few. There's also experience, which is probably one of the best teachers. I don't think it is wise to pander to an audience that doesn't want to think on their own, then you not only limit progress but also alienate the majority of target users. This of course is just my personal (not-very-well-thought-out) opinion and I'm sure a lot of people disagree. -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 3:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mysqldump file fails to recreate database Lynn David Newton wrote: 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. I had one like this yesterday. I'm pretty sure 'field' is a reserved word. The problem arises because some GUIs back-quote (`) all field / index names, which MySQL then can recognize as names. You can continue to use KEY field(field) if you must by using the -Q option with mysqldump, to quote fieldnames. But I wouldn't. Does anyone know the logic behind allowing reserved words when inside quotes like this? It clearly causes problems - especially for people using GUIs - and these people will be the least able to diagnose the problem. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - 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
