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

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

rmeetinrmeetin Posts: 81Questions: 20Answers: 0

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?

Answers

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    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

  • rmeetinrmeetin Posts: 81Questions: 20Answers: 0

    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.

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

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

    Allan

Sign In or Register to comment.