Questions tagged [mysql-5.6]
MySQL version 5.6 - please also tag with mysql for search purposes.
318
questions with no upvoted or accepted answers
5
votes
0answers
615 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
1answer
477 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
2answers
213 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"
"...
3
votes
1answer
71 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
473 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 ...
3
votes
0answers
427 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
161 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
142 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
1k 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
2k 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
272 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
1answer
57 views
Incredibly long execution time for a update query?
I am using MySQL 5.6.
In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value.
UPDATE invoices
SET twoWeekAlert = DATE_ADD(now(), ...
2
votes
1answer
756 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 ...
2
votes
0answers
285 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
823 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
54 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
167 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
0answers
109 views
Query latency increases when connections to DB increases without any change in query throughput
We are currently using HikariCP to manage our connection pool and our ORM is eclispelink.
We have the following observation: On production, when the max number of DB connections to master database ...
2
votes
0answers
394 views
How do you capture blocked transactions
I can run
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query ...
2
votes
0answers
345 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
72 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
0answers
352 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
530 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
959 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
0answers
87 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
37 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
82 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
694 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
104 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
0answers
309 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
2answers
42 views
Index creation in extremely slow in Mysql Aurora 5.7
I have a table in mysql Aurora 5.7 version .
The db instance has very good configuration 488 GB ram (db.r4.16xlarge).
Data base is static data base no application is running on this .
I have 1.5 ...
1
vote
0answers
13 views
Create SCD type 2 table from change log table using SQL
I have a change log table, which captures the transition of a state with its time, for example:
user_id,user_status_from,user_status_to, created_date
7cc4d A2 A1 2019-11-03 ...
1
vote
0answers
26 views
Pair records by grouping on foreign key
I am stuck on a question I got in a coding challenge. Essentially I have 3 tables; ratings, reviewers, and books.
The ratings contain a list of book reviews from reviewers. I need to output a query ...
1
vote
1answer
36 views
SELECT gives different results with/without FOR UPDATE in same transaction
I'm sorry but I havent't found a better wording for the title.
Given the table a:
CREATE TABLE a (
id INT NOT NULL auto_increment PRIMARY KEY,
ref VARCHAR(10) NOT NULL,
val INT NOT NULL
)...
1
vote
0answers
104 views
MySQL Password Validation - No previous passwords
I wonder if there is any possibility to use the password validation for MySQL 5.6 (https://dev.mysql.com/doc/refman/5.6/en/validate-password.html) to prevent for example the last 4 passwords.
The ...
1
vote
1answer
98 views
Aurora Serverless alter table Error 1114 table is full
Using Aurora Serverless, when attempting to alter table mytable add column mynewcolumn decimal(10,3) after myoldcolumn I receive "ERROR 1114 (HY000): The Table 'mytable' is full". The table has ...
1
vote
0answers
38 views
How to enforce “at most N child rows” in a foreign key relationship without a trigger?
I have a table that I will be using to save "template" messages for my users. The schema is
CREATE TABLE listemailtemplates (
id int primary key auto_increment,
userId int not null,
...
1
vote
0answers
467 views
Difference between Aurora Global database and Aurora regional database with multiple read replicas?
I am planning to migrate my 300GB RDS PostgreSql database to Aurora MySql using SCT and DMS. RDS Postgresql is in seven regions in current setup. Datapipelines are used to ingest data in these ...
1
vote
0answers
38 views
Windows MySQL: Create table with DATA DIRECTORY
I'm using Mysql on Windows 10, can I create a new table on another drive(different than the drive that contains the MySQL data directory) without creating a new MySQL instance?
When trying to create ...
1
vote
0answers
159 views
Restart a GTID Based MySQL Master-Master Replication
I can see many good articles around the web about setting up MySQL Master-Master Replication with GTID.
However, strangely enough, I simply can't find the same about in how to stop and start it.
My ...
1
vote
1answer
133 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
9k 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
324 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
220 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
481 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
284 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 '...
