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

Query performance degrade after upgrade from MySQLv5.6 to v5.7

I upgraded the MySQL Server from v5.6 to v5.7 and since then I see query performance degrade. For example, I have the following select query that selects some columns and has a single column in the ...
msbeast's user avatar
  • 11
0 votes
0 answers
9 views

Temporary table information left over on InnoDB data dictionary after a db crash while a alter table was runnig

After a database crash while the system was running an alter table command I'm getting the following message during mysqld initialization: 2024-02-25 19:58:48 7fa2f9e84880 InnoDB: Operating system ...
Vaiks's user avatar
  • 1
1 vote
2 answers
40 views

Table partitioning for data pruning purposes

We are using MySQL database. The total space it can accommodate is 250 GB. It is currently at 225 GB. With this, there had been this initiative within our team to create a daily cron job for daily ...
Xmus Jackson Flaxon Waxon's user avatar
0 votes
0 answers
114 views

Upgrade from MySQL 5.6 and 5.7 to MariaDB

We have several servers with MySQL 5.6.51 and several with MySQL 5.7.39. What are the right steps to upgrade to latest MariaDB LTS 10.11? I think we can't go directly to 10.11 or? MySQL 5.6.51 > ...
lear1's user avatar
  • 79
1 vote
0 answers
38 views

Slow database performance after mysqldump backup [closed]

we are using mysql 5.6-91 (percona) as the backend database. Everyday morning mysqldump is used to back up the entire database and upload it to s3. The database size is around 70GB. The issue we are ...
Shreehari's user avatar
  • 119
0 votes
1 answer
108 views

Slow select queries but CPU usage always less than 1%

We recently moved to a bigger server (64G, 16core Amazon EC2) and I was hoping some increased performance. However, there are plenty of slow queries (some even 50ms) but CPU usage remains very very ...
JM John's user avatar
  • 13
1 vote
2 answers
98 views

Speeding up Select Query

I have the following query which needs to run up to 40-50 times on some pages. SELECT lid, dest, rebuild FROM {store_links_link} WHERE source = :url; Source column is URLs and the problem is that ...
JM John's user avatar
  • 13
1 vote
0 answers
159 views

Replication is slow after upgrading from mysql 5.6.17 to mariadb 10.6.12

I was using mysql 5.6.17 and upgraded to mariadb 10.6.12 due to EOS. After installing mariadb on the same server, change the basedir to run mariadb. After that, I ran the mariadb-upgrade tool to ...
user avatar
0 votes
0 answers
44 views

MySQL join 2 column which every column got string with command seperator and add same value

Hello guys, I want to combine 2 column with same total comma separator and add same string in the middle, anybody can help me how to do it? column1 Column2 Column3 a,b,c,d 1,2,3,4 Output Table1 ...
Akram's user avatar
  • 1
0 votes
1 answer
352 views

Upgrade Aurora from 5.6 to 5.7.mysql_aurora.2.11.1 getting tons of deadlocks and lock wait timeouts, maybe 20-30x what they were before the upgrade

Our application logs are showing an excessive amount of lock wait times, and deadlocks for queries that have not had issues previously after upgrading from 5.6 to 5.7 MySQL in AWS Aurora. Are there ...
user2966697's user avatar
1 vote
1 answer
140 views

Migrate from MySQL 5.6.33 to 5.7.41 through replication without downtime

I have to migrate from outdated standalone MySQL 5.6.33 to new server with 5.7.41(a separate server) without downtime (or just as short as possible). To accomplish that I'm going to use replication (...
Artsiom Shymanouski's user avatar
2 votes
0 answers
142 views

MySQL release memory allocated for a temp table

I'm using 5.6 MySQL database to insert tens of millions records between multiple tables. Before the insertion I send all my data into a temp memory table, for that reason I have increased both ...
Maksim Vi.'s user avatar
0 votes
1 answer
2k views

AWS RDS Can't complete Blue / Green changeover

I'm trying to do a Blue / Green changeover but it's failing with the error: Switchover from DB cluster asamazon2-cluster to asamazon2-cluster-green-xxxx was canceled due to external replication on ...
Nigel McLaughlin's user avatar
0 votes
1 answer
356 views

MySQL crashed after Out of memory (Needed 1048528 bytes)

We are facing Out of memory issue from last few days. Doing research on that said to update some of the values, but we are not sure what values we should put as per our system configurations. System ...
Hardik Shah's user avatar
0 votes
0 answers
229 views

mysqldump with --quick option crashes mysqld with out of memory

I am trying to dump the mysql database that contains a lot of databases. Most of them are empty/small, and there are only 3 large databases. Two are 10GB each, and another is 40GB. The tables of these ...
Barracuda's user avatar
  • 111
0 votes
1 answer
95 views

MySQL Database migration for 1.5 TB database size from MySQL 5.6 to MySQL 8.0.25

I have database in MySQL for an application which basically generates some kind of numbers with some other info attached with each number and the current size of the database is 1.5 TB . Now I want to ...
rahularyansharma's user avatar
0 votes
0 answers
62 views

RDS Size Larger than DB Size after Upgrade

I have an Amazon cluster that's currently at 14TB, when my DB size is less than 1GB. This only started after I upgraded the Aurora/MySQL version to 2.08.3/5.7. It started slowly ramping up the size ...
Goblin Girl's user avatar
0 votes
0 answers
28 views

Is there any need of log table

I am developing an application that has database of mysql having orders and orderdetails table. order table having column orderStatus. My question is whether it is necessary to create another order ...
Sudip977's user avatar
2 votes
1 answer
79 views

MySQL result set changes based on select list

So this is a bit weird as I've never had to deal with this issue before. I have a dataset with 4 basic joins to find the user who edited, created the record set. SELECT `tasks`.`id`, `tasks`.`title`, `...
MagicSquirrel's user avatar
0 votes
1 answer
45 views

Using having after grouping by a rand is not returnig expected results

AFAIK having statement is executed after group by (if present, otherwise it works like a simple where), so it can be used to filter grouped rows, but i'm facing a strange behavior issue I can't ...
fudo's user avatar
  • 111
4 votes
2 answers
2k views

MySQL RDS: Drop formerly huge table without freezing DB?

I am using MySQL 5.6 in AWS RDS. I want to drop a table that used to have 8 billion rows. Hearing that dropping a large table can freeze the database, I instead chose to delete the rows using a loop. ...
user984003's user avatar
-3 votes
1 answer
472 views

Import 5gb sql file into MYSQL database [duplicate]

i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname <file.sql command ...
deep.er's user avatar
0 votes
1 answer
339 views

Organize IDB tables in subfolders in MySQL data folder

I can't seem to find the answer elsewhere, and i apologize if the question has been done before, but is it a way to force a MySQL server (community edition) to organize the idb files into subfolders ? ...
Aevr's user avatar
  • 1
1 vote
1 answer
1k views

Is it okay to turn off new flags in optimizer-switch setting for making a higher version of mysql behave like a lower version?

We have been using MySQL 5.6 on Amazon Aurora RDS but are now forced to upgrade to a higher version due to end of life. We noticed that MySQL 5.7 and 8 were not performing as well as 5.6. For some of ...
Kushan Sen's user avatar
0 votes
0 answers
97 views

Is it possible to capture the output of SHOW OPEN TABLES into a table?

I need to periodically capture the output of different SHOW commands in mysql, for example SHOW OPEN TABLES. I know I can use a cron job to do something, but I'd prefer to do it internally, if ...
j4nd3r53n's user avatar
  • 221
0 votes
0 answers
231 views

Change Column size of a table

We have a mysql 5.6 cluster, 1 master 5 slaves. Requirements:- We have to modify the size of a column varchar from 20 to 35 for 2 databases. What is the best approach for doing this accross the ...
Rajan Sharma's user avatar
1 vote
1 answer
2k views

Mysql 5.6 Memory usage keeps increasing till it is killed by OOM killer

we have a production mysql 5.6.46, which is under heavy load (2000 Queries per second). The memory allocation to Mysql keeps on increasing over a period of time (sometimes in 1 day, sometime in 1 week)...
Vikas's user avatar
  • 11
0 votes
1 answer
166 views

InnoDB Buffer Pool and Buffer Log

I have been reading too many information about InnoDB parameters, but I still have a doubt. Should "innodb_log_buffer_size" be larger than "innodb_buffer_pool_size"?
Esteban Santangelo's user avatar
1 vote
1 answer
572 views

WHERE + GROUP BY + HAVING to SELECT only the rows where I have different column ID's

I am trying to select only the rows where I have different columns of the PartnerID column. I have 1 simple table which contains 3 main columns - ID, Acct and PartnerID. I am trying to GROUP BY Acct ...
Dimitar's user avatar
  • 121
0 votes
1 answer
29 views

In which case Optimizer rearranges the conditons in mysql?

recently I have added index "new_index" on this table. CREATE TABLE `y` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `shipping_provider_id` int(10) unsigned DEFAULT NULL, `...
dragon's user avatar
  • 97
0 votes
0 answers
132 views

MySQL replication out of AWS RDS lagging

I have a very large database in RDS - just under 3 TB. I have set up a replication slave in an EC2 instance (t2.xlarge with io1 disk volume for data). OS is Debian 9, DB is MySQL 5.6. The problem is ...
j4nd3r53n's user avatar
  • 221
0 votes
1 answer
49 views

How can I get MySQL to use the right index

I have a query on a table with almost 1.8M rows. I created an index new_index which includes all columns in the WHERE and ORDER clauses in order to improve performance. Now I issue the query: select ...
Johnny's user avatar
  • 140
0 votes
1 answer
2k views

MySQL not using index as expected with a WHERE IN clause

I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, ...
Johnny's user avatar
  • 140
0 votes
0 answers
257 views

insert does not work in aurora serverless?

This is in continuation of Aurora Serverless - tables are empty after successful insert, which I asked yesterday. I create a separate question to highlight what seems to be the real problem: I have ...
j4nd3r53n's user avatar
  • 221
-1 votes
1 answer
404 views

Error in Master/Master replication between Mysql 8 and Mysql 5.6

I've setup master to master replication Between a Mysql 5.6 and Mysql 8.0 server. I can get both slaves to run on both servers. Except when there is a change made on the Mysql 8.0 server (e.g. ...
ishvn's user avatar
  • 1
1 vote
0 answers
56 views

mysql mirroring

I have 2 mysql server 5.6.34 running, one on aws RDS other locally. Is it possible to set up mirroring from RDS to local one? I know that later version like 8 support replication and mirroring via ...
user3766600's user avatar
1 vote
1 answer
184 views

Inconsistent results between MySQL and MariaDB when using variables and order

I'm building an accounting table and I need to show the current balance on each transaction. Everything is working fine in my local environment (Windows 10 + MySQL 5.7.19), but the same query gives me ...
azeós's user avatar
  • 113
-2 votes
2 answers
90 views

MySQL Upgrade Procedure in a production environment [closed]

Am new to the role and employers have asked me to come up with a plan to upgrade the MySQL 5.6 on the production server to 5.7. It has partitions and MASTER-MASTER replication setup and has around ...
usernew6666's user avatar
1 vote
1 answer
584 views

Table is full even with innodb_file_per_table

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
Ankit Bansal's user avatar
3 votes
1 answer
3k views

How to fix file format in tables after upgrade of mysql 5.6 -> mysql 8.0.23 in AWS RDS?

I upgraded mysql 5.6 -> mysql 8.0.23 in AWS RDS. There is a warning: Creating indexes larger than 767 bytes on tables with redundant row format might cause the tables to be inaccessible. You have ...
Sviatoslav's user avatar
1 vote
1 answer
402 views

Elusive problem on AWS RDS MySQL

I have a provisioned, single-master RDS DB (5.6.mysql_aurora.1.23.0) with a writer and a reader. The clients are a bunch of jetty applications with a pool of up to 250 connections each. I have for ...
j4nd3r53n's user avatar
  • 221
0 votes
2 answers
68 views

Is it possible to use a default partition for all reads?

I have a legacy application which queries one of its database (MySQL on AWS Aurora) tables extensively. Lots of queries and lots of query types. 90% of the rows in this table are outdated and are ...
Johnny's user avatar
  • 140
0 votes
1 answer
533 views

How to Update Date in each row with different dates in MySQL

I want to update date column of the table with dates in the increment of 1 for all rows. How do I do it in MySQL? Table: tbl_question_of_the_day Column: date So the output that I want is: Row 1: Today ...
meetpd's user avatar
  • 101
-1 votes
2 answers
169 views

How to make transactions atomic in Mysql 5.6

How do we make the following transactions atomic? conn - is a connection to the old primary, which is being reset as replica now. Python code conn.execute('unlock tables') conn.cursor().execute('stop ...
RobinHood's user avatar
0 votes
2 answers
5k views

How to install mysql server 5.6 on ubuntu 18.04 or 20.04 system?

What are the steps to install MySQL 5.6 on Ubuntu 18.04 or 20.04 system? I know that MySQL 5.6 version is not available in 18.04 and 20.04 apt repos. Any other way to install this particular version?
Itz-easy's user avatar
0 votes
1 answer
2k views

an error in your SQL syntax; check the manual that corresponds to your MySQL server version [closed]

I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6 while creating a table it is showing me this error ERROR 1064 (42000) at line 25 in file: 'name.sql': You have an error in your SQL ...
DB guy's user avatar
  • 69
1 vote
1 answer
379 views

migrate mysql DB between two servers using replication

I need to perform some steps, I have to migrate our company live DB to a new server, if I use normal way such as lock table, mysqldump, scp, mysql restore, it take around 5-6 hours, so I want to make ...
Centos's user avatar
  • 11
0 votes
0 answers
509 views

Problem with connect mysql databases of remote

I'm getting error with connect remote mysql database. Firstly; 1-) I use windows 10 machine and my remote server also windows 10 machine. 2-) My database at locale at remote windows machine with root. ...
Kayhan's user avatar
  • 1
2 votes
2 answers
1k views

Select maximum of a count in a grouped clause

I have the following tables: Vehicles(v͟i͟n͟, model,category) Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date) vehicleSold(saleID,v͟i͟n͟,salePrice) When I join these tables using: select YEAR(Sales....
chimmy102's user avatar
2 votes
0 answers
339 views

Faster way to restore MySQL innoDB dump

I have a below MySQL configuration. # The MySQL server [mysqld] .... key_buffer = 384M max_allowed_packet = 5M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = ...
RobinHood's user avatar

1
2 3 4 5
21