Opened 10 years ago

Closed 10 years ago

#6811 closed defect (fixed)

Invalid default value for BLOB field in MySQL CREATE statement for mythweb_sessions table

Reported by: Jonathan Martens <jonathan@…> Owned by: Rob Smith
Priority: trivial Milestone: unknown
Component: Plugin - MythWeb Version: head
Severity: low Keywords:
Cc: Ticket locked: no

Description

Like issue #6802, there is also a invalid default value specified in the create statement of the mythweb_sessions table in the MythWeb plugin.

According to http://dev.mysql.com/doc/refman/5.1/en/blob.html:

"In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

  • For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.4.2, “Column Indexes”.
  • BLOB and TEXT columns cannot have DEFAULT values."

Please find attached a patch to fix this.

Attachments (2)

mythplugins-mythweb-includes-db_update_php.patch (723 bytes) - added by Jonathan Martens <jonathan@…> 10 years ago.
mythplugins-mythmusic-mythweb-includes-db_update_php.patch (732 bytes) - added by jonathan@… 10 years ago.
Oops, the patch removed the NOT NULL specifier which is allowed, this is an updated patch

Download all attachments as: .zip

Change History (7)

Changed 10 years ago by Jonathan Martens <jonathan@…>

Changed 10 years ago by jonathan@…

Oops, the patch removed the NOT NULL specifier which is allowed, this is an updated patch

comment:1 Changed 10 years ago by anonymous

Oops, the patch removed the NOT NULL specifier which is allowed, this is an updated patch

Are you planning also to audit main MythTV DB? If you want to see real fireworks, turn on MySQL mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) and see what happens. There are many cases of "NOT NULL" in the myth schema that are not respected in code (frequently dates and text fields), but by default MySQL allows it anyway. mythfilldatabase, manual recordings, and channel scanner are just a few things that break if you activate strict mode.

comment:2 in reply to:  1 Changed 10 years ago by anonymous

Replying to anonymous:

Are you planning also to audit main MythTV DB? If you want to see real fireworks, turn on MySQL mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) and see what happens. There are many cases of "NOT NULL" in the myth schema that are not respected in code (frequently dates and text fields), but by default MySQL allows it anyway. mythfilldatabase, manual recordings, and channel scanner are just a few things that break if you activate strict mode.

No, not for the moment. The fact whether a column is specified a default value of NOT NULL is not really an issue here. I only reported show stoppers I found in trying to compile and run MythTV on Windows. Specifying default values for column types (TEXT, BLOB) that do _not_ support default values which makes queries fail and hence database updates fail. Specifying whether or not columns are allowed to hold a NULL value by default will not break upgrades AFAICT.

In this case MythWeb won't show the interface as the creation of the mythweb_sessions table is not created.

comment:3 Changed 10 years ago by danielk

Milestone: 0.22unknown
Priority: majortrivial
Severity: mediumlow

comment:4 Changed 10 years ago by Rob Smith

Owner: changed from xris to Rob Smith
Status: newaccepted

comment:5 Changed 10 years ago by Rob Smith

Resolution: fixed
Status: acceptedclosed

(In [21794]) Fixes #6811, this removes the DEFAULT from the mythweb session table, as it breaks on strict database modes

Note: See TracTickets for help on using tickets.