Posted about 8 years ago by Thomas Eisenbarth.

So you're switching to PostgreSQL from MySQL? Here is some help… General hints on PostgreSQL \? opens the command overview \d lists things: \du lists users, \dt lists tables etc...

...Command comparison Description MySQL command PostgreSQL equivalent Connect to the database mysql -u $USERNAME -p sudo -u postgres psql Show databases SHOW DATABASES; \l[ist] Use/Connect to a database named...

Posted over 9 years ago by Arne Hartherz.

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...

Posted about 10 years ago by Kim Jahn.

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...

Posted about 10 years ago by Tobias Kraze.

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...

Posted almost 10 years ago by Henning Koch.

...and end_date are simple dates. In order to make sense of your query, MySQL will cast your dates to datetimes where the time component is 00:00:00. Because...

...FROM log_items WHERE created_at BETWEEN '2007-05-01' AND '2007-05-31' MySQL sees that you are trying to compare a datetime with a date and silently casts...

Posted almost 8 years ago by Dominik Schöler. dev.mysql.com

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...

Posted over 9 years ago by Arne Hartherz.

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...

Posted about 10 years ago by Thomas Eisenbarth.

...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...

Posted over 10 years ago by Arne Hartherz. forums.mysql.com

...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...

Posted over 8 years ago by Tobias Kraze.

...databases are actually very complicated, and chances are, your making some incorrect assumptions. The MySQL innodb engine actually has four different modes for transactions: READ UNCOMMITTED READ COMMITTED REPEATABLE READ...

...are consistent nonlocking reads. The problem with consistent nonlocking reads If you open a MySQL transaction, the first SELECT statement will establish a "snapshot" of the database's state. All...

Posted about 11 years ago by Tobias Kraze.

...want to see how long your database queries actually take, you need to disable MySQL's query cache. This can be done globally by logging into a database console, run...

ActiveRecord::Base.uncached do yield end true end Don't forget to re-enable MySQL query caching later with SET GLOBAL query_cache_type=ON...

Posted about 11 years ago by Tobias Kraze.

CONCAT('foo', 'bar', NULL) = NULL the NULL always wins in MySQL. If you would rather treat NULL as an empty string, use CONCAT_WS (concatenation with separator) instead...

Posted over 8 years ago by Arne Hartherz. ariejan.net

...create an int column, which is smaller than a bigint. These are values for MySQL. At least on PostgreSQL you can just use bigint instead of integer: create_table :example...

Posted about 8 years ago by Andreas Robecke. dev.mysql.com

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...

Posted about 8 years ago by Thomas Eisenbarth.

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...

Posted almost 9 years ago by Henning Koch.

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...

Posted almost 11 years ago by Tobias Kraze.

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...

Posted almost 11 years ago by Henning Koch.

...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...

This website uses short-lived cookies to improve usability.
Accept or learn more