Thursday, April 24, 2008

Reset User Account Password Example for MySQL

Recently I upgraded a legacy application on my server. After the upgrade, I had to reset a user account's password to match the one in the application's configuration file. Luckily, MySQL provides a utility to handle this fairly straightforward task. Here is the syntax and a couple examples.

mysql> use mysql;
Database Changed
mysql> set password for 'elforum'@'localhost' = password('98m4@9er');
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'mysysad'@'localhost' = password('950v4.Kr');
Query OK, 0 rows affected (0.00 sec)

mysql> select user, password from user;
+---------------+-------------------------------------------+
| user | password |
+---------------+-------------------------------------------+
...
| elforum | *EAE7DE143B1B9598745AD571885A98E723ABE9056 |
| mysysad | *Ebe7D596878627EDD581EADEFSA98E723ABE9C58 |
...

Sunday, April 20, 2008

My SysAd Blog Changes Domain Name

I finally implemented the domain name, www.mysysad.com, for My SysAd Blog. I had been reluctant to change the name because I had heard nightmare stories where a site’s traffic dries up overnight. But apparently Blogger does a decent job forwarding requests to your new domain. I have not really noticed a drop off. Knock on wood though…

I found my domain registrar, yahoo, Advanced DNS settings to be somewhat limited. I had no problems setting up the canonical name (www.mysysad.com) to Google’s ghs.google.com, which is a load balanced server array, but setting up the A record for mysysad.com has been annoying. Unless the A record is pointing to an explicit hostname IP, yahoo will not allow it. I was trying to set it to ghs.google.com. Moreover, Blogger will not forward requests for mysysad.com to www.mysysad.com, even if told to do so in the publishing backend. It goes to the “parked page.” I had to set an explicit IP for Blogger to work on the non-www version. I sent yahoo an email about the aforementioned, but I have yet to receive a response, not even an automated acknowledgment. That has been the case in previous contact attempts, too (i.e. transferring domains). It appears I have two options: a 301 redirect or a new DNS host.

While visiting China in November 2007, I remember attempting to access esofthub.blogspot.com but was redirected to the Baidu.com search engine. But when I clicked on its link, it was blocked. I was later told the *.blogspot.com domain was blocked there. I wonder if that is still the case…

For the blogs linking back to me, I would greatly appreciate if you would change your link from http://esofthub.blogspot.com to http://www.mysysad.com. Thanks.

Here is a great resource regarding the aforementioned issue.
The Real Blogger Status

In particular
Google customer domain setup DNS referral
DNS host is crucial for custom domain
Use CNAME referral for custom domain
Custom domains using 301 redirect
Setting up custom domain here's advice

#####
Update: Three hours after this post, I received this message from Yahoo! I guess I won't be using Google's ghs.google.com - load balanced server array. Yahoo's solution was different from Bloggers. At any rate, thanks Heather. Here is the response from yahoo.
#####

Hello,

Thank you for writing to Yahoo! Domains.

I welcome the opportunity to assist you.

I understand from your email that your domain "mysysad.com" is not working without 'www'.

I realize that you have been greatly inconvenienced by this situation. I am glad to provide you further assistance.

With the help of your test permission, I accessed your account and found that you have set A records for your domain.

I further checked your account and found that you have also set CNAME records to "ghs.google.com". Due to this reason your website was not resolving.

I have rectified the problem and now your website is working fine without 'www' and showing all the contents as below:

"My SysAd Blog -- UNIX

This blog covers UNIX system administration HOWTO tips for using inline for loops, find command, UNIX scripting, configuration, SQL, various UNIX-based tools, and command line interface syntax. The UNIX OS supports tasks such as running hardware, device drivers, peripherals and third party applications. Share tips/comments. Read the comments. But most importantly: Read Disclaimer - Read Disclaimer.

Sunday, April 20, 2008

My SysAd Blog Changes Domain Name

I finally implemented the domain name, www.mysysad.com, for My SysAd Blog. I had been reluctant to change the name because I had heard nightmare stories where a site's traffic dries up overnight. But apparently Blogger does a decent job forwarding requests to your new domain. I have not really noticed a drop off. Knock on wood though..."

------------------------

In many cases, issues with your browser can be responsible for the difficulty. Clearing your browser's cache and deleting the cookie files on your computer can often resolve these issues.

Therefore, I would suggest you to clear cache and cookie of your browser.

To clear your cache:

1. Open Internet Explorer 7 and click on the "Tools" menu.
2. Select "Internet Options."
3. Click on the "General" tab if it isn't selected already.
4. Click on the "Delete..." button.
5. Under the heading "Temporary Internet files," click "Delete
Files..."
(Note that it can take up to 30 minutes for your computer to delete
your cache history.)
6. Click "OK" to exit.

To clear your cookies:

1. Click on "Tools" > "Internet Options" > "General" again.
2. Click the "Delete..." button.
3. Under the heading "Cookies," click on "Delete cookies..."
4. Click "Yes" for the "Delete all cookies in the Temporary Internet
Files folder?" prompt.
5. Click "Close" to exit.

I would also suggest you to replicate the issue in a different browser and in a different system.

Therefore, with the help of above given steps, you will be able to view your website without facing any problems.

However, if you feel your question was not addressed, please let us know by responding to this email. Please describe all of the actions you took that led up to the problem, the exact text of any error messages you received, and how often this occurs.

The more information we have, the better able we will be to investigate this issue.

I have tried my best to address your concerns and I hope my explanations are up to your satisfaction, as your satisfaction is my primary goal.

Please do not hesitate to reply if you need further assistance.

Regards,

Heather

Yahoo! Customer Care

For assistance with all Yahoo! services please visit:

http://help.yahoo.com/

Original Message Follows:
-------------------------

Yahoo Id: NO_WAY_SPAMMER
First Name: Roy
Last Name: Wood
Email Address: NO_WAY_SPAMMER at whatever
Domain Name: mysysad.com
Comments: I'm trying to add ghs.google.com as an A record for mysysad.com (points to my esofthub.blogspot.com account). But it's not working. It keeps failing because I'm trying to use Google's "ghs.google.com." as a destination. I want this config because it's a load balancing server array, not a single server. Can you make the modification? Most other web host allows this type of modification. I don't understand why Yahoo.com is limiting it. The only thing (extraneous ...) The www.mysysad.com works fine.
Subject: A Record
Browser: Internet Explorer
Operating System: Windows Vista
Email Tool: none
Site Building Tool: none
Internet Connection: Dial-up
Permit Test: yes

Friday, April 18, 2008

Create User Account Example for MySQL

Lately, I have been adding several user accounts to the mysql database. Since I have seen several queries via my metadata provider to create user accounts in MySQL, I have decided to add a post about it. There are three different methods that can be used to create user accounts and they are fairly straightforward.

mysql> use mysql;
Database changed

Method 1: (create user, password - no privileges)
mysql> CREATE USER 'esoft'@'localhost' IDENTIFIED BY '12wer56hi';
Query OK, 0 rows affected (0.16 sec)

--grant certain privileges
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'esoft'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--grant all privileges
mysql> GRANT ALL ON *.* TO 'esoft'@'localhost';
Query OK, 0 rows affected (0.00 sec)


Method 2: (create user/password and grant all privileges at one fell swoop)
mysql> GRANT ALL ON *.* TO 'topblog'@'localhost' IDENTIFIED BY 'topblog123';
Query OK, 0 rows affected (0.00 sec)


Method 3: (Insert user, password and privileges in table)
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','freeads',PASSWORD('adlists123'));
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)
-> VALUES('localhost','softhub',PASSWORD('softhub126'),'Y','Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES('localhost','freebies',PASSWORD('afreeb456'), 'Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Note additional *_priv:
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
Create_view_priv
Show_view_priv
Create_routine_priv
Alter_routine_priv
Create_user_priv
Event_priv
Trigger_priv

Tuesday, April 15, 2008

Create Database and Migrate Data Example for MySQL

Over the weekend, I was supposed to be preparing for my second Toastmaster's speech which is "organizing your speech." I really dislike public speaking and that was the primary reason I joined up - basically facing my fears. I did the “Icebreaker” speech about three months ago and decided it was time to do another one a couple weeks ago. Predictably, I found everything else in the world to do, except preparing for my speech.

All of sudden, I decided it was "time" to setup a MySQL server, Apache webserver, FTP server, PHP and another CMS interface. I have wanted to install another instance of my website on a local box, but I guess I just needed an impetus (i.e. speech preparation avoidance) to get it done. I performed the setup twice because I had some issues with the initial run and wanted to get it done right. I had enough time, right? Smile.

The part I didn't like was the data migration (always seems like dead time). But anyways, here is how I handled that part from an exported file. Make sure your user has database creation privileges.

Note: Used the "_" to get around Blogger's parser.

mysql -u database_user -p > c:_\tmp\adlistInfile.txt
Enter password: *********

Sample contents of adlistInfile.txt:
create database my_exampledb;
use my_exampledb
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE `ad_admin` (
...
...
PRIMARY KEY (`IdAd`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `ad_admin` VALUES(3, 'exampleuser', '987...', '2008-04-12
22:59:06', '2008-04-12 22:57:09');
...

By the way, I did give my speech today. It was about how the housing, credit, and financial crises are contributing to a possible recession. After getting through the speech, I volunteered for another speech for the next meeting. Well, you know what that means…maybe it is time to build that "time converter" program I have been thinking about in C#.

Friday, April 11, 2008

Truncate Table Example for MySQL

I have been upgrading my phpbb forum from 2.X to 3.X. It has been a slog so far and mostly due to spammers. Before the 3.X converter would allow me to complete the conversion process, I had to delete most of the spammer usernames because they were essentially dups (e.g. User, USer, user, etc) but with different UID's . There were about 7,000 rows in the phpbb_user table - now there is a fraction of that left. Good.

Anyways, after exporting approximately 7000 rows to a file, I truncated the table. Here is an example.

TRUNCATE TABLE `phpbb_users`;

Here is the cleaned up (unique usernames) version of the file, which was re-ingested into the phpbb_users 2.X table.

INSERT INTO `phpbb_users` VALUES(2, 1, 'adam123', '49ad5...,NULL);
INSERT INTO `phpbb_users` VALUES(3, 1, 'laurahing', 'a3dcb4d2..., NULL);
INSERT INTO `phpbb_users` VALUES(4, 1, 'joshman', '49ad5b0771f..., NULL);
INSERT INTO `phpbb_users` VALUES(5, 0, 'ipsofacto', 'b63fc..., NULL);
INSERT INTO `phpbb_users` VALUES(6, 1, 'FitForFree', '04305e8e..., NULL);
INSERT INTO `phpbb_users` VALUES(7, 1, 'banchee', '8a645340...,NULL);
...

#####
I saw another meta data query asking how to limit the deletes by time for mysql. Here is a simple example.

DELETE FROM classified_ads WHERE user = 'esoft123'
ORDER BY timestamp_col LIMIT 10;

Saturday, April 05, 2008

Free Anti-virus Software Solution

If you are like me, you are always searching for ways to protect your personal computer. Probably, the first thing that comes to mind is viruses and worms.

Along with millions of other AVG users, I have been using their free anti-virus software version off and on since 1997. Usually, when something is for free, you get what you paid for - sub par performance. But with AVG, I can't complain too much about their free non commercial anti-virus solution. Why? It has been easy to use, consumes low system resources, automatic update functionality and provides real time protection. If you want more services, such as anti-spyware, anti-rootkit, anti-spam, firewall, web shield or 24/7 support, you will have to pay for them. The free version supports most Windows and Linux platforms. As usual, this is not a paid post and other free anti-virus solutions/suggestions are welcome.

Note: Per AVG's website: AVG Anti-Virus Free is only available for single computer use for home and non commercial use - for platform compatibility.

Tuesday, April 01, 2008

Create table and Insert into Examples for MySQL

A couple of days ago I was adding entries to my ban related tables via phpbb's administration tools. After awhile, I quickly tired of serially adding an entry and then submitting it. I decided to dump the three tables in question and then modify them by hand. Plus that, I wanted to review the MySQL syntax.

Here are a series of create and insert statements for MySQL. I modified some of the entry values with "x", "!", "?" as to not offend some readers.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Table structure for table `phpbb_banlist`
--

CREATE TABLE `phpbb_banlist` (
`ban_id` mediumint(8) unsigned NOT NULL auto_increment,
`ban_userid` mediumint(8) NOT NULL default '0',
`ban_ip` varchar(8) NOT NULL default '',
`ban_email` varchar(255) default NULL,
PRIMARY KEY (`ban_id`),
KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=97 ;

--
-- Dumping data for table `phpbb_banlist`
--

INSERT INTO `phpbb_banlist` VALUES(84, 0, '', '*@fromru.com');
INSERT INTO `phpbb_banlist` VALUES(5, 0, '', '*@mail.ru');
INSERT INTO `phpbb_banlist` VALUES(6, 0, '', '*@*.ru');
INSERT INTO `phpbb_banlist` VALUES(7, 0, '', '*@go2.pl');
...

CREATE TABLE `phpbb_disallow` (
`disallow_id` mediumint(8) unsigned NOT NULL auto_increment,
`disallow_username` varchar(25) NOT NULL default '',
PRIMARY KEY (`disallow_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

INSERT INTO `phpbb_disallow` VALUES(1, '*porn*');
INSERT INTO `phpbb_disallow` VALUES(2, '*sex*');
INSERT INTO `phpbb_disallow` VALUES(3, '*xxxxx*');
INSERT INTO `phpbb_disallow` VALUES(4, '*co!!*');
INSERT INTO `phpbb_disallow` VALUES(5, '*axx*');
INSERT INTO `phpbb_disallow` VALUES(6, '*adult*');
INSERT INTO `phpbb_disallow` VALUES(7, '*viagra*');
INSERT INTO `phpbb_disallow` VALUES(8, '*pill*');
INSERT INTO `phpbb_disallow` VALUES(9, '*drug*');
INSERT INTO `phpbb_disallow` VALUES(10, '*pharm*');
...

-- Table structure for table `phpbb_words`
--

CREATE TABLE `phpbb_words` (
`word_id` mediumint(8) unsigned NOT NULL auto_increment,
`word` char(100) NOT NULL default '',
`replacement` char(100) NOT NULL default '',
PRIMARY KEY (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

--
-- Dumping data for table `phpbb_words`
--

INSERT INTO `phpbb_words` VALUES(1, 'xxxx', '***');
INSERT INTO `phpbb_words` VALUES(2, 'sh??', '****');
INSERT INTO `phpbb_words` VALUES(3, 'xxxch', '*****');
INSERT INTO `phpbb_words` VALUES(4, 'fxxke?', '******');
INSERT INTO `phpbb_words` VALUES(5, 'axx', '***');
INSERT INTO `phpbb_words` VALUES(6, '*turd*', '****');
...