Welcome Guest, Not a member yet? Register   Sign In
CI 1.7.3 databased backed sessions (`user_data` text NULL)
#1

[eluser]blasto333[/eluser]
A user is reporting an error with the default CI sessions table as described in the manual of:

Code:
CREATE TABLE `phppos_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '0',
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT 0,
  `user_data` text NOT NULL DEFAULT '',
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


A Database Error Occurred
Error Number: 1364

Field 'user_data' doesn't have a default value

INSERT INTO `phppos_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('8efeb8a2708af2ea799584ff50aa3c2f', '127.0.0.1', 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) Ap', 1302002136)
--


Having them use:

Code:
CREATE TABLE `phppos_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '0',
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT 0,
  `user_data` text NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Seems to fix the problem. Is there anything wrong with having them have NULL data for user_data. (It seems to fix their problem)
#2

[eluser]InsiteFX[/eluser]
It's an error in the User Guide Documentation!
Code:
CREATE TABLE `phppos_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '0',
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT 0,
  `user_data` text,
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MySQL text and blob cannot have a NOT NULL setting!
InsiteFX
#3

[eluser]Greg Aker[/eluser]
Yep, a bug in the docs. I fixed it today in the HG repo. Additionally, in the repo, I bumped up the length of user_agent to VARCHAR(120), and provided SQL to toss an index on last_activity.

-greg
#4

[eluser]blasto333[/eluser]
Shouldn't user_data allow null values so the error I described in my original post doesn't happen? (MySQL Strict Mode enabled)

Field ‘user_data’ doesn’t have a default value

I looked at commit: https://bitbucket.org/ellislab/codeignit...2da06eb360
#5

[eluser]InsiteFX[/eluser]
It's a MySQL Thing! Text fields cannot be assigned a NULL VALUE.
#6

[eluser]blasto333[/eluser]
Sure they can (I just did it on my mysql 5.1.54 install.


mysql> describe phppos_sessions
-> ;
+---------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| session_id | varchar(40) | NO | PRI | 0 | |
| ip_address | varchar(16) | NO | | 0 | |
| user_agent | varchar(50) | NO | | NULL | |
| last_activity | int(10) unsigned | NO | | 0 | |
| user_data | text | YES | | NULL | |
+---------------+------------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> insert into phppos_sessions (session_id, ip_address, user_agent, last_activity,user_data) VALUES ('123','456','789',10,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM phppos_sessions;
+----------------------------------+------------+----------------------------------------------------+---------------+--------------------------------+
| session_id | ip_address | user_agent | last_activity | user_data |
+----------------------------------+------------+----------------------------------------------------+---------------+--------------------------------+
| 123 | 456 | 789 | 10 | NULL |
| 4bd88e70ea9ebea3c6618a6c56527679 | 0.0.0.0 | Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_6; | 1303349644 | a:1:{s:9:"person_id";s:1:"1";} |
+----------------------------------+------------+----------------------------------------------------+---------------+--------------------------------+
2 rows in set (0.00 sec)




Theme © iAndrew 2016 - Forum software by © MyBB