Q&A

Syntax

Yearning is currently compatible with 99% of Mysql standard SQL syntax.

But due to the constraints of audit logic and other factors, some SQL statements are not guaranteed to get correct feedback

Known unsupported statement types are:

• Complex query statements (multi-table, multi-function, not necessarily present. Not all complex statements are not supported)
• Foreign key
• Rollback of DML statements across database is not supported

Affected rows

Yearning uses Explain to analyze the number of rows affected by SQL statements. Because the number of rows generated by Explain is affected by other factors such as primary keys, indexes, etc., its accuracy cannot be guaranteed 100%. It more reflects the number of full table scans caused by this statement, so it has certain optimization reference significance for statements with large errors.

Pre-check

Yearning uses a pre-check method for SQL testing.For statements added and edited in the same work order, the field does not exist.

For example

ALTER TABLE core_data_sources ADD t1 int( 11) DEFAULT 0 COMMENT 'a';
ALTER TABLE core_data_sources ADD t2 int( 11) DEFAULT 0 COMMENT 'b' AFTER t1;

The second statement of the ticket relies on the fields created by the first statement. Because the current database does not have the t1 field, the problem that the t1 field does not exist will be reported when the work order is detected.

The statement itself is no problem , because it is executed and detected one by one in the mysql execution. When this field is generated in the table after the first item is executed, the detection before the second item is naturally normal.

Use osc

Yearning uses the pt function by calling pt-osc with exec. The administrator needs to fill in the relevant pt-osc configuration in the rule setting page and turn on the osc use switch. It should be noted that DDL statements using pt-osc must not contain such writing as dbname.tablename! Please comply with pt-osc notation!

Generate rollback SQL

Yearning uses the method of parsing binlog to obtain the rollback statement. Due to the limitation of the Mysql replication protocol, an account with SUPER / REPLICATION SLAVE, REPLICATION CLIENT, ALL PRIVILEGES privileges must be available to obtain the binlog stream. Therefore, if you want to experience the function of generating the rollback statement backup, please ensure that the corresponding data source account has the corresponding permissions, and the corresponding data source should enable the binlog function.