Mysql Development --- get 01 on a daily basis, mysql --- 01

Mysql Development --- get 01 on a daily basis, mysql --- 01

July 7, 2015 -------------------------

1. The truncate table clears the auto_increment value of the table creation statement. the id of a table is the primary key and auto-increment. You can choose to insert any id value. If the value is not inserted from 1, when insert starts from 3 and then insert a value without id, the auto-increment value is 4.

2. Check the index size of each engine to optimize database parameters.
Select engine,
ROUND (SUM (data_length)/1024/1024, 1) AS "Data MB ",
ROUND (SUM (index_length)/1024/1024, 1) AS "Index MB ",
ROUND (SUM (data_length + index_length)/1024/1024, 1) AS "Total MB ",
COUNT (*) "Num Tables"
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema not in ("information_schema", "cece_schema ")
Group by engine;

3. When using prepare stmt from to prepare a dynamic SQL statement
(1) The prepared statement must be a session-level variable instead of a local variable. You must add @ to define the variable. The prepared statement will not be lost until the session ends, deallocate prepare stmt can be used to eliminate the allocation statement.
The table name is unknown. Check the maximum table id and insert 10 rows of data starting from id + 1.
BEGIN
-- In a stored procedure, values can be a variable in a general SQL statement, but the table name and field name cannot be a variable.
Declare v_xname varchar (20) default 'testincre1 ';
Delete from test. testincre1 where id = 1;
Select ifnull (max (id), 0) + 1 into @ increfrom test. testincre1;
Set @ end = @ incret + 10;
Repeat
Set @ SQL = concat ('insert into test. ', v_xname, 'values (@ incret, ''yangsq '', now ());');
Select @ SQL;
Prepare stmt from @ SQL;
Execute stmt;
Deallocate prepare stmt;
Set @ incret = @ incret + 1;
Until @ inre = @ end repeat;
END
4. SQL _slave_skip_counter
Last_ SQL _Error: Error 'unknown table 'sakila. testrepldb'' on query. Default database: 'sakila'. Query: 'drop TABLE 'testrepldb'/* generated by server */'
Mysql> start slave SQL _thread; error: the SQL statement that causes the error will be executed repeatedly, but the io_thread still accepts the SQL statement normally.
10:42:25 12378 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
10:42:25 12378 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000012' at position 4449, relay log'./yaolansvr_slave01-relay-bin.000014 'position: 283
10:42:25 12378 [ERROR] Slave SQL: Error 'unknown table' sakila. testrepldb ''on query. default database: 'sakila '. query: 'drop TABLE 'testrepldb'/* generated by server */', Error_code: 1051
10:42:25 12378 [Warning] Slave: Unknown table 'sakila. testrepldb' Error_code: 1051
10:42:25 12378 [ERROR] Error running query, slave SQL thread aborted. fix the problem, and restart the slave SQL thread with "SLAVE START ". we stopped at log 'mysql-bin.000012' position 4449

Select @ SQL _slave_skip_counter;
Stop slave; -- or stop slave SQL _thread
Set global SQL _slave_skip_counter = 1;
Start slave;

-- Log-error:
10:53:30 12378 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
10:53:30 12378 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000012' at position 4449, relay log'./yaolansvr_slave01-relay-bin.000014 'position: 283
10:53:30 12378 [Note] 'SQL _ SLAVE_SKIP_COUNTER = 1' executed at relay_log_file = '. /yaolansvr_slave01-relay-bin.000014 ', relay_log_pos = '000000', master_log_name = 'mysql-bin.000012', master_log_pos = '000000' and new position at relay_log_file = '. /yaolansvr_slave01-relay-bin.000014 ', relay_log_pos = '000000', master_log_name = 'mysql-bin.000012', master_log_pos = '000000'

5. An error occurred while querying mysql from sqlserver when the data type dbtype_dbtimestamp was converted to datetime.
In a mysql table, the datetime type data is 0028-01-01 00:00:00. When datetime is inserted, an error is returned. sqlserver datime supports the date type range: January 1 ,.

6. Comparison of replicate Parameters
-- Replicate-do-table: No like pattern function. Multiple tables must be specified multiple times.
-- Replicate-wild-do-table: Use the like pattern Function Example: -- replicate-wild-do-table = foo %. bar % replicates only updates that use a table where the database name starts with foo and the table name starts with bar

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.