This is an archived copy of the forum for reference purposes

Dupilcate records for an AUTO_INCREMENT...DB too slow? PHP too fast? How is this possible?

posted by audvare 8 years ago

On every form generation, I store some information about the form. The form then receives a build ID so I can retrieve any state data (such as if an error occurs) again and again.

The table is like this:

DROP TABLE IF EXISTS form_states;
CREATE TABLE form_states (
  bid INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  form_id VARCHAR(255) NOT NULL DEFAULT '',
  data MEDIUMTEXT NOT NULL,
  created INT NOT NULL DEFAULT 0,
  expired INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX bid_form_id ON form_states (bid, form_id);

      try {
        $formState = new Base_FormState;
        $formState->setFormId($this->getFormId());
        $formState->setCreated(time());
        $formState->setData(!is_null($oldState) ? serialize($oldState) : serialize($this));
        $formState->store();
        
        // Add Build ID
        $this->createValues(array('build_id' => $formState->getBid()));
      }
      catch (fValidationException $e) {
      }

What's weird is that if I use:

CREATE UNIQUE INDEX bid_form_id ON form_states (bid, form_id);

#!text/html
The following problems were found: Bid, Form ID: The values specified must be a unique combination, however the specified combination already exists

On a page where 6 forms with the same form_id (same form but different parameters), every other form has no build_id (2,4,6) and for each I get an fValidation exception. I don't understand how this can happen when there's no real threading in PHP, I thought the fORM was performing transactions and/or locking the table anyway, and I don't specify the build ID in creating the form state anywhere.

Only 2 functions call the above code: one when the form is about to be rendered, and one after the form has been processed in order to update the state (but store a new record and get a new build ID).

For now I've decided on not having the UNIQUE INDEX. But this was working before, so I'm not sure what changed.

On another note, let's write fMollom or something to stop this spam!

http://mollom.com/

I am already working on such a thing.

posted by audvare 8 years ago

I apologize that some spam gets through on the forum. The site uses both Akismet and a heuristic engine built into Trac to reduce the spam by an extremely large percentage. Unfortunately sometimes real users get their posts marked as spam because the content contains code and words that can often be associated with spam. Because of this, having signed up a for a real user account adds a bit of weight to any given post.

The best solution for now is probably to add recaptcha to the signup process.

posted by wbond 8 years ago

Can you post the code for the method ->createValues()?

Also, can you provide the backtrace for the exception that is being thrown?

posted by wbond 8 years ago

Part of a much larger class based on DOM that basically creates a big data structure that eventually gets translated to HTML or XML (because I hate writing HTML).

  /**
   * Create hidden form value fields.
   * @param array $values Key->Value pairs.
   * @return Base_HtmlTag The element that this element was added to.
   */
  public function createValues($values = array()) {
    foreach ($values as $name => $value) {
      $this->createElement('input', array('name' => $name, 'value' => $value, 'type' => 'hidden'));
    }
    
    return $this;
  }

This basically outputs the following within the form element:

#!text/html
<input type="hidden" name="name_given" value="value_given">

This backtrace is from me requesting /politics on my site. A list of polls that are forms will be listed. In #1, the object referenced is an fActiveRecord object. #0 stores the fActiveRecord I mentioned before and has the code above. In this run, ->storeState() is the ONLY place where the Base_FormState fActiveRecord object will be stored with the code above.

#!text/html
#0  Base_HtmlTag->storeState() called at [/var/www/s/Module/Poll/PollForm.php:58]
#1  Module_Poll_PollForm::create(Module_Poll_Poll Object ([] => Array (),[] => Array (),[] => Array (),[] => Array ([pid] => 6,[uid] => 1,[question] => Will the uprising in Egypt spread across the Middle East?,[answer_1] => YES - and there's a storm coming for the West!,[answer_2] => YES - but it'll be good for everyone,[answer_3] => NO - it won't spread beyond Egypt,[answer_4] => NO - nothing will happen even in Egypt,[category_id] => 6,[created] => 1300937380,[status] => 1,[tags] => a:5:{i:0;i:8;i:1;i:9;i:2;i:10;i:3;i:11;i:4;i:12;})), ) called at [/var/www/s/Module/Poll/PollCategoryPage.php:76]
#2  Module_Poll_PollCategoryPage->renderForm() called at [/var/www/s/Module/Poll/PollCategoryPage.php:103]
#3  Module_Poll_PollCategoryPage->render() called at [/var/www/s/Base/Page/FormPage.php:24]
#4  Base_Page_FormPage->_render() called at [/var/www/s/Base/Router.php:325]
#5  Base_Router::getRequest(politics, , ) called at [/var/www/s/Base/Router.php:390]
#6  Base_Router::route() called at [/var/www/s/Base/Sutra.php:87]
#7  S::render() called at [/var/www/s/index.php:4]

posted by audvare 8 years ago

The backtrace doesn't seem to be from fActiveRecord.

You mention you are storing 6 records in a loop and every other one doesn't have an ID. It seems there is something going on with the database. If you execute the following before you start storing the fActiveRecord objects you will get a kinda-noisy debug of all SQL statements being executed.

fORMDatabase::retrieve()->enableDebugging(TRUE);

You can turn it off after the loop by calling:

fORMDatabase::retrieve()->enableDebugging(FALSE);

I bet if you look through the SQL statements they can help you determine what is causing these issues.

posted by wbond 8 years ago