Letting the database to the heavy lifting: Capitalization via SQL

By Josh Highland in coldfusion, MySQL, php

Years ago I had the pleasure of meeting Ben Forta and listen to his thoughts on web development. One thing that stuck with me was his statement that:

“Dynamic programming languages like PHP and Coldfusion are great, but developers need to leverage the database more. Allow the database to do it’s job, the heavy lifting”

With that being said, I was recently working with a database that contained users first names. The data was not snantized at entry, and contained a mix of lower cased (ex. josh) and upper cased (ex. Josh) first characters in the firstname column. I needed to always present the users first name in a capitalized format.

The first solution that most developers would reach for is dynamically parsing the users first name, then upper-casing the first character. This works, but in my opinion isn’t the most elegant solution.

With a simple SQL statement, like the one below, we can return the users first name already in a capitalized state.

SELECT (UPPER(LEFT(firstname,1))+SUBSTRING(firstname,2,LEN(firstname))) as firstname 
FROM users
It’s quick to implement, its easy to work with, and it’s also very fast on the performance scale.
Yes, I understand that there are cool convenience functions in like ucfirst() in PHP, but I’m a lazy programmer and I don’t want to have to remember to use ucfirst() every time I’m dealing with the users name. I’d rather get the information in the correct format the first time, then remembering to groom it every time before I use it. Set it and forget it.

SQL Server 2008: FIXED “Data has changed since the Results pane was last retrieved”

By Josh Highland in MySQL, Programming, software, tutorials, windows

Today I was trying to edit the value in a table cell through the query browser in Microsoft SQL Server Management Itudio and I kept getting the following error:

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

I would click “Yes”, and get another error:

No row was updated.
The data in row X was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(N rows).
Correct the errors and retry or press ESC to cancel the change(s).

I was killing myself trying to figure out what was wrong. I was the only person working on the database, so the data wasnt being changed behind my back.

After an hour on goolge I came across a Microsoft tech article for MS SQL 2005, http://support.microsoft.com/kb/925719

It stated the issue could occure from one of the following conditions:

  • The table contains one or more columns of the text or ntext data type.
  • The value of one of these columns contains the following characters:
  • Percent sign (%)
  • Underscore (_)
  • Left bracket ([)
  • The table does not contain a primary key.

I double checked the table I was working on and realized it didnt have a primary key. It must have gotten dropped in the data migration from MS SQL 2000 to MS SQL 2008. A simple right click, set primary key and my problem was fixed.

Verify the primary keys! It’s a simple fix to a head breaker of a problem. I hope someone finds this and it fixes their issue.

phpBB admin password manual reset

By Josh Highland in hacks, internet, MySQL, php, semi-legal, software, tutorials

phpbb_logo

Today I ran into a situation where I had forgotten the password to my development instance of phpBB 3. I was stuck in a situation were I needed to reset the password. I had full admin access to the database, so changing it there wouldn’t be the problem. The real problem is that phpBB uses its own password hashing, not MD5.

In a work around, I created a new user and used the password “123456” looking at the database , in the users table of the phpBB install. I saw the “user_password” field was “e10adc3949ba59abbe56e057f20f883e”.

I then changed my admin accounts user to the same string, “e10adc3949ba59abbe56e057f20f883e”.

phpbbpasswordreset

I went to the phpBB login screen, fillled out my username, and entered the password “123456”… BINGO! it worked.

So to save you the work. You can follow what I did or just use these hashes to reset your own password:

Hash: e10adc3949ba59abbe56e057f20f883e
Password: 123456

Hash: $H$9Ae3Uk.ECdWW5ya13M4ErWhr4c.761/
Password: password

I hope this helps someone else out there.

Manually reset your WordPress password

By Josh Highland in hacks, internet, MySQL, software, tutorials, windows, wordpress

lost-wordpress-password

It doesn’t matter what application your working with, losing your password is always a pain in the ass. Luckly if you are working with wordpress (man, I blog a lot about wordpress these days), on your server and you have access to the MySql database, resetting your password manually is a snap.

  • Login to your PhpMyAdmin
  • Select your WordPress database and click on the “SQL” button to open the SQL query window.

wordpress-phpmyadmin

  • Paste the following code in the window textarea. (Don’t forget to modify the password and username before executing it)

UPDATE ‘wp_users’ SET ‘user_pass’ = MD5(‘PASSWORD’) WHERE ‘user_login’ =’admin’;

That’s it! Your password has been reset, and you should be able to login to your wordpress admin area once again.


			



	

		
Solution for: MySQL server has gone away at mysqlhotcopy line 528
		
By Josh Highland in linux, MySQL, software 
	

		
 
		
mysql-logo


Recently I was backing up a large MySql database (several hundred megabytes), using the awesome MySqlHotCopy script, when I started getting the following error:


DBD::mysql::db do failed: MySQL server has gone away at mysqlhotcopy line 528.


I have no clue what that error means. mysqlhotcopyworked great on all of my other smaller databases. I did a little searching on my old friend google, and after sniffing around a bit, I came up with a resolution to the problem… the script was timing out, so I just had to increase the allowed time in the /etc/my.cnf file.


Here are the steps I took


pico /etc/my.cnf


add these lines to the file:

interactive_timeout = 3600

wait_timeout = 3600


save file


/etc/init.d/mysqld restart


I ran mysqlhotcopy again, and everything worked and the backup was made.