Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
27
questions
5
votes
1
answer
8k
views
How do I create a point with an SRID in MySQL?
MySQL supports using ST_PointFromText to construct a point with an SRID,
ST_PointFromText(wkt[, srid [, options]])
Constructs a Point value using its WKT representation and SRID.
This ...
- 60.4k
2
votes
1
answer
618
views
combined contents of 2 columns can't contain duplicates
This is a hard question to ask. I find it difficult to put into words.
I have two columns where the content of column A can not appear in column B and vice versa. So, the combined contents of both ...
- 121
25
votes
3
answers
27k
views
Why did MySQL remove the query cache feature after version 8.0?
Why did MySQL remove the query cache feature after version 8.0?
- 397
3
votes
3
answers
1k
views
RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table
On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.
This is a problem for me because I would like to create ...
9
votes
2
answers
4k
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(...
- 197
7
votes
2
answers
10k
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 ...
- 83
5
votes
2
answers
2k
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 ...
- 352
3
votes
4
answers
9k
views
mysql 8.0, updates are sometimes stalling
Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, ...
- 208
3
votes
1
answer
838
views
Mysql get records more then 3 in interval of 1 minute, return group_concat ID
Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3.
CREATE TABLE test
(
`id` bigint NOT NULL AUTO_INCREMENT,
created_date ...
- 239
3
votes
3
answers
5k
views
Performance issues after upgrading to MySQL 8
UPDATE (tl;dr;):
I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details.
Certain queries seem to ...
2
votes
1
answer
1k
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 (...
- 101
2
votes
1
answer
1k
views
MySQL 8 Geospatial Queries, MakeEnvelope Workaround?
I'm trying to work with some data from Google Maps and have a few locations along with their bounding boxs (northeast, southwest) data stored.
Using that data, I have the following query...
SELECT
...
- 117
2
votes
1
answer
14k
views
Migrate MySQL 5.7 to 8.0 via mysqldump
I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p
...
- 21
1
vote
1
answer
565
views
Statement Based Replication still giving duplicate Entry Error
New to the replication. I am configuring multi-master replication. I did the following steps:
made one slave.
configured two masters to it.
Now i am performing following scenario:
disconnect both ...
- 15
1
vote
1
answer
454
views
MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica
We have a Source Database of about 1TB, and a Replica on another server (similar architecture).
We have setup Master Slave replication and delay between Master and Slave is always growing since.
...
- 13
1
vote
1
answer
915
views
What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?
I've been trying to find the answer to this online for a while with no luck.
What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) ...
- 41
1
vote
1
answer
153
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 ...
- 2,046
1
vote
1
answer
2k
views
Delete large number of rows and change index - what steps in which order and things to watch out for
We daily collect data with multiple records per second. The table my_xpa_data has currently close to 100 Million rows and is growing fast.
table size around 8 GB (used to be 4 GB a couple of days ago)...
- 187
1
vote
1
answer
557
views
MySQL test if function is available
Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course :-(
Ref: https://dev.mysql.com/doc/...
- 111
0
votes
1
answer
309
views
mysql: ERROR 1040 (HY000): Too many connections and can't restart because of Error: 28 (No space left on device) [closed]
My app crashes because there are too many connections open and mysql does not make me log in as root showing the same problem.
After restarting mysql, it does not restart because of no space on disk
0
votes
1
answer
132
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
- 185
0
votes
2
answers
41
views
Can't drop an NDB database because the storage engine is unavailable
I tried to set up a MySQL cluster and failed. I finally got it switched back to standard MySQL, but now I have an ndbinfo database that I cannot drop. It causes my automysqlbackup cron job to report ...
- 103
0
votes
2
answers
801
views
MySQL 8.0 Hanging and Disconnecting At Random Times - Slow Downs - Need Help
Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of ...
- 33
0
votes
0
answers
262
views
Mysql 8.0:Innodb index performance loss until server is dead. Performance perfect after index is re-created?
Important Update
Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.
Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.
The "...
- 402
0
votes
0
answers
80
views
Loading 260GB worth of CSV into MySQL in Linux
I'm bulk loading a few thousand CSVs totaling ~260GB and ~4.5 billion rows to MySQL using mysqlimport calls on each CSV executed within a bash script. The MySQL installation and the data itself are ...
- 181
-1
votes
1
answer
209
views
MySQL 8 insert values into selected columns only
I have a huge insert sql script for insertion and the problem that this insertion was possible in MySQL 5.5 version, but not in Mysql 8:
CREATE TABLE IF NOT EXISTS `accounts` (
`ID` int(11) NOT NULL ...
- 3
-3
votes
1
answer
72
views
From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app?
I am developing an app where I need to store an undetermined number of properties for any given entity, for example:
Entity || # of properties**
Apples || 14
Oranges || 4
Pears |...
- 1