...replaced file-based methods to ensure only one running instance of a program with MySQL’s GET_LOCK function. The result is mutual exclusivity that works in a distributed environment...
...implementation as a Rubygem seems to be with_advisory_lock. It has support for MySQL, PostgreSQL and SQLite. Unfortunately, it has a horrible caveat in MySQL: With MySQL (at least...
...you don't need to, but when you want to see which queries your MySQL server currently needs to handle (and if there are locks, etc), you could say SHOW...
...PROCESSLIST in a MySQL shell. Unfortunately, SHOW PROCESSLIST does not allow filtering. When you are on MySQL ≥ 5.1.7, do this instead: SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id...
I had a huge MySQL dump that took forever (as in: days) to import, while I actually just wanted to have the full database structure with some data to use...
...on my development machine. After trying several suggestions on how to speed up slow MySQL dump imports (which did not result in any significant improvement), I chose to import just...
When talking to your MySQL server via a mysql shell, you can terminate queries by ; or \G – the latter gives you a vertical output. You know this: mysql> SELECT * FROM...
+----+---------+---------------------+-----------------+ 3 rows in set (0.00 sec) This is how it looks like vertically: mysql> SELECT * FROM users \G *************************** 1. row *************************** id: 1 name: Alice email: alice@example.com greeting: Hello world...
To show the collation of your tables you have to login to the MySQL console and execute SHOW TABLE STATUS FROM database; mysql> SHOW TABLE STATUS FROM test; +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine...
...show the default character set of a table you have to login to the MySQL console and execute SHOW CREATE TABLE table; mysql> SHOW CREATE TABLE foobar; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table...
A MySQL DECIMAL column is used when it is important to preserve exact precision. It takes two parameters, where precision is the total number of digits and scale the...
...a decimal column definition looks like this: t.decimal :amount, :precision => 6, :scale => 2. Issue MySQL prior to 5.6 stored leading zeros (0003.1) and +/- characters (+2.1) within the column. However, it...
When you do a script/dbconsole -p, your MySQL shell will already be using UTF-8. When you call it yourself using mysql, it may not be enabled.
...of Hlavní město Praha. You need to manually switch on UTF-8, in the MySQL console: SET NAMES 'utf8...
Note: This applies specifically to MySQL. In PostgreSQL for example, this is not an issue. If you care about performance, never use a query like UPDATE users SET has_message...
...WHERE users.id IN (SELECT user_id FROM messages) MySQL does not optimize this and seems to scan the temporary table, which isn't indexed, for every row in the update...
...to use encrypted connections between your application and RDS instances. If you're using MySQL on RDS, here's what to do: Download the AWS CA file and copy it...
...further details of the SSL configuration (such as public key). Try to connect using MySQL client % mysql -uyour_username -p -h rds_hostname_from_management_cockpit.eu-west-1.rds.amazonaws.com --ssl --ssl-ca=/path/to/mysql-ssl-ca-cert.pem Use this statement to...
...but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don't represent the actual database sizes? This is for you!
...a mysql root console: SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; This will get you...
...of does not consider "username" and "USERNAME" to be a collision. If you use MySQL this will lead to issues, since string comparisons are case-insensitive in MySQL.
...sensitive: false end When you get an ActiveRecord::RecordNotUnique error (probably in combination with Mysql2::Error: Duplicate entry) for a string field, case sensitivity may be your issue...
Usually our mysql queries are not case sensitive. In order to query case sensitive, you can use the mysql COLLATE clause. The collate clause lets you specify a collation, which...
The mysql2 gem in version 0.3.13 might break while compiling on older patch releases of Ruby 1.9.3 within rvm: ** [err :: server] ruby: symbol lookup error: /path/to/deployment/shared/bundle/ruby/1.9.1/gems/mysql2-0.3.13/lib/mysql2/mysql2.so: undefined symbol: rb_wait...
...server] ruby: symbol lookup error: /path/to/deployment/shared/bundle/ruby/1.9.1/gems/mysql2-0.3.13/lib/mysql2/mysql2.so: undefined symbol: rb_wait_for_single_fd Fixating mysql2 to version 0.3.11 helped...
The way MySQL's FULLTEXT tokenizer splits text into word tokens might not always be what you need. E.g. it splits a word at period characters. Since the tokenizer has...
...before it is stored in your FULLTEXT column. E.g. if you don't want MySQL to split at period characters, just strip out period characters. If you apply the same...
When searching for text in a MySQL table, you have two choices: The LIKE operator FULLTEXT indexes (which currently only work on MyISAM tables, but will one day work on...
...pain and you will eventually need to deal with it, probably using a non-Mysql solution...
This might be due to AppArmor denying the MySQL server access to most of the filesystem. You can instead use LOAD DATA LOCAL INFILE … to pipe data through the MySQL...
...or end of a word: note.title =~ /\bfoo\b/ Unfortunately \b is not available in MySQL. You can use [[:<:]] and [[:>:]] to match the beginning and end of a word instead:
...FROM notes WHERE title REGEXP "[[:<:]]foo[[:>:]]" These markers are unique to MySQL and not available in Ruby regular expressions...
To clear the query cache in your MySQL database manually, e.g. for database profiling, execute the following command in your MySQL console: RESET QUERY CACHE...
Directly from the MySql docs: There are three likely causes for this error message. Usually it indicates network connectivity trouble and you should check the condition of your network if...
For reasons that completely escape me, MySQL 5.x limits UTF-8 strings to U+FFFF and smaller...
When you work in the MySQL console and you want to see which database is used, type: SELECT database(); The result you see is the database you would activate with...
The next version of MySQL will include a built-in memcached daemon. This daemon can quickly get and set key/value pairs from and to InnoDB tables while completely bypassing the...
Unless you changed the default, this will be 16 MB: mysql> SHOW VARIABLES WHERE Variable_name="max_allowed_packet"; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | max_allowed_packet...
...month / 3.0).ceil #=> 1 Yes, you do actually divide by 3.0, not 4.0. MySQL has SELECT QUARTER...