"subtype" has no default value error message (SMF 1.1.3, TP 0983)

[Wivelrod]

sorry. more info that might help someone get me a resolution (can never have enough eh?)

line 404 is:

db_query("INSERT INTO {$tp_prefix}variables (value1,value2,value3,type,value4,value5) VALUES('$catn','$catp','','category','',0)", __FILE__, __LINE__);

Table variables is defined as the following when scripting the database via portal admin:

#
# Table structure for table `smf_tp_variables`
#

DROP TABLE IF EXISTS `smf_tp_variables`;

CREATE TABLE `smf_tp_variables` (
  id int(11) NOT NULL auto_increment,
  value1 text NOT NULL default '',
  value2 text NOT NULL default '',
  value3 text NOT NULL default '',
  type tinytext NOT NULL default '',
  value4 text NOT NULL default '',
  value5 int(11) NOT NULL default -2,
  subtype tinytext NOT NULL default '',
  value7 text NOT NULL default '',
  value8 text NOT NULL default '',
  PRIMARY KEY (id)
) TYPE=MyISAM;

#
# Dumping data in `smf_tp_variables`
#

INSERT INTO `smf_tp_variables`
   (`id`, `value1`, `value2`, `value3`, `type`, `value4`, `value5`, `subtype`, `value7`, `value8`)
VALUES (1, 'Portal features', 0, '', 'category', '', -2, '', '', ''),
   (3, 'Demo articles', 0, 'cats1', 'menubox', 0, 10, '', '', '');
# --------------------------------------------------------

# Done

[Wivelrod]

This seems to be a compatibility bug in TP and the platform my chosen web host chooses to run. I can't change host, and I can't make my host run a different version of mySql.

I've therefore hacked the TP php page to allow me to add TP articles and categories.

The problem seems to lie in that inserting data into a mySQL Table that has columns that disallow nulls, but have defaults, mySQL will throw an error stating there is no default value for those columns if they haven't been provided in the INSERT statement (despite there being defaults in the table scripts). I've therefore had to change the php file so that INSERTS into tables now include the NOT NULL columns and the default value.

My changed TPortalAdmin.php is attached.

Please, investigate this.

[splattercaster]

Hi,

Sorry to bump this thread but I have exactly the same issue as Wivelrod.
I noticed the problem when I first installed SMF/TP and followed all the advice in this forum for rectifying the error, no fixes worked. I reinstalled the site many times always with the same results.

I have now been using SMF/TP for a couple of months and I have managed to work around using articles by utilising the centre blocks. However this is seriously limiting the full potential of TP and I would like to use articles.

Has any progress been made to discover the problem with TP and SQL ?
Is there a version compatibility problem ? (I remember seeing a post where Bloc admits there might be an issue with MySQL 5, but I can't find it now)
Is there a fix for TPortalAdmin.php ? Wivelrod's fix didn't work for me.

My versions: (yes I have recently upgraded to 1.1.4 and followed the fixes described by G6 again)
SMF 1.1.4
TP_0983
PHP Version 5.2.3
MySQL 5.0.45

Error msg when I add a category:
Database Error: Field 'subtype' doesn't have a default value
File: C:\******\Sources\TPortalAdmin.php
Line: 404

Error msg when I add an article:
Database Error: Incorrect integer value: '' for column 'category' at row 1
File: C:\******\Sources\TPortalAdmin.php
Line: 463

Regards & Thanks,
Tim

[G6Cad]

These errors should be fixed after a DB upgrade for both SMF and then TP
So start with updating SMF database and then update TP database tables with the file from the download section

[splattercaster]

OK I've tried to do this again and now I get a problem with the smileys table (I already knew there was a problem with this table as I can't add smiley groups and have to use the default).
I know it's not your problem and I'll have to post this in SMF forum but here it is anyway, just incase you know what the fix is, I'm stuck in maintenance mode now and can't get to admin login screen:

Updating Settings.php... Successful.
Updating and creating indexes...
    Updating indexes on "messages"... done.
    Updating table indexes... done.
    Reordering boards and categories... done.
    Updating indexes and data on "smileys"... Unsuccessful!
This query:

    ALTER TABLE smfukdk_smileys
    ORDER BY LENGTH(code) DESC;

Caused the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LENGTH(code) DESC' at line 2


off to SMF I go bbs o/

[G6Cad]

These database tables is SMF tables, you have to ask over at SMF forums for help on your error there.

[splattercaster]

Yep I know, I found a fix there, had to modify source files and upgrade sql file http://www.simplemachines.org/community/index.php?topic=178857.0

OK I did the upgrade for SMF (successfully this time) and for TP as instructed many times before on this forum and performed many times by myself

    * Upshrink table exists
    * Settings table exists
          o Updated version to 098
    * Blocks table exists
          o Changed pos in blocks table
          o Changed pos in blocks table
    * Variables table exists
          o Changed value5 in variables table
    * Articles table exists
          o Updated body in articles table
    * DL manager table exists
          o Updated file counts in DL Manager table
    * Shoutbox table exists
    * UTF-8 setting is found.
          o Converted articles table for utf-8
          o Converted settings table for utf-8
          o Converted blocks table for utf-8
          o Converted variables table for utf-8
          o Converted dlmanager table for utf-8

TinyPortal´s table structure is now installed/updated.

The error message stating databases are the wrong version has now gone but the other errors remain:

When adding an article in no category:
Incorrect integer value: '' for column 'category' at row 1
File: C:\Webroot_UKDK\Sources\TPortalAdmin.php
Line: 463

When adding a new category:
Field 'subtype' doesn't have a default value
File: C:\Webroot_UKDK\Sources\TPortalAdmin.php
Line: 404

What is strange now after the upgrades, if I list the Categories I can see the test category I did before the upgrades (which I couldn't see before). I can add articles to that one but I can't create new categories.

It looks like I'm getting somewhere, any ideas ?? (I'm looking forward to using articles, i r excited hehe)

cheers for help.

[G6Cad]

The only think i can think of is that it have some thing to do with the utf8 langauge settings.
I will have to ask you to wait for Bloc to give you help with this cause I dont know where to look from here.
I know that an upgrade of the SMF tables usually sort this error out, but it seems like somethings still buggering up.

[splattercaster]

Hmm interesting you should say that because I have another problem now - the TP admin pages are blank, white with nothing on, I've been searching forum for similar errors and they all seem to specify language files as the problem.

I'm just trying to fix that now - maybe it will kill 2 birds with 1 stone.

Any instructions for fixing language? I have uploaded all the TP files again and the language files but problem remains. I'll try the SMF core language files again..

Cheers.

[IchBin]

The TP admin pages are blank? The whole page? Or just the block with the links to each section in the TP admin?