Wordpress "Could not insert post into the database"

[jndawson]

This is a newly built server with very little customization. Customer site was moved from another server with no issues.

Pertinent info:
OS: CloudLinux 3.10.0-962.3.2.lve1.5.28.el7.x86_64
WHM: v86.0.9 (will update to .12 tonight)
Webserver: Apache/2.4.41 (cPanel) OpenSSL/1.1.1d mod_bwlimited/1.4 mod_fcgid/2.3.9 mod_cpanel/1.4 Phusion_Passenger/5.3.7 Server MPM: prefork

PHP 7.3.14 (cli) (built: Feb 4 2020 08:00:17) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.14, Copyright (c) 1998-2018 Zend Technologies
with the ionCube PHP Loader + ionCube24 v10.3.9, Copyright (c) 2002-2019, by ionCube Ltd.
with Zend OPcache v7.3.14, Copyright (c) 1999-2018, by Zend Technologies

PHP Handler: lsapi
php-fpm OFF

WordPress 5.3.2

Issue: Customer can't upload images to Wordpress using the media library using the drag&drop method or the browser method.
Turns out that the uploaded files don't display in the media library, but they actually get uploaded to the uploads directory.

Directory and file perms and owns are correct (755/644/user), and we've tested with changing them around, including 777/777 (really stupid, but, testing).

We've tried somewhere around twenty supposed sure fire fixes from every Wordpress-related site around. We turned off all the plugins. Turned off mod_sec. Nothing worked.

The only error during all this testing was:

from user_org.php.error.log

[25-Feb-2020 11:36:12 UTC] WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTOwp_postmeta(post_id,meta_key,meta_value) VALUES (415, '_et_pb_truncate_post_date', '2020-02-25 11:36:12') made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/Divi/index.php'), truncate_post, apply_filters('et_truncate_post_use_custom_content'), WP_Hook->apply_filters, et_divi_truncate_post_use_custom_content, update_post_meta, update_metadata, add_metadata

We repaired/optimized tables and had this error:

Table   Op  Msg_type    Msg_text 
user_wp.wp_posts    optimize    note    Table does not support optimize, doing recreate + ...
user_wp.wp_posts    optimize    error   Invalid default value for 'post_date'
user_wp.wp_posts    optimize    status  Operation failed

Trying to determine how to correct 'Invalid default value for 'post_date'' ran us down one rabbit hole after the other.

Does anybody have any idea what is going on and where to go to fix it?

Side Note: While filling out this form, we wanted to be sure we clearly tagged the post:

Oops! We ran into some problems.
            You may not create new tags. Please change the following tags: media, library, error, upload

 

[cPanelLauren]

I was reading somewhere that this is commonly caused by the tables being set to MYISAM as oppossed to INNoDB, have you looked at switching them?

From the mysql command line what's the output of the following:

show variables like 'sql_mode';

Side Note: While filling out this form, we wanted to be sure we clearly tagged the post:

We have create new tags turned off but you should be able to add existing tags (all tag creation was disabled previously) so you can add tags that exist but not create new ones. I'm not sure why all tags were disabled previously but I can look at enabling them globally.

Wordpress, wp upload, mysql, post_date might be good ones to add and I've added the ones you didn't have in there in the meantime.

 

[jndawson]

I was reading somewhere that this is commonly caused by the tables being set to MYISAM as oppossed to INNoDB, have you looked at switching them?

From the mysql command line what's the output of the following:

show variables like 'sql_mode';

Tables are INNoDB
sql=""

We tried several different sql= variables, but none had any effect.

 

[jndawson]

We've opened a ticket #93460218 . Any revelations will be reported here.

 

[cPanelLauren]

Hi @jndawson

Ok, great, I apologize I've been out sick all week - I'm glad you opened a ticket though.

 

[cPanelLauren]

Reading through that ticket and it looks like the following was the outcome:

It looks like Auto Increment was not enabled for the table which lead to duplicate keys the workaround for this was as follows:

The resequencing issue appears to be related to the key value of 0. When auto_increment is enabled, this results in any column value with a key of 0 or NULL being set to '1'.

A proposed solution to this behavior when the structure of a database that is already heavily populated with data is updated is to temporarily set the sql_mode for MySQL to no_auto_value_on_zero in the /etc/my.cnf configuration file, restart the server and then alter the MySQL database structure.

Can you let us know if all is functioning well at this time? I did see you had some further issues with it but they may not be specific to this issue.