Questions tagged [mysql-5.7]

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

Filter by
Sorted by
Tagged with
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 ...
Marek's user avatar
  • 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 ...
James Gaul's user avatar
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 "...
northmoose's user avatar
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 ...
RED_Tom's user avatar
  • 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, `...
Mario Raymundo's user avatar
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 ...
Potter's user avatar
  • 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 ...
Paras Sahu's user avatar
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 @@...
Marco Antonio's user avatar
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 ...
Avinash Pawar's user avatar
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 ...
Merinorus's user avatar
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 ...
Avinash Pawar's user avatar
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 ...
Dimas's user avatar
  • 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, ...
rajeshnair's user avatar
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 ...
user avatar
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 = ...
Synchro's user avatar
  • 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 ...
Manish Kumar's user avatar
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/...
GWR's user avatar
  • 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 ...
senrick's user avatar
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 ...
George's user avatar
  • 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 ...
Brian Estrada's user avatar
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 --------------...
owl's user avatar
  • 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 ...
cbmanica's user avatar
  • 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 ...
mozey's user avatar
  • 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 ...
cweinberger's user avatar
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 ...
santhosh's user avatar
  • 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 ...
Sam's user avatar
  • 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 ...
Akram's user avatar
  • 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' ...
CKT's user avatar
  • 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]' ...
Ben Holness's user avatar
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. ...
Mr.spark's user avatar
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 ...
user2966697's user avatar
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 ...
Jesus Uzcanga's user avatar
-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 ...
Jaimin Sutariya's user avatar
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....
Sri's user avatar
  • 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] --...
svet0slav's user avatar
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 ...
Will's user avatar
  • 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 ...
Toto's user avatar
  • 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 ...
VagrantPaladin's user avatar
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 ...
TheOrdinaryGeek's user avatar
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 ...
Haijerome's user avatar
  • 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 ...
jeremysawesome's user avatar
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 "...
Toodarday's user avatar
  • 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 ...
Abhishek Anand's user avatar
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 ...
Tinfo's user avatar
  • 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 ...
Sagardevd's user avatar
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) ...
SQLProfiler's user avatar
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....
Bundy Ugh's user avatar
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 ...
Paa Kojo Annan's user avatar
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 ...
Captain Cool's user avatar
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 ...
hskris's user avatar
  • 11

1
2 3 4 5
22