Supergeek MySQL Tech Bleg
January 10th, 2009 at 11:06 pm by Preston Taylor HolmesThis is the kind of post that can make your eyes roll back in your head, so go no further unless you’re interested in MySQL databases and the like.
Here’s my bleg. I need some help with my MySQL database. Here’s the poop – I’m trying to export both my comments and posts tables (both of which are large), then import the comments to a new/test database on the same MySQL server.
I’ve exported the comments file in a varity of compatibility modes (MYSQL40, 323, ANSI, NONE) and every time I try to restore it, I get the same error.
“#1064 – 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 ” at line 76″
I’m baffled because I’m exported from and importing to the same MySQL db server, and none of the compatibility versions seem to make a difference.
If anyone has any suggestions, in the words of Ross Perot, I’m all ears. You can shoot me an e-mail at preston at this here domain (you know the drill). Thanks!
January 11th, 2009 at 4:24 pm
Try splitting it up into multiple backups then look for malformed syntax in the comments that might be mucking up the works.
January 12th, 2009 at 11:30 am
I’m used to MS SQL Server, rather than MySQL, but have you tried inserting directly from one database to the other?
In transact SQL, the syntax is DATABASE_NAME.OWNER.TABLE_NAME
So inserting into a table in another database would look like:
Insert Into TestDatabase.dbo.Comments (field, list, here)
Select field, list, here from dbo.Comments
Again, I don’t know if this syntax works in MySQL, but that’s how I would do it in full SQL Server.
January 14th, 2009 at 7:06 am
I have essentially a similar problem with backups using PostgreSQL.
In PostgreSQL I have the option of dumping data using PG’s own format or a more universal INSERT mode, which enables me to export to any other SQL database. For some reason that PG mode doesn’t work but the universal mode works famously
January 14th, 2009 at 9:08 am
I was able to overcome my main issue by migrating all my dbs to the same server, which enabled me to copy the db. I have a feeling the problem with my export/import is the collation my DB is in, so I may have a fix for that but haven’t had time to mess with it. Thanks for the help guys.
January 19th, 2009 at 10:50 am
Just wait a few days. Once Obama is in office all SQL queries will return the correct results. Also, you’ll be able to successfully divide by zero.
January 21st, 2009 at 7:53 pm
Yeah. Man up and use a real DBMS.