Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
1
vote
0answers
14 views

MySQL RDS queries time performance is considerably slower than MySQL on EC2

We have a MySQL 8.0 db running on EC2 (t2.xlarge instance, 300GB General Purpose SSD) which was doing pretty well handling all of our load (approximately 500 requests per minute during working hours). ...
0
votes
1answer
17 views

Backed up MySql folder… upgraded to newer version and cant seem to recover data

I copied the entire MySql folder mysql-8.0.13-macos10.14-x86_64 and installed a newer version mysql-8.0.17-macos10.14-x86_64. I should have researched but thought the installer would handle moving ...
0
votes
1answer
10 views

Relay log read fail, relay or master log currupt ,How to repair?

I am running mysql InnoDB cluster 8.0.17(group replication) During the repication the slave disk gets full and no space left at all the thread waits for the disk space to be freed at the same time ...
0
votes
2answers
22 views

Mysql Daemon cannot start after upgrade from 5.1 to Mysql 8

I preinstalled Oracle Enteprise Linux and it automatically installed Mysql Community 5.1. But I wanna update it to 8. After update the daemon won't start and the /var/log/mysqld.log shows that [...
-1
votes
2answers
55 views

Mysql 8 picking wrong index for group by date function [closed]

I have a table as below. CREATE TABLE `alarm` ( `alarmTypeID` tinyint(4) NOT NULL, `vehicleID` mediumint(9) NOT NULL, `alarmDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `...
1
vote
1answer
24 views

What should be innodb_buffer_pool_chunk_size for pool_size=2G, pool_instances=2?

I have 4G VPS with following config innodb_buffer_pool_size=2048M innodb_buffer_pool_instances=2 SELECT @@innodb_buffer_pool_chunk_size/1024/1024 128.00000000 Should I change ...
2
votes
3answers
56 views

How to use left join with a subquery

This or this questions do not answer my doubt. I have the following set of tables in a database called order_mgmt_sys (Please note that database is static and I can't alter the tables) mysql> ...
0
votes
1answer
23 views

Cannot login in as root user after MySQL installation (RHEL)

I am new to MySQL. I installed MySQL (version 8) in RHEL 7.7 using yum . Now , I am trying to configure MySQL. [root@devdbvm278 ~]# cat /etc/my.cnf # commented lines removed for readability datadir=/...
0
votes
0answers
21 views

Optimising InnoDB configuration in MySQL80 Server

I have a WAMP production environment, currently running Apache24, MySQL57, PHP73 on two Windows Server 2016 servers - an application server and a dedicated database server - and I'm toying with the ...
0
votes
1answer
25 views

Selecting consecutive rows with same values and one that differs

So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this: ...
0
votes
0answers
17 views

Character set issue

Error 'Character set '#255' is not a compiled character set and is not specified in the '/user/share/mysql'. Facing error in replication channel from mysql 8.0.16 to mysql 5.7.25.Is there any ...
0
votes
0answers
15 views

Does MySQL support ED22519 certificates for SSL?

I'm trying to set up SSL for a public MySQL server with both server and client-side certificate verification. The server is a MySQL 8.0 instance and the client is Connector/NET (other .NET connectors ...
0
votes
1answer
33 views

MySQL, Group Replication not surviving after a single restart

Can anyone see where I am going wrong, as this is driving me round the bend. I have set up a clean install of MySQL 8.0.17 on my Windows 10 laptop. I have gone through the whole initialize process, ...
1
vote
0answers
27 views

Restoring a mysqldump loses user privileges for routines?

When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql ...
0
votes
0answers
13 views

In Mysql 8 user creation statements replicating on slave even after using replicate-ignore-db=mysql

Description: ` Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql or replicate-wild-...
0
votes
0answers
24 views

Master MySQL5.6 - Slave MySQL 8.0 slow relay log reads

We have 4 Servers, 1 Master and 3 Slaves. 2 slaves are on 5.7 version and working perfectly, whereas 3rd slave that is mysql 8 (test version). 3rd slave imported dump in 1 hour when other took 3-4 ...
0
votes
2answers
29 views

Update with rolling sum grouped by another column

I'm trying to build a query to update a MySQL 8.0 table that looks like this ----------------------- user_id | total | delta ----------------------- 1 | 0 | 30 1 | 0 | -10 ...
0
votes
0answers
19 views

mysqlbackup 8.0.16 does not recognise --defaults-extra-file option

OS-RHEL 7.4 MySQL Server- 8.0.16 commercial (trial) defaults-file=/mysql_data/config/options.cnf defaults-extra-file=/mysql_data/config/extra_options.cnf [root@exampledb01 logical_backup]# ls -l /...
0
votes
1answer
28 views

How to implement complex search functionality?

Below is my table structure: CREATE TABLE `post` ( `postId` bigint(20) NOT NULL AUTO_INCREMENT, `userId` mediumint(8) unsigned NOT NULL, `areaId` smallint(5) unsigned DEFAULT NULL, `postTitle`...
0
votes
0answers
44 views

Do i need to add index for apply filtering for this query?

Below is my query: with cte as (SELECT a.postId from (SELECT p.postId FROM join_area ja INNER JOIN post p USING (areaId) WHERE ja.belongAreaId = 29 AND p....
1
vote
2answers
91 views

IN clause not working on mysql 8.0.17

I was on mysql:8.0.15 and I updated to mysql:8.0.17 On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect On 8.0.17 the same query with same data return 0 ...
0
votes
0answers
19 views

Create User in MySQL

Is it possible to create an user without create user command? I mean just by make an entry(framing insert query) in mysql.user table? Will it cause any crash in system tables?
0
votes
1answer
26 views

MySQLRouter on Windows 10 Unable to connect to the metadata server

I have set up a set of three MySQL 8.0.17 databases using group replication. They are all on my Windows 10 PC, using ports 3306,3307,3308. I am now trying to set up mysqlrouter. I have installed it ...
1
vote
2answers
47 views

Why this query giving wrong output?

I want to find review group by ratings 1,2 etc and for doing so i write below query but this giving wrong info. This is the query: SELECT CASE ur.rating WHEN ur.rating between 0.5 and 1 ...
0
votes
1answer
27 views

MySQL 8 roles - I'm probably missing something obvious

I'm trying to use roles in MySQL 8, and failing. I have read the documentation and as far as I can tell, I'm doing it right. Since this is a 'hello world' level attempt, I assume I'm missing something ...
1
vote
1answer
17 views

How to skip prefix column check in multi column index in MySQL?

Below is my demo table structure: CREATE TABLE `table1` ( `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent', `postLocation` smallint(6) NOT NULL, `postArea` smallint(6) ...
0
votes
1answer
40 views

Why last table don't using index for joining?

By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
0
votes
0answers
21 views

How can i remove filesort for this subqueires?

This is the Query: SELECT A.* FROM messages_thread_user mtu, LATERAL (SELECT m.*, u.userName FROM messages m JOIN ...
0
votes
0answers
25 views

Does MySQL Lazy Union really work?

Following query is an example of Lazy Union: SELECT GREATEST(@found := -1, userId) AS userId FROM user WHERE userId = 1 UNION ALL SELECT userId FROM user WHERE userNumber = 1 AND @found IS NULL UNION ...
0
votes
1answer
25 views

Why this join provide multiple value?

This is my Query: SELECT GROUP_CONCAT(c.categoryName) AS categories FROM post p INNER JOIN join_post_cat jpc USING (postId) INNER JOIN categories c USING (categoryId) WHERE p.postId ...
0
votes
1answer
80 views

MySQL Group Replication fails to understand “::ffff:” IP Address

I'm trying to set up group replication using 3 x MySQL 8.0.16 databases, but when I start up the second Node it fails. The error log reports: [GCS] Connection attempt from IP address ::ffff:10.12.1.2 ...
3
votes
1answer
60 views

Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

I am trying the following insert query on mysql 8. Insert Into fence Set fenceName='aa', radius=2, fenceGeometry=ST_GeomFromText('POINT(...
1
vote
2answers
53 views

Changing Data Directory on MySQL 8

Need: To store / accesses dbTable data from a separate drive not the default I was using MySQL 5.7 so far & as specified in the solution - Link1 I added the my.ini file with the info as stated &...
0
votes
1answer
23 views

How to change MySQL 8.0 data folder. Can I use OneDrive folders?

I'm trying to create a DataBase for a very little program that just I and 2 more friends use, so the database will be little. What I want to do is to share this database with these 2 friends, so I ...
0
votes
1answer
27 views

How do i get a list of records from two tables that have different values based on a matching PK? [closed]

Lets say i have the following two tables: Table1: |IP |Name | Gateway | Version | +------+------+---------+---------+ |1 |A | 1.0.0 | v1 | +------+------+---------+---------+ |2 ...
1
vote
1answer
24 views

MySQL change user login from one plugin type to another

I'm in the process of testing MySQL 8.0. I have set up a test database, and the first thing I want to do is import my user account from MySQL 5.7. So to do this I run: /*ON MySQL 5.7*/ SHOW CREATE ...
0
votes
0answers
15 views

Mysql 8.0.16 crash due to informatiin_schema.Keywords table

How to resolve issue of mysql 8.0.16 crash?mysql 8.0.16 crash many times and shown server logs due to invalid argument.and in error log query is select WORD from information_schema.KEYWORDS. how to ...
2
votes
0answers
49 views

check if MySQL is using the Henry Spencer implementation for REGEX

Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries: '[[:<:]]' and '[[:>:]]' From 8.0.4 MySQL is using the ICU implementation ...
0
votes
0answers
14 views

Missing directory after MySQL 8.0 for Ubuntu installation

After installing MySQL Community Server 8.0.16-2 on Ubuntu 19.04 I have the directory /var/run/mysqld missing and the server refusing to start. What's wrong?
1
vote
2answers
79 views

Same column present on two or more foreign key constraints

A simple schema leading to my question is the following: CREATE TABLE A ( AID INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE A_COPY ( AID INT, CID INT, PRIMARY KEY (AID, CID), ...
0
votes
1answer
22 views

Installed mysql 8 on Mac then mysql 5.7 now now they wont work

I have a brand new Macbook pro with Mojave. I installed mysql 8 using the dmg file but realized that Sequel Pro is not playing nice with it and after researching I decided to install mysql 5.7. This ...
0
votes
1answer
43 views

Spatial Query using MySQL 8.0

I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like PID, SurveyID, GeoPoint ...
0
votes
2answers
44 views

What's the maximum number of partition per table I can/should have in MySql 8?

I have around 1T (1,000,000,000) records of products belong to 500,000 accounts. Each product record have the account_id and since I always query within a single account context (there are no cross-...
0
votes
1answer
42 views

MySQL Internals: SQL Execution Flow [closed]

This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components. eg the sequence an UPDATE query ...
0
votes
0answers
59 views

mysql 8 innodb clustering - adding instance fails with error “Unknown database 'mysql_innodb_cluster_metadata'”

I'm setting up my first mysql innodb cluster, following the instructions here: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-production-deployment.html I've configured the instances ...
0
votes
0answers
29 views

Mysql 8.0 table_rows problem

I'm doing an application and I have the following problem: I'm doing a LOAD DATA INFILE into a table from a txt file, that has 2 million rows. I want to see the table_rows of my table with: SELECT ...
-1
votes
1answer
24 views

No MySQL Server 8.0.16 included in the installer [closed]

I downloaded the MySQL community Edition 8.0.16 in my windows 7 32-bit. However there was
0
votes
3answers
74 views

How to generate unique and sequential Id values

We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it: CREATE TABLE `Task` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
44 views

How can I figure out the root cause of “Out of range value for column” error in MySQL

I have a console app written with C# on the top of .Net Core 2.2 framework with EntityFrameworkCore as ORM. My application runs various tasks that run every 15 minutes. Each task calls an external API ...
0
votes
1answer
19 views

How to write a query to get concatenated names of riders with the same weight?

I want to retrieve a table with all Riders of the same Weight from a table named Rider, which is defined in this way: CREATE TABLE Rider( name VARCHAR(30), weight REAL, PRIMARY KEY(name) )...