~ overflow ~

Problem:

MySQL InnoDB ibdata file size never shrinks no matter if you deleted a big database / table or whatever.

Solution:

There is no easy solution for this, according to mysql this is supposed to be a bug, however they never fixed it since, apparently, there’s not many people around deleting big tables / databases.

Looking around, someone suggested that recreating the whole dataset would help fixing the issue, since we won’t be having trash on the innodb ibdata file.

Recreating the whole dataset is a extremely time consuming task, you would need to do a dump of every database, completly delete mysql data folder, create a new one with defaults, and inject the dump file. Just for example, on my dev machine i run about 100 different databases the total size is around 8GB, innodb was 20GB, wasting a lot of space on my precious SSD, so dumping everything, doing backups and injecting the whole data took over 8 hours, which is something nobody can take on a production server.

After some more research I’ve found about a specific configuration switch:

innodb_file_per_table

This is what you should have on your my.cnf or my.ini into the innodb branch. Instead of creating one big innodb ibdata for every single innodb table across all databases, this switch will create a `.ibd` file for each of the the innodb tables. Those files will be stored inside the database folder, so if you eventually have a greatly sized table you just need to drop it and recreate the single table to avoid gigantic innodb .ibd files instead of doing a full database recreation.

If you don’t have time to recreate everything you can just add `innodb_file_per_table` into the config, so new tables will be created using the new scheme.

The only con i can think about this switch is because you will have one extra file that will have to be seeked and will incrase the overhead when doing table joins and so on, although the disk overhead should be minimal, if you have thousands of tables into the same db it might make some difference, if you relay on speed you should do some benchmarks to make sure.

Bug Tracking:

bugs.mysql.com

Problem:

You need to find a entry or a specific value that’s into a table from a database, but you don’t know what is the table neither the database for sure.

Solution:

Use my happy mysql full database search script.
It’s so fun when all you gotta do is type a single command.

download mysql full database search script

Problem:

A supposed plain query like:

select
	pacote.*,
	users.id as uid,users.usr,
	group_concat(content.nm separator '|') as fnm,
	group_concat(historico.mid separator '|') as fmid
from
	pacote
	join historico on pacote.id=historico.pid
	join content on historico.mid=content.id
	join users on users.id=historico.uid
group by historico.pid,pacote.id
order by pacote.id desc

takes 15s to run, only because of the order by, assuming that each of the involved tables have at least 100,000 entries and pacote.id is the primary key.

Solution:

I’ve searched a lot but there’s no way to optimize this, limits will not help due the `order by`, having this query un-merged in many others will get worst results, having it without join will get about the same processing time (a bit slower in long run).

So, the rest of options were quite bad:
1. Remove crap rows out the tables
2. Get new hardware?!

Although pacote.added will not match pacote.id 100%, it will match 99.9%, so why not use the bare sorting (without order by mysql will output the rows in primary key order) ?

This would require a little coding to have it right:

So we have the same query as above WITHOUT order by:

select
	pacote.*,
	users.id as uid,users.usr,
	group_concat(content.nm separator '|') as fnm,
	group_concat(historico.mid separator '|') as fmid
from
	pacote
	join historico on pacote.id=historico.pid
	join content on historico.mid=content.id
	join users on users.id=historico.uid
group by historico.pid,pacote.id

(0.1s avg)

now on php:

// some vars
define("_ipp",20); // items per page
$x['page']=0; // current page

// do the query (assuming that $sql is the query above)
$res=mysql_db_query($db,$sql);

// build an array with ALL results
for ($rez=array();$r=mysql_fetch_array($res,MYSQL_ASSOC);$rez[]=$r) { }

// cut the array from the end (note minus signal)
$rez=array_slice($rez,-(intval($x['page']+1)*_ipp),_ipp);
krsort($rez); // invert array, simulating order by's DESC

foreach ($rez as $val) {
    // do things
}

(4.25s avg)

So the php solution is far faster than the mysql order by, although it’s not accurate as, it really solved the issue.

Problem:

How to copy a table into another, like a fast backup of a single table.

Solution:

MySQL:

create table <destination> select * from <source>

MSSQL:

select * into <destination> From <source>

Notes:

Although primary keys are preserved, the primary key column will not be automatically set on the destination table, so watch out before start inserting content on the destination.

Problem:

Server got stuck and had to be manually rebooted, some mysql content might be corrupted.

Solution:

I had an old script to check it file by file, however, i found out that MySQL 3.23.38+ got mysqlcheck addition which basically does all the work.

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Notes

This might take a long time depending on how big is your data source, although you can run it `on-fly` (while database is actually being used) you may have a slow down on all processes depending on that database/table while it’s being worked on. It also may potentially destroy partially recoverable data, so use –auto-repair with care.

Problem:
MySQL has reached max connections and you can’t login, even being root.

Solution:
You can either edit my.cnf raising the limit and logging in or you can do this hack:

gdb -p $(cat /var/run/mysqld/mysqld.pid) \
-ex "set max_connections=5000" -batch

What’s the difference?

By changing my.cnf you will need to restart the server, if you got the limit reached long time ago lots of queued connections will instantly flood the server once you reset it, making impossible to access it within a few seconds. So you will need to keep restarting it over and over again until all the queue is gone. In some cases you can’t even restart it, server gets stuck trying to close connections, then you need to kill mysqld, but that can generate broken tables. Sometimes only a full system restart fixes the issue.

By changing the value with the hack, you will be able to raise the max connections without restarting mysqld, which allows you to login and check what’s going on, even run some scripts to clean up idle/stuck connections, making things easier.

Problem:

MySQLd 5.0.18 (and some others) will bug on queries with more than 128 joins.

The error issued has nothing todo with the limit, and there’s no var to raising this limit, this is a mysql bug for this specific version.

Solution:

Upgrade.

Sources:

bugs.mysql

Problem:

When importing a .sql with entries with accents, like not regular english, it may lead to issues, like:

‘São Paulo‘ instead of ‘São Paulo‘

Solution:

Even mysqld default charset being latin1, sometimes it don’t work with accents, depending on the imports you’re doing.

So you may need to force it to fallback to utf8, on my case i just added this to the beggining of the .sql file i was importing:

charset utf8 \c

and it worked just fine.

Note: If you are using asian chars (japanese/chinese specific), then utf8 might not be enough to cover all chars.

Problem:

How to reset a auto_increment / identity value on a table?

Solution:

MySQL: alter table tablename auto_increment=1234;

MSSQL: dbcc checkident(‘tablename‘,RESEED,1234);

Since i keep forgetting this I will post as reminder:

show procedure status;

Lists procedures stored on current database.

show create procedure database.procedure_name;

Shows the source of the stored procedure.

grant execute on procedure database.procedure_name to username;

Grants execute privileges to the specific procedure and user.

Those are somehow obscure functions on mysql documentation, I think that they are still improving the procedures usability.