Migrations are essential in the realm of database management to protect against incompatibility, data loss, or corruption. One of our customers recently wanted to upgrade their MySQL 5.6 single server to 5.7 single server and attempted several different server upgrading procedures. Unfortunately, couple of approaches failed, also as lot of migration tools doesn’t support with 5.6 instance, the outcome of the migration resulted in index corruption, performance decline, and function incompatibilities. As a result, several stored procedures that used functions encountered phantom errors and one of the errors is discussed in detail in this article. As we are aware, version 5.6 and version 5.7 of MySQL have major incompatibility issues with various built-in functions that are available in other relational databases like row_number () and one of such functionalities that was fixed in version 8.0.

Therefore, the session variable was used as an alternative to the row_number() function, and its syntax is shown below.

Figure 1:

The output for the above query looks like this:

Figure 2:

As we have seen, the row_number functionality  is bought with the help of  a session variable and it is working  same as row_number function that got fixed in  version 8.0, but the real problem lies elsewhere and is detailed further down.

The customer had built the logic in such a way, that can substitute the select statement’s row_number functionality with a session variable to generate a row number for each row while still ensuring that the query behaves as wanted in both 5.6 and 5.7.

The true issue aroused when we attempt to enter data into the view together with the session variable in the select query, which is intended to operate in version 5.6 but not in version 5.7. Here is a brief demonstration of the above scenario.

Steps to demonstrate the above scenario is as follows:

Step 1: View is created on top of the colours table

Step 2: Insert the records inside the view with the help of select query along with session variable in version 5.6

The above query is working fine in version 5.6 and output is as below.

Step 3: Insert the records inside the view with the help of select query along with session variable in version 5.7

Output for the above query is as below:

Running code in both versions 5.6 and 5.7 led to the discovery that version 5.7 does not permit the insertion of records along with session variables inside the view.

Resolution & Workaround:

Replace view name with the table name in MySQL 5.7 while attempting to insert entries inside the view along with session variable.

The results of the above query are as follows:

As you can see in the code above, we substituted the name of the view(names1) with the name of the table (a.colours). As a result, records are being inserted into the table without any error like “Error Code: 1471. The target table names1 of the INSERT is not insertable-into.” Given that the view’s name is names1, and doing so will help us to correct the above challenge.

Additional Information:

1. The same row number functionality can be achieved using built-in row_num () in 8.0 and later versions of MySQL.
2. In version 8.0, the table name can once more be substituted with the view name because this version supports view insertion along with the session variable.

thejas.krishna