Shantanu's Blog

There are times when my stored procedure fails with this error:

mysql> call PROC_DBD_EVENTS;

ERROR 1270 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT) for operation 'case'

1) The work-around is to modify the proc table like this...

mysql> select db,name,character_set_client,collation_connection from mysql.proc where name='PROC_DBD_EVENTS' ;
+-----------+-----------------------------+----------------------+----------------------+
| db | name | character_set_client | collation_connection |
+-----------+-----------------------------+----------------------+----------------------+
| upsrtcVTS | PROC_DBD_EVENTS | utf8 | utf8_general_ci |
+-----------+-----------------------------+----------------------+----------------------+

update mysql.proc set character_set_client='latin1', collation_connection='latin1_swedish_ci' where name= "PROC_DBD_EVENTS";

2) But officially supported workaround should be (re)creating the procedure using latin1 character set: E.g. in MySQL command line client:

set names latin1;
CREATE DEFINER= ... PROCEDURE ...

3) In Java application you should not use utf8 in connection string, (when procedure is created), and use Cp1252 instead, e.g.:

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=Cp1252

If you want to copy the data from one server for e.g. (63) to another (64):

1) Stop MySQL service on both servers, for e.g. 10.10.10.63 and 10.10.10.64
2) Go to /var/lib/mysql/ directory on both servers.
cd /var/lib/mysql/

3) On 10.10.10.63
tar -cf - * | nc -l 1234

4) On 10.10.10.64
nc 10.10.10.63 1234 | tar xf -

Restart MySQL service on both the servers and you should get exactly the same data on 64 as you see on 63 (assuming you have the same my.cnf config). This needs to be done very carefully or else data may corrupt.

Here are 3 easy steps to enable mysql failed query log.
1) Download packetbeat config file
2) Edit config file to add "send_response" parameter
3) Start docker container

# download packetbeat config file
curl -L -O https://raw.githubusercontent.com/elastic/beats/7.0/deploy/docker/packetbeat.docker.yml

# add send_response parameter to mysql
packetbeat.protocols.mysql:
  ports: [3306]
  send_response: true

# start docker container
docker run \
  --user=packetbeat \
  --volume="$(pwd)/packetbeat.docker.yml:/usr/share/packetbeat/packetbeat.yml:ro" \
  --cap-add="NET_RAW" \
  --cap-add="NET_ADMIN" \
  --network=host \
  -d docker.elastic.co/beats/packetbeat:7.0.0 \
  --strict.perms=false -e \
  -E cloud.id=XXX \
  -E cloud.auth=elastic:XXX

# Once you get logs in Kibana, use a filter type:mysql and status:Error to extract failing queries.

This is required if you get an error while initiating tokudb engine:

echo never > /sys/kernel/mm/transparent_hugepage/enabled

And this is required if you get permissions error:

rm -rf /storage/custom3381

mkdir /storage/custom3381

chown 1001 /storage/custom3381

percona server has built-in environment variable for tokudb:

docker run -p 3381:3306 -v /my/custom3381:/etc/mysql/conf.d -v /storage/custom3381:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=india3381 -e INIT_TOKUDB=1 -d percona/percona-server:5.7

1) Download xtrabackup package
2) change directory
3) Full Backup
4) INcremental backup
5) Restore
6) Start mysql using backup
 
# Linux
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz

# centOS and redhat
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install percona-xtrabackup-24
_____

cd percona-xtrabackup-2.4.9-Linux-x86_64

bin/xtrabackup --defaults-file=/my/custom3396/my.cnf -H 172.31.0.57 -uroot -pindia3396 -P 3396 --datadir /storage/mysql/datadir3396 --backup --target-dir=/data3/backups/full/
_____

The main advantage of using xtrabackup is that we can take incremental backup that will be much faster.

bin/xtrabackup --defaults-file=/my/custom3396/my.cnf -H 172.31.0.57 -uroot -pindia3396 -P 3396 --datadir /storage/mysql/datadir3396 --backup --target-dir=/data3/backups/inc1 --incremental-basedir=/data3/backups/full/


The next day, we need to simply change the target directory path to "inc2" like this:

bin/xtrabackup --defaults-file=/my/custom3396/my.cnf -H 172.31.0.57 -uroot -pindia3396 -P 3396 --datadir /storage/mysql/datadir3396 --backup --target-dir=/data3/backups/inc2 --incremental-basedir=/data3/backups/inc1
_____

In case of disaster we need to apply logs and then prepare data:

1) First apply logs of target directory:
bin/xtrabackup --prepare  --apply-log-only --target-dir=/data3/backups/full/

2) Apply logs from incremental backup:
bin/xtrabackup --prepare --apply-log-only --target-dir=/data3/backups/full/ --incremental-dir=/data3/backups/inc1

3) apply log only option should not be used for the last incremental backup.
bin/xtrabackup --prepare  --target-dir=/data3/backups/full/  --incremental-dir=/data3/backups/inc2

4) Finally prepare target without apply log option for target directory:
bin/xtrabackup --prepare --target-dir=/data3/backups/full/
_____

Now since the backup data directory is ready, we can create a new docker container pointing to the newly "prepared" data.

docker run -p 3391:3306 -e MYSQL_ROOT_PASSWORD=india3391 -v /my/custom3391:/etc/mysql/conf.d  -v /data3/backups/full:/var/lib/mysql -d shantanuo/mysql:5.7

You can check if the new data is working correctly.

mysql -h `hostname -i` -uroot -pindia3396 -P 3391

Here are the steps to install mysql version 5.7.18 on CentOS 7 and Red Hat (RHEL) 7

# yum rpm
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

# yum install
yum install mysql-community-server

# make sure mysql starts on reboot
chkconfig --levels 235 mysqld on

# config 
[mysqld]
server-id=101048235

#log-bin=/var/log/mysql/mysql-bin.log
max_binlog_size=1024M
expire_logs_days=40
binlog_format=ROW
binlog_checksum=NONE

innodb_buffer_pool_size=4G
innodb_log_file_size=512M
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb-flush-log-at-trx-commit = 2

# start mysql
service mysqld start

# find current root password
grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1

# Change password
/usr/bin/mysql_secure_installation

Here is 6 lines of python code that will import any (excel or csv) data into mysql.

1) Open the excel file and save the data from a single sheet "s3_usage" into a data frame.
2) Copy the dataframe data into MySQL (test database - myreport_tbl table)

!wget https://s3.amazonaws.com/oksoft/pandas_work.xlsx

import pandas as pd
xl = pd.ExcelFile('pandas_work.xlsx')
s3_usage=xl.parse('s3_usage')

import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:passwd@172.31.30.248/test')
s3_usage.to_sql('myreport_tbl', engine, if_exists='replace')
_____

Login to mysql and check if the data is imported correctly.

mysql> select * from myreport_tbl limit 1;
+-------+----------+------------------+--------------------------+----------+---------------------+---------------------+------------+
| index | Service  | Operation        | UsageType                | Resource | StartTime           | EndTime             | UsageValue |
+-------+----------+------------------+--------------------------+----------+---------------------+---------------------+------------+
|     0 | AmazonS3 | ListAllMyBuckets | C3DataTransfer-Out-Bytes | NULL     | 2016-12-01 00:00:00 | 2016-12-01 01:00:00 |       1332 |
+-------+----------+------------------+--------------------------+----------+---------------------+---------------------+------------+
1 row in set (0.00 sec)

mysql> show create table myreport_tbl\G
*************************** 1. row ***************************
       Table: myreport_tbl
Create Table: CREATE TABLE `myreport_tbl` (
  `index` bigint(20) DEFAULT NULL,
  `Service` text,
  `Operation` text,
  `UsageType` text,
  `Resource` text,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
  `UsageValue` bigint(20) DEFAULT NULL,
  KEY `ix_myreport_tbl_index` (`index`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is a generic shell script that will take the backup of all mysql tabels in CVS format.
I have added --where clause to mysqldump command. It will take backup of 10 rows of each table. This is for testing purpose only. While implementing the script in production evvironment, remove that where clause.

#!/bin/sh
mydate=`date +"%d-%m-%Y-%H-%M"`

for db in `mysql -Bse"show databases"`
do
mydir='/tmp/testme/'$mydate/$db
rm -rf $mydir
mkdir -p $mydir
chmod 777 $mydir

for i in `mysql -Bse "select TABLE_NAME from information_schema.tables where TABLE_SCHEMA = '$db'"`
do
# take backup of only 10 rows for testing purpose
# remove --where clause in production
mysqldump $db $i --where='true limit 10' --tab=$mydir

done
done

Here is a list of some of the containers those I use frequently. Here is how to install docker
First make sure that you are a root user. And there is enough disk space available.

# install docker on AWS linux

yum install -y docker

# You can install docker on centOS version 7 if you have 64-bit version

cat /etc/redhat-release

sudo yum remove docker docker-common container-selinux docker-selinux docker-engine

yum install -y yum-utils device-mapper-persistent-data lvm2

yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo

yum makecache fast

yum install docker-ce

service docker start
_____

# start docker
vi /etc/sysconfig/docker-storage

DOCKER_STORAGE_OPTIONS="--storage-opt dm.basesize=200G"

/etc/init.d/docker start

MySQL

ofifical mysql repository

mkdir -p /storage/test-mysql/datadir
docker run -d -p 3306:3306  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes  -v /my/custom:/etc/mysql/conf.d  -v /storage/test-mysql/datadir:/var/lib/mysql   -v /my/custom:/etc/mysql/conf.d  mysql:5.6

(size: 100MB)
Just by changing the name to test-mysql2 we can set up another mysql container. Instead of mysql official version, we can use tutum/mysql which has customized installation.

fixed the bug in the official mysql image

https://github.com/shantanuo/mysql

Percona with tokuDB

Suppose you have a MySQL container running named "mysql-server", started with this command:

$ docker run -d \
--name=mysql-server \
-v /storage/mysql-server/datadir:/var/lib/mysql \
-e MySQL_ROOT_PASSWORD=mypassword \
mysql

Then, to perform backup against the above container, the command would be:

$ docker run -it \
--link mysql-server:mysql \
-v /storage/mysql-server/datadir:/var/lib/mysql \
-v /storage/backups:/backups \
--rm=true \
severalnines/mysql-pxb \
sh -c 'exec /run_backup.sh'

# install packages
yum install -y git mysql-server docker

curl -L https://github.com/docker/compose/releases/download/1.7.1/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose
chmod +x /usr/local/bin/docker-compose

/etc/init.d/docker start

# create and copy public key to github to clone private repo

ssh-keygen -t rsa -b 4096 -C "user@gmail.com"

cat ~/.ssh/id_rsa.pub


# clone your private repository
git clone git@github.com:shantanuo/xxx.git

# create your custom my.cnf config file

mkdir -p /my/custom/
vi /my/custom/my.cnf
[mysqld]
sql_mode=''

# start docker containers as per yml file

/usr/local/bin/docker-compose up -d

# restore mysql data into container from host:
mysqladmin -h localhost -P 3306 --protocol=tcp -u root -ppasswd create livebox

mysql -h localhost -P 3306 --protocol=tcp -u root -ppasswd livebox < livebox.sql

# access mysql container from another container:

mysql -h mysql -uroot -ppasswd
_____

## use container ID of tutum/mysql:5.5 for e.g.
docker logs c7950eeab8e9
    mysql -uadmin -pxmFShXB1Asgn -h127.0.0.1

# use the password to connect to 127.0.0.1 and execute commands:
mysql> grant all on *.* to 'root'@'%' identified by 'passwd' with grant option;
Query OK, 0 rows affected (0.00 sec)

# data only container to be used for mysql data
docker run -d -v /var/lib/mysql --name db_vol1 -p 23:22 tutum/ubuntu:trusty
docker run -d --volumes-from db_vol1 -p 3306:3306 tutum/mysql:5.5

1) Create mysql data directory
mkdir -p /opt/Docker/masterdb/data

2) Create my.cnf file
mkdir -p /opt/Docker/masterdb/cnf

vi /opt/Docker/masterdb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=1

innodb_buffer_pool_size = 2G
innodb_log_file_size=212M
binlog_format=ROW
log-bin

3) Run docker image

docker run --name masterdb -v /opt/Docker/masterdb/cnf:/etc/mysql/conf.d -v /opt/Docker/masterdb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d shantanuo/mysql

Official docker image for mysql using simply mysql:5.5
You can also use percona or an image by oracle using percona:5.5 OR mysql/mysql-server:5.5

$ sudo docker run --detach=true --name node1 -h node1 erkules/galera:basic --wsrep-cluster-name=local-test --wsrep-cluster-address=gcomm://

$ sudo docker run --detach=true --name node2 -h node2 --link node1:node1 erkules/galera:basic --wsrep-cluster-name=local-test --wsrep-cluster-address=gcomm://node1
$ sudo docker run --detach=true --name node3 -h node3 --link node1:node1 erkules/galera:basic --wsrep-cluster-name=local-test --wsrep-cluster-address=gcomm://node1

# sudo docker exec -ti node1 mysql
mysql> show status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

_____


deploy Galera Cluster over multiple servers

By design, Docker containers are reachable using port-forwarded TCP ports only, even if the containers have IP addresses. Set up port forwarding for all TCP ports that are required for Galera to operate.

docker run -d -p 3306:3306 -p 4567:4567 -p 4444:4444 -p 4568:4568 --name nodea erkules/galera:basic --wsrep-cluster-address=gcomm:// --wsrep-node-address=10.10.10.10
docker run -d -p 3306:3306 -p 4567:4567 -p 4444:4444 -p 4568:4568 --name nodeb erkules/galera:basic --wsrep-cluster-address=gcomm://10.10.10.10 --wsrep-node-address=10.10.10.11
docker run -d -p 3306:3306 -p 4567:4567 -p 4444:4444 -p 4568:4568 --name nodec erkules/galera:basic --wsrep-cluster-address=gcomm://10.10.10.10 --wsrep-node-address=10.10.10.12
docker exec -t nodea mysql -e 'show status like "wsrep_cluster_size"'

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size |     3 |
+--------------------+-------+

## create a directory on base machine for mysql data
mkdir -p /my/own/datadir

## link data directory and port 3306 to base machine while starting mysql container from official mysql image
docker run --name some-mysql1 -v /my/own/datadir:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql

## connect to your mysql
mysql -uroot -pmy-secret-pw -h127.0.0.1 -P 3306

You can pull the latest mysql image and create a container named "new-mysql1". Then start the container in second command as shown below:

docker create -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password --name="new-mysql1" mysql:latest

docker start 22914939f301

Or merge create + start into a single "run" command as shown below:

docker run --name new-mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql/mysql-server:latest
_____

Then you can access from your host using the mysql command line:

mysql -h127.0.0.1 -ppassword -uroot

db.users.find({}, {})	select * from users
db.users.find({}, {“username”:1, “email”:1}}	select username, email from  users
db.users.find({}, {“username”:1, “email”:1}}.limit(10)	select username, email from  users limit 10
.limit()          .skip()                   .sort()
db.users.count()	select count(*) from users
db.runCommand(“distinct”: “users”, “key”: “age”})	select distinct(age) from users
db.users.find({“age”:{“$gte”:18, “$lte”:30}})	select * from users where age >= 18 and age <= 30
$lt      $gt     $lte    $get             $ne    $elemMatch
db.users.find({“ticket_no”:{“$in”:[75, 390]}})	select * from  users where ticket_no in (“75”, “390”)
$in     $nin             $not             $all
db.users.find({“$or”:[{“ticket_no”:{“$in”:[75, 390]}}, {“winner”:true}]})	select * from  users where ticket_no in (“75”, “390”) or winner is not null
$or     $and           $nor             $elemMatch
db.users.find({“age”:{“$in”:[null], “$exists”:true}})	select * from  users where age is null
db.users.find({“username”:/happy?/i})	select * from  users where username like ‘happy%’
perl compatible regular expressions
db.users.find({“ticket_no”:75})	select * from  users where ticket_no like ‘%75%’
[75, 390, 120, 450]	“75”, “390”, “120”, “450”
db.users.find({“ticket_no.2”:120})
db.users.find({“ticket_no”:{“$size”:4}})
db.users.findOne({criteria as above}, {“$slice”:[23, 10]}})	select * from users where age >= 18 and age <= 30 limit 23, 10
db.runCommand($getLastError”:1})	show warnings;
db.articles.aggregate("$project": {"author":1}}, {$group":{"_id":"$author", "count":{"$sum":1}}}, {"$sort": {"count": -1}}, {"$limit":5}	Select  author, count(*) as cnt from  articles group by author order by cnt desc limit 5
Aggregation results are limited to maximum response time of 16 MB
db.employees.aggregate( {"$project": {"totalPay" : {"$subtract" : [{"$add": ["$salary", "$bonus"]}, "$taxes"] } } } )	Select  (salary + bouns – taxes) as totalPay from  employees
$add  $subtract      $multiply  $divide   $mod
db.employees.aggregate( { "$project" : { "tenure" : {"$subtract" : [{"$year" : new Date()}, {"$year": "$hireDate"}] } } } )	select   year(now()) – year(hireDate) as tenure from employees
$year $month $week $dayOfMonth $dayOfWeek $dayOfYear  $hour  $minute  $second
db.employees.aggreage( { "$project": { "email" : { "$concat" : [ {"$substr" : [ "$firstName", 0, 1]}, ".", "$lastName", "@company.com" ] } } } )	select  concat(left(firstName, 1), “.”, lastName, “@company.com”) as email from employees
$substr   $concat  $toLower   $toUpper
db.sales.aggregate( { "$group": { "_id": "$country", "totalRevenue": { "$sum" : "$revenue" } } } )	select country, sum(revenue) from sales group by country
db.blog.aggregate({"$project": {"comments": "$comments"}}, {"$unwind" : "$comments"}, {"$match": {"comments.author" : "Akbar" }})

While troubleshooting mysql issue, the first place to check is error log. If the error log is clean, then the next option to evaluate is slow query log

# enable slow query log
mysql> set global slow_query_log = on;

# change the default 10 seconds to 1 second
# make sure that the queries not using indexes are logged
SET GLOBAL long_query_time=1;
SET GLOBAL log_queries_not_using_indexes=1;

# If the slow log is growing too fast, feel free to again set the variables back to how they were:

SET GLOBAL long_query_time=10;
SET GLOBAL log_queries_not_using_indexes=0;

# or disable the slow query log
set global slow_query_log = off;

The unicode characters are not allowed to be stored in latin1
You will get an error as shown below:

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=latin1;

insert into todel values ('हिदी' )
Error in query (1366): Incorrect string value: '\xE0\xA4\xB9\xE0\xA4\xBF...' for column 'name' at row 1

insert into todel values (convert ('हिदी' using binary));
select convert(convert(name using binary) using utf8) from todel;

The work-around is to store the record as binary and while selecting, use convert function twice as shown above.
_____

There is a better way though. Why not to use utf8 encoding for the entire table?

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=utf8;

insert into todel values ('हिदी' );
select * from todel;
_____

You think that altering the table to utf8 will solve this issue?

alter table todel default charset=utf8;

No. Because even if the default table type is now utf8, the columns are still latin1

 mysql> show create table todel;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| todel | CREATE TABLE `todel` (
  `name` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

alter table todel modify name varchar(100) character set utf8 ;

Now even if we have column as well as table type utf8, we still get junk characters instead of unicode.

mysql> select * from todel;
+---------------------------+
| name                      |
+---------------------------+
| हिदी              |
+---------------------------+
1 row in set (0.00 sec)

The bad news is that the convert query that was working fine earlier has now stopped working as expected.

mysql> select convert(convert(name using binary) using utf8) from todel;
+------------------------------------------------+
| convert(convert(name using binary) using utf8) |
+------------------------------------------------+
| हिदी                                   |
+------------------------------------------------+
1 row in set (0.00 sec)

The unicode data is lost in the conversion.
_____

So the correct solution would be to add a utf8 column to latin1 table and update that column data with correct unicode string.
Let's start all over again:

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=latin1;
insert into todel values (convert ('हिदी' using binary));
select convert(convert(name using binary) using utf8) from todel;

alter table todel add column hindi varchar(100) character set utf8;

update todel set hindi = convert(convert(name using binary) using utf8) ;

mysql> select convert(convert(name using binary) using utf8), hindi from todel;
+------------------------------------------------+--------------+
| convert(convert(name using binary) using utf8) | hindi        |
+------------------------------------------------+--------------+
| हिदी                                           | हिदी         |
+------------------------------------------------+--------------+
1 row in set (0.00 sec)

_____

Now the problem is that the application is not aware of this new column called "hindi" and it is still using column "name". We need an insert and update trigger to keep correcting the values in the hindi column. Something like this...

delimiter |
CREATE TRIGGER todel_bi after INSERT ON todel
  FOR EACH ROW
  BEGIN
    UPDATE todel SET hindi = convert(convert(name using binary) using utf8) WHERE id = NEW.id;
  END;
|

insert into todel (id, name) values (2, (convert ('मराठी' using binary)));

I thought the after insert trigger would solve this issue, but I get an error.

ERROR 1442 (HY000): Can't update table in trigger because it is already used by statement which invoked this stored function/trigger.

Now I need to write a stored procedure that inserts the record in the target table and change the code accordingly (that will call the procedure).
_____

So we are back where we started. Why not simply replace the name column with this new unicode aware column?

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=latin1;
insert into todel values (convert ('हिदी' using binary));

alter table todel add column hindi varchar(100) character set utf8;
update todel set hindi = convert(convert(name using binary) using utf8) ;
alter table todel drop column name;
alter table todel change column hindi name varchar(100) character set utf8;

Here is the Mysql code that can be used to quickly generate 5 digit numbers starting from 10000 to 99999

drop table t;
create table t (series int);

set @n = 1;
drop view if exists v3;
drop view if exists v10;
drop view if exists v100;
create view v3 as select null union all select null union all select null;
create view v10 as select null from v3 a, v3 b union all select null;
create view v100 as select null from v10 a, v10 b;
insert into t select @n:=@n+1 from v10 a,v100 b, v100 c;
delete from  t where length(series) != 5;

Here is how to quickly review the database. We can query the information_schema database to find if the column types are as per internal guidelines.

mysql> select DATA_TYPE, COUNT(*) AS CNT, GROUP_CONCAT(DISTINCT(COLUMN_TYPE)) from information_schema.COLUMNS
where TABLE_SCHEMA = 'recharge_db' GROUP BY DATA_TYPE;

+-----------+-----+----------------------------------------------------------------------------------------+
| DATA_TYPE | CNT | GROUP_CONCAT(DISTINCT(COLUMN_TYPE))                                                    |
+-----------+-----+----------------------------------------------------------------------------------------+
| bigint    |  15 | bigint(20)                                                                             |
| datetime  |   6 | datetime                                                                               |
| double    |   3 | double                                                                                 |
| int       |  24 | int(11),int(2)                                                                         |
| text      |   2 | text                                                                                   |
| varchar   |  16 | varchar(100),varchar(500),varchar(10),varchar(20),varchar(15),varchar(255),varchar(25) |
+-----------+-----+----------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

The suggestions would be:
1) Change double to decimal
2) Change int(2) to tinyint
3) change text to varchar(1000) if possible

mysql> select IS_NULLABLE, COUNT(*) AS CNT from information_schema.COLUMNS
where TABLE_SCHEMA = 'recharge_db' AND COLUMN_KEY != 'PRI'  GROUP BY IS_NULLABLE;
+-------------+-----+
| IS_NULLABLE | CNT |
+-------------+-----+
| NO          |   1 |
| YES         |  52 |
+-------------+-----+
2 rows in set (0.00 sec)

Most of the columns are nullable those should be changed to "NOT NULL".
Certain columns can not be changed to "NOT NULL" and they are candidate for further normalization.

The following query will list all the columns those can be linked to the table that has declared it as Primary Key.

select COLUMN_NAME, COUNT(*) AS CNT, GROUP_CONCAT(IF(COLUMN_KEY = 'PRI', concat(TABLE_NAME, '_primary_key'), TABLE_NAME) order by COLUMN_KEY != 'PRI', TABLE_NAME) as tbl_name
from information_schema.COLUMNS
where TABLE_SCHEMA = 'recharge_db'
group by COLUMN_NAME HAVING CNT > 1 AND tbl_name like '%_primary_key%';
_____

Find missing primary key:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type in ('PRIMARY KEY', 'UNIQUE'))
AND table_schema = 'recharge_db';
_____

// Check if the column names are consistent and as per standard

select column_name, count(*) as cnt
from information_schema.columns where TABLE_SCHEMA = 'recharge_db'
GROUP BY column_name;

_____