Tag Archives: mysql

Amazon RDS Webinars Available

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

Posted in Linux, Marketing, Server Admin, Web Development | Tagged , , | Leave a comment

WordPress: Convert All Tables To Utf8

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

Posted in Server Administration, Web Development | Tagged , | Leave a comment

phpmyadmin: Drag And Drop File Upload

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.

Posted in Server Administration, Tech Opinion, Web Development | Tagged , , , , , | Leave a comment

WordPress: CREATE TABLE `wp_redirection_404` mysql error

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.

Posted in Tech Tips, Web Development | Tagged , , , , | 1 Comment

fix “max_allowed_packet” on MySql

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;

Posted in Server Admin, Server Administration, Tech Tips, Web Development | Tagged , , | Leave a comment

How to Reset WP to Twitter

The WordPress plugin WP to Twitter “posts a Twitter status update when you update your WordPress blog or post to your blogroll, using your chosen URL shortening service. Rich in features for customizing and promoting your Tweets.” You can activate the plugin and then set all the options, but if you are having trouble the plugin offers no way to unset all of the options associated with it. You can use the following sql / mySQL command to remove all of the options generated by this plugin. Use this at your own risk, and of course create a backup of your database before attempting this. delete from wp_options where option_name in (‘app_consumer_key’, ‘app_consumer_secret’, ‘bitlyapi’, ‘bitlylogin’, ‘comment-published-text’, ‘comment-published-update’, ‘disable_oauth_notice’, ‘disable_twitter_failure’, ‘disable_url_failure’, ‘jd_date_format’, ‘jd_donations’, ‘jd_dynamic_analytics’, ‘jd_individual_twitter_users’, ‘jd_keyword_format’, ‘jd_last_tweet’, ‘jd_max_characters’, ‘jd_max_tags’, ‘jd_post_excerpt’, ‘jd_replace_character’, ‘jd_shortener’, ‘jd_status_message’, ‘jd_strip_nonan’, ‘jd_tweet_default’, ‘jd_twit_append’, ‘jd_twit_blogroll’, ‘jd_twit_custom_url’, ‘jd_twit_prepend’, ‘jd_twit_remote’, ‘limit_categories’, ‘newlink-published-text’, ‘oauth_token’, ‘oauth_token_secret’, ‘suprapi’, ‘suprlogin’, ‘tweet_categories’, ‘twitter-analytics-campaign’, ‘twitterInitialised’, ‘use-twitter-analytics’, ‘use_dynamic_analytics’, ‘use_tags_as_hashtags’, ‘wp_bitly_error’, ‘wp_debug_oauth’, ‘wp_supr_error’, ‘wp_to_twitter_version’, ‘wp_twitter_failure’, ‘wp_url_failure’, ‘wpt_inline_edits’, ‘wpt_post_types’, ‘wtt_oauth_hash’, ‘wtt_twitter_username’, ‘wtt_user_permissions’, ‘yourlsapi’, ‘yourlslogin’, ‘yourlspath’, ‘yourlsurl’)

Posted in Social Media, Tech Tips, Web Development | Tagged , , , | Leave a comment

Woo Themes and Affiliate Link Structure

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 [371]: 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/’, ‘123.45.67.89’, NULL, ”, 0) Stack Trace system/libraries/drivers/Database/Mysql.php [99]:Mysql_Result->__construct( ) system/libraries/Database.php [259]: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/', '123.45.67.89', NULL, '', 0) ) system/libraries/Database.php [962]: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/', '123.45.67.89', NULL, '', 0) ) application/models/affiliatestats.php [138]:Database_Core->insert( wm_affclick, Array ( [aff_id] => 123456 [url] => http://www.woothemes.com/extension/google-product-feed/ [remote_addr] => 123.45.67.89 [referrer] => [woo_product] => [is_unique] => 0 ) ) application/helpers/log.php [95]:AffiliateStats_Model->create( Array ( [aff_id] => 123456 [url] => http://www.woothemes.com/extension/google-product-feed/ [remote_addr] => 123.45.67.89 [referrer] => [woo_product] => [is_unique] => 0 ) ) application/controllers/go.php [124]:log_Core->add_affclick( 123456, http://www.woothemes.com/extension/google-product-feed/ ) Go_Controller->index( ) system/core/Kohana.php [291]: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 [209]:call_user_func( Array ( [0] => Kohana [1] => instance ) ) system/core/Bootstrap.php [55]:Event::run( system.execute ) index.php [106]:require( system/core/Bootstrap.php )Loaded in 0.0088 seconds, using 1.13MB of memory. Generated by Kohana v2.3.4.

Posted in Server Admin, Web Development | Tagged , , , | 1 Comment

MySQL UPDATE JOIN

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)

Posted in Tech Tips, Web Development | Tagged , , , , | Leave a comment

WordPress: SQL to get all Categories

If you’re trying to get all categories in WordPress with a real, raw, mysql sql statement, this is the type of query you can use: SELECT * FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id This is how you can get posts of a certain category / taxonomy. I have a few extra lines commented out that could also be used: SELECT * FROM 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) WHERE 1 #AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (13) #AND wp_term_taxonomy.term_id IN (1,2,3,4,5,6,7) GROUP BY wp_posts.ID ORDER BY ID Update: Omar asks: cool.. but how can I get the NAME of those categories ?? Well, try this: SELECT * FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id or just for the names: SELECT wp_terms.name FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id

Posted in Social Media, Tech Tips, Web Development | Tagged , , , , , | 6 Comments

phpMyAdmin plugins / extensions

Posted in Web Development | Tagged , , , | 2 Comments