Posts Tagged mysql
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
Update the running server with the same value
SET GLOBAL innodb_file_per_table=1;
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).
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).
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:
And that’s it, zero dates will be converted to nulls and jdbc will be happy.
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, '<', '<'); set _str = replace(_str, '>', '>'); return _str; END$$ DELIMITER ;
Usual disclaimers apply.