dbd-mysql-4.011.tar.gz下载_技术分享 | MariaDB 10.1.9 迁移到 MySQL 5.7.25

//首先查看我们导入备份后的库表，可以看到MariaDB上的test库以及sysbench库都已经成功导入

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| sysbench           |

| test               |

+--------------------+

6 rows in set (0.00 sec)

mysql> use sysbench

Database changed

mysql> show tables;

+--------------------+

| Tables_in_sysbench |

+--------------------+

| sbtest1            |

+--------------------+

1 row in set (0.00 sec)

mysql> select k from sbtest1 where id=1;

+------+

| k    |

+------+

| 9974 |

+------+

1 row in set (0.01 sec)

mysql>

//然后根据报错，查看mysql.proc这张损坏的表

mysql> use mysql

Database changed

mysql> select * from proc limit 1\G

*************************** 1. row ***************************

db: test

name: AddGeometryColumn

type: PROCEDURE

specific_name: AddGeometryColumn

language: SQL

sql_data_access: CONTAINS_SQL

is_deterministic: NO

security_type: DEFINER

param_list: catalog varchar(64), t_schema varchar(64),

t_name varchar(64), geometry_column varchar(64), t_srid int

returns:

body: begin

set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end

definer: @

created: 2020-05-24 11:40:41

modified: 2020-05-24 11:40:41

sql_mode:

comment:

character_set_client: utf8

collation_connection: utf8_general_ci

db_collation: utf8_general_ci

body_utf8: begin

set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end

1 row in set (0.00 sec)

mysql>

##看起来似乎是正常的，不过这张表是关于存储过程的，那我们创建存储过程看下

mysql> use sysbench

Database changed

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)

->        BEGIN

->          SELECT COUNT(*) INTO cities FROM world.city

->          WHERE CountryCode = country;

->        END//

ERROR 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted

mysql>

## 可以看到创建存储过程是报错的，所以这张表还是有问题的。//接下来我们对比下MariaDB 10.1.9与正常MySQL5.7.25的这张表的表结构

--MariaDB 10.1.9

MariaDB [(none)]> show create table mysql.proc\G

*************************** 1. row ***************************

Table: proc

Create Table: CREATE TABLE `proc` (

`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`name` char(64) NOT NULL DEFAULT '',

`type` enum('FUNCTION','PROCEDURE') NOT NULL,

`specific_name` char(64) NOT NULL DEFAULT '',

`language` enum('SQL') NOT NULL DEFAULT 'SQL',

`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',

`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',

`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

`param_list` blob NOT NULL,

`returns` longblob NOT NULL,

`body` longblob NOT NULL,

`definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',

`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`body_utf8` longblob,

PRIMARY KEY (`db`,`name`,`type`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

1 row in set (0.00 sec)

MariaDB [(none)]>

--MySQL5.7.25(需要另外找一个正常的数据库)

mysql> show create table mysql.proc\G

*************************** 1. row ***************************

Table: proc

Create Table: CREATE TABLE `proc` (

`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`name` char(64) NOT NULL DEFAULT '',

`type` enum('FUNCTION','PROCEDURE') NOT NULL,

`specific_name` char(64) NOT NULL DEFAULT '',

`language` enum('SQL') NOT NULL DEFAULT 'SQL',

`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',

`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',

`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

`param_list` blob NOT NULL,

`returns` longblob NOT NULL,

`body` longblob NOT NULL,

`definer` char(93) DEFAULT NULL,

`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT NULL,

`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`body_utf8` longblob,

PRIMARY KEY (`db`,`name`,`type`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

1 row in set (0.01 sec)

mysql>

##通过对比表结构发现，MySQL5.7.25的proc表的'definer'字段长度只有93，而MariaDB 10.1.9的该字段长度是141；此外，'sql_mode'字段的取值范围也不相同

//接下来就是把导入备份后损坏的proc表的表结构修改正确

mysql> alter table proc modify column definer char(93);

ERROR 1067 (42000): Invalid default value for 'modified'

mysql>

##此时又遇到报错，查看报错字段'modified'发现，该字段是个timestamp 类型，而且默认值是'0000-00-00 00:00:00'，我们知道MySQL5.7版本的sql_mode可能会限制日期全为0的值，那么我们可以在会话级别修改sql_mode值，允许插入全为0的日期

mysql> show variables like '%sql_mode%';

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value                                                                                                                                     |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> set @@session.sql_mode='';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table proc modify column definer char(93);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table proc modify column sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql>

//接下来，再次创建存储过程，发现可以成功创建了

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)

->        BEGIN

->          SELECT COUNT(*) INTO cities FROM world.city

->          WHERE CountryCode = country;

->        END//

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;