Multiplay Labs

tech hits and tips from Multiplay

Archive for the ‘MySQL’ Category

MySQL under FreeBSD max_open_files more than 11095

without comments

If your seeing the following in your MySQL log even after raising kern.maxfilesperproc on FreeBSD, don’t forget you need to logout and log back into the machine before the new kern.maxfilesperproc value will be available to your session and hence available when you start MySQL via rc.d
[Warning] Buffered warning: Could not increase number of max_open_files to more than 11095 (request: 25000)

Written by Dilbert

September 5th, 2013 at 3:41 pm

Posted in FreeBSD,MySQL

Making mysqldump v5.5 work with v5.6 servers

without comments

Due to a bugs in mysqldump that ship with v5.5 & v5.6 neither are compatible with each others DB making backups across version impossible.

The first issue is that v5.5 mysqldump against a v5.6 server failes with: “Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1'” the second is that v5.6 mysqldump against a v5.5 server fails with “Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193)

There is however a dirty hack which works around this. Starting with mysqldump from v5.5 edit the binary and search for the test “SET OPTION SQL_QUOTE_SHOW_CREATE” and replace each character in “OPTION” with a space, so you end up with: “SET        SQL_QUOTE_SHOW_CREATE

Written by Dilbert

April 9th, 2013 at 1:44 pm

Posted in MySQL

Plugin Storage Engines under MySQL 5.5 require Grant Tables

without comments

Under MySQL 5.5 optional storage engines now require privileges loaded and hence will fail to load when –skip-grant-tables is specified.

If you try to load them when this option is specified the result is that the entire db will fail to start and the following error will appear in the logs:
110318 21:37:23 [ERROR] /usr/local/libexec/mysqld: unknown option ‘–federated’

This in turn means that an upgrade from 5.x needs to be done in three steps if you have federated tables
1. With –skip-grant-tables and without –federated
2. Install and enable the federated plugin: mysql> install plugin federated soname ‘ha_federated.so’;
3. Without –skip-grant-tables and with –federated

Written by Dilbert

March 18th, 2011 at 9:52 pm

Posted in MySQL

Serious mysql client bug when handling –port

without comments

When running mysql client to connect to a database on the local machine using tcp by specifying –port and optionally –host=localhost the client will still use socket connection under unix.

This could potentially be “critical” issue if the host runs multiple instances and the user connects to the wrong instance, as they won’t know this has happened and hence could delete or modify vital information.

For more information see the mysql bug:
MySQL Bug: mysql client connects via socket even when –port and –host is specified

Written by Dilbert

December 8th, 2010 at 11:23 am

Posted in MySQL

ultrastats mysql database port fix

without comments

Just been playing more with ultrastats and went to set it up on our test db which runs on a none standard port. After fighting with it for 1/2 an hour it telling me access denied I looked at to code to find the nice port option it asks you for in the installer was totally and utterly ignored 🙁

Here’s the patch

--- ./install.php.orig	Tue Feb 24 21:19:24 2009
+++ ./install.php	Tue Feb 24 21:21:38 2009
@@ -240,5 +240,5 @@
 
 	// Now Check database connect
-	$link_id = mysql_connect( $_SESSION['DB_HOST'], $_SESSION['DB_USER'], $_SESSION['DB_PASS']);
+	$link_id = mysql_connect( "{$_SESSION['DB_HOST']}:{$_SESSION['DB_PORT']}", $_SESSION['DB_USER'], $_SESSION['DB_PASS']);
 	if (!$link_id) 
 		RevertOneStep( $content['INSTALL_STEP']-1, GetAndReplaceLangStr( $content['LN_INSTALL_ERRORCONNECTFAILED'], $_SESSION['DB_HOST']) . "<br>" . DB_ReturnSimpleErrorMsg() );
--- ./include/functions_db.php.orig	Tue Feb 24 21:24:00 2009
+++ ./include/functions_db.php	Tue Feb 24 21:24:48 2009
@@ -43,5 +43,5 @@
 
 	//TODO: Check variables first
-	$link_id = @mysql_connect($CFG['DBServer'],$CFG['User'],$CFG['Pass']);
+	$link_id = @mysql_connect("{$CFG['DBServer']}:{$CFG['Port']}",$CFG['User'],$CFG['Pass']);
 	if (!$link_id) 
 		DB_PrintError("Link-ID == false, connect to ".$CFG['DBServer']." failed", true);

Written by Dilbert

February 24th, 2009 at 10:41 pm

Posted in Hackery,MySQL

Tagged with , , ,