Re: [sqlite] Resetting a Primary Key
Hi Vincent, On 11/21/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > .. is giving me "primary key must be unique" errors. > > How can I reset the sequence for a primary key? The table only has > about 15000 records in it and I've never seen this happen before.. Though I haven't used sqlite but apparently you must have stored NULL once into the table and were storing it a second time which led to this error because this violated the primary key constraint. You could have COUNTed NULLs instead on ItemID using different values for other relevant columns to use GROUP BY to check the data yourself and see what was wrong. -- Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Mitchell Vincent wrote: I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how the corruption happened but my users have been known to do things like store the live data file on a USB flash drive and use it so it's hard to tell! Thanks to all for the information and suggestions! I'm glad to see you got it resolved and didn't loose your data. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Mitchell Vincent wrote: I'm not sure what it means, but here it is : *** in database main *** On page 2580 at right child: 2nd reference to page 2677 On tree page 9 cell 15: 2nd reference to page 2678 On tree page 9 cell 15: Child page depth differs On tree page 9 cell 16: Child page depth differs Page 2681 is never used Page 2682 is never used Page 2683 is never used ... This means your database is hosed. :-) If everything was correct, the integrity check would have simply returned "OK". There is generally no practical way to resurrect a corrupt database file. It's time to get out your backup. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how the corruption happened but my users have been known to do things like store the live data file on a USB flash drive and use it so it's hard to tell! Thanks to all for the information and suggestions! On Nov 20, 2007 7:43 PM, Trey Mack <[EMAIL PROTECTED]> wrote: > INSERT INTO invoice_items > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > Does this work? > > INSERT INTO invoice_items > (invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Mitchell Vincent wrote: select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: Mitchell Vincent wrote: Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. Can you select the max and min item_id values form the database and report them back here. Can you then change your code to insert a new dummy record with an item_id that is set to one greater than the maximum value, rather than your hard coded NULL value. I just want to see if you can insert a properly formed record into your database at all. You should then delete that record so it doesn't cause a problem in your database later. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - Mitchell, You should probably run an integrity check on your database to see if it has been corrupted somehow. Can you issue a PRAGMA INTEGRITY_CHECK command? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
No triggers at all in the database. It's pretty vanilla.. On Nov 20, 2007 6:04 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > > The table schema : > > > > CREATE TABLE invoice_items ( > > item_id INTEGER PRIMARY KEY, > > invoice_id int4, > > product_id int4, > > product_name text , > > sku text , > > description text , > > quantity text, > > price int4, > > cost int4, > > taxable bool, > > format_price text, > > format_total_price text, > > taxable2 boolean) > > > > The query : > > > > INSERT INTO invoice_items > > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > The error : > > > > PRIMARY KEY must be unique (19) > > > > I wonder if this error might be coming from a TRIGGER > and not from the INSERT statement itself? > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Mitchell Vincent wrote: > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > > The table schema : > > > > CREATE TABLE invoice_items ( > > item_id INTEGER PRIMARY KEY, > > invoice_id int4, > > product_id int4, > > product_name text , > > sku text , > > description text , > > quantity text, > > price int4, > > cost int4, > > taxable bool, > > format_price text, > > format_total_price text, > > taxable2 boolean) > > > > The query : > > > > INSERT INTO invoice_items > > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > The error : > > > > PRIMARY KEY must be unique (19) > > > > I can't post the database itself because it contains some sensitive > > information (as you might have guessed, it's a database of billing > > information). > > > > The INSERT query for the invoice_items table has "NULL" hard coded in > > for item_id, so I know it's not accidentally being set to an existing > > value. > > > > > > > Can you select the max and min item_id values form the database and > report them back here. > > Can you then change your code to insert a new dummy record with an > item_id that is set to one greater than the maximum value, rather than > your hard coded NULL value. I just want to see if you can insert a > properly formed record into your database at all. You should then delete > that record so it doesn't cause a problem in your database later. > > Dennis Cote > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > Here we go. I am accessing the database through an ODBC connection > using the most recent version from Christian's site.. > > The table schema : > > CREATE TABLE invoice_items ( > item_id INTEGER PRIMARY KEY, > invoice_id int4, > product_id int4, > product_name text , > sku text , > description text , > quantity text, > price int4, > cost int4, > taxable bool, > format_price text, > format_total_price text, > taxable2 boolean) > > The query : > > INSERT INTO invoice_items > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > The error : > > PRIMARY KEY must be unique (19) > I wonder if this error might be coming from a TRIGGER and not from the INSERT statement itself? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Mitchell Vincent wrote: Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. Can you select the max and min item_id values form the database and report them back here. Can you then change your code to insert a new dummy record with an item_id that is set to one greater than the maximum value, rather than your hard coded NULL value. I just want to see if you can insert a properly formed record into your database at all. You should then delete that record so it doesn't cause a problem in your database later. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. On Nov 20, 2007 4:44 PM, <[EMAIL PROTECTED]> wrote: > > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I have a primary key that auto increments and has apparently > > overlapped back on to itself. > > > > INSERT into mytable(id,name) values(NULL,'test'); > > > > ... is giving me "primary key must be unique" errors. > > > > How can I reset the sequence for a primary key? The table only has > > about 15000 records in it and I've never seen this happen before.. > > > > There are over 9e18 keys. So if you create 1 billion new > rows per second, continuously, it will take you 262 years to > exhaust all primary keys. Since SQLite has only been in > existance for 7 years, this is clearly impossible so you > must be doing something wrong. > > Perhaps if you provided more details we could help you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
No doubt, it is obviously something that has been screwed up. Unfortunately I'm in "hot fix" mode right now and the investigation into why it happened will happen after I get this customer back up and running. I have a feeling it has something to do with the ODBC driver being used. I'll gather and post the schema of the table along with the query. The "null" value is hard-coded into the query string of the INSERT query so it is definitely not being set incorrectly by the software - I let SQLite handle the ID assignment. Is it possible to set the sequence that a primary key uses to track the next ID? If so, how can I do it? Thanks very much for the reply! On Nov 20, 2007 4:44 PM, <[EMAIL PROTECTED]> wrote: > > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I have a primary key that auto increments and has apparently > > overlapped back on to itself. > > > > INSERT into mytable(id,name) values(NULL,'test'); > > > > ... is giving me "primary key must be unique" errors. > > > > How can I reset the sequence for a primary key? The table only has > > about 15000 records in it and I've never seen this happen before.. > > > > There are over 9e18 keys. So if you create 1 billion new > rows per second, continuously, it will take you 262 years to > exhaust all primary keys. Since SQLite has only been in > existance for 7 years, this is clearly impossible so you > must be doing something wrong. > > Perhaps if you provided more details we could help you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > ... is giving me "primary key must be unique" errors. > > How can I reset the sequence for a primary key? The table only has > about 15000 records in it and I've never seen this happen before.. > There are over 9e18 keys. So if you create 1 billion new rows per second, continuously, it will take you 262 years to exhaust all primary keys. Since SQLite has only been in existance for 7 years, this is clearly impossible so you must be doing something wrong. Perhaps if you provided more details we could help you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Resetting a Primary Key
Hi Mitchell, I don't know that resetting the primary key would be productive, since rollover of INTEGER PRIMARY KEY would not occur anywhere even remotely close to 15000. Are you sure you aren't somehow attempting an insert of a key that has already been used -- perhaps because of some race condition? -Original Message- From: Mitchell Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 4:01 PM To: sqlite-users@sqlite.org Subject: [sqlite] Resetting a Primary Key I have a primary key that auto increments and has apparently overlapped back on to itself. INSERT into mytable(id,name) values(NULL,'test'); .. is giving me "primary key must be unique" errors. How can I reset the sequence for a primary key? The table only has about 15000 records in it and I've never seen this happen before.. -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
