Syntax error on inport of a SQL file produced by drizzledump

Bug #653300 reported by m00dawg
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Fix Released
High
Andrew Hutchings
7.0
Fix Released
High
Andrew Hutchings

Bug Description

After removing my VIEWs from my MySQL schema, I am able to produce a SQL file using drizzledump. However, trying to import this file produces:

root@mcp:~# drizzledump -c -p3306 -uroot -pxyz mcptest > mcp.sql
-- %10000 rows dumped for table Logger
-- %20000 rows dumped for table Logger
-- %30000 rows dumped for table Logger
-- %40000 rows dumped for table Logger
-- %50000 rows dumped for table Logger
-- %60000 rows dumped for table Logger
-- %70000 rows dumped for table Logger
-- %80000 rows dumped for table Logger
-- %90000 rows dumped for table Logger
-- %100000 rows dumped for table Logger
-- %110000 rows dumped for table Logger
-- %120000 rows dumped for table Logger
-- %130000 rows dumped for table Logger
-- %140000 rows dumped for table Logger
-- %150000 rows dumped for table Logger
-- %160000 rows dumped for table Logger
-- %170000 rows dumped for table Logger
-- %180000 rows dumped for table Logger
-- %190000 rows dumped for table Logger
-- %200000 rows dumped for table Logger
-- %210000 rows dumped for table Logger
-- %220000 rows dumped for table Logger
-- %230000 rows dumped for table Logger

root@mcp:~# drizzle -p3307 moocowproductions < mcp.sql
ERROR 1064 (42000) at line 58: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '0x654b88,'1280x720','Unknown',10850x654b88,'M00Book Running:<BR>\
 Pov-Ray 3.1 ' at line 1

If I try to wrap it into one command, I get:

root@mcp:~# drizzledump -p3306 -uroot -Pxyz mcptest --destination-type=database --destination-port=3307 --destination-database=moocowproductio
-- drizzledump 2010.09.1802 libdrizzle 7, for pc-linux-gnu (x86_64)
--
-- Host: localhost Database: mcptest
-- ------------------------------------------------------
-- Server version 5.1.41-3ubuntu12.6 (MySQL server)

Error executing query: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use \ ' at line 1b8206d8,'1280x720','Unknown',10850x7fffab8206d8,'M00Book Running:<BR>\
Error executing query: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '0x7fffab8206d8,'Owiber','',''),('2','151','Uhh, whoops :) Sorry dude. I\'ll fix ' at line 1
root@mcp:~#

It looks like it has to do with using the MySQL equivalent of --hexblob?

Related branches

Changed in drizzle:
assignee: nobody → Andrew Hutchings (linuxjedi)
importance: Undecided → High
milestone: none → 2010-10-11
status: New → Triaged
Revision history for this message
m00dawg (tim-moocowproductions) wrote :
Revision history for this message
Patrick Crews (patrick-crews) wrote : Re: [Bug 653300] Re: Syntax error on inport of a SQL file produced by drizzledump
Download full text (3.2 KiB)

Are there blobs/text in that table? I was seeing something similar in my
randgen testing, but I haven't had a chance to analyze it yet.

On Fri, Oct 1, 2010 at 6:20 PM, m00dawg <email address hidden> wrote:

>
> ** Attachment added: "MySQL dump version of the database (data included)"
>
> https://bugs.launchpad.net/drizzle/+bug/653300/+attachment/1664838/+files/mcptest.sql.gz
>
> --
> Syntax error on inport of a SQL file produced by drizzledump
> https://bugs.launchpad.net/bugs/653300
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
>
> Status in A Lightweight SQL Database for Cloud Infrastructure and Web
> Applications: Triaged
> Status in Drizzle elliott series: Triaged
>
> Bug description:
> After removing my VIEWs from my MySQL schema, I am able to produce a SQL
> file using drizzledump. However, trying to import this file produces:
>
> root@mcp:~# drizzledump -c -p3306 -uroot -pxyz mcptest > mcp.sql
> -- %10000 rows dumped for table Logger
> -- %20000 rows dumped for table Logger
> -- %30000 rows dumped for table Logger
> -- %40000 rows dumped for table Logger
> -- %50000 rows dumped for table Logger
> -- %60000 rows dumped for table Logger
> -- %70000 rows dumped for table Logger
> -- %80000 rows dumped for table Logger
> -- %90000 rows dumped for table Logger
> -- %100000 rows dumped for table Logger
> -- %110000 rows dumped for table Logger
> -- %120000 rows dumped for table Logger
> -- %130000 rows dumped for table Logger
> -- %140000 rows dumped for table Logger
> -- %150000 rows dumped for table Logger
> -- %160000 rows dumped for table Logger
> -- %170000 rows dumped for table Logger
> -- %180000 rows dumped for table Logger
> -- %190000 rows dumped for table Logger
> -- %200000 rows dumped for table Logger
> -- %210000 rows dumped for table Logger
> -- %220000 rows dumped for table Logger
> -- %230000 rows dumped for table Logger
>
> root@mcp:~# drizzle -p3307 moocowproductions < mcp.sql
> ERROR 1064 (42000) at line 58: You have an error in your SQL syntax; check
> the manual that corresponds to your Drizzle server version for the right
> syntax to use near '0x654b88,'1280x720','Unknown',10850x654b88,'M00Book
> Running:<BR>\
> Pov-Ray 3.1 ' at line 1
>
>
> If I try to wrap it into one command, I get:
>
> root@mcp:~# drizzledump -p3306 -uroot -Pxyz mcptest
> --destination-type=database --destination-port=3307
> --destination-database=moocowproductio
> -- drizzledump 2010.09.1802 libdrizzle 7, for pc-linux-gnu (x86_64)
> --
> -- Host: localhost Database: mcptest
> -- ------------------------------------------------------
> -- Server version 5.1.41-3ubuntu12.6 (MySQL server)
>
> Error executing query: You have an error in your SQL syntax; check the
> manual that corresponds to your Drizzle server version for the right syntax
> to use \ ' at line 1b8206d8,'1280x720','Unknown',10850x7fffab8206d8,'M00Book
> Running:<BR>\
> Error executing query: You have an error in your SQL syntax; check the
> manual that corresponds to your Drizzle server version for the right syntax
> to use near '0x7fffab8206d8,'Owiber','',''),('2','151','Uhh, whoops :) Sorry
> ...

Read more...

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

1. Date regex was found to be broken for matching dates with 0000 or 00 in.
2. Date/time conversion was spitting out a pointer as well as converted data (main problem)
3. A '%' was output with the progress output
4. Disable NOT NULL on dates for conversion because a 0000-00-00 in data will turn into a NULL.

Changed in drizzle:
status: Triaged → Fix Committed
Revision history for this message
m00dawg (tim-moocowproductions) wrote :
Download full text (6.4 KiB)

Yep. I don't think there are BLOBs (as in binary data) but TEXT is definitely in there.

On Oct 1, 2010, at 6:23 PM, Patrick Crews wrote:

> Are there blobs/text in that table? I was seeing something similar in my
> randgen testing, but I haven't had a chance to analyze it yet.
>
> On Fri, Oct 1, 2010 at 6:20 PM, m00dawg <email address hidden>
> wrote:
>
>>
>> ** Attachment added: "MySQL dump version of the database (data included)"
>>
>> https://bugs.launchpad.net/drizzle/+bug/653300/+attachment/1664838/+files/mcptest.sql.gz
>>
>> --
>> Syntax error on inport of a SQL file produced by drizzledump
>> https://bugs.launchpad.net/bugs/653300
>> You received this bug notification because you are a member of Drizzle-
>> developers, which is subscribed to Drizzle.
>>
>> Status in A Lightweight SQL Database for Cloud Infrastructure and Web
>> Applications: Triaged
>> Status in Drizzle elliott series: Triaged
>>
>> Bug description:
>> After removing my VIEWs from my MySQL schema, I am able to produce a SQL
>> file using drizzledump. However, trying to import this file produces:
>>
>> root@mcp:~# drizzledump -c -p3306 -uroot -pxyz mcptest > mcp.sql
>> -- %10000 rows dumped for table Logger
>> -- %20000 rows dumped for table Logger
>> -- %30000 rows dumped for table Logger
>> -- %40000 rows dumped for table Logger
>> -- %50000 rows dumped for table Logger
>> -- %60000 rows dumped for table Logger
>> -- %70000 rows dumped for table Logger
>> -- %80000 rows dumped for table Logger
>> -- %90000 rows dumped for table Logger
>> -- %100000 rows dumped for table Logger
>> -- %110000 rows dumped for table Logger
>> -- %120000 rows dumped for table Logger
>> -- %130000 rows dumped for table Logger
>> -- %140000 rows dumped for table Logger
>> -- %150000 rows dumped for table Logger
>> -- %160000 rows dumped for table Logger
>> -- %170000 rows dumped for table Logger
>> -- %180000 rows dumped for table Logger
>> -- %190000 rows dumped for table Logger
>> -- %200000 rows dumped for table Logger
>> -- %210000 rows dumped for table Logger
>> -- %220000 rows dumped for table Logger
>> -- %230000 rows dumped for table Logger
>>
>> root@mcp:~# drizzle -p3307 moocowproductions < mcp.sql
>> ERROR 1064 (42000) at line 58: You have an error in your SQL syntax; check
>> the manual that corresponds to your Drizzle server version for the right
>> syntax to use near '0x654b88,'1280x720','Unknown',10850x654b88,'M00Book
>> Running:<BR>\
>> Pov-Ray 3.1 ' at line 1
>>
>>
>> If I try to wrap it into one command, I get:
>>
>> root@mcp:~# drizzledump -p3306 -uroot -Pxyz mcptest
>> --destination-type=database --destination-port=3307
>> --destination-database=moocowproductio
>> -- drizzledump 2010.09.1802 libdrizzle 7, for pc-linux-gnu (x86_64)
>> --
>> -- Host: localhost Database: mcptest
>> -- ------------------------------------------------------
>> -- Server version 5.1.41-3ubuntu12.6 (MySQL server)
>>
>> Error executing query: You have an error in your SQL syntax; check the
>> manual that corresponds to your Drizzle server version for the right syntax
>> to use \ ' at line 1b8206d8,'1280x720','Unknown',10850x7fffab8206d8,'M00Book
>> Running:<BR>\
>> E...

Read more...

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

Patrick: the 0x7fffab8206d8 was a pointer that was sent out by accident when doing date/time conversions. Epic fail. No idea why this didn't happen in my original testing, I must have changed the code since then.

Revision history for this message
Patrick Crews (patrick-crews) wrote :
Download full text (3.4 KiB)

Hmm, that means there might be something else with blobs as I haven't
introduced dates into my randgen tests. Expect to hear something more about
this tomorrow. It's awesome to see people testing this!

On Fri, Oct 1, 2010 at 7:36 PM, Andrew Hutchings
<email address hidden>wrote:

> Patrick: the 0x7fffab8206d8 was a pointer that was sent out by accident
> when doing date/time conversions. Epic fail. No idea why this didn't
> happen in my original testing, I must have changed the code since then.
>
> --
> Syntax error on inport of a SQL file produced by drizzledump
> https://bugs.launchpad.net/bugs/653300
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
>
> Status in A Lightweight SQL Database for Cloud Infrastructure and Web
> Applications: Fix Committed
> Status in Drizzle elliott series: Fix Committed
>
> Bug description:
> After removing my VIEWs from my MySQL schema, I am able to produce a SQL
> file using drizzledump. However, trying to import this file produces:
>
> root@mcp:~# drizzledump -c -p3306 -uroot -pxyz mcptest > mcp.sql
> -- %10000 rows dumped for table Logger
> -- %20000 rows dumped for table Logger
> -- %30000 rows dumped for table Logger
> -- %40000 rows dumped for table Logger
> -- %50000 rows dumped for table Logger
> -- %60000 rows dumped for table Logger
> -- %70000 rows dumped for table Logger
> -- %80000 rows dumped for table Logger
> -- %90000 rows dumped for table Logger
> -- %100000 rows dumped for table Logger
> -- %110000 rows dumped for table Logger
> -- %120000 rows dumped for table Logger
> -- %130000 rows dumped for table Logger
> -- %140000 rows dumped for table Logger
> -- %150000 rows dumped for table Logger
> -- %160000 rows dumped for table Logger
> -- %170000 rows dumped for table Logger
> -- %180000 rows dumped for table Logger
> -- %190000 rows dumped for table Logger
> -- %200000 rows dumped for table Logger
> -- %210000 rows dumped for table Logger
> -- %220000 rows dumped for table Logger
> -- %230000 rows dumped for table Logger
>
> root@mcp:~# drizzle -p3307 moocowproductions < mcp.sql
> ERROR 1064 (42000) at line 58: You have an error in your SQL syntax; check
> the manual that corresponds to your Drizzle server version for the right
> syntax to use near '0x654b88,'1280x720','Unknown',10850x654b88,'M00Book
> Running:<BR>\
> Pov-Ray 3.1 ' at line 1
>
>
> If I try to wrap it into one command, I get:
>
> root@mcp:~# drizzledump -p3306 -uroot -Pxyz mcptest
> --destination-type=database --destination-port=3307
> --destination-database=moocowproductio
> -- drizzledump 2010.09.1802 libdrizzle 7, for pc-linux-gnu (x86_64)
> --
> -- Host: localhost Database: mcptest
> -- ------------------------------------------------------
> -- Server version 5.1.41-3ubuntu12.6 (MySQL server)
>
> Error executing query: You have an error in your SQL syntax; check the
> manual that corresponds to your Drizzle server version for the right syntax
> to use \ ' at line 1b8206d8,'1280x720','Unknown',10850x7fffab8206d8,'M00Book
> Running:<BR>\
> Error executing query: You have an error in your SQL syntax; check the
> manual that correspo...

Read more...

Changed in drizzle:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.