...calculations, e.g. 10 AM CEST minus 8 AM UTC is zero. A datetime in MySQL does not have a zone. It just stores the literal string...
...That means that Rails must make assumptions about timestamps loaded from and written to MySQL. Rails has two completely different modes of dealing with this. We also prefer to use...
...itself did not work for me. Uninstall everything related to MySQL 5.7 with apt remove mysql-client mysql-server libmysqlclient-dev mysql-common Check if you removed everything using
...MySQL 5.6" and "Ubuntu Wily" (assuming Xenial is not yet provided) Run apt-cache policy mysql-server If this shows a 5.6 version, continue. If not, check your /etc/apt/sources.list.d/mysql.list. It...
ALTER DATABASE database_name CHARACTER SET "utf8"; ALTER DATABASE database_name COLLATE "utf8_unicode_ci"; After that, for...
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...
To show the MySQL default character set you have to login to the MySQL console and execute SHOW VARIABLES LIKE 'char%'; mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+
...to uft8 you have to add this default-character-set = utf8 to the client, mysqld_safe, mysqld and mysqldump section (may differ depending on configuration) in your my.cnf and restart...
Look here for informations how you can show the MySQL default character set. At first you need the Amazon RDS Command Line Toolkit download and unzip the Amazon RDS Command...
After that you can create a new parameter group /home/foobar/Downloads/RDSCli-1.4.007/bin/rds-create-db-parameter-group utf8 -f mysql5.1 -d "default parameter with utf8" --aws-credential-file ./aws-credential.txt --region eu-west-1
mysql> SELECT @@global.version; +------------------+ | @@global.version | +------------------+ | 5.6.30 | +------------------+ 1 row in set (0,00 sec) MySQL 5.6 Reference Manual says "BLOB and TEXT columns cannot have DEFAULT values". If you want to run...
...and stash you changes in the migration files. 2. Temporally change the sql mode mysql> SELECT @@global.sql_mode; +--------------------------------------------+ | @@global.sql_mode | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+
When using GROUP BY, MySQL now complains if the SELECT includes columns which are not part of the GROUP BY. Reason: There could be multiple values for those columns per...
Also see our card on SQL compatibility modes. Install MySQL 5.6 in parallel, using mysql-sandbox Use MariaDB 10.x What TRADITIONAL changes MySQL 5.7 standard TRADITIONAL
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 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...
...hosts non-critical development data and only you have access to, you can store MySQL's root password in your home directory so you can use mysql commands without prompt...
user=root password=YOURMYSQLROOTPASSWORDHERE EOF Now you should be able to just type mysql and be logged in as MySQL's root user. This works for different MySQL commands...
...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...
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...
Unless all MySQL server defaults are set to UTF-8, mysqldump encodes UTF-8 characters incorrectly and only outputs correct UTF-8 when you switch to Latin 1 (!). Also you...
...for each new database. To prevent this, make sure your /etc/mysql/my.cnf looks like this: [mysqld] default-character-set = utf8mb4 collation-server = utf8mb4_unicode_ci [mysql] default-character-set=utf8mb4
...database server your application runs on. Like MAX or COUNT, GROUP_CONCAT is a MySQL aggregation function you can use whenever your query contains a GROUP BY. You can use...
...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...
...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...
shell-for stage_1 shell-for stage_2 Get the stage1 and stage2 MySQL credentials: cat /opt/www/the_stage.host.tld/current/config/database.yml cat config/database.yml # should do it Dump the table to a path reachable...
...by the stage2 user (e.g. home): mysqldump -h mysql1 -u stage_1_user -p stage_1_database table_name > ~/table_name_dump.mysql # Select certain records using --where "some_id > 666"
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...
Stop MySQL: sudo service mysql stop Move (or copy) your mysql directory. If you want /mnt/mysql to be the new directory, do it like this:
.../var/lib/mysql /mnt/ Open your MySQL configuration (sudo vim /etc/mysql/my.cnf) and change the datadir value to your new path (e.g. /mnt/mysql) Modify your AppArmor configuration: sudo vim /etc/apparmor.d/usr.sbin.mysqld Change/copy the lines...
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...
...COUNT(DISTINCT #{field}) AS count") end Although the given variable is sanitized here, the MySQLAdapter's (and probably other adapters as well) method for this is insufficient as it only...
...query will look like this: SELECT COUNT(DISTINCT `id); DELETE FROM users; -- `) AS count; MySQL will complain about a missing column 'id); DELETE FROM users; -- ' which is safe.
I recently experienced the error ActiveRecord::StatementInvalid: Mysql2::Error: closed MySQL connection. Apparently this happens when there is a timeout during query execution. In order to fix this you can...
All TEMPORARY tables are closed (and dropped). ... For further details see the mysql doc. You can actually force this error by setting a short timeout and sleeping in...
...and their data, under a new name. Make a dump of your source database: mysqldump -uroot -p my_project -r my_project.sql Or, if you only want to dump the database...
...s table structure (schema) without any contents: mysqldump -uroot -p my_project -r my_project.sql --no-data Open up a MySQL shell: mysql -uroot -p From the MySQL shell, create a...