==== 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>
  1. insert following line to your my.cnf
     set-variable=character-set-server=UTF8
  2. 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>