Shulou(Shulou.com)05/31 Report--

This article will explain in detail what you should pay attention to when upgrading to the MySQL5.7 version. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Problems to pay attention to when upgrading to MySQL5.7 version

1) insert a negative value in an unsigned column

Create a table with unsigned columns: CREATE TABLE test (id int unsigned)

Insert a negative value.

Previous version: INSERT INTO test VALUES (- 1)

Query OK, 1 row affected, 1 warning (0.01 sec)

MySQL version 5.7:

INSERT INTO test VALUES (- 1)

ERROR 1264 (22003): Out of range value for column 'a'at row 1

2) divided by zero

Create a test table: CREATE TABLE test2 (id int unsigned)

Try to divide by zero.

Previous version: INSERT INTO test2 VALUES (0amp 0)

Query OK, 1 row affected (0.01sec)

MySQL version 5. 7: INSERT INTO test2 VALUES (0 Compact 0)

ERROR 1365 (22012): Division by 0

3) insert a 20-character string into a 10-character column

Create a table with 10-character columns: CREATE TABLE test3 (a varchar (10))

Try to insert a longer string.

Previous version: INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz')

Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL version 5.7: INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz')

ERROR 1406 (22001): Data too long for column 'a'at row 1

4) insert a non-standard zero date into the date-time column

Create a table with a datetime column: CREATE TABLE test3 (a datetime)

Insert 0000-0000: 00:00.

Previous version: INSERT INTO test3 VALUES ('0000-00-0000: 00')

Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL version 5.7: INSERT INTO test3 VALUES ('0000-00-0000: 00')

ERROR 1292 (22007): Incorrect datetime value: '0000-00-0000: 00 for column' at row 1

5) use GROUP BY and select an ambiguous column

The description when this happens is not part of the GROUP BY, and no aggregate function (such as MIN or MAX) is imposed on it.

Previous versions: SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id

+-- +

| | id | invoice_id | description | |

+-- +

| | 1 | 1 | New socks |

| | 3 | 2 | Shoes |

| | 5 | 3 | Tie |

+-- +

3 rows in set (0.00 sec)

MySQL version 5.7: SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id

ERROR 1055 (42000): Expression # 3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'invoice_line_items.description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

5) mysql5.7 version SQL_MODE

By default, MySQL 5.7 includes the following modes:

* ONLY_FULL_GROUP_BY

* STRICT_TRANS_TABLES

* NO_ENGINE_SUBSTITUTION

* NO_AUTO_CREATE_USER

The mode STRICT_TRANS_TABLES has also become more stringent. In mysql 5.7, strict mode is used by default, which can sometimes cause problems.

For example:

Mysql > CREATE TABLE `events_ t` (

-> `id` int (11) NOT NULL AUTO_INCREMENT

-> `event_ date` datetime NOT NULL

-> `profile_ id` int (11) DEFAULT NULL

-> PRIMARY KEY (`id`)

-> KEY `event_ date` (`event_ date`)

-> KEY `profile_ id` (`profile_ id`)

->) ENGINE=InnoDB DEFAULT CHARSET=latin1

->

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into events_t (profile_id) values (1)

ERROR 1364 (HY000): Field 'event_date' doesn't have a default value

There must be a default value for the date here, not NULL, but:

Mysql > alter table events_t change event_date event_date datetime NOT NULL default '0000-00-0000: 00VOV 00'

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

Mysql > alter table events_t change event_date event_date datetime NOT NULL default '2000-00-0000GRV 0000'

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

It doesn't work either. There must be a real date by default.

Mysql > alter table events_t change event_date event_date datetime NOT NULL default '2000-01-01 00VG 0000'

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > insert into events_t (profile_id) values (1)

Query OK, 1 row affected (0.00 sec)

So only the date format is changed to timestamp, either with NULL, or with now () when insert.

6) mysql-connector-java.Jar

Table 2.1 Summary of Connector/J Versions

Connector/J version

Driver Type

JDBC version

MySQL Server version

Status

5.1

four

3.0, 4.0, 4.1, 4.2

4.1, 5.0, 5.1, 5.5, 5.6, 5.7

Recommended version

5.0

four

3.0

4.1, 5.0

Released version

3.1

four

3.0

4.1, 5.0

Obsolete

3.0

four

3.0

3.x, 4.1

Obsolete

Table 26.2 MySQL Connector Versions and MySQL Server Versions

Connector

Connector version

MySQL Server version

Connector/C

6.1.0 GA

5.6, 5.5, 5.1, 5.0, 4.1

Connector/C++

1.0.5 GA

5.6, 5.5, 5.1

Connector/J

5.1.8

5.6, 5.5, 5.1, 5.0, 4.1

Connector/Net

6.5

5.6, 5.5, 5.1, 5.0

Connector/Net

6.4

5.6, 5.5, 5.1, 5.0

Connector/Net

6.3

5.6, 5.5, 5.1, 5.0

Connector/Net

6.2 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

6.1 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

6.0 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

5.2 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

1.0 (No longer supported)

5.0, 4.0

Connector/ODBC

5.1

5.6, 5.5, 5.1, 5.0, 4.1.1 +

Connector/ODBC

3.51 (Unicode not supported)

5.6, 5.5, 5.1, 5.0, 4.1

Connector/Python

2.0

5.7, 5.6, 5.5

Connector/Python

1.2

5.7, 5.6, 5.5

This is the end of this article on "what are the problems you should pay attention to when upgrading to the MySQL5.7 version?" I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.