Questions tagged [mysql-5.6]
MySQL version 5.6 - please also tag with mysql for search purposes.
1,025
questions
0
votes
0
answers
31
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 > ...
- 69
1
vote
0
answers
24
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 ...
- 119
0
votes
1
answer
36
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 ...
- 13
1
vote
2
answers
96
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 ...
- 13
1
vote
0
answers
130
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 ...
insim
0
votes
0
answers
39
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
...
- 1
0
votes
1
answer
313
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 ...
1
vote
1
answer
130
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 (...
2
votes
0
answers
120
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 ...
- 121
0
votes
1
answer
1k
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 ...
0
votes
1
answer
253
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 ...
0
votes
0
answers
164
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 ...
- 111
0
votes
1
answer
77
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 ...
- 111
0
votes
0
answers
58
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 ...
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 ...
- 1
2
votes
1
answer
57
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`, `...
0
votes
1
answer
44
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 ...
- 111
2
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. ...
- 131
-3
votes
1
answer
442
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 ...
- 1
0
votes
1
answer
276
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 ?
...
- 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 ...
- 31
0
votes
0
answers
80
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 ...
- 231
0
votes
0
answers
213
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 ...
1
vote
1
answer
1k
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)...
- 11
0
votes
1
answer
135
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"?
1
vote
1
answer
384
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 ...
- 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,
`...
- 97
0
votes
0
answers
124
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 ...
- 231
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 ...
- 140
0
votes
1
answer
1k
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, ...
- 140
0
votes
0
answers
235
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 ...
- 231
-1
votes
1
answer
345
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. ...
- 1
1
vote
0
answers
49
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 ...
- 11
1
vote
1
answer
166
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 ...
- 113
-2
votes
2
answers
89
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 ...
- 21
1
vote
1
answer
536
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;...
- 61
2
votes
1
answer
2k
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 ...
- 45
1
vote
1
answer
346
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 ...
- 231
0
votes
2
answers
57
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 ...
- 140
0
votes
1
answer
478
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
...
- 101
-1
votes
2
answers
167
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 ...
- 49
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?
- 31
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 ...
- 69
1
vote
1
answer
346
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 ...
- 11
0
votes
0
answers
422
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.
...
- 1
2
votes
2
answers
817
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....
- 45
2
votes
0
answers
304
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 = ...
- 49
1
vote
1
answer
2k
views
Find users having grants on a table in MySQL
We grant access to users with the following
grant select, insert, update, delete on product_events to 'product_admin'@'%' ;
Now, like this there are a lot of tables, and a lot of users.
I want to ...
- 193
0
votes
0
answers
239
views
Store Procedure Two different database and join help
I try to write store procedure that ı have a question.Firstly I have two db in same local server.One of them is hfcenta_test db has agency old datas and it has country code column with country name....
- 1
0
votes
1
answer
94
views
How can we implement "A student can enroll in up to 4 units in a semester "
I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into....
- 45