Short Notes on MySQL - Revision history

Admin at 01:01, 23 April 2021

2021-04-23T01:01:52Z

Admin: /* The insert ... on duplicate key update Pitfall */

2014-02-02T00:45:44Z

‎The insert ... on duplicate key update Pitfall

Admin at 12:24, 23 December 2013

2013-12-23T12:24:30Z

Admin: Created page with "== 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..."

2013-12-20T14:49:29Z

Created page with "== 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..."

New page

== 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:
<pre>mysql> select User, Host, Password from mysql.user;
+-------------+----------------+-------------------------------------------+
| User | Host | Password |
+-------------+----------------+-------------------------------------------+
| root | localhost | [.................hash..................] |
| root | 127.0.0.1 | [.................hash..................] |
| | % | [.................hash..................] |
| ... |
+-------------+----------------+-------------------------------------------+</pre>
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.
<pre>show variables like 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | ON |
+-----------------+-------+</pre>

You can either set this variable to <tt>OFF</tt>, or comment out the setting in ''/etc/mysql/my.cnf'':
<pre># skip-networking</pre>
One more option is that your MySQL server binds only to local IP; under <tt>[mysqld]</tt> section in ''/etc/mysql/my.cnf'' comment out bind setting, or replace it with your public IP:
<pre># bind-address = 127.0.0.1
# or
bind-address = 123.1.2.3</pre>
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:
<pre>mysql> select User, Host, Password from mysql.user;
+-------------+----------------+-------------------------------------------+
| User | Host | Password |
+-------------+----------------+-------------------------------------------+
| ... |
+-------------+----------------+-------------------------------------------+</pre>
If it is (make sure both ''User'' and ''Host'' match, or ''Host'' is <tt>%</tt>), make sure it has rights granted:
<pre>show grants for 'dbuser'@'example.com';</pre>
If the user does not exist, or if it doesn't have rights granted, you can create and/or update it at once with:
<pre>grant all on db.* to 'dbuser'@'example.com' identified by 'password';</pre>
Of course, restrict the ''grant all'' to just rights required whenever possible.

Also, make sure to include the <tt>identified by 'password'</tt> 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):
<pre>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 | |
+-------------+----------------+-------------------------------------------+</pre>
Note that <tt>'dbuser'@'example.com'</tt> has empty password! It's just as possible that the hashes/passwords differ. To fix this, use
<pre>set password for 'dbuser'@'example.com' = PASSWORD('password');</pre>
After all this, you should be able to connect... hopefully.

'''Note''': You can also
<pre>flush privileges;</pre>
after updates to the user grants and passwords, but this is rarely necessary unless you're on a MySQL Cluster.

← Older revision		Revision as of 01:01, 23 April 2021
Line 85:		Line 85:
	** start ''mysql'' service,		** start ''mysql'' service,
	** restore databases from dump.		** restore databases from dump.
		+
		+	== Index JSON columns ==
		+
		+	MySQL does not 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'':
		+
		+	<pre>
		+	'data' column of 'book' table contains:
		+	{
		+	"title": "...",
		+	"author": "...",
		+	... other fields ...
		+	}
		+	</pre>
		+
		+	To alter the <tt>book</tt> table, you need to add 2 generated columns, e.g. to type <tt>VARCHAR(255)</tt>, and index these in the usual fashion:
		+	<pre>
		+	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);
		+	</pre>
		+
		+	''Note'': MySQL, unlike SQL standard, allows multiple <tt>ALTER TABLE</tt> actions within single query, simply comma separated.
		+
		+	''Note'': The <tt>GENERATED ALWAYS</tt> and <tt>VIRTUAL</tt> statements are not required, but I find it more explicit about your intentions.
		+	The <tt>AS</tt> automatically means that the column is generated based on other data, and generated columns are by default <tt>VIRTUAL</tt> (the opposite is <tt>STORED</tt>, which actually creates a column within 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 do not have to worry about using these columns, or whether your ORM is aware of generated columns :)

← Older revision		Revision as of 00:45, 2 February 2014
Line 70:		Line 70:

	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.		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 <tt>innodb_file_per_table</tt> setting in ''/etc/mysql/my.cnf'', under <tt>[mysqld]</tt> 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):
		+	** <tt>mysqldump</tt> 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.

← Older revision		Revision as of 12:24, 23 December 2013
Line 60:		Line 60:
	<pre>flush privileges;</pre>		<pre>flush privileges;</pre>
	after updates to the user grants and passwords, but this is rarely necessary unless you're on a MySQL Cluster.		after updates to the user grants and passwords, but this is rarely necessary unless you're on a MySQL Cluster.
		+
		+	== The <tt>insert ... on duplicate key update</tt> Pitfall ==
		+
		+	Some time back I had a problem - gaps in values in <tt>auto_increment</tt> column in MySQL table.
		+
		+	It turns out that when using <tt>insert ... on duplicate key update</tt>, the <tt>auto_increment</tt> counter is being incremented even if the row exists, and is only being updated.
		+
		+	If you want the <tt>auto_increment</tt> column to be contiguous, or simply don't wanna waste values available, you'll have to check if the row exists and use <tt>insert</tt>/<tt>update</tt> 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.