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
0answers
22 views

Mysql to sql server query conversion creating problem in group by

I am trying to convert mysql query to sql server query. The main problem is with group by, sql server only support full group by, which actually giving unexpected result. The mysql query is as ...
1
vote
0answers
15 views

Percona Server 5.6.40 restarting with singal 11

We recently migrated our mysql to new hardware and it was running in slave mode for 15 days. We made it the master 11th June. On 13th June it restarted for the first time with signal 11. Stacktrace ...
0
votes
0answers
18 views

How can I resolve the `package mysql-server is not installed` issue?

I have just upgraded MySQL from version 5.5.55 to version 5.6, via SSH. I have managed to successfully start the MySQL, however I am experiencing the 500 Internal Server Error when trying to access ...
1
vote
1answer
22 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 ...
0
votes
0answers
45 views
+50

MySQL 5.6 too much write after enabling compression

Recently I have enabled table compression (i.e. ROW_FORMAT=COMPRESSED) on half of the tables in a MySQL 5.6 server. I am observing: much higher write IOPS (~700 -> ~3000) dangerously lower dirty ...
-1
votes
1answer
15 views

Exclusion With Many to Many Relationships

I have created a select statement that show all the topics that are assigned to a book. Now I am trying to create a Select statement that shows all the topics that are not assigned to the book. Book ...
0
votes
0answers
7 views

Reconstruct folder hierarchy in JSON format from MySQL using Python

I have a table with a structure like this: | itemID | parentFolderID | folderName 1 0 folderA 2 1 folderB 3 1 ...
-1
votes
0answers
15 views

REGEXP is giving syntax error in MySql

I am using an IF statement to see if a parameter being passed is an integer, but getting a syntax error The following is the statement: set @_repositoryID = 1; IF @_repositoryID REGEXP '^[0-9]+$' ...
1
vote
1answer
34 views

Limit left join for multiple rows

Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables. Let's overview simple example. ...
0
votes
0answers
17 views

How to increase mysql dumping speed

I have sql file size of 2.51 Gb I checked mysql dumping speed with 2 different machine os For MAC ----16 Gb Ram , core i7 , 2.7 Ghz---- It's take 10 min only For cent os 6.6 ---8 gb Ram , 4 core ...
0
votes
1answer
9 views

Concatenate two columns into one column in a view in MySQL

I am trying to create a view and would like to display the firstName and lastName columns from a table as one column (User) in a view. The view is using a left join. What I would like to do is join ...
0
votes
1answer
34 views

MySQL Internals: SQL Execution Flow [closed]

This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components. eg the sequence an UPDATE query ...
0
votes
2answers
65 views

MySQL Create View WITH CTE

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming. SQL SERVER Command: CREATE VIEW [dbo].[vw_PurchParent] as WITH cte AS ( SELECT a._Id,...
0
votes
2answers
40 views

Query not returning results if selecting by indexed columns (Mysql 5.6)

We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column ...
0
votes
0answers
16 views

MYSQL need log executed query in table along with executed user name

I would to like the executed quires with entire database tables for some mysql users only. Because some of users executed wrong query in production environment. And needs to store this log in new ...
0
votes
1answer
18 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
0
votes
1answer
19 views

How to map single-products to categories in my scenario?

I have the tables depicted in the following diagram: Let's say I have only two products in my database at moment, one that has variants, while the other does not have them. Now my question is: how ...
0
votes
2answers
23 views

Trouble Enabling Query Caching on MySQL 5.6?

Recently I discovered our MySQL 5.6 database is not using any query caching and I wanted to change that. I added [mysqld] query_cache_type = 1 query_cache_size = 50M query_cache_limit = 2M to a ...
0
votes
0answers
29 views

MySQL not using index. Working fine with FORCE INDEX

This is my table schema along with defined indexes: CREATE TABLE `chat_state_event` ( `id` bigint(20) NOT NULL, `agentId` varchar(255) DEFAULT NULL, `allocState` varchar(255) NOT NULL, `...
4
votes
6answers
287 views

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
1answer
33 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
31 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
22 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
146 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
5 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
18 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
17 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
44 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
73 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
16 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
21 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
140 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
27 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
128 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
53 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
17 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
1k 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
90 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
30 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
23 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
767 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
15 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
60 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
69 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
56 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
21 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 ...