Participation table not allowing Null values for Rating and Club

[nixe-]

When not using SFL and PDGA integration players/users adding a player to tournament causes error because Participation table is not allowing Null values for Rating and Club fields.

[nixe-]

Cause   
InternalDescription Failed SQL query: INSERT INTO kkn_Participation (Player, Event, Classification, Club, Rating) VALUES (1, 66, 1, NULL, NULL) Column 'Club' cannot be null
Function    
Backtrace   
Array

(
   [0] => Array
       (
           [file] => /var/www/clients/client4/web21/web/kisakone/core/error.php
           [line] => 156
           [function] => Error
           [class] => Error
           [object] => Error Object
               (
                   [title] => error_db_query
                   [description] => Tietokantakysely epäonnistui.
                   [cause] => 
                   [internalDescription] => Failed SQL query:

INSERT INTO kkn_Participation (Player, Event, Classification, Club, Rating)
                           VALUES (1, 66, 1, NULL, NULL)

Column 'Club' cannot be null
                   [function] => 
                   [isMajor] => 1
                   [errorPage] => error
                   [errorCode] => 500
                   [data] => 
                   [url] => 
                   [backtrace] => Array
*RECURSION*
               )

           [type] => ->
           [args] => Array
               (
               )
       )

   [1] => Array
       (
           [file] => /var/www/clients/client4/web21/web/kisakone/data/player.php
           [line] => 113
           [function] => Query
           [class] => Error
           [type] => ::
           [args] => Array
               (
                   [0] => INSERT INTO kkn_Participation (Player, Event, Classification, Club, Rating)
                           VALUES (1, 66, 1, NULL, NULL)
               )
       )

   [2] => Array
       (
           [file] => /var/www/clients/client4/web21/web/kisakone/core/event_management.php
           [line] => 79
           [function] => SetPlayerParticipation
           [args] => Array
               (
                   [0] => 1
                   [1] => 66
                   [2] => 1
                   [3] => 1
               )
       )

   [3] => Array
       (
           [file] => /var/www/clients/client4/web21/web/kisakone/inputhandlers/add_competitor.php
           [line] => 72
           [function] => SignUpUser
           [args] => Array
               (
                   [0] => 66
                   [1] => 2
                   [2] => 1
                   [3] => 1
               )
       )

   [4] => Array
       (
           [file] => /var/www/clients/client4/web21/web/kisakone/core/inputmapping.php
           [line] => 54
           [function] => processForm
           [args] => Array
               (
               )
       )

   [5] => Array
       (
           [file] => /var/www/clients/client4/web21/web/kisakone/index.php
           [line] => 117
           [function] => gate_ProcessInputData
           [args] => Array
               (
               )
       )
)

[tuminoid]

Which version you are using?

Tables are defined to allow null:

    Club INT,
    Rating INT,

[tuminoid]

Please attach describe kkn_Participation too from mysql prompt.

[nixe-]

It's latest version, that's upgraded. I've already fixed this problem to my own installation. It's possible that I've missed update, that changes this, but in my install Null value wasn't allowed for those fields, my bad. This is my working Participation table now.

mysql> describe kkn_Participation;
+------------------+-------------+------+-----+-------------------+----------------+
| Field            | Type        | Null | Key | Default           | Extra          |
+------------------+-------------+------+-----+-------------------+----------------+
| id               | int(11)     | NO   | PRI | NULL              | auto_increment |
| Player           | smallint(6) | NO   | MUL | NULL              |                |
| Event            | int(11)     | NO   | MUL | NULL              |                |
| Classification   | int(11)     | NO   | MUL | NULL              |                |
| Approved         | tinyint(4)  | NO   |     | NULL              |                |
| EventFeePaid     | datetime    | YES  |     | NULL              |                |
| OverallResult    | smallint(6) | YES  |     | NULL              |                |
| Standing         | smallint(6) | YES  |     | NULL              |                |
| DidNotFinish     | tinyint(4)  | NO   |     | NULL              |                |
| SignupTimestamp  | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| TournamentPoints | int(11)     | YES  |     | NULL              |                |
| Rating           | int(11)     | YES  |     | NULL              |                |
| Official         | int(11)     | NO   |     | 0                 |                |
| Club             | varchar(60) | YES  |     | NULL              |                |
+------------------+-------------+------+-----+-------------------+----------------+
14 rows in set (0.00 sec)

[tuminoid]

It seems you have custom modifications made to your database and possibly conflicting Club field.

Table is supposed to look like this:

describe kisakone_Participation;
+------------------+-------------+------+-----+-------------------+----------------+
| Field            | Type        | Null | Key | Default           | Extra          |
+------------------+-------------+------+-----+-------------------+----------------+
| id               | int(11)     | NO   | PRI | NULL              | auto_increment |
| Player           | smallint(6) | NO   | MUL | NULL              |                |
| Event            | int(11)     | NO   | MUL | NULL              |                |
| Classification   | int(11)     | NO   | MUL | NULL              |                |
| Approved         | tinyint(4)  | NO   |     | NULL              |                |
| EventFeePaid     | datetime    | YES  |     | NULL              |                |
| OverallResult    | smallint(6) | YES  |     | NULL              |                |
| Standing         | smallint(6) | YES  |     | NULL              |                |
| DidNotFinish     | tinyint(4)  | NO   |     | NULL              |                |
| SignupTimestamp  | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| TournamentPoints | int(11)     | YES  |     | NULL              |                |
| Club             | int(11)     | YES  | MUL | NULL              |                |
| Rating           | int(11)     | YES  |     | NULL              |                |
+------------------+-------------+------+-----+-------------------+----------------+
13 rows in set (0.01 sec)

There is no Official in Participation table, and Club is Int, not varchar(60).

[nixe-]

It's originally SFL kisakone. Codebase have been changed to your version in some point, but database is manually "fixed" (multiple times). I think I need to check all tables and change it to correct format, because it's not first time I have same kind of issues.

[tuminoid]

It is very much advised, if you intend to run vanilla version. Saves you a lot of trouble down the road. Fun fact, even official installation was behind the clean version as recently as a month ago :-)

I added foreign key constraints to 2013.09.20 upgrade script (as it is the first upgrade script if you upgrade from unversioned kisakone), it might be worthwhile to check those too.