Posts Tagged mysql

Copy mysql table between servers/databases

Posted by Bricky in Programming on July 20th, 2009

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).

Popularity: 9% [?]

,

2 Comments


All zero dates, mysql, and jdbc

Posted by Bricky in Programming on April 3rd, 2009 

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.

Popularity: 7% [?]

,

No Comments


htmlspecialchars for mysql

Posted by Bricky in Programming on March 2nd, 2009

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.

Popularity: 7% [?]

,

No Comments


SetPageWidth