…talking about using a temporary table to store an interim result set, and then use … storing another column, and reinitializing the sequence after making certain insert/update/delete operations. …MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… me know. mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; +———-+—–+————-+——-+———+——+——+ | TABLE_ID | POS | NAME | MTYPE …SET ‘utf8′ COLLATE ‘utf8_general_ci’ NOT NULL, `last_name` VARCHAR(45) CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’ NOT NULL, `last_update…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL …achievement, your application updates this table with a statement like this: UPDATE achievements SET count = count + 1 … ‘joined’ column to the users_groups table so we don’t need to update the…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
…DROP TABLE statement is used on a temporary table. Bug fixed …case mixed CHAR and VARCHAR columns would be used in foreign… #1130888 (Seppo Jaakola). If MySQL replication threads were started … when xtrabackup tmpdir was set up on tmpfs. Bug fixed … our bug tracking system. UPDATE[18-04-2013]: There…
Post: Fixing column encoding mess in MySQL
December 18, 2007 By 32 Comments
… MySQL. The trick is to tell it to treat the string coming from the table as binary, and then do charset conversion: UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING binary) USING utf8) USING…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… strings update sbtest set c=concat(sha1(rand()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table…. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest…
Post: Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
…an UPDATE against your 10 million row users table without a WHERE clause? Did you know that in MySQL…1 exec_time=0 error_code=0 SET TIMESTAMP=1350677442/*!*/; UPDATE `t1` SET `c2` = ‘tom’ WHERE `c1` = 2 /*!*/; # at …the old and new versions of the changed column is stored in the binary log — in this…
Comment: Fixing column encoding mess in MySQL
March 14, 2012 By 32 Comments
… TABLE CONVERT TO CHARACTER SET utf8; Does nothing except changes table/column encodings, but no actual content changes. So.. After this mysql thinks my table is utf8, I try the nr2 way mentioned in this blog: UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…0 error_code=0 SET TIMESTAMP=1359714063/*!*/; ALTER TABLE test add column id_2 int…table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL… update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) session2 > CREATE TABLE t1…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…update all columns whenever they have changed or not: mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) mysql…
