Category: MySQL

MySQL related posts

Ways to export MySQL result set to file on client side

MySQL

Problem: you wish to write table data to file, but you wish to do so on client side.

SELECT … INTO OUTFILE writes the file on server. What are your options on client?

1. mysql client

If you have direct access from your client machine to your DB server machine, and can connect via mysql client, you get a very customizable file write:

bash$ mysql -h db_host --execute "SELECT * FROM my_table" > /tmp/output.txt

The above writes fancy tables, so you probably want to:

bash$ mysql -h db_host --execute "SELECT * FROM my_table" --bat > /tmp/output.txt

Also try:

bash$ mysql -h db_host --execute "SELECT * FROM my_table" --silent --raw > /tmp/output.txt

To turn off headers.

More options include the –xml or –html formats. Continue reading » “Ways to export MySQL result set to file on client side”

Things that can’t (and some that can) be done from within a MySQL stored routine

MySQL

I’m doing a lot of stored routine programming lately, working on common_schema. I’m in particular touching at the extremes of abilities. Some things just can’t be done from within a stored routine. Here’s a list of can’t be done:

  • Cursor for SHOW statements: can’t be done — this is explicitly blocked from operating (it once used to work).
  • Get detailed error information on exceptions: apparently 5.6 has support for this. 5.1 and 5.5 do not.
  • Change binlog_format: this is obvious, if you think about it. binlog_format dictates how the routine itself is replicated in the first place.
  • Set sql_log_bin. Again, this makes sense.
  • Work out different results depending on current machine. For example, you can’t have a routine that returns with ‘master’ on the master and with ‘slave’ on the slave. That is, not under any condition. Consider: if Row Based Replication is used, you don’t actually have a routine executing on the slave. I’m happy to be proven wrong on this.
  • Know what database was in use by calling code. The routine executes within the context of the database where it is defined. But you can’t tell what database was in use just a couple milliseconds before.
  • Likewise, know what sql_mode was in use by calling code. Stored routines have their own sql_mode – the one they were created with. No way to check up on the calling stack.
  • And you can’t USE another database (database as in schema). USE is a client command.
  • Reconnect after failure (kind of obvious, isn’t it?)
  • Connect to other servers (not so obvious to SQL Server DBAs). You can’t issue queries on other servers. Bummer.
  • Shutdown the server
  • Fork (you’re in a connection, you can’t issue a new connection from your own connection)

Continue reading » “Things that can’t (and some that can) be done from within a MySQL stored routine”

MySQL error handling on server side: a NO GO!

MySQL

There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went wrong.

To illustrate, consider the following query:

INSERT INTO my_table (my_column) VALUES (300);

What could go wrong with this query?

  • We might hit a UNIQUE KEY violation
  • Or a FOREIGN KEY violation
  • my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
  • Or, similarly, it could be an ENUM (2,3,5,8)

Is that it? Not remotely:

  • This could be a read-only MyISAM table
  • We may have issued a LOCK TABLES my_table READ — this violates our lock
  • Or this could be an InnoDB table, and this INSERT would make for a deadlock
  • Or we have read_only=1 configuration
  • Or the user is not allowed access to this table
  • Or the table does not exist
  • Or the column does not exist

Or… I’m pretty sure there could be many other issues.

Now, if I write a Java program, perhaps using Hibernate, I get the error nicely wrapped up in a SQLException object, with easy access to error code and error message.

But can I have the same on server side? No.

Take a look at the following code: Continue reading » “MySQL error handling on server side: a NO GO!”

MySQL joins: ON vs. USING vs. Theta-style

MySQL

What is the difference between the following three syntaxes?

SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)
SELECT * FROM film JOIN film_actor USING (film_id)
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id

The difference is mostly syntactic sugar, but with a couple interesting notes.

To put names, the first two are called “ANSI-style” while the third is called “Theta-style”.

Theta style

On the FROM clause, tables are listed as if with Cartesian products, and the WHERE clause specifies how the join should take place.

This is considered to be the “old” style. It is somewhat confusing to read. Consider the following query: Continue reading » “MySQL joins: ON vs. USING vs. Theta-style”

Notes on row based replication

MySQL

MySQL’s Row Based Replication (RBR) succeeds (though not replaces) Statement Based Replication (SBR), as of version 5.1.

Anyone who is familiar with replication data drift — the unexplained growing data difference between master & slave — might wish to look into row based replication. On multiple servers I’m handling the change to RBR has eliminated (to the best of my findings) replication data drift.

This is easily explained, as RBR writes particular row IDs into the binary log. You no longer need to hope the statement

DELETE FROM my_table ORDER BY my_column LIMIT 100

acts deterministically on all servers (is my_column UNIQUE?). With row based replication the particular rows deleted on the master are then deleted on the slave.

Here are three notes on RBR: Continue reading » “Notes on row based replication”

DELETE, don’t INSERT

MySQL

Have just read INSERT, Don’t DELETE by Aaron Brown, and have some lengthy response, which is why I write this post instead of commenting on said post.

I wish to offer my counter thought and suggest that DELETEs are probably the better choice.

Aaron suggests that, when one wishes to purge rows from some table, a trick can be used: instead of DELETEing unwanted rows, one can INSERT “good” rows into a new table, then switch over with RENAME (but please read referenced post for complete details).

I respectfully disagree on several points discussed.

Lockdown

The fact one needs to block writes during the time of creation of new table is problematic: you need to essentially turn off parts of your application. The posts suggests one could use a slave – but this solution is far from being trivial as well. To switch over, you yet again need to turn off access to DB, even if for a short while. Continue reading » “DELETE, don’t INSERT”

A new MySQL backups temperature scale, with showers

MySQL

We’re used to Cold, Warm and Hot backups. This scale of three temperatures does not quite reflect the impact of backups on your MySQL database.

In this post I offer a new backup temperature scale, and (somewhat seriously) compare it with showers. Call it the backup shower scale.

A database backup is like a shower: the colder it is, the less time you want to spend doing it.

Cold

A cold backup requires taking your database down (i.e. stop the service).

Example: file system copy

This can work well for replicating slaves, which may not be required for normal operation. You take the slave down, turn off the service, make your backup, turn everything on again, and let the backup catch up. Just make sure its master has all the necessary binary logs.

A friend was staying at my place and was lecturing me on the benefits of cold showers; how it was good for your health. A couple hours later, preparing for bed, I hear him exclaiming from the bathroom: “Whaaaaa! There’s no hot water!” Continue reading » “A new MySQL backups temperature scale, with showers”

Who is hogging my MySQL connections?

MySQL

Got “too many connections” this morning. New attempts continuously abort. Every once in a while some slipped through, but overall behavior was unacceptable.

max_connections is set to 500, well above normal requirements.

Immediate move: raise max_connections to 600, some urgent connections must take place. But, this is no solution: if 500 got hogged, so will the extra 100 I’ve just made available.

So, who’s to blame? SHOW PROCESSLIST is so unfriendly at that. Wait. Didn’t I create that view in common_schema, called processlist_per_userhost? I wonder what it says…

SELECT * FROM common_schema.processlist_per_userhost;
+-------------+------------------+-----------------+------------------+---------------------+
| user        | host             | count_processes | active_processes | average_active_time |
+-------------+------------------+-----------------+------------------+---------------------+
| maatkit     | sqlhost02.myweb  |               1 |                0 |                NULL |
| rango       | webhost04.myweb  |               2 |                0 |                NULL |
| rango       | webhost07.myweb  |               8 |                0 |                NULL |
| rango       | sqlhost02.myweb  |              38 |                0 |                NULL |
| rango       | management.myweb |              35 |                0 |                NULL |
| rango       | webhost03.myweb  |              10 |                0 |                NULL |
| rango       | local01.myweb    |               8 |                0 |                NULL |
| rango       | analytic02.myweb |              11 |                0 |                NULL |
| mytop       | localhost        |               2 |                0 |                NULL |
| buttercup   | sqlhost02.myweb  |             451 |                5 |              0.0000 |
| replc_user  | sqlhost00.myweb  |               1 |                1 |         392713.0000 |
| replc_user  | sqlhost02.myweb  |               1 |                1 |          38028.0000 |
| root        | localhost        |               2 |                0 |                NULL |
| system user |                  |               2 |                2 |         196311.5000 |
+-------------+------------------+-----------------+------------------+---------------------+

Ah! It’s buttercup connecting from sqlhost02.myweb who is making a fuss. I knew that view was created for a reason.

Continue reading » “Who is hogging my MySQL connections?”

On stored routines and dynamic statements

MySQL

I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!

I’m dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.

Still hush hush, the development of that feature touched at the very same issues described in Michael’s post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a few insights of mine:

Internal array implementation

Like Michael, I started by implementing arrays through tables. That is, create a (temporary, in my case) table, wrap it up with a lot of stored routine code, and simulate an array. This array is not yet provided to the user, but is used internally for QueryScript’s own code.

Well, disappointment here: during load tests on intense structures, such as a foreach loop, where each iteration of the loop requires the creation of an array, I found that the current solution does not hold well on busy servers.

Seemingly, there’s nothing wrong with the creation of a new table every once in a while — and in particular a temporary table. However, I quickly found out that a busy server thrashes the table cache with such intense rate of creation/dropping of tables. The competition over the table cache mutex becomes intolerable and hogs not only the script’s execution but the entire server’s.

There’s also the issue of the type of array values — no going around using textual columns, of course, but — how long? A VARCHAR(32767) should be enough for any reasonable implementation, but — how much memory would that consume? Both MEMORY and standard temporary tables (Percona Server has that partially resolved) use a fixed row format, which means a 32K text is actually allocated in memory even when your value is ‘x’. Continue reading » “On stored routines and dynamic statements”

CHAR and spaces

MySQL

I know about it, I knew about it all along, but… it’s so easy to fall for it; there’s just so much absurdity!

A CHAR type has a known number of characters. For example, the column:

CountryCode CHAR(3) CHARSET ascii NOT NULL

– is known to have exactly three characters. These could be ‘USA’, ‘FRA’, etc.

What happens with spaces? A SQL CHAR type ignores any trailing spaces; thus, the code ‘GB ‘ (the characters ‘G’, ‘B’, and the space ‘ ‘) is interpreted as ‘GB’. Trailing spaces are not regarded as part of the text. Want to see some absurdity?

CREATE TABLE `c_test` (
  `c` char(1) DEFAULT NULL
);
INSERT INTO c_test VALUES ('a');
INSERT INTO c_test VALUES ('b');
INSERT INTO c_test VALUES (' ');

SELECT c, LENGTH(c), c = 'a', c = 'b', c = ' ', LENGTH(' ') FROM c_test;
+------+-----------+---------+---------+---------+-------------+
| c    | LENGTH(c) | c = 'a' | c = 'b' | c = ' ' | LENGTH(' ') |
+------+-----------+---------+---------+---------+-------------+
| a    |         1 |       1 |       0 |       0 |           1 |
| b    |         1 |       0 |       1 |       0 |           1 |
|      |         0 |       0 |       0 |       1 |           1 |
+------+-----------+---------+---------+---------+-------------+

Continue reading » “CHAR and spaces”