MySQL Error: Query was empty (1065) on simple INSERT statement

Question

I am trying to run a large script that creates a table, and then inserts almost 15,000 rows into it. The table gets created just fine, and then at the 833 INSERT, I get an error:

Error: Query was empty (1065)

Here is my 833rd INSERT statement (the one that is failing):

INSERT INTO CLASSCODE (CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, PROGRAM_NM, ST_CODE, EFF_DT, EXP_DT) VALUES (10255, "Funeral Directors - incl PL other than Crematory  - 10255", 3, "Service", "AZ", 19980801, NULL);

I can't see any syntax errors or differences between this line, and one that works. FOr reference, here is an example of an INSERT statement that works just fine:

INSERT INTO CLASSCODE (CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, PROGRAM_NM, ST_CODE, EFF_DT, EXP_DT) VALUES (10425, "Frame Shop - Picture/Posters                      - 10425", 2, "Retail", "AZ", 19980801, NULL);

The part that puzzles me is that the error sounds like something that would happen if I was populating the new row using data from another SELECT statement, which was coming up empty. That is not the case, though, as my INSERT statements are all using static data.

My table definition looks like this:

CREATE TABLE CLASSCODE (
   CLASS_CODE INTEGER NOT NULL, 
   CLASS_CODE_NAME VARCHAR(60) NOT NULL, 
   RATE_GROUP SMALLINT NOT NULL, 
   PROGRAM_NM VARCHAR(20) NOT NULL, 
   ST_CODE CHAR(2), 
   EFF_DT DATE, 
   EXP_DT DATE)

I am running this script in the GUI MySQL Query Browser.

Could it be something to do with the number of rows I'm trying to insert? Do I need to periodically commit? Is there something simple that I am just overlooking?

Thanks!

Answer 1

The most common scenario to run into these in a script file is when you have a double semi-colon somewhere:

INSERT INTO CLASSCODE 
(CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, PROGRAM_NM, ST_CODE, EFF_DT, EXP_DT)
VALUES
(10255, "Funeral Directors - incl PL other than Crematory  - 10255", 3, "Service", "AZ", 19980801, NULL)
;;

I'd do a quick search through the script and see if there's a ;; in there around line 833.

Answer 2

Since the other inserts are working okay and there doesn't appear to be a difference in the structure of them, my first thought is that you're running out of some resource at insert number 833.

What happens if you do a commit after each insert (your transaction buffer may be running out of space)? Does it work then? Actually, a better test would be a single commit at transaction number 800. If you then get beyond 833, then that was your problem.

If so, simply modify your script (or the script that creates your script id it's auto-generated) to commit every 100 or so inserts (committing after every insert will probably make it unbearably slow).

Answer 3

In case you're worried about your actual query syntax, it seems fine. I created your table and ran the insert and it worked. When you say you're running a script, do you just have a file full of raw SQL that you're running through? The times I've run into the "Empty Query" type error before have been when people are using JDBC-type stuff and have their looping logic messed up.

I think we can safely say it's not that particular insert statement that you pasted in, it's got to be something server related or syntax around that query in the script. The rows should be getting commited automatically unless you've reconfigured something. You can try running "show variables;" from the mysql command line and appending that here. We can then take a look at your setup and see if anything looks weird.

Have you tried re-ordering some of your inserts in the file? That will at least tell us if it's failing on a particular query or a particular spot in the file. Try reordering your inserts just to see if it still fails about 833 in or if it fails in a different place. If it falls in the same place, it sounds like a memory or buffer-related thing. If it changes places when you move it, I'd say you've got a stray character (a ; or a . maybe) laying around somewhere like @zombat said.

Hope that helps. Let us know what you can.

Answer 4

I would suggest running the sql from the command line in a terminal or SSH session. The gui interface may be interfering with or limiting your ability to run the large number of inserts.