New answers tagged

0

Use common string functions and extract the value you need. SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(amount_detail, '"date":"', -1), '"', 1) AS date_from_JSON FROM sourcetable


0

Native JSON support wasn't introduced until MySQL 5.7 so you're out of luck if you're still running [the six-year-old] version 5.6. I have to question why this is being stored as JSON in the first place. This looks to me like perfectly normal, relational-style Data that could be stored in a conventional table structure: create table table1 ( id int ...


0

WITH RECURSIVE cte AS ( SELECT @starting_deviation num UNION ALL SELECT num + @delta_deviation FROM cte WHERE num < @final_deviation ) SELECT * FROM invoices JOIN cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY For the query from the question @starting_deviation := 5, @delta_deviation := 5 and @final_deviation := 15. These ...


0

The sentence about requiring a checkpoint before the log file rotation/reuse may have been removed from documentation, but the logic hasn't changed in version 5.6 -- you cannot overwrite log records that belong to not-yet-persisted data, so you have to checkpoint still. The fact that it is happening in your environment indicates that flushing of dirty pages ...


0

When InnoDB add an index it adds a record to I_S.INNODB_SYS_INDEXES. INDEX_ID of a new index will be highest. Analyzing existing records you can get an idea in what order indexes where created, but that's it. INNODB_SYS_INDEXES doesn't contain any time fields. On other hand, any DML will be logged to a binlog. If it's enabled and it hasn't been rotated yet ...


2

I created a minimal, complete, and verifiable example for your query. CREATE TABLE oss_collection_history ( org int , Balance int , DateFrom date ); INSERT INTO oss_collection_history VALUES (1, 1, '2019-01-01'); INSERT INTO oss_collection_history VALUES (1, 2, '2019-01-02'); INSERT INTO oss_collection_history VALUES (2, 1, '2019-01-03'); ...


-1

Since your other slaves are running fine, this is possibly caused by user error. Some row was probably changed on this slave manually, which was meant to be changed on the master. The slave then encounters something like duplicate key constraint 10 times and fails. If the transaction causing this error is slow or it effects a lot of rows combined with ROW ...


1

This below worked for me: SELECT count(*) INTO @exist FROM information_schema.columns WHERE table_schema = 'mydatabase' and COLUMN_NAME = 'mycolumn' AND table_name = 'mytable' LIMIT 1; set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable` ADD COLUMN `mycolumn` MEDIUMTEXT NULL', 'select \'Column Exists\' status');...


-1

The issue disappeared when I changed the engine from MyISAM to InnoDB. I understand that InnoDB has row locking.


1

And if you want to avoid a UNION - not certain of performance here, but this might be better (see fiddle here) - you don't have to query the table twice as for @Akina's query! You might want to check with a large dataset. Create and populate table: CREATE table pat_dis ( p1 INT, p2 INT, dis FLOAT ); INSERT INTO pat_dis VALUES ( 1, 2, 1.1), ( 1, 5, ...


0

SELECT patientIDa, patientIDb, distance FROM Distances WHERE patientIDa=3 UNION ALL SELECT patientIDb, patientIDa, distance FROM Distances WHERE patientIDb=3 ORDER BY distance LIMIT 3;


0

RHEL7 uses systemd. Using the OOMScoreAdjust as follows: sudo systemctl edit mysqld.service Note: I'm not sure of the exact service name Add the following text: [Service] OOMScoreAdjust=-1000 restart with: systemctl restart mysqld.service Check by looking at the score: cat /proc/$(pidof mysqld)/oom_score_adj


0

Do you have INDEX(COMPONENT_NAME, UPDATE_DATE) with the columns in that order? (Please provide SHOW CREATE TABLE.) What else was running at the same time? (SHOW PROCESSLIST before the DELETE times out.) Let's see that other query; perhaps it can be optimized to run faster. What is the value of autocommit? Are you using BEGIN...COMMIT?


Top 50 recent answers are included