Jetpack import fails due to `Integrity constraint violation: 19 NOT NULL constraint failed: wp_posts.post_content_filtered` error

[ivan-ottinger]

Importing Jetpack backup attached to the related Linear issue (STU-816) results in the following error:

<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: wp_posts.post_content_filtered in /wordpress/wp-content/mu-plugins/sqlite-database-integration/wp-includes/sqlite-ast/class-wp-sqlite-connection.php:116

Steps to reproduce:

1. Take the Jetpack backup attached to the Linear issue above.
2. Try to import it to a local site in Studio.
3. Observe the issue.

[JanJakes]

@ivan-ottinger I checked this, and this doesn't seem to be an issue with the driver.

There are some queries like this in the dump:

REPLACE INTO `wp_posts` (`ID`,`post_author`,`post_date`,`post_date_gmt`,`post_content`,`post_title`,`post_excerpt`,`post_status`,`comment_status`,`ping_status`,`post_password`,`post_name`,`to_ping`,`pinged`,`post_modified`,`post_modified_gmt`,`post_parent`,`guid`,`menu_order`,`post_type`,`post_mime_type`,`comment_count`) VALUES (980,'5','2025-07-22 13:21:54','2025-07-22 12:21:54','','Primitivo Salento IGP Poggio Maru\'','','publish','open','closed','','c-primitivo-salento-igp-poggio-maru','','','2025-09-19 08:01:45','2025-09-19 07:01:45',0,'https://vinarius.embark.studio/?post_type=product&p=980',0,'product','','0');

The query doesn't list post_content_filtered which is a NOT NULL column, so the error is correct.

Perhaps the dump expects to be executed with a specific SQL mode that is non-strict? But in that case, a statement like SET SQL_MODE='...'; is usually included in the backup, but I wasn't able to find one.

We do support SET SQL_MODE='...'; and recognize some of the strict/non-strict SQL modes, and when set, the backup works.

So this problem seems to be rather about the usage of the backup—why does the backup seem to expect non-default SQL mode but doesn't set it anywhere? Is it expected to be used in some specific way?

That made me wonder whether wp db import sets any non-default SQL modes, but I don't think so. It seems to directly use the mysql/mariadb commands.

[ivan-ottinger]

Thank you for looking into this @JanJakes!

That's helpful.

So this problem seems to be rather about the usage of the backup—why does the backup seem to expect non-default SQL mode but doesn't set it anywhere? Is it expected to be used in some specific way?

If I am understanding correctly, from your point of view it looks like the backup itself isn't correct, right?

The file should be a standard Jetpack backup created from the site that is hosted on Pressable so I don't expect there should be anything special about it. Unless there's some bug. 🤔

I am going to check with Jetpack Backup folks to see if they can spot anything about that DB dump.

[JanJakes]

If I am understanding correctly, from your point of view it looks like the backup itself isn't correct, right?

@ivan-ottinger The backup looks kind of correct, but it seems that it's presuming a specific SQL mode or a specific way of importing, because I couldn't find a statement within the backup that would set the SQL mode for the backup connection.

That's strange, because it means it would fail in MySQL the same way. Here's a demo: https://onecompiler.com/mysql/43z84u5br

[ivan-ottinger]

@ivan-ottinger The backup looks kind of correct, but it seems that it's presuming a specific SQL mode or a specific way of importing, because I couldn't find a statement within the backup that would set the SQL mode for the backup connection.

That's strange, because it means it would fail in MySQL the same way. Here's a demo: https://onecompiler.com/mysql/43z84u5br

Thanks for sharing. I looked into this a bit today from Studio perspective. I can imagine that we could rewrite the dump in Studio as the importing happens, but it might be better and cleaner to address the issue at the point where the backup is generated.

Let's see what our colleagues from Jetpack Backups think first. 🙂