New answers tagged

0

I am not sure what was causing the issue for the original poster, but in my case it was a case sensitivity issue. A Master server in my replication topology was using PascalCase for its table names when specifying them in queries, where the actual table name files on disk were lowercase. For example, see my error message: Slave SQL for channel 'master1': ...


0

This is little incorrect steps to upgrade mysql version. Although, to overcome this you can try below steps if it fixes this issue. Try mysql_upgrade, mysql_upgrade -uuser -ppassword Restart mysql instance, systemctl restart mysql


0

You can take physical backup of mysql data directory. Step-1 Take backup of mysql data directory. cd /datadir/ cp -r /datadir/ /backup_path/ Step-2 Once you re-install it, just verify data are whether data are purged or not ? Step-3 If data are not purged from datadir, Simple you need to start mysql service & ready go. Step-4 Otherwise you are ...


0

I finally figured it out. Trick was to check if the users_roles uid column is null. UPDATE users u LEFT JOIN users_roles ur ON u.uid = ur.uid SET status = 0 WHERE u.uid > 1 AND u.status <> 0 AND ur.uid is null AND u.uid <> 99; – Brian N Test UPDATE users u SET u.status = 0 WHERE u.uid > 1 AND u.uid <> 99 AND u.status AND ...


0

WHERE (DATEDIFF(due_date, CURDATE) % 5) = 0 Will filter down to today and any multiple of 5 days before or after today. Use that as the basic building block for the rest of your query.


0

'publish' is a string + is an _arithmetic operator So 'publish' + _pad_term_counts() is probably not anything useful. How long does each SELECT take? Please provide SHOW CREATE TABLE and EXPLAIN SELECT and how big the tables are. Descending index -- Not a problem; MySQL simply scans through the data in reverse order. However, no index is useful for ...


0

Find the configuration file. Lower this setting: innodb_buffer_pool_size = 300M It is probably bigger than that, hence using up a bunch of RAM.


1

REQURE SSL only requires a TLS connection. Use REQUIRE X509 to enforce a TLS client certificate, or better, use REQUIRE SUBJECT or REQUIRE ISSUER to ensure that is the identical certificate that matches the one issued.


0

Just to let you know that I've found the issue: related tables on the new server were not broken so everything worked "fine", but the indexes needed to by repaired after the copy from old server (probably because of the big MySQL version upgrade). I just ran mysqlcheck -A --auto-repair and the perf are at least equivalent now! The EXPLAIN SELECT doesn't show ...


0

without the subquery ... several million inserts per minute ... with the subquery ... ~100 inserts per minute Of course. Server executes the subquery per each separate imported record! Specify datatypes for table_1.col2 and table2.id. If table2.id can be stored into table_1.col2 then perform 2-step importing: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE ...


0

You can completely reinstall XAMPP or just install it in another directory and then copy the folder xampp\mysql\data to the old installation. It helped me.


0

This is probably due to the fact that memory tables by default use a hash index instead of a btree index. Read more about it here. Or maybe you don't use an index at all in your memory table? A pretty similar performance problem is described in this blog post: Performance gotcha of MySQL memory tables If you don't have an index on the id column, add one (...


0

Unless you made some kind of change, there is no need to take action when restarting the Slave -- No CHANGE MASTER, no RESET. At most, you might need START SLAVE. Your setup seems to be hosed. So rebuild the slave from scratch. Then do a single CHANGE MASTER with lots of params to initialize it.


0

Check the WP plugins. Some of them create thousands of "options" and fail to clean up after themselves.


0

Dump based backups (logical backups) and recommended for small-medium databases, however, you could speed up logical backup by using enhanced logical backup tools like mysqlpump or mydumper. Raw backups are recommended for large databases and there are two major tools to explore: mysqlbackup (Enterprise backup) and Percona xtrabackup (free - only Linux ...


0

Test: UPDATE source_table SET field = CONCAT( SUBSTRING_INDEX(field, @start, 1), @replacement, SUBSTRING_INDEX(field, @end, -1) ) WHERE LOCATE(@middle, SUBSTRING_INDEX(SUBSTRING_INDEX(field, @start, -1), @end, 1))


1

I think the Optimizer works something like this: It can find the first row: MIN(name) WHERE foo=123 It can find the last row: MAX(name) WHERE foo=123 Those are done via a drill-down in the BTree, and assumed to be reasonably cheap. If there are a lot of rows for foo=123, then this is likely to skip over some blocks. Note: fetching blocks potentially ...


0

Looks like there has been a bug on the issue: ----- 2019-07-22 8.0.17 General Availability -- -- ----- mysqldump failed to wrap SET NAMES utf8mb4 and SET character_set_client = utf8mb4 statements within version-specific comments, which could cause compatibility problems. (Bug #29007506, Bug #93450) ----- 2018-11-20 MariaDB 10.3.11 -- Release Note -- ----- ...


0

I use to follow this path: create all table, only structure (fields), without any Primary Keys nor Foreign Key, without any Index, the order doesn't matter 1.1 inject predefined (aka static) datas create Primary Keys & Indexes create Constraints if any finally, create inter-table relations aka Foreign keys Gains expected: no time spent to manage ...


0

Create the table with simple structure i.e tables with no foreign keys. Then create the tables that are going to refer those new tables created.


0

Please follow the below steps that might help you to achieve your desired goal. create parent tables(for foreign key reference) or any table and define primary keys. create child tables(for foreign key) and define the foreign key and refer it from the column of parent table. create indexes on the table.


1

SELECT 1 Start, t1.time `Start Time`, 0 Stop, MIN(t2.time) `Stop Time` FROM src_table t1 JOIN src_table t2 ON t2.time > t1.time AND t2.ign = 0 GROUP BY t1.time WHERE t1.ign = 1


-1

SQL: WHERE date_field >= '2018-04-01 00:00:00' AND date_field < '2018-04-01 00:00:00' To make it generic, we need to conditionally change Year based on current month WHERE date_field >= ( SELECT CASE WHEN MONTH(CURRENT_DATE)>= 4 THEN concat( YEAR(CURRENT_DATE), '-04-01 00:00:00' ) ELSE concat( ...


0

There are times when a subquery is better: SELECT users.user_id, m.author, m.message_id, users.first_name, m.content, ( SELECT COUNT(*) FROM liked_messages WHERE message_id = m.message_id ) AS times_liked FROM messages AS m JOIN users ON m.author = users.user_id ORDER BY times_liked ...


0

From MySQL 8 , the transaction write-set extraction algorithm is introduced which is XXHASH64. The new default will make it easy for users to enable binary log write-set parallelization on master to speed up group replication.[1] If you are not using MySQL Group Replication, then you can change the settings from XXHASH64 to OFF. And you can enable binary ...


1

Perhaps a better policy: When registering a user, store the fee charged into the user's history. Derive the fee from some business logic that may or may not be stored in a database table. Note how this policy freezes the fee as soon as it is billed. This is safer than having it in some other table that might accidentally be changed for unrelated reasons.


0

Which values are you concerned about? Some are GLOBAL only. my.ini and a restart is needed to change them from outside. Or a user with SUPER can change them from a connection. Some are SESSION, but initialized to the corresponding GLOBAL when you connected. You can override these with SET SESSION .... Once you have done that, no one else can change them ...


0

There is no advantage in having the buffer_pool_size much more than the total amount of data and indexes you have. (Cf, SHOW TABLE STATUS) The buffer_pool_size should not be so large that it leads to swapping. Swapping is terrible for performance. Note: If you are running other apps on the same box, you need to take their memory usage into account. The ...


0

2GB memory isn't that much, particularly for a server. (My 2+ year old mobile phone has 2x more RAM than your server) 2 CPU cores & 2GB is the bare minimum needed to be in a supported configuration for MySQL. Database systems are memory intensive, since they are designed to handle large amounts of data. RDBMSs want to hold data in memory to avoid ...


0

Sorry this startetd as comment, but it git to big This only means that the database server should run on a hardware where it doesn't share Memory, CPU and Hard drive with other services like webserver for example, or run in a virtual hardware with other virtual environments, which share memory and CPUs. The biggest Problems you will face are huge amount of ...


0

I ended up using dbForge with a trail period and it copied the database without issue. It still took many hours. The upgrade form 5.5 to 8 was fine but my database is tables only and nothing odd with the data structure. Thank you all for you advise and help.


0

I think your problem is that you have exported your dump file with ANSI character set, to take a dump file using UTF8, issue your mysqldump command with option --default-character-set=utf8 and check the results. (AFAIK the same options for mysqldump work for Aurora as well)


0

It may not be possible to eliminate all deadlocks; so write code to check for deadlocks and replay the transaction that gets such an error. Many deadlocks can be avoided by speeding up the queries in question. Let's look at what is slowing down the IODKU you have presented. Having multiple unique indexes aggravates the situation. This is because it must ...


-1

SELECT r.rm_number FROM hotel h, reservation rr, room_booking b, room r WHERE rr.res_id = b.res_id AND b.rm_id = r.rm_id AND r.htl_id = h.htl_id AND h.htl_name = 'Holiday Inn Express' AND rr.res_checkin IS NOT NULL AND rr.res_checkout IS NULL ;


2

Settings in my.cnf (etc) are seen only when mysqld starts up. Settings changed by SET GLOBAL... are not seen until someone logs in. But, such settings go away when mysqld is restarted. (This item probably explains what you encountered.) Settings changed for the "session" last only for that one connection, and only until disconnecting. (MySQL 8.0 has ...


0

You can try such a Query UPDATE contents c JOIN modules m ON c.module_id = m.id SET c.`varchar` = CASE WHEN m.type = 'VARCHAR' THEN 'foobar' ELSE NULL END, c.`text` = CASE WHEN m.type = 'TEXT' THEN 'foobar' ELSE NULL END WHERE c.id = 1;


0

This is my query select * from arc_fattura af left join arc_fattura_item afi on afi.id_fattura = af.id where year(af.data_fattura) in (2020,2019,2018,2017,2016,2015,2014) and af.mittente_id=2508 and af.id_tipo_documento in (1,2,3,11,14,15,19) and af.data_fattura<now() group by year(af.data_fattura) order by year(af.data_fattura) af.id is primary ...


-1

Add an index on the column photo_id on both the tables- photos and photo_people.


0

Well I was using a simple bash script to automate the backup process. This script would run in a cronjob everyday at 3 am(you could set the time to your own liking). In this way, I could easily organize all the db backups in 1 server(location). I am pretty sure log backups(I am assuming you are Talking about mysql logs) would also be a one line command in ...


0

To avoid the problem in the future, turn on sync_binlog. What is happening is that the replication stream had not yet gotten to the Slave when the Master shutdown. The offset into the binlog had been bumped up, but the data was not on the Slave. After coming up, move to the next binlog and set the position to 0 (or 4, either one works).


0

The optimizer might build a hash table -- key == values of col, value = count. Or it might extract all the col values. But in either case, it will have to do some kind of sort at some point. When everything fits in RAM, the cost of the sort is minimal. As a rule of thumb, when the GROUP BY and the ORDER BY are different, then there will be at least one ...


0

There's no one correct way of doing it. What I would do, is to introduce another table on top of what you already have. A table that I would call ReservationHeader, this is the table that would hold a header information and then beneath it I would add another table called RoomReservation which will hold multiple bookings related to ReservationHeader. FInally,...


1

(re: MySQL/MariaDB) It depends. For small columns, say under 1KB, don't bother with the hash. For large columns, say over 7KB, it may not matter -- InnoDB puts large columns "off-record" in a block separate from the main part of the record. For medium-sized columns, say 1KB-7KB, the column is likely to be kept "on record", thereby making any action on the ...


0

Neither, for MySQL: SELECT ts, DATE_FORMAT(ts, '%W') AS W, DATE_FORMAT(ts, '%a') AS a FROM ... +---------------------+--------+------+ | ts | W | a | +---------------------+--------+------+ | 2020-01-26 10:53:05 | Sunday | Sun | +---------------------+--------+------+ That is, just use an expression.


1

Basically 'column1' 'column2' 'column3' is different than 'column1,column2,column3' So, you must compare the whole string: BEGIN IF (col_names = 'column1,column2,column3') THEN . . . If you want to compare each variable separately, then you must pass them separate in your procedure call: Call: call hussian('prstate','table_1','column1','column2','...


0

Schematic: CREATE TABLE requirement_versions ( id SERIAL PRIMARY KEY, requirement_id INT NOT NULL, version_id INT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (requirement_id, version_id) ); CREATE TABLE options ( id SERIAL PRIMARY KEY, option_name VARCHAR NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE ...


0

I solved it by using the last join example and was able to string together 8 different queries using this structure: select date, sc, fc, fc10s, fc30s, ... from (select date(hit_date) as date, ...) t join (select date(hit_date) as date, ...) t2 on t.date = t2.date join (...) t3 on t.date = t3.date ... It took about 20m to run, which is OK for a report and ...


0

This is old but sad to see the obvious not mentioned. Setting an AUTO_INCREMENT high on every table is a lot more work as it has to be done for every table, and only works for so long until the db grows big enough. None of these even explain what auto_increment_offset is really for! The sensible way is to use auto_increment_increment Set it to 2 or 3 or ...


0

I usually keep all traces of all old values of all the table in my database into a single table in an XML format, with some more details : datetime in UTC format U or D for UPDATE or DELETE name of the table and of course an ID of BIGINT datatype (PK). But I am on SQL Server and use the FOR XML clause of the SELECT for the pseudo tables of the trigger to ...


1

FLUSH LOGS gracefully does most of what you want. It stops writing to a log, renames the file and starts a new one. After that, you can deal with copying/moving/archiving/etc the files. FLUSH LOGS can be run at any time without damaging anything or losing any log entries. Caveat: There are many different log files; FLUSH may not work the same way on ...


Top 50 recent answers are included