Table creation fails when database charset is non-UTF8

When you are providing pre-create database for installation make sure to set the database default charset to utf8 or compatible.

Status changed to closed

Issue through which this was tracked - see notes

You can alter table charset with following command:

ALTER DATABASE DB_NAME CHARACTER SET utf8 COLLATE utf8_general_ci;

@prasad well this is a pain because normal user installing vtiger don´t know this and it´s not supposed to know.

This is just a workaround and not a solution.

Status changed to reopened

mentioned in issue #740 (closed)

Status changed to closed by commit 94dec625

@prasad why not force tables to be created in UTF8? Is not what install process is supposed to do white tables creation?

I test many systems and some of them even installation through softaculous or installatron and they don´t seem to require any changes before installation.

Sorry but the system must handle this by himself.

mentioned in issue #664 (closed)

@manuelgit I agree with you. Vtiger should do a check before installing/update. In my case I have seen this in multiple cases when going from 6.5 to 7.0. It seems the problem is a mix of tables using latin1 and utf8 as charset. I don't know how that comes to be.

@uma.s is there a chance we might reopen this?

It happened to me many times too, to solve a vtiger 6.x problem by changing character set to UTF8, it should be default for 7.4 upgrade or clean install.

I have a bit of code I run on all migrations now before running the main vtiger migration scripts. It changes all non *_seq tables to InnoDB and sets every table to utf8 character set and collation to utf8_general_ci:

// Change any MyISAM tables to InnoDB
$getMyISAMTablesQuery = "
    SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
    FROM    information_schema.tables AS tb
    WHERE   table_schema = ?
    AND     `ENGINE` = 'MyISAM'
    AND     `TABLE_TYPE` = 'BASE TABLE'
    AND     `table_name` NOT LIKE '%_seq'
    ORDER BY table_name DESC";
$MyISAMresult = $db->pquery($getMyISAMTablesQuery, array($dbconfig['db_name']));

if($db->num_rows($MyISAMresult) > 0) {
    $fixTableQuery = "ALTER TABLE ? ENGINE=InnoDB";
    echo "No. of rows found: " . $db->num_rows($MyISAMresult) . PHP_EOL;
    while($row = $db->fetch_array($MyISAMresult)) {
        echo "Running " . $row['sql_statements'] . " ..." . PHP_EOL;
        $db->query($row['sql_statements']);
    }
}

echo "Change any non-utf8 collations ..." . PHP_EOL;
$getCollationTablesQuery = "
    SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS sql_statements
    FROM information_schema.tables AS tb
    WHERE `TABLE_SCHEMA` = ?
    AND `TABLE_TYPE` = 'BASE TABLE'
    AND `TABLE_COLLATION` != 'utf8_general_ci'
    ORDER BY `TABLE_NAME` DESC";
$CollationResult = $db->pquery($getCollationTablesQuery, array($dbconfig['db_name']));

if($db->num_rows($CollationResult) > 0) {
    echo "No. of rows found: " . $db->num_rows($CollationResult) . PHP_EOL;
    while($row = $db->fetch_array($CollationResult)) {
        echo "Running " . $row['sql_statements'] . " ..." . PHP_EOL;
        $db->query($row['sql_statements']);
    }
}

FWIW, I also have a bit of code which attempts to find all "orphan" entity records (those with no matching vtiger_crmentity, or _cf, or no main table) and remove them. When you do not do this, many of the later migration scripts in v7 silently fail when setting up foreign key constraints and some of the user custom view tables never get created. But I can't show this as I am not 100% sure of its reliability yet.

Thanks for sharing @lord_alan

@uma.s is it posible to include alan's code in the migration scripts. However, we would have to add it to the 6.5->7.0 migration script, right? Otherwise I think it wouldn't make a lot of sense.

Alan, maybe you should share the other script as well. Just to have the idea of how it works and maybe improve it.

One last question. You said all non *_seq tables. Why is that? What's special about those?

One last question. You said all non *_seq tables. Why is that? What's special about those?

I think I remember reading somewhere, ages ago, that they either do not need to, or should not be InnoDB. I could be wrong ;-)

As for the orphan rows code - I am very reticent about sharing that because it has the potential to be very destructive... Until I have had a chance to really test it thoroughly I intend to not share it. If you think about it, it isn't that difficult...

@lord_alan fair enough. Thanks a lot for the other code. I'll be sure to use it in my next migration.

@lord_alan I used your script today. There was an error in the workflows module where new workflows could not be created. After taking a look at the logs, there were query errors due to mixed character sets across tables.

I've added a few lines to the script to be able to run it:

<?php 

require_once 'vtlib/Vtiger/Module.php';
require_once 'include/utils/VtlibUtils.php';
require_once 'config.inc.php';
require_once 'includes/Loader.php';
require_once 'modules/Emails/mail.php';
require_once 'vtlib/Vtiger/Mailer.php';
require_once 'libraries/ToAscii/ToAscii.php';
vimport ('includes.runtime.EntryPoint');

//set admin as current user
$current_user = Users::getActiveAdminUser();

$db = PearDatabase::getInstance();

// Change any MyISAM tables to InnoDB
$getMyISAMTablesQuery = "
    SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
    FROM    information_schema.tables AS tb
    WHERE   table_schema = ?
    AND     `ENGINE` = 'MyISAM'
    AND     `TABLE_TYPE` = 'BASE TABLE'
    AND     `table_name` NOT LIKE '%_seq'
    ORDER BY table_name DESC";
$MyISAMresult = $db->pquery($getMyISAMTablesQuery, array($dbconfig['db_name']));

if($db->num_rows($MyISAMresult) > 0) {
    $fixTableQuery = "ALTER TABLE ? ENGINE=InnoDB";
    echo "No. of rows found: " . $db->num_rows($MyISAMresult) . PHP_EOL;
    while($row = $db->fetch_array($MyISAMresult)) {
        echo "Running " . $row['sql_statements'] . " ..." . PHP_EOL;
        $db->query($row['sql_statements']);
    }
}

echo "Change any non-utf8 collations ..." . PHP_EOL;
$getCollationTablesQuery = "
    SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS sql_statements
    FROM information_schema.tables AS tb
    WHERE `TABLE_SCHEMA` = ?
    AND `TABLE_TYPE` = 'BASE TABLE'
    AND `TABLE_COLLATION` != 'utf8_general_ci'
    ORDER BY `TABLE_NAME` DESC";
$CollationResult = $db->pquery($getCollationTablesQuery, array($dbconfig['db_name']));

if($db->num_rows($CollationResult) > 0) {
    echo "No. of rows found: " . $db->num_rows($CollationResult) . PHP_EOL;
    while($row = $db->fetch_array($CollationResult)) {
        echo "Running " . $row['sql_statements'] . " ..." . PHP_EOL;
        $db->query($row['sql_statements']);
    }
}

I believe this should at least go to the wiki.

@ruben.estrada If you are running this from the command line, my particular chuck of code, doesn't need all those includes...

require_once('config.php');
require_once('includes/main/WebUI.php');

Should be sufficient.

Thanks for the tip!

Table creation fails when database charset is non-UTF8

Designs

Child items 0

Activity

Admin message

Table creation fails when database charset is non-UTF8

Activity