Syntax error on inport of a SQL file produced by drizzledump

Bug #653300 reported by m00dawg on 2010-10-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
High
Andrew Hutchings
7.0
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
m00dawg (tim-moocowproductions) wrote :
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...

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
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...

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.

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...

Lee Bieber (kalebral) on 2010-10-06
Changed in drizzle:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers