Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
430
questions
0
votes
0
answers
36
views
MySQL Workbench mysqldump: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
When using workbench on Mysql 8, I get the following error:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
I'...
- 101
0
votes
1
answer
18
views
Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table
I'm attempting to add an index to a very large table (2B rows) and the command fails after a few hours with:
ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine
I'm finding the ...
- 3
1
vote
2
answers
37
views
how to downgrade mysql on Debian 10 from 8.029 to 8.02x
I have MySQL installed on my test server (Debian 10). I recently updated it to the newest version (8.0.29), but there are some issues with it so I want to go back to an older version (8.0.28). But I ...
- 1,920
0
votes
0
answers
38
views
How to create correct mysql update query using window function for grouped items
We have a table that you could label as a summary table that we need to update every so often from another table that contains dated sales rows. How do you create a query, along with the index for it, ...
- 146
0
votes
0
answers
38
views
mysql: setting default default_collation_for_utf8mb4?
How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ?
I tried
[mysqld]
init_connect='SET ...
- 111
0
votes
0
answers
25
views
MYSQL8 unknown runaway memory
First thank you, I am not very knowledgeable in MYSQL and am purely self taught.
I moved from metal to AWS(EC2, with maxed out IOPS:3000) and in the process we also upgrade from MYSQL5.6 to MYSQL8.0....
1
vote
2
answers
23
views
Change location of innodb_temporary
I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see:
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
***...
- 111
1
vote
0
answers
27
views
How to create a comprehensively auditable database that is updated from multiple concurrent sources?
I have various different Python scripts running concurrently updating a MySQL database from a circa 15 API and web scraping sources. In another script a continuous loop runs that queries batches of ...
- 111
1
vote
0
answers
37
views
Join JSON column using an index
I have two tables:
definition (7,000 rows):
+--------------+----------+
| definitionId | nameRef |
+--------------+----------+
| 1 | [1] |
| 2 | [1,2] |
+--------------+-...
- 155
0
votes
0
answers
32
views
Error while running mysql_upgrade
Recently upgraded Mysql from 8.0.20 to 8.0.28 ( removed the older rpms and installed newer ones)
and started Mysqld in RHEL7. All seems good except that I see below error when trying to run ...
0
votes
0
answers
23
views
mysql information_schema.columns missing data
i've a mysql 8.0.28 instance with a set of "broken" views.
When i run this query
mysql> select * from information_schema.columns where table_name = 'vw_accounts';
Empty set, 1 warning (0....
- 1
1
vote
2
answers
154
views
Performance of the same MySQL database is different on different servers
I have two servers:
Server A: AMD Epyc 7371 - 16c / 32t - 3.1 GHz / 3.8 GHz 256 GB ECC 2400 MHz 2 × 960 GB NVMe SSD MySql 8.0.26
Server B: Dual Intel Xeon Gold 6242R - 20c / 40t - 3.1 GHz / 4.1 GHz ...
0
votes
1
answer
75
views
MySQL 8 Replication
I've got this error message:
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin....
Ayaa Ahmed
1
vote
1
answer
51
views
How to create a file in query with read and write permission for all users in mysql 8
When I run this query on mysql 5.7:
SELECT CURDATE() INTO OUTFILE '/var/lib/mysql-files/test.csv';
It creates test.csv with the following permissions:
-rw-rw-rw- 1 mysql mysql 11 Mar 18 04:27 ...
- 121
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
0
votes
2
answers
36
views
How to select the value of `show global status like 'Com_stmt_pre%';`
I need to read this value in a bash script so want to "select" the value but I'm not sure how. Currently I run show global status like 'Com_stmt_pre%'; which returns:
But I need to actually ...
- 575
1
vote
0
answers
26
views
Error MY-000067 - unknown variable 'validate-password=FORCE_PLUS_PERMANENT'
I'm struggling with password valaidity settings in 8.0.28. In this version component is in use instead of plugin.
Which means password validity component installed via:
INSTALL COMPONENT 'file://...
0
votes
0
answers
36
views
Will a query which uses IS NULL always perform bad on large tables?
I've got the following table :
CREATE TABLE `file` (
`id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`track_id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`...
- 113
0
votes
0
answers
13
views
phpMyAdmin Export duplicates column name
I have a MYSQL 8.0.28 server as well as phpMyAdmin running in docker containers. Everything seems to work except that when exporting the database to a dump file through phpMyAdmin one of the views has ...
0
votes
0
answers
32
views
Cannot find file with secure_file_priv in MySQL 8.0 on Windows
Trying to find the local_infile command in MySQL 8.0 on Windows. Getting the following error...
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
I ...
- 1
-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 ...
0
votes
1
answer
40
views
How to use json_arrayagg function on more than one pivot table
As the title suggests consider the following example where there are three tables and two pivot table
#Tables
user(user_id char(42));
role(role_id char(42));
task(task_id char(42));
#Pivot tables
...
1
vote
1
answer
20
views
Inndob Cluster - Automatically collect the output from mysqlsh?
Is there a way to take the output from mysqlshell and use it in a script or similar.
I have set up a basic cluster for testing (3 x MySQL 8.0.27 databases, running on Debian 11).
I am now trying to ...
- 1,920
0
votes
0
answers
60
views
replicate-wild-do-table causes unknown database error My-001049 for CREATE PROCEDURE on not replicated schema
OS: RHEL 7.7
Percona server 8.0.26-16
I am trying to create a simple replication topology with one master and one slave. The master has 9 schema out of which three are being replicated to the slave.
...
0
votes
0
answers
33
views
Why JSON indexing take less storage than multiple columns in MySQL 8?
I am doing a quick performance comparison for 10 000 rows on MySQL 8.0.27 between
a table multiple_cols with 2 columns num INT and str CHAR(200) having each an index,
a table json_indexing with 1 ...
- 101
1
vote
1
answer
52
views
Stored procedure on OVHCloud MySQL server fails with Unknown column '' in 'field list'
I'm using the jaro winkler stored procedure here: https://gist.github.com/TheHiddenHaku/7229861. This runs fine on my local MySQL server (version 8.0.19) but when trying to use the same function on ...
- 575
0
votes
0
answers
17
views
MySQL 8 Mysqldump is slow after updating from mysql 5.1
We recently upgraded from MySQL 5.1 to 8.0.26. We are currently running the following command for our backup: mysqldump -F -A -R --source-data=2 --single-transaction -u $myuser -p$mypw -P 3306 -S /var/...
- 1
0
votes
0
answers
18
views
Which option should I use to avoid error #2006 on MySQL (Docker Compose)
I am getting error #2006 - MySQL server has gone away while trying to restore a 11.9 mb database using PhpMyAdmin. It worked with a 4.70 mb database, but not with this one
I thought that if I put --...
- 101
0
votes
0
answers
247
views
MySQL 8.0 timestamp CURRENT_TIMESTAMP sets date to '0000-00-00 00:00:00' (was ok in 5.7)
The table T has this field
+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra ...
- 395
0
votes
1
answer
72
views
Access denied for user 'root'@'localhost' (using password: YES) after setting mysql_native_password
I have a brand new installation of MySQL on Ubuntu 20.04.
During the installation I picked a password for root. After the installation, I did the following:
sudo mysql -u root
It logged me in without ...
- 101
0
votes
0
answers
39
views
MySQL: Can't login after forgotten root password
I have a CentOS Linux 8 machine with mysql Ver 8.0.26 I forgot the root user password tried to reset with the following commands
systemctl stop mysqld
mysqld_safe --skip-grant-tables &
mysql
...
- 1
1
vote
1
answer
29
views
The performance_schema database of MySQL has been deleted by mistake. Is there any way to recover it?
Yesterday I dealt with a production data problem for them. I used Navicat during operation. I didn’t know how to press the backspace key. Because it is a Mac system, this backspace key was deleted. I ...
- 11
0
votes
0
answers
46
views
Joining server to new cluster broken pipe error
While joining the cluster it seems as the connection is broken between donor and joiner. At least that's what the broken pipe error represents.
Any way to keep the two server talking to one another ...
- 133
0
votes
0
answers
16
views
mysql ndb-cluster error unknown variable 'ndb_connectstring=172.17.0.27'
I have a working ndb-cluster 8.0.27 community edition in one docker, it works fine. now setting up one in another server docker I get an error [ERROR] [MY-000067] [Server] unknown variable '...
- 47
0
votes
1
answer
282
views
How to fix "File '.\binlog.000327' not found" error?
I am using Mysql 8.0 in Laragon on Windows 10.
Apparently, I did something which made MySQL stop working. Tried to restart several times but it's showing this same error again and again.
2021-12-15T08:...
4
votes
1
answer
457
views
Why is the Innodb Memcached plugin being deprecated in MySQL 8.0.22?
I originally used "the handlersocket" plugin for NoSQL access to InnoDB tables, then it eventually couldn't be built on newer versions so I migrated to the "Memcached" plugin which ...
- 506
1
vote
0
answers
40
views
Why Isn't the MySQL Index Immediately Available?
We are in the process of moving from AWS RDS MySQL 5 to AWS RDS MySQL 8. We have a script that runs a few dozen queries. The last few look like this:
ALTER TABLE my_right_table ADD INDEX (col_a, col_b)...
- 11
0
votes
0
answers
5
views
paritition exchange without validation mysql 8
I have a table in production facing about 1M inserts per day,partitioned on a date column.
This table is queried as well but the inserts are important for application.
Have a small script that runs ...
0
votes
1
answer
26
views
SELECT Query Optimisation
I have a simple SELECT query, for which me and MySQL disagree as to how to use (or not) the indexes on the table.
SELECT *
FROM events
WHERE `deviceId` = ?
AND `type` = ?
ORDER ...
- 1
0
votes
2
answers
31
views
MySQL 8 - Performant query for exact words match
We have a database table with more than 10 million entries, and we are struggling to find the best SQL query to find the entries having all the exact words in a field irrespective of the order. Below ...
- 133
0
votes
1
answer
18
views
exitStateAction not working as expected on MySQL INNODB Cluster
I'm in the process of setting up and testing an Innodb Cluster. So far I have three nodes running 8.0.27 and two routers all working fine.
I now want to test what happens if the network connection ...
- 1,920
1
vote
1
answer
56
views
MySql 8.0 Foreign Keys Missing in InnoDB
I have created 10 tables in MySql 8.0 using the InnoDB engine. I defined relationships using the references statement.
When I view the tables using show create table, the foreign keys are missing. I ...
- 111
0
votes
2
answers
132
views
Year and Month as Primary Key
I have a Payments table, with paymentDate and licenseId attributes. I want to make sure that, there is only one payment for a licenseId every month. How can I do that? I have seen an answer which says ...
- 103
0
votes
0
answers
166
views
How to correctly set wait_timeout for MySql server
I have a dedicated server to run a WordPress website.
I use Nignx + php fpm + MySql 8.0.
Php processes are set to run for a maximum of 60 seconds, and mysql is used just to run this website.
I checked ...
- 1
0
votes
1
answer
73
views
replicate-wild-do-table=channel_name:db_name.% doesn't work in MySQL 8.0 for channel based replication
I am trying to configure a replication topology where there will be one slave which will connect to two masters. Percona Server 8.0.25.
Master1- main master
bkp_server - slave to master1 and ...
0
votes
1
answer
36
views
How to create a root user / admin database with access to child databases
New user here, excuse me if that isn't grammatically correct, but this is what I want to do -
I have a server set-up with multiple sites on one VM instance - I want to create an admin mysql database ...
- 1
0
votes
1
answer
104
views
MySQL slave stuck on Applying batch of row changes (read)
Might be duplicate of:
MySQL slave stuck on Applying batch of row changes (write)
or
MySQL8.0 Replication Got Stuck "Applying batch of row changes (delete)"
I am trying to use MySQL 8.0.26 ...
- 1
2
votes
1
answer
68
views
does the order of index columns matter when we have equality operators?
Given that I have the tables
CREATE TABLE `replies` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`repliable_id` bigint unsigned NOT NULL,
`repliable_type` varchar(255) COLLATE ...
- 21
0
votes
0
answers
72
views
Is it possible perform a fulltext search on MySQL 8.0, with double quotes and wildcard?
Based on past answers, I see that it was previously possible to perform fulltext searches with double quotes and wildcard, like the following:
SELECT COUNT(*) FROM mytable MATCH (mycolumn) AGAINST ('&...
- 327