Blog

Debugging Magento Duplicate Entry Database Error

Posted on December 7, 2011 by jess

I recently got a call from a client that has a Magento cart. He was getting an error when saving a product:

#1062 - Duplicate entry '59-0-1-229' for key 1

I wanted to do a quick writeup because it was a rather difficult one to debug and solve. After duplicating the error, I first tried to reindex the database tables. However, the “Product Prices” tabel was giving an error and couldn’t be saved.

I logged into the server and check the execption log. It didn’t give me much more info:

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '59-0-1-229' for key 1' in /home/print/public_html/shop/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/print/public_html/shop/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/print/public_html/shop/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/print/public_html/shop/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/print/public_html/shop/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /home/print/public_html/shop/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/print/public_html/shop/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Default.php(449): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#6 /home/print/public_html/shop/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Default.php(120): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Default->_applyCustomOption()
#7 /home/print/public_html/shop/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price.php(377): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Default->reindexAll()
#8 /home/print/public_html/shop/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price->reindexAll(.....

Notice how the query error left out the actual query. At this point I figure there’s something wrong with a table somewhere. There are 250 tables in a Magento database, where to start? It has to be something to do with products, but that doesn’t narrow it down much, there are many tables dealing with products.

Then I tried a global search on the database from phpMyAdmin for the
duplicate key 59-0-1-229, but it turned up no results.

After much trial and error, a database dump and reimport, and more, I decided to focus on finding the query that was causing the error.

I found this article on how to get more detailed exception logging. Basically we’re modifiying the logException function rather than the “string” version of the error. The code I used from the article didn’t change anything for me, so here’s basically what I did:

 * Write exception to log
 *
 * @param Exception $e
 */
public static function logException(Exception $e)
{
    if (!self::getConfig()) {
        return;
    }

    $file = self::getStoreConfig('dev/log/exception_file');
    //comment out the below line
    //self::log("\n" . $e->__toString(), Zend_Log::ERR, $file);
    //add the below line
    self::log($e, Zend_Log::ERR, $file);
}

This will give you a huge dump of info into the
app/var/log/exception.log file. From there I was actually able to see
the sql querry causing the problem. 

INSERT INTO `catalog_product_index_price_opt_agr_idx` SELECT `i`.`entity_id`, `i`.`customer_group_id`, `i`.`website_id`, `o`.`option_id`, IF((@price:=IF(IF(ops.option_price_id>0, ops.price_type, opd.price_type)='fixed', IF(ops.option_price_id>0, ops.price, opd.price), ROUND(i.price * (IF(ops.option_price_id>0, ops.price, opd.price) / 100), 4))) AND o.is_require, @price,0) AS `min_price`, @price AS `max_price`, IF(i.base_tier IS NOT NULL, IF((@tier_price:=IF(IF(ops.option_price_id>0, ops.price_type, opd.price_type)='fixed', IF(ops.option_price_id>0, ops.price, opd.price), ROUND(i.base_tier * (IF(ops.option_price_id>0, ops.price, opd.price) / 100), 4))) AND o.is_require, @tier_price, 0), NULL) AS `tier_price` FROM `catalog_product_index_price_final_idx` AS `i`
INNER JOIN `core_website` AS `cw` ON cw.website_id = i.website_id
INNER JOIN `core_store_group` AS `csg` ON csg.group_id = cw.default_group_id
INNER JOIN `core_store` AS `cs` ON cs.store_id = csg.default_store_id
INNER JOIN `catalog_product_option` AS `o` ON o.product_id = i.entity_id
INNER JOIN `catalog_product_option_price` AS `opd` ON opd.option_id = o.option_id AND opd.store_id = 0
LEFT JOIN `catalog_product_option_price` AS `ops` ON ops.option_id = opd.option_id AND ops.store_id = cs.store_id

The first thing I did was copy and paste the query into mysql to make
sure I was getting the same result, and sure enough I got the same
Dtable I checked was the duplicate entry error: 

#1062 - Duplicate entry '59-0-1-229' for key 1

I tried deleteing the catalog_product_index_price_opt_agr_idx table
first. Magento rebuilt it back, but with the same error. This is when
I took a closer look at the keys on the table. This is when I see there
is a unique key on fields:

entity_id, customer_group_id, website_id, option_id

That’s when it hits me that the 59-1-1-229, are those field values:

entity_id           59
customer_group_id   0
website_id          1
option_id           229

Then I go and check product #59 for an option 229, but theere isn’t one.
So I go into the catalog_product_option table and delete option 229.
Then I save a product and get no error. Also all tables will now index
properly.

I can only figure that that option must have been deleted at some point,
but never got completely deleted from the options table. A new option
took it’s place, but now was causing a duplicate.

Moral: logging and being able to read logs can help tremendously!

Posted in General | Leave a comment

Read The Code

Posted on September 6, 2011 by jess

Several monts ago, John Nunemaker bloged “Stop Googling“.  It was about reading the code in an open source project oppsed to Googling it.  He was a bit funstrated at someone who Googled, polled friends, and basically searched everywhere besides the source to look for an answer for their problem.

Today I found out the hard way how right he was.  I was trying to access the object info passed along to a delayed_job record.  I googled for 30 or 45 minutes trying to find an answer and couldn’t get any bites.  I knew the handler field had the info I needed, but it was a string.  I also knew that somehow that info had to be extracted when my job was processed.  Finally on a whim, I looked to the source code.  In the second file I opened, I found a method called payload_object.

    def payload_object
      @payload_object ||= deserialize(self['handler'])
    end

I saw object, deserialize, and handler.  That’s all I needed to know.  It was exactly what I wanted.  It took less than a minute. I tried it out and that was it.

That’s when I remembered John’s blog.  Note to self:  if a quick google search doesn’t display the obvious answer…LOOK AT THE CODE!

Posted in General | Leave a comment

Ditch FTP For GIT

Posted on August 4, 2011 by jess

Brown Webdesign Does GitEver since I started learning Ruby On Rails several years ago, I’ve been using git. Git is a cool tool you can use for version control, or in some cases, simple incremental backups. It basically keeps track of your code and file changes, you commit/save the changes and can revert back, review history and do all sorts of cool stuff. It additionally will allow you to setup a remote repository to push changes to. That’s where this article comes in.

Using git in rails is really a must, but in the static world, FTP mostly dominates.

I’m really tired of using FTP. I probably hit Command + Shift + Upload 200 times a day. Sometimes I edit files very quickly and hop from file to file, add images to a images directory and javascript files to the js directory. How can I remember which files to FTP? It can be as simple as “git push” and only my changes are updated.

First off, requirements:

  • web server with shell access
  • web server with git installed
  • local machine with a terminal
  • local machine with git installed
  • local machine to test (if you can’t run your code on your local machine or local server, it doesn’t make much sense to setup git…it would be painful to add, commit, push every tiny change)

Get Started

Server Side

Take care of the server side first. ssh into your web server and find a place outside your public folder. For example, a cpanel server is /home/account_name/public_html. I’d store it in /home/account_name.

mkdir account_name.git && cd account_name.git
git init --bare

Now we’ll tell git where to store the actual files:

git config core.worktree /home/account_name/public_html
git config core.bare false
git config receive.denycurrentbranch ignore

Now we have to tell git what to do after we push changes.  Create and open a new file:

vim hooks/post-receive

In this file place this code:

 #!/bin/sh
 #tell git to copy over the files
 git checkout -f
 #change ownership of files so your web server can serve them (I’m logging in as root so it may not be necessary if you logging in as account_name
 chown -R account_name:account_name /home/account_name/public_html/*

Note: This file (post-receive) needs to be executable. To be sure:

chmod 755 hooks/post-receive

Local Side

Navigate to the directory where your website code lives. If you haven’t already setup git:

git init
git add .
git commit -m ‘initial commit’

Now link up the remote repository:

git remote add origin ssh://user@yourwebserver.com/home/account_name/account_name.git

Now it’s time for the magic:

git push origin master

Your website should now be live. You can now make changes locally, test and then push. Example:

#...make some changes to your code
# test them (this could be a visual QA test or automated test)
git add .
git commit -m ‘my first changes’
git push #no need for the origin master now
Posted in General, Programming, Web Design | 1 Comment

Highlights From LessMoney

Posted on June 20, 2011 by jess

Photo: Adam Wiggall

The past weekend I attended a conference for web development/creative agencies.  The topic was how to make your business better and was taught by some of the best in the industry:  LessEverything.

I’m sure everyone got a little something different out of LessMoney, but I’m going to share the highlights of what I’ve learned. Please feel free to comment or ask questions below.

Marketing

Blogging

First you’ll notice you’re reading this out of my blog which until now has been a boring bit of news about us.  I’ve always been hesitant about writing technical articles on my blog.  My clients don’t care about that stuff right?  Well, it’s almost impossible to market web design to actual clients.  Our clients come from all walks, small local businesses to large corporate businesses and from all different industries.  How can you possibly market to that wide a range…especially as a small firm?  What LessEverything does is market to their peers and well…their competition basically.  Firstly, this establishes themselves as an authority in the industry (they’re well known, respected and liked).  And secondly, our industry is blessed enough to have clients abound that we have to refer out speciality work.  So a design firm may refer work to a development firm and vice verse.  Marketing to your competition still feels backwards to me, but I’m willing to give it a try.  So expect at the least, more insightful technical, award winning, can’t miss articles from this blog :-)

Website

Next is my website.  I’m going to make it better.  I’ve been so busy working on other people’s sites that I’ve neglected my own.  But, all that says is “hey I can afford this guy. (Steven Bristol)”   There were some amazing designers here, like http://turnandface.com/ , http://gauged2.com/, http://www.16toads.com/, just to name a few (I could actually put everyone’s site here).  They explain how much traction they’ve gotten from their sites.  I also want to design my site in a way that will qualify leads.  By making it great, it will naturally discourage away people looking for a cheap site.  But if done in a clever way, a different way, a less boring way, then then ideal clients will get that and others will fly under the radar.

Open Source

Another way to market myself is to create open source projects or design for open source projects.  This is perfect motivation for me to release a project into open source that I use to track my billable time in my company.  I wrote the rails app after needing a new solution for myself and it’s a nice simple tool many others could use, add to, and help me improve my code.  And maybe along the way, new relationships will be created that will lead to new business.

Swagger

I’m a quiet, soft spoken, easy going, humble type of guy.  I have opinions and preferences, but I keep them to myself.  This will need to change.  Clients want to pay for opinions.  They need guidance, ideas, experience and consulting.  Consulting means giving advice, and this is no different in the web industry.  From now on, we’ll display a little swagger!

Success

Our number one goal as consultants, designers, and developers is to help a client define success for a website or web app and provide them with the best path to success with the least risk and least headache. I think we generally do a pretty good job of this, but we could probably do a better job of reiterating it to the client.

Business & Contracts

Use LessAccounting.  Period.  Just kidding.  The joke of the weekend was that LessMoney was really a rouse to get us all to use LessAccouting (LessEverything’s flagship product).

Contracts

Before, I only used contracts for larger projects or on gut feelings.  I’ll now be using a contract for every new project, large or small.  I’ve never had an unhappy client (that I’m aware of), but that day may come and and I want to make sure I’m protected by a contract for what was or wasn’t supposed to be delivered.  They gave us the contract they use and I’ll be using it against my own to modify it for my own needs.

Insurance

I’d never thought of this, but from now on we’ll carry errors and omission insurance to protect the company against the potential of frivolous lawsuits.  I don’t believe we’ll ever have a legitimate lawsuit against us because we’re Godly, honest folks that bend over backwards to please people, but in this country, anyone can sue anyone for anything and it’s expensive to defend yourself, even when there’s absolutely no case against you.

Fees

This is bad news for my clients, but I’m charging way too little for my services compared to others.  We will likely be raising our fees sometime in the near future.  Our quality of work and way too busy schedule is proof that the time has come.  We also haven’t raised our rates since we started over 3 years ago.

Fixed Rate or Hourly?

There was no cut and try answer to this, but one thing clear for me was that I need to move to hourly, or adjust my fixed estimates.  At times I go double over budgets in hours for a project.  Fortunately I can afford to do this because it’s my time instead of having to pay other devs to do it, but it’s not fun, and also adds unnecessary stress takes time away from my family.  If you’re having a hard time estimating, don’t feel alone, nearly everyone there struggles with this.

No content no problem

Myself included, many others cited a problem of getting clients to submit copy/content for the site, which leads to delayed launch, which leads to delayed payment (you get final payment when you launch the site right?).  This has to change.  Some people said they get content up front before starting a project.  Some said they get down payment first, then wait on content to start the project.  Others suggested putting language in the contract that all copy would need to be submitted x number of days (10-30) after the site was ready to launch or there would be a charge for it and they the client was still liable for final payment regardless.  Whether the client is too busy, or is using as a stall tactic, it isn’t fair and should be thought out by the agency ahead of time.

Agency Success

When we first kicked the workshop off, we all (only ~20 total allowed to attend) introduced ourselves, said why we were there and defined success for our business.  It really warmed my heart and inspired me to say that probably 90% + of us said something along the lines of this:  making more money wasn’t really an an objective, but spending more time with family, better quality of life, working on more interesting projects, having less stress, and just learning how to deal with problems was why they were there, and was their goal.  This was really so cool to hear.  As an industry, we all love designing, coding, building, and creating things.  We want to get paid for our hard work to support our families, many are not just in it for the money only.

These highlights were just the tip of the iceberg.  Some of the most value came from the group discussions and relationships established.  I met some super cool people and already have loose plans to meet up with some of them and also already have some leads on some new exciting projects. I highly recommend contacting LessEverything and asking when/if LessEverything Season #2 is going to happen..  It’s well worth the time and money to go!

Posted in General, Web Design | Leave a comment

New Site Launch – EyeCare4Kidz.com

Posted on January 23, 2011 by jess

We’re proud to launch a new website www.eyecare4kidz.com for Dr. Andre Lenior.  This is our 2nd website for Dr. Lenior and we hope he has as much success with this site as he did his first!

Posted in General, Site Launches | Tagged , , , | Leave a comment

New Site Launch – The Hour Glass

Posted on February 10, 2010 by jess

New site launch – thehourglasses.com

thehourglass

Posted in Site Launches | Tagged , , , , | Leave a comment

Interesting to see…

Posted on January 20, 2010 by jess

No one has 100% up time do they?

twitter home page this morning

twitter home page this morning

Posted in General | Tagged , , | Leave a comment

Frazier Cycling Redesign Site Launch

Posted on October 28, 2009 by jess

New Site Launch – www.fraziercycling.com

Tools/Technolgies: xhtml, php, Graphics, Facebook tools, AJAX

Frazier525

Posted in Site Launches | Tagged , , , , | Leave a comment

Harmony Preschool Academy

Posted on October 20, 2009 by jess

New Site Launch – http://www.harmonypreschoolacademy.com

Tools/Technolgies:  Flash, Flash Video, xhtml, php, Graphics, Facebook tools, AJAX

harmonypreschool525

Posted in Site Launches | Tagged , , , , , , , | Leave a comment

Face Book Mentions

Posted on September 15, 2009 by jess

As you may know, Facebook rolled out its planned new feature that lets you “tag” other users in your status updates earlier today.

It’s very similar functionality to @replies on Twitter, and most folks will be pretty familiar with how it works and why you’d want to use it. Still, there are some differences to note, and some may appreciate a basic walkthough of how the new feature works.

Facebook’s version of the feature, like Twitter, also uses the @ symbol to invoke. However instead of having to simply know or remember the person’s username you’d like to tag, Facebook will generate an auto-suggest dropdown based on what you’ve typed after the @ symbol.

This is a nice touch considering that the scope of Facebook’s status tagging feature goes beyond just being able to tag your friends. You can also tag pretty much anything you’re connected to in the Facebook universe: companies, brands, artists and shows — anything that has a Facebook Page. You can also tag events and groups you’re a part of.

Once the item you’re wanting to tag appears in the list, all you have to do is click on it to select it and include a linked version of that entity in your status update.

When your friends see your status update, they’ll be able to click on any of those links to find out more and possibly friend, fan, join or attend whatever you linked to.

Have fun and don’t forget to plug your friends and companies!

Posted in General | Tagged , | Leave a comment