PHP Magazine

Today, I wanted to import quite a huge sql-dump, and got this error. What I was using was:

mysql --user=root -p dbName < sqlDumpName.sql

I was pretty sure about dump's integrity, so after receiving "Error 1064" decided to import using source command:

#mysql --user -p
mysql>use dbName;
mysql>source ./sqlDumpName.sql

This time import went through w/o any issues.

Today, when I wanted to backup my database with mysqldump I run into error I never received before:


mysqldump: Got error: 29: File './sampl/table.MYD' not found (Errcode: 24) when using LOCK TABLES


As it turned out this error simply means that mysql run out of file descriptors when locking tables – and this error happens only if you have many tables in your database (more than open_files_limit in your my.cnf).

It even has been reported as a bug, although it is not. To cure the problem, simply pass

--single-transaction

option to your mysqldump command.

If, however, you are getting this kind of errors during normal database operations – then the only solution is to increase open_files_limit in mysql configuration file.

Hope this saves you some time!

If you’ve ever installed some 3rd party software on you website (eg: phpbb forum), you certainly know that database tables, created during that process, generally have a convenient prefix in their names (default one for phpBB is phpbb_). This is done, I believe, to make sure that all of application’s tables get grouped together and do not mix with your previously created ones.

There are situations when you’d want to remove such grouped tables, all of them at once – for example, when, several days ago, I obtained our latest UMapper SQL-dump I, unfortunately, forgot to ignore forum tables, so they got into my localhost box. Today, I spotted those and wondered how can I quickly get rid of them. As I really like to work with MySQL via CLI, the question raised – how to remove multiple tables using single query.

At first, I tried to use LIKE clause the way it is used in SHOW TABLES:

mysql> SHOW TABLES LIKE "phpbb_%"; /* list all phpBB tables */
mysql> DROP TABLES LIKE "phpbb_%"; /* DROP THEM? Quite a naive approach.. */

Well, even before executing it I was 99% sure that this “method” wouldn’t work. Indeed, it didn’t

I certainly knew that you can drop several tables at once if you list all of them separated with commas:

mysql> DROP TABLE phpbb_zebra, phpbb_words; /* DROP several tables*/

So, I decided that it would be good enough if I form a query which would generate yet another query that would drop all similarly prefixed tables. If it’s not already, it would be crystal clear what I mean in a minute.

To get list of tables complying to certain criteria one could use information_schema database. To get list of all phpbb_ prefixed tables we can issue something like this:

mysql> SELECT GROUP_CONCAT(TABLE_NAME) 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "phpbb_%";

Result (line-breaks added for readability):

phpbb_acl_groups,phpbb_acl_options,phpbb_acl_roles,phpbb_acl_roles_data,
phpbb_acl_users,phpbb_attachments,phpbb_banlist,phpbb_bbcodes,phpbb_bookmarks,
SKIPPED
phpbb_search_results,phpbb_search_wordlist,phpbb_search_wordmatch,phpbb_sessions,
phpbb_sessions_keys,phpbb_sitelist,phpbb_smilies,phpbb_styles,phpbb_styles_imageset,

Going from here, the only thing we need is to prepend the result with DROP TABLE string:

mysql> SELECT 
           CONCAT(
                  "DROP TABLE ",  
                   GROUP_CONCAT(TABLE_NAME)
           ) AS stmt 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "phpbb_%";

Then, actually removing tables is a matter of copy-pasting the generated query.

That’s it! If you know a better way, please, share in comments!

P.S. It only occurred to me that I relied on two queries for doing the job, albeit second one was simply copy-pasted.

Answer is actually very simple, but several of my friends have missed the point of having size modifier in MySQL column type definitions, so I suppose it’s something non-obvious. Main confusion, I believe, has its roots in string data-type definitions:

VARCHAR(N)
CHAR(N)

where N is the maximum number of characters you want to store into field, i.e. it is field’s length. And depending on your settings you might receive an error (in strict SQL mode) when you are inserting something that exceeds the pre-defined length. Even if you don’t receive error or warning, the field is limited to the length you set, and the exceeding part is truncated. So, VARCHAR(20) and VARCHAR(40) are really different with respect to how big the stored value could be.

Now, let’s get back to numeric types. For numeric types length modifier is actually display width, which has nothing to do with what can be stored in field. Yes, that’s it – both TINYINT(1) and TINYINT(4) can store values in range -128..127 (or for unsigned values 0..255), and are absolutely identical datatypes, with one small twist (which concerns date retrieval – see below).

Here is explanation of what is meant by display width, taken directly from the source:

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

So, if display width doesn’t constrain the range of values our numeric type can hold, why to use it at all? On MySQL forum, there was a post suggesting that by using the display width, we set mental reminder to ourselves on how big the field should be. Me personally, find this quite logical, this is some kind of mental tooltip which pops up when you review your schema.

Of course, there’s a more deliberate functionality for having display width. One obvious example would be ZEROFILL attribute, which when used in datatype definition guarantees that the value returned would always have pre-defined width, and default space padding is replaced with zeroes. For example, when column is defined as INT(5) ZEROFILL, and value to be retrieved is 4, MySQL would return it as 00004.

So, the bottom line: for numeric types, N in TYPE(N) has nothing to do with value range, and refers to display width.

Have a nice weekends!

MySQL command-line client has an option for table names/fields auto-complete. Moreover, it is enabled by default (at least it says so in manual, on my Slackware box it wasn’t). This feature is called auto-rehash – when typing names you simply can hit TAB to see all possible alternatives for auto-completion (if there’s exactly one alternative, then auto-complete would occur without further intervention). Command-line folks would find this quite handy, indeed, if you are using CLI a lot, you just get used to hitting TAB key when in doubt.

So, if it’s not enabled in your client by default, you can enable this by issuing auto-rehash option when invoking the mysql client:

mysql -uroot -p --auto-rehash

Please note that you have to invoke use DBNAME at least once, since that’s the point when MySQL does hashing. Otherwise feature would refuse to do its work.

Of course, you can have auto-rehash (or no-auto-rehash for that matter) option in your ini file if you like:

[mysql]
auto-rehash

Particular drawback of auto-rehashing is slower start up time of client, since data has to be read and hashed for this functionality to work. If that troubles you, I have a good news: you do not have to use –auto-rehash option on start up at all, and you can still get auto-completion, even if –no-auto-rehash option was used. It’s quite simple, just type one of

\#
or
REHASH

in your MySQL client and rehash would occur, allowing you to utilize auto-complete. Personally, I prefer this alternative if my interactions with database are not so often, otherwise I still think that starting with –auto-rehash is easier, and speed penalty is not that big.

На самом деле проблема встречается очень даже часто:

Дано две таблицы (tableA, tableB), в которых присутсвует семантически эквивалентная колонка, для простоты предположим что поле id, таблицы A, указывает на поле uid, таблицы B (простейший внешний ключ). Далее предположим нам нужно получить список пользователей из таблицы А, которые не представленны в таблице B. Как это сделать? Легко! Используем LEFT JOIN (обратите внимание на IS NULL):

SELECT a.* FROM tableA a LEFT JOIN tableB b ON a.id = b.uid WHERE b.uid IS NULL

Удачного дня!