Make WordPress Core

Opened 13 years ago

Closed 12 years ago

Last modified 12 years ago

#16429 closed defect (bug) (maybelater)

WP Failures with MySQL 5.1

Reported by: kbento0's profile kbento0 Owned by:
Milestone: Priority: normal
Severity: major Version: 3.0.4
Component: Database Keywords:
Focuses: Cc:

Description

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 );
        }

Change History (4)

#1 @filosofo
13 years ago

Related: #8857

#2 @Ramoonus
13 years ago

anyone tested this with MySQL 5.5 or 5.6?

#3 @pento
12 years ago

  • Resolution set to maybelater
  • Status changed from new to closed

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.

#4 @nacin
12 years ago

  • Milestone Awaiting Review deleted

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.

Note: See TracTickets for help on using tickets.