MySQL

error 1045: access denied

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

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

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

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

the next problem, when connecting remotely, may be caused simply by networking being disabled, or by MySQL binding only to the 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 the local IP; under the [mysqld] section in /etc/mysql/my.cnf comment out the 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 present in the system at all:

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

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 doesn't exist, or if it doesn't have rights granted, you can create and/or update it at once with:

create user if not exists 'dbuser'@'example.com' identified by 'password';
grant all on db.* to 'dbuser'@'example.com';

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

update 2026 - this used to be a single grant … identified by 'password', which created the user on the fly - and without the identified by, left it with an empty password. MySQL 8 removed both, grant no longer creates users

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

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

note that 'dbuser'@'example.com' has an empty password! it's just as possible that the hashes/passwords differ. to fix this, use

alter user 'dbuser'@'example.com' identified by '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 an auto_increment column in a 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 the 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 the ibdata1 InnoDB file

warning - it's not easily possible :)

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

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

index JSON columns

MySQL doesn't support direct indexing of JSON columns, or values within

that said, it does support generated columns based on data within

consider book storage, where we want to index title and author:

'data' column of 'book' table contains:
{
  "title": "...",
  "author": "...",
  ... other fields ...
}

to alter the book table, you need to add 2 generated columns, e.g. of type VARCHAR(255), and index these in the usual fashion:

ALTER TABLE book
ADD data_title VARCHAR(255) GENERATED ALWAYS AS (data ->> '$.title') VIRTUAL,
ADD data_author VARCHAR(255) GENERATED ALWAYS AS (data ->> '$.author') VIRTUAL;

ALTER TABLE book
ADD INDEX (data_title),
ADD INDEX (data_author);

note: MySQL, unlike the SQL standard, allows multiple ALTER TABLE actions within a single query, simply comma separated

note: the GENERATED ALWAYS and VIRTUAL statements are not required, but i find them more explicit about your intentions. the AS automatically means that the column is generated based on other data, and generated columns are VIRTUAL by default (the opposite is STORED, which actually creates a column within the table that is used for the data)

note: in most cases, MySQL's query planner is smart enough to use the generated column's index to execute queries involving the fields, thus you don't have to worry about using these columns, or whether your ORM is aware of generated columns :)