Re: Error importing from mysqldump output
Michael Stassen wrote: > Mysql reports the first thing it didn't understand, which isn't > necessarily the first thing wrong. I note that it thought \' was a > command, which implies it didn't see the preceding ' as the *start* of > a string, which implies something went wrong earlier in the line. Of > course, it's impossible to guess what. Could you post the entire > line, and perhaps a line or two before? Unfortunately not, for a number of reasons. Firstly, in the meantime I've been doing search & replace on the dump file to get rid of the duplicated \'\' stuff. Secondly, the dump file is HUGE, and I'm not really sure what part it had a problem with. I probably *should* be able to narrow it down to the table, by opening the dump file in a text editor and going to the line number mentioned in the error, but I've tried that a couple of times and whatever editor I use just locks up ... the file's far too big. I let gedit run for 15 minutes before finally killing it. Thirdly, if the error is where I think it is, the whole table has confidential stuff in it, and I'd have to mask every mention of companies / people. This wouldn't leave much. Anyway, my original search and replace seems to have been a stupid thing to do. Since I'm only testing things out ( trying to get stored procedures working ), I didn't think to keep a backup of the backup in case something happens. Frankly I'm not too concerned about it anyway. I'll start from scratch, importing the data via ODBC, make a new mysqldump file, and see if the problem persists. If it does, I'll be back, and I won't destroy the evidence this time... Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error importing from mysqldump output
Daniel Kasak wrote: Hi all. I'm testing out mysql-5.0.7. I dumped a database from 5.0.4 with the command: mysqldump --opt DB_NAME > DB_NAME.sql -p Now I'm importing with: mysql DB_NAME < DB_NAME.sql -p The data has a field which has some quotes in it. The field looks like this ( all quotes included ): '' '' ie 2x single quotes, a space, and 2x single quotes. Don't ask me why. I didn't put it there. Anyway, mysqldump has packaged this field as follows: '\'\' \'\'' I'll put it into context inside an SQL statement: insert into some_table ( some_field ) values ( '\'\' \'\'' ); When I mysql hits this line, I get: ERROR at line 895: Unknown command '\''. The line looks properly escaped to me. Should I file a bug report? Mysql reports the first thing it didn't understand, which isn't necessarily the first thing wrong. I note that it thought \' was a command, which implies it didn't see the preceding ' as the *start* of a string, which implies something went wrong earlier in the line. Of course, it's impossible to guess what. Could you post the entire line, and perhaps a line or two before? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error importing from mysqldump output
Daniel Kasak wrote: >The data has a field which has some quotes in it. The field looks like >this ( all quotes included ): >'' '' > >ie 2x single quotes, a space, and 2x single quotes. >Don't ask me why. I didn't put it there. Anyway, mysqldump has packaged >this field as follows: >'\'\' \'\'' > > Update. Perhaps this is 2 bugs in 1. I've found a LOT of this sort of thing in the dump file. It seems that every single quote that's encountered is represented: \'\'...instead of just: \' But still, mysql should simply import 2 quotes where there should have been one, right? I don't know. I'm starting to get confused. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
I agree that using a reserved word for any purpose than that for which it is reserved is a poor design choice. I also strongly encourage you to change the name of that field and any others that conflict with the reserved words list (the field name "desc" is another name that frequently causes this problem for just the same reason) http://dev.mysql.com/doc/mysql/en/Reserved_words.html However, mysqldump does have the option to backtick-quote all field names in its CREATE TABLE statements. The full list of options is available if you run mysqldump --help and the option you are interested in can be turned on either with -Q or --quote-names. That makes your dump command read daemon0% mysqldump -Q -S mysqld-daemon0.sock --master-data --all-databases > daemin0-dump.sql Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Kasak <[EMAIL PROTECTED]> wrote on 11/09/2004 06:16:38 PM: > Russell E Glaue wrote: > > > > > ERROR 1064 at line 14071: 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 'order int(1) NOT NULL default '0', > > PRIMARY KEY (moduleID), > > > > > > Would this possibly be a bug with mysqldump ? > > -RG > > 'order' is a reserved word. > I would rename that field, pronto! > In my opinion the bug is not in mysqldump, but in mysql allowing you to > use a fieldname that is a reserved word. > And yes I know about the backticks that mysqlcc uses, but surely this > causes more problems than it solves. See above example. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
At 10:33 +1100 11/10/04, Daniel Kasak wrote: Russell E Glaue wrote: 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. So if a word is reserved, a table with that same spelling cannot exist? That's right. You can't ( you *shouldn't* be able to ) use reserved for table or field names. If this is so, then why did mysql allow it to be created? I really don't know. See my above comments. order is indeed a reserved word, but it is easily possible to create a table that has order (or any other reserved word) as a column name if you quote it with identifier quoting characters (backticks): http://dev.mysql.com/doc/mysql/en/Legal_names.html As for mysqldump, if you use the command "mysqldump --help", you get a list of alowable options. One of them is this: -Q, --quote-names Quote table and column names with a ` So if you add -Q or --quote-names to your mysqldump command, you should get dump output that can be imported without the problems that you are seeing. This option is on by default in MySQL 4.1, by the way, which helps forestall the issue. It remains off in 4.0 by default so as not to break existing 4.0 scripts that use mysqldump. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
Russell E Glaue wrote: 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. So if a word is reserved, a table with that same spelling cannot exist? That's right. You can't ( you *shouldn't* be able to ) use reserved for table or field names. If this is so, then why did mysql allow it to be created? I really don't know. See my above comments. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
Daniel Kasak wrote: Russell E Glaue wrote: ERROR 1064 at line 14071: 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 'order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), Would this possibly be a bug with mysqldump ? -RG 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. So if a word is reserved, a table with that same spelling cannot exist? If this is so, then why did mysql allow it to be created? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
Russell E Glaue wrote: ERROR 1064 at line 14071: 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 'order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), Would this possibly be a bug with mysqldump ? -RG 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
