Issues with Using pt-online-schema-change on TencentDB for MySQL
TencentDB for MySQL has supported Online DDL since version 5.6. For schema changes in version 5.5, to avoid business impact caused by table locking, users are still advised to use open-source tools such as pt-online-schema-change. However, many users encounter issues when using pt-online-schema-change through CVM to modify MySQL table structures.
Common error message:
Use of uninitialized value $host in string eq at /usr/local/percona-toolkit-3.0.3/bin/pt-online-schema-change line 4284.
View the corresponding source code:
sub _find_slaves_by_processlist {
my ( $self, $dsn_parser, $dbh, $dsn ) = @_;
my @slaves = map {
my $slave = $dsn_parser->parse("h=$_", $dsn);
$slave->{source} = 'processlist';
$slave;
}
grep { $_ }
map {
my ( $host ) = $_->{host} =~ m/^([^:]+):/;
if ( $host eq 'localhost' ) {
$host = '127.0.0.1';
}
$host;
} $self->get_connected_slaves($dbh);
return @slaves;
}
The code attempts to find replica node information through the processlist. However, because TencentDB has processed replication account-related information, the replica node information cannot be obtained through the processlist.
Fix:
When using pt-osc, add the following parameters to skip checking the replica node status.
--recursion-method=none
TencentDB Data Import Error: Specified key was too long
Cause:
When a customer imports an XXXX.sql file into MySQL through the CVM command line, MySQL returns the error "Specified key was too long".
The error message "ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes" means that the index field length is too long and exceeds 767 bytes.
For the innodb storage engine, the length limits for multi-column indexes are as follows:
The length of each column cannot exceed 767 bytes, and the total length of all indexed columns cannot exceed 3072 bytes.
For the myisam storage engine, the length limits for multi-column indexes are as follows:
The length of each column cannot exceed 1000 bytes, and the total length of all indexed columns cannot exceed 1000 bytes.
Note:
768 / 2 = 384 double-byte fields, or 767 / 3 = 255 three-byte fields (GBK is double-byte, UTF8 is three-byte, and UTF8MB4 is four-byte).
In MySQL 5.6 and later, all myisam tables are automatically converted to innodb. Therefore, if a composite index column exceeds 767 bytes on a self-built database that uses the myisam storage engine, the same table creation statement runs without issues on the self-built database but fails on MySQL 5.6 and later.
Solution:
1. Modify the length of the composite index column in the erroneous row of the backup file.
Common examples:
create table test(test varchar(255) primary key)charset=utf8;
-- Success
create table test(test varchar(256) primary key)charset=utf8;
-- Failure
ERROR 1071(42000): Specified key was too long; max key length is 767 bytes
2. When TencentDB 5.5 is used, the myisam engine is not automatically converted to innodb.
Why Does "select * from XX into outfile xxxx" Report an Error?
For platform security reasons, enabling the file privilege and exporting data by using select into outfile are not supported. You are advised to use other methods to export data.
How to Fix Garbled emoji Characters When Inserting into a MySQL Database?
You need to check whether the MySQL instance, the client, and the connection to the MySQL instance consistently use or support the utf8mb4 character set.
To store emoji in a MySQL instance, the MySQL instance, the client, and the connection to the MySQL instance must all consistently use or support the utf8mb4 character set.
1. First, set the character set of the MySQL instance to utf8mb4. You can modify the character_set_server parameter by logging in to the console.
Attention:
Modifying this parameter will restart the database. You are advised to back up the database in advance to avoid unnecessary losses.
2. The client application must ensure that the character set of the output strings is utf8mb4.
3. When creating a connection, the application needs to specify a character set. The following uses a common jdbc connection as an example:
4. For jdbc connections, use MySQL Connector/J 5.1.13 or later. The following is a sample code:
String query = "set names utf8mb4";
stat.execute(query);