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.

0
votes
2answers
42 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
18 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
15 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
54 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
12 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
14 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
18 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
28 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
10 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
0answers
19 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
51 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
12 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
31 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
18 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
5 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 ...
0
votes
2answers
63 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
12 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
41 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
33 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
27 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
80 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
1answer
56 views

CentOS 6.9 mysqld start fails without innodb_force_recovery = 3 [closed]

Please help me. I've googled and searched here, but I couldn't solve my problem. The problem is that service mysqld start fails. But if I write innodb_force_recovery = 3 in my.cnf, mysqld starts. ...
0
votes
0answers
22 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
55 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
36 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
29 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
48 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
23 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
24 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
92 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
16 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 <-->...
0
votes
1answer
25 views

SQL Query Optimization (Mysql 5.7)

I have following query to execute in live mysql 5.7, which takes around 7 sec and we want to reduce their time, please support SELECT COUNT( DISTINCT InternalInquiry.inquiry_id ) ...
0
votes
1answer
27 views

MySQL Range Optimization and Multipart index

I'm new to the MySQL world. When I read about MySQL Range Optimization some questions were raised related to multipart indices. MySQL 5.7 Doc (http://jorgenloland.blogspot.com/2011/08/mysql-range-...
0
votes
1answer
27 views

MySQL 5.6 - Query Performance Issue. Searching for Slug

I've a case where I don't know how to better the performance of this query. This is a simple query to the Geonames.org database. The query: SELECT name, latitude, longitude FROM geoname WHERE ...
0
votes
0answers
18 views

Unable to select the table result in linked Server

As I have successfully created the Linked Server in SQL Server 2012 SP4 with MYSQL Server. Some of the small tables record able to fetch from the MySQL Server. And some of the big tables records when ...
1
vote
0answers
50 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
1answer
27 views

Proper use of FULLTEXT index for a column storing unique hash value

We have an InnoDB table called user, where we have a column called token (varchar(128)) where a unique md5-like value that represents whether a user is authenticated or not, is stored. This column can ...
0
votes
1answer
52 views

SQL: get product stock of all product types in a single query

I want to get stock information for all the products depending upon the product type. The tables are products, product_stocks, complex1_childs & complex2_childs. products | product_id | type ...
0
votes
1answer
22 views

drop big table freeze server

I am using amazon rds mysql 5.6 master instance and 4 slaves. there is big table with 800 GIB size that i want to drop it for releasing space. But when i execute drop command, the server entering to "...
1
vote
2answers
74 views

MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

I am issuing some long-running delete queries to some databases, and I am getting the following error: Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout ...
2
votes
2answers
81 views

Is it possible to make mysql effective in low cardinality if I may need a majority of the rows?

I currently have a table with 3.2kk records, index cardinality is around 1000. It's data from google search console, so 5000~ records per day for 5000~ different queries, and I need to run reports ...
0
votes
2answers
123 views

mySQL query optimizer not using indexes

In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment ...
0
votes
1answer
36 views

Remove Characters & Sum Numeric Values

I am using mysql 5.6 and want to remove all characters from json output of db column and after that required sum of numeric values Input: {"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50"...
2
votes
0answers
99 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 ...
0
votes
1answer
38 views

mysql query to get time difference

I have query like this SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam FROM `transaksi` WHERE tanggal = '2018-10-11' AND kode_layanan = 'A' AND aksi_ID BETWEEN 4 AND 9 and ...
0
votes
1answer
21 views

How to query when one ID has more than one name and find the total count of unique names, id

Same id having more than one name and the count of the unique names which are associated with the unique id
2
votes
2answers
355 views

Lost connection to server executing ALTER TABLE

I'm trying to execute an ALTER TABLE statement in MySQL 5.6. Every time I run the query, it's creating a file with a name like #sql2-503-918bf.ibd taking full table size. I don't have enough disk ...