971 questions
-1
votes
1
answer
68
views
How do I use two databases with docker compose?
I have set up an php laravel sql platform with docker compose with the following docker compose.yml. I only have one database(with production data) but I also want to test out somethings with dummy ...
- 15
0
votes
0
answers
34
views
Mysql 5.7 error "Duplicate column name 'M'" [duplicate]
I have big SQL query with JOIN of two tables:
SELECT
`t1`.`M`,
`t1`.`Y`,
COALESCE(`t2`.`Занято дней`, 0),
`t1`.`Дней в месяце`,
COALESCE(`t2`.`Доход`, 0),
COALESCE(`t2`.`...
- 589
0
votes
1
answer
75
views
group by email_id then get oldest date
I am trying to get the oldest date while using a GROUP BY.
Using MySQL 5.7, I have a table called submissions. I can write a query the returns data like this:
SELECT `email_id`,
`message_id`,
...
- 3,099
-2
votes
1
answer
56
views
Update consecutive dates for a certain amount of records
I have a MySQL 5.7 table.
There are 9000 records that I need to set consecutive dates to.
The first 300 records will have 2025-01-02.
The next 300 records will have 2025-01-03.
And so on until all ...
- 3,099
3
votes
3
answers
168
views
Get all tax rates within a given date range
I have a table with tax rates. Each tax rate has two date fields: 'valid_from' and 'valid_to'. 'valid_to' can be NULL, then the tax rate is valid until a later tax rate is inserted. Now I want to ...
- 605
1
vote
1
answer
54
views
Update throwing warning instead of error and continues to run
In my previous question (str_to_date is showing NULL when date is available), I was trying to update a VARCHAR datetime to a date format.
This is the update query that I am using:
UPDATE `table` SET `...
- 3,099
-2
votes
1
answer
67
views
str_to_date is showing NULL when date is available
I was using an UPDATE query to fix my dates. It's the same query with a CASE statement that I've used before. It checks if the VARCHAR date is blank, if so, then set to NULL. Otherwise, change the ...
- 3,099
1
vote
1
answer
42
views
SELECT INTO Returning NULL in MySQL Trigger
I'm having an issue where a SELECT INTO query in a MySQL trigger is returning NULL for values that should be fetched correctly.
Here is my trigger code:
BEGIN
DECLARE program_price DECIMAL(10, 2);
...
-3
votes
1
answer
92
views
UPDATE column with Y or N if data exists in another table
I have a MySQL 5.7 table with 8 million distinct email records.
I have another table with about 2 million records with emails that are not distinct.
I want to see the emails from the larger table that ...
- 3,099
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
1
answer
48
views
Create a dataset based on an ID and create columns for each corresponding date
I am trying to fathom what exactly is being asked by my management.
I have a MySQL 5.7 table that looks like this:
| email_id | source | message_id | submit_dt |
---------------------------------------...
- 3,099
-1
votes
2
answers
90
views
Convert strange Datetime format
In one of my MySQL 5.7 tables, I have column that has a strange datetime format. It looks like this:
2024-08-09 20:57:31.4477
I am trying to use this query to convert it to a date format m/d/Y. ...
- 3,099
0
votes
1
answer
341
views
Incorrect datetime value: ' ' for function str_to_date
In MySQL 5.7, I have a varchar column that has a string datetime that looks like this:
`2024-09-03 02:00:07`
I can run this query:
select date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s'), '%m/%...
- 3,099
1
vote
3
answers
620
views
Trouble Upgrading MySQL 5.7 to 8: The auxiliary tables of FTS indexes on the table are created in system table-space due to bug
My team and I are attempting to upgrade our multi-tenancy AWS RDS/MySQL instance from MySQL 5.7 to 8. After trying the upgrade, we received a report showing a large number of errors for the customers, ...
- 958
-4
votes
1
answer
72
views
Get back the NULL date records between another set of dates
In MySQL 5.7, I have a table that looks like this:
| email | a_last_sent | a_last_open | b_last_sent | b_last_open | ...more columns
-------------------------------------------------------------------...
- 3,099
0
votes
2
answers
70
views
Exclude records that have 3 or more dates within a series of date columns
I am using phpMyAdmin that uses MySQL version 5.7.
I have a database table called condensed which contains millions of unique emails with multiple "send dates", among other columns. Some of ...
- 3,099
-2
votes
1
answer
119
views
Turn cell values into columns in MySQL
I have a table that looks like this:
| EMAIL | LIST | STATUS | ISP_GROUP | SENT_DATE | CREATED_DATE |
--------------------------------------------------------------------------------...
- 3,099
1
vote
4
answers
97
views
SQL join that excludes rows that have a shared value with another row
Please refer to this db fiddle (SQL also pasted below):
https://www.db-fiddle.com/f/bJ5FfufvP9GHPYtBhkywdP/5
I would like to show rows from the "mpr_metrics" table that have NO associated ...
- 631
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
1
vote
1
answer
74
views
MySQL 8 Syntax error or access violation: 1064 SQL_CALC_FOUND_ROWS
I have the following MySQL query:
select SQL_CACHE
SQL_CALC_FOUND_ROWS
stat_users_events_daily.user_id,
stat_users_events_daily.tracking_category_id as tracking_category_id,
IF(ISNULL(...
- 377
2
votes
1
answer
121
views
MySQL ask to add column to GROUP BY which I don't want to use in GROUP BY
I have a query that should create a view that will be used to be exported as csv file. Here's the query:
CREATE OR REPLACE VIEW coupons_export AS
SELECT
coupons.code AS coupon,
...
0
votes
1
answer
54
views
MySQL thinks a Limit 1 subquery returns more than one row
I am using a limit 1 subquery inside a while loop to run a procedure against all records in a table. I'm targeting MySQL 5.7, which I do not have the ability to change.
When calling MyMultiRecordProc, ...
- 1,105
0
votes
1
answer
52
views
I need the mysql query to remove all the characters and number after first appearing of number reGEXP_REPLACE is not working in myqsql
I have the trade name which contain the number and special characters so i want to remove all the content after first appearing of numeric value
trade name
expected o/p
HUMULIN R, HUMULIN R PEN
...
1
vote
1
answer
63
views
trying to speed up a query - index isn't being used?
Here's my SQL query:
EXPLAIN
SELECT co.id, ca.duration, ca.type,
ca.from, ca.to, ca.queue_name, ca.created_at
FROM conferences co
JOIN calls ca ON ca.sid = co.name
JOIN conference_participants p ON ...
- 17k
0
votes
1
answer
60
views
How to output `\N` in MySQL 5.7 when redirecting from a SQL script?
I have the following example.sql file:
SELECT '\N', '\\N', SUBSTRING('\\N', 1, 2), SUBSTRING('\\N', 2, 2), CONCAT('\\', 'N');
Executing it with mysql <example.sql or cat example.sql | mysql ...
- 20k
0
votes
0
answers
75
views
mysql 5.7, match against query causing irregular case related problems
Environment
MySQL version 5.7.43-log
DB variables
collation_connection utf8mb4_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci
table column ...
- 484
1
vote
1
answer
68
views
mysql query to summarize data
I am trying to summarize some data from two data sources. The tables are following
Table t1
auto_id
unique_column
value
1
d1
10
2
d2
5
3
d3
15
Table t2
auto_id
unique_column
value
null
d3
5
null
d4
6
...
- 381
0
votes
1
answer
20
views
Sorting by ID (integer) in phpMyAdmin does not always work, why?
Explain this: When I sort a particular table by ID (unsigned int10) in phpMyAdmin, it correctly lists a couple most recent rows, but then skips to much older rows.
(phpMyAdmin 5.2.0 under MySQL 5.7.39)...
- 51
0
votes
1
answer
351
views
Speed up SQL query with huge amount of data
I have this SQL query
SELECT *
FROM `storages_remains`
WHERE `FREE` > 0
AND `CATALOG_ID` IN (...)
CATALOG_ID is not unique, because table may contains multistorages data.
CATALOG_ID + ...
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
0
votes
1
answer
135
views
How can I use SQL to find events with overlapping durations? [duplicate]
I have the following table
Field
Type
Null
Key
EventId
int(11)
NO
PRI
Duration
int(11)
NO
EventParentId
int(11)
NO
MUL
EventDateTime
datetime
NO
PRI
Created
datetime
NO
est_datetime
datetime
YES
MUL
...
- 1
0
votes
1
answer
252
views
Is there any performant way to run a JSON_CONTAINS query in mysql 5.7 on multiple values
I have an array of objects inside a mysql json column, and I'd like to query all rows where one of these objects contains a member within a list of values.
continuing from this question
SELECT *
FROM ...
- 5,082
2
votes
2
answers
144
views
Create Mysql Storec Procedure via Python using Mysql Connector
I Am builing a small application. where I Need to create MySQL Stored procedure via python using mysql Connector.
I have tried following code. But getting an following error that
"Commands out ...
- 1,075
0
votes
1
answer
57
views
MySQL query results were confusing. When selected with year separately, it gives count greater than 1, but overall it is not
mysql> select count(distinct message_id) from table1 where message_notification=0 and YEAR(created_at) = '2024';
| count(distinct message_id) |
| 33 |
mysql> select ...
- 39
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
1
vote
1
answer
46
views
How indicies updates are executed in InnoDB mySQL 5.7?
Engine: MySQL 5.7 ( InnoDB )
Metadata about table:
I have relatively big table with hundreds of thousands of rows and like 20 columns.
Table lists child entities for parent entities.
It's being ...
- 4,339
0
votes
0
answers
21
views
SUM multiple columns building column names as string in MySQL 5.7
Unfortunately, I am only able to utilize MySQL 5.7.
I have a table that has a series of columns named for each month. My goal is to sum the total for each column dynamically that is relevant based on ...
0
votes
0
answers
1k
views
Installing MySQL version 5.7 on ubuntu 20.04 machine
So I have tried to downgrade installed mysql version-8 to version-5.7 on ubuntu 20.04 machine. But couldnt do it. I have the below issues.
Some packages could not be installed. This may mean that you ...
0
votes
1
answer
56
views
5 latest topics with only 1 reply
There are 2 tables, one for topics, the second for answers.
How to use 1 SQL query to display 5 extreme topics, with only 1 extreme answer.
In this case, new answers should raise the topic higher in ...
- 45
0
votes
0
answers
31
views
MY SQL listing all the parent and childrens nodes with a JOIN in MY SQL 5.7
I am having a categories table like below
[1]: https://i.sstatic.net/xbPCG.png
and another table called clients_categories
[2]: https://i.sstatic.net/ertkx.png
clients_categories having only parent ...
- 3
0
votes
1
answer
118
views
AES_ENCRYPT and AES_DECRYPT won’t work on MySQL 5.7
When I run the following code I simply get NULL for both outputs. I have also tried using unhex() around the first decryption argument, but still the same result.
SELECT @encrypt = AES_ENCRYPT('hello',...
anon
0
votes
0
answers
114
views
Missing illegal Collation issues after upgrading mysql5.7 to mysql8.0.3*
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '=', query was: SELECT mobile_users.* FROM mobile_users ...
- 19
0
votes
1
answer
549
views
How to query MySQL database for each day in a date range?
I have these three tables:
hotel
hotel_id
hotel_name
room
room_id
hotel_id
room_price
room_id
date
price
I know how to query the database to get the price for a given room_id and given date for the ...
- 3
1
vote
1
answer
69
views
Retrieve all answers in an array from a MySQL JSON column
I am using MySQL 5.7 and currently have a JSON column with data like this:
{"quiz_scores":[{"quiz_date":1697706210448,"symptom_improvement_score":false,"...
- 3,650
-2
votes
1
answer
66
views
order by then group by
how can i first order by and then group by
select saf.*
from sm_artists_followers saf,
sm_artists_meta sam
where sam.fk_sm_artist_id = 13
and saf.artist_ID = sam.artist_ID
and DATE(saf....
- 556
0
votes
1
answer
470
views
MySQL - How to set a default value as TIMESTAMP on varchar column
I need to add current timestamp as a default value in a varchar column in MySQL 5.7
Here is table query I am trying
CREATE TABLE foo (
creation_time VARCHAR(100) DEFAULT CURRENT_TIMESTAMP, col1 ...
- 110
0
votes
0
answers
32
views
Issues with timestamp query [duplicate]
I have issues with timestamp query. Please look at below query:
create table captions (
text varchar(150),
created_at timestamp default current_timestamp,
updated_at timestamp on update ...
- 1
0
votes
0
answers
542
views
Query works differently in Mysql 8 rather that in MySQL 5.7
Working on migration of a legacy Java Spring product from MySQL 5.7 to MySQL 8 and encountered an interesting observation
There is such a query
SELECT
r.roomID,
rt.roomType,
m.memberID,
m....
- 1,147
-1
votes
1
answer
66
views
Syntax Error when adding a LEFT JOIN to a SELECT-FROM-WHERE [duplicate]
inscription
diver
This is OK:
SELECT id_membre, id_sortie, id_diver, licence, mail, actif
FROM inscription
WHERE id_sortie = 4071
When I add the LEFT JOIN it crashes:
SELECT id_membre, id_sortie, ...
- 59
0
votes
0
answers
57
views
How to show user improvement over time - mysql query
I have a table of users moods, they all have a feeling and timestamp. I want to write a query to show a users mood improvments or otherwise over time.
I figured i would start by converting the feeling ...
- 3,650