Short Notes on MySQL

Error 1045: Access denied

The 1045 bug may appear under many circumstances, and it may be related to multiple problems.

Kind of "legacy" problem might be that there is anonymous user defined. Such user will show up with empty Name in the following set:

mysql> select User, Host, Password from mysql.user;
+-------------+----------------+-------------------------------------------+
| User        | Host           | Password                                  |
+-------------+----------------+-------------------------------------------+
| root        | localhost      | [.................hash..................] |
| root        | 127.0.0.1      | [.................hash..................] |
|             | %              | [.................hash..................] |
| ...                                                                      |
+-------------+----------------+-------------------------------------------+

This user will be selected first, and will interfere with your login. Removing such user is generally a good idea anyways.

Next problem, when connecting remotely, may be caused simply by networking being disabled, or that MySQL binds only to local IP.

show variables like 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | ON    |
+-----------------+-------+

You can either set this variable to OFF, or comment out the setting in /etc/mysql/my.cnf:

# skip-networking

One more option is that your MySQL server binds only to local IP; under [mysqld] section in /etc/mysql/my.cnf comment out bind setting, or replace it with your public IP:

# bind-address = 127.0.0.1
# or
bind-address = 123.1.2.3

If none of this helps, you're left with user privileges. Make sure the user that is trying to connect is at all present in system:

mysql> select User, Host, Password from mysql.user;
+-------------+----------------+-------------------------------------------+
| User        | Host           | Password                                  |
+-------------+----------------+-------------------------------------------+
| ...                                                                      |
+-------------+----------------+-------------------------------------------+

If it is (make sure both User and Host match, or Host is %), make sure it has rights granted:

show grants for 'dbuser'@'example.com';

If the user does not exist, or if it doesn't have rights granted, you can create and/or update it at once with:

grant all on db.* to 'dbuser'@'example.com' identified by 'password';

Of course, restrict the grant all to just rights required whenever possible.

Also, make sure to include the identified by 'password' each time, otherwise, if the user does not exist in system, it'll be left with empty password.

One last thing - make sure individual users with the same username have the same password (if this is desirable):

mysql> select User, Host, Password from mysql.user where User = 'dbuser';
+-------------+----------------+-------------------------------------------+
| User        | Host           | Password                                  |
+-------------+----------------+-------------------------------------------+
| dbuser      | localhost      | [.................hash..................] |
| dbuser      | 127.0.0.1      | [.................hash..................] |
| dbuser      | example.com    |                                           |
+-------------+----------------+-------------------------------------------+

Note that 'dbuser'@'example.com' has empty password! It's just as possible that the hashes/passwords differ. To fix this, use

set password for 'dbuser'@'example.com' = PASSWORD('password');

After all this, you should be able to connect... hopefully.

Note: You can also

flush privileges;

after updates to the user grants and passwords, but this is rarely necessary unless you're on a MySQL Cluster.

The insert ... on duplicate key update Pitfall

Some time back I had a problem - gaps in values in auto_increment column in MySQL table.

It turns out that when using insert ... on duplicate key update, the auto_increment counter is being incremented even if the row exists, and is only being updated.

If you want the auto_increment column to be contiguous, or simply don't wanna waste values available, you'll have to check if the row exists and use insert/update accordingly.

This might especially become a problem in tables where updates to rows are much more frequent than inserts, and you might run out of values for the ID column.

Shrink ibfile1 InnoDB File

Warning - it's not easily possible :)

That said, here are steps to get rid of the problem, to certain extent:

• it's better to activate innodb_file_per_table setting in /etc/mysql/my.cnf, under [mysqld] section,
  • this won't solve the problem of growing files, but each table will be in separate file, and dropping a table will result in file being deleted,
• if you already have existing data in MySQL, do the following (you can ignore information_schema database in the process):
  • mysqldump all databases, procedures, triggers, etc. except mysql and performance_schema databases,
  • drop all databases except mysql and performance_schema,
  • stop mysql service,
  • delete the ibdata1 and ib_log* files in MySQL data folder,
  • start mysql service,
  • restore databases from dump.