MySQL Archives

How to fix System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” Exception with MySQL Connector/NET and utf8mb4 character set

MySQL / By Jan Reilink / 18/10/2019

An System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” can be the result of using a too old MySQL Connector/NET version in your ASP.NET web application. A KeyNotFoundException is thrown when an operation attempts to retrieve an element from a collection using a key that does not exist in that collection. An unsupported character set like utf8mb4 can be such a key, if your Connector/NET doesn’t support this character set. Luckily there is an easy workaround for this.

…

How to fix System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” Exception with MySQL Connector/NET and utf8mb4 character setRead More »

Restore single MySQL table from a full mysqldump backup file

MySQL / By Jan Reilink / 21/08/2017

MySQL fix. Image via Flickr by Luis M. Gallardo D

If you need to restore a single table from a full MySQL backup, you may find yourself wondering “how do I do that?”. There are a few steps required, I outline them here for you to restore the contents of just one table back into the database from the mysqldump using Bash.

…

Restore single MySQL table from a full mysqldump backup fileRead More »

MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!

MySQL / By Jan Reilink / 10/06/2016

Are you running into MySQL load problems? Learn how to tune MySQL servers for a heavy InnoDB workload, by configuring innodb_buffer_pool_instances and increasing read/write I/O threads. Dividing the InnoDB buffer pool into multiple instances can improve Disk I/O. By doing so, you run your database more efficiently and faster. Here is a little help for you.

…

MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!Read More »

MySQL DoS in the Procedure Analyse Function – CVE-2015-4870

MySQL / By Jan Reilink / 30/05/2016

Sri Lankan Security researcher Osanda Malith discovered a DoS -or crash- vulnerability in MySQL’s Procedure Analyse Function. The vulnerability crashes MySQL versions up to 5.5.45.

…

MySQL DoS in the Procedure Analyse Function – CVE-2015-4870Read More »

How to string replace on all WordPress posts in MySQL

MySQL / By Jan Reilink / 13/09/2014

Learn how to replace content in your MySQL database in bulk with MySQL REPLACE. Sometimes it’s useful to know how to bulk edit content in your WordPress MySQL database, using MySQL’s REPLACE() function. Here is how to string replace content in WordPress wp_posts table to bulk edit WordPress posts through MySQL.

…

How to string replace on all WordPress posts in MySQLRead More »

MySQL database optimization with indices

MySQL / By Jan Reilink / 21/06/2014

Why MySQL indices are so important? Here at Vevida, we like to help our customers as much as possible. Even with optimizing a MySQL database when they don’t ask for it, or when a customer doesn’t know performance can be improved. For example by adding an index because we spotted a slow query in our slow-query log.

…

MySQL database optimization with indicesRead More »

Convert MySQL MyISAM tables to InnoDB

MySQL / By Jan Reilink / 19/06/2014

In the earlier days of MySQL, the default storage engine for your database tables was “MyISAM”. InnoDB is the engine to use now, and MyISAM is no longer actively developed. Therefore all MySQL optimizations are for InnoDB, and it’s recommended to switch from MyISAM to this InnoDB storage engine for your MySQL database tables. Follow this tutorial on how to convert from MyISAM to InnoDB.

…

Convert MySQL MyISAM tables to InnoDBRead More »

How to flush MySQL query cache from time to time

MySQL / By Jan Reilink / 15/06/2014

From time to time you need to flush MySQL’s query_cache to prevent fragmentation of free query cache memory space. Whether we need to flush the query cache is provided to us by MySQL’s Qcache_free_blocks status information. The higher the value the more fragmented free space is. In this post, you’ll learn how to flush the MySQL query cache unattended / automatic through a Linux cronjob and manually on the MySQL shell.

…

How to flush MySQL query cache from time to timeRead More »

Check, repair and optimize MySQL tables with mysqlcheck

MySQL / By Jan Reilink / 02/05/2014

MySQL’s mysqlcheck is a command line table maintenance program for MySQL. It checks, repairs, optimizes, or analyzes MySQL tables. Very handy for automated performance optimizations of MySQL databases and tables. In this post I’ll show you how to use mysqlcheck to optimize all MySQL tables using a cronjob.

…

Check, repair and optimize MySQL tables with mysqlcheckRead More »

How to compare MD5 and SHA1 hashes in MySQL

MySQL / By Jan Reilink / 27/04/2014

Some web scripting languages, such as classic ASP, don’t have native string hashing functions – like MD5 or SHA1. This makes it quite difficult to hash or encrypt user supplied input, and to perform string comparison to compare hashes. Let’s make MySQL do the string comparison and hash calculations for us!

…

How to compare MD5 and SHA1 hashes in MySQLRead More »