MySQL 5.6 : Latest Release of MySQL with improvements that enhance every functional area of the database kernel.

learn more… | top users | synonyms

5
votes
1answer
210 views

Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY. The SHOW CREATE TABLE: ...
5
votes
2answers
5k views

Cannot open table mysql/innodb_index_stats

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
3
votes
1answer
3k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
3
votes
1answer
106 views

Slower queries after upgrade from mysql-5.5 to mysql-5.6

There is lots written about how MySql 5.6 is faster than MySql 5.5 (e.g. http://dev.mysql.com/tech-resources/articles/mysql-5.6.html) but we upgraded our Amazon RDS instance (using the new upgrade ...
3
votes
2answers
85 views

SQL Question: Selecting different language versions based on availability

I have the following situation: I have a table that contains entries with language versions (German and English) as part of the key (the other part being a "name" field). However, not always both ...
3
votes
1answer
78 views

How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option ...
2
votes
2answers
185 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
2
votes
3answers
242 views

Does MySQL 5.6 support fan-in replication?

I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example). By fan-in replication I mean combining multiple datasources into a single instance (like a ...
2
votes
2answers
321 views

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb Moving the undo log files outside seemed logical but this procedure seems ...
2
votes
2answers
564 views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
2
votes
2answers
412 views

MySQL version confusion

How is this possible: I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!) But upon SHOW VARIABLES LIKE "%version%"; I get ...
2
votes
1answer
142 views

Optimize table and query, aggregate by date or date-hour

I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows: CREATE TABLE sensor_data ( ...
2
votes
0answers
258 views

How to drop all scheduler events in MySQL 5.6?

In MySQL 5.6 is there a way to drop all scheduler events with an administrative command or do I have to go through them one by one with DROP EVENT [IF EXISTS] event_name?
1
vote
2answers
65 views

Why query_cache_type is disabled by default start from MySQL 5.6?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6. We enabled it again and ...
1
vote
1answer
38 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
1
vote
1answer
109 views

Can anyone offer an opinion on handlersocket vs memcached performance in MySQL 5.6

I must say I was a little mis(un)-informed about the MySQL Memcached plugin which offers the same NoSQL like access to InnoDB tables as HandlerSocket, I had only had experience with entirely ...
1
vote
1answer
98 views

New users not working on MySQL 5.6 in Amazon RDS

I'm using the CREATE USER and GRANT commands as mentioned here http://dev.n0ise.net/2012/09/amazon-rds-mysql-add-additional-users-with-limited-previledges/ When I try to connect I get this in MySQL ...
1
vote
1answer
4k views

MySQL 5.6 Failure

I have the following in RPM_UPGRADE_HISTORY in /var/lib/mysql MySQL RPM upgrade to version 5.6.10-1.linux_glibc2.5 'pre' step running at Fri Mar 15 15:41:26 EDT 2013 ERR file(s): -rw-r----- 1 mysql ...
1
vote
1answer
21 views

Why didn't MySQL keep the innodb_file_format after loading a mysqldump

I was loading a mysql dump today when I ran into ERROR 1118 (42000) at line 279: Row size too large (> 8126) Which I thought was weird because I know I had turned on row compression and was ...
1
vote
2answers
82 views

MySQL 5.5 to 5.6 Upgrade. Will data remain the same?

We are considering to upgrade to MySQL 5.6 this summer. I would like to know if our data will be changed in any way once we perform the upgrade? I've been reading and I found that mysql_upgrade ...
1
vote
1answer
157 views

Data lost in MySQL InnoDB after crash

I found a 6-days rollback in a MySQL 5.6 community edition server installation. It seems that some logs were lost, and I cannot understand why. 2013-12-22 20:38:54 380 [Note] Plugin 'FEDERATED' is ...
1
vote
1answer
317 views

GeomFromText in trigger has error

I have this trigger: DELIMITER $$ CREATE TRIGGER geometrytrigger BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.user_geometry=GeomFromText(NEW.user_geometry); END$$ DELIMITER ; when I insert a ...
1
vote
1answer
123 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
1
vote
1answer
200 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
1
vote
1answer
1k views

MySQL 5.6 OS X 10.8: how tell if my.cnf read?

With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands ps aux | grep mysql | grep -v grep mysqladmin variables | grep defaults-file showing the value ...
1
vote
1answer
161 views

Can't query one view from another?

Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, ...
1
vote
2answers
310 views

MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root

I'm having serious problems with a MySQL 5.6 instance on a Mac Server. We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the ...
1
vote
1answer
37 views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
1
vote
1answer
43 views

MySQL update row cell with 0 value with value from closest row that does not have 0 value

I have a list of entries that has a time field where unix timestamp is stored. I recently discovered that some of the entries have that field value 0. I want to run an update that will update those ...
1
vote
3answers
233 views

Amazon RDS - and binary log rotation

New to RDS (used to having my own server in my own data center). We are on MySQL 5.6 on RDS. when a slave gets behind in replication lag, the master ceases to rotate out its binary logs. It is, ...
1
vote
0answers
103 views

Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns. As far as I can ...
1
vote
1answer
897 views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
1
vote
1answer
329 views

How to get MySQL to iterate a subquery in an aggregate select?

Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does. Consider the following two simple queries: #1 ...
1
vote
3answers
1k views

How do I remove partitioning?

There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code: alter table my_table remove ...
1
vote
2answers
3k views

Issue on mysqldump in MySQL5.6

I did upgrade the MySQL server from 5.5 to 5.6 I am trying to take dump for a table like this mysqldump -uroot -p dba admin > D:\admin.sql But it gets following error mysqldump: Couldn't ...
0
votes
1answer
185 views

Is using the alpha version of Percona 5.6 safe? [closed]

I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it ...
0
votes
1answer
20 views

What is the difference between Com_xxx and handler_xxx?

Initially my com_delete variable and handler_xxx variables are nil. | Com_delete | 0 |+ | Handler_delete | 0 | +----------------+-------+ then I issued a delete query delete from gt ...
0
votes
2answers
49 views

mysql no longer defaulting to /tmp/mysql.sock

I was testing some software to see if it could connect to mysql correctly when mysql is not using /tmp/mysql.sock. I stopped mysql, editted /etc/my.cnf to add socket=/var/lib/mysql/mysql.sock ...
0
votes
1answer
21 views

How to include a semicolon (“;”) in a MySQL pager command

I've just discovered the pager command in the MySQL command-line client, and I'm having a tonne of fun. This blog post is great: ...
0
votes
2answers
60 views

MySQL: Moving Database from Windows to Linux

I have a Windows based dedicated server have below configuration: Windows edition: Windows Server 2008 R2 Standard Service Pack 1 Processor: Intel(R) Xeon(R) CPU X5460 @3.16GHZ 3.16 GHZ (2 ...
0
votes
1answer
192 views

Mysql Innodb as Key value store, performance

I read about NoSQL databases using Key value pairs (KVP) tables. So i wanted to find out if KVP tables would be faster than conventional relational tables (with multiple columns) in mysql with innodb ...
0
votes
1answer
594 views

Master - Master replication Duplicate entry

I've got a Master - Master replication setup and it worked fine for a couple of days but I keep getting: Duplicate entry errors after a few days... My setup is as follow: One server in The ...
0
votes
1answer
132 views

General error 1651 after a mysql migration

I finished migration from 5.1 to 5.6, and (with the same queries as before) mysql complains right away about: General error: 1651 Query cache is disabled; restart the server with ...
0
votes
1answer
19 views

How performance schema in mysql 5.6 improve performance?

Since MySQL 5.5 performance_schema database has been added likewise information_schema. As we know that tables in information_schema contains statistical information like tables, plugins, partitions, ...
0
votes
1answer
19 views

MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

Lately the server's been going a bit haywire and we've spent several weeks chasing down leads to no avail. All we can tell is that when the websites go down (and come back up, and go down, and come ...
0
votes
1answer
42 views

Find the last SQL statement executed for a given connection

MySQL running on Windows 7 64 bit. I'm trying to track down a leak of connections, I'm pretty sure the only ASP.NET app using the server is at fault here. We received the "Too many connections" ...
0
votes
1answer
76 views

Getting an error in MySQL Workbench: Cannot Open Administrator

I'm using local installation of MySQL CE on Windows 7 64-bit. Tried to use Administrator part for the first time. Here's the error I'm getting: I also see the following in the log: <object ...
0
votes
1answer
44 views

MySQL Server 5.6 only runs when making swapfile

I am new to MySQL server and I have setup a dedicated MySQL server. I am aiming to install MySQL server version 5.6 and since version 5.6 is not available on the Ubuntu repository I have to upgrade it ...
0
votes
1answer
30 views

MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)?

My DB server has MySQL 5.1.58-community-log and I am planning to upgrade it to MySQL 5.6 with specific purpose of improving my system performance and removing the bottleneck issue. So, I have gone ...
0
votes
1answer
116 views

What is wrong with this a MySQL 5.6 procedure definition? Use a variable for table name [closed]

Just can't understand why it's throwing this error? Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ...