Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
18 views

How to select and order by id and get multiple results?

I have one database clients and two tables: business and categories. The site is a classified ads placement, business contains: Field Type Collation Null Key ...
1
vote
0answers
622 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
41 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
vote
0answers
175 views

Restart a GTID Based MySQL Master-Master Replication

I can see many good articles around the web about setting up MySQL Master-Master Replication with GTID. However, strangely enough, I simply can't find the same about in how to stop and start it. My ...
28
votes
2answers
150k views

mysql Lock wait timeout exceeded; try restarting transaction

we are running java application, running for ages, back end is db is MySQL, recently updated to mysql 5.6. Everything was running fine, past week started getting this error Lock wait timeout ...
1
vote
2answers
1k views

Restoring XtraBackup from MySQL 5.6 to MySQL 5.7

We are running MySQL 5.6 and taking backups using XtraBackup 2.4. We want to migrate to MySQL 5.7 using that same backup as we have datasets well over 600GB. Is it possible to do so and what are ...
0
votes
1answer
51 views

Table data is not getting dumped

This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is running....
0
votes
0answers
44 views

Failing on queries with length of multiples of 32

tl;dr: Every SQL query of length 64 or higher that is a multiple of 32 results in the connection closing with a 2013 error when interacting with our remote MySQL server. I am having the strangest ...
2
votes
1answer
2k views

The CPU utilization spikes to 80+ even with just one user on Amazon RDS (MYSQL INNODB)

These are my RDS instance configs Instance Class: db.m3.xlarge Storage 40 GB Engine MySQL 5.6.34 Multi AZ : No I have a the node.js based backend hosted in EC2 instance and also have a cloud front ...
0
votes
0answers
117 views

MySQL event scheduler — Event running at wrong time

I have created a event to run EVERY 1 DAY at 3:15 (am). I know that timezones affects events and actually I create a query to check the START time in mysql.event (GMT timezone) and information_schema....
0
votes
1answer
71 views

Mysql Query Optimization [Match Against] [closed]

I have tried all the possible option to optimize the query, but no luck. Query 1: SELECT *, ( 3959 * acos ( cos( radians(51.5073509) ) * cos( radians(...
4
votes
1answer
3k views

InnoDB Error - InnoDB: Error: trying to remove tablespace 264 from the cache but InnoDB: it is not there

Im using AWS aurora which is MySQL 5,6 Recently I can see an error like below. 2017-02-14 20:42:34 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 264 from the cache but InnoDB: it is not ...
0
votes
2answers
69 views

MySql GROUP BY, ORDER BY LIMIT query taking more than 5000 seconds

I have a simple table. In which one composite index is created. We have a job which hits select statement on slave server and which takes around 5000 seconds to execute query. Please check table and ...
0
votes
2answers
4k views

Split a pattern string into columns in select query

I have a person table with below structure. CREATE TABLE person(id integer, details text); INSERT INTO person(id,details) VALUES(1, "name:Tahir,age:30,sex:male"); I want to split details column by ...
0
votes
0answers
28 views

Migrate a legacy application that uses MySQL replication

I have been working on the migration of a legacy ecommerce web application to a new server with far hardened security. I have found an aspect with the MySQL server that I am not acquainted with and I ...
0
votes
1answer
290 views

Mysql Dump Taking longer time

Mysql Dump Taking longer time I have 188 databases, which I am individually dumping in a loop using a shell script as below echo "$(date +%b-%d-%y) Dumping Started at $(date +%r)" | tee -a $DEST/...
0
votes
1answer
1k views

Large MySQL operations slowing down SELECT queries on unrelated tables

On a fairly busy SQL server, we sometimes need to do large index-building operations (after importing ~6GB tables). The indexes are built using the more efficient "Repair by sorting" method, rather ...
0
votes
1answer
25 views

For MySQL, how to INSERT the result of a SELECT statement? [closed]

For MySQL, how to INSERT the result of a SELECT statement? I tried SET @input1 = 1; SET @input2 = 2; INSERT INTO my_table (col1, col2) SELECT @input1 AS col1, @input2 AS col2 WHERE @...
0
votes
1answer
26 views

MySQL replication without baseline data import

I've been reading about MySQL replication and all the resources I've looked at say that a data import (be it a mysqldump, or a raw data dump import) is needed before replication can start. I'm trying ...
0
votes
1answer
34 views

In MySQL, how to execute two different SELECT statements depending on a condition?

SET @input1=123; SET @input2='foobar'; IF @inout1>0 THEN SELECT @result := col12 FROM table1 WHERE col11=@input1; ELSE IF @input2 != '' SELECT @result := col21 FROM table2 ...
0
votes
1answer
20 views

mysql master and slave has different schema alter table add column

I have added column in large table it was having some unique index which was causing issue,So i drop index and add column and after adding column i have added unique index. But all of my slave is ...
0
votes
1answer
45 views

Slow logs are not showing full details

I am new to mysql DB. I have set log_slow_verbosity value to full. root@localhost/(none)> SELECT @@global.log_slow_verbosity; +-----------------------------+ | @@global.log_slow_verbosity | +------...
1
vote
2answers
4k views

Should I turn off GTID to fix this error?

Recently, a query has started failing. It is a query in the Magento software core (not a module) that Reindexes Catalog Search. I have receive the following error. Catalog Search Index index ...
0
votes
0answers
48 views

MySQL, error creating system tablespace ibdata1

I have a MySQL database (version 5.6.21-log) which I wanted to reset. I have erased the database folder (where I had my tables), and also the ibdata1 (the system tablespace). The problem is that ...
-1
votes
1answer
127 views

MySql SQL_CALC_FOUND_ROWS disabling the order by clause

I am using MySql and SQL_CALC_FOUND_ROWS. It seems to be disabling the order by clause. MySql Server version: 5.7.15-0ubuntu0.16.04.1 (Ubuntu) SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM ( ...
0
votes
1answer
255 views

Endless Query Loop In Mysql

I will delete millions records from a table in mysql dbms, but i don't want to delete all records at one time not to force database server cpu. That's why i want to use my query many times. My whille ...
0
votes
0answers
381 views

MySQL Explain. How to improve type ref extra NULL

I'm struggling to find the right index to the query shown below (I have improved it from minutes to 20 seconds but it needs to be faster): explain extended SELECT cta.NRO_CUENTA , (IF(aut.Reversada=...
1
vote
2answers
126 views

MySQL 5.6 users index instead of range type select on a big table and simple query

We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
0
votes
0answers
167 views

Master MySQL5.6 - Slave MySQL 8.0 slow relay log reads

We have 4 Servers, 1 Master and 3 Slaves. 2 slaves are on 5.7 version and working perfectly, whereas 3rd slave that is mysql 8 (test version). 3rd slave imported dump in 1 hour when other took 3-4 ...
7
votes
2answers
21k views

Set sql_mode “blank” after upgrading to MySQL 5.6

After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting. ...
0
votes
1answer
37 views

how to add key and UNIQUE KEY to existing table without check to speed up performance?

i have very big InnoDB tables in MYSQL 5.6 version , i want to add KEY an UNIQUE KEY as fast as i can , the data is from restored database so all keys are checked , i am looking something like SET ...
0
votes
0answers
26 views

Create User in MySQL

Is it possible to create an user without create user command? I mean just by make an entry(framing insert query) in mysql.user table? Will it cause any crash in system tables?
1
vote
1answer
110 views

Slow MySQL searching from millions record

My table looks like this. id | hash | date I've an index on hash which is in binary mode. I have about 3 million rows right now. But if I query with the following query, it takes about 4 seconds. ...
3
votes
1answer
2k views

Need help to understand how information_schema.TABLES get updated

I created large innodb table mysql> select count(*) from big_data; +----------+ | count(*) | +----------+ | 23822336 | +----------+ checked the stats in informaion_schema.TABLES mysql> ...
6
votes
3answers
5k views

What can cause a rapid drop in RDS MySQL Database free storage space?

How could my MySQL database on Amazon RDS have recently gone from 10.5 GB free to "storage-full" status within about 1.5 hours? It's a 15GB MySQL 5.6.27 database running on a db.t2.micro instance. ...
2
votes
1answer
2k views

Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything. But, Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN ...
3
votes
3answers
3k views

Same record shows up multiple times in ordered paginated query

Some records show up on multiple result "pages" with the following query: SELECT `description`, `wallpaper` FROM `bbr_bar` WHERE `country_id` = '2510769' ORDER BY `online` DESC ...
5
votes
4answers
2k views

Is our MySQL poor performance related to excessive daily query cache prunes?

We are experiencing a high number of query cache prunes per day, currently 80851746. MySQL really struggles even under a reasonable load of say 50+ concurrent connections. The host is a Physical ...
1
vote
1answer
5k views

Where to put my.ini file for 5.6.21 MySQL Community Server on Windows

I have copied the C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini to C:\Program Files\MySQL\MySQL Server 5.6\my.ini and changed some settings including setting table_definition_cache=400. When ...
1
vote
1answer
989 views

Issues with mysqldump after upgrade 5.5 -> 5.6

A few months ago I updated a MySQL setup from 5.5 to 5.6. Since then I've been having problems with a script that I use to dump the various databases so that I can back them up. The script is a short ...
6
votes
2answers
245 views

Issues after schemas migration from mysql 4 to mysql 5.6

Recently I've migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully. ...
2
votes
1answer
1k views

MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
3
votes
2answers
2k views

MySQL 5.6 showing wrong “second_behind_master”

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.7.98 ...
2
votes
1answer
793 views

Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
0
votes
1answer
707 views

Mysql Percona 5.6 slave lags gradually

I have got into slave lag gradually say every minute it lags around 5secs on slave. So it started morning 10AM the Seconds_behind_master was 13. Now at 4:30 PM it has reached to 560. No variable ...
0
votes
2answers
11k views

Can't enable MySQL 5.6 Query Cache

I'm trying to enable MySQL Query Cache on Ubuntu 15.04 with MySQL 5.6.25 I've added this to end end of /etc/mysql/my.cnf and /etc/mysql/conf.d/mysql.cnf: query_cache_type = 1 query_cache_size = ...
3
votes
4answers
19k views

unable to insert utf8mb4 characters in mysql 5.6

recently i was facing problem in inserting emoji and other 4 bytes character in my mysql database then i found that my mysql version was 5.1 and it does not support utf8mb4 so i upgraded to mysql 5.6 ...
2
votes
2answers
119 views

Function is accepting varchar when input is set to INT

I have a function that is suppose to have INT as input. However, when I pass any string, the input is accepted. Why does the function not error out because of the input not matching the input type? ...
3
votes
1answer
3k views

After mysql_upgrade to 5.7, optimizer not using index on large IN clause

I have a similar issue to some other people have described. In MySQL 5.6.23-72.1-log, a query with a large number of values in an IN clause uses an index, and takes 10 minutes to run; In 5.7.19-17 ...
0
votes
2answers
64 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 ...

1
3 4
5
6 7
21