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

Activity

1. JanJakes commented on Oct 10, 2025

   

   JanJakes

   on Oct 10, 2025

   Member

   More actions

   @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.
2. ivan-ottinger commented on Oct 10, 2025

   

   ivan-ottinger

   on Oct 10, 2025

   Author

   More actions

   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.
3. JanJakes commented on Oct 10, 2025

   

   JanJakes

   on Oct 10, 2025

   Member

   More actions

   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

   👍React with 👍1Reacted by Ivan Ottinger
4. ivan-ottinger commented on Oct 10, 2025

   

   ivan-ottinger

   on Oct 10, 2025

   Last edited by ivan-ottinger

   Author

   More actions

   @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. 🙂
5. JanJakes commented on Apr 16, 2026

   

   JanJakes

   on Apr 16, 2026

   Member

   More actions

   I believe this was fixed by Automattic/wp-cli-sqlite-command#17.
6. JanJakes

   closed this as completedon Apr 16, 2026