| Bug #7563 | Query Browser won't execute script output from mysqldump with quotes in fields | ||
|---|---|---|---|
| Submitted: | 28 Dec 2004 22:03 | Modified: | 29 Dec 2004 22:27 |
| Reporter: | John McClenahan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Query Browser | Severity: | S2 (Serious) |
| Version: | 1.1.4 | OS: | Linux (Fedora Core 3, fully patched) |
| Assigned to: | CPU Architecture: | Any | |
[28 Dec 2004 22:03]
John McClenahan
[29 Dec 2004 0:12]
MySQL Verification Team
I tested your script on Slackware and QB 1.1.4 without any problems.
[29 Dec 2004 11:52]
John McClenahan
Thank you for trying this. I still have the problem, consistently repeatable on Fedora Core 3, but have found a different way round most of it - which I should probably have used in the first place, if I had remembered about it first. Using the command line tool 'mysql' reads almost all of the input without problems, and gets way past the error I reported previously. I found in QB that even after I edited the SQL text to replace \' with ~ (tilde), QB fell over at other input characters - those characters with numeric values (in UTF8) showing in little boxes as 0085, 0091 and 0092 when viewed in the QB script tab. I think these correspond to the display characters 'long dash', and curly open or close quotes but I'm not sure about the latter - they may be ordinary double quote characters. There seemed also to be some un-escaped single quotes left in the dump file too, in the middle of text fields, which converted oddly from latin1 to UTF8 and also caused QB to fail. However, even the command line tool mysql falls over with an error on a much smaller table, reproduced below - this time on the table definition, not the data. When I tried re-creating this table in the Query Browser it too wouldn't read it, giving the same error message about a SQL error in the table definition (without saying what the problem was). When I had edited the text in a couple of places to see if I could fix it, then re-executed, QB exited abruptly without an error message. I tried first removing the default '' entry for the Text field, then the NOT NULL entry for the DUMMY Timestamp - the latter edit on its own caused QB to crash. So there still seem to be some inconsistencies between the output of mysqldump and various input tools including mysql as well as QB. I have now got all the data in, except for this non-critical table, by cutting out the lines in the SQL file for this table alone, and re-importing the rest using the command line tool. Thank you again for responding so promptly. John McC -- -- Table structure for table `testautoinc` -- CREATE TABLE testautoinc ( ID int(20) unsigned NOT NULL auto_increment, Text field varchar(100) NOT NULL default '', Dummy timestamp(14) NOT NULL, PRIMARY KEY (ID) ) TYPE=MyISAM; -- -- Dumping data for table `testautoinc` -- INSERT INTO testautoinc VALUES (1,'Test record 1',20040106230300); INSERT INTO testautoinc VALUES (2,'Test record 2',20040106230454); INSERT INTO testautoinc VALUES (3,'Test record 3',20040106230614); INSERT INTO testautoinc VALUES (4,'Test record 4',20040106231141);
[29 Dec 2004 22:27]
MySQL Verification Team
Well I tested again your small script without problem. However you have a syntax error: Text field varchar(100) NOT NULL default '', and I changed to: Text_field varchar(100) NOT NULL default '',
[29 Dec 2004 23:24]
John McClenahan
Again, thanks for looking at this so promptly and helping me understand where the error came from - I certainly would NOT have spotted it. All I did was cut and paste the entries (on the Linux system) from the mysqldump output file when sending extracts to you, and also before trying to edit the extract in QB to get it to work. The syntax error (which first appeared when mysql command line method was reading in from the SQL dump output) appears due, you think, to the missing underscore between Text and Field. It was missing in the dump file! I see now that it is (confusingly) the name of the field (Column name = 'Text field'), and was generated when I originally designed the table in MS Access - which allows spaces in field names. When exported to a MySQL table, the space persisted, and is still there in the original table on our web host. (Maybe that explains something else I can't get to work properly - converting autoincrementing fields in an existing table from MS Access to MySQL - which is what that table was trying to test out in a simple case.) At least I now understand where and how the error was generated, and perhaps it isn't a bug -- if MySQL isn't supposed to allow spaces in column (field) names then neither the command line tool mysql nor mysqldump would waste time checking for them. Since I didn't remember reading this anywhere, I've gone back and looked at the manual, and still can't find it! I looked at the Tutorial - Defining a Table - and also searched for Column Name and Column Naming. I'm sure it must be there somewhere, but I still can't see it! This Linux and MySQL learning process reminds me of an adventure game - every time you get to one level of understanding, there are another two to come, full of traps! I've been using computers since my school days in the late 1950s as a hobby, but I still have a long way to go, clearly. J McC
[29 Dec 2004 23:33]
John McClenahan
Tried to be too clever when searching - asking for Column Name instead of Field Name. The latter does find it in section 9.2. I still had to read it a few times to clarify that the space character is NOT ok in unquoted field names, but might be (I think) in quoted ones surround by back-ticks. JMcC
