So you want to store URLs in MySQL, and the URLs have those annoying "%20%27%7C%26%5E%2B%2D%25" symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:%20 - space%27 - '%7C - |%26 - &%5E - ^%2B - +%2D - -%25 - %So, how about we do some search'n'replace on that?mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';Query OK, 0 rows affected (0.00 sec)mysql> SELECT @url as original, -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> @test,'%20',' '), -> '%27','\\''), -> '%7C','|'), -- REPLACE() is case sensitive -> '%7c','|'), -- so we have -> '%26','&'), -> '%5E','^'),  []

The other day we began to encounter weird and random errors on small and innocent queries that shouldn't give any errors at all. It all lead to one of our most basic health checks failing for no apparent reason. The first clue that popped into our minds was related to case-sensitivity, since the failing check was looking for the column names of the only table in the schema with UPPER CASE name. This symptom was especially weird since all of this was happening on MySQL setups running on Windows, and so we're not sure if this was being caused by some internal code library, MySQL, or Windows itself.Since that was the only clue we then had, it seemed obvious that we should start fiddling with the lower_case_table_names system variable. After a couple of restarts, this approach was leading us nowhere, so we finally gave up on it. Then, we had the brilliant idea of actually executing that statement by hand on the command line to see  []

The other day, a client mentioned they were getting strange results when running "ALTER TABLE". The episode involved modifying an existing primary key to add an "auto_increment" "primary key": it was "shifting" values. Say what?! As it turns out, it was a very special value getting changed: "zero". Some fiddling revealed the underlying reason. Care to join me?To understand what's going on, follow the example below as we start with an empty database, create a table and insert a few rows:mysql> use test;Database changedmysql> show tables;Empty set (0.00 sec)mysql> create table test_table (id int not null primary key) engine=innodb;Query OK, 0 rows affected (0.01 sec)mysql> desc test_table;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) |  []

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the "information_schema" database, but it's scattered through several different tables.Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy's Law.In any case, I'd like to share with you a simple stored procedure that has helped us a lot in the past.

Hi All!This year, the International Free Software Forum celebrated its 10th anniversary. It happened last week in Porto Alegre.Pythian presented a session on Thursday called 8 Rules for Designing More Secure Applications with MySQL. As promised, here are the slides we used on that session: