MySQL Errors

ERROR 2002

server1@server1:~$ mysql -uroot -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Solution:
Installed MySQL server

$ sudo apt-get install mysql-server

If in windows:
getting this error maybe because mysqld is not running. In cmd run,
$ mysqld

to check if its running you can see if the you’re listening to the port 3306
$ netstat -a -n

to check if its running you can telnet it
$ telnet localhost 3306

A useful tool
$ mysqladmin

ERROR 1709 / ERROR 1071

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

ERROR 1709 (HY000) at line 206: Index column size too large. The maximum column size is 767 bytes.

mysql error 1709

Show mysql variables
mysql> show variables
mysql> show variables where variable_name = 'innodb_large_prefix'
mysql> SELECT @@innodb_large_prefix

View table file_format and row_format
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES where name like '%database_name/table_name%'

Make changes to variables (but you should also set them in my.cnf so they survive a restart)
mysql> set global innodb_file_format = BARRACUDA;
mysql> set global innodb_large_prefix = ON;

Change table row format after table has been created
mysql> ALTER TABLE tablename ROW_FORMAT=DYNAMIC

Specify table row format when table is being created
...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

In InnoDB version 5.7.9 you can set a default row format

Leave a Reply