Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,073
questions
0
votes
1
answer
28
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 1
0
votes
0
answers
23
views
MySQL 5.7 - could not establish connection (every 2 months or so) - how to log / ID issue?
I have a v5.7.42 MySQL server (community) running on Ubuntu 20.04.6 LTS - My issue is about every two months or so , the site will show could not establish connection to database, service mysql status ...
0
votes
0
answers
8
views
Dropping database stored with upper cases without downtime
my problem is the following. We are running a MySql server on AWS RDS and are about to upgrade from MySql 5.7 to 8.0.34. When testing this on a restored snapshot I discovered that there is a "...
1
vote
2
answers
61
views
Sudden spike in BinLogDiskUsage
Two days ago our MySQL RDS instance saw a sudden spike in BinLogDiskUsage, CPUUtilization, NetworkReceiveThroughput and NetworkTransmitThroughput.
The result was a database that was extremely slow to ...
- 13
1
vote
2
answers
30
views
data with underscore in it is being selected rather than being excluded
I hope someone could help me to clarify some doubts on this question.
I have got a table with this schema:
CREATE TABLE IF NOT EXISTS `entities` (
`id` int(6) unsigned NOT NULL,
`...
0
votes
1
answer
56
views
Is Innodb Cluster synchronous?
I was reading the InnoDB Cluster page, but I did not find any information stating whether it's synchronous or not. Is it configurable to work in either synchronous or asynchronous mode?
I currently ...
- 550
1
vote
1
answer
32
views
Can't use performance_schema in my mysql server
I am using AWS RDS aurora mysql 5.7 serverless but in RDS I have enabled the performance_schema parameter, I have connected my RDS to mysql workbench version 6.3 in mysql workbench when I am checking ...
- 11
0
votes
0
answers
13
views
About error Error Code: 1872. Coordinator stopped because there were error(s) in the buffer_pool_size (s) RDS MYSQL 5.7
My replication instance is a read replica in rds Amazon, with those configurations
select @@innodb_buffer_pool_size;
99857989632
SELECT @@innodb_buffer_pool_instances;
8
SELECT @@...
0
votes
0
answers
23
views
MySQL Master Slave Asynchronous GTID Replication Failover over ProxySQL with little downtime
This is a production setup and I have to perform a failover from Master A to new potential master B.
MySQL Version is 5.7 and CentOS 7 is the OS.
I have a simple master slave setup with A as Master ...
2
votes
1
answer
42
views
Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks
I have the following database schema (MySQL 5.7):
I have assistants that may be linked to either:
one customer (might evolve to multiple customers in the future, hence the association table)
one or ...
- 23
0
votes
1
answer
23
views
Replication Error: Last_IO_Errno: 1236 - Replicating two different channels from same master
Need little help with a production issue.
Case: I had to migrate two schema from MasterA to MasterC. It would have been a simple exercise but because it was decided to migrate schema1 first and later ...
0
votes
1
answer
69
views
MySQL 5.7 to MariaDB 11 migration index problems
I'm having a problem with one big SQL. We're migrating a MySQL 5.7 database to a MariaDB 11, and the majority of queries are running faster, but i've problems with this one, and I don't know how to ...
- 121
0
votes
0
answers
233
views
MySQL TEXT vs JSON column
We are using MySQL 5.7 and has recently introduced a new DB schema which basically stores key string and value json.
Currently it is designed as
CREATE TABLE foo (
key VARCHAR(124) NOT NULL,
...
- 101
1
vote
1
answer
360
views
AWS RDS MySQL upgrade from 5.7 to 8 failing due to ROW_FORMAT
We need to upgrade our DB to version 8, but we're getting an error in the migration log that's preventing us from upgrading.
Creating indexes larger than 767 bytes on tables with redundant row format ...
user3411972
0
votes
0
answers
72
views
MySQL client not reading ~/.my.cnf
I have a server running MySQL 5.7.42 on Ubuntu Linux. I have a config file in ~/.my.cnf containing login info to connect to it from a shell on the same server:
[client]
host = localhost
user = ...
- 261
0
votes
1
answer
101
views
Joining Columns with different character set
I am joining two tables on varchar columns having CHARACTER SET utf8 COLLATE utf8_bin and one with CHARACTER SET latin1. Indexing is not working when i am joining these two tables on this column, I am ...
0
votes
0
answers
46
views
MySQL System Timezone using Abbreviation (AST) vs Olson Timezone (America/Curacao)
I am working on a MySQL 5.7.41-44 instance running on a CentOS server.
The Linux timezone (per timedatectl) is America/Curacao (AST, -0400).
The MySQL @@system_time_zone returns AST (and not America/...
- 2,767
0
votes
0
answers
106
views
Mutliple sessions on commit status/ aurora_redo_log_flush event in AWS Aurora 2.11.1 - MySQL 5.7
Our database runs on Aurora 2.11.1 - MySQL 5.7 and we have enabled performance insights.
It's usually runs in between 15 to 50 sessions at a given time. According to the performance insights around 20 ...
- 1
3
votes
3
answers
494
views
MySQL 5.7.42, Gradual memory growth over time, memory leak?
I'm currently running a MySQL server (version 5.7.42) in production, and I've noticed a gradual increase in memory usage throughout the day. I suspect there might be a memory leak issue causing this ...
- 31
0
votes
0
answers
158
views
MySQL 5.7 vs. 8.0 Regression Performance on Ubuntu 22.04
I am testing the performance of MySQL 5.7.42 vs. MySQL 8.0.33 on Ubuntu 22.04 (amd64). Each of the test is using their own AWS EC2 instance (t2.2xlarge which has 8 core/32 GB). I ran a simple sysbench ...
0
votes
2
answers
125
views
Deadlock while concurrently updating multiple rows on the same table
We have a database where concurrent kafka streams are updating multiple rows at the same time, We are seeing deadlock occur in very few cases and I am not entirely sure what's causing it
--------------...
- 3
0
votes
0
answers
39
views
What kind of lock does INSERT ... ON DUPLICATE KEY UPDATE obtain when the UPDATE is a no-op?
I am working on an apparent performance problem possibly involving row lock contention, and I'm seeking to understand whether INSERT ... ON DUPLICATE KEY UPDATE Foo=Foo (with a no-op in the UPDATE ...
- 101
-1
votes
1
answer
293
views
Including deleted_at in unique index for upsert
I have a table with a deleted_at column, see comment.
The deleted_at column behaves like a one-way boolean event flag, i.e. once a row is deleted it can't be restored.
There is also an asynchronous ...
- 99
0
votes
3
answers
78
views
MySQL - low performance with WHERE, ORDER BY, LIMIT
I have a very weird issue, and I can't wrap my head around it.
Given a rather big table (~5M transactions).
(1) ⚠️ I am running this query - this wallet has about ~20000 transactions in total:
SELECT
...
- 101
2
votes
2
answers
261
views
mysql bin logs expiry to not to fill up disk space
I am using 5.7.37-log MySQL Community Server. my mysqld.cnf looks below:
server_id = 11
log_bin = bin.log
log-bin-index = bin-log.index
binlog_format = row
max_binlog_size = 100M
socket = mysql.sock
...
- 123
1
vote
0
answers
20
views
MySQL 5.7: Understanding SIGINT, KILL, and DECLARE HANDLER
I'm reading the documentation for MySQL 5.7 and I would like to know how to write a Stored Procedure that gracefully terminates when requested by the user.
It seems like CTRL-C or SIGINT only kills ...
- 11
0
votes
0
answers
32
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
-2
votes
2
answers
198
views
Why the order of indexed columns in ORDER BY affect performance?
I'm trying to improve performance of the following query which takes a 1+ minutes to execute:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' ...
- 101
2
votes
1
answer
346
views
How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?
I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning:
Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]' ...
- 173
1
vote
0
answers
35
views
Mysterious Increase in Server Load Every Hour with MySQL 5.7.4
I have a CentOS 7 server with MySQL 5.7.4 installed. The machine has 8 cores and 16 GB of RAM, and only the MySQL service is running. The database is not very large, and there are not many queries. ...
- 21
0
votes
1
answer
286
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
114
views
MySQL upgrade 5.7 to 8.0 with Keyring encrytption failed to complete upgrade
I ran the mysql community installer on Red Hat 7.9 over a 5.7.36 installation.
First, I ran xtrabackup for all dbs
Then I stopped mysqld
Then I ran yum install
Everything ended ok
I got the below ...
- 496
-1
votes
1
answer
2k
views
Stored procedure to Insert data into table with select statement and argument parameter [duplicate]
I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table.
I have a table with three columns (cart_id, product, sold_quantity). Now, I ...
- 239
0
votes
1
answer
94
views
Difference between current_allocation s total_allocation in MySQL sys.memory_by_thread_by_current_bytes
I'm trying to debug a memory leak in MySQL 5.7.37 and running the following query gives me insights about the threads running and the memory allocation for each thread.
select * from sys....
- 101
0
votes
0
answers
148
views
mysqld --initialize not starting MySQL5.7 on Ubuntu 20.04
Recent crash (RAID controller cache failure) made me re-create the MySQL system databases and tables for some were missing. I run MySQL 5.7 on Ubuntu 20.04.
2023-03-10T19:06:19.363955Z 0 [ERROR] --...
- 11
0
votes
0
answers
469
views
MySQL 8 taking 1000x times longer to execute this query vs 5.7 when ORDER BY is used
This query comes from an ORM (Laravel Eloquent) on a WordPress database. I upgraded to MySQL 8 from 5.7 (AWS RDS), and the performance went from pretty speedy to basically broken. API requests that ...
- 1
0
votes
2
answers
404
views
Converting from latin1 to utf8mb4 of ENUM and SET with accented characters on mysql
I am trying to convert tables from latin1 to utf8mb4 on MySQL 5.7.
Everything works fine except the tables with ENUM and SET which contains a value with an accented character. 2 types of errors are ...
- 93
0
votes
0
answers
34
views
Why does slave status show a lot of IDs for Retrieved_Gtid_Set
I have run into replication errors and eventually just rebuilt the replica. I then had to skip some more transactions and now see there is a large Retrieved_Gtid_Set, and also Executed_Gtid_Set.
I ...
0
votes
1
answer
148
views
Exporting via Phpmyadmin Charset
MySQL 5.7
I am exporting my db from my web host using phpmyadmin. This produces a single .sql file.
When I check the export with Notepad+, there are special characters in the file.
e.g. in my source ...
- 177
1
vote
2
answers
199
views
Identify the MySQL user that executes the query and changes the database
Our database has been breached. Though we changed the MySQL database user credentials, even moved to a new database, changed the server user credentials, CPanel, and FTP user credentials, and hardened ...
- 111
2
votes
1
answer
37
views
How to get the most recent result sets when grouping key and date are in separate tables
Good evening! I'm having some difficulty. I'm trying to obtain the most recent row from a few tables using SQL. I can do this using a loop in code, but it requires a lot of round trips to the database ...
- 121
0
votes
0
answers
65
views
Search from text file contain string and replace row
My database table named VmIpAddress has the following 12 columns
id, hosting_id, server_id, vm_id, ip, mac_address, subnet_mask, gateway, cidr, trunks, tag, net
A row might contain for example
"...
- 113
0
votes
0
answers
58
views
optmizing inner join of two table with million of rows
SO I have two tables - reports (~30million rows) and reportsresponse (~333million row).
I am trying to join this
SELECT
rr.ReportsResponseId,
rr.Text,
rr.type
FROM
Reports r
JOIN ...
- 103
0
votes
1
answer
119
views
Migration MySQL to Oracle 19C error ORA-00942
I'm trying to migrate a simple MySQL test database to Oracle 19c using the migration wizard available in Oracle SQL Developer, the result is that the procedure create the user and the tables but the ...
- 1
0
votes
0
answers
63
views
Can we use AWS SCT (Schema Conversion Tool) with AWS DMS for ETL and migrating data from MySQL to Amazon Aurora for MySQL?
I want to migrate data from on-premises MySQL/MySQL RDS to amazon Aurora for MySQL. While migrating, I need to convert schema as well as do some ETL to get required schema and data.
In ETL I need to ...
- 21
0
votes
0
answers
26
views
Pre-aggregating the events due to resource constraints
I've this table in the main database
create table sales (
`updated_date_key` INT(10) UNSIGNED NOT NULL,
`store_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`category` INT(10) ...
- 53
2
votes
0
answers
248
views
Why AWS RDS Mysql is not keeping binlog files for "binlog retention hours" value?
I have an AWS RDS Mysql master with 1 AWS RDS slave and 1 ec2 mysql slave replicating this master instance. I already set the binlog retention hours to 7 days with the command:
CALL mysql....
- 21
0
votes
1
answer
659
views
I get a syntax error whenever I use `CHANGE REPLICATION SOURCE TO` in mysql
This is the MYSQL query:
CHANGE REPLICATION SOURCE TO SOURCE_HOST='3.94.185.28', SOURCE_USER='replica_user', SOURCE_PASSWORD='password', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_L
OG_POS=154
ERROR ...
2
votes
3
answers
384
views
Is it safe to enable Fast DDL in Production
We have a Database running Aurora MySql V5.7. We would like to enable Fast DDL to improve the speed of Adding a Column to a large database. According to the documentation, this feature is Enabled in ...
- 21
1
vote
1
answer
227
views
effectiveness of creating index for ORDER BY column when used with many WHERE clauses
Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
- 11