WP Failures with MySQL 5.1

[kbento0]

WP Fails to work properly if autocommit is turned off and the default storage engine is InnoDB. It also fails to work when the SQL mode prohibits zero dates (as required when the DDL is applied).

There are three places (associated with mysql_connect) where I've added...

$this->query( "SET sql_mode = ''" ) ;
$this->query( "SET autocommit = 1" ) ;

This is not a good long-term fix. A better long-term fix would be to remove NO_ZERO_IN_DATE and NO_ZERO_DATE and anything that would imply either setting from the SQL mode.

A patch similar to this one should help fix the issue (based on current stable):

diff -ur wordpress/wp-includes/class-simplepie.php wordpress_new/wp-includes/class-simplepie.php
--- wordpress/wp-includes/class-simplepie.php   2010-04-01 23:37:42.000000000 -0500
+++ wordpress_new/wp-includes/class-simplepie.php       2011-01-31 15:56:26.000000000 -0600
@@ -8832,6 +8832,8 @@
                if ($this->mysql = mysql_connect($server, $username, $password))
                {
                        $this->id = $name . $extension;
+                        $this->query( "SET sql_mode = ''" ) ;
+                        $this->query( "SET autocommit = 1" ) ;
                        $this->options = SimplePie_Misc::parse_str($mysql_location->get_query());
                        if (!isset($this->options['prefix'][0]))
                        {
diff -ur wordpress/wp-includes/wp-db.php wordpress_new/wp-includes/wp-db.php
--- wordpress/wp-includes/wp-db.php     2010-07-25 02:34:49.000000000 -0500
+++ wordpress_new/wp-includes/wp-db.php 2011-01-31 15:56:26.000000000 -0600
@@ -533,6 +534,8 @@
                }

                $this->ready = true;
+                $this->query( "SET sql_mode = ''" ) ;
+                $this->query( "SET autocommit = 1" ) ;

                if ( $this->has_cap( 'collation' ) && !empty( $this->charset ) ) {
                        if ( function_exists( 'mysql_set_charset' ) ) {
@@ -1051,6 +1054,8 @@
 <p>If you're unsure what these terms mean you should probably contact your host. If you still need help you can always visit the <a href='http://wordpress.org/support/'>WordPress Support Forums</a>.</p>
 "/*/WP_I18N_DB_CONN_ERROR*/, $details['db_host'] ), 'db_connect_fail' );
                }
+                $this->query( "SET sql_mode = ''" ) ;
+                $this->query( "SET autocommit = 1" ) ;
                $this->select( $details[ 'db_name' ], $this->$dbhname );
        }

[filosofo]

Related: #8857

[Ramoonus]

anyone tested this with MySQL 5.5 or 5.6?

[pento]

Removing NO_ZERO_IN_DATE and NO_ZERO_DATE from the sql_mode is not a proper fix for this edge case.

Manually setting autocommit may be an option in the future, once InnoDB becomes more common amongst WordPress users, and if there's a significant number who have autocommit disabled.

[nacin]

Manually setting autocommit may be an option in the future, once InnoDB becomes more common amongst WordPress users, and if there's a significant number who have autocommit disabled.

Agreed.