Tag Archives: mysql
Dear EC2 User, I work in the Business Development team for Amazon Database Services. We co-ordinate interactions between AWS customers and the product teams for DynamoDB, ElastiCache, and Amazon Relational Database Service (RDS). In case you’re not familiar with RDS, it’s a web service designed to make it easy to set up, operate, and scale a relational database in the cloud. We’re running a series of free webinars that will highlight examples and best practices used by RDS customers to help improve performance, reduce costs, and eliminate most of the effort of database administration. Details of the next event are: Save time and effort. Focus on your app with Amazon RDS Amazon RDS simplifies database administration, giving you more time to build and optimize your applications 13 September 2012, 10.00AM to 11:00AM PDT This webinar will provide detailed information on customer use cases and best practices.Topics include: performance, security, migration and data protection. I hope you can make it. Regards, David Pearson Business Development Manager
Are your WordPress MySQL Tables of mixed charsets? The ideal or standard character set is utf-8 as it supports multiple languages and special characters – so people from all over the world can post comments, register, and interact with your blog without having characters from their name, languages, etc converted into weird or strange symbols. I read the WordPress article on Converrting your Tables but wasn’t so sure which direction to go. The article states: In most cases if a collation is not defined MySQL will assume the default collation for the CHARSET which is specified. For UTF8 the default is utf8_general_ci, which is usually the right choice. After a little research I got it working. The following code is from the supplemental code page from that article and I found that the code below (second code block on that page) worked well for WordPress 3.4.1 I would not recommend using the bash script at the bottom of that page because while it converts the tables it doesn’t convert the fields. When connecting from the command line you may need to specify –protocol=TCP if you get an error like ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) But again, I would not really recommend that script, because while it will run, it will basically only run the following commands: ALTER TABLE wp_commentmeta CHARACTER SET utf8; ALTER TABLE wp_comments CHARACTER SET utf8; ALTER TABLE wp_links CHARACTER SET utf8; ALTER TABLE wp_options CHARACTER SET utf8; ALTER TABLE … Continue reading
I use phpMyAdmin to manage mySql databases quite often and I find myself uploading / importing SQL files. I’ve gotten a little spoiled with WordPress’ HTML5 drag and drop ajax style uploader. Every time I go to use phpMyAdmin I start wishing that it had a HTML5 style drag-and-drop file uploader, something like this: I think it would be fairly easy to write a plugin and implement this idea but I have not done it yet. Leave a comment if the lack of a drag and drop HTML5 file uploader for phpMyadmin bugs you too.
If you’re getting the following error: WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘KEY ‘referrer’ (‘referrer’) ) DEFAULT CHARACTER SET utf8′ at line 12] CREATE TABLE ‘wp_redirection_404’ ( ‘id’ int(11) unsigned NOT NULL AUTO_INCREMENT, ‘created’ datetime NOT NULL, ‘url’ varchar(255) NOT NULL DEFAULT ”, ‘agent’ varchar(255) DEFAULT NULL, ‘referrer’ varchar(255) DEFAULT NULL, ‘ip’ int(10) unsigned NOT NULL, PRIMARY KEY (‘id’), KEY ‘created’ (‘created’), KEY ‘url’ (‘url’), KEY ‘ip’ (‘ip’,’id’) KEY ‘referrer’ (‘referrer’) ) DEFAULT CHARACTER SET utf8; Most likely you need to edit the plugin file: wp-content/plugins/redirection/models/database.php and add a comma around line 195: KEY `ip` (`ip`,`id`) needs to be changed to: KEY `ip` (`ip`,`id`), This is a flaw with the upgrade_to_231 function which is not called very often.
MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes To fix this I ran these two commands in mySQL before executing the queries that were giving me the error message about the max allowed packet bytes. set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;
Recently received an error from Woo. It’s below, and it has some unique insights to how the database and affiliate tracking is done. Database Error Database Error A database error occurred while performing the requested procedure. Please review the database error below for more information. system/libraries/drivers/Database/Mysql.php : There was an SQL error: Duplicate entry ‘9802422’ for key 1 – INSERT INTO `wm_affclick` (`aff_id`, `url`, `remote_addr`, `referrer`, `woo_product`, `is_unique`) VALUES (‘123456’, ‘http://www.woothemes.com/extension/google-product-feed/’, ‘22.214.171.124’, NULL, ”, 0) Stack Trace system/libraries/drivers/Database/Mysql.php :Mysql_Result->__construct( ) system/libraries/Database.php :Database_Mysql_Driver->query( INSERT INTO `wm_affclick` (`aff_id`, `url`, `remote_addr`, `referrer`, `woo_product`, `is_unique`) VALUES ('123456', 'http://www.woothemes.com/extension/google-product-feed/', '126.96.36.199', NULL, '', 0) ) system/libraries/Database.php :Database_Core->query( INSERT INTO `wm_affclick` (`aff_id`, `url`, `remote_addr`, `referrer`, `woo_product`, `is_unique`) VALUES ('123456', 'http://www.woothemes.com/extension/google-product-feed/', '188.8.131.52', NULL, '', 0) ) application/models/affiliatestats.php :Database_Core->insert( wm_affclick, Array ( [aff_id] => 123456 [url] => http://www.woothemes.com/extension/google-product-feed/ [remote_addr] => 184.108.40.206 [referrer] => [woo_product] => [is_unique] => 0 ) ) application/helpers/log.php :AffiliateStats_Model->create( Array ( [aff_id] => 123456 [url] => http://www.woothemes.com/extension/google-product-feed/ [remote_addr] => 220.127.116.11 [referrer] => [woo_product] => [is_unique] => 0 ) ) application/controllers/go.php :log_Core->add_affclick( 123456, http://www.woothemes.com/extension/google-product-feed/ ) Go_Controller->index( ) system/core/Kohana.php :ReflectionMethod->invokeArgs( Go_Controller Object ( [uri] => URI Object ( ) [input] => Input Object ( [use_xss_clean:protected] => 1 [magic_quotes_gpc:protected] => 1 [ip_address] => ) [session] => Session Object ( [input:protected] => Input Object ( [use_xss_clean:protected] => 1 [magic_quotes_gpc:protected] => 1 [ip_address] => ) ) ) ) Kohana::instance( ) system/core/Event.php :call_user_func( Array (  => Kohana  => instance ) ) system/core/Bootstrap.php :Event::run( system.execute ) index.php :require( system/core/Bootstrap.php )Loaded in 0.0088 seconds, using 1.13MB of memory. Generated by Kohana v2.3.4.
Here is a working example of how to do an UPDATE JOIN with MySQL: MySql UPDATE LEFT / CENTER / RIGHT JOIN Example This is the correct way to update a table when using a JOIN. They key is that the SET needs to come after the JOIN. UPDATE wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) SET wp_term_taxonomy.taxonomy = ‘category’ WHERE 1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (1) This WILL NOT WORK because the SET is in the wrong place: UPDATE wp_posts SET wp_term_taxonomy.taxonomy = ‘category’ LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (1)