New answers tagged

0

Test SELECT t1.InvoiceID, t1.UserID, t2.InvoiceID, t2.UserID FROM ( SELECT InvoiceID, UserID, @num1 := @num1 + 1 rownumber FROM au.invoice, (SELECT @num1 := 0) variable WHERE InvoiceID > 1234 ORDER BY UserId ) t1 JOIN ( SELECT InvoiceID, UserID, @num2 := @num2 + 1 rownumber FROM nz.invoice, (SELECT @num2 := 0) variable ...


0

You need to embrace the filepath in quotes. This works for me (tested on MariaDB 10.4, but should work on older versions as well): DELIMITER // CREATE PROCEDURE test(filepath text, _query text) BEGIN DECLARE stmt TEXT; SET stmt := CONCAT(_query, ' into OUTFILE \'' , filepath, '\''); PREPARE s FROM stmt; EXECUTE s; DEALLOCATE PREPARE s; END // ...


1

This is now possible with tools like Ghostferry: https://github.com/Shopify/ghostferry


1

Your condition in the WHERE clause pm1.meta_key = 'profile_user' basically filter out all the rows which column meta_key is NULL. To resolve, put the condition in the ON clause. SELECT wp_posts.*, , mmud.membership_level_id FROM wp_posts LEFT JOIN wp_postmeta AS pm1 ON wp_posts.ID = pm1.post_id AND pm1.meta_key = 'profile_user' JOIN wp_postmeta AS pm2 ...


0

Issue fixed. I found mysql tables (db & user) columns available in 5.7. So i copy these tables from 8.0 to 5.7. And copy data only for available columns in tables db & user of mysql schema. Now its working fine.


1

Understanding -- I can't explain the details, but I think this is the big clue: "lock_mode X locks gap before rec insert". Avoiding Are there other statements in each transaction? They may have impact. The two INSERTs are hitting the same pair of columns that you are SELECTing FOR UPDATE: (1294, 26396574) You are only providing a conflicting token for ...


0

CHAR(n) -- Depending on the vintage of your copy of MySQL and the ROW_FORMAT, CHAR may allocate a full 3 bytes per character. VARCHAR(n) -- This will occupy one or two bytes for a length, plus only as many bytes are needed. That is each character will occupy 1, 2, or 3 bytes for the CHARACTER SET utf8 (utf8mb3). In general, you should go for utf8mb4, with ...


2

That sounds like the answer: Upgrade the OS to 64-bit to run the 64-bit version of MySQL. Interestingly, there is a limit to the size of an InnoDB table in Windows 32 Note Bulletpoint 10 of MySQL Documentation on "InnoDB Limits" Tablespace files cannot exceed 4GB on Windows 32-bit systems (Bug #80149). Note the excerpt from that bug report (submitted ...


0

Checklist for UTF8MB4 Conversion: Space should be available Foriegn_key_check should be disabled Need to convert Database Need to convert Routines Need to convert Triggers Need to convert Tables Note: Bulky tables should be converted usingLOAD DATA ... INTO table syntax SAMPLE SYNTAX For the database: ALTER DATABASE database123 CHARACTER SET utf8MB4 ...


0

The mentioned issue will not surface for the given code. If you could create a new code with which we shall reproduce the same error, immediate reply is guaranteed. The choices are Do not alter the unique key. You shall redesign your logic. Unique key fields takes little longer to get updated as they need to be updated both in the data file and the index ...


0

Actually here is a solution that worked for me: https://techierg.blogspot.com/2019/06/ubuntu-1804-mysql-this-installation-of.html Quote: Briefly, the solution is Edit the file /var/lib/dpkg/info/mysql-server-5.7.postinst sudo nano /var/lib/dpkg/info/mysql-server-5.7.postinst At line # 370, comment out the echo ALTER statement which would like ...


2

Rule of Thumb: A disk hit on HDD takes about 10ms (100 hits/second). SSD access might be 10 times that fast. "0.1-0.6" on HDD -- perhaps 10 to 60 blocks needed to be fetched. That might be only one for the index, then several for the data. Perhaps there are at least 60 rows in the resultset? You say that the slow version is "remote". This might add a ...


0

Depends on a character. Some need one byte, some - four. See http://www.fileformat.info/info/charset/UTF-8/list.htm . MySQL has two UFT8 character sets: mysql> SHOW CHARACTER SET like 'UTF8%'; +---------+---------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+--------------------+...


-1

From MySql documentation: Requires a maximum of four bytes per multibyte character. Or, if your database character encoding is set as utf8mb3: Requires a maximum of three bytes per multibyte character. So, yes, an utf8 character will occupy the same size in your database as it would have. No more, no less.


1

Aside from @mustaccio's link, in general, mysql will guarantee auto_increments will be unique aand monotonically increasing, but for several reasons it cannot guarantee there is no gaps. One reason is distributelly generated ones like in your case, but it can also happen with 2 concurrent write threads on the same server- so your applications should always ...


1

You can add max_allowed_packet=512M to your mysqldump command. Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf Note: It will not work if it is not under the [mysqldump] section


2

There are two problems PROBLEM #1 : MyISAM MyISAM does not cache data (stored in the .MYD) which means data must be read for disk every time. Indexes may be cached (read once from .MYI), but not data. See my old post in What are the main differences between InnoDB and MyISAM? under the MyISAM subheading PROBLEM #2 : HDD vs SSD An HDD must use spindles ...


1

First, make sure you have set max_allowed_packet in the appropriate section [mysqldump] of the config file my.cnf and that it does not exceed the maximal value of 1G: [mysqldump] max_allowed_packet=1G Or you can also specify it at runtime (without having to change my.cnf): mysqldump --max-allowed-packet=1G ...


0

What is the use case? Normalization? That is a table with 2 columns, a 'name' and an 'id' for it? Then this is the optimal pattern: CREATE TABLE Names ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(44) NOT NULL, PRIMARY KEY(id), UNIQUE(name) ) ENGINE=InnoDB; This effectively gives you two BTrees, each with both columns in ...


0

The easiest way to know is to test, on Percona Server for MySQL 5.7.28 (latest as of this writing) it shows that mysqlpump includes the mysql schema in the backup: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mytest` /*!40100 DEFAULT CHARACTER SET latin1 */; CREATE ...


-4

Rate Per Second = RPS Suggestions to consider for your my.cnf [mysqld] section read_rnd_buffer_size=128K # from 256M to reduce handler_read_rnd_next RPS of 40,203 read_buffer_size=512K # from 1G - never seen anyone try this PER CONNECTION RAM request key_cache_age_threshold=7200 # from 300 seconds to reduce age out of MyISAM data You should find these ...


-2

Note: Yes, You can add cluster index on column (name) But it's once time because in one table have only one cluster Index in SQLServer.


0

can we add cluster index on column (name) too? No. Only one index is clustered. For MySQL: If primary key exists it is clustered. If no primary index but unique indexes exists then first unique index is clustered. If no unique indexes exists then the index by inner record number (hidden, unaccessable) is clustered one.


-1

Step 1. Compare Explain plan of MySQL 5.7 & MySQL 8.0 Step 2. Try setting below parameter & check execution time in MYSQL 8.0 set global internal_tmp_mem_storage_engine=MEMORY ; Because there is bug in internal_tmp_mem_storage_engine=TEMPTABLE which leads to performance regression. Bug 30562964 Step 3. This is new parameter introduced in MySQL 8....


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.


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

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

In the master server, you don't need to issue either RESET MASTER nor PURGE BINARY LOGS to extract a DB copy... when you need to extract a master copy to create a slave, just do it with the current binary log file and position. Since you will need the current binary log file and position to start replication on the slave, just include the --master-data (...


0

We had similar issue, Mysql never stop eating RAM, he needed a month to eat all 16GB. Of course it was beyond the maximum possible memory usage set in .cnf file. We used ubuntu 18.02 with mysql 5.7 version. After few weeks of fight we found that performance_schema was caused. In my.cnf file, we add: performance_schema=0 that was solved our issue. If ...


0

Basically, this means your slave is now out of date with the master, and it's going to keep being out of data until the slave is fully synced. You might have to start a new slave from scratch, and get that synced with the current state of the primary. This has been answered here before: https://forums.mysql.com/read.php?26,367180,367785#msg-367785


1

Here is a query what will generate all the necessary update statements. It temporarily disables foreign key checks so that the update statements succeed. It updates the default character set for each database that needs it. It updates the default character set of each table: If it doesn't have the right default character set. If it contains text columns ...


3

This behaviour is described in explicit_defaults_for_timestamp system variable which is by default disabled for 5.6,5.7 (and effectively disabled on 5.1) and it is enabled in 8.0. Quoting from above link: (5.7) If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMP columns as follows: ...


0

After spending lot of time I am able to resolve my issue and reason is that in Openshitf/kubernetes we have section to verify readiness of mysql like readinessProbe: tcpSocket: port: 3306 initialDelaySeconds: 20 periodSeconds: 2 timeoutSeconds: 1 So it was pinging every two seconds on 3306 port, i changed it to below and its ...


Top 50 recent answers are included