SQL Review | SQLE Compatible with MySQL 8.0 Review

Author: Ren Zhongyu

A member of the DBA team of Aikesheng, good at failure analysis and performance optimization, welcome to discuss technical issues related to articles.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

content

• front
• DDL statement auditing
• DCL statement auditing
• DML statement auditing
• DDL and DML contextual association
• in conclusion

front

SQLE is an extensible SQL audit tool ( https://github.com/actiontech/sqle ) developed and open sourced by AKSON, which supports SQL auditing, standardized online process and other rich functions. Currently, most MySQL businesses use The scenario is mainly based on version 5.7. Today, this article will verify the degree of SQLE's support for MySQL 8.0.

• SQLE version: sqle-ce-1.2203.0
• MySQL version: MySQL 8.0.28
• Environment installation process: omitted

DDL statement auditing

1. Test the create table statement

The audit results are as follows.

• Conclusion

  • There is basically no problem with the audit results, and SQLE will normally throw errors if it does not meet the table building rules and naming conventions.

2. Test the create table statement 2

• There are two SQL examples below, one is syntax error and the other is syntax error (including MySQL reserved keywords).

As shown in the following review results, the second item rank table does not match the expected results, and SQLE should throw out an error.

The normal audit result should throw out errors as shown below.

• Conclusion

  • The reason for the difference in the above audit results is that although order and rank are both reserved keywords of MySQL 8.0, rank is newly added in version 8.0, and the keyword database in the current SQLE audit rules is estimated to need to be updated.
  • Note: Although the rank table creation statement can pass the audit, it will still fail to go online.
    

3. Test alter table to create/modify indexes or columns.

The audit results are as follows.

• Conclusion

  • There is basically no problem with the audit results, and SQLE will normally throw errors if it does not meet the index and field creation rules.

4. Test alter table to disable indexes

• The following two SQL examples, one is to create an index, and the other is to modify the index to an invisible (disabled) state.
  

The results of the audit as shown in the figure below are actually not in line with expectations.

The normal audit result should be ⽆问题 , the execution of this statement in MySQL is shown in the following figure.

• Summary The reason why the audit result does not meet expectations should be that the ALTER TABLE ... ALTER INDEX ... syntax was added in 8.0. Currently, SQLE has not yet added a parser.

DCL statement auditing

DCL statements are rarely reviewed or online in an online environment, but SQLE can still support them.

1. The sample statement of the test set command is as follows.

However, there may also be unaudited commands, as shown in the following figure.

The audit result is shown in the figure below. The audit result of the second sentence is not as expected.

The normal audit result should be ⽆问题 , the execution of this statement in MySQL is shown in the following figure.

• Conclusion

  • The reason why the audit results did not meet expectations should be that the set persist ... syntax was added in 8.0, and SQLE has not yet added a parser.

2. Test user/role authorization statements such as GRANT.

The audit results are as follows.

• Conclusion

  • There is basically no problem with the audit results, and the user and role creation and authorization statements can be correctly identified by SQLE.

DML statement auditing

1. Test the INSERT/UPDATE/DELETE statement as follows.

The audit results are as follows.

• Conclusion

  • There is basically no problem with the audit results, and SQLE will throw errors normally if it does not conform to the DML specification.

    2. Test the select statement as follows.

    
    The audit results are as follows.
    

• Conclusion

  • There is basically no problem with the audit results, and SQLE will normally throw out the problems that do not meet the SQL development specifications.

DDL and DML contextual association

The contextual association between DDL and DML is often seen in the release and online scenario, but there are still many such scenarios in the actual production environment.

1. Test statement one.

The audit results are as follows.

• Conclusion

  • The audit results were as expected, and SQLE was able to identify that the newly added column was associated with the data insertion statement in the text.

2. Test Statement Two (Counter Example)

The audit results are as follows.

• Conclusion

  • The audit results are in line with expectations, and SQLE can identify and flush out the unrelated DML statements.

3. Test statement three

Verify MySQL 8.0 JSON related features

The audit results are as follows.

The statement is normally executed in MySQL as shown in the figure below.

• Conclusion

  • Except for the new JSON features of MySQL 8.0 (such as the JSON_TABLE method), other JSON syntax is the same as MySQL 5.7, and SQLE still supports it.

in conclusion

• This article tests the degree of SQLE's support for MySQL 8.0.28. According to the test results, most DDL, DML and DCL statements are the same as MySQL 5.7 and can be audited normally, and DML and DDL context associations can be correctly identified .

• The SQL involved in a few new features of MySQL 8.0 may not be compatible with SQLE at present, such as

  • When creating a table, there are new reserved words in MySQL 8.0, such as RANK and ROW_NUMBER related to window functions.
  • ALTER TABLE ... ALTER INDEX ... Syntax
  • set persist ... Syntax
  • JSON_TABLE function etc.
• In short, the flaws do not hide the flaws. After all, the incompatible points are rarely used in the daily SQL development review and launch. I believe that subsequent SQLE versions will be optimized.