An SQL error occurred: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value ...

rmeetin

The full error (using fictitious database account name:

The error: An SQL error occurred: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column account_table.galleries2.id at row 1

I mostly use VestaCP for my websites but sometimes must use cPanel. All my forms execute perfectly on Vesta, complex to simple. I was updating a client's site today which runs on cPanel and I am seeing the above error on most of my forms, not all. The forms, db connection, database tables are identical.

The invalid datetime format makes no sense at all because these forms do no use any date fields in the forms and none of the fields are of date or datetime type. See:

create table galleries2 
(
id int auto_increment primary key,
catid int,
title varchar(80),
gallery_type varchar(12),
rowOrder int
)

I have a couple similar simple forms that are working. This is feeling like something specific to cPanel and maybe even mysql or mysql configuration. Suggestions?

allan

My guess is that your cPanel server is using MiraDB, while the VestaCP one is running MySQL as the database. MiraDB is more strict about the date formats it accepts. ISO8601 is the best format to use here.

Are you using out PHP libraries for Editor? If so, can you add ->debug(true) just before the ->process(...) call, and then when you submit the form, the JSON response will show SQL debug information. Can you show me that JSON response please?

Thanks,
Allan

rmeetin

I added the debugging lines to the code but they gave no more information than the original error.

Technical support got back to me in a few minutes after submitting a ticket. They have seen the problem before. All they did was add:

sql-mode=""

to the /etc/my.cnf file and restart mysql. This solved the problem.

allan

Fair enough - thanks for the update! Good to hear you've got it working now.

Allan