Ok. So it all begins with somebody who is using INSERT ON DUPLICATE KEY UPDATE. That guy wants to count the number of UPDATE actions that statement has taken, as opposed to INSERT actions.
We could have been using mysql_info() to fetch that information. But instead we rig the UPDATE clause:
CODE:
root@localhost [kris]> create table t (
id integer unsigned not null primary key,
d integer unsigned not null
) engine = innodb;
Query OK, 0 rows affected (0.16 sec)
root@localhost [kris]> insert into t values ( 1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [kris]> set @x = 0;
Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> insert into t values (4,4), (2,1), (3, 1)
-> on duplicate key update
-> d= values (d) + 0\* ( @x := @x +1 );
Query OK, 5 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0
root@localhost [kris]> select @x;
+------+
| @x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Wonderful side effects! And this is only the beginning.
Continue reading "Down the dirty road"
This is the translation of an
article from my german language blog. It is not a literal translation, but has been amended and changed a bit to take more recent information into account.
It started out as a discussion within the german language MySQL group in USENET. There the eternal question came up why phpMyAdmin gets no love at all from the helpers and regulars in that group. My answer was:
phpMyAdmin (PMA) like many other GUI tools for MySQL has a number of limitations. For a web tool such as PMA these come from its operating principles and can hardly be changed. But let's start at the beginning:
In MYSQL the connection is a special context or scope for many things. At least the following things are part of the connection scope:
- Transactions. A disconnect implies a ROLLBACK.
- Locks. Transactions generate locks with writing statements or SELECT for UPDATE. Table locks are generated by LOCK TABLES. Disconnect releases the locks.
- The number returned by LAST_INSERT_ID() is cached within the connection context. It is unavailable after a disconnect.
- Tables created by CREATE TEMPORARY TABLE. They are deleted on disconnect.
- Prepared Statements and the parsed bytecode of stored routines are kept per connection, even if that is the wrong solution from a performance POV.
- @-variables. SET @bla = 10 or SELECT @bla := count(*) FROM cookie; are defining variables within the context of a connection. They are lost on disconnect.
- SESSION-parameters. SET SESSION mysiam_sort_buffer_size = 1024*1024*64 or SET @@session.myisam_sort_buffer_size = 1024*1024*64 are setting configuration parameters with session scope. They are lost on disconnect.
- Replication specific SET-commands such as SET TIMESTAMP or SET LAST_INSERT_ID can affect the behavior of functions such as SELECT NOW() or SELECT LAST_INSERT_ID().
I am calling all of that connection scoped state.
A client that is disconnecting in an uncontrolled way or that does not report a disconnect properly is defective in the sense that all functionality dependent on connection scoped state is unavailable. The opposite case also exists - a client that reuses existing connections may have connections that have an unclean state, which may or may not be a security risk.
Continue reading "Connection Scoped State in MySQL"
In MyISAM, we do have
LOAD INDEX INTO CACHE. In InnoDB this does not work. For benchmarking I often require a way to preload the innodb_buffer_pool with the primary key and data after a server restart to shorten warmup phases.
According to Blackhole Specialist Kai, the following should work:
CODE:
mysql> create table t like innodbtable;
mysql> alter table t engine = blackhole;
mysql> insert into t select * from innodbtable;
Another win for the unbreakable BLACKHOLE storage engine.
Many maintenance procedures need to generate SQL as strings, and then need to execute that string as SQL. Use PREPARE for this.
CODE:
CREATE DATABASE `admin`;
USE admin
CREATE PROCEDURE `execute`(in cmd text)
SQL SECURITY INVOKER
begin
set @x = cmd;
prepare x from @x;
execute x;
drop prepare x;
end
(as requested on Freenode:#mysql)
Frank asked in
Can MySQL triggers update another table just that.
Here is how to have a users table with a summary field, and a detail table with a value field. When records are inserted, modified or deleted with changing values in the detail table, the matching summaries in the users table are updated.
Continue reading "Triggers maintaining summaries"
Last week I was with a customer and came across a side problem that was of a more general nature. It is the problem of users having a number of things that use up ressources. The things may be mails, recordings, stamps or whatever - for simplicity we assume recordings, and the ressource consumed is size. The system has a quota system in place, which stores a ressource limit per user.
The quota problem is: if a user is over quota, we want to get a number of item ids to delete per user from old to new, until that user is under quota or just one item over quota. This is the quota problem, and I needed a quota query that solves it.
Here is the setup.
Continue reading "The Quota Query and Running Sums by Jan and Kai"
The
sieve of
Eratosthenes finds prime numbers by creating a list of numbers, taking the smallest number in this list that is not stroken out and striking out all of its multiples, then going on to the next number that is not stroken out and so on.
For example, if the starting list is (2, 3, 4, 5, 6, 7, 8, 9) (we do leave out 1, because it is not a prime), then the first number from the list is 2, and therefore 4, 6 and 8 cannot be primes and are removed from the list: 2 is prime, and the list is now (3, 5, 7, 9). Lowest number from the list is 3, and therefore 6 and 9 cannot be primes, leaving (5, 7) and so on.
Continue reading "Exploring stored procedures: Eratosthenes"
At work, I came across an interesting problem involving graphs for which I found no completely satisfactory solution.
A colleague tried to model applications and their dependencies as a directed graph. When editing dependencies for a given node, he wanted to show only nodes as new possible descendants which are not yet direct descendants of the current node. Additionally, when selecting new parents, nodes that are already direct parents should not be shown. Since he is still on MySQL 4.0, subselects could not be used.
Here is the data model and a bit of test data:
Continue reading "A graph problem"
The most recent MySQL server I used in a
production environment was a 3.23, so in conjunction with reading the 5.0 sources, I saw fit to reread large parts of the
documentation to see where updates had happened, fixes were applied, extensions added. Here are some random clippings from the docs that illustrate quirks, changes from MySQL 3.23 (other than those noted in
3.23->4.0,
4.0->4.1,
4.1->5.0 which we'll give a brief tour of at the end) and differences from the standard. If you recently read the docs, kindly walk on, there's nothing to see here. If being a MySQL admin is #96 on the list of who you are, stand by for some trivia.
Continue reading "A day in the docs"
By chance, I came across this interactive
interactive SQL tutorial — select your preferred engine, create queries for a variety of exercises, see the results online immediately. Instructive for the beginner, possibly fun for the advanced.
: )
