How to find source of SQL error

[Robin S]

A couple of times when logging out (or it might have been logging in) I got an error message like:

Session: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1464507951' for column 'created' at row 1

How can I trace back to find out the table that is affected here?

Anyone know what can cause this error?

[Tom.]

Can you do a search for "1464507951" in the column "created"? 

SELECT * FROM * WHERE created = 1464507951

[Robin S]

Thanks for the suggestion.

Unfortunately the string 1464507951 doesn't appear anywhere in the database. Maybe it's not saved because it's invalid?

Does the "Session: " prefix at the start of the error message have any significance? - I have the core SessionHandlerDB module installed. Or is that shown for all PW error notifications?

[Craig]

The sessions table doesn't have a 'created' field, so I don't think it's that.

The 1292 looks like a page ID. Can you find a page with that ID and what is it? Do you have any hooks that may be attached to login/logout functionality? 

[Robin S]

The 1292 looks like a page ID.

Thanks, that was my first thought too. But I think it's actually a MySQL error code - Googling shows a lot of non-PW issues with the same code.

I don't have any login/logout hooks, but I do have custom code for front-end user registration/login/logout.

One thing I noticed that might be related is that when I add the 'Created' column to the Users lister (Access > Users) I see that the guest account and the superuser account were created '5 years ago'. Is that normal or a sign that something may be wrong with the created date for these users?

[kongondo]

I agree about the error being a MySQL code...(Google n all that). Besides, in the 'pages' table, the created column is not a datetime but a timestamp.

If you grep 'datetime' in /wire/ folder (dev branch), you get a result set with 15 files. Narrowing those down to Fieldtypes only you get 3 files. Narrowing these down to which ones have a column 'created' in their db schema, you get 2 files...

1. \wire\modules\Fieldtype\FieldtypeFile.module
2. \wire\modules\System\SystemNotifications\FieldtypeNotifications.module

If you are on dev branch (I don't know about devns), my money is on #2

Edited May 29, 2016 by kongondo

[Bill C]

Hi,

 This query will give you the columns and their data types -

SELECT DISTINCT TABLE_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('created')
        AND TABLE_SCHEMA='YourDatabaseName';

In my plain vanilla PW 3+ for example, I'm only seeing the following - 

field_images                            datetime
field_img1                                datetime
field_page_comment               int
field_page_comment_votes    timestamp
modules                                  timestamp
page_path_history                  timestamp
pages                                      timestamp

The end of the error message is giving you the row number the error occurred on ... 'for column 'created' at row ###'.

Having the row number, and knowing the tables that contain a 'created' column, perhaps you can determine the table involved with the error?? Of course knowing the table is one thing. It's significant, but it's only a piece of the puzzle.

As you already know, 1292 appears to be a mysql error number for an incorrect datetime value.

So, as you said, the error has occurred a couple of times when you were logging in , or out. This brings up questions for myself also. I have never looked into what type of user session logging goes on in PW by default.

Are you using some sort of module that is keeping track of user login/logout/session history? I just did a few searches and found this link from 4 years ago..  https://processwire.com/talk/topic/2082-module-process-login-history/ . Leads me to think there is no logging by default. Need to research.

Just something else for me to add to the 'Things to look into' list, I guess.

[Robin S]

Thanks for the help, really good advice.

I think I have found the source of the problem, and the answer came from looking in the PW error log (duh!).

There were a heap of similar 'Invalid datetime format' errors there, and most mention FieldtypeComments. An example:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1464551299' for column 'created' at row 1 (FieldtypeComments)

All the errors are for row 1, and I suspect even the ones that don't mention FieldtypeComments relate to the comments field because they are identical in all other respects (apart from the timestamp of course).

When I check the FieldtypeComments table in phpMyAdmin I can see that all the entries the 'created' column are indeed saved as timestamps.

Some strange things:

1. My comments field continues to work normally and all the comment times are displayed correctly on the front-end and back-end.

2. When I ran the SQL query suggested by Bill C my comments field was not one of the ones listed (results were 2 x file, 4 x image, and 1 x procache tables).

Is the comments fieldtype meant to store its times in datetime format? Could the table perhaps have been created wrong on install with the wrong data type for the 'created' column? I know little about databases and not sure how to check this.

[Robin S]

More strangeness: the timestamp in the error log and the timestamp in the database for the same comment are exactly 1 hour different.

Error log:

10 hours ago
2016-05-30 08:48:19 	puppyfan 	/stories/punters-favourite-is-one-very-fast-goat/ 	SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1464551299' for column 'created' at row 1 (FieldtypeComments)

Database row:

1204 	Good on you Calum for taking the risk on the Goat.... 	3 	114 	1 	puppyfan 	barry+six@mydomain.co.nz 	1464554899 	1590 	122.58.151.116 	Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) Ap... 		0 	0 	HfDxzuBCb8t8d6uVQWYJeiDFFouiA9dAXGb_qhx_2lK9vlnIbP... 	potLYc_2ERq9vc7RGD42xyTr81tfOZk3qSMMKR77 	0 	0 	NULL

Note the difference of 1464551299 vs 1464554899, which is 2016-05-30 07:48:19 vs 2016-05-30 08:48:19

[Robin S]

I resolved this issue - it was related to the default SQL mode changes introduced in MySQL 5.7

I set sql_mode to the previous MySQL version's default of NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION and all is good now.