949 questions
0
votes
1
answer
147
views
CASE-WHEN impacted by where clause
I have table saa_prices (id, symbol, price, date). And I create view to query quarterly prices.
CREATE OR REPLACE VIEW saa_quarterly_prices_final1 AS
WITH last_dates AS (
SELECT
symbol,
...
- 2,343
0
votes
1
answer
76
views
Delete db rows that contain reference to self
I have some data:
id
parent_id
topic_id
1
1
2
1
1
3
2
4
3
2
parent_id has FK to id.
I'm trying to delete data by topic_id - as U see, one row is referencing to another row which both are to be ...
- 500
0
votes
1
answer
55
views
MySQL 8.0 Stored Procedure Showing as Slow Query Due to Total Execution Time
I have a stored procedure that runs daily to archive data from a large table. It moves data in batches of 10,000 rows from table_name to table_archive_table, and deletes the moved rows from table_name....
0
votes
0
answers
12
views
Disable MySQL foreign key field compability/inconsistency checks temporarly [duplicate]
I am doing global convert for database fields - ints to bigint unsigneds, etc. But hitting a FK-ed field returns error:
SQLSTATE[HY000]: General error: 3780 Referencing column 'local_id' and ...
- 500
0
votes
1
answer
51
views
Is it a good idea to run Optimize table query on a table with billions of records in mysql 8 (innodb engine)?
As part of a maintenance activity we have started deleting unwanted data from our database tables. This is being done to free up space and improve the I/O operation efficiency of our database tables, ...
1
vote
4
answers
84
views
Optimizing MySQL Query Performance for Large Datasets with CHAR Column and Keyword Search
I have a query that is extensively used for keyword searches. Due to the large volume of data in the table, the query takes a significant amount of time to execute. The query also includes ORDER BY ...
- 33
0
votes
1
answer
172
views
Can I see timestamp in dbeaver in human readable format?
In MySQL 8 app table sessions has date defined as last_activity int NOT NULL.
I see timestamp in dbeaver 24 as an integer value and I wonder if there is a way to format this date in human-readable ...
- 3,331
0
votes
1
answer
66
views
MySQL 8: insert binary string failed
CREATE TABLE `tmp` (
`wid` bigint unsigned NOT NULL,
`simhash` binary(255) NOT NULL,
PRIMARY KEY (`wid`),
UNIQUE KEY `u_simhash` (`simhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=...
- 635
2
votes
2
answers
200
views
How can I perform a case-insensitive search of array members in a JSON column?
I have a situation where we are storing tags in a JSON array, and the client started using this as a key-value store. I now need to add a way to search this data case-insensitively.
As an example, if ...
- 1,279
0
votes
1
answer
122
views
How to use DROP DEFAULT in MySQL 8?
I'm using MySQL: Server version: 8.0.36 Homebrew
I'm trying to remove the default value of a column (currently set to 0), and it's erroring:
ALTER TABLE comments ALTER COLUMN commentable_id integer ...
- 33k
2
votes
2
answers
105
views
Why is MySQL more picky about parentheses in union subqueries than in normal queries
(SELECT 1 `a`)
UNION
(SELECT 2 `a`)
ORDER BY `a` DESC
LIMIT 1
is a perfectly valid query in MySQL and as far as I can tell the same as
SELECT 1 `a`
UNION
SELECT 2 `a`
ORDER BY `a` DESC
LIMIT 1
...
- 444
0
votes
2
answers
70
views
How to Efficiently Find Consecutive Rows with Matching Criteria in SQL?
I have a dataset that tracks user activity, and I need to identify consecutive rows where a specific condition is met. The table structure and sample data are as follows:
Table: UserActivity
UserID
...
- 72
1
vote
1
answer
94
views
Issue with ENUM Column Becoming Nullable After Upgrading from utf8mb3 to utf8mb4
I recently upgraded the character set of my MySQL database from utf8mb3 to utf8mb4. One of my table columns is defined as an ENUM('saved', 'updated', 'deleted') NOT NULL DEFAULT 'saved'.
However, ...
- 13
-2
votes
1
answer
103
views
I need to know what to replace this code with after updating to MYSQL 8 [duplicate]
This code is part of what is used to import data from a text file to a WordPress database:
mysqli_query($cons, '
LOAD DATA LOCAL INFILE "'.$file.'"
REPLACE INTO TABLE ...
0
votes
2
answers
1k
views
In MySQL 8.4, can binlog files be deleted?
I recently upgraded to MySQL 8.4, and I just noticed my C drive is almost full. After looking into it, I realized that MySQL has been creating a bunch of files called "binlog.XXXXXX" where ...
- 883
0
votes
1
answer
344
views
Cannot grant user privilege to create REFERENCES in MySQL 8
I tried to create the quartz table with a user that has privilege as below.
Privileges: [Select, Insert, Update, Delete, Create, Drop, File, Index, Alter, Show databases, Create temporary tables, Lock ...
0
votes
1
answer
35
views
DBI 1.2.3 dbQuoteLiteral() POSIXct with MySQL 8
Since DBI Version 1.2.3, the format used to encode datetime objects as strings has changed:
DBI 1.2.1:
> DBI::dbQuoteLiteral(DBI::ANSI(), as.POSIXct("2023-04-05 12:34:56"))
<SQL> '...
- 123
0
votes
0
answers
120
views
How to ignore Row was cut by GROUP_CONCAT error
I have a function that uses GROUP_CONCAT. On my local database if the function tries to concatenate more than the value of group_concat_max_len, it just gives me a string of that size, without ...
0
votes
1
answer
800
views
How to properly use Nginx as reverse proxy with ssl for MySQL?
Nginx as reverse proxy without ssl works fine Nginx reverse proxy for mysql without ssl, however when I enable ssl, no more connections can be established. Ssl certificate is issued to lets say my....
- 1,196
0
votes
0
answers
36
views
With MySql is there any way to create a global endless sequence integer, other than the trick of using a "replace" table? [duplicate]
In most systems you need a global, endlessly increasing sequence number. With MySql the only way I know to do it is, make a new small table for the purpose:
mysql> CREATE TABLE `ubersequence` (
...
- 9,780
0
votes
0
answers
57
views
Mysql syntax error using php exec shell commands regarding version of mysql
hello am always getting mysql syntax error , check the version etc ..
$databaseName = sprintf("%s_%s", substr($this->user, 0, 8), $installationData['worldId']);
// Create database ...
2
votes
2
answers
116
views
MySQL 8 Spatial column with GEOMETRYCOLLECTION
We are upgrading MySQL from 5.7 to 8.0. Table cuatom_shapes has column shape with SRID=0, in which few rows have data with GEOMETRYCOLLECTION and few other rows have data with POLYGON.
Though column ...
- 6,270
0
votes
1
answer
60
views
Handling Date comparisons in MYSQL 8 and Spring boot without Milliseconds
We are upgrading our project from Mysql 5 to Mysql 8. there is weird issue coming in db queries in date handling.
On MySQL 5, while saving data to db, the date used to be saved with 'yyyy-MM-dd HH:mm:...
- 850
0
votes
2
answers
319
views
.Net Core 8 Entity Framework (MySQL) DROP PROCEDURE IF EXISTS `MYSQL_BEFORE_DROP_PRIMARY_KEY` Error
I'm created a migration to add a few tables and update one table (add one column). The database is MySQL 8.0.31 and the EF provider is Oracle's MySql.EntityFrameworkCore
The migration was completed ...
- 343
0
votes
1
answer
207
views
indexing is not working properly after upgrade mysql 8.0.28
The issue is coming after up gradation of mysql 8.0.28. I have a table called user_products. When I run the EXPLAIN SELECT query on it, the possible keys identified are user_id, productId, status, ...
- 49
1
vote
1
answer
61
views
I need to update a row in a table after the use of a view in MySQLWorkbench
It's been a while since i used MySQLWorkbench to make queries and i'm kinda lost here.
I want to update a row in a table named last_export that refers the last time a specific app had access to the ...
- 11
0
votes
1
answer
247
views
There is something like OPENJSON?
In this moment I'm using SQLSERVER to store json data into a Table. I do it using OPENJSON. Here is an example how I do it:
INSERT INTO myTable (ColA, ColB, ColC)
SELECT jDataA, jDataB, jDataC
FROM ...
- 827
2
votes
1
answer
2k
views
MySQL 8, Add unique column without locking
Note: I did look up other answers, but they all seem to be for MySQL 5.x. MySQL 8 has made huge strides in ease of altering tables so I am looking for mySQL 8 specific answers.
I'm using MySQL 8.0.36
...
- 29.9k
0
votes
0
answers
45
views
MySQL 8 fails to record binary session data in MEDIUMTEXT column
I have a table like this:
CREATE TABLE `sessions` (
`id` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`access` int unsigned DEFAULT NULL,
`data` mediumtext,
...
- 4,386
0
votes
1
answer
34
views
MySQL How to select value if ANY is present, or otherwise fallback to value if ALL match
Say I have these two tables
table REQUESTS:
ID_REQUEST
USER
1
Adam
2
Ben
3
Charlie
table REVIEWS:
ID_REVIEW
ID_REQUEST
REVIEW
1
1
APPROVED
2
1
APPROVED
3
1
DENIED
4
2
APPROVED
5
2
APPROVED
I would ...
- 3
0
votes
0
answers
772
views
MySQL Query stuck in executing wait/io/table/sql/handler in AWS RDS
I have a query
SELECT `property_category_name` as `property_category`
FROM `listings`
WHERE (
community_name LIKE '%Sun%'
)
AND `listings`.`property_for` = 'sale'
LIMIT
Now when i am executing this ...
- 822
0
votes
0
answers
114
views
Update mysql 5.7 for 8.0 in phalcon 4.1 and php 7.4
i have a problem in update mysql version of my application, 8.0 version causes a 300% more loewst process in database compare of 5.7 version.
Any sugestions or cases for improve performance in this ...
0
votes
1
answer
608
views
How to perform window function with where clause?
From the columns 1-4 in the table below I'd like to create a query that can return columns 5 & 6:
my_date
cat_1_id
cat_2_id
my_value
cat_2_id_1_value
cat_2_id_2_value
01/01/2024
1
1
1
1
Null
01/01/...
- 1,131
0
votes
2
answers
126
views
MySQL 8.0.28 covering (and functional) index is not used
I am trying to use covering index (with a functional index) in MySQL 8.0.28 to speed up a group by query on a large InnoDB table.
Covering index created:
ALTER TABLE `sp_files`
ADD INDEX `...
- 2,127
0
votes
1
answer
37
views
TIMESTAMP Field - UTC <> Local TimeZone
Referring to Timestamp Lookups Documentation
https://dev.mysql.com/doc/refman/8.3/en/timestamp-lookups.html
In the documentation there is an example how UTC to Local TimeZone can cause descrepenices
...
- 2,136
0
votes
1
answer
763
views
Difference in result ( MySQL 5.7 vs MySQL 8.0 ) when using group_by and offset limit
I'm wondering why this query shows different results in different versions of MySQL when I change offset. This is my query
SELECT
users.partner_id,
users_summaries.total_count,
users....
- 221
1
vote
0
answers
50
views
How to enable errors in MySQL when a JOIN clause with no ON clause is given?
Writing a JOIN statement and forgetting the corresponding ON clause is a common mistake and some DBMS can show errors or warnings in such cases. In MySQL 8, is it possible to report or prevent queries ...
4
votes
1
answer
323
views
Mysql8 upgrade: one table has developed a problem with null constraints
We carried out an upgrade of a production system recently, from mysql5.7 to mysql8.
This was extensively tested on CI, local environments, staging. All queries ran as expected. With the move to mysql8 ...
- 6,436
1
vote
0
answers
991
views
MySQL 8 TLS/SSL error. Wrong number version
I searched a lot regarding this matter but unfortunately, I could not find anything helpful.
I have an application in JAVA EE deployed in Payara Glassfish server. This application connects with SSL in ...
- 29
0
votes
3
answers
134
views
MySQL: wrong sorting order in a query with ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function
Apparently, sorting is somehow applied incorrectly in mysql 8 (checked on 8.0.33 to 8.0.35) when query has ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function. See test case below (...
- 3,372
11
votes
1
answer
3k
views
MySQL 8 - Duplicate entry '1' for key 'tablespaces.PRIMARY' upon triggering a TRUNCATE command
I am trying to empty the content of a table using the following command:
TRUNCATE TABLE <table_name>;
The table engine is InnoDB.
Upon executing this command, I receive the following error:
SQL ...
- 1,414
0
votes
0
answers
493
views
About query cache in RDS mysql 8.0
Recently we changed version of RDS mysql from 5.7 to 8.0 by using blue/green deployment. first we put our green instance in 5.7 version later we upgraded the instance and switched over successfully....
- 9
2
votes
1
answer
1k
views
Fix UTF-8 Character Display in PHP *After* Upgrade from MySQL 5.7 to MySQL 8
We upgraded our RDS database on AWS from MySQL 5.7 to MySQL 8.
The server Character Set and Server Connection are set to UTF-8 Unicode. That's utf8mb4 and utf8mb4_unicode_ci, respectively.
But the ...
- 1,777
0
votes
0
answers
104
views
Error 1217 vs 1452: Still less verbose despite `GRANT_ALL`
On MySQL 8.0.34, errors 1217 and 1452 represent the same thing but provide less information in the former case by design. A user without the relevant grants should not see the information about a ...
- 81
-1
votes
1
answer
76
views
Mysql 8.0.23 is causing heavy dip in performance for the select queries
I have a simple select query which earlier in mysql 5.7 version used to work in 850 ms is now taking 2.20 seconds to run.
SELECT c.id,
c.cart_type as cartType,
c.name,
c.storefront_id as storefrontId,
...
2
votes
0
answers
1k
views
On AWS RDS, why does MySQL replication break only after upgrading the replica's Engine Version from 5.7.x to 8.0.x?
Edit: I found a solution, but not sure that it is the definitive answer. Information at the end of the post.
On AWS RDS, I have created a read replica from a master database running 5.7.44 (extended ...
- 310
2
votes
1
answer
231
views
How to make an index for when querying json array (array of object) in Mysql 8.0.35?
Environment
Mysql 8.0.35
My Situation
I want to get all rows if cancel.cancels[*].cancel_no is exactly "202401215050123". and also I want to use multi value index to optimize the query.
...
- 523
0
votes
1
answer
570
views
Migrating to MySQL 8 calculating the RANK Error
Trying to calculate the RANK of a ticket from the Table, on MySQL 8 getting "You have an error in your SQL syntax", but is working fine on lower MySQL versions. Cannot figure it out, Thank ...
- 73
0
votes
2
answers
726
views
Laravel 5.2 + MySQL 8.0 On RDS - Connection Errors Out With "SQLSTATE[HY000] [2002]"
I have an application that runs on PHP 5.6 and Laravel 5.2. It's hosted in AWS on an AWS Linux 1 EC2 machine. This web server connects to a RDS MySQL instance. The RDS instance has been running ...
- 37k
0
votes
1
answer
77
views
MySQL - math operations between rows [duplicate]
I have a MySQL 8 database table like this:
+-------+
| Total |
+-------+
| 100 |
+-------+
| 40 |
+-------+
I want to get the difference between the rows -> result = (row - (row + 1))
Before ...
- 123