DB encoding lost when reconnecting after error

  • Resolved eddie.ridwan

    (@eddieridwan)


    9 years, 6 months ago

    I encountered the following error when the installer (Duplicator version: 0.5.28) was writing the first line in the installer-data.sql file to the target database:

    **ERROR** database error write 'MySQL server has gone away' - [sql=/* DUPLICATOR MYSQL SCRIPT CREATED ON : 2015-08-18 02:47:41 */

SET FOREIGN...]

    The installer continued to process the rest of the .sql file with no reported errors. However, the resultant website had strange characters, and the theme option settings was lost.

    I traced the problem to the following code in installer.php:

    //Check to make sure the connection is alive
		if (!empty($err)) {

			if (!mysqli_ping($dbh)) {
				mysqli_close($dbh);
				$dbh = DupUtil::db_connect($_POST['dbhost'], $_POST['dbuser'], $_POST['dbpass'], $_POST['dbname'], $_POST['dbport'] );
			}
			DUPX_Log::Info("**ERROR** database error write '{$err}' - [sql=" . substr($sql_result_file_data[$counter], 0, 75) . "...]");
			$dbquery_errs++;

		//Buffer data to browser to keep connection open
		} else {
			if ($fcgi_buffer_count++ > $fcgi_buffer_pool) {
				$fcgi_buffer_count = 0;
				DupUtil::fcgi_flush();
			}
			$dbquery_rows++;
		}

    After reconnecting, the code does not set the session charset, so that the connection uses the server charset, which happens to be ‘latin1’. I managed to resolve the issue by adding the following code after the db_connect line:

    @mysqli_query($dbh, "SET wait_timeout = {$GLOBALS['DB_MAX_TIME']}");
DupUtil::mysql_set_charset($dbh, $_POST['dbcharset'], $_POST['dbcollate']);

    Can you please have a look at this, and to put in a longer term fix to set the appropriate db settings after the reconnection.

    Thanks for a very useful plugin.

    https://www.ads-software.com/plugins/duplicator/

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter eddie.ridwan

    (@eddieridwan)

    I have also identified the reason for why the installer raised the following error when writing the first line in the installer-data.sql file to the target database:

    **ERROR** database error write 'MySQL server has gone away' - [sql=/* DUPLICATOR MYSQL SCRIPT CREATED ON : 2015-08-18 02:47:41 */

SET FOREIGN...]

    It is because the hosting server I am using has a very short default db wait_timeout of only 10 seconds. This means by the time the installer gets to the WRITE DATA section, the db connection has already gone.

    I was able to overcome this problem by setting the session wait_timeout straight after the main db_connect line, as follows:

    //===============================
//ERROR MESSAGES
//===============================
//ERR_MAKELOG
($GLOBALS['LOG_FILE_HANDLE'] != false) or DUPX_Log::Error(ERR_MAKELOG);

//ERR_MYSQLI_SUPPORT
function_exists('mysqli_connect') or DUPX_Log::Error(ERR_MYSQLI_SUPPORT);

//ERR_DBCONNECT
$dbh = DupUtil::db_connect($_POST['dbhost'], $_POST['dbuser'], $_POST['dbpass'], null, $_POST['dbport']);
// ======== added the following line ========
@mysqli_query($dbh, "SET wait_timeout = {$GLOBALS['DB_MAX_TIME']}"); 

($dbh) or DUPX_Log::Error(ERR_DBCONNECT . mysqli_connect_error());
if ($_POST['dbaction'] == 'empty') {
	mysqli_select_db($dbh, $_POST['dbname']) or DUPX_Log::Error(sprintf(ERR_DBCREATE, $_POST['dbname']));
}

    Can you please have a look at this as well, and put in the correct fix for this issue of installing on a server with a very short wait_timeout.

    Thanks.

    Hey Eddie,

    Thanks for the contribution. I have added this forum thread as a back-log item to update. All of the code is also on github if you would like to contribute there as well, it will probably get integrated much quicker that way…

    Thanks~

    Thread Starter eddie.ridwan

    (@eddieridwan)

    G’day Cory. Have raised pull request at https://github.com/lifeinthegrid/duplicator/pull/13.

    Hope you would be able to add to your next update.

    Thanks.

    Sweet… Thanks! I’ll have a look at it…

    Cheers~

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘DB encoding lost when reconnecting after error’ is closed to new replies.