My Monkey Do

A Log of Coding Solutions

mysql

WordPress Deactive Plugin

by

Problem

I had a wordpress site where the public side was fine, but the admin side was throwing a Internal Server Error 500 intermittently.   I couldn’t figure out what was wrong since I had done no changes, and my host said nothing was wrong with the server.  To solve mysterious crashing errors in WordPress, the standard procedure is to first deactivate all your plugins.  However, I could not log in, so how was I to deactivate them?

Solution

A very smart fellow on the wordpress.org forums suggested to log into phpMyAdmin, and look at the wp_options table.  Then look for ‘active_plugins’ string under the option_name field.  Once located, change the option value to  a:0:{}

This deactivates all plugins.  Once deactivated, I re-activated each plugin and found the culprit.  It turned out the plugin was contacting its creator’s server and the creator’s server was down at the time.

source: http://wordpress.org/support/topic/500-internal-server-error-wp-admin-only

mysqldump – Using password: NO error

by

Problem

I need to back up a mysql database on a shared hosting server.  I am using the cron job to run a bash file which runs a mysqlcommand.  The problem is that I can’t get MySQL to recognize the password.  I have no spaces after the “-p” however, I still get the following error:

mysqldump: Got error: 1045: Access denied for user ‘some_user’@’localhost’ (using password: NO) when trying to connect

 

Solution

No thanks to JustHost of course.  They don’t know why this doesn’t work as one support member tried to help me, but stopped replying after all his solutions failed and another support member just plain said “we do not provide support for this issue”.

On to the solution.  The problem is that the mysql configuration file on JustHost is set to not supply a password no matter what you pass into the mysqldump command.  I’m not sure of the specifics of this so you can read more on configuration files if you need more details.  The key that clued me into the problem was a comment on this page.   He had a similar problem to what I was having.  In the end my cnf file looked like below, named “mymysql.cnf”:

 

[client]
# The following password will be sent to all standard MySQL clients
password="thePassword"

 

And my bash file looked like so:

#!/bin/bash

mysqldump --defaults-file=/somedir/mymysql.cnf -u theusername thedbname > /somedir/thedbname`date +%d`.sql

 

keywords: access denied using password NO
source: http://dev.mysql.com/doc/refman/5.1/en/option-files.html

LemonStand eCommerce Notes

by

Lemonstand is an eCommerce solution that lets you try before you buy.  It is very simple to use and virtually no learning curve to develop for.  It uses PHP and mySQL.  Check it out for yourself here.

Below are just a bunch of notes I jotted down while using and developing for Lemonstand.

Docs are located here:  http://lemonstandapp.com/docs/api_function_and_class_reference/
http://lemonstandapp.com/docs/developer_s_guide/

Editor buttons are editable in:

System/Settings/HTML Editor Settings

 

Enabling file-based tempaltes

  1. create an empty directory on the server – not web accessible, php writable
  2. Enable file-based templates on the System/Settings/CMS Settings page.

@font-face

If you need extra file extensions for fonts to be accessible, you need to edit the .htaccess file.
http://forum.lemonstandapp.com/topic/760-font-face/page__p__3768__hl__fonts__fromsearch__1#entry3768

 

Global Content

http://forum.lemonstandapp.com/topic/2392-global-editable-region-how-to/page__p__11010__hl__%2Bpartial+%2Bcontent_block__fromsearch__1#entry11010

 

 

 

 

MySQL Auto Backup

by

The following commands should be run daily.  This will give you a backup for 1 full month.  After 1 month the backups will overwrite the oldest files.  The %d is the day of the month.

GoDaddy:

In your Hosting Dashboard -> Content -> Chron Manager, enter the following line as the command.  Replace <xxx> with the appropriate values.

mysqldump --opt -Q -h <server_address> --user=<username> --password=<password> <database_name> > <your_path>/<database_name>_`date +%d`.sql

BlueHost:

In the CPanel -> Cron Jobs, enter the following line as the command.  replace <xxx> with the appropriate values.

mysqldump --opt -Q -h localhost --user=<username> --password=<password> <database_name> > /<your_path>/<database_name>_`date +%d`.sql

MySQL UTF-8

by

Problem:

I have a database that needs to accommodate all languages.  Currently it is set to the default latin1_swedish_c1.  I’m thinking I need to change it to UTF8, but there are so many UTF8 encodings!

 

Solution:

“_cs” is for case sensitive collations.  So for case insensitive applications you can use utf8_general_ci.  utf8_bin is binary so it’s case sensitive.

 

MySQL Query Examples

by

MySQL examples and queries:

http://www.artfulsoftware.com/infotree/queries.php

This great resource showed me a simple way to pivot in MySQL.  If you have the following tables:

CREATE TABLE `project` (
  `project_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`project_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `project_service` (
  `project_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  UNIQUE KEY `project_id` (`project_id`,`service_id`),
  KEY `service_id` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `service` (
  `service_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `short_name` varchar(5) NOT NULL,
  PRIMARY KEY  (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The result I wanted was to show a project name and its services all in one row.  To accomplish this the website said to use GROUP_CONCAT like so:

select
    p.name,
    GROUP_CONCAT(if(s.service_id = 1, '1', NULL)) AS 'Service1',
    GROUP_CONCAT(if(s.service_id = 2, '1', NULL)) AS 'Service2',
    GROUP_CONCAT(if(s.service_id = 3, '1', NULL)) AS 'Service3'
  from project as p
  inner join project_service as ps
    on (p.project_id = ps.project_id)
  inner join service as s
    on (s.service_id = ps.service_id)

With that query I can see a 1 for each of the project’s services.  Please see the pivot section for more details.