Leginon

I posted a work around for the libCV problem in 1.4.1, but if you still would like to use 1.3, I can try to figure out this database problem.

In version 1.3, in data.py, there is a check to see if your database tables have been updated from Leginon 1.2 to 1.3. It checks the UserData table to see if all the columns (except DEF_id) have the NULL flag set to 'YES'. Maybe the method we are using to check for NULL is not working in some cases. Can you post the result of running the query "describe UserData" from the mysql command line or phpMyAdmin. It could be a different format than we are expecting. We expect the NULL flag to be in the third column of the result. For example, this is my result:

mysql> describe UserData;
+---------------------+-----------+------+-----+-------------------+----------------+
| Field               | Type      | Null | Key | Default           | Extra          |
+---------------------+-----------+------+-----+-------------------+----------------+
| DEF_id              | int(16)   | NO   | PRI | NULL              | auto_increment |
| DEF_timestamp       | timestamp | YES  | MUL | CURRENT_TIMESTAMP |                |
| name                | text      | YES  |     | NULL              |                |
| full name           | text      | YES  |     | NULL              |                |
| REF|GroupData|group | int(20)   | YES  | MUL | NULL              |                |
+---------------------+-----------+------+-----+-------------------+----------------+

I can already see two ways this could fail. One is if Null is not in the third column, the other is if the value of Null is not exactly 'YES', but something else, like 'yes' or 'true' or something like that.

Output on the older system, which does not produce errors in Leginon 1.3 is:

mysql> describe UserData;

------------------------------+------+-----+-------------------+----------------+

| Field | Type | Null | Key | Default | Extra

|

------------------------------+------+-----+-------------------+----------------+

DEF_id

int(16)

PRI

NULL

auto_increment

DEF_timestamp

timestamp

YES

MUL

CURRENT_TIMESTAMP

|

name

text

YES

NULL

|

full name

text

YES

NULL

|

REF

GroupData

group

int(20)

YES

MUL

NULL

|

------------------------------+------+-----+-------------------+----------------+

5 rows in set (0.00 sec)

mysql>

I think this is the same as your output except the NULL flag is neither YES or NO for the DEF_id field. Leginon 1.3 runs okay on this system.

On the new system (SUSE 10.3) with Leginon 1.3 I get:

mysql> describe UserData;

------------------------------+------+-----+-------------------+----------------+

| Field | Type | Null | Key | Default | Extra

|

------------------------------+------+-----+-------------------+----------------+

DEF_id

int(16)

NO

PRI

NULL

auto_increment

DEF_timestamp

timestamp

NO

MUL

CURRENT_TIMESTAMP

|

name

text

YES

NULL

|

full name

text

YES

NULL

|

REF

GroupData

group

int(20)

YES

MUL

NULL

|

------------------------------+------+-----+-------------------+----------------+

5 rows in set (0.05 sec)

mysql>

I think this is the same as your output except the DEF_timestamp field has NO for the NULL flag. (In particular, note that the NULL flag is the same as your output for the DEF_id field.) It is surprising that the output is not identical to that for my old Leginon system because it's supposedly a copy of that database (using one of two approaches described in the O'Reilly MySQL book - I may need to look for another reference on MySQL). I believe it is the DEF_timestamp field that causes the error in Leginon 1.3. I modified the code in data.py to produce a little more output at the relevant section in the program with field names, etc and that's what the output suggested. Could a newer release of MySQL be imposing the value NO on the NULL flag for the DEF_timestamp - which I think means there is a non-null constraint on that field which is possibly reasonable for a timestamp field?

William

"pulokas" wrote: I posted a work around for the libCV problem in 1.4.1, but if you still would like to use 1.3, I can try to figure out this database problem.

In version 1.3, in data.py, there is a check to see if your database tables have been updated from Leginon 1.2 to 1.3. It checks the UserData table to see if all the columns (except DEF_id) have the NULL flag set to 'YES'. Maybe the method we are using to check for NULL is not working in some cases. Can you post the result of running the query "describe UserData" from the mysql command line or phpMyAdmin. It could be a different format than we are expecting. We expect the NULL flag to be in the third column of the result. For example, this is my result:

mysql> describe UserData;
+---------------------+-----------+------+-----+-------------------+----------------+
| Field               | Type      | Null | Key | Default           | Extra          |
+---------------------+-----------+------+-----+-------------------+----------------+
| DEF_id              | int(16)   | NO   | PRI | NULL              | auto_increment |
| DEF_timestamp       | timestamp | YES  | MUL | CURRENT_TIMESTAMP |                |
| name                | text      | YES  |     | NULL              |                |
| full name           | text      | YES  |     | NULL              |                |
| REF|GroupData|group | int(20)   | YES  | MUL | NULL              |                |
+---------------------+-----------+------+-----+-------------------+----------------+

I can already see two ways this could fail. One is if Null is not in the third column, the other is if the value of Null is not exactly 'YES', but something else, like 'yes' or 'true' or something like that.

I have studied this for a while and this is the conclusion:

It is a bug in data.py to check all fields except DEF_id for the NULL flag. In fact, we should be checking all fields except DEF_id and DEF_timestamp. The datasync.py script is actually doing the update properly by adding the NULL flag to all columns except DEF_id and DEF_timestamp. We do not declare those two fields to be NULL or NOT NULL, because they are always set to something automatically during an insert, and we never expect them to be NULL. Not declaring the NULL status of the field causes it to be set to some default defined in the particular version of MySQL server. After copying the database to another server, the field takes on the default NULL declaration for that server.

The fix:

data.py is checking that DEF_timestamp is NULL, when it should be ignoring it.

The fix is to change:

if field[0] == 'DEF_id':

to

if field[0] == 'DEF_id' or field[0] == 'DEF_timestamp':

or completely remove that database check (about 21 lines of code). If you completely remove the database check, you need to be sure to manually check the database to be sure that the NULL conversion has been done, and run datasync.py if necessary.

Special note for directly upgrading from 1.2 to 1.4

Version 1.4 of Leginon does not even do check for NULL in the database. Therefore, if you are upgrading from 1.2 or earlier to 1.4, then you will not be warned that your database needs to be updated. If doing such an upgrade, be sure to run datasync.py.