New row gets ID 0

Hi,

I have a table with compound batch data. There's 2783 row already, starting with ID 1. If I manually add a new one, it gets ID 0 (zero). The ID generation for this table is set to Autoincrement.

Please advice!

You are presumably using a MySQL database?

The ID values should be assigned automatically by the database using the autoincrement feature. If this is not happening then it could be that something is wrong with the DB. Could you restart IJC and the MySQL DB and see if the problem still happens.

If it does still occur, could you describe how you are inserting the new rows - using the Add row dialog in form or grid view?

 

Thanks

Tim

Hi,

If by restarting you mean disconnecting and reconnecting to the mySQL database, then this does not help. The procedure I use for adding the new row is through the 'New Row...' button in the Grid view.

No, I was meaning to also restart the MySQL server.

So the 'New row...' used to work but now has stopped working?

Tim

Hi,

Stopping and starting the mySQL server does not make a difference, I still get row ID 0 if I add a single row. Normally I tend to import new data, but since this is a single entry I use the 'New Row...' function.

If I create a new dummy table within the database it works fine, the first row starts with ID 1...

could you connect to MySQL directly (e.g. using the mysql program that is provided with the DB) and report on the definition of the table of concern. e.g like this:

 

$ mysql -u ijc -p ijc
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.39 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> desc AAA;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| ID      | int(11)       | NO   | PRI | NULL    | auto_increment |
| AAA_ID  | int(11)       | YES  | UNI | NULL    |                |
| Country | varchar(1000) | YES  |     | NULL    |                |
+---------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> 

Thanks

Tim

mysql> desc COMPOUND_BATCHES;
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| ID_COMPOUND_BATCHES | int(11)       | NO   | PRI | 0       |       |
| Compound_Batch_ID   | varchar(1000) | YES  |     | NULL    |       |
| ID_COMPOUNDS        | int(11)       | YES  | MUL | NULL    |       |
| Compound_ID         | varchar(1000) | YES  |     | NULL    |       |
| Supplier            | varchar(1000) | YES  |     | NULL    |       |
| Supplier_ID         | varchar(1000) | YES  |     | NULL    |       |
| Submitter           | varchar(1000) | YES  |     | NULL    |       |
| Submission_Date     | date          | YES  |     | NULL    |       |
| Project_of_Origin   | varchar(1000) | YES  |     | NULL    |       |
| Mol_Weight          | float         | YES  |     | NULL    |       |
| Total_Mol_Weight    | float         | YES  |     | NULL    |       |
| Salt                | varchar(1000) | YES  |     | NULL    |       |
| Salt_Weight         | float         | YES  |     | NULL    |       |
+---------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

mysql>

Hi Tim,

I tend to use the mysqladministrator GUI when looking at mySQL databases. There is an option to activate the Autoincrement feature in the Table Editor. Is this what I should do?

Tim,

It looks like I could fix everything through the procedure described, i.e. turning on Autoincrement, and also setting NULL as default value. Now I could add a new row manually, and I got ID 2784.

Thanks!

Yes, it looks like the autoimcrement property was lost for that table somehow.

The solution is to add it again and set the value to the next integer value. Seems like this is what you did and its OK now.

Tim

Tim,

I now found out what causes the problem. If I create a new table, a default ID field is created, and has the right settings (autoincrement on, default value null). However, when I change the ID field name and corresponding DB Column Name to something else (e.g. ID_COMPOUNDS) in IJC, then the autoincrement and default value settings are removed at the database level (I noticed this through the mysqladministrator GUI).

So it looks like changing the ID field name triggers mySQL to change these table settings. I don't know if this is a bug, but it is a bit annoying, because for each new table I have to check whether the settings in mySQL are correct.

Kinds regards,

Evert

Hi Evert,

Thanks for that detective work. We'll see if we can find a solution for this.

Tim