Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
119
questions
1
vote
1answer
14 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
36 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
18 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
19 views
Why does MySQL tend to avoid indexes with few non-NULL values, even when it has a large penalty?
Over the last upgrades (5.6 -> 5.7 -> 8.0), I've observed that MySQL increased its aversion to indexes with few non-NULL values.
This caused some seriously underperforming queries that I needed to ...
0
votes
0answers
22 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 ...
-2
votes
1answer
39 views
How get multiple Columns from that Subquries [closed]
The following query does not run:
SELECT (SELECT *
FROM messages m
WHERE m.threadId = mtu.threadId
ORDER BY m.id DESC LIMIT 1) AS A
FROM messages_thread_user mtu
WHERE mtu....
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
0answers
24 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
27 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
33 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
22 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
26 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
17 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
13 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
41 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
12 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
72 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
18 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
28 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
33 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
34 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
31 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
26 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
22 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
48 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
28 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)
)...
2
votes
1answer
23 views
MySQL 8.0 Replication filters lost between restarts
I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information.
I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). ...
0
votes
1answer
34 views
Cannot connect to MySQL using PHP 7 [duplicate]
The version of MySQL is 8.0.16. This is installed on an EC2 Linux and is a fresh install. I have run the mysql_secure_installation command after installation and was able to change the root password ...
0
votes
1answer
50 views
Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8
I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04
Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it.
I have 2 ...
0
votes
1answer
73 views
mysql Replication stalled
I am having mysql 8.0 installation with replication between 2 servers in master-master configuration. While data is being replicated from server A to server B the replication is stalled from hours. ...
2
votes
1answer
81 views
Why Subquery do not filter the result in multiple Subquery
This is Query (skipped selection for simplicity and also it work perfect):
SELECT
*
FROM
(SELECT
a.striker
FROM
ball_by_ball a
LEFT JOIN batsman_scored b USING (...
0
votes
0answers
11 views
How to drop dynamically events in mysql 8.0?
Having a question about drop events from MySQL
I see here already related a question which I see from far.
But I just want to know, How to drop events dynamically?
I have tried below way using ...
0
votes
0answers
65 views
MySQL Error seems unfixable: Index column size too large
I'm getting: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
There are many questions about this here, I tried many of them. No luck. My context:
MySQL 8.0.13 ...
0
votes
0answers
29 views
MySQL How to get first row from subquery when using aggregate function
This is my Query:
SELECT
COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4),
extra_runs,
0)) AS Runs,
SUM(...
0
votes
0answers
25 views
SSL certificate is not sent to MySql Server while connecting from MySql C connector
I am writing a C program to communicate to MySql server (running on linux) as root, for whom REQUIRE SSL is set. In this program I am using following function call (from libmysqlclient.so) to set SSL ...
0
votes
0answers
127 views
MySQL Error 1032 “Can't find record in table”
I'm getting MySQL 1032 "Can't find record in 'person'" errors for some queries in my database, and I cannot resolve them.
Here's the table:
CREATE TABLE `person` (
`id` int(11) NOT NULL ...
0
votes
1answer
34 views
Why these Two Queries performace so different?
This is Query 1:
SELECT
A.innings_no AS Inn, bowler, OT.Out_Name AS OutType, Runs
FROM
(SELECT
a.innings_no, a.striker, SUM(b.runs_scored) AS Runs
FROM
ball_by_ball a
...
0
votes
1answer
56 views
My disk is full of binlog files
I just realized that my server is growing by 2 GB per day, which is not normal.
I looked at my folder and the folder / var / lib / mysql reached 26 GB
Normally it should be about 4 GB
A month ago I ...
0
votes
2answers
246 views
How to group multiple ROW as ARRAY
This is my current Query:
SELECT innings_no,
Player_Name,
sum(NoBalls) as NB
FROM (
SELECT A.innings_no,
A.bowler,
...
1
vote
1answer
253 views
mysql 8.0, updates are sometimes stalling
Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack ...
0
votes
2answers
59 views
How to set all auto increments to 1 in a Schema
Is it possible to reset all auto increment in a schema in one query? if not even just by per table will do. btw I am using MySQL server 8 thanks in advance :)
0
votes
0answers
39 views
MySQL how to GROUP SUM based on JOIN TABLE
I have Three Table, One Master Table and Other Two Table have relation with it.
Below is the Three Table:
1. ball_by_ball table, this table holds full information by ball by ball:
CREATE TABLE `...
1
vote
1answer
39 views
Why Output is wrong after doing the left join as subquery
I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.?
Query 1:
select Batsman.innings_no,
bowler,
sum(...
1
vote
1answer
86 views
Secondary nodes in mysql 8.0.15 innodb cluster are not reading group_replication_member_expel_timeout variable from my.cnf
I have setup a 3 node mysql 8.0.15 innodb cluster. I have been trying to set group_replication_member_expel_timeout variable to 60 via the /etc/my.cnf file. Although the primary node has read the ...
0
votes
1answer
119 views
Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8
I am looking into upgrading some major components of a webserver (php, apache, mysql).
I've done the update to 5.7 from 5.6. I've updated to php7.3.
What I wonder now is: I see that a mysql ...
2
votes
1answer
83 views
How to query all entities associated with all children (recursive) of a node in SQL?
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.
Ok, I’m having trouble putting this into words… so bear with me.
Say I have a tree of ...
0
votes
0answers
12 views
MySql slaves on Amazon EC2 RHEL 7.6 server not getting connected automatically after mysql service restart on master
I have setup MySQL Master - slave replication on Amazon EC2 instance.
OS : RHEL 7.6
MySQL Version: 8.0.13
Master server IP : 192.168.1.128
Slave server IP : 192.168.1.129
I have two EC2 instances on ...
0
votes
0answers
21 views
Reverse the order in the table derived from lateral join
Example table:
CREATE TABLE `level_2` (
`id` int(11) NOT NULL,
`comment_id` int(11) DEFAULT NULL,
`comment_parent` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`user_name` varchar(...
1
vote
1answer
105 views
How can I recover all the Stored procedures, Functions, Events from previous server version
Until recently I have been running MySQL5.5.8 - foolishly a couple of days ago I decided to upgrade to take advantage of new features to be found in version 8. Before the upgrade I did a backup, using ...
