Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,010
questions
0
votes
0
answers
4
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
24
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
14
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
16
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
33
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
3
answers
61
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
0
votes
0
answers
16
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 ...
- 101
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
27
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
12
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 ...
- 349
0
votes
2
answers
24
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
18
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
34
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
28
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
131
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
6
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
16
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
16
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
31
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
27
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
107
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
107
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
29
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
32
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
77
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
18
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
14
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
24
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
44
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
35
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
47
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
33
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
17
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
60
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
16
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
87
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
32
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
43
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
5
views
how to handle the minisecond in timestampdiff in MySQL 5.7
I am using this command to do a generate column:
ALTER TABLE spark_user_demo
MODIFY COLUMN birthday_filter_second bigint
GENERATED ALWAYS AS ((1000 * timestampdiff(MINISECOND,'1971-01-01',str_to_date(...
- 477
0
votes
0
answers
141
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日'))) ...
- 477
0
votes
0
answers
21
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
34
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
53
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';
...
0
votes
1
answer
19
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
0
votes
0
answers
70
views
how to get last MySQL binlog file position on instance restore
I'm writing a dr creation script. I do not want to use aurora built-in feature for dr.
what we have been doing until now are the following manual steps:
restore aurora snapshot snapshot
search in the ...
- 373
2
votes
2
answers
458
views
Does the timestamp type have (hidden) milliseconds?
The updated column was created as
| updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
a simple SELECT gives
+---------------------+
| updated |
+--------------...
- 395
0
votes
2
answers
35
views
MySQL - Planning Cost Higher Than Number of Blocks Occupied in Disk
There is a table dept and it has 100 rows, all nicely tucked in 1 block, as seen in the mysql.innodb_index_stats table.
table_name
index_name
stat_name
stat_value
sample_size
stat_description
dept
...
- 165
2
votes
1
answer
24
views
MariaDB index chosen differs on ARM and x64 based procesors
When I have been using mariaDB on apple M1 chip, I discovered some diference between index chosen in select statement versus x64 server linux system with this query:
explain
SELECT z.id_hlavni AS ...
- 21
0
votes
1
answer
76
views
OPTIMIZE TABLE seems to "lock" tables (innodb)
During executing an OPTIMIZE TABLE on big tables the process seems to "lock" the tables. Is there something you can do to speed up the process or prevent it from "locking" the ...
- 123
0
votes
0
answers
11
views
Does turning on mysql strict mode have any performance benefits
I am in the process of identifying what changes are needed to enable strict mode on a mysql 5.7 database. The most obvious one is making sure we don't have anything which would conflict with the ...
- 101