Posts Tagged mysql

MySQL: moving innodb tables out of ibdata1

Note-to-self style post again.

MySQL’s innodb will store all table data in one file called ibdata1, unless you tell it to do otherwise. This can become an issue sometimes, particularly when the file gets really big.

Thankfully there’s a way to fix it.

Add innodb_file_per_table to my.cnf

[mysqld]
innodb_file_per_table

Update the running server with the same value
SET GLOBAL innodb_file_per_table=1;

Then
ALTER TABLE tablename ENGINE=Innodb;
for each table to move it out of the ibdata1 file.

Alternatively, you can use this little bit of trickery to generate the sql needed to do it for all tables:

SELECT concat('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=Innodb;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB';

Then paste the result as sql and run it (it may take some time though).

,

No Comments

Copy mysql table between servers/databases

This is one of those reminder-to-self style posts.

To copy mysql table tablename from database1 on server1 to database2 on server2, issue the following command (on either server)

mysqldump -h server1 -u user1 -ppassword1 database1 tablename | mysql -h server2 -u user2 -ppassword2 database2

user1, password1, user2, and password2 are the respective usernames and passwords to use on server1 and server2, respectively. Note that there is no space between the -p and password (unlike the other switches).

,

3 Comments

All zero dates, mysql, and jdbc

Cannot convert value '0000-00-00 00:00:00' from column N to TIMESTAMP

Familiar? This problem arises because ‘null’ dates in MySql are generally represented as ‘0000-00-00 00:00:00′, which, while valid in MySql, are completely forbidden in jdbc.

There is a wonderful workaround however, simply add the zeroDateTimeBehavior parameter to the end of your jdbc url as follows:

jdbc:mysql://hostname/dbname?zeroDateTimeBehavior=convertToNull

And that’s it, zero dates will be converted to nulls and jdbc will be happy.

,

No Comments

htmlspecialchars for mysql

Yes, it’s bad, evil, nasty, and just plain wrong.

But that doesn’t mean that there aren’t occasions when you do need to spew out html directly from mysql (or at least I’ve found one).

DELIMITER $$

DROP FUNCTION IF EXISTS `htmlspecialchars`$$
CREATE FUNCTION `htmlspecialchars` (_str text) RETURNS text
BEGIN
	set _str = replace(_str, '"', '"');
	set _str = replace(_str, '&', '&');
	set _str = replace(_str, '<', '&lt;');
	set _str = replace(_str, '>', '&gt;');
	return _str;
END$$

DELIMITER ;

Usual disclaimers apply.

,

No Comments

css.php