Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [mysql-5.7]

MySQL version 5.7 - please also tag with mysql for search purposes.

1076 questions
261
votes
5 answers

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql , but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump…
subhanshu kumar
64
votes
1 answer

What is the meaning of filtered in MySQL explain?

As described here in the MySQL docs: The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the…
Iman Tumorang
  • 695
  • 1
  • 6
  • 12
35
votes
3 answers

How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?

Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes? Or should this question be on StachExchange?
Hvisage
  • 483
  • 1
  • 4
  • 6
34
votes
1 answer

What is mysql_native_password?

I was trying to set password for root. When I run: mysql> SELECT * from mysql.user where User="root"; It…
d a i s y
  • 443
  • 1
  • 4
  • 7
23
votes
1 answer

InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT

I am working with mysql version 5.7.14 (homebrew installation on OS X El Capitan). My system did not shutdown properly while the mysql was running and after rebooting when i try starting the mysql daemon i am getting the below…
Dushyant Sabharwal
  • 391
  • 1
  • 3
  • 7
14
votes
1 answer

MySQL deadlock: upsert query acquiring gap lock twice?

I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen. Here is the deadlock output: ------------------------ LATEST DETECTED…
Agniva De Sarker
  • 141
  • 4
14
votes
1 answer

invalid default value (mysql 5.7) for timestamp field

EDIT: When updating an existing database from mysql 5.6 and executing: UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00'; This produces: #1292 - Incorrect datetime value: '0000-00-00 00:00:00'…
Chris Muench
  • 711
  • 5
  • 15
  • 34
13
votes
3 answers

MySQL 5.7 Community Server End of Life/Support

Is there any official document that outlines the date till which MySQL Community Server 5.7 will receive security updates/patches? I see that page 20 of this document from Oracle outlines Premier and Extended Support end date for MySQL 5.7, but I am…
code2002
  • 131
  • 1
  • 1
  • 3
12
votes
2 answers

Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER statements for every table and column that needs to…
Stephen Ostermiller
  • 423
  • 1
  • 3
  • 10
12
votes
2 answers

MySQL - UUID/created_at cursor based pagination?

For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we know where to continue paginating from where we…
Wonka
  • 145
  • 2
  • 2
  • 10
12
votes
3 answers

Won't start: InnoDB: Corrupted page ... Tablespace was not found

After a server crash (Ubuntu 16.04), when I try to start Mysql (Mysql 5.7) using innodb_force_recovery=0 it doesn't start and error.log shows: InnoDB: Checksum mismatch in datafile: ./panel_financiero_v2/kpis_analytics.ibd, Space ID:93, Flags: 33.…
Felipe Alonso
  • 223
  • 1
  • 2
  • 6
12
votes
6 answers

High CPU system time usage on MySQL server

A little backstory, some time ago we started to experience high CPU system time on one of our MySQL databases. This database was also suffering from high disk utilization so we figured that those things are connected. And since we already had plans…
chimmi
  • 119
  • 1
  • 2
  • 10
11
votes
1 answer

Does latin1 have performance benefits over utf8?

I have a table in utf8 with > 80M records and one of the columns (char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL) can contain just latin symbols ([a-zA-Z0-9]). Does it have the sense to convert this column into latin1? MySQL doc says: To save…
Alexey
  • 288
  • 4
  • 14
10
votes
1 answer

How to add index to a big table with 60M records without downtime?

we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our production DB. That table is used very frequently in…
Rohan
  • 53
  • 1
  • 8
10
votes
5 answers

MySQL :: How to run ANALYZE TABLE for all tables in a database

I'm using MySQL 5.7 How can I run ANALYZE TABLE for all tables in a database. It should work if new tables are added to the database in future. This is to refresh the statistics of table indexes.
Satish Gadhave
  • 203
  • 1
  • 2
  • 6
1
2 3
…
71 72