MySQL Archives - Redino blog

mysql docker connection error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

gbstack09/05/2022 0 Comments

I started docker mysql container and try to connect it outside docker using mysql command but got error:

$ mysql -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

1

2

3

4

$ mysql -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

It's because mysql clinet will use unix socket protocol as default protocol value to connect to mysql server if not specifying protocol or host argument, but mysql docker container is listening on tcp socket.

Solution

$ mysql -uroot -proot -h127.0.0.1

1	$ mysql -uroot -proot -h127.0.0.1

Specify IP address as host argument value in mysql command will make mysql to connect using TCP protocol instead of default unix socket.
(Note that using localhost as host argument value will still using unix socket protocol)

Or use

$ mysql -uroot -proot --protocol tcp

1	$ mysql -uroot -proot --protocol tcp

This command will specify protocol argument explicitly

Specified key was too long; max key length is 3072 bytes

gbstack03/28/2018 0 Comments

alter table actionstatistics add unique(value, productId, productName, productSerialNumber, userPhone, userName, userId, type, statStartTime, statEndTime);

1	alter table actionstatistics add unique(value, productId, productName, productSerialNumber, userPhone, userName, userId, type, statStartTime, statEndTime);

When running above SQL statement to add an unique index, following error happened.

[2018-03-28 11:38:24] [42000][1071] Specified key was too long; max key length is 3072 bytes

The actionstatistics table structure is defined as following:

CREATE TABLE <code>paymentstat</code> (
id</code> bigint(20) NOT NULL AUTO_INCREMENT,
created</code> datetime DEFAULT NULL,
isCalculatedReward</code> bit(1) NOT NULL,
productId</code> int(11) DEFAULT NULL,
productName</code> varchar(255) DEFAULT NULL,
productSerialNumber</code> varchar(255) DEFAULT NULL,
statEndTime</code> datetime DEFAULT NULL,
statStartTime</code> datetime DEFAULT NULL,
type</code> varchar(255) DEFAULT NULL,
userId</code> bigint(20) DEFAULT NULL,
userName</code> varchar(255) DEFAULT NULL,
userPhone</code> varchar(255) DEFAULT NULL,
value</code> varchar(255) DEFAULT NULL,
valueDouble</code> decimal(20,2) DEFAULT NULL,
actionStatisticsExpression_id</code> bigint(20) DEFAULT NULL,
user_id</code> bigint(20) DEFAULT NULL,
  PRIMARY KEY (<code>id</code>),
  KEY <code>FK_d7fu0dasg9vq3w21qtvr6227o</code> (<code>actionStatisticsExpression_id</code>),
  KEY <code>FK_r852hjlpseh3ue8wd8gugd14d</code> (<code>user_id</code>),
  KEY <code>phone_idx</code> (<code>userPhone</code>)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

CREATE TABLE <code>paymentstat</code> (

id</code> bigint(20) NOT NULL AUTO_INCREMENT,

created</code> datetime DEFAULT NULL,

isCalculatedReward</code> bit(1) NOT NULL,

productId</code> int(11) DEFAULT NULL,

productName</code> varchar(255) DEFAULT NULL,

productSerialNumber</code> varchar(255) DEFAULT NULL,

statEndTime</code> datetime DEFAULT NULL,

statStartTime</code> datetime DEFAULT NULL,

type</code> varchar(255) DEFAULT NULL,

userId</code> bigint(20) DEFAULT NULL,

userName</code> varchar(255) DEFAULT NULL,

userPhone</code> varchar(255) DEFAULT NULL,

value</code> varchar(255) DEFAULT NULL,

valueDouble</code> decimal(20,2) DEFAULT NULL,

actionStatisticsExpression_id</code> bigint(20) DEFAULT NULL,

user_id</code> bigint(20) DEFAULT NULL,

PRIMARY KEY (<code>id</code>),

KEY <code>FK_d7fu0dasg9vq3w21qtvr6227o</code> (<code>actionStatisticsExpression_id</code>),

KEY <code>FK_r852hjlpseh3ue8wd8gugd14d</code> (<code>user_id</code>),

KEY <code>phone_idx</code> (<code>userPhone</code>)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

So the length of above index key I'm trying to create is value(255*3), productId(4), productName(255*3), productSerialNumber(255*3), userPhone(255*3), userName(255*3), userId(8), type(255*3), statStartTime(8), statEndTime(8)

Total length is 255*6*3 + 4 + 8*3=4618 is exactly greater than 3072 bytes.

You may notice when calculating the bytes length, we multiply 3 for the varchar type, that's because charset of actionstatistics table is UTF8, so every character will occupy 3 bytes.

Solution

One solution is to decrease length of each key. Now I'm using 255 as key length for each varchar column, but that's wasting of disk space. Since columns like userPhone, userName, etc. don't need occupy 255 bytes space.

So change to following statement, and the error is fixed.

alter table actionstatistics add unique(value, productId, productName(10), productSerialNumber(20), userPhone(12), userName(20), userId, type(10), statStartTime, statEndTime);

1	alter table actionstatistics add unique(value, productId, productName(10), productSerialNumber(20), userPhone(12), userName(20), userId, type(10), statStartTime, statEndTime);

Another solution is to increase this 3072 bytes max key length limit.

MySQL find all MyISAM tables

gbstack12/21/2016 0 Comments

The information_schema table saves information about databases, tables, columns, indexes and triggers, etc.

To list all MyISAM tables, we can use following SQL

select * from information_schema.tables where engine='MyISAM';

1	select * from information_schema.tables where engine='MyISAM';

And to find MyISAM tables in database wordpress

select * from information_schema.tables where engine='MyISAM' and table_schema='wordpress';

1	select * from information_schema.tables where engine='MyISAM' and table_schema='wordpress';

Next filter them with a table prefix wp_

select * from information_schema.tables where engine='MyISAM' and table_schema='wordpress' and table_name like 'wp_%';

1	select * from information_schema.tables where engine='MyISAM' and table_schema='wordpress' and table_name like 'wp_%';

MySQL Auto Backup

gbstack05/02/2015 0 Comments

First create a shell script which is used to backup database, here we named the script as mysql_backup.sh:

current_date=`date +"%y-%m-%d"`
mysqldump -u.. -p.. db_name > db_name$current_date.sql

1 2	current_date=`date +"%y-%m-%d"` mysqldump -u.. -p.. db_name > db_name$current_date.sql

In above command, we use date command to generate current date, and use it as part of filename.

After that we need to make this file executable

chmod +x mysql_backup.sh

1	chmod +x mysql_backup.sh

To make this backup script run automatically, we should create a cronjob. Type following command to open cronjob editor

crontab -e

Write following text and save it

0 0 * * * /home/usernane/mysql_backup.sh

1	0 0 * * * /home/usernane/mysql_backup.sh

Above command means the cronjob will run at 0:00 everyday

Check MySQL case-insensitive query or table name

gbstack04/30/2015 0 Comments

I tried to install Umbrao CMS, it supports MySQL database but it need MySQL to support case-insensitive query and table name.

Following is way to check whether case-insensitive query is supported:

show variables where variable_name='lower_case_table_names';

or check lower_case_table_names option in my.ini (MySQL configuration file)

If this option's value is set to 1, then case-insensitive query and table name is supported.