EDIT: TL;DR version
I typed this
CREATE INDEX IF NOT EXISTS IDX_FILE_SIZE table_name (file_size);
instead of this
CREATE INDEX IF NOT EXISTS IDX_FILE_SIZE ON table_name (file_size);
Don't do that.
-
-1: Too much code. Can you cut this down to just the code that shows the problem? The minimal SQL that exhibits the error? And then replace the looooong code samples with short, to-the-point code samples?S.Lott– S.Lott2010-03-05 11:06:42 +00:00Commented Mar 5, 2010 at 11:06
-
1Minus error handling and sql, there are 14 lines of code there. I spent an hour shortening the code to this point, which was the smallest I could get it while still getting the error.Nathan Spears– Nathan Spears2010-03-05 13:04:05 +00:00Commented Mar 5, 2010 at 13:04
-
@Nathan Spears: Valiant, but the question is still HUGE. What's all that "testIndexes" code? And all that "verifyIndexes" code? Can you actually reduce the amount of code you're posting here so something that minimally shows the problem.S.Lott– S.Lott2010-03-05 15:48:25 +00:00Commented Mar 5, 2010 at 15:48
-
@S.Lott: Do what the OP should have done: chill out, slow down, read carefully the CREATE INDEX statements at the start of the question, if you haven't spotted the one line that's causing the problem by the time you get to the 6th CREATE INDEX statement, go back to step 1. If that doesn't work, read my answer.John Machin– John Machin2010-03-06 00:24:14 +00:00Commented Mar 6, 2010 at 0:24
-
The problem was that I was missing an ON keyword. After an hour of rearranging the code and script in every conceivable manner while completing missing the obvious, I had a test function and several verify functions. Because I didn't see the discrepancy in that one line, I thought the problem had something to do with the way I was running the python code or the script order or something arcane, which is why I posted all of it. You are right, I should have narrowed the problem down more, but then Catch 22: if I had correctly narrowed the problem down, I wouldn't have posted it here.Nathan Spears– Nathan Spears2010-03-06 01:54:02 +00:00Commented Mar 6, 2010 at 1:54
|
Show 1 more comment
1 Answer
Some silly questions:
Is it a concidence that the offending statement is missing the word ON?
CREATE INDEX IF NOT EXISTS IDX_FILE_FULLPATH_FILE_PARENT_DIR ON table_name (file_fullpath, file_parent_dir);
CREATE INDEX IF NOT EXISTS IDX_FILE_SIZE table_name (file_size); -- missing ON
CREATE INDEX IF NOT EXISTS IDX_TAG_TITLE ON table_name (tag_title);
Somewhere in all the verbiage in your question, did I see the phrase "syntax error"?
Did you try the simple step of running the SQL statements in the sqlite3 command interpreter and seeing which syntax error you were actually getting?
E.g.
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (bar int, zot int);
sqlite> create index barx on foo(bar);
sqlite> create index zotx foo(zot);
SQL error: near "foo": syntax error
sqlite>
Have you considered perusing TFRRD (The Fantastic Rail-Road Diagram) in the docs?
You wrote: """when I run that command in the smaller script (verifyIndexSmaller), it gives no error. If I then try to run the larger script again, even though the index has been created by the smaller script, I still get the error""".
Have you considered the possibility that you didn't run that command in the smaller script, but actually ran another (fixed!) version of that command?
Do you now understand why S.Lott was trying to get you to cut the waffle and focus on the piece of SQL that was causing the error?
1 Comment
Nathan Spears
It's no coincidence, but I had been staring at it too long to see what was missing. I outlined pretty clearly in my original post exactly what the sql problem was. I had broken down every line in the script and run them individually, which was how I figured out which line was the problem, but somehow I just didn't see the missing keyword. I thought I had even copied and pasted the line from the working script to the "broken script", but obviously I hadn't. Thanks for the second pair of eyes!