WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 21 months ago

Last modified 21 months ago

#16429 closed defect (bug) (maybelater)

WP Failures with MySQL 5.1

Reported by: 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)

comment:1 filosofo3 years ago

Related: #8857

comment:2 Ramoonus3 years ago

anyone tested this with MySQL 5.5 or 5.6?

comment:3 pento21 months 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.

comment:4 nacin21 months 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.