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 | +--------------------------------------------+
MySQL version 5.1 comes with an alternative, faster InnoDB implementation (called "InnoDB Plugin"). Switching is easy: Stop your mysqld with sudo stop mysql Add the following lines to your /etc/mysql/my.cnf...
...under the [mysqld] section ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so Start your mysqld with sudo start mysql The file format has not changed, your tables should survive this.
...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...
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
Bundler::GemRequireError: There was an error while trying to load the gem 'mysql2'. Gem Load Error is: Incorrect MySQL client library version! This gem was compiled for 5.5.46 but the...
...libmysqlclient.so.20: cannot open shared object file: No such file or directory": gem pristine mysql2 gem pristine re-installs a gem (without re-downloading), re-compiling all native extensions in...
This will make MySQL log all received queries so you can see for yourself what happens on the database level. Don't switch this on for production machines!
sudo vim /etc/mysql/my.cnf In the [mysqld] section, add: log=/var/log/mysql.log Restart your MySQL daemon. On Ubuntu: sudo service mysql restart Note that your MySQL performance will suffer. But when...
...in different order. Solving deadlocks is potentially complicated, so here are a few pointers: MySQL should always detect the deadlock right when it happens, and will throw an error to...
...still cannot see what actually happened, you might need to get better acquainted with MySQLs locking model. While it keeps out of your way usually, it's actually pretty complex...
This Perl script will run diagnostics on your MySQL database and recommend changes to your MySQL configuration...
...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.
MMM (MySQL Master-Master Replication Manager) is a set of flexible scripts to perform monitoring/failover and management of MySQL Master-Master replication configurations (with only one node writable at any...
...data. Dumping single tables makes sense if a complete dump would be to big. mysqldump -u deploy_user -p application_production table1 table2 table2 > table1_table2_table2.sql.dump Hint: If a table has...
...be more handy. Further reading: - How to load only a subset of a massive MySQL dump - How to import production database dump to staging (or the other way...
If you have many connections to your MySQL or MariaDB (as we have) you might want to filter the list you see when running a SHOW PROCESSLIST. To hide all...
There is an more advanced way by querying the information_schema database: Show MySQL process list without sleeping connections
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...
When MySQL refuses to use your index, you can do a number of things wrong. One of them might be conditions with improper data types. An example For example, let...
...assume you have a users table with an email field (varchar) which is indexed. MySQL will use the index when your query is well-formed: mysql> EXPLAIN SELECT * FROM users...
...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...
Note: For PostgreSQL you should use advisory locks. For MySQL we still recommend the solution in this card. If you need to synchronize multiple rails processes, you need some shared...
...can be used as a mutex. One option is to simply use your existing (MySQL) database. The attached code provides a database-based model level mutex for MySQL. You use...
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...