SQL Errors "Integrity constraint violation: 1048 Column 'points' cannot be null:"

To confirm: you were using the development version?

If you refresh your cache (either from the Performance admin page, or the Achievements configuration page), and then try to submit a comment on a new account (i.e., one that doesn't have any achievements), can you reproduce the error? My initial suspicion is that the above error was caused by having an achievement unlock prior to the achievement cache being updated. This would be the case if, say, you had a) defined an achievement, b) looked at the achievement pages [which would create the cache]), c) added new achievements, d) went and unlocked the new achievements, prior to the achievement cache knowing about the other achievements.

The SQL/error you show is, in fact, an error - there are no 'points' defined in the SQL, but all your achievements have points defined. But, theoretically, if no achievement data was loaded (due to the cache being old/invalid, etc.), then this database call shouldn't have happened in the first place.

Need more info, unfortunately, and if it's easily repeatable.

Yeah, there's something definitely wrong - I logged in and viewed http://havoccommunity.net/testsite/user/1/achievements and saw "0 of 0 achievements have been unlocked." which means there's something wrong with your code (or, at least, hook_achievements_info()). Even if your achievements were hidden or still locked, they'd still get counted. Is your custom module of achievements still enabled? Could you paste what you've got in there for hook_achievements_info() again? Can you show me a SQL dump of the various achievements_ tables in your database, and the achievements_info cache in 'cache'?

Awesome. No worries :)

Around line 629 of achievements.module one could check that some points are set:

if (is_null($achievement['points'])) {
watchdog('achievement', 'warning: no points, ignored');
}
else {
db_merge('achievement_totals')
...
}

This won't be necessary as the next/dev version of Achievements will now default undefined point values to 0 during the next cache creation.

Sorry to reopen this issue again, but im having the exact same problem. trillex, can you remember what fixed this problem for you ?

Are you having this problem in the latest version of Achievements (1.4)?

Yes, and I also tried the newest dev version.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'points' cannot be null: INSERT INTO {achievement_totals} (uid, points, unlocks, timestamp, achievement_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( [:db_insert_placeholder_0] => 5 [:db_insert_placeholder_1] => [:db_insert_placeholder_2] => 1 [:db_insert_placeholder_3] => 1323805732 [:db_insert_placeholder_4] => comment-count-1 ) in achievements_unlocked() (line 428 of C:\wamp\www\Sjukrathjalfunin\sites\all\modules\achievements\achievements.module).

Can you post your hook_achievements_info() and/or refresh the achievements cache (in admin/config/people/achievements)?

I always refresh the cache after making changes :)

function hook_achievements_info() {
  $achievements = array(
    'comment-count-1' => array(
      'title'       => t('Posted 1 comment!'),
      'description' => t("We no longer think you're a spam bot. Maybe."),
      'storage'     => 'comment-count',
      'points'      => 50,
    ),
    'comment-count-100' => array(
      'title'       => t('Posted 100 comments!'),
      'description' => t('But what about the children?!'),
      'storage'     => 'comment-count',
      'points'      => 100,
      'images' => array(
        'unlocked'  => '/sites/default/files/example1.png',
      ),
    ),

  
    'article-creation' => array(
      'title' => t('Article creation'),
      'achievements' => array(
        'node-wednesdays' => array(
          'title'       => t('Published some content on a Wednesday'),
          'description' => t("Oh my gosh, you managed to post an article on Wednesday!"),
          'points'      => 5,
          'images' => array(
            'unlocked'  => '/sites/default/files/example1.png',
            'locked'    => '/sites/default/files/example2.png',
            'hidden'    => '/sites/default/files/example3.png',
          ),
        ),
      ),
    ),
  );

  return $achievements;
}

When changing the date of the achievement to give points on a Wednesday i also get this error when creating an article. But not if i change it to some other day (well maybe it goes without saying :) i can post again)

Error when creating an article (same one):

PDOException: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'points' at row 1: UPDATE {achievement_totals} SET points=points + :points, unlocks=unlocks + :increment, timestamp=:db_update_placeholder_0, achievement_id=:db_update_placeholder_1 WHERE ( (uid = :db_condition_placeholder_0) ); Array ( [:points] => [:increment] => 1 [:db_update_placeholder_0] => 1323855937 [:db_update_placeholder_1] => node-wednesdays [:db_condition_placeholder_0] => 1 ) in achievements_unlocked() (line 428 of C:\wamp\www\Sjukrathjalfunin\sites\all\modules\achievements\achievements.module).

I'm curious: does the user you're testing with (in the above example, user 1) have an EXISTING record in achievement_totals when you get this error? From my side, your hook_achievements_info() looks fine, and the SQL itself looks fine, but the lack of a :points value is mystifying. Care to do some debugging inside achievements_unlocked by just dumping out various variables?

I'm getting the same error, trying to trouble shoot ... I download the dev version of the module, clear all the caches, create my own module and call it in this fashion.

function mymodulet_node_insert($node) {

  if ($node->type == 'mytype')
  {
      drupal_set_message('Node Has Been Inserted With Type'.$node->type);
      achievements_unlocked('comment-count-50', $node->uid);
  }
}

I know that this should get called every time, just for testing, but whenever I add the node I get the following error;

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'points' cannot be null: INSERT INTO {achievement_totals} (uid, points, unlocks, timestamp, achievement_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( [:db_insert_placeholder_0] => 1 [:db_insert_placeholder_1] => [:db_insert_placeholder_2] => 1 [:db_insert_placeholder_3] => 1327078532 [:db_insert_placeholder_4] => comment-count-50 ) in achievements_unlocked() (line 428 of /Users/me/Documents/mydrupalsite/sites/all/modules/achievements/achievements.module

Maybe there is something more useful I can do to trace back the error?

I ended up changing the points column in the database from null = no to null = yes and it seemed to fix the problem, I really don't know what that did, but it all seems to be working ok once I did it.

FYI, I also got an
'Integrity constraint violation: 1048 Column 'points' cannot be null: INSERT INTO {achievement_totals} (uid, points, unlocks, timestamp, achievement_id)'
and the reason was that I was calling
achievements_unlocked('myachievement', $uid);
where myachievement was not a valid entry in the achievements array return from _achievements_info()

Improvement suggestion:
It would be nice if this was more robust, failing with an error to the logs, but not dying with a massive SQL error, if the achievement name, or uid is not valid.

I'm marking this as fixed (hopefully) based on a recent commit. @boran in 20 was on the right track - I was finally able to duplicate the SQL error by passing a non-existent achievement ID. This also seems evident in comment 15 by @olitomas: the SQL error he pasted indicates an attempted unlock on 'node-wednesdays', which doesn't exist in the achievements_info he pasted. The revised code switches out an isset() with an empty() and issues a watchdog error if the achievement ID doesn't exist. It shouldn't SQL error anymore.