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

Writing a ledger with high concurrency

I'm trying to achieve a high level of writes on a single account for a ledger, however, if there is a high level of concurrency, things start to slow down significantly. My current setup is basically ...
user avatar
  • 1
1 vote
1 answer
29 views

How can I correct this mysql join query and put 1 to many in the same row?

This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to ...
user avatar
1 vote
0 answers
31 views

MySQL occurring responds "Error 9001: Max connect timeout" even root account

We face a strange experience. There is the MySQL we are running on GCP. The metrics (current connection count, CPU, memory, IO, and disk) aren't abnormally high. After 06:25:01, all our connections ...
user avatar
1 vote
2 answers
30 views

Convert Long list of IN clause into a temporary table

Instead of using a list of items for IN Clause I'm trying to use a temporary table. The code I used previously. SELECT * FROM table1 AS t1 WHERE itemid in ('3363134,5189076, ...) The code I'...
user avatar
1 vote
1 answer
10 views

innodb cluster group replication Error_code: 1594

So we have an innodb cluster consisting of 4 nodes. one of the nodes crashed (OS crash), while starting the node it shows the following error: 2022-06-13T06:46:02.529058Z 491 [ERROR] Slave SQL for ...
user avatar
0 votes
1 answer
17 views

Why does my JDBC keep setting session variables?

I'm looking at the Performance Insights for MySQL Aurora on AWS and among the top queries I see: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ SET SESSION TRANSACTION ISOLATION LEVEL READ ...
user avatar
  • 130
0 votes
0 answers
23 views

Design proper schema for flexible entity field

I have some entities modeled like on the picture. Every entity have field classifications which contains list of Classification. Classification itself can have any name and value, all represented as ...
user avatar
  • 101
0 votes
3 answers
100 views

Slow Import Performance & my.cnf review - MySQL v5.7.32

UPDATED SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; SHOW FULL PROCESSLIST; STATUS; MySQLTuner 1.9.8 LIMITS; --> https://pastebin.com/ebLg1HXV I have 25 MySQL Databases running on my system (details ...
user avatar
0 votes
0 answers
10 views

Finding the MySQL binlog positions to restore replication

I have a large database where replication became broken (a query to delete rows ran on the slave directly by mistake) The decision was taken to restore the slave from the master. I've made an export ...
user avatar
0 votes
1 answer
26 views

Mysql choosing really poor index without IGNORE INDEX hint

Problem The following query produces a really inefficient query plan: select `event`.`id` as `eventId` from `alert` inner join `event` on `event`.`id` = `alert`.`eventId` inner join `property` on `...
user avatar
0 votes
0 answers
16 views

mysqldump Silently Fails to Backup One Table

I'm using an application that uses MySQL Server 5.7.16.0 on a Windows Server 2012 R2 server. I cannot update mysql, because the app depends on this version and handles updates of it. I use mysqldump ...
user avatar
3 votes
1 answer
43 views

InnoDB buffer getting higher every day. How to flush the buffer pool?

I'm managing a db server for a lot of people connecting to it and one of the tools we are using is not well optimized so it uses a lot of JOIN queries without indexes. The problem is that lately we've ...
user avatar
1 vote
0 answers
29 views

Running `ALTER TABLE` trips into a `Index PRIMARY is corrupted`

we've currently come across a problem when trying to run a DDL in a table which we don't know how to sort out. Given our schema: CREATE TABLE `some_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `...
user avatar
1 vote
2 answers
51 views

Why is the query planner only using one column of two in the index?

I have a table on MySQL 5.7 with the following definition and indexes: +-------------------------+---------------+------+-----+---------+----------------+ | Field | Type | ...
user avatar
  • 130
0 votes
0 answers
6 views

Percona MySQL 5.7 to 8.0

I wondered if anyone has successfully upgraded percona 5.7 to 8.0 with a fully populated database. All the articles I've read suggest it is possible but I've tried twice in a test environment and ...
user avatar
  • 1
0 votes
0 answers
29 views

What is the best way of implementing expiration semantics on MySQL?

Presently I am using Redis as a data store, it all goes well until it doesn't namely we have records that suddenly disappear but there's no rhyme or reason we can find it and it's generally one offs. ...
user avatar
1 vote
1 answer
17 views

dual-password-like feature for MySQL before v 8.0

tldr; Is there a dual-password-like feature for MySQL v.5.x ? Problem I have a MySQL server that needs to temporarily allow two passwords for one account, due to legal constraints that impose DBMS ...
user avatar
  • 13
1 vote
0 answers
17 views

How to set administrative user in Mysql 5.7

I am trying to run a scan from Nessus against Mysql 5.7 database I am getting the following error: Ensure Only Administrative Users Have Full Database Access 'mysql.user' Perform the following actions ...
user avatar
0 votes
1 answer
40 views

MySQL Indexes on all column combinations

I have a table that basically represents the log of records CREATE TABLE records ( id varchar(36), -- uuid text blob, user_id bigint, cluster_id bigint, status_id ...
user avatar
0 votes
2 answers
65 views

The UPDATE queries is 50x slower than its SELECT query

The UPDATE queries is 50x slower than its SELECT query. I have a table named sync_read and the have the following columns id club_id created_at user_id queue_id The table have the following Indexes: ...
user avatar
1 vote
0 answers
96 views

AWS RDS Aurora mysql Query Cache

AWS RDS mysql 5.7 Current Query Cache is by default enabled on Aurora mysql. (16GB query cache size, on 300GB of RAM). Am facing some deadlocks from Hangfire and it doesn't show anything to having ...
user avatar
  • 111
0 votes
0 answers
7 views

Dynamic table lookup for row counts

I would like to set up a view that I can check for row counts based on a list of tables. For example, CREATE TABLE `tbl_lookup` ( `tbl_name` varchar(30) ); INSERT INTO `tbl_lookup` (`tbl_name`) ...
user avatar
  • 1,194
1 vote
2 answers
35 views

how to properly index a varchar column that won't use LIKE

I've been experimenting with EXPLAIN and noticed that the row count was big for our query so I tried adding an index to the VARCHAR column. First I added FULLTEXT but it ended up just making the query ...
user avatar
0 votes
0 answers
13 views

Finding duplicate records in a subset

I have 3 tables: users orders order_items I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
user avatar
  • 359
0 votes
2 answers
30 views

Is the use of Stored Procedures in MySQL 5.7 recommended?

I am working in a company where we have an ERP system running on a dedicated server with LAMP (2 x Octa-Core E5-2620 V4 2.1GHz w/HT 32 threads with 32GB of RAM and two 480 GB SSD in hardware based ...
user avatar
1 vote
0 answers
19 views

How to determine from which script/service access is denied for specific user in Mysql?

On my client server there is a lot of services and scripts running. In MySQL error.log I get this NOTE: 2022-04-14T19:32:31.962446Z 490136 [Note] Access denied for user 'root'@'localhost' (using ...
user avatar
0 votes
0 answers
49 views

Locker booking system db design help

Good afternoon I'm trying to design the database for a locker booking system but being the first time that i design such kind of db i'm facing many issues and doubt that i cannot solve myself as i can'...
user avatar
3 votes
1 answer
32 views

MySQL: Alter user permissions in one go instead of manual revokes/grants

My team has been managing permissions (MySQL <=5.7) by creating "roles" (not MySQL 8 roles) and then programmatically calculating the target permissions. Then, using those we get the ...
user avatar
0 votes
0 answers
439 views

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 95

I am upgrading from MySQL 5.7.33 to MySQL 5.7.37 on a CentOS 7 server. For a minor upgrade, I normally accomplish this by removing the old binaries, replacing them with the new binaries, restarting ...
user avatar
1 vote
0 answers
7 views

Delete Temporary Tables from Abandoned ALTER Query

I had some big tables, where the running ALTER query timed out. Is there a way to remove the temporary tables created by this ALTER query, as they are taking up a huge amount of storage. I am on RDS. ...
user avatar
0 votes
1 answer
46 views

On duplicate key update throws an en duplicate entry exception

I have a table with the following structure create table stats ( id int auto_increment primary key, serial_no varchar(20) not null, epoch int not null, ...
user avatar
  • 133
0 votes
2 answers
22 views

How mysql store data and their encoding

I have few databases in latin1 and i will migrate it to utf8. I have some characters like 'œ' that are utf8 characters. I want to know how mysql store the encoding ? Cause if it stores utf8 characters ...
user avatar
0 votes
0 answers
45 views

General error: 2006 MySQL server has gone away. All settings fine

I have 2 linux servers. 1 is running apache2 with an PHP application and the other one is running mysql 5.7. (so a remote connection to DB) PHP applications tried to create a BIGBLOB from a file (90MB)...
user avatar
1 vote
0 answers
36 views

Drop Foreign Key that does not exist?

So I just tried to drop a foreign key constraint from a table which did not exist. Long story short, we had a script which looked for FK constraints from information_schema, it looked like this: ...
user avatar
  • 111
2 votes
1 answer
181 views

Aurora upgrade 2 to 3 / MySql 5.7 to 8.0: schemaInconsistencyCheck

In our attempts to upgrade our Aurora 2 MySql 5.7 DB to Aurora 3 MySql 8.0, we are running into a pre-check that we are unable to resolve. The error is: { "id": "...
user avatar
  • 21
0 votes
1 answer
135 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 ...
user avatar
2 votes
0 answers
30 views

Table going into Repair mode during upgrade

Recently I was doing an upgrade in production system from MySQL 5.6 to 5.7. there is a particularly large innodb table about 2.5 TB which uses hash partitioning. when I ran mysql_upgrade I noticed ...
user avatar
0 votes
1 answer
40 views

What is the performance overhead of IFNULL in the select statement of mysql 5.7+ query

I've recently made an upgrade to a mysql query performed by my web server when serving request. There are two main permutations of the query, one will typically be limited to returning 500 rows and ...
user avatar
1 vote
1 answer
79 views

How to index a query with many joins

I have this query with this schema that takes ~8s on my laptop and longer on the vm running the service. SELECT DISTINCT `mofs`.* FROM `mofs` INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `...
user avatar
  • 111
0 votes
0 answers
21 views

how to make the mysql add column fast

I have a table in MySQL 5.7, this table contains 1000000+ rows data, now I want to add colums into the table like this: ALTER TABLE `zhuolian-report-uat`.`home_overview_daily` ADD COLUMN `...
user avatar
  • 477
0 votes
0 answers
18 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 ...
user avatar
  • 221
0 votes
0 answers
29 views

Where is mysqlpump log file in Linux/Ubuntu located

I am neither a DB administrator, nor a DevOps but I need to migrate a very large table (~500M rows) from one MySQL schema to another. For doing so, I use mysqlpumpbackup tool. Also, since the DB is an ...
user avatar
  • 141
2 votes
1 answer
64 views

Is there a proper order to run mysqlcheck commands in for periodic maintenance?

Is there a proper order in whcih to run the various mysqlcheck commands? e.g. CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE and also FLUSH TABLES and FLUSH LOGS. I wrote this script that I ...
user avatar
  • 143
0 votes
0 answers
41 views

mysqlbinlog not showing 'pseudo-SQL' comments for BINLOG statements

We are running Percona mysql server 5.7.36-39.1 and corresponding version of mysqlbinlog. I'm running mysqlbinlog against a binlog file and position (taken from the output of show master status;) with ...
user avatar
  • 311
1 vote
0 answers
64 views

Migrate MSSQL 2008 database to MySQL 5.7 or 8.0

I am trying to migrate an old database runing in SQL Server 2008, to MySql 5.7 or 8.0 using MySql Workbench (Migration wizard). It gave me this errors Traceback (most recent call last): File "...
user avatar
1 vote
1 answer
39 views

Should a client connecting to a server via a unix-socket be encrypted?

Does a client connection need encryption if the only connection option is via unix socket? e.g. my.cnf [mysqld] socket = /run/mysqld/mysqld.sock I'm considering these options, but I'd prefer not to ...
user avatar
  • 143
0 votes
1 answer
18 views

How can MySQL be optimized for unattended operation on unreliable hardware?

I run some personal services on a not-entirely-reliable single-board computer. Sometimes it locks up and needs to be power cycled. One of these services is currently configured to use MySQL with ...
user avatar
  • 101
0 votes
2 answers
96 views

Very slow large stored procedure

I have a rather large stored procedure that processes over a hundred thousand records. This SP has been running beautifully until last week when the database server was hit with a ransomware attack. ...
user avatar
-1 votes
1 answer
21 views

Error while declaring procedure

When I run this query it returns me a count of null values of the column country (select sum(case when (country IS NULL) then 1 else 0 END) as 'null_flag' from city) whereas ...
user avatar
1 vote
1 answer
116 views

RDS slow queries after few weeks

Since I updated from MySQL 5.6 to 5.7 I started to have very slow queries in my Aurora RDS instance. In my local XAMPP the same queries took a few seconds but in RDS some of them were about 6 minutes. ...
user avatar
  • 113

1
2 3 4 5
21