Find the collation of all tables within a mysql database

Needing to find a collation of a mysql database for a migration I went to google. Found several options, but the best was one from stack overflow.


SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS;

This will output something like:


mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
 -> FROM INFORMATION_SCHEMA.COLUMNS;
+---------------+--------------------+---------------------------------------+-------------------------------+-------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+---------------+--------------------+---------------------------------------+-------------------------------+-------------------+
| NULL | information_schema | CHARACTER_SETS | CHARACTER_SET_NAME | utf8_general_ci |
| NULL | information_schema | CHARACTER_SETS | DEFAULT_COLLATE_NAME | utf8_general_ci |
| NULL | information_schema | CHARACTER_SETS | DESCRIPTION | utf8_general_ci |
| NULL | information_schema | CHARACTER_SETS | MAXLEN | NULL |
| NULL | information_schema | COLLATIONS | COLLATION_NAME | utf8_general_ci |
| NULL | information_schema | COLLATIONS | CHARACTER_SET_NAME | utf8_general_ci |
| NULL | information_schema | COLLATIONS | ID | NULL |
| NULL | information_schema | COLLATIONS | IS_DEFAULT | utf8_general_ci |
| NULL | information_schema | COLLATIONS | IS_COMPILED | utf8_general_ci |
| NULL | information_schema | COLLATIONS | SORTLEN | NULL |
| NULL | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | COLLATION_NAME | utf8_general_ci |
| NULL | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | CHARACTER_SET_NAME | utf8_general_ci |
| NULL | information_schema | COLUMNS | TABLE_CATALOG | utf8_general_ci |
| NULL | information_schema | COLUMNS | TABLE_SCHEMA | utf8_general_ci |

[...]

| NULL | information_schema | VIEWS | SECURITY_TYPE | utf8_general_ci |
| NULL | information_schema | VIEWS | CHARACTER_SET_CLIENT | utf8_general_ci |
| NULL | information_schema | VIEWS | COLLATION_CONNECTION | utf8_general_ci |
| NULL | test_db_wp | wp_commentmeta | meta_id | NULL |
| NULL | test_db_wp | wp_commentmeta | comment_id | NULL |
| NULL | test_db_wp | wp_commentmeta | meta_key | utf8_general_ci |
| NULL | test_db_wp | wp_commentmeta | meta_value | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_ID | NULL |
| NULL | test_db_wp | wp_comments | comment_post_ID | NULL |
| NULL | test_db_wp | wp_comments | comment_author | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_author_email | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_author_url | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_author_IP | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_date | NULL |
| NULL | test_db_wp | wp_comments | comment_date_gmt | NULL |
| NULL | test_db_wp | wp_comments | comment_content | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_karma | NULL |
| NULL | test_db_wp | wp_comments | comment_approved | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_agent | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_type | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_parent | NULL |
| NULL | test_db_wp | wp_comments | user_id | NULL |
| NULL | test_db_wp | wp_links | link_id | NULL |
| NULL | test_db_wp | wp_links | link_url | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_name | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_image | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_target | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_category | NULL |
| NULL | test_db_wp | wp_links | link_description | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_visible | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_owner | NULL |
| NULL | test_db_wp | wp_links | link_rating | NULL |
| NULL | test_db_wp | wp_links | link_updated | NULL |
| NULL | test_db_wp | wp_links | link_rel | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_notes | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_rss | utf8_general_ci |
| NULL | test_db_wp | wp_options | option_id | NULL |
| NULL | test_db_wp | wp_options | option_name | utf8_general_ci |
| NULL | test_db_wp | wp_options | option_value | utf8_general_ci |

The first part of this shows the overall database collation. In this case, it is utf8_general_ci.

Then the second part shows the collation per column with each table within the database. In this case they are all the same as the default. However, it should be noted that this isn’t always the case. It can be different then the default.

How to find mysql variables with a query, specifically data directory

I needed to find out the data directory for mysql. A quick way to do this within mysql is to use the show variables command.


mysql> show variables;

Which will output something like:


+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |

[ .......]

| version_comment | (Ubuntu) |
| version_compile_machine | i686 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+-----------------------------------------+-------------------------------------------------------------------------------------------+

You’ll see on this development server the data directory was in /var/lib/mysql/

Laravel 4 release date

When will Laravel 4 be released?

Per Taylor’s announcement at Laracon it will be released in May of 2013. Further on the wiki there is a time table for the next 4 releases.

Is Laravel 4 stable enough to use in production?

I would say yes. At this point in development there are a lot less items up for change. In the previous months I have seen major things change and break existing sites that I have running Laravel 4. These types of events have calmed down lately. I feel comfortable launching a site in Laravel 4 today. In fact, if you need a start in Laravel 4, check out the starter site on github.

*Update*

Release date is May 27th 2013!

How to specify specific engine in Laravel 4 migration

Earlier today I needed to set up a specific database engine for a mysql table that wasn’t the databases’s default. In this instance, the default was INNODB and I needed it to be MyISAM. We could discuss the reasons why I shouldn’t use MyISAM but in this instance that’s what I needed.

It’s fairly straight forward. Take the following example migration block.


'mysql' => array(
 'driver' => 'mysql',
 'host' => 'hostname',
 'database' => 'database_name',
 'username' => 'username',
 'password' => 'password_here',
 'charset' => 'utf8',
 'collation' => 'utf8_unicode_ci',
 'prefix' => '',
 ),

Then just add the engine configuration.


'mysql' => array(
 'engine' => 'MYISAM',
 'driver' => 'mysql',
 'host' => 'hostname',
 'database' => 'database_name',
 'username' => 'username',
 'password' => 'password_here',
 'charset' => 'utf8',
 'collation' => 'utf8_unicode_ci',
 'prefix' => '',
 ),

**UPDATE**
Thanks @unisys12 for the comment. This needed to be updated for the Blueprint code in Laravel.
Your “up” method might look like:


	public function up()
	{
		// Create the `Posts` table
		Schema::create('posts', function($table)
		{
			$table->increments('id')->unsigned();
			$table->integer('user_id')->unsigned();
			$table->string('title');
			$table->string('slug');
			$table->text('content');
			$table->timestamps();
		});
	}

In order to add the table type, you’ll need to do this:


	public function up()
	{
		// Create the `Posts` table
		Schema::create('posts', function($table)
		{
			$table->engine = 'MYISAM';
			$table->increments('id')->unsigned();
			$table->integer('user_id')->unsigned();
			$table->string('title');
			$table->string('slug');
			$table->text('content');
			$table->timestamps();
		});
	}

Using xargs to parallel a process in the command line

Most of us have multiple CPUs in our personal machines. I would also hope that your servers do too. If you’re running an intensive command line process, running them in parallel will speed it up by paralleling the process.

Say you need to find a string in all of your log files. On any server that has multiple programs running there’s a lot of log files to run though. Here’s an example:

 

find /var/logs -type f | xargs -P 4 -n 10 grep -H 'string-to-search'

Read more on xargs
and even more examples