Michael's Blog

Home | RSS | Gallery | Stats | About | Comics | Downloads | Links | Scripts | Fuel Stats | Test Site | Wiki |
ack noob sauce. its all over this ticket
-- Chris Zimmerman     Add quote.

Selecting random records in postgresql

2015-08-18 09:15:00 by Michael 0 Comments
Tags: postgresql sql code

In an attempt to make my site more responsive I have been working on optimizing the SQL code used on the backend, this includes the random quote generator that I have set up on the main page.

The old code used a query similar to below.

SELECT quote, name FROM quotes ORDER BY RANDOM() LIMIT 1

This works fine if you have small tables and fast disks but consider the issue when there is a table with millions of rows. To find *one* record the server must read through the table, sort the records, and then discard every result but one. This operation is slow and inefficient.

To improve performance you can reduce the number of rows read by using a primary key on the table. Each row has a unique ID number which can then be used as the limit for the random() function. For example, the following query will select a random record based on the last index number created:

SELECT quote_text, name FROM quotes WHERE quote_id = (SELECT floor(random() * (SELECT last_value from quotes_quote_id_seq)+1)) ;

This query is not perfect and may result in empty results which your code will need to accommodate for but it is still more efficient than reading the entire table every time the page is loaded.

How to Manually Change Domain in Magento

2014-10-10 10:20:00 by Michael 0 Comments
Tags: magento linux mysql sysadmin

Changing the domain name on a Magento install requires a few steps to update the site URL in mysql. The procedure should be similar to below.

Update your core_config_data table to edit the two records for web/unsecure/base_url and web/secure/base_url


mysql> update core_config_data set value = 'http://dev.example.com/' where path = 'web/unsecure/base_url';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update core_config_data set value = 'http://dev.example.com/' where path = 'web/secure/base_url';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After this is done delete the contents of WEBROOT/var/cache. The location of the WEBROOT varies depending on how your server is set up.

cd /home/username/public_html/var/
rm -rf ./cache/*

Update any .htaccess redirects you may have added.

That's it, you're done! Open the site in a new browser tab to make sure that everything loads properly.

How to fix "Your profile could not be loaded" error in Google Chrome

2014-03-20 21:04:00 by Michael 0 Comments
Tags: chrome linux sqlite

If you get an error from chrome stating that your profile could not be loaded properly here is the PROPER way to fix the issue. Unfortunately googling for this error leads to a lot of false information and speculation.

First, go to your profile's data directory. In Linux this would be ~/.config/google-chrome/Default.

Now check for any processes that have the Web Data file open.

lsof Web\ Data

Kill those processes.

Next run an integrity check on the database.

sqlite3 Web\ Data "pragma integrity_check"

This should repair any errors in the file. After that is done start up chrome.

Manually create a Wordpress admin user from the mysql command line

2013-05-01 16:06:00 by Michael 0 Comments
Tags: linux mysql wordpress

If you need admin access to a wordpress install you can easily create a new admin user by running a few SQL commands on the database. This has been tested and verified to work on Wordpress 3.5.

To do this you will first need to identify what database the site is actually using. Check wp-config.php for the database name and mysql host info. Once you have that connect to mysql and run the following statements.

INSERT INTO wp_users (user_login,user_pass,user_email,user_registered,user_status) VALUES("user_name",md5('password'),"username@example.com",NOW(),0);

Find user ID from wp_users table:

SET @user_id = (SELECT ID FROM wp_users where user_login = 'user_name');

INSERT INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (@user_id,"wp_user_level","10");

INSERT INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (@user_id,"wp_capabilities",'a:1:{s:13:"administrator";s:1:"1";}');