Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql-5.6]

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

1
vote
2answers
80 views
+100

MySQL join has drastically worse performance after upgrading from 5.6 to 5.7

I have a fairly straightforward query that has become very slow after upgrading from MySQL 5.6.35 to MySQL 5.7.12 (running on AWS RDS). SELECT DISTINCT Name,d.id,deviceType,issuedBy, description,...
0
votes
0answers
18 views

Quiz System Database Schema [on hold]

I want to create a database schema for quiz system which is little different from the rest of the typical schema(s). As we are all aware of the entities involved in it which are therefore Quiz-type(...
0
votes
0answers
21 views

why Threads_connected falls down automatically in some cases

How can I monitoring, when Threads_connected increase from which place(files) or when it decrease, because some of cases it dose not fall down automatically in our production server and we are getting ...
0
votes
1answer
30 views

Combine many table into one table in mysql table design

I'm grappling to design table relation. Version is Mysql 5.6.10. My case is like below bill table, payment table, contract table, recruitment tables... are existing. Currently we're considering new ...
0
votes
1answer
17 views

Check and count if a column value is used in another column of same table

I have a table like this and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0,...
0
votes
0answers
20 views

How can I convert the version of a MySQL database from 5.5 to 5.6? [duplicate]

everybody. I need, please, to convert the version of a MySQL database from 5.5 to 5.6. It's the database for my wordpress site and it's a requirement from my new hosting provider (the old one is ...
0
votes
3answers
79 views

Configuring the max_connections setting in MySQL

I've gone through a number of docs and forums but can't quite find the right answer for how to determine the number of maximum connections I should set. I tried using MySQLTuner, that gave me: ...
0
votes
0answers
4 views

AWS MySQL remote Connection

I am attempting to connect to a mySQL database that is not on AWS from a client that is. The AWS EC2 instance has an elastic IP and that IP has been white listed, but I continue to get an access ...
0
votes
1answer
15 views

Mysql alter/write lock database

I runned this in mysql: UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE Db='wordpress_fixed'; But this not preventing from inserting more data to database wordpress_fixed. Is it supposed to ...
0
votes
1answer
16 views

Is there a way to partition a mysql table, by range columns, where the 2 columns in question are of datetime and bigint?

I need to partition a table into 4 partitions, p0 to p3: with p0 having all rows with "id"=0, and p1 having all rows with "id"=1 and "doj" before february, and p2 having "id"=1 and "doj" before march, ...
0
votes
0answers
8 views

Got timeout reading communication packets

I am facing this warning continuously in my error log , [Warning] Aborted connection 27 to db: 'unconnected' user: 'xxxxx' host: 'xxxxx' (Got timeout reading communication packets) can i have some ...
0
votes
2answers
41 views

Is there a way to speed up this update query using joins or any other method

I have an update query that selects data from a table (HST) and updates it into another table (PCLN). The PCLN table contains ~17000 rows while the HST table contains about 1.1 million rows. I have ...
0
votes
1answer
23 views

MySQL Calculate percentage difference (not change) between 2 columns

The calculation formula for difference between 2 numbers should be: Using the above formula I need to calculate the difference in percentage between N1 and N2. Both numbers are the result of 2 ...
0
votes
0answers
15 views

one to many relation how to create a new row with mysql trigger

i'm really new to mysql triggers so...i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 ...
0
votes
1answer
10 views

MySQL logging max_questions resource

How would I know in MySQL if the user has exceeded his max_queries per hour? I know that I can see this error on the client side, but I just want to know that before client is reporting to me, ...
0
votes
2answers
60 views

Upgrade Antelope to Barracuda on Slave

I received a Last_Errno: 1118, Last_Error: Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row ...
1
vote
1answer
24 views

How to drop column named `

I managed to create a column in my MySQL database named ` (the backtick character). How can I delete it? Is there a way to escape the backtick character in a query?
0
votes
2answers
94 views

Optimize MySQL update statement

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have ...
0
votes
0answers
37 views

MySQL InnoDb crash when importing tablespace from remote machine

InnoDB crashes when importing ibd file from remote machine. Source version is MySQL-5.6.40, Destination version is MySQL-5.6.40. Database was backed up was backed Percona Xtrabackup with xbstream ...
0
votes
0answers
5 views

Benchmark for a rowlock in mysql

I am trying to benchmark a sql transaction with a select..for update statement which uses an exclusive lock on the row and then insert a row into another table as shown below. START TRANSACTION; ...
0
votes
0answers
16 views

Learning Percona Monitoring - CPU Usage

I've just started using the Percona Monitoring. I am not necessarily a db admin but I ended up with the task. I am enjoying it but truth be told I am not sure what I am looking at. There is an ...
2
votes
1answer
566 views

Incorrect datetime value: '0000-00-00 00:00:00' for column 'tmp_field_2' at row 1

I'm trying to run the following query: INSERT INTO `user_domain_rating_days` ( `user_domain_rating_id`, `start`, `...
0
votes
0answers
48 views

Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

Getting one issue in error log in every 30-45 mins. Mysql version 5.6.20-log Server is slave of master main server and its having 100 slaves to this server. Replication hierarchy is there in 3 stages. ...
0
votes
1answer
24 views

How to setup Master to Master Replication with Application auto failover

I have a requirement as follows , need to setup a Multi-Master replication with automatic application failover ,if one master goes down, Can anyone share me some information regarding this. Thanks ...
0
votes
1answer
20 views

How to convert binary string value from error log?

I'm trying to investigate an issue with a query I'm currently debugging. The error I get from my client code is the following: Duplicate entry '\x9F\x98\x9Ern\x06\x8BN\x97\x8D\xB5A\xB5e\x12\xC9-\x9C\...
1
vote
1answer
197 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 ...
0
votes
1answer
13 views

NULL safe NOT LIKE operation in mysql

I have InnoDB table with two columns (1) account(int) (2) comment(varchar). I have index on (account,comment) Now I want to run query like select * from table where account = 1111 AND comment NOT ...
1
vote
1answer
40 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 ...
0
votes
1answer
58 views

MySQL converting column of type TEXT to TINYTEXT truncates value at non-intuitive position, why?

I recently ran into a problem where a table had a column of type TEXT inadvertently converted to type TINYTEXT (MySQL version 5.6.24). We were able to restore any truncated data, but the confusing ...
0
votes
1answer
13 views

Getting aggregate sub-values in aggregate query

I have a query that need to show aggregated values in an already aggregated outer query. Consider a query that fetches total count of accounts being credited on a certain day. I would like to ...
-1
votes
2answers
40 views

mysql trigger data from a many to many relation

I am new to triggers, so I want to know how I could select attributes from 3 different tables to insert them into a new table. I have 3 tables with a many to many relationship: table users wich has ...
0
votes
0answers
19 views

Deciphering InnoDB status logs

I noticed MySQL deadlock errors in my application logs and for investigation, I queried InnoDB status and got the below mentioned Latest detected deadlock *** (1) TRANSACTION: TRANSACTION ...
0
votes
0answers
7 views

a lack of DTrace probes of mysql@5.6 installed via brew under macOS

I installed mysql@5.6 via brew (brew install mysql@5.6) under my macOS, but when I run sudo dtrace -ln 'mysql*:::', comparing to Table 5.6 MySQL DTrace Probes there are only a few probes listed, sudo ...
1
vote
2answers
200 views

Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL?

Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN Can somebody clear this up for me? What's ...
2
votes
2answers
41 views

Trouble Figuring out why My Query is so slow

I have a query that takes 15 seconds to get 350 results in a MySQL 5.6 Server and I am unable to diagnose why, I am still very new to database optimizing. U The EXPLAIN visual does show some non-...
0
votes
0answers
22 views

MySQL DB Migration from on-premises to AWS through replication

I am having a production MySQL server with set of 20 databases in on-premises server. Now I require to migrate few databases from production server to AWS RDS without disturbing other databases in one-...
0
votes
2answers
42 views

How to get rows only contains multiple ID using SQL?

Let say I have this table: id | foo 1 | 100 1 | 200 1 | 200 2 | 300 2 | 300 3 | 200 4 | 100 I am expecting to have this result: id | foo 1 | 100 1 | 200 1 | 200 2 | 300 2 | 300 I want ...
0
votes
0answers
89 views

Increase select query execution time in mysql database with 15 million data

I have a table in my database which contains about 15 million data. I have to repeatedly perform join operations in my application and while applying joins into the table (containing 15 million data ...
0
votes
0answers
26 views

Why Update join sql locks a table in Mysql 5.6

I'm encountering transaction error like this. ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-12-25 08:45:10 2b128be88700 *** (1) TRANSACTION: TRANSACTION 112497037, ...
0
votes
1answer
33 views

When I try to update/set a datetime I get an invalid mysql syntax 1064

I copy a value from the pre-existing values. I update the date. I do an update query. It has an issue and the issue seems to be the time. Here's my query: UPDATE xxxxx_eb_events Set ...
1
vote
1answer
189 views

Downgrading from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6

We are trying to downgrade from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6 due to some issues. But AWS RDS doesn't allow Aurora MySQL 5.7 snapshot restore to Aurora MySQL 5.6 instance/cluster. Does ...
0
votes
0answers
29 views

point in time recovery not working with blackhole engine tables involved

The basic problem summary: I am trying to do a point in time recovery on a specific table. To speed things up I made every other table use the blackhole engine. Unfortunately this runs into errors ...
0
votes
2answers
57 views

How can I speed up this 2m5s query that has indices?

How can I speed up this 2m5s query that has indices? select urls.id as urlId, count(case when s1.hit_type = 0 then 1 end) as aCount, count(case when s1.hit_type = 1 then 1 end) as bCount, ...
0
votes
1answer
90 views

How to specify “partition (partx)” in “mysqldump” command?

I have to export some data from a table which is partitioned, and with some extra condition So I use mysqldump command,and use --where"xxxxxx" options as below mysqldump -hxxx -Pxxx -uxxx xxx rtm --...
0
votes
1answer
30 views

How to get same column values to make multiple columns?

I don't know if my question is valid or not but I'm working on an attendance system, and I want to get some output for my project. I have an attendance table that holds three columns: emp_id, ...
1
vote
1answer
78 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
31 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 ...
0
votes
1answer
36 views

How to update a sequence column using single update statement

I have a table, lets call it foo which consist a column priority and has a unique constraint on it. lets assume below is the current state of foo table :- mysql> select * from foo; +-----+--------...
1
vote
1answer
165 views

Mysql connection pool set timezone

Service application is nodejs It is using mysql connection pool. Currently, we will plan to sell service to another country What is the best way to set timezone? timezone setting execute, and query ...
0
votes
0answers
19 views

MySQL 5.6 and Replication

I'm stuck with one specificity of Replication on MySQL servers. I'm currently working with this configuration : A <--> B ⊦---> C ---> D I explain. I've got a replication Master <-->...