MySQL version 5.6 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

0
votes
1answer
36 views

Fetching data from 3 tables

Wanna fetch data from 3 tables by mysql query to display in feed. But stack. My query is "SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, ...
5
votes
2answers
109 views

How can I copy the MySQL EXPLAIN result into a table?

I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis. I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how ...
2
votes
1answer
164 views

Services don't have MySQL Server. How to run it?

I need to run MySQL Server on a new for me laptop. There's Windows 7. And MySQL have been installed on it. In order to verify this, I opened the Programs and Features window and saw it there: ...
3
votes
1answer
77 views

Improve performance of query with multiple group by column and count function in select

Brief Background of issue We have a aggregate table to store activities of competitors and this table have currently 3 Million activities and we need to retrieve data from this table group by ...
-1
votes
0answers
47 views

How do we convert SQL Server query to MYSQL? [on hold]

How do we implement this SQL Server syntax: IF(SELECT MAX(DateAdded) FROM RestoreDetectors " + " WHERE CONVERT(VARCHAR,DateAdded,101) = CONVERT(VARCHAR,GETDATE(), 101) " + " AND DATEDIFF(SECOND, ...
0
votes
0answers
11 views

MySQL assertion errors after upgrade to 5.6

We've recently upgraded from 5.1 to 5.5 and then to 5.6. Since then we have database instances crash with following assertion error. I've also reported it as a bug with MySQL ...
0
votes
0answers
30 views

Transaction Identifier - possible with MySQL?

I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if ...
3
votes
0answers
14 views

insufficient history for index

In MySql log, there is one error `InnoDB: insufficient history for index 0' I don't know why its occur. I have done google and found following ...
2
votes
0answers
7 views

Cluster of Clusters (Not using MySQL-Cluster)

I'll start with an apology, I'm an Ops guy at heart, not a DBA, so I may use the wrong terminology, not in an effort to annoy, merely because I don't know any better. OK, that over with, on to the ...
0
votes
0answers
19 views

MYISAM can't create table: Row size too large

We are having database in MySQL 5.1, we have taken the DB backup from MySQL 5.1 and trying to restore in MySQL 5.6. We are getting below error: ERROR 1118 (42000) at line 603: Row size too large. ...
1
vote
0answers
29 views

Disable/Remove Table Index for INSERT Performance

I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
0
votes
0answers
32 views

Replication Error_code: 1032

I an unable to fix this as I don't know what cause this error. Replication was stoped for maintenance activity and after completing the maintenance we started it and then after some point the ...
0
votes
1answer
146 views

Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this: select datetime, PLANT_STATUS, ...
1
vote
0answers
58 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
1
vote
0answers
36 views

InnoDB AUTO_INCREMENT resetting itself without MySQL restart

We've recently noticed the following scenario happening in many of our databases: A record is inserted, and receives an AUTO_INCREMENT primary key. The record is deleted. Another record is inserted, ...
0
votes
0answers
16 views

Sum data from 3 tables(joining), but one of may no data then other tables data will show

I am making a stock group summary report for this I am making Sum data from 3 joining tables, but one of may no data then other tables data will show. I have 3 tables as: ac_stock_item, ...
0
votes
2answers
27 views

MySQL : How do I partition based on Month of date

I have a table having id as primary key and date column. I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that ! I am using mysql ...
1
vote
1answer
27 views

mysql workbench migration changed my database. Can I trust it?

I'm migrating a large production database(s) from mysql 5.5 to 5.6. Used Mysql Workbench Migration Wizard which creates a shell script to run on source and taget machines. The process worked well but ...
0
votes
1answer
160 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
0
votes
1answer
659 views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm ...
0
votes
0answers
13 views

Reliability of MySQL Events Table

Is there any limitation of usage of MySQL Events table. Let us say i will have 10K of entries which will be executed across 3k schemas. They all are not same time but at-least 3K events will be ...
0
votes
0answers
36 views

Slave not getting replicated SERVER_UUID error

Unable to start replication Master-Slave, I have searched a lot for this but unable to get the Server-UUID of both the server. Do we have any solution for this. Scenario: I have created new slave ...
1
vote
1answer
203 views

MySQLWorkbench with MariaDB 10.0 Performance tab not working

I'm using MySQLWorkbench 6.3.3.0 (build 592) and I cannot use the performance tab on a Mariadb10 connection. This is the error I receive: This feature requires MySQL version 5.6.6 or newer ...
0
votes
0answers
13 views

MySQL 5.6.27 keep logging ERROR : Got an error reading communication packets

I have a two nodes MySQL replication set with M1-M2 configuration. The MySQL version is 5.6.27. After deployed and the application(which is jboss 7) connected the M1 and ran few queries for ...
4
votes
2answers
2k views

What is default MySQL binlog retention in AWS RDS?

I tried to retrieve my MySQL RDS binlogs in order to run manual "rollback" for some SQLs. Unfortunately, I noticed that only 2 binlogs for the last 30 minutes are available. "call ...
0
votes
0answers
19 views

Mysql Replication break Error_code: 1032

Created new slave replication, it was working fine but suddenly after 2 days its giving me this error and the replication stopped. Both on Centos 6.7 Master Version ...
2
votes
2answers
920 views

mysqld_safe mysqld from pid file /data/mysql/mysqld.pid ended

MySQL is restarting by itself, but it's crashing most of the time. I am unable to restart the instance due to a pid issue. Tried to restart MySQL moving original ib* files to _old - this time ...
0
votes
1answer
51 views

MySQL Tuning and optimization

How to Tune and Optimize MySQL Database, as a reference we are using mysqltuner.pl and we got the below output of slave host which we created recently. our future plan is the perform this same action ...
2
votes
1answer
136 views

MySQL slave server getting stopped after each replication request from Master

Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports. Now when executing update or insert in master server, ...
0
votes
0answers
23 views

limit of open files: Debian Jessies´ MySQL vs Community Oracle MySQL

I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6. I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables. Whilst for two or three years, I ...
2
votes
0answers
29 views

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

I have the following tables: CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'draft', `publish_at` datetime DEFAULT NULL, ...
2
votes
0answers
52 views

mysql trigger on a federated table

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a ...
1
vote
1answer
57 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
1
vote
1answer
19 views

Is it possible to drop tablespace CASCADE in MySQL?

I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query: DROP TABESPACE ...
2
votes
1answer
95 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
0
votes
1answer
59 views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql ...
-1
votes
2answers
36 views

MySQL: Why is ibdata file growing? How can we shrink it?

Even though we are using "innodb_file_per_table = 1" from day one. The ibdata file still continues to grow. Total size of our database is around 800GB and the ibdata file is currently around 50GB. All ...
1
vote
1answer
2k views

add column without metadata lock in mysql 5.6

I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the ...
0
votes
1answer
411 views

ndbcluster: Error: 1114, The table is full, when using table

I have searched for a while about this error. I have a MySQL cluster running with 2 data nodes. The cluster houses a few other databases. I have a dump of a database in --tab format from another ...
1
vote
5answers
155 views

Replication in same server within different databases. Mysql 5.6

What I have : a windows machine with one instance of MySQL 5.6 Server. two database named test and test2. Test database has a table called activity with columns id and class What I need : ...
0
votes
1answer
17 views

mixing binlog formats between master and slave

I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb. Both my Master and Slave are setup in MIXED binlog format. Now I need my slave to act as a master to ...
0
votes
1answer
31 views

Fast copy/rename database, not mysqldump

I would like to keep a "default" setup for a database on a testing server, which can be used to "restore" a "dirty" testing database back to the default state. mysqldump is very fast, done in a few ...
0
votes
0answers
42 views

MySQL performance issue after migration

I have a MySQL performance issue with a brand new server, and am somewhat of a beginner as a database administrator (I'm mostly a general-purpose Linux sysadmin). I'm looking for pointers where to ...
7
votes
1answer
505 views

Issues with MySQL installation

First of all, excuse the length, this post is very long, and needs some background, second; it has been hard for me discerning the signal from the noise, please bear if some of this is not relevant: ...
0
votes
0answers
32 views

Cannot restart mysql (installed natively) but possibly caused by a Homebrew package

I've previously been running mySql which I had installed with the dmg files without any issues, but I also have Homebrew installed and noticed some errors today which I fixed successfully but ...
0
votes
1answer
975 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
0answers
31 views

How to start mysql without recovery rollback

I started a delete operation on table "A" in mysql which ran for 12 hours (lots of triggers and cascading deletes) already before I decided to kill this transaction. I waited then for another 12 hours ...
2
votes
1answer
171 views

How to suppress specific warnings in mysql error log when ignoring a table to replicate

Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now ...
3
votes
1answer
398 views

“Set Password” with a limited user

I'm using MySQL 5.6. I want to have a limited service-user which is able to create/modify the users of my databases. Now my problem is that I can quite figure out what privileges my service-user ...
0
votes
2answers
28 views

MySQL Binary Installation (mysql_install_db)

While installing from binaries. The below always gives error when we execute from 'scripts' directory [mysql@hostname ~]$ cd /mysql/product/5.6.12/scripts/ [mysql@hostname ~]$ . mysql_install_db ...