Differences

This shows you the differences between two versions of the page.

--- daily_issues [2017/03/18 20:54] – [MySQL with where clause] k2patel
+++ daily_issues [2020/08/10 02:35] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+==== Mysql Upgrade Issue ====
+==== Easy Command List ====
+== How to check Type of Table ==
+You can use any of the following command to determine table type.
+<code sql>
+SHOW TABLE STATUS WHERE Name = 'table_name';
+OR
+SHOW CREATE TABLE 'table_name';
+OR
+SHOW TABLE STATUS;
+</code>
+===== Mysql Generate hash for password =====
+<code mysql>
+SELECT PASSWORD('mypass');
+</code>
+OR
+<code mysql>
+SELECT OLD_PASSWORD('mypass');
+</code>
+==== MySQL Cluster information gathering ====
+=== How to find which master log slave reading ===
+<code sql>
+cat slaves | while read i ; do echo $i; mysql -h$i -e "SHOW SLAVE STATUS \G" | grep "Relay_Master_Log_File"; done
+</code>
+=== How continue with the next statement in the replication ===
+NOTE : Make sure you run the query manually on slave if it is query issue.
+<code sql>
+STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
+</code>
+==== 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.
+<code text>
+. insert following line to your my.cnf
+     set-variable=character-set-server=UTF8
+. re-import database should fix the issue.
+</code>
+== 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
+<code mysql>
+mysqldump -R -u <username> -p<password> <database>
+</code>
+== 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
+<code text | my.cnf>
+ set-variable=character-set-server=latin1
+</code>
+==== 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\\
+Structure usually created with mysql installation if you don't see could be old structure.\\
+try running //mysql_upgrade//
+<code sql | 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'
+</code>
+You can choose //log_output// to be file OR table OR both.
+<code sql | Variable>
+general_log=1
+log_output=TABLE,FILE
+</code>
+Following command can help you apply changes without restart.
+<code sql | Command >
+SET GLOBAL log_output='TABLE';
+SET GLOBAL general_log='ON';
+</code>
+How to flush log.
+<code sql | Flush log>
+mysql -u root -pp4ssw0rd -e "TRUNCATE TABLE mysql.general_log"
+</code>
+==== mysql dump only partition information ====
+If you were planning to drop a partition, you may need to back it up. find out the range of the partition by
+<code>
+show create table tablename
+</code>
+replace the range fields as follows:
+<code>
+mysqldump -S --no-create-info --where="RANGE(day) >= lowrange AND RANGE(day) < highrange" database_name table_name > /jtmp/table.sql
+</code>
+==== Reading Binlog ====
+Simple command to read binlog is
+<code bash>
+mysqlbinlog mysql-bin.000013
+</code>
+You can use following regularly used options.
+<code bash>
+--start-datetime=
+--start-position=
+--server-id=
+</code>
+==== Can't open file ====
+I've started seeing following error in my log.\\
+<code text>
+[ERROR] /usr/libexec/mysqld: Can't open file:
+</code>
+It is caused by file discriptor limit set by the system.\\
+You can check that by changing your shell to mysql user and running following command.
+<code bash>
+ulimit -a
+</code>
+In order to increase the open file limit.\\
+edit following file.\\
+<code bash /etc/security/limits.conf>
+mysql  soft  nofile  49152
+mysql  hard  nofile  65536
+</code>
+Now you can check mysql for limit seen by mysql.\\
+login to mysql as root and run following command.\\
+<code mysql>
+show GLOBAL VARIABLES LIKE "open%";
+</code>
+Now you can modify it to increased limit in limits.conf.\\
+by editing following file.
+<code bash /etc/my.cnf>
+[mysqld]
+open-files-limit=49152
+[mysqld_safe]
+open-files-limit=49152
+</code>
+restart mysql and check again it should represent new value.
+==== InnoDB: Unable to lock ./ibdata1, error: 11 ====
+I've stumble across this issue while running mariadb server in VM Guest.\\
+I believe this happens sometime when VDP is doing backup or machine is being migrated by DRS.
+In my case, i've enable innodb per tables. So i've to clear lock from all files in subfolder.\\
+In order to fix the issue, following command usually fix the issue.
+<code bash>
+systemctl stop mariadb
+cd /var/lib/mysql
+for i in `find . -name '*.ibd'`; do mv $i $i.bk; cp -a $i.bk $i ; rm $i.bk ; done
+systemctl start mariadb
+</code>