Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
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'...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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 ***...
user avatar
  • 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 ...
user avatar
  • 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] | +--------------+-...
user avatar
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 ...
user avatar
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....
user avatar
  • 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 ...
user avatar
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....
user avatar
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 ...
user avatar
  • 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": "...
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
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 ...
user avatar
  • 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://...
user avatar
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, `...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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. ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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/...
user avatar
  • 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 --...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 '...
user avatar
  • 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:...
user avatar
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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ('&...
user avatar
  • 327

1
2 3 4 5
9