Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,079
questions
1
vote
1
answer
15
views
Identify sequences of events starting at well-known start locations, end locations not predetermined, and WITH MYSQL 5.x
I'm working within the limitations of MySQL 5.x.
Several security guards are responsible to walk around a property and visit checkpoints. Two specific gates permit entry and each guard must begin a &...
- 11
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
1
answer
48
views
Will Dropping a corrupt table free up space in MySQL
We have a MyISAM Table in our database which has around 97 Gb worth of data with resulted in 95% disk utilization. Due to this we started clearing the data from the table by deleting the rows but this ...
0
votes
0
answers
14
views
Need to build MySQL replication with TSL cert between onPremises server as Master and Azure flex server as Slave
I have a requirement
Need to build MySQL replication with TSL cert between onPremises server as Master and Azure flex server as Slave .
On Master & slave host ssl is enabled . also i have followed ...
1
vote
2
answers
54
views
How to Identify Tables with Index Size Issues during MySQL 5.7 to 8.0 Migration?
I'm trying to migrate from MySQL 5.7 to MySQL 8.0 for my application's database. However, the precheck failed with an incompatibility error "25," stating that tables with redundant row ...
- 13
0
votes
0
answers
8
views
How Innodb Concurrency Tickets working in sorting by mysql?
Innodb concurrency tickets how reduced when the sorting performing query running? is tickets applied for sorting or not? If applied how it was calculated by mysql and is there any performance issue ...
- 1
0
votes
0
answers
10
views
What is the Strategy for Rotating MySQL 5.7 certs with Active/Passive Pool?
Aside from migrating while we upgrade to 8.0, which is an option, what have people done workflow/strategy to rollout and rotate MySQL SSL credentials with zero downtime?
- 169
1
vote
1
answer
80
views
Moving from MySQL 5.7 to MySQL 8.0
I'm currently running MySQL 5.7 in one of the key servers. This version reaches end of life soon, so it's time to move to 8.0. Or give it a shot and jump on 8.1?
This one is also running on dated ...
- 13
1
vote
1
answer
70
views
Restoring MySQL Database from mysqldump file produces seemingly random syntax errors
Sorry for the long title I wasn't sure how else to make it distinct from the auto-search results.
I have a large-ish dump file (~44GB) and I'm trying to import this into MySQL on a different server.
...
- 25
0
votes
1
answer
57
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 ...
- 13
0
votes
0
answers
36
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
11
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
3
answers
171
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
108
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 ...
- 630
1
vote
1
answer
49
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
15
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
49
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
52
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
24
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
88
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
503
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
571
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
129
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
147
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
59
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,777
0
votes
0
answers
158
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
690
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
206
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
223
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
69
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
454
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
169
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
515
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
23
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
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
-2
votes
2
answers
305
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
579
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
39
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
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
146
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 ...
- 506
-1
votes
1
answer
3k
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
111
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
187
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
601
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
505
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
35
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
168
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
373
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