Stack Exchange Network

Stack Exchange network consists of 174 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
1answer
11 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
28 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
17 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
3 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
35 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
39 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
8 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
38 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
19 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
24 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
25 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
32 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 ...
1
vote
1answer
47 views

CentOS 6.9 mysqld start fails without innodb_force_recovery = 3

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
20 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
16 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
28 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
30 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
19 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
22 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
52 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
14 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
24 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
22 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
22 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
16 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
39 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
22 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
46 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
19 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
56 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
75 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
80 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
35 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
59 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
37 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 ...
-1
votes
0answers
33 views

How to convert the SQL query from MariaDB 10.3 to MariaDB 10.1/MySQL 5.6

Since I need to convert the below query which should work on MariaDB 10.1/MySQL 5.6. When I tried to run the below query I used to get the error as #1064 - You have an error in your SQL syntax; ...
0
votes
1answer
17 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
228 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 ...
0
votes
1answer
20 views

Analyze general log

Motivation : understand the highest CPU\usage\long_query of historic queries running on production. example :10 minute ago on server x graph showed high CPU usage. I thougth for next steps : ...
0
votes
1answer
395 views

How to insert data to database by using checkbox in PHP MySQL? [closed]

I have this output: a problem while I inserting data the time value for all users inserted to the database, I want when I checked the checkbox send the time value to the database for every student I ...
-1
votes
1answer
48 views

Reference table to same table in another database

I'm currently working on a bigger project with a few friends of mine. However, at the moment, we have a ... pretty bad development setup. Here an - extremely simplified - database: -------------- | ...
1
vote
2answers
36 views

For each value in field A, select the row with some special value in field B, while respecting further criteria

Disclaimer: While the basis of my question—the greatest-n-per-group question—has been asked and also answered so many times before, I am facing a seemingly hard issue due to extra conditions that I ...
1
vote
1answer
30 views

Delete from table using FK columns, JOIN and LIMIT

This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
0
votes
1answer
38 views

Mysql Slave Claims to be Running but New Records Not Appearing and No Delay

I've created a new mysql master and a slave. My master is also a slave of another running instance. The replication between those two works fine so I don't believe my issue is there. I created a dump ...
0
votes
2answers
59 views

Mysql Percona 5.6 Configuration File

I've just installed a Percona instance of Mysql on an Amazon server and can not find the my.cnf file anywhere. I found auto.cnf in /var/lib/mysql but there is nothing in that file like what I have ...
0
votes
1answer
58 views

in MySQL what is the query to show these student's that each marks less than 50?

I have this table marks in MySQL database I used a query to output this table that shown as below: it's my output: I want use a query to show these student's that each marks less than 50, output ...
0
votes
2answers
220 views

How to use variable into SQL in MySQL trigger

I'm trying to write trigger. I have encountered an one problem. My Code is like. DELIMITER // CREATE TRIGGER upd_check BEFORE UPDATE ON user FOR EACH ROW BEGIN DECLARE tempVar text; IF(OLD.name ...
1
vote
0answers
202 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
2answers
77 views

Retrieve parent and child data using child id

I have a parent and child table, I am able to retrieve all data using UNION SELECT. Parent Table: id | parent_name 1 | Parent A 2 | Parent B Child Table: id | parent_id| child_name 1 | 1 ...