ExpressionEngine CMS
Open, Free, Amazing

Thread

This is an archived forum and the content is probably no longer relevant, but is provided here for posterity.

The active forums are here.

New CI 1.7 session class database error

October 25, 2008 10:09pm

Subscribe [6]
  • #1 / Oct 25, 2008 10:09pm

    CIfan1000

    37 posts

    Hi,
    I started using the new CI 1.7 session class with database based session variable storage, and got this error:

    A Database Error Occurred

    Error Number: 1364

    Field ‘user_data’ doesn’t have a default value

    INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES (‘5aa4c19ec6e1b86e0401e23f3dc27508’, ‘127.0.0.1’, ‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv’, 1224982938)

    I am not an experienced user/programmer but I think this is happening because the SQL query does not insert anything into the user_data field of the ci_sessions table. I looked at the design of this table and this field is set to NOT NULL so maybe it is expecting a value during an INSERT query.

    Please please help. Thank you for your time and effort in advance.

  • #2 / Oct 25, 2008 10:19pm

    Randy Casburn

    997 posts

    Hi CIfan1000,

    You’re very intuitive! You’ve interpreted this error correctly.

    Are you trying to store data in the session?  If so, can you share the code with us that you’re using to do that?  That would be helpful in isolating what might be going on.

    Thanks,

    Randy

  • #3 / Oct 25, 2008 10:32pm

    dmiden

    48 posts

    Simply put the field to NULL and it should not give you any error :x

  • #4 / Oct 25, 2008 10:35pm

    CIfan1000

    37 posts

    Hi,
    Thanks for your quick response.

    To answer your question, I am storing session data. I get this error in a login controller I have developed, and the code below handles the case if the validation was ok or not…...

    if ($this->form_validation->run() == FALSE)  // If login failed (is FALSE)
            {
    
                // ---------------------------------------------------- Load login view
                // Load first part of template:
                $data['title'] = "User login";
                $this->load->view('templates/template01part1',$data);
    
                // Load menu:
                
                // Load second part of template:
                $this->load->view('templates/template01part2',$data);
    
                // Load body:
                $this->load->view('login/login');
                
                // Load third part of template:
                $this->load->view('templates/template01part3',$data);
    
            }
            else // If login passed
            {
    
                // --------------------------------------------------------------------
                //Get the username from the post of the form:
                $UserName = $_POST['LoginUsername'];
    
    
                // Store the username as session variable called UserName so that it can be used in other controllers:
                $this->session->set_userdata('UserName', $UserName);
    
    
                // --------------------------------------------------------------------
                // Store the user's authentication status as Registered
                $this->session->set_userdata('UserStatus', 'Registered');
    
    
                // --------------------------------------------------------------------
                // Store the userid as a session variable so that it can be used in other controllers
    
                // Get the user id:
                $query = $this->db->query("SELECT UserId FROM users WHERE UserName='{$UserName}' ");
    
                // If there is at least one
                if ($query->num_rows() > 0)
                    {
                        foreach ($query->result() as $row)
                            {
                                    $this->session->set_userdata('UserId', $row->UserId);
                            }
                    }
    
    
                // ---------------------------------------------------- Load login success view
                // Load first part of template:
                $data['title'] = "Login successful!";
                $this->load->view('templates/template01part1',$data);
    
                // Menu goes here:
                
                // Load second part of template:
                $this->load->view('templates/template01part2',$data);
    
                // Load body:
                $this->load->view('login/loginsuccess');
    
                // Load third part of template:
                $this->load->view('templates/template01part3',$data);
    
            }

    I fear my code may be amateurish…............................

    Thanks again for any help! (Its bedtime here so I may not respond again till the morning…...)

  • #5 / Oct 25, 2008 10:39pm

    CIfan1000

    37 posts

    Thanks dmiden,

    I think that is a good suggestion (to make the field NULL) and I thought of that as well.

    But then I became concerned that if I am trying to store session variables, why is the SQL query not trying to insert anything into the user_data field? I would imagine that custom session data would be stored in that field?

  • #6 / Oct 25, 2008 11:35pm

    CIfan1000

    37 posts

    Just some additional info:

    When I set:

    $config[‘sess_use_database’]  = FALSE;

    in config.php,

    my code works fine and I can see the custom session variables buried in the value of the cookie:

    a:7:{s:10:"session_id";s:32:"276573a61533e38a3111f11dcf46e876";s:10:"ip_address";s:9:"127.0.0.1";s:10:"user_agent";s:50:"Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US;+rv";s:13:"last_activity";s:10:"1224988329";s:8:"UserName";s:7:"ciuser1";s:10:"UserStatus";s:10:"Registered";s:6:"UserId";s:1:"1";}c83f8beafb0327d84fccf924698dbea8

  • #7 / Oct 25, 2008 11:48pm

    Randy Casburn

    997 posts

    I fear my code may be amateurish..

    Right!  I was just going to say…

    Your code looks fine, and I want to especially thank you for putting out the effort to try.  You’re doing very well.

    But… I can’t duplicate your error.

    Let’s make sure this the data type for the user_data column is, in fact, TEXT type.  Then it will be helpful to know which version of MySQL you’re using.

    As a side note, you cannot set a default value in your table definition for TEXT datatype.

    Randy

  • #8 / Oct 25, 2008 11:49pm

    dmiden

    48 posts

    Using the database your custom session data can have a much larger size.
    Therefore I recommend using the database.

    Setting the table to NULL will fix your problem 😊.
    I think the error is occurring the session is initialized and setting the user data.
    As default, no custom user data is being set at that point and thus the sql generates an error.

  • #9 / Oct 25, 2008 11:59pm

    Randy Casburn

    997 posts

    What I don’t understand is why it works for me (and likely everyone else) the way it is specified in the documentation.  i.e. TEXT data type NOT NULL

    That’s puzzling.

    Randy

  • #10 / Oct 26, 2008 12:02am

    dmiden

    48 posts

    It didnt work for me.
    Are you using custom data and the database?

  • #11 / Oct 26, 2008 12:11am

    Randy Casburn

    997 posts

    Do you mean using sessions with a table created with the CREATE TABLE statement from this page:

    http://ellislab.com/codeigniter/user-guide/libraries/sessions.html

    does not work?  Or something else didn’t work?

    Randy

  • #12 / Oct 26, 2008 12:16am

    dmiden

    48 posts

    It didn’t work using the field set to ‘NOT NULL’.

    Yes, I mean using a database to store the default session data and also custom user data (user_data field).

  • #13 / Oct 26, 2008 12:17am

    CIfan1000

    37 posts

    Hi Randy,

    I did confirm in the table that the user_data field is TEXT.

    I just copied the table definition from the CI session documentation and ran it in MySQL:

    CREATE TABLE IF NOT EXISTS `ci_sessions` (
    session_id varchar(40) DEFAULT ‘0’ NOT NULL,
    ip_address varchar(16) DEFAULT ‘0’ NOT NULL,
    user_agent varchar(50) NOT NULL,
    last_activity int(10) unsigned DEFAULT 0 NOT NULL,
    user_data text NOT NULL,
    PRIMARY KEY (session_id)
    );

    I have attached a screencap of my MySQL Table Editor - Please take a look - you’ll notice that the Default value of the user_data field is NULL - is this ok?

    MySQL is version   5.0.51a

  • #14 / Oct 26, 2008 12:20am

    dmiden

    48 posts

    CIFan1000, there’s no problem having set the field to NULL 😉

  • #15 / Oct 26, 2008 12:22am

    CIfan1000

    37 posts

    Dear user dmiden - With all due respect could I ask you not to post further in this please?

    I am trying to understand Randy’s posts and you posts are confusing me.

.(JavaScript must be enabled to view this email address)

ExpressionEngine News!

#eecms, #events, #releases