Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

New answers tagged

0

From my point of view, @Mr.Brownstone answer is only partially correct. The backtick ` is used in MySQL to delimit literals that represent identifiers (not strings). This allows you to use not usually accepted characters like spaces, reserved words, etc. as identifiers. For example: SELECT * FROM `Strange table name`; Also important is that it also ...


0

MySQL 5.6.4 I am not sure this SHOW STATUS LIKE 'Ssl_cipher'; has been changed in later versions. For me even if I am not using SSL, it shows SSL: Cipher in use is DHE-RSA-AES256-SHA for me. You can use following to get confirmed SSL is using or not. ubuntu@ip-111-22-3-444:~$ mysql -h 111.22.3.444 -u dbuser --ssl-mode=VERIFY_IDENTITY -p ERROR 2026 (...


0

SELECT aois_id, polygon( linestring(WS_A, WS_B, WS_C, WS_D, WS_A) ) as geom FROM aois;


1

Try: SELECT a.aircraftid 'Aircraft ID', COUNT(s.serviceid) FROM aircraft a JOIN service s ON a.aircraftid = s.serviceid GROUP BY a.aircraftid HAVING COUNT(s.serviceid) > ( SELECT COUNT(serviceid) / COUNT(DISTINCT aircraftid) FROM service )


0

ALTER TABLE myDatabase.myTable ADD COLUMN age INT GENERATED ALWAYS AS (FLOOR(((CURRENT_DATE + 0) - (birth_date + 0)) / 10000)) VIRTUAL; More about generated columns read at MySQL 8.0 Reference Manual / ... / ALTER TABLE and Generated Columns.


0

As mentioned on the comment of my question, the answer is "NO", especially the FOR UPDATE it will hold the lock until a COMMIT or ROLLBACK is called.


1

You want AND d.tentative_date_of_return <= CURDATE() + INTERVAL 3 DAY


1

The Slave purges old log files on its own. No problem. The Master does not have a perfect way to get rid of old binlogs. The Master can't remove an old binlog until all the slaves have copied it to their relay logs. But the Master neither has a list of Slaves, nor does it know where each is in the copying process. expire_logs_days works pretty well, but ...


1

Here's how to identify which 'bad' query to fix that will give you the most bang for the buck: The slowlog plus pt-query-digest. More details here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog I rarely need to go down the list more than 3 items to make a dramatic improvement. If you are weak on knowing what indexes to add, don'...


0

Use set-environment command: systemctl set-environment MYSQLD_OPTS="--skip-log-bin=1" Next, reload configuration files and restarted the server using the following commands: systemctl daemon-reload systemctl restart mysqld Next check the value of log_bin global variable: mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME IN (...


0

This is not a complete answer, but I need 50 rep to comment. Check out this similar question - > https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded-on-mysql . The first two answers have some steps you can follow to nail down what query is causing the lock and a possible resolution with setting your innodb_lock_wait_timeout ...


1

You don't need all binary logs from the master. Just the ones that your slave hasn't read yet. On your slave you can do show slave status\G. Look for the lines Master_Log_File and Relay_Master_Log_File. The first is the binary log file, that your master is using, the second is the log file that your slave is currently reading. You can then delete all ...


0

In between BEGIN and END, use the below code: IF DAY(NOW()) = DAY(LAST_DAY(NOW())) THEN IF HOUR(NOW()) = 23 THEN IF MINUTE(NOW()) = 50 THEN CALL IncidentsSummaryRecorder(); END IF; END IF; END IF;


0

The queries are bad Then they need fixing. You could add extra hardware but you will get linear improvements at best, fixing the queries could potentially give several orders of magnitude of improvement by removing huge scans and such. Though I'm only guessing here as we have no information about the actual queries. and there are far too many of them to ...


0

Identify all corrupted tables using myisamchk # myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 18361936 MyISAM-table 'attach_data.MYI' is corrupted Fix it using switch "-r" or "-o" myisamchk: warning: 1 client is using or hasn't closed the table properly MyISAM-table 'groups.MYI' is ...


0

Of course you will see your terminal running the grep command .. that's pretty much to be expected. Please learn some console basics (either mac os or free bsd related as that is the underlaying system that mac os builds on ) If ps -> grep really only shows its own instance then there is nothing else with "mysql" in its process name. Which pretty much ...


0

After further research it seems this is actually a bug. Instead of directly reverse-engineering your database, -create a new model -save all your code in an .sql file -while in the model tab go to file-import-reverse engineer create script -check "place imported objects on diagram" and execute and there you go Still unsure why the first above example ...


1

what is wrong with this code? Wrong point is: WHERE ... Relationship='Son' AND NOT Relationship='Daughter' The conditions are applied to THE SAME RECORD. So when Relationship is equal to 'Son' then it is not equal 'Daughter' in this record automatically. The conditions do not check any another record. So if a record where Relationship='Son' exists then ...


0

By "source file", do you mean the source command in the commandline tool mysql? If so, you need to kill that client, not the individual commands in PROCESSLIST. Go to the OS's task manager. (You may need to be root.) # ps -axl | grep mysql (or other args of your picking) # kill -9 1234567 (using the PID from the ps)


2

You can use exists and not exists select ... from employee e where exists ( select * from dependent where ssn = e.ssn and relationship = 'Son' ) and not exists ( select * from dependent where ssn = e.ssn and relationship = 'Daughter' ) You can also achieve this with a left join select ... from employee e left join dependent d on e....


3

If you are using a older MySQL versions like 5.1 - 5.7 , you need to use MySQL user variables as these MySQL versions does not support window functions and or common table expressions .. I ported the answers of @Akina and @mustaccio and the fiddle of Akina to a MySQL 5.1+ working query. Warning as MySQL 5.1 to MySQL 5.7 does not use window functions and ...


1

How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0: SELECT t1.contact_id, t1.contact_group_id, t1.contact_email, COUNT(t2.contact_id) - 1 row_number FROM contacts t1 JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id AND t1.contact_id >= t2....


2

Depending on the size of the table, this variant of Akina's solution might perform a little better by calculating both running total and full total of volume in one go: WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) running, SUM(volume) OVER (ORDER BY price ASC RANGE BETWEEN UNBOUNDED PRECEDING AND ...


0

That depends. If the test database on the Master was never in use, then the slave won't care. As long as the slave is not a master to another server, then you are OK. If the slave has binary logging enabled, the database will just get created and dropped and a record of it would be in the binary logs of the slave. Besides a test database would not be in ...


2

the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows Test: WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) summ FROM sourcetable ) SELECT AVG(price) FROM cte WHERE summ <= 0.1 * ( SELECT MAX(summ) FROM cte ) fiddle


1

In my situation slave replication coordinates were broken and I fixed this by resetting my slave and connect my slave to master by new coordinates without any data loss. First, check if your slave gtid_slave_pos is differing from other slaves or master gtid_current_pos: # on master SELECT @@GLOBAL.gtid_current_pos; +---------------------------+ ...


0

a group leader, and with a variable amount of members That sounds like a 1:many mapping. Such is implemented by having a column leader_id (with FK) in the members table.


0

Connections left in a connection pool will show up as "Sleep" in PROCESSLIST. The "Time" will continue to grow until grabbed by a new "connect".


0

CONCAT(FORMAT(sum(ns), 0)) --> COALESCE(FORMAT(sum(ns), 0)) txndate = CURDATE() - INTERVAL 7 DAY --> txndate >= CURDATE() - INTERVAL 7 DAY What is SUBDATE(CURDATE(),2) supposed to do?


0

Putting parentheses in a BETWEEN leads to strangenesses: date between (x and y) and filter1 = filter2 is treated as (date between (x and y) and filter1) = filter2 Why? BETWEEN expects 2 'arguments'; you have given it (1) (x and y), which returns true or false, which are numerically 1 or 0, and filter1. The result of that (probably 0) is then compared: ...


0

total_hours is not visible for GROUP_CONCAT. Either use the expression behind total_hours (like @Rick-James does) or nest the query: SELECT ..., GROUP_CONCAT(total_hours SEPARATOR ',') as real_hours FROM ( SELECT u.id ,tc.id as timecard_id, tc.week, tc.year, tc.status, u.first_name, u.last_name, efv.value as staffasap, p.name, ti.paycode_id ...


0

Reference: https://dev.mysql.com/doc/refman/8.0/en/create-table.html No parentheses after in either of the places you used them. [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition reference_definition: REFERENCES tbl_name (key_part,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE ...


1

According to Documentation at MySQL 5.7 Doc, you will only have to add '--skip-ssl' to your mysqld startup command. This will completely disable SSL support on server side.


0

You're correct in that the most common way to join two tables is to use a foreign key referencing a primary key. But it is not a requirement. The SQL will work (i.e. will by syntactically correct) for any two columns you wish to use in your JOIN clause, as long as the columns' types can be coerced into something comparable. The question is, will the ...


0

Get rid of @num_rows and @i; you can loop without counting. There are lots of examples floating around. You must construct the query something like SET @stmt := CONCAT('SELECT "', col_name, '", ' MIN(', col_name, '), ', ' MAX(', col_name, ')', ' FROM ', tbl_name'); The intent is to ...


0

The only thing that worked for me was to edit the my.ini file using Notepad++ ... do NOT use Windows Notepad as any change will stop mysql from starting. I would guess this is because mysql fails to parse the file, so uses internal defaults which are no good as its looking for mysql master db in 'program files'.


0

As expected. The updating of secondary indexes (INDEXes other than PRIMARY KEY) happens later. Such changes are stored in the Change Buffer; they are actually written to disk later in a more efficient way than trying to do them as the rows are being inserted. You will see both CPU and I/O activity. The net effect should be that your LOADs and SELECTs ...


0

The way the query is written, and the way IsDeleted is implemented, make the use of any index on IsDeleted useless. (Akina's comment explains h If you have INDEX(UpdatedDate) then that index may help in the query. (Please provide EXPLAIN SELECT ....) But, if there are a lot of rows that fail that IsDeleted test, then it would have to scan a lot of rows ...


1

You can create timestamp in SYSTEM timezone with: FROM_UNIXTIME(timestamp_in_seconds) -- This is a number, not a datetime To convert back to a timestamp you can use UNIX_TIMESTAMP function. This sample demonstrate how to generate random datetime based on starting point. It give you only correct timestamps: select FROM_UNIXTIME(UNIX_TIMESTAMP('2016-03-27 ...


0

The old way of adding a Slave might be best: Stop mysqld on an existing Slave (Replica) Copy the disk (or at least all of MySQL's stuff) Change the server_id See if something in the Master contains a list of servers in the topology; if so, augment it Start mysqld on both replicas.


1

Schematically. CREATE TABLE users ( id PRIMARY KEY, ... ); CREATE TABLE groups ( leader_id REFERENCES users (id), member_id REFERENCES users (id) ); The keys (including primary) and constraints in groups are dependent by: does the user may be a member of one group only, or of a lot of groups; does the group leader may be a member of ...


0

Try using the funtion max for the fields value and date, also apply `GROUP BY to id_item For example: SELECT a.id_item, a.value , max(b.date) as date FROM tb_purchase_item a LEFT JOIN tb_purchase b ON b.id = a.id_purchase LEFT JOIN tb_sheet_item c ON c.id_item = a.id_item LEFT JOIN tb_sheet d ON d.id = c.id_sheet WHERE d.id = :id_sheet GROUP BY a....


0

If B.field1 has a collation ending with _ci, then the UPPER is not needed; get rid of it. Then you need INDEX(field1); do you have it? And what about the ...? So far, I see no use for including C in the query.


0

Have the primary key in place, but not the secondary keys. When finished loading, add all the secondary keys in a single ALTER statement. Caveats: I have not done that particular task, so I dont speak from experience. There have been changes over the years in how ALTER works. Doing multiple alters at once used to be optimal. But with online/instant/etc ...


1

Direct solution: (0[1-9])|([1-9][0-9]) It is simple enough for do not look for shortening or another optimization, I think.


-1

Brackets have the highest priority. -- original select * from countries where date between (x and y) and filter = filter and choosen like '%col%'; -- step 1 select * from countries where date between (x and y) and filter = filter and (choosen like '%col%'); -- step 2 select * from countries where (date between (x and y) and filter) = filter and (choosen ...


0

I answer by myself. In short, sql syntax above is correct, so how to query mysql tables with regular expressions? Exactly like I did. The reason why it didn't work, is regexp syntax being too 'complex' for mysql. I found out that mysql doesn't support the full syntax possibilities of standard regular expressions. I must use simpler symbols/constructs. So ...


0

I think you posted on here the other day asking a similar question. 1) Don't use mysql start, use sudo service mysql start or similar. 2) You don't want to change the permissions on /var/log/mysql - Administer Ubuntu properly and change to the mysql user in order to view them, using sudo su - mysql Do not attempt to start MySQL as your user, or hack the ...


0

SELECT t1.* FROM purchase t1 JOIN ( SELECT t2.`month`, MAX(t2.numPurchases) numPurchases FROM purchase t2 GROUP BY t2.`month` ) t3 ON t1.`month` = t3.`month` AND t1.numPurchases = t3.numPurchases


2

Use user-defined variable for to convert the date value from dd\mm\YYYY format to the date/datetime datatype value during import: LOAD DATA INFILE .... (field1, field2, ..., @date, ...) SET datefield = STR_TO_DATE(@date, '%d\/%m\/%Y');


Top 50 recent answers are included