mail

ERROR 1045 (28000): Access denied for user 'bob'@'host' (using password: YES)

This is a very common error which can have multiple causes.

Things you don't need to check :

  • this error is returned by MySQL itself, which indicates it's running fine as for the network / firewall setup
  • underscores _ are allowed in user names

Possible causes :

  • forgot to provide a password at login :
    ERROR 1045 (28000): Access denied for user 'bob'@'localhost' (using password: NO)
    • mysql -u bob -p + + type password +
    • mysql -u bob -ppassword +
      No space between -p and password !
  • wrong password :
    ERROR 1045 (28000): Access denied for user 'bob'@'localhost' (using password: YES)
  • typo in user name or in host name
  • forgot to flush privileges;
  • unauthorized host : for MySQL, an account is a user tied to a host
    • either you're trying to connect from the wrong host
    • or you've not allowed the current (good) host to connect
    On servers having multiple network interfaces ("admin" and "operations" LANs), make sure you've registered to right "way out" for the host trying to connect.
  • wildcards : 'bob'@'%' will match any host, except localhost, depending on your situation, you may need distinct entries :
    • 'bob'@'%'
    • 'bob'@'localhost'
    (details : 1, 2)
    https://dba.stackexchange.com/questions/10852/mysql-error-access-denied-for-user-alocalhost-using-password-yes/10897#answer-10897
    When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:
    
    In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.
    
    Make sure that
    	* every user has a password.
    	* there are no anonymous users (when user is blank)
    https://dba.stackexchange.com/questions/10852/mysql-error-access-denied-for-user-alocalhost-using-password-yes/10897#answer-10890
    
    The '%' host wildcard doesn't match 'localhost'. By default the mysql client will try to connect via a socket rather than tcp (usually some place like /var/lib/mysql/mysql.sock).
    
    
    If you don't specify a -h hostname it assumes "localhost" meaning it's looking for a socket, not a TCP port w/o the protocol flag.
  • stuff related to "empty string" user (aka "anonymous user"). Google this...
  • in environments with 2 MySQL servers + keepalived, make sure the configuration is ok :
    hostnameBase='sql'; environment='prp'; domain='example.com'; for i in {01..2}; do ssh $hostnameBase$i-$environment.admin.$domain "grep -A1 virtual_ipaddress /etc/keepalived/keepalived.conf | sed -nr 's/^ *(([0-9]+\.){3}[0-9]+).*$/\1/p'"; done; host $hostnameBase-$environment.ope.$domain | awk '{print $NF}'
    10.203.15.8
    10.203.15.8
    10.203.15.8	match ! 
    10.203.10.11
    10.203.10.11
    10.203.10.6	Houston, we have a problem ! 
mail

Replication fails : Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table mysql.gtid_slave_pos doesn't exist in engine

Situation :

This actually happened on :
  • mysql --version
    mysql Ver 15.1 Distrib 10.0.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
  • lsb_release -a
    Distributor ID:	Debian
    Description:	Debian GNU/Linux 8.10 (jessie)
    Release:	8.10
    Codename:	jessie
  • in a master-master replication setup
On the server acting as the slave, I can see :
  • mysql -u root -e 'show slave status\G' | grep 'Last_Err'
    Last_Errno: 1932
    Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
  • mysql -u root -e 'select count(1) from mysql.gtid_slave_pos;'
    ERROR 1932 (42S02) at line 1: Table 'mysql.gtid_slave_pos' doesn't exist in engine

Solution :

Let's give mysql_upgrade a try (source, MariaDB version, MySQL version) :

  1. mysql_upgrade
    This installation of MySQL is already upgraded to 10.0.32-MariaDB, use --force if you still need to run mysql_upgrade
  2. systemctl restart mysql.service
  3. check again

No luck at the previous step, let's be more insistent :

mysql_upgrade --force
Phase 1/6: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

Repairing tables
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed

stackoverflow.com to the rescue : mysql upgrade failed innodb tables doesn't exist :

The page suggests a large MariaDB SQL script, which didn't work effortlessly for me, which is why I'll show it sliced here :

The gtid_slave_pos table :

  1. use mysql;
    --
    -- Table structure for table `gtid_slave_pos`
    --
    
    DROP TABLE IF EXISTS `gtid_slave_pos`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `gtid_slave_pos` (
      `domain_id` int(10) unsigned NOT NULL,
      `sub_id` bigint(20) unsigned NOT NULL,
      `server_id` int(10) unsigned NOT NULL,
      `seq_no` bigint(20) unsigned NOT NULL,
      PRIMARY KEY (`domain_id`,`sub_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';
    /*!40101 SET character_set_client = @saved_cs_client */;
    ERROR 1813 (HY000): Tablespace for table '`mysql`.`gtid_slave_pos`' exists. Please DISCARD the tablespace before IMPORT.
  2. Ok, let's do this (source) :
    ALTER TABLE `mysql`.`gtid_slave_pos` DISCARD TABLESPACE;
    ERROR 1146 (42S02): Table 'mysql.gtid_slave_pos' doesn't exist
  3. So I have some tablespace for a missing table () ???
    Seems like my "MySQL data" directory has orphan idb files (i.e. idb files having no frm mate, source). Let's find them :
    find /var/lib/mysql/data -type f -name "*ibd" | sed -r 's/\.ibd$/.frm/' | xargs -I foo bash -c 'ls foo &>/dev/null && : || echo foo'
    /var/lib/mysql/data/mysql/gtid_slave_pos.frm
    /var/lib/mysql/data/mysql/innodb_index_stats.frm
    /var/lib/mysql/data/mysql/innodb_table_stats.frm
    Now let's clean up (simulating file deletion) :
    for stuff in /var/lib/mysql/data/mysql/gtid_slave_pos.ibd /var/lib/mysql/data/mysql/innodb_index_stats.ibd /var/lib/mysql/data/mysql/innodb_table_stats.ibd; do mv "$stuff" "${stuff}_DISABLED"; done
  4. After that, I've been able to drop + create table gtid_slave_pos

Other tables, end of script :

--
-- Dumping data for table `gtid_slave_pos`
--

LOCK TABLES `gtid_slave_pos` WRITE;
/*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */;
/*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `innodb_index_stats`
--

DROP TABLE IF EXISTS `innodb_index_stats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `innodb_index_stats`
--

LOCK TABLES `innodb_index_stats` WRITE;
/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */;
/*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `innodb_table_stats`
--

DROP TABLE IF EXISTS `innodb_table_stats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `innodb_table_stats`
--

LOCK TABLES `innodb_table_stats` WRITE;
/*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */;
/*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `slave_master_info`
--

DROP TABLE IF EXISTS `slave_master_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `slave_master_info`
--

LOCK TABLES `slave_master_info` WRITE;
/*!40000 ALTER TABLE `slave_master_info` DISABLE KEYS */;
/*!40000 ALTER TABLE `slave_master_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `slave_relay_log_info`
--

DROP TABLE IF EXISTS `slave_relay_log_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `slave_relay_log_info`
--

LOCK TABLES `slave_relay_log_info` WRITE;
/*!40000 ALTER TABLE `slave_relay_log_info` DISABLE KEYS */;
/*!40000 ALTER TABLE `slave_relay_log_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `slave_worker_info`
--

DROP TABLE IF EXISTS `slave_worker_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `slave_worker_info`
--

LOCK TABLES `slave_worker_info` WRITE;
/*!40000 ALTER TABLE `slave_worker_info` DISABLE KEYS */;
/*!40000 ALTER TABLE `slave_worker_info` ENABLE KEYS */;
UNLOCK TABLES;
This worked fine

Final check (remember ?):

mysql -u root -e 'select count(1) from mysql.gtid_slave_pos;'
+----------+
| count(1) |
+----------+
|        2 |
+----------+
The database replication can now run normally.
mail

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104 "Connection reset by peer"

Situation :

While trying to connect :
mysql -h 10.203.10.6 -u tools_admin
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104 "Connection reset by peer"

Details :

Not much from the online doc :
Message: Lost connection to MySQL server during query

Solution :

This was a very specific network issue :
  • client to server : packets were flowing right
  • server to client : packets were dropped due to a firewall misconfiguration
mail

MySQL can't restart: InnoDB: mmap(n bytes) failed; errno 12

Situation :

  1. service mysql start gives : Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
  2. /var/log/syslog says :
    
    mysqld: 131125	9:38:01 InnoDB: The InnoDB memory heap is disabled
    mysqld: 131125	9:38:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    mysqld: 131125	9:38:01 InnoDB: Compressed tables use zlib 1.2.3.4
    mysqld: 131125	9:38:01 InnoDB: Using Linux native AIO
    mysqld: 131125	9:38:01 InnoDB: Initializing buffer pool, size = 11.0G
    mysqld: InnoDB: mmap(12087984128 bytes) failed; errno 12
    mysqld: 131125	9:38:01 InnoDB: Completed initialization of buffer pool
    mysqld: 131125	9:38:01 InnoDB: Fatal error: cannot allocate memory for the buffer pool
    mysqld: 131125	9:38:01 [ERROR] Plugin 'InnoDB' init function returned error.
    mysqld: 131125	9:38:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    mysqld: 131125	9:38:01 [ERROR] Unknown/unsupported storage engine: InnoDB
    mysqld: 131125	9:38:01 [ERROR] Aborting
    mysqld: 131125	9:38:01 [Note] /usr/sbin/mysqld: Shutdown complete
    mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
    

Details :

Looks like something's wrong with :
  1. the InnoDB logs files (Sources : 1, 2):
    • /var/lib/mysql/ib_logfile0
    • /var/lib/mysql/ib_logfile1
    • /var/lib/mysql/ib_logfile2 (if exists)
  2. SWAP space (source)

Solution :

  1. rename InnoDB logs files so that they look as deleted
  2. enable swappiness (if it was previously completely disabled.)
mail

Error 1449 : The user specified as a definer ('bob'@'host') does not exist

To update the definer (source) :
  1. get further details on that non-existing user : show create view viewName;
  2. It is not possible to alter information_schema entries directly (source).
  3. get details on the view : SELECT TABLE_SCHEMA, TABLE_NAME, DEFINER, VIEW_DEFINITION FROM information_schema.views WHERE TABLE_SCHEMA='dbName' AND TABLE_NAME='viewName';
  4. save the viewDefinitionString for future use
  5. build a query like : SELECT CONCAT("ALTER DEFINER=`bob`@`host` VIEW ", "viewDefinitionString" ,";") FROM information_schema.views WHERE table_schema='dbName';
  6. save it into tmp.sql
  7. run the query : login='bob'; password='password'; dbName='dbName'; cat tmp.sql | mysql -u $login -p$password | mysql -u $login -p$password $dbName
Run the big query :
mysql -u root -p****** < tmp.sql > alterView.sql

For some unknown reason, the "big query" resulted in a :
CONCAT("ALTER DEFINER='.....(unterminated WTF-query)
And 5 legal "ALTER DEFINER=..." queries.

edit alterView.sql to remove the WTF-query
mysql -u root -p****** dbName < alterView.sql

If it complains : The user specified as a definer ('root'@'10.0.16.0/255.255.255.0') does not exist
INSERT INTO mysql.user (User, Host) VALUES ('root', '10.0.16.0/255.255.255.0');


==> YOU'RE F***ED ! Re-import the views (see details)
mail

Disk space errors

This happens when there is not enough space left on /tmp to write temporary data. To workaround this, edit /etc/mysql/my.cnf and set tmpdir to another directory on a filesystem having some free space.
mail

Error 111 : Can't connect to MySQL server on '192.168.0.228' (111)

This means the server only listens on the loopback interface. To fix this, comment the lines :
skip-networking
bind-address = 127.0.0.1
from /etc/mysql/my.cnf, and restart MySQL : /etc/init.d/mysql restart
reload and force-reload seem to have no effect here.
mail

Error 2002 : Can't connect to [local] MySQL server

Possible causes :
mail

Error 2006 : MySQL server has gone away

This usually means the server timed out and closed the connection. Possible causes :
mail

Generic notes about MySQL errors

Here are some resources that may help when facing MySQL errors :