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
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 ...
user avatar
  • 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. ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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: ...
user avatar
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 ...
user avatar
  • 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`) ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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'...
user avatar
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 ...
user avatar
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 ...
user avatar
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. ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
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)...
user avatar
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: ...
user avatar
  • 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": "...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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` = `...
user avatar
  • 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 `...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 "...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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. ...
user avatar
-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 ...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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(...
user avatar
  • 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日'))) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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'; ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 | +--------------...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar

1
2 3 4 5
21