Skip to content

Dude, where is my memory?

Posted by Kristian Köhntopp on
"Kris, please have a look at our database. We are running a materialized dataset processor, and on a database with about 40 GB of buffer pool and a database size of 6 GB, we are observing swapping."

Now, that is interesting. The box in question has 48 GB of memory, and indeed, hardly 6 GB of data.

CODE:
mysql> select 
 -> sum(data_length+index_length)/1024/1024/1024 as gb 
 -> from tables 
 -> where table_schema not in ('information_schema', 'performance_schema', 'mysql');
+----------------+
| gb             |
+----------------+
| 5.832778930664 |
+----------------+
1 row in set (0.00 sec)

Yet, at the same time in "top", and growing:
CODE:
 7552 mysql     15   0 55.1g  43g 6888 S  0.7 91.7 499:13.56 mysqld 


That's going to be interesting!

Continue reading "Dude, where is my memory?"

CREATE TEMPORARY TABLE

Posted by Kristian Köhntopp on
If you have a slave, that slave is probably running with the read-only flag set in the mysqld-section of your my.cnf. To be able to write to a read-only slave you need to be the replication SQL_THREAD or have SUPER privilege.

Since 5.0.16, it is still possible to execute CREATE TEMPORARY TABLE on a read-only slave, so CREATE TEMPORARY TABLE privilege also allows you to write to a read-only slave in a limited and controlled way.

If you want to process a lot of data in a temporary table, you are probably creating the temporary table without any indices, then INSERT ... SELECT data into it, and then ALTER TABLE ... ADD INDEX afterwards, because that is usually faster than to insert data into a table with indices. Only that you cannot ALTER TABLE a temporary table, even on a server that is not read-only - in order to run ALTER TABLE on any table, even temporary onces, you need ALTER TABLE privilege which you might not want to give out lightly.

There is no reason at all to check ALTER TABLE privilege for an alter table operation on a temporary table, because that table is visible only to your connection and cannot be shared. It is also deleted when you disconnect. In fact there is no reason at all to check permissions for temporary tables. But it is done.

Because it is done, you can grant ALTER TABLE to a single table that does exist or even to a table that does not yet exist. If you grant ALTER TABLE privilege to an existing table on a read-only server, you cannot alter that table, because the server is read-only. If you then use CREATE TEMPORARY TABLE to create a table with that name, the temporary table will shadow the existing persistent table and for your connection the persistent table will become inaccessible.

The semantics of the GRANT will change, though, and will now apply to the temporary table, which is writeable on a read-only server because it is temporary, and is alterable because of the grant which was not meant to apply to it in the first place. Problem solved: I now can ALTER TABLE my temporary table on the read-only server after I have finished my data load.

All is well? Not!

There are multiple things at work here which I consider broken:
  1. GRANTs are applied to temporary tables. This is not making any sense at all in my book. Temporary tables are connection-local objects and they cannot have grants applied to them which were always referring to persistent objects when they were made.
  2. Temporary tables can shadow persistent tables in the namespace of a connection. Because GRANTS are tied to objects via the objects name and not an objects UUID or another form of truly unique object-identifier, GRANTS can refer to changing objects even when the grant does not change. This feels somehow broken, as in "not properly normalized". Does RENAME TABLE edit grant tables as well? I have to check!
  3. By granting CREATE TEMPORARY TABLE privilege to a user I am allowing that user to shadow any other object within a schema. The temporary table will then pick up any rights granted to the shadowed object for the duration of its lifetime. This cannot be good.

sysdate() weirdness

Posted by Kristian Köhntopp on
The behaviour of SYSDATE() has changed between MySQL 4.1 and 5.0. The manual indicates:
Within a stored routine or trigger, SYSDATE() returns the time at which it executes, not the time at which the routine or triggering statement began to execute. This differs from the behavior for NOW().
Obviously, this was implemented internally by having SYSDATE() ignore SET TIMESTAMP, as can be easily tested.

Continue reading "sysdate() weirdness"

AUTO_INCREMENT Trivia

Posted by Kristian Köhntopp on
MySQL (MyISAM) exhibits strange and little known (but documente) behaviour when dealing with compound keys where a part of the compound is AUTO\_INCREMENT.

This is as everybody knows:

mysql> create table standard ( name varchar(20), i integer not null auto_increment primary key );
Query OK, 0 rows affected (0.18 sec)

mysql> insert into standard ( name ) values ( "kris" ), ("azundris"), ("kris"), ("azundris");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from standard;
+----------+---+
| name | i |
+----------+---+
| kris | 1 |
| azundris | 2 |
| kris | 3 |
| azundris | 4 |
+----------+---+
4 rows in set (0.00 sec)

Continue reading "AUTO_INCREMENT Trivia"