Try to convert database from sqlite3 to MySQL

lucam · August 3, 2022, 2:07pm

Hi,

I try to convert the database from sqlite3 to MySQL (hosted on Unraid in a docker container).
Nextcloud is running on Unraid in a container, too.
My data is saved in specific folders on Unraid. Connected through external storage (local).
But I don’t get it to work:

That’s the output of the console:

Creating schema in new database
oc_accounts
 3/3 [============================] 100%oc_accounts_data
 18/18 [============================] 100%oc_activity
chunked query, 16 chunks
  1300/15125 [==>-------------------------]   8%
In ExceptionConverter.php line 114:
                                                                                                                             
  An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x8E  
  \xBF' for column 'file' at row 1                                                                                           
                                                                                                                             

In Exception.php line 26:
                                                                                                              
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x8E\xBF' for column 'file' at row 1  
                                                                                                              

In Statement.php line 82:
                                                                                                              
  SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x8E\xBF' for column 'file' at row 1  
                                                                                                              

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

Thank you in advance.

SysKeeper · August 3, 2022, 2:59pm

This looks like MySQL is using a character set which does not support 4 byte UTF 8. Take a look how the database was created and check for utf8mb4.

lucam · August 3, 2022, 3:10pm

Thank you for your answer.

I deleted the whole stuff in the database and let nextcloud create it for itself.
But it sets the format to utf8mb3_bin by itself.

How can I change this?

SysKeeper · August 3, 2022, 4:40pm

Take a look at Configuration Parameters — Nextcloud latest Administration Manual latest documentation there are some requirements to meet for utf8mb4 during the installation

SysKeeper · August 3, 2022, 4:45pm

For reference, here are the corresponding code parts

github.com

nextcloud/server/blob/33148016a52b6f0006deb70e520492230f0f1880/lib/private/Setup/MySQL.php#L47


      
          
          
class MySQL extends AbstractDatabase {
          	public $dbprettyname = 'MySQL/MariaDB';
          
          
	public function setupDatabase($username) {
          		//check if the database user has admin right
          		$connection = $this->connect(['dbname' => null]);
          
          
		// detect mb4
          		$tools = new MySqlTools();
          		if ($tools->supports4ByteCharset(new ConnectionAdapter($connection))) {
          			$this->config->setValue('mysql.utf8mb4', true);
          			$connection = $this->connect(['dbname' => null]);
          		}
          
          
		$this->createSpecificUser($username, new ConnectionAdapter($connection));
          
          
		//create the database
          		$this->createDatabase($connection);
          
          
		//fill the database if needed

github.com

nextcloud/server/blob/33148016a52b6f0006deb70e520492230f0f1880/lib/private/Setup/MySQL.php#L82


      
          	}
          
          
	/**
          	 * @param \OC\DB\Connection $connection
          	 */
          	private function createDatabase($connection) {
          		try {
          			$name = $this->dbName;
          			$user = $this->dbUser;
          			//we can't use OC_DB functions here because we need to connect as the administrative user.
          			$characterSet = $this->config->getValue('mysql.utf8mb4', false) ? 'utf8mb4' : 'utf8';
          			$query = "CREATE DATABASE IF NOT EXISTS `$name` CHARACTER SET $characterSet COLLATE {$characterSet}_bin;";
          			$connection->executeUpdate($query);
          		} catch (\Exception $ex) {
          			$this->logger->error('Database creation failed.', [
          				'exception' => $ex,
          				'app' => 'mysql.setup',
          			]);
          			return;
          		}

github.com

nextcloud/server/blob/0447b53bda9fe95ea0cbed765aa332584605d652/lib/private/DB/MySqlTools.php#L37


      
          
          
/**
           * Various MySQL specific helper functions.
           */
          class MySqlTools {
          
          
	/**
          	 * @param IDBConnection $connection
          	 * @return bool
          	 */
          	public function supports4ByteCharset(IDBConnection $connection) {
          		$variables = ['innodb_file_per_table' => 'ON'];
          		if (!$this->isMariaDBWithLargePrefix($connection)) {
          			$variables['innodb_file_format'] = 'Barracuda';
          			$variables['innodb_large_prefix'] = 'ON';
          		}
          
          
		foreach ($variables as $var => $val) {
          			$result = $connection->executeQuery("SHOW VARIABLES LIKE '$var'");
          			$row = $result->fetch();
          			$result->closeCursor();