Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,024
questions
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 ...
- 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 ...
- 113
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 ...
- 11
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'...
- 111
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 ...
- 11
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 ...
- 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 ...
- 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 ...
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 ...
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 `...
- 121
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 ...
- 124
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 ...
- 143
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,
`...
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 | ...
- 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 ...
- 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. ...
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 ...
- 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 ...
- 111
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 ...
- 1
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:
...
- 101
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 ...
- 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`) ...
- 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 ...
- 113
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 ...
- 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 ...
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 ...
- 11
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'...
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 ...
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 ...
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.
...
- 123
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,
...
- 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 ...
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)...
- 1
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:
...
- 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": "...
- 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 ...
- 21
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 ...
- 21
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 ...
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` = `...
- 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 `...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 "...
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 ...
- 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 ...
- 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. ...
-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 ...
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. ...
- 113