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
Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
7