Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
0
votes
0answers
13 views
MySQL Internals: SQL Execution Flow
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
8 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
19 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 ...
0
votes
0answers
22 views
How to change MySQL 8.0 datadir to a network drive
I changed the datadir=' ' in the my.ini file but this seems to not work, the server never going up, always appers a error with the code "Can't connect to MySQL server on '127.0.0.1' (10061) (2003)" I ...
-1
votes
1answer
12 views
No MySQL Server 8.0.16 included in the installer [on hold]
I downloaded the MySQL community Edition 8.0.16 in my windows 7 32-bit. However there was
0
votes
1answer
29 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,
...
-1
votes
0answers
15 views
Restart MySQL server [on hold]
I want to know the best way to restart MySQL 8.0 server running on production? Because there are data coming every seconds. I tried to restart and it took almost 1 hour, yet still not able to restart ...
0
votes
0answers
14 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
18 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
18 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
17 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 ...
-2
votes
0answers
24 views
How to improve performance of system? [duplicate]
I have a question about MySQL 8. I inserted about 2000 record to two table users and transactions by 1000 thread , it took 9 secounds ( too slow). I want to improve performance when use 1000 thread.
...
0
votes
1answer
29 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
31 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. ...
-1
votes
0answers
39 views
Optimize MySql Self Cross Join and Grouping - Preorder Tree Traversal Algorithm
I'm on MySql 8, I am trying to implement Preorder Tree Traversal
(Referal : https://gist.github.com/tmilos/f2f999b5839e2d42d751)
I have 65,000 rows which i traverse, the following T-SQL takes ...
2
votes
1answer
68 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
10 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
47 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
26 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
20 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
85 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
38 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
97 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
102 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
30 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
38 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
38 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
69 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
64 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
48 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
11 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
54 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 ...
0
votes
1answer
41 views
MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved]
I'm trying to create a simple cricket application, where i will store all cricket records from a database. I collect a sample database with data from online and i successfully did some query but I'm ...
0
votes
1answer
44 views
Limit groups of results based on a column value
I'm not sure I formulated the question properly, but anyway.
I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query ...
1
vote
0answers
29 views
Why is my InnoDB table size so much larger than the data?
I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but ...
0
votes
0answers
23 views
Why sum with previous and current column do't working
I want to update my specific row with that row previous value, after
searching i found some post about this but those solution not working
with me. So what is wrong with my query or how can i ...
0
votes
1answer
21 views
which one is faster: “insert on duplicate update” OR “update with case”
Table:
CREATE TABLE t5 (
col1 VARCHAR(36) NOT NULL PRIMARY KEY,
col2 int(11) DEFAULT NULL,
col3 int(11) DEFAULT NULL,
UNIQUE (col2)
);
Records already inside:
insert into t5(col1, col2, col3)...
0
votes
1answer
33 views
Does MySQL or MariaDB support a LineString constructor that takes points?
It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2)
> SELECT ST_MakeLine(Point(0,0), Point(0,...
1
vote
1answer
30 views
Logs from Syslog-ng to MySQL
Syslog-NG ver 3.18
MySQL ver 8.0.15
I configured my Syslog-ng as follows:
source s_network {
udp();
tcp();
};
destination d_file { file("/var/log/app_network.log"); };
destination ...
0
votes
0answers
47 views
Restoration in MySQL 8 is slower than MySQL 5.6
Recently I have installed mysql 8.0.12 version and started observing that its taking too long to restore database. So I verified the same with using a backup file which I restored to MySQL 8.0.12 as ...
0
votes
1answer
41 views
Query with HAVING clause is taking too long
I have query like this:
SELECT msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
category, msg.`uid`,name, user_name, profile_pic, STATUS,
isNotificationOn, ...
0
votes
1answer
25 views
Can MySQL show the respective timestamps in each INSERT output message?
For example, after an INSERT, I see:
Query OK, 296 rows affected (0.02 sec)
Records: 296 Duplicates: 0 Warnings: 0
I would like to see timestamps with these lines, if possible, e.g.:
2019-...
2
votes
1answer
98 views
Longitude and Latitude order with ST_X, and ST_Y on SRID 4326
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.
Nothing in the docs other than the primitive Point() suggests the ...
0
votes
1answer
152 views
Regex replace with empty string results in NULL characters in between
Replacing a character with regexp_replace results in NUL characters in between.
SELECT REGEXP_REPLACE(aColumn,'c', '')
INTO OUTFILE 'Replaced.csv';
for abcd results in:
ab d (infact: abNULd)
This ...
1
vote
2answers
29 views
How to fill GROUP_CONCAT() with 0's when JOIN data missing
I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a ...
0
votes
3answers
236 views
mysql 8 - rds - 1022 Can't write; duplicate key in table
Documenting this error and solution here in case it could help anyone.
We have a MYSQL 8 database on Amazon RDS.
The below group by used to work just fine on Mysql 5.7 but it started giving
1022 Can'...
5
votes
2answers
647 views
MySQL crashing at about 70k open files
Server running community 8.0.14, we hit the number of open files a few days ago, so I increased the limit but after a while the server crashed with this in the error log:
InnoDB: We intentionally ...
2
votes
2answers
3k views
Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
In mysqld.log I have thousands of lines like:
Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
So, according to virtually every post on the ...
