Column names with trailing spaces in tables and views

Just found out a bizarre feature of MySQL: you simply cannot create a table with a trailing space in a column name. Something like this, doesn’t work:

create table a (`a ` int);
ERROR 1166 (42000): Incorrect column name 'a '


There’s a thread in MySQL’s lists explaining it

Ok, it seems there’s a technical limitation but this solution seems like a lazy workaround to me.
The problem is with CREATE VIEW. In recent MySQL builds you cannot either create a view with a trailing space in a column name. But recent means, at least, greater than 5.0.51a, cause this version DOES let you create a view with a trailing space.

# mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

mysql> create view b as select a `a ` from a;
Query OK, 0 rows affected (0.00 sec)


There’s a big problem here: 5.0.51a is the default and supported version in Debian Lenny! So, if you have Lenny as a master of a more recent MySQL installation, be careful cause a CREATE VIEW with a trailing space in a column name will break your replication. Grrrr. MySQL, I hate you, really.

MySQL slaves and the “corrupted” relay log/binlog problem

When trying to pull up to date a new slave, maybe using a binlog server (a MySQL server with no true tablespace, just acting as master for slaves, feeding them with replication data), you may encounter an error like this in your mysql.err file, after noticing the replication has just broken

091028 11:32:49 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
091028 11:32:49 [Warning] Slave: Field 'owner' doesn't have a default value Error_code: 1364
091028 11:32:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysqld-bin.000780' position 786513508


Well, everything on the web points to a “OMG, my file is corrupt!! My disk is freaking out!”. Probably, instead, it’s just simpler: you have forgotten a FLUSH LOGS; before backing up your binary logs from the original master. So, when you are recreating the binlog server from the backup, the final slave arrives to the last statement wich obviously is not complete/correct and booom, it stops.

The solution is to copy the correct and complete binlog file (in my case mysqld-bin.000780) again in the binlog server, then issue a
STOP SLAVE;
CHANGE MASTER TO master_log_file="mysqld-bin.000780", master_log_pos=786513508;
SLAVE START;


and… problem solved!
(remember to adjust file name and position to suite your needs)

Lesson learned (once again): always test your backups and do not hesitate to flush with MySQL :)

HOWTO: Install Mysql 5.1 for SPARC64 under Debian Lenny

If you happen to own a SPARC64 box, you’ll probably already know that even if the kernel is 64bit the userland comes from the normal SPARC Debian port, so it’s 32bit. Mysql is no exception, with all the 32bit limitations – mainly the 4GB RAM per process limit.

This is really  a PITA because if you have a SPARC64 box probably it has got plenty of RAM and you want to use it at its full potential, without having to messing around with Solaris (yeah, I don’t like it very much, I’m sorry).

This guide covers Mysql 5.1 installation in Debian Lenny, so we have to use SID repositories.


# echo "deb http://ftp.de.debian.org/debian/ sid main" >> /etc/apt/sources.list
# echo "deb-src http://ftp.de.debian.org/debian/ sid main" >> /etc/apt/sources.list


then let’s edit our apt preferences to avoid massive update on next dist-upgrade :)

# vim /etc/apt/preferences
Package: *
Pin: release a=stable
Pin-Priority: 900
Package: *
Pin: release a=sid
Pin-Priority: 100


and then update our repo list

# aptitude update


And here we go:

# apt-get build-dep mysql-server-5.1
# mkdir /tmp/mysql-build; cd /tmp/mysql-build
# apt-get source mysql-server-5.1
# vim mysql-dfsg-5.1*/debian/rules


here we touch a little the rules for compiling cause there are a couple of things that are not going to work by default.

The MAKE_J variable doesn’t work very well, so you can modify the grep to look for “CPU” instead of “processor” or you could hardcode it to the number of processor you have. This will make compilation a lot faster.

MAKE_J = -j$(shell if [ -f /proc/cpuinfo ] ; then grep -c CPU* /proc/cpuinfo ; else echo 1 ; fi)


then edit the CFLAGS variable because it’s used to compile some library that will ignore the environment variables we are going to set later in this howto.

CFLAGS=$${MYSQL_BUILD_CFLAGS:-"-O3 -DBIG_JOINS=1 -m64 -mcpu=niagara2 ${FORCE_FPIC_CFLAGS}"} \

it should be about line 73. Please note that -m64 will make it 64bit so it’s mandatory while the mcpu flag it’s to optimize the executable for your CPU. In my case it’s a niagara2 chip but you can use another CPU as well. Check the GCC documentation for more details
Save and quit and then we can start with the compilation process:

# export CFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export CXXFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export CPPFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export LDFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export DEB_BUILD_OPTIONS="nocheck"
# debuild -us -uc --preserve-env


that’s it. After some minutes (depending on your HW), you should have in /tmp/mysql-build all your new DEBs which you can install with dpkg -i. I advice to install the stock mysql-server-5.1 with aptitude before to get all dependencies installed, then you can use dpkg with your new DEBs.

Mysql and integers: INT, TINYINT and all that jazz

Today I stumbled on a discussion here where I work about what’s the best INT field in Mysql to represent boolean values and about what’s the real meaning of the *INT(number) definition.

So, I ended looking in the online Mysql manual for answer but also in the “High Performance MySQL 2nd edition” written by worldwide-fame MySQL hackers (and published by O’Really).

To resume, as Mysql Manual says: BOOLEAN is an alias for TINYINT(1), so you can use both, although BIT could be a better suited solution.

But what about the parenthesis thingie? Here there are two different opinions on the matter. According to High Performance Mysql’s authors (page 82, emphasis’ mine):

MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools (such as commandline client) will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20)

but now let’s look at this other page in Mysql Manual (emphasis mine):

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.

Now…who’s right? I use to trust in the Percona & OpenQuery crew but anyway the official Mysql Manual seems pretty clear about some cases in which the INT(x) value is important.
Any idea?

Kill every MySQL SELECT older than X seconds

If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar.  Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…

So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.

It’s rather quick’n'dirty, but for it’s small use case, it works. It expects GNU userland


#!/bin/bash
SEC=$1
IFS='|'
if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid
fi
done


Tips learnt in Mysql HA classes

Replication

• the --read-only switch (or read_only in my.cnf) it’s the best way to block unwanted writes in a replication slave. So, ditch your specific user’s privileges :)
• log-slave-updates in my.cnf needs underscores _ because with normal dashes doesn’t work at all (at least with Mysql 5.1.22). So, it’s log_slave_updates
• Talking about log_slave_updates again, it’s fundamental if you’re mounting a circular replication system. Without it, replication will stop at first hop.
• SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n could be usefull if you have to skip on a replication error. Be careful anyway because it can lead to logical inconsistences in your data if abused.

Cluster

• When starting up data nodes using ndbd, you can add the --nostart flag to have the process in memory without doing nothing. Then, you can tell the node to actually start working from the management console with ID_node start. Moreover, you can put the nostart flag in your config.ini, in the [ndbd default] section, if you want the change to apply to every node.
• in the management console, as said, you can start (or stop, or restart) the data nodes manually, but if you try with the SQL nodes, you’ll get a strange error. That’s because SQL nodes can only be restarted from the local machine, as it were a normal mysql installation (in fact, it is a normal mysql install :)

Mysql Proxy

• If you are trying to execute mysql-proxy with a LUA script, and you have the scripts tree in /usr/share/mysql-proxy, then you have to issue a command like this, otherways mysql-proxy will complain about LUA includes that cannot be found:
  LUA_PATH=/usr/share/mysql-proxy/?.lua mysql-proxy --proxy-backend-addresses=192.168.45.112:3306 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

Copy a table with MySQL

Hello,

If you want to copy all content from one table to another table with the same structure and data, it’s easy using MySQL syntax.

mysql> CREATE TABLE new_destination_table SELECT * FROM source_table;

It will create a new table with the same content of the source table.

See you!

EDIT: As Arjen Lentz said in his blog, this is not the best way to copy a table in Mysql. So, ignore this post if you don’t need to copy just the simple structure without indexes :)

HOWTO: Install recent Mysql Community 5.0.x in Debian Etch

EDIT: If you want to upgrade a 5.0.x installation on Etch, forget this howto and use the packages provided by http://www.backports.org which is the official way to do it. You may still use this howto as an inspiration if you plan to install Mysql 5.1.x, until it get backported ;) (although I doubt it will ever be).
Anyway, for the lazy:

• Add this line to /etc/apt/sources.list

deb http://www.backports.org/debian etch-backports main contrib non-free

• Then execute these commands:
  apt-get update
apt-get -t etch-backports install mysql-server

---

THIS IS DEPRECATED!!If you install a Debian 4.0 (Etch) system and you do an apt-get install mysql-server you will install an old 5.0.32 version. So, since we know there are a couple of critical bugs fixed in more recient versions (solving for example scalability issues on SMP machines), you may want to install a more recent version from the official Mysql Community web site. This link will let you download a dinamically-linked RPM which needs glibc 2.3, which is the version we have in our Etch system.After the download, install the Alien tool
apt-get install alienand then magically convert the RPM to a DEB package withalien --scripts MySQL-server-5.0.*.glibc23.i386.rpmand in a couple of minutes you will have a new mysql-server_5.0.45-1_i386.deb (for example, the number depends on the Mysql version you have downloaded and the architecture you are using).Now, let’s install the old, default Debian mysql version, with

apt-get install mysql-server
that will install mysqld in the Debian way and create all the things it needs.

WARNING:
Before proceding furthermore, we have to remove the mysql-server-5.0 package with

apt-get remove mysql-server-5.0

This is needed because otherwise when upgrades to this package appear in the official Debian repository, you will be forced to install them (if you’re not using pinning) even if you don’t need them. Why? Because the mysql-server package that we’re replacing in this howto is only a meta-package for mysql-server-5.0 which is in Debian the real package. I’m working on a better solution, I’ll keep you informed.

Now, with the deb we have just generated with Alien, we can update the Debian installation, since they both provide the same package (mysql-server)

Install it with:

dpkg -i --force-overwrite mysql-server_*i386.deb

the –force-overwrite is needed because there are files in conflict between the Debian meta-package mysql-server-5.0 and the deb we are installing right now. This could be solved modifying the manifesto of our debian package telling the system that our deb provides a mysql-server-5.0 installation or, since the original RPM is LSB-compliant, with this force-overwrite option.

Now, let’s stop the mysql daemon just a moment

/etc/init.d/mysql stop


create this symlink (because the RPM init script will look for /etc/my.cnf)

ln -s /etc/mysql/my.cnf /etc/my.cnf


and then let’s start again Mysql

/etc/init.d/mysql start

and that’s all, it should work (at least it worked for me).

Mysqldump UTC timezone workaround

Hello,

Today We needed to use mysqldump to dump lots of tables from one database to another, but seeing

select COUNT(*) from table where date_time < ‘2007-08-30 23:59:59′;

New copied table says 18 extra rows, so ’something is wrong’.

Our mysqldump was something like:

hostbackup$ mysqldump dumpoptions -w date_time < ”2007-08-30 23:59:59″ | mysql options

Reading the mysqldump manual, it tells that it uses UTC by default, so the only way to disable it is using “–skip-tz-utc” option.

Using mysqldump ‘–skip-tz-utc’ works as expected, since we do not use UTC., we use CET instead.

The complet bash script, if someone want use it :)

#!/bin/sh
# default path
PATH=/bin:/usr/bin
# tables
tables="table1 table2 table3 etc"
# mysqldump & mysql options
db="database"
rhost="remotedatabasehost"
lhost="localhost"
user="ouruser"
pass="ourpass"
dumpopts="--skip-tz-utc --insert-ignore --quote-names --no-create-info --host=${rhost} --user=${user} -p${pass}"
lsqlopts="-h${lhost} -u${user} -p${pass}"
rsqlopts="-h${rhost} -u${user} -p${pass}"
# current time
datesql="`date '+%Y-%m-%d'` 00:00:00"

for table in $tables; do
  echo "dumping $table ..."
  if mysqldump $dumpopts -w "fecha_hora < '$datesql'" $db $table | mysql $lsqlopts $db; then
    echo "table $table was dumped :)"
  else
    echo "$table not dumped!"
  fi
done

See you!