User Tools

Site Tools


daily_issues

This is an old revision of the document!


Mysql Upgrade Issue

Easy Command List

How to check Type of Table

You can use any of the following command to determine table type.

SHOW TABLE STATUS WHERE Name = 'table_name';
OR
SHOW CREATE TABLE 'table_name';
OR
SHOW TABLE STATUS;

MySQL Cluster information gathering

How to find which master log slave reading

cat slaves | while READ i ; do echo $i; mysql -h$i -e "SHOW SLAVE STATUS \G" | grep "Relay_Master_Log_File"; done

How continue with the next statement in the replication

NOTE : Make sure you run the query manually on slave if it is query issue.

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Issue / Solutions

 appeared after upgrade

This issue is appeared due to the default character set.
As Both version of database diff. character set. To get rid of that character try to following steps.

  1. insert following line to your my.cnf
     set-variable=character-set-server=UTF8
  2. re-import database should fix the issue.
mysqldump does not save stored procedure / functions / triggers.

By default mysqldump only store triggers not procedures/functions.

In order to include to your mysqldump there is switch
-R, –routines Dump stored routines (functions and procedures).

In order to include triggers to your mysqldump there is switch
–triggers Dump triggers for each dumped table

mysqldump -R -u <username> -p<password> <database>
After upgrade from 4.0 to 4.1 see issue on charset

Use following line in your my.cnf will resolve the issue. as latin1 is default character set in 4.0 and 4.1 has utf8

| my.cnf
 set-variable=character-set-server=latin1

General Log

It log each and every query.
You can enable it in TABLE or FILE.

Following method shows how to enable it in table without restarting,
Also same you can apply in my.cnf

| Table Structure
CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext,
`thread_id` int(11) DEFAULT NULL,
`server_id` int(11) DEFAULT NULL,
`command_type` varchar(64) DEFAULT NULL,
`argument` mediumtext
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

You can choose log_output to be file OR table OR both.

| Variable
general_log=1
log_output=TABLE,FILE
| Command
SET GLOBAL log_output='TABLE';
SET GLOBAL general_log='ON';
daily_issues.1302745397.txt.gz · Last modified: 2020/08/10 02:30 (external edit)