Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-9637

SET NAMES 'BINARY' error in convertor

    Details

    • Type: Bug
    • Status: Unverified Fix
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.0.7-PL1
    • Fix Version/s: 3.0.8-RC1
    • Labels:
      None
    • Environment:
      Destination database MySQL >= 4.1.3
      Source database MySQL 3.23 or 4.0

      Description

      The error occurs when converting from a database version < 4.1.3 to a database version >= 4.1.3. The resulting error is one of the two listed here:

      General Error
      SQL ERROR [ mysql4 ]
       
      Unknown system variable 'NAMES' [1193]
       
      SQL
       
      SET NAMES 'binary'

      SQL ERROR [ mysql4 ]
       
      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NAMES 'binary'' at line 1 [1064]
       
      SQL
       
      SET NAMES 'binary'

      The backtrace in both of these errors points to line 707 of install/install_convert.php.

      The problem is that function sql_server_info in mysql.php is returning the version of the destination database instead of the source database.

        Issue Links

          Activity

          Hide
          A_Jelly_Doughnut A_Jelly_Doughnut added a comment -

          The code looks right to me.

          			// Thanks MySQL, for silently converting...
          			case 'mysql':
          			case 'mysql4':
          				if (version_compare($src_db->sql_server_info(true), '4.1.3', '>='))
          				{
          					$convert->mysql_convert = true;
          				}
           
          [snip]
          		if ($convert->mysql_convert && !$same_db)
          		{
          			$src_db->sql_query("SET NAMES 'binary'");
          		}
          


          However, I note that elsewhere in the code where SET NAMES is issued, the condition looks like this:

          		if ($convert->mysql_convert && $same_db)
          		{
          			$src_db->sql_query("SET NAMES 'binary'");
          		}
          

          (Removed a ! operator)

          Can you try this? If that doesn't do it, I'll grumble and pull down some ancient MySQL versions for testing

          Show
          A_Jelly_Doughnut A_Jelly_Doughnut added a comment - The code looks right to me. // Thanks MySQL, for silently converting... case 'mysql': case 'mysql4': if (version_compare($src_db->sql_server_info(true), '4.1.3', '>=')) { $convert->mysql_convert = true; }   [snip] if ($convert->mysql_convert && !$same_db) { $src_db->sql_query("SET NAMES 'binary'"); } However, I note that elsewhere in the code where SET NAMES is issued, the condition looks like this: if ($convert->mysql_convert && $same_db) { $src_db->sql_query("SET NAMES 'binary'"); } (Removed a ! operator) Can you try this? If that doesn't do it, I'll grumble and pull down some ancient MySQL versions for testing
          Hide
          A_Jelly_Doughnut A_Jelly_Doughnut added a comment -

          Whoops, forgot to mention a file name. The snippet I'm talking about is in install/install_convert.php, around line 705.

          Show
          A_Jelly_Doughnut A_Jelly_Doughnut added a comment - Whoops, forgot to mention a file name. The snippet I'm talking about is in install/install_convert.php, around line 705.
          Hide
          D¡cky Richard Foote [X] (Inactive) added a comment -

          The code is correct as it is written and the change you have suggested does work, I have suggested that change several times in the convertors forum, but it only masks the problem. It is not a fix for the problem.

          You don't need a really old version of MySQL to see where the real problem is. You only need to be able to connect to two databases of different versions. This is the test that I used:

                   case 'mysql4':
          echo 'Line 688. mysql version is ' . $src_db->sql_server_info(true) . '<br />';
                      if (version_compare($src_db->sql_server_info(true), '4.1.3', '>='))
                      {
                         $convert->mysql_convert = true;
                      }
          

          When the echo statement is displayed on the screen, it displays the destination database version instead of the source database version.
          So, what happens is if phpBB3 is using a database version that is >= 4.1.3 and phpBB2 is using a database that is running a server version of < MySQL 4.1.3, $convert->mysql_convert is being set to true instead of false which means that

          if ($convert->mysql_convert && !$same_db)

          will be true and SET NAMES "BINARY" will be executed and fails because it does not work on database versions older than 4.1.3.

          This points to a problem with either $src_db->sql connecting to the wrong database or a problem with the version_compare function.

          Show
          D¡cky Richard Foote [X] (Inactive) added a comment - The code is correct as it is written and the change you have suggested does work, I have suggested that change several times in the convertors forum, but it only masks the problem. It is not a fix for the problem. You don't need a really old version of MySQL to see where the real problem is. You only need to be able to connect to two databases of different versions. This is the test that I used: case 'mysql4': echo 'Line 688. mysql version is ' . $src_db->sql_server_info(true) . '< br />'; if (version_compare($src_db->sql_server_info(true), '4.1.3', '>=')) { $convert->mysql_convert = true; } When the echo statement is displayed on the screen, it displays the destination database version instead of the source database version. So, what happens is if phpBB3 is using a database version that is >= 4.1.3 and phpBB2 is using a database that is running a server version of < MySQL 4.1.3, $convert->mysql_convert is being set to true instead of false which means that if ($convert->mysql_convert && !$same_db) will be true and SET NAMES "BINARY" will be executed and fails because it does not work on database versions older than 4.1.3. This points to a problem with either $src_db->sql connecting to the wrong database or a problem with the version_compare function.
          Hide
          A_Jelly_Doughnut A_Jelly_Doughnut added a comment -

          I see. The SQL server info is being cached. This will take a bit of work.

          Show
          A_Jelly_Doughnut A_Jelly_Doughnut added a comment - I see. The SQL server info is being cached. This will take a bit of work.
          Hide
          D¡cky Richard Foote [X] (Inactive) added a comment -

          Since this is the one and only time I can see during a conversion that we need the version of the source database, why not create a separate function specifically for it?

          OPEN install/install_convert.php
          FIND

          if (version_compare($src_db->sql_server_info(true), '4.1.3', '>='))

          REPLACE WITH

          if (version_compare($src_db->sql_src_server_info(true), '4.1.3', '>='))

          OPEN includes/db/mysql.php
          FIND

          		return ($raw) ? $this->sql_server_version : 'MySQL ' . $this->sql_server_version;
          	}

          AFTER, ADD

          	/**
          	* function for convertors source database version info
          	* @return string sql source server version
          	*/
          	function sql_src_server_info()
          	{
          		$result = @mysql_query('SELECT VERSION() AS version', $this->db_connect_id);
          		$row = @mysql_fetch_assoc($result);
          		@mysql_free_result($result);
           
          		return $row['version'];
          	}

          Show
          D¡cky Richard Foote [X] (Inactive) added a comment - Since this is the one and only time I can see during a conversion that we need the version of the source database, why not create a separate function specifically for it? OPEN install/install_convert.php FIND if (version_compare($src_db->sql_server_info(true), '4.1.3', '>=')) REPLACE WITH if (version_compare($src_db->sql_src_server_info(true), '4.1.3', '>=')) OPEN includes/db/mysql.php FIND return ($raw) ? $this->sql_server_version : 'MySQL ' . $this->sql_server_version; } AFTER, ADD /** * function for convertors source database version info * @return string sql source server version */ function sql_src_server_info() { $result = @mysql_query('SELECT VERSION() AS version', $this->db_connect_id); $row = @mysql_fetch_assoc($result); @mysql_free_result($result);   return $row['version']; }
          Hide
          bantu Andreas Fischer added a comment -

          Adjusted commit message to contain no more than 80 characters per line.

          Show
          bantu Andreas Fischer added a comment - Adjusted commit message to contain no more than 80 characters per line.

            People

            • Assignee:
              A_Jelly_Doughnut A_Jelly_Doughnut
              Reporter:
              D¡cky Richard Foote [X] (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development