Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,079
questions
1
vote
1
answer
35
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
150
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
1k
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
94
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 ...
- 19
0
votes
1
answer
474
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 ...
- 140
0
votes
1
answer
230
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
209
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
257
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
2k
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
107
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,
`...
2
votes
2
answers
114
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 | ...
- 140
0
votes
1
answer
549
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 ...
- 3
0
votes
0
answers
39
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. ...
2
votes
1
answer
149
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 ...
- 33
1
vote
0
answers
28
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
1
vote
1
answer
632
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 ...
- 11
0
votes
2
answers
291
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:
...
- 111
3
votes
2
answers
3k
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 ...
- 133
1
vote
2
answers
958
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
36
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 ...
- 379
0
votes
2
answers
236
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
21
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
164
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
290
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
3k
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
99
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
1k
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
321
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 ...
1
vote
0
answers
349
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
3
votes
1
answer
1k
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": "...
- 31
1
vote
1
answer
1k
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 ...
- 31
2
votes
0
answers
39
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
335
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
154
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
80
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 ...
- 231
2
votes
1
answer
291
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
1
answer
147
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 ...
- 341
1
vote
0
answers
229
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
626
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
27
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
1
answer
950
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
307
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
720
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
0
votes
0
answers
273
views
MySql NOT IN query gives wrong results
I have a simple database that I want to use to do some data analysis. My original data was in excel documents so, I exported them as csv file with character encoding UTF-8. I used mysqlimport to ...
- 1
0
votes
1
answer
440
views
drop and re add an index on innodb table causes slow performance
We just made a code and db update, that involves a table on production.
This INNODB table was "altered" by adding a new index and dropped the old one.
The new is made by 3 params and the old ...
- 101
0
votes
0
answers
1k
views
Expression of generated column 'birthday_filter' contains a disallowed function
I want to add a generate column using this sql command:
ALTER TABLE spark_user_demo
ADD COLUMN birthday_filter bigint
GENERATED ALWAYS AS (1000 * UNIX_TIMESTAMP(str_to_date(birthday, '%Y年%m月%d日'))) ...
- 755
0
votes
0
answers
107
views
Process for upgrading MySQL Master-Slave to a new release series version
I have several MySQL servers that I want to upgrade from 5.7.31 to the most recent release series (5.7.36 I believe), but in one test I found that replication on one server set failed, and it required ...
- 37
0
votes
1
answer
169
views
mysql --login-path connecting to the wrong database
I have a strange problem with mysql and the --login-path option.
I have configurated multiple users and databases, some local and some remote using mysql_config_editor.
But since I had my last ...
- 101
0
votes
0
answers
1k
views
ERROR 1682 (HY000): Native table 'performance_schema'.'global_variables' has the wrong structure
I’m getting stuck into a strange problem. When I check for global_variable -it says my performance schema has wrong structure:
mysql> SHOW GLOBAL VARIABLES WHERE Variable_name = 'gtid_mode';
...
- 348
0
votes
1
answer
150
views
MySQL Partitioning for transaction data
basically I have 3 identical transactional table which is contains order_id and user_id. (deposit transaction table, prepaid transaction table, and postpaid transaction table) I would to partitioning ...
- 101