Questions tagged [mysql-5.6]
MySQL version 5.6 - please also tag with mysql for search purposes.
280
questions with no upvoted or accepted answers
5
votes
0answers
591 views
Create an SSL connection with ODBC driver to MySQL(5.6) using openSSL
I want to create an asymmetric SSL connection from MySQL to an ODBC driver but don't fully know how to generate the keys and certificates (I'm using openSSL as recommended here: https://dev.mysql.com/...
4
votes
3answers
2k views
How to restore corrupted InnoDB tables?
We have a data loss: the MySQL database is dropped and we have to restore the tables from ibd files. The backup is too old so we have to undelete the deleted database files. Most of the tables are ...
4
votes
1answer
384 views
Running Triggers on Slave with Row-based Events in MySQL >=5.6
We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys
If one uses statement-based ...
3
votes
1answer
70 views
Stored procedure getting delayed more
Stored procedure and it is getting delayed. Taking nearly more than half an hour if I call the stored procedure in MySQL command line client. This procedure is to update values in two tables
drop ...
3
votes
0answers
376 views
Replication doesn't resume once it fails to connect
We have MySQL replication running for our application. Right now the slave fails to resume if it fails to connect to the server for some time.
We get the error below:
[ERROR] Slave I/O: error ...
3
votes
0answers
145 views
Fast index creation issue
One thing that I can't explain is creating secondary index that doesn't trigger fast index creation as it should. I noticed that while creating secondary index on larger table, it gets actually ...
3
votes
0answers
135 views
SSL for security - Percona MySQL
I have been told to implement SSL in my Master-Master MySQL instances by my security team to have secure traffic only to be received at the database end. I have never implemented SSL in a database ...
3
votes
2answers
757 views
Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output
I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
3
votes
0answers
1k views
Replication stops with GTID_NEXT error after creation/drop of memory table in mysql5.6
We have recently upgraded to mysql5.6.25 from mysql5.5.x/mysql5.1.x on our mysql-cluster.
Below is a brief snapshot of our architecture.
Since we have upgraded and enabled gtid-mode we have been ...
3
votes
0answers
1k views
amazon rds logs all queries
I have enabled slow query log on Amazon RDS with following configuration.
long_query_time = 0.5
general_log = 0
log_output = TABLE
min_examined_row_limit = 1000
slow_query_log = 1
...
3
votes
0answers
1k 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 ...
3
votes
1answer
216 views
query optimization of a left join
I have the following query written into the slow log (mysql-slow.log).
I don't have much idea how to optimize this query. I already have indexes for:
mode,
f.user_id... etc
but doesn't seem to be ...
2
votes
0answers
189 views
missing ibd files
I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables.
When I try to mysql I get the following messages for the missing ...
2
votes
0answers
485 views
MySQL memory usage is too high - OOM
Im using 32vCPU and 120GB VM for Mysql 5.6.
it's a dedicated MySQL server.
15000 IOPS SSD disk.
Total database
size 40G
This server was working fine for a couple of months. Memory usage is ...
2
votes
0answers
32 views
MySQL Queries Truncate Causing Connection Spikes
We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits)...
2
votes
1answer
68 views
Can I stop replication of create/drop index statement on one slave of Mysql 5.6?
I have this cluster where master and slave uses same indexes but one of the slaves has some extra indexes (large machine which runs some analytics queries).
So I have to maintain two sets of indexes. ...
2
votes
2answers
109 views
Do MySQL (5.6) triggers support 4 byte emojis?
I'm using MySQL 5.6.34 with the following settings:
"character_set_client","utf8mb4"
"character_set_connection","utf8mb4"
"character_set_database","utf8mb4"
"character_set_filesystem","utf8mb4"
"...
2
votes
0answers
260 views
MySQL delete gradually locks more and more rows
I've encountered a strange problem that I can't explain. MySQL 5.6.34, RDS.
Basically, some delete from table where id in (...) queries are locking up indefinitely, but never truly deadlocking.
I ...
2
votes
1answer
1k views
The CPU utilization spikes to 80+ even with just one user on Amazon RDS (MYSQL INNODB)
These are my RDS instance configs
Instance Class: db.m3.xlarge
Storage 40 GB
Engine MySQL 5.6.34
Multi AZ : No
I have a the node.js based backend hosted in EC2 instance and also have a cloud front ...
2
votes
1answer
2k views
InnoDB Error - InnoDB: Error: trying to remove tablespace 264 from the cache but InnoDB: it is not there
Im using AWS aurora which is MySQL 5,6 Recently I can see an error like below.
2017-02-14 20:42:34 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 264 from the cache but
InnoDB: it is not ...
2
votes
0answers
302 views
MySQL 5.6.34 keep eating all the RAM
I have 2 servers (master-slave) with Centos 7.3 (64 GB RAM) and MySQL Server 5.6.34 installed. MySQL keeps using more and more RAM; what I've been doing is stop/start MySQL so it releases the memory, ...
2
votes
0answers
61 views
Signal handler on mysql connection break
I want to know if there is any signal that I can catch by which I can know that my mysql connection with the server has broken in c programming? I know I can check at query time whether my connection ...
2
votes
2answers
129 views
website performing slow once mysql connection RDS reached 50
Below the rds parameter group config, please help me to resolve this issue.
I am using 4 web server having 2 CPU and 8 GB ram and rds having 4 cpu and 16 GB ram.
Once the connection reached 50 site ...
2
votes
0answers
299 views
Modify slow query log rotation on Amazon RDS instance
Our Amazon RDS instance rotates slow logs every hour, and keeps only the last 20 of them.
This is not optimal for monitoring as it prevents us from seeing any slow query that might have been ...
2
votes
0answers
398 views
Intermittent IOExceptions when connection with MySQL Connector/Net. Why?
I have a web application which uses a MySQL 5.7.13 server for storing its data. MySQL is running on Windows Server 2008 R2. A couple of month ago I started seeing exceptions like the following being ...
2
votes
1answer
769 views
Flush Host Connection Blocked, but some connections allowed
We are running Percona MySQL 5.6.30. We've had an issue where a PHP script on a different host was reporting an issue connecting to the database:
Database may be down : SQLSTATE[HY000] [1129] Host ...
2
votes
1answer
101 views
How to avoid row locks on both tables joined in an update?
I have little knowledge about MySQL internals, but I'm experiencing that in a query like:
UPDATE t1
JOIN t2 ON t1.id = t2.id
SET t1.a = x, t1.b = y, [, ...]
WHERE [ ... ]
rows from both t1 and ...
2
votes
0answers
86 views
Read READ COMMITED transaction advantage in MySQL, high performance object model
I am writing high performance object model with READ COMMITED locking.
My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not ...
2
votes
0answers
36 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 ...
2
votes
0answers
80 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 ...
2
votes
0answers
482 views
After disabling replication, should I disable binary logging?
I have a master-slave setup currently but am going to remove the replication DB server. The DBs are running on MySQL 5.6. I have a couple of questions about removing the replication:
1) I have read ...
2
votes
1answer
78 views
Alter a large mysql table 700 gb
The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log.
I found following options:
pt-online-schema-change: alters a table’s ...
2
votes
1answer
87 views
MySQL users not visible after import
I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After ...
2
votes
1answer
1k views
Issues with mysql enterprise backup, assertion failure on restore
I'm trying to restore a backup to a new server, but having some issues with corruption. The source server has a 2.5T table which is very active. I copied the latest backup over to a new server and ...
2
votes
1answer
488 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, ...
2
votes
0answers
290 views
Mysql Match…against query performance in InnoDB Mysql 5.6
I need to search records in the table which contains millions of records. I have recently updated Mysql version from 5.1 to 5.6.
I was using like in query which was taking around 15 sec to 30 sec.
...
2
votes
1answer
1k views
Table partition in aws MYSQL RDS master slave setup
we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this ...
2
votes
0answers
4k 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
32 views
Percona Server 5.6.40 restarting with singal 11
We recently migrated our mysql to new hardware and it was running in slave mode for 15 days. We made it the master 11th June. On 13th June it restarted for the first time with signal 11.
Stacktrace ...
1
vote
1answer
32 views
Unable to downgrade MySQL from version 8 to 5.6 nor start MySQL (MySQL Error Log Included)
I run a Plesk VPS (OS: Centos 6/Linux (Final)), whereby I recently tried to upgrade the MySQL from version 5.5.55 to version 5.6.
Being relatively new to this area, I strictly followed this Plesk ...
1
vote
1answer
1k views
Alternate to use LIMIT in subquery in mysql?
I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to ...
1
vote
1answer
76 views
Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS
I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS.
For this, we have taken backup from physical server and restored in RDS ...
1
vote
1answer
120 views
MySQL data warehouse calculate difference over years in percentage
I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months.
My question is how to add the new ...
1
vote
0answers
125 views
mysql RDS cpu utilization is 90 % above always for less than for 250 connections ( max conn 1236)
I'm using mysql RDS db.m3.xlarge( 4 vcpus, 15G Ram). max_connection limit is 1236 but cpu is used more than 90% for less than 250 connection. I don't know what I'm struggling to find reason. Please ...
1
vote
0answers
164 views
mysql 5.6 Unknown system variable 'optimizer_trace'
I'm trying to follow this tutorial to trace optimizer that is used by mysql for certain query.
But when I do
SET optimizer_trace="enabled=on";
It said ERROR 1193 (HY000): Unknown system variable '...
1
vote
0answers
55 views
Can't add new node to Galera Cluster
I have a 5 node Galera 3 Cluster running MySQL 5.6 on Ubuntu 16.04 (I tried running 5.7 but ran into the AppArmor issue).
I'm trying to add another node to the cluster but it continually fails. The ...
1
vote
0answers
39 views
I want to help data fetching query in MySQL 5.6
I'm fetching data from mysql.
But, query is so complex.
My case is like
Table schema
CREATE TABLE `mt_department` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '[ID]',
`mt_company_id`...
1
vote
1answer
66 views
Article table has a lot of null. What is the best way to prevent null in MySQL
I'm optimizing table schema.
My case is like.
User can generate an article.
It has a lot of item. like name, age, sex, address, hobby...
name, sex is a required entry.
but age, address, hobby is ...
1
vote
1answer
200 views
How to pivot a column showing its content as new columns
I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common.
I have a table like the following one:
direc ...
1
vote
0answers
21 views
Inserts locking mysql 5.7
We currently are seeing record locks during insert after we upgraded mysql from 5.6 to 5.7. I have disabled the deadlock detection and logging everything but still can't determine why the locks are ...
