658,898 questions
Best practices
0
votes
7
replies
88
views
MySQL SELECT … FOR UPDATE causing table lock during high traffic while generating sequential transaction IDs
I have a PHP + MySQL project where I need to generate sequential transaction IDs.
Each transaction can have multiple items, so all rows for that transaction must share the same txn_in value.
Tables
...
-2
votes
0
answers
67
views
Как подключить python скрипт к базе данных mysql на phpmyadmin? [closed]
Пароль я использую настоящий, тут я его просто скрыл. Объясните мне пожалуйста, что я сделал не так? Вот сам скрипт:
import pymysql
try:
# Подключение к базе данных
connection = pymysql....
- 1
-1
votes
0
answers
33
views
Dapper Mapping not working as expected. Using myql, asp.net core web api [closed]
I am fetching ProductTransfer table together with related products. Although the total records = 1200, am only getting three records returned by my api.
Here is my models
public class ...
- 37
2
votes
3
answers
143
views
What is the meaning of that Database is the highest levels in MySQL structure? [closed]
In the book Learning PHP, MySQL, JavaScript, CSS, and HTML5 – A Step-by-Step Guide to Creating Dynamic Websites (6th Edition), it says: “The highest level of MySQL structure is a database.”
What does ...
- 41
Best practices
1
vote
3
replies
73
views
Deleting large data without stopping the active mysql server
I'm using a table of approximately 1TB in a MySQL database. This table also has a monthly partition. We store the last two months of data in this table and regularly truncate the data from the ...
- 21
1
vote
1
answer
145
views
Make MySQL evaluate subquery first
SELECT tt.trans_type_name AS transaction_type,
trans.transaction_time,
a.trans_action_name AS transaction_action_name,
trans.transaction_notes
FROM(SELECT Cast(...
- 175
Advice
0
votes
1
replies
61
views
How to Automate Extraction and Standardization of Multilingual Name/Username Data Without Modifying the Original Database?
We’re working with a user table populated from multiple SSO sources, and the data has inconsistencies that hinder generating standardized names. I need a persistent, automated way to extract and ...
- 17
Advice
0
votes
9
replies
141
views
How to make it so a parent NEEDS its children to exist in MySQL?
I am designing a DB where basically parent can't exist without two children. E.g: Entity set Marriage, CAN'T exist without entity set Man and entity set Woman, but neither Man or Woman need Marriage ...
- 21
-2
votes
0
answers
27
views
Access denied for user to a database? [migrated]
A user I'm supporting is getting the error. The user has grants to various tables in mydb, but when he tries to access them, he gets the error:
Access denied for user 'myuser'@'myhost' to database '...
- 532
0
votes
1
answer
58
views
AWS Glue Script Scanning Entire Table Despite Date Filter
I have written a small Glue script that fetches some data between two dates, but I found that it scans the entire table instead of just the data within the specified time range. I also tried creating ...
Advice
1
vote
5
replies
160
views
Can I make MyDAC update a blob field faster?
So I'm uploading around 50MB into a longblob in a local mysql server.
Using LoadFromStream, setting the param value is near-instantaneous but the execution of the UPDATE statement takes around 2 ...
- 113
Best practices
1
vote
6
replies
123
views
Best way to get a number of list elements whose sum of values equal a constant value
I have a list which consists of numeric values.
In Python, it is written as:
[959, 3480, 9346, 8367, 2847, 8330, 6946, 9586, 9722, 2775]
Or in the case of MySQL:
create table testtb(n int);
insert ...
- 2,217
Best practices
0
votes
8
replies
140
views
Should I generally use a LIMIT clause when testing my SQL queries to a database?
I am learning SQL from a class online.
I wanted to ask if it's generally best to use LIMIT in my queries to make them run faster, or at least until I have them tested and pulling the right data.
If ...
Advice
0
votes
8
replies
139
views
MySQL dump from Ionos server not compatible with dockered MySQL db
I am trying to set up a development and testing environment for the software of my new employer.
The data is quite complex and due to some shortcuts/bad architecture decisions of the company that ...
- 31
1
vote
0
answers
42
views
SQLAlchemy Base.metadata.create_all(bind=engine) doesn't create table [duplicate]
So I have been trying to create 2 tables using MySQL and SQLAlchemy, but it keeps firing error: sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'chatbot_db.sessions' ...
- 13
0
votes
0
answers
62
views
dbt-core having issues with establishing connection with mysql
I have a dbt-core setup to connect to mysql database which is installed on a localhost on my personal machine. The connectivity from dbt-core to mysql was working fine until I recently upgraded dbt-...
5
votes
3
answers
95
views
Create a trigger to update a column on a table after deleting an entry in another table
I'm making a very simple webpage for an animal shelter (school project) and I want to create a trigger where, if I delete a row in he 'adoptions' HTML table, it sets the situation of the adopted ...
-1
votes
0
answers
40
views
MYSQL generate row for month data without entry [duplicate]
I'm writing a program to count attendance, and I have a table (att_events) containing the holidays in this format:
Date | Description | Poster ID
2025-01-01 New Year Admin
by using the ...
Best practices
0
votes
6
replies
141
views
MySQL query to calculate based on 2 sets of data
I have a MySQL database that I have the following type of data records. It's a little more in depth than this but its good enough for the sample question.
Ticket Number
Event (Start or Stop)
Location
...
- 331
1
vote
1
answer
72
views
An alias is giving different result in MySQL [duplicate]
Can someone explain why I'm getting two different results from the below two queries?
Q1:
SELECT match_no, COUNT(*) as "number of cards"
FROM player_booked
GROUP BY match_no
ORDER BY "...
- 21
1
vote
0
answers
49
views
Doctrine ORM metadata for special id to represent no relationship on column that cannot be null in mysql [duplicate]
Hi I'm trying to improve a doctrine entity for it to be able to use doctrine relationships while still reflect the current schema in the database. As per the doctrine recommended practices it is ...
- 47
-8
votes
1
answer
120
views
ERROR 1064 (42000): You have an error in your SQL syntax; Getting error for adding foreign key after creating table [closed]
I created 2 tables students and course, in course I want to add student_id as foreign key but I am adding the key after creating the table and it is showing error. Need help in solving this thing.
...
Best practices
3
votes
1
replies
115
views
Architecture design for multilingual descriptions for several classes
I am creating an E-Commerce app. It has Category and Product classes. Both have multilingual descriptions, so, tables with title, description, meta-*, etc. and unique key (id, language_id).
To add ...
- 178
2
votes
0
answers
145
views
Performance issue with ST_CONTAINS
I have a performance issue with a query in MySQL. I need to compare location data and am trying to use ST_CONTAINS in a join. However, I am having a performance issue as it is quite slow, taking ...
- 46
2
votes
1
answer
71
views
Join Query with an Aggregate Function (number of bowl games won for colleges that have won at least once)
I'm working through my Data Management Applications labs and I'm stumped on one particular lab. I'm definitely missing something incredibly obvious, and I'd rather look dumb on the Internet than look ...
- 23
-2
votes
1
answer
74
views
MySQL order by id but prioritize an empty value of a certain column
Mainly, I want to order the ids in descending direction but also, how can I sort parent product/items (the ones with empty ean) first and their child rows below them?
Here's an example data from the ...
- 1
0
votes
0
answers
65
views
Mysql create a view for Power bi
I would like to create a view in mysql who show the place of my order as text. This place is a json object (example {"sap_hold": 1, "in_licensing": 1}). I would like to add a ...
- 414
4
votes
1
answer
145
views
Spatial index will not be used 'cause it has no SRID attribute
I am very confused now, because I cannot get this to work. I have a tbl_address which already has longitude and latitude. I want to add a spatial index for my search. So I want to add a location POINT ...
- 1,092
-1
votes
1
answer
64
views
Dictionary memory allocated and buffer pool in mysql
What is the dictionary memory and where does it stores, is it uses the memory of buffer pool or a seperate memory, does buffer pool stores metadata pages also or only user table and indexes, in both ...
- 1
3
votes
1
answer
112
views
bash script concat() in prepared statement for mysql
This gives a syntax error:
read -p "entry: " entry
sql="select concat('entry ', id) from mytbl where id = ?";
$mysql_conn "prepare stmnt from '${sql}'; set @id='${entry}';
...
- 109
1
vote
1
answer
67
views
Is it possible to force mysql server authentication using django.db.backends.mysql?
it's my first question on stack overflow because I can't find relevant information in Django documentation. Is it possible to force mysql server authentication with ssl using django.db.backends.mysql? ...
- 11
3
votes
5
answers
171
views
How to get the number of rows from multiple related tables with one query
I have four tables, one primary and three dependents, let's call them A, B, C and D.
All tables have the column ID, with ID being the primary key for A and the foreign key for B-D.
B-D also have a ...
- 4,365
2
votes
2
answers
98
views
Generic Date Add SQL that works in both MySQL and H2
select DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY)
works in MySQL.
SELECT DATEADD(‘DAY’, -30, CURRENT_DATE())
works in H2(2.2.224).
I am looking for a generic SQL solution that works in both the ...
- 516
0
votes
2
answers
93
views
MySQL USE command not working from PowerShell
Here is a snippet from a PowerShell script:
$sql.CommandText = "CREATE DATABASE IF NOT EXISTS ``$dbName``;"
$rowsInserted = $sql.ExecuteNonQuery()
$sql.CommandText = "USE ``$dbName``;&...
- 89
0
votes
0
answers
65
views
Commands out of sync MySQL error - possible missing commit [duplicate]
I'm trying to run:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXIST abc();
SELECT( IF( ( SELECT 1 FROM information_schema.statistics WHERE index_name = 'abc_x' AND table_name = 'abc' ) > 0, SELECT 0, ...
- 6,473
-3
votes
1
answer
280
views
Why are these simple MYSQL update statements failing
I have been coding php for years, but today I have encountered a problem I have never seen before. Some very simple update statements are failing -- or rather instead of updating, they are replacing ...
3
votes
1
answer
66
views
Is there an option to run a large query without causing it to pollute the InnoDB cache?
I want to run a bunch of large background queries where I don't want these lower priority queries to fill up the InnoDB cache with rows that will flush out cached rows needed by higher priority ...
- 532
0
votes
1
answer
109
views
Using `with` to find the instructors with the highest salary
I am learning SQL and was testing the with expression syntax when I ran into this error for my SQL query. I want to get the ids and names of all instructors from a university databse with the maximum ...
- 14.9k
0
votes
1
answer
105
views
Syntax error 1064 with LOAD DATA ... FIELDS OPTIONALLY ENCLOSED BY '"'
My SQL statement in MySQL 5.7.44, Windows 10:
drop table if exists test;
create table test (col1 VARCHAR(64), col2 VARCHAR(80) );
LOAD DATA LOCAL INFILE "t.csv" INTO TABLE test FIELDS ...
- 1,193
0
votes
0
answers
102
views
Return multiple columns with aggregate functions [duplicate]
I am trying to figure out how to return multiple columns that correspond with the desired aggregate functions, max of a sum, in SQL.
Based on data from CDC's Serotypes of concern: Illnesses and ...
- 9
0
votes
3
answers
65
views
MySQL: Problem with extracting first character from text field with Regular Expressions
I'm not able to get the first character of a string with the dot operator – it returns the rest of the string, not the character:
CREATE TABLE test (
name VARCHAR(10)
);
INSERT INTO test (name) ...
- 7,244
0
votes
2
answers
223
views
Why correlated scalar is 10x times slower in MySQL comparing to PG
Let's create a table with 3000 rows
create table tt(id int, txt text);
insert into tt
with recursive r(id) as
(select 1 union all select id + 1 from r where id < 3e3)
select id, concat('name', id)
...
- 663
3
votes
0
answers
136
views
Laravel migrations on production with large dataset take too long and cause deployment timeout
I'm facing an issue while deploying code to my production server using GitHub Actions (CI/CD).
I have 3 environments: dev, staging, and production.
On the dev server, migrations run fine because ...
0
votes
0
answers
32
views
MySql Show the names of users whose ID does not appear in a third table [duplicate]
I have 3 MySql tables:
a_users
`id` int(10)
`display_name` varchar(250)
a_predictions
`user_id` int(10)
`match_id` int(10)
`home_score` tinyint(3)
`away_score` tinyint(3)
a_matches
`id`...
2
votes
1
answer
60
views
Solving Deadlock Issue with MySQL REPLACE INTO
MySQL's REPLACE INTO causes deadlocks during high-concurrency batch data insertion. What locks does REPLACE INTO acquire during execution, and why do deadlocks occur?
Known Clues:
The scenario where ...
- 21
4
votes
2
answers
129
views
Cleanup MySQL tables after duplicate records where created and are referenced by second table [closed]
After a long period of this going wrong we found out that there is a error in our data set.
In the manufacturers table, manufacturers are often added multiple times and the product in the product ...
- 433
0
votes
2
answers
114
views
SQL Query to get the Min & Max Date [closed]
I have 3 tables:
Schedule (scheduleId, scheduleDate, requestBranch, assignBranch, customerName, customerAddress, invoiceNo, refkey)
Branches (branchId, branchName)
ActivityLog (activityLogId, ...
- 18
1
vote
1
answer
80
views
MYSQL Trigger after insert
I am writing a trigger to run whenever a new row is inserted into table_a. The trigger adds a new row into table_b, see insert statement.
CREATE TRIGGER `trigger` AFTER INSERT ON `table_a`
FOR EACH ...
- 846
1
vote
1
answer
101
views
MySQL 8 runs very slow when including a NOT IN in the where clause
I have a website that displays on the main page the 12 latest uploads from the site's users. In that query, users can filter out uploads from certain other users (hence the NOT IN clause). Here is an ...
- 61
0
votes
0
answers
29
views
SQLSTATE[HY000] [1040] Too many connections (Connection: mysql, SQL: select * from `cache` where `key` in(job_hubwale_cache_illuminate:queue:restart)) [duplicate]
occurs this error in live project hosted in contabo sesrver
i have create project in laravel12,php 8.2 & database used MySQL. this project hosted in contabo serve in this why generate this error ...
