449 questions
0
votes
0
answers
50
views
PDO MySQL 5.6 Query Execution Timeout [duplicate]
Going through threads from 2009-2022 it doesn't look like there has been a real answer on getting a query to timeout in PHP from MySQL (5.6). The PDO timeout attribute is for the connecting, not the ...
- 5,212
0
votes
1
answer
37
views
Removing and replacing whitespace to hyphens in an html description in mysql in img tags
There are several image tags in a html description row there is a problem as the images uploaded had some spaces in between
for ex:
<div class="colUnitImg">
<img src=...
- 11
0
votes
0
answers
427
views
how do I modify docker-compose.yml to download mysql 5.6 for an apple m2 silicon running Ventura OSX 13?
I have a docker-compose.yml file that looks like:
version: '3'
services:
mysql:
image: dtr.s-9.us/mysql:5.6
restart: always
environment:
MYSQL_DATABASE: 'mydb'
...
- 1,959
1
vote
0
answers
70
views
Optimise query to return various Nth percentiles in MySQL 5.6
I have a dataset that contains the execution time for various api calls.
What I am trying to do is return a table that provides the execution time for various percentiles across different api calls.
...
- 11
-1
votes
2
answers
43
views
Update missing values in MySQL 5.6 table by selecting values from closest records with same id
Let's say I have a table with logs. The value of status column may be NULL and I would like to UPDATE this value by copying value from the log for same settingid which have non-NULL status and closest ...
6
votes
2
answers
6k
views
Suddenly cannot dump remote database: Unknown column 'generation_expression' in 'field list' [closed]
I've always been able to dump my remote database using mariadb-dump from the CLI. Starting today, I suddenly got the following error
mysqldump: Couldn't execute
'select column_name, extra, ...
- 314
0
votes
0
answers
158
views
datetime/timestamp problem with replication from 5.6 to 5.7?
I'm replicating from mysql 5.6.33 to 5.7.41. I have a table with a datetime field. If I understand correctly, between 5.6 and 5.7 the decreased the space a datetime field uses because it doesn't store ...
1
vote
2
answers
2k
views
list is not in GROUP BY clause and contains nonaggregated column in X2CRM
I have created a custom module in X2CRM. Then, I created a new record in that module and logged time using the Log Time widget. Ultimately, when I retrieve the same record by clicking on that record ...
- 397
1
vote
0
answers
26
views
How to find the table record difference after changing the ROW_FORMAT of mysql table
I want to migrate from mysql 5.6 to mysql 8 but got some warning related with maximum allowed size (8126) for a record on index leaf page.
so need to confirm whether data are safe after changing the ...
- 1,323
3
votes
1
answer
1k
views
Mysql 5.6 to mysql 8 upgradation throwing warning message the row size is xxx which is greater than maximum allowed size (8126)
While upgrading existing mysql 5.6 to mysql 8 got the warning message logs in mysqld.err logs several times for each database
[Warning] [MY-011825] [InnoDB] Cannot add field abc in table dbName....
- 1,323
0
votes
0
answers
152
views
mysql prepared statement unable to execute. Getting error 1064 in prepared statement
Query is retrieving correct result but in Prepared statement getting error "Error Code: 1064. check the manual that corresponds to your MySQL server version for the right syntax to use near '...
- 1
-1
votes
1
answer
55
views
MySQl v5.6 How to exclude unwanted / incorrect rows produced after Joining two tables
I have two tables , I want to fetch correct data rows, but when I make a join in between incorrect extra rows are getting create. I want to exclude them.
Table T1
Employee_ID
Work_START
1111
10 Nov
...
- 1
1
vote
1
answer
43
views
Select last registered row from each group from two tables in MySQL v5.6
Could you please help me with a query I'm having issues with? I tried to seach for similar questions here with no success.
I have 2 tables which I need to join and get the latest created_date from ...
- 33
0
votes
1
answer
505
views
Will adding index solve lock wait timeout exceeded error
We have a table with 2 million + records. The records are inserted through two sources, from UI and from AWS lambda/step function. The issue is that when records are inserted/updated via lambda, DB ...
0
votes
0
answers
14
views
What should be ideal value of Filtered column in mysql explain clause? [duplicate]
As per MySQL manual
rows shows the estimated number of rows examined and rows × filtered /
100 shows the number of rows that will be joined with previous tables.
in my case i have executed one ...
- 1,323
0
votes
2
answers
250
views
MySQL: giving the query hints about sort order
I'm not clear how to search for this, if there's a duplicate feel free to point me to it.
I'm wondering if there's a way to tell mysql that something will be sorted before filtering so that it can ...
- 7,796
0
votes
1
answer
56
views
SQL: difference between where in main body vs join clause
I'm wondering why does the following queries give me a slightly different dataset:
SELECT t.name, COUNT(e.id)
FROM event_type t
LEFT JOIN event e ON t.id = e.type_id AND e.start BETWEEN ? AND ?
GROUP ...
- 2,089
3
votes
4
answers
211
views
How to combine the initial state of a row with the latest state in MySQL?
I need to get the initial state as well as the latest state from a MySQL database. This is over two tables:
customer
id
name
surname
dob
email
telephone
10
Steve
Bobbly
01-01-1970
[email protected]
...
- 2,351
1
vote
1
answer
41
views
Multi Ordering 4 SQL columns with a single query
Environment: MySQL 5.6
SqlTable name = CategoryTable
Sql Columns
CATEGORY_ID (INT)
CATEGORY_NAME (VARCHAR)
LEVEL (INT)
MOTHER_CATEGORY (INT)
I've tried with
SELECT
CATEGORY_ID, CATEGORY_NAME , ...
- 13
0
votes
1
answer
868
views
Replacement for JSON_ARRAYAGG and JSON_OBJECT from MySQL in v5.6 or PHP
I've inherited an old project that runs on MySql 5.6 and Zend FW 2. I'm trying to run a query that will give the same results as JSON_OBJECT() but mysql 5.6 doesn't support that.
Basically I have 2 ...
- 2,172
-1
votes
1
answer
6k
views
Mysql 5.6 Installation errors in Mac M1
System version - macOS Monterey (12.4)
Chip - Apple M1 Pro
Objective - I want to install [email protected] using brew as per requirement. Any other methods are also welcomed.
Steps followed -
run command for ...
- 1
-2
votes
1
answer
813
views
how can we import big sql file size 5gb into mysql database
i have sql file size 4.93 gb which i am not able to import into my database using mysql command.
--file was exported by using mysqldump command--
i've tried set connect_timeout=10 , max_allowed_packet=...
- 9
1
vote
1
answer
102
views
Optimize MySQL Query using explain
I have following SQL
SELECT COUNT(*)
FROM A
INNER JOIN B ON A.evnumber=B.evnumber
INNER JOIN D ON B.userid=D.userid
This is explain result.
[
{
"id": "1",
"select_type&...
- 33
0
votes
1
answer
45
views
Mysql query to get the distinct count by current year and last year on the same date
I am trying to get the distinct count of (ordno,partno) by the date for current year and last year..Please find below the DDL for the input and the output tables.
Create table input
(ordno varchar(20),...
- 421
2
votes
1
answer
153
views
mySQL Group_Concat and Case when query gives error
I am getting the error "Incorrect parameters in the call to native function 'CONCAT': on the query below:
SELECT
*,
GROUP_CONCAT(
CASE
WHEN `REASONORINSTRUCTIONCODE` = 'R'
...
- 421
1
vote
1
answer
224
views
Mysql Query to find the status of the SKU items manufactured
I have a delay table (as below) where a SKU is identified by the combination of mmodel and srno and the snapdate.. A SKU present on a specific snapdate indicates the SKU was delayed on that date
Delay ...
- 421
1
vote
1
answer
87
views
Mysql Query to calculate the distinct count of pickup numbers by plant
I am trying to get the distinct count of pickup numbers by plant and pickupmonth year
Input
pickdate picknumber Plant
1/3/2022 L009803 Seattle
1/3/2022 L009803 Seattle
1/3/2022 L009803 ...
- 421
2
votes
2
answers
763
views
mysql null value case when is null does not work
I have following query to handle null value and flag those based on two table join
however c.pure_proc_flag is null then 0 else 1 does not provide the result I expect.
query as following
select
b....
- 667
1
vote
2
answers
555
views
Select clients that that don't have an event after a certain date
I have two tables involved in this situation:
tools_events
COLUMN_NAME
DATA_TYPE
event_id
int
event_type
varchar
event_value
varchar
event_client
int
event_timestamp
datetime
tools_clients
COLUMN_NAME
...
- 450
1
vote
0
answers
50
views
Find overlapping date-range records with Mysql 5.6?
I have a list of events with date-range and venue id.
I need to find the max count of distinct active venues at the same time.
create table if not exists events
(
eventstart datetime,
eventend ...
- 9,617
0
votes
1
answer
402
views
MySQL ORDER BY performs better with a SUBQUERY than a PLAIN simple query
For some reason I don't understand this plain regular MySQL query runs SLOWER than expected. From what I understood, MySQL would first slice the queryset using WHERE then sort the subset of results. ...
- 17.2k
0
votes
0
answers
68
views
Hierarchical data handling based on ID php mysql
I have a sample MySQL table with the columns ID, NAME AND ID_PARENT.
ID
NAME
ID_PARENT
1
NODE 1
NULL
2
NODE 2
1
3
NODE 3
2
4
NODE 4
2
5
NODE 5
1
6
NODE 6
5
7
NODE 7
6
8
NODE 8
7
9
NODE 9
6
10
NODE 10
...
- 2,688
0
votes
1
answer
537
views
Issue with Spring Cloud Dataflow and Mysql 5.6
I have the following problem when trying to start a SCDF server connected to Mysql 5.6.
Using the following command to start a SCDF server connected to a mysql database (version 5.6):
java -jar spring-...
0
votes
1
answer
123
views
MySQL returning only part of results from FULL TEXT SEARCH after version 5.6 to 5.7 upgrade
I have a query that consists of two full text searches in boolean mode (combined with OR operator) that worked just fine on MySQL 5.6 and that fails after bumping MySQL version 5.7. Both DBs have the ...
- 3,580
0
votes
1
answer
392
views
change default value to a column through a migration
I have an existing table in which default value of column is already set. This table contains lot of data in it. I don't want to change any of exiting record in table(don't want to change column of ...
- 45
1
vote
1
answer
382
views
Calculating rolling percentage change by category in MySQL 5.6
I have a SQL data table like this, and I wanted to calculate the rolling percentage change (by row and category). So that the result looks like this below
The SQL query I use is really slow and it ...
- 93
0
votes
0
answers
649
views
Error Code: 1193. Unknown system variable "block_encryption_mode"
I want to change AES encryption mode to CBC from DEFAULT ECB, I executed the following query to change mode: My MySQL version is 5.6.10
SET block_encryption_mode = 'aes-128-cbc'
This gives following ...
0
votes
2
answers
125
views
Want to generate a row_number variable in MySQL 5.6 - but not using @variables
I have a table (all code is on fiddle).
id nom bal bal_dt val_dt
1 Bill 75.00 2019-11-01 2020-03-31
1 Bill 100.00 2020-04-01 2020-07-31
1 Bill 500.00 2020-08-01 ...
- 25
1
vote
1
answer
283
views
MySql optimizer doesn't use the index on varchar
I have a table defined as
CREATE TABLE `article` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`field1` varchar(1024) NOT NULL,
`priority` int(11) NOT NULL,
`prodcode` varchar(64) NOT NULL,
`...
- 1,593
0
votes
1
answer
156
views
Implicit cast of string boolean to integer fails
We have a table with the following column
`IsDeleted` bit(1) NOT NULL DEFAULT FALSE
These work as expected in both our non-prod and prod environments
select * from mytable where IsDeleted = 0;
select ...
- 24.2k
2
votes
1
answer
246
views
Mysql query result generate months(1-12) for each category and arrange in same column order by category
I want to generate months no for 1-12 for each category. There are two categories, DEBIT and CREDIT. So, after finish print 1-12 for DEBIT. Then, it should go to next category and print the same with ...
- 2,014
4
votes
1
answer
4k
views
SQL query much slower in MySql 8 than in MySql 5.6
I have a simple query:
select count(*)
from ror
where rorsts not in ( 'RECD', 'CANC', 'CCNS', 'SDNY' )
and ( ( rorbcd = '00009310022487'
and rorfid = 'VDR' )
...
- 959
0
votes
1
answer
39
views
Mysql search string in all fields slowness after version upgraded to 5.6
Current mysql version is 5.5. I upgraded a copy to version to 5.6.
Below query get 15secs to execute from 5.5.
But the version 5.6 took 750+s to run same query.
Query :
SELECT SQL_NO_CACHE *
FROM ...
- 11
0
votes
1
answer
555
views
Is it possible to use a list of items in a SELECT without using a temporary table in MySQL?
I have the following MySQL query:
create temporary table if not exists temp_terms(
term varchar(256) NOT NULL,
soundexterm varchar(4) NOT NULL
);
INSERT INTO temp_terms VALUES ('brocoli', ...
- 5,855
4
votes
2
answers
111
views
How can I update my database table with the result of my query
I am a French designer and I have a SQL problem. My MySQL server is in v5.6 and i ask for help because I have a syntax problem. I think you can bring me a solution.
I have a data base called "...
0
votes
1
answer
944
views
Is Aurora MySQL 1.* reaching end-of-life?
I see the forum discussing the end-of-life process of the Amazon RDS for MySQL 5.6 here
I am using Aurora MySQL 1.19.5, which is compatible with MySQL 5.6. Do I need to upgrade it to Aurora MySQL 2.* ...
- 233
1
vote
1
answer
320
views
using INFORMATION_SCHEMA.COLUMNS in a TRIGGER to log changes to _all_ columns?
TRIGGERs can be used to log changes to individual DB columns as described at https://stackoverflow.com/a/779250/569976 but that technique requires you have an IF statement for each column. It's not a ...
- 17k
0
votes
2
answers
63
views
Complex mysql query counting
I have 1 table name "companies" with several datas like :
Id
Owner
Company
Job
1
John Doe
Company 1
CEO
1
John Doe
Company 2
CEO
1
John Doe
Company 3
CEO
1
Gab Durand
Company 4
CEO
1
Rob ...
0
votes
1
answer
348
views
select list of date between date range with condition
hi i have a goals want to generate list of date between 2 date but i also have some condition like this :
Table_A : Maximum Delivery Time and Week off
Code Max_delivery_time Week_off
01 ...
- 27
4
votes
1
answer
5k
views
Why does --default-character-set=utf8mb4 have no impact on the client connection?
According to https://dev.mysql.com/doc/refman/5.6/en/charset-connection.html, When I connect to a mysql 5.6 server with the mysql 8.0 client using the command:
/usr/bin/mysql -h ${DB_HOST} -u ${...
- 306