…=/usr/share/java/mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int…: # `col2` int(11) default null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 1
May 21, 2013 By 8 Comments
…_auto_position = 1; mysql> start slave; and let’s create a new table on the master: mysql> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES FROM test on both slaves shows that the table has been created everywhere. So…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…/05/simple-mysql-and-php-prodcuts-and-cart.html create dynamic main menu and sub menu using php and mysql CREATE TABLE `menu` ( `id` int(11) NOT NULL auto_increment, `label… KEY (`id`)) —————————————————————————————————— $mysql=mysql_connect(’127.0.0.1′,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql_query(“SELECT a…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… Cluster (PXC) for MySQL is a hot thing right now and some…PXC node. This is because before Galera can create writesets for the replicated events, binlog events …replace in short term. Control Your Auto-Incrementing Columns PXC/Galera controls auto-incrementing values internally within the cluster, …
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… fields (what we know today as auto_increment), CASEUP and CASEDN fields which although not implemented in MySQL, the defines can still found… small static rows in MySQL */ reclength=max(file->min_record_length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP… the same as what I can create. That’s right folks, in MySQL 5.6 you can create a table with a few…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates… check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… cause auto_increment value to be reused too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment=1000; Query OK…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…auto_increment_incrementblogpercona.commanual auto_increment…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… the following sequence of events: session1 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0…) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would just…_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; However, I feel that the CREATE TABLE should…
