Questions tagged [mysql-5.6]
MySQL version 5.6 - please also tag with mysql for search purposes.
311
questions with no upvoted or accepted answers
6
votes
0answers
624 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/...
5
votes
1answer
501 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 ...
4
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 ...
4
votes
1answer
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
347 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 ...
3
votes
2answers
261 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
0answers
575 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
441 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
165 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
566 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 ...
3
votes
0answers
143 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
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
286 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
58 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
1k 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
998 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
71 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
210 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
124 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
451 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
360 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
1answer
3k views
How change security group in AWS RDS to allow public access?
I am new to MySQL and AWS. I have set up a new (free trial) AWS account for the purpose of setting up a MySQL database server that I can access from multiple PCs.
Per the AWS RDS tutorial, I set up ...
2
votes
0answers
78 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
203 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
361 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
1answer
1k 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
88 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
38 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
85 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
828 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
128 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
849 views
MySQL Server Instance Configuration Wizard does not start for the first time
I tried to install MySQL on my machine (win 8 64 bit). I downloaded mysql-5.6.24-winx64.exe and after completing the MySQL 5.6 setup wizard, MySQL Server Instance Configuration Wizard does not start ...
2
votes
0answers
318 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
2k 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
32 views
Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird
-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --
We recently upgraded from mysql 5....
1
vote
1answer
50 views
MySQL takes huge memory by ignoring buffer pool size
I am facing a very strange problem, looks like on mysql startup, mysql is taking so much memory than 4x of buffer pool size.
I am using Ubuntu VM (4 Core 8 GB) with MySQL 5.6.33. /etc/mysql/my.cnf is ...
1
vote
0answers
32 views
Mysql “memory usage” keeps increasing
I have 32core CPU 204GB RAM Mysql 5.6 server, hosted on GCP VM.
I keep getting RAM high usage issues.
I have around 200+ tables, most of which are Innodb.
At any time, i see arnd 1k+ active ...
1
vote
0answers
54 views
Non-existent table that actually exists?
I've been tasked to solve a very difficult and interesting problem.
Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10.
We have an ...
1
vote
1answer
21 views
Query To Show YTD Total Summing Each Month Prior To Current
I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would ...
1
vote
2answers
123 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
23 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
54 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
175 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
826 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
47 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 ...
