TP-Docs
HTML5 Icon HTML5 Icon HTML5 Icon
TP on Social Media

Recent

Welcome to TinyPortal. Please login or sign up.

December 03, 2023, 07:30:52 AM

Login with username, password and session length
Members
Stats
  • Total Posts: 194,609
  • Total Topics: 21,189
  • Online today: 70
  • Online ever: 3,540
  • (September 03, 2022, 01:38:54 AM)
Users Online
Users: 0
Guests: 37
Total: 37

All parts of a PRIMARY KEY must be NOT NULL

Started by tty456, November 24, 2022, 10:35:35 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

tty456

Link to my site: (not publically accessible)
SMF version: 2.1.3 (previously 2.1 RC2)
TP version: 2.2.2 (previously 1.6.3)
Default Forum Language: English
Theme name and version: Default Curve2
Browser Name and Version: Multiple, FF,Waterfox, Chromium etc
Mods installed: Tiny Portal + SMF Gallery pro
Related Error messages: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
File: /home/wealdencaving.org.uk/public_html/forum2/smf/Packages/temp/install.php
Line: 354

Hi, I'm working on a backup copy of our clubs site testing the upgrade of both SMF and TinyPortal. I have tried un-installing TP before the SMF upgrade or upgrading TP before the upgrade, but have found that either way after upgrade of SMF I can't re-install TP without getting the SQL error above.

The new server (Ubuntu) hosting tests above is MySQL 8.0.29, old one was 5.7.40

I have tried setting sql_mode to "" as test but no change in error

After restoring site to test server SMF/TP/Gallery all work fine, I just can't upgrade without breaking TP.

Thanks

tino

I don't see where we set the primary key to null, can you paste a copy of your database structure for the tp_ tables? I don't need the data just the structure.

tty456

Thanks for looking at this, dump attached for the smf_tp_* tables, if thats not what you need let me know.

tty456

Just tried another test, result might be of use or not?

restored another copy of live site to another VM this time with same SQL/php versions as live
used SMF large upgrade archive 2.1.2 NOT 2.1.3
SMF upgrades ok with no errors
click to re-install mod TP 1.6.3, completes fine with no errors.
Site seems to work fine
uploaded TP 2.2.2
package menu offers an upgrade option for 2.2.2 (thats new!?)
click upgrade 2.2.2, completes fine with no errors
Going back to forum then gives 500 server error!
checked logs and seems like calling TPassimilate and TPSanitise hooks from 1.6.3
deleted those files and forum back but home page error msg, but admin menu etc. ok

So seems like TP fine with SMF 2.1.2 and TP 1.6.3, but not TP 2.2.2/SMF 2.1.3


tino

Quote from: tty456 on November 25, 2022, 10:48:25 AM
Thanks for looking at this, dump attached for the smf_tp_* tables, if thats not what you need let me know.

Interesting all the tables are `id` int NOT NULL AUTO_INCREMENT which is correct. We are not trying to change that from what I can tell.

Do you have the query which is running and causing the error? I wonder if it's a bug in SMF rather than TinyPortal, or it's how we interact with their code in a way they don't expect.


tty456

Not sure how I provide the actual query, the error line in the install.php calls smfFunc and some variables, if you can advise how happy to provide?

The error is shown when (re)installing TP from the Package manager, click install mod, then next screen lists files etc. with no warnings, then when continue after a moment the SQL error shown, I presuming at the point it tries to update/alter the TP tables?

I'm also looking at an error with Gallery Pro, but assumed my verison 8.0.8 too old, however in case related to this issue I found that Subs-Db-mySQL.php now sets an additional mode of PIPES_AS CONCACT which breaks gallery mod, if mode omited gallery works fine (just as a seperate test).


thanks




tty456

Lines around 354 are these I believe:

        if($db_type == 'mysql') {
            foreach ($col['columns'] as $column) {
                if (!isset($column['old_name']) || !$smcFunc['db_change_column']($db_prefix . $table, $column['old_name'], $column))
                    $smcFunc['db_add_column']('{db_prefix}' . $table, $column);

                // if utf8 is set alter column to be utf8 if text or tinytext.
                if ($utf8 && in_array($column['type'], array('text', 'tinytext', 'longtext'))) {
                    $smcFunc['db_query']('', '
                        ALTER TABLE {db_prefix}{raw:table}
                        CHANGE {raw:name} {raw:name} {raw:type} CHARACTER SET utf8',
                        array('table' => $table, 'name' => $column['name'], 'type' => $column['type'])
                    );
                }
            }
        }
    }

tino

Quote from: tty456 on November 25, 2022, 01:52:03 PM
Lines around 354 are these I believe:

        if($db_type == 'mysql') {
            foreach ($col['columns'] as $column) {
                if (!isset($column['old_name']) || !$smcFunc['db_change_column']($db_prefix . $table, $column['old_name'], $column))
                    $smcFunc['db_add_column']('{db_prefix}' . $table, $column);

                // if utf8 is set alter column to be utf8 if text or tinytext.
                if ($utf8 && in_array($column['type'], array('text', 'tinytext', 'longtext'))) {
                    $smcFunc['db_query']('', '
                        ALTER TABLE {db_prefix}{raw:table}
                        CHANGE {raw:name} {raw:name} {raw:type} CHARACTER SET utf8',
                        array('table' => $table, 'name' => $column['name'], 'type' => $column['type'])
                    );
                }
            }
        }
    }


Yeh, I can see what we are calling, just not what the actual SQL is, was hoping it would be in your error log.

My test environemnt runs maria db which is different to MySQL now.

tino

Quote from: tty456 on November 25, 2022, 01:43:00 PM
Not sure how I provide the actual query, the error line in the install.php calls smfFunc and some variables, if you can advise how happy to provide?

You might get the output if you set $db_show_debug = true; in your settings.php, but that's clutching at straws.

tino

I think I have found it..

ALTER TABLE smf_tp_articles CHANGE COLUMN `id` `id` int(11) NOT NULL DEFAULT NULL auto_increment

Now to dig into the SMF code and work out why they have added NULL

tty456

Edit: just seen your updated while was posting, left below in case helps!

ah right sorry, below is from SMF log after TP 2.2.2 install fails, there looks to be log entry for each loop, the first is:

10.10.11.27
896535194166c9d5d81748d5380bebed
https://wealdencaving.org.uk/forum2/smf/index.php?action=admin;area=packages;sa=install2;package=TinyPortal-2.2.2-1-test2b.zip;pid=0
/home/wealdencaving.org.uk/public_html/forum2/smf/Packages/temp/install.php (Line 354)  Backtrace information

#0: smf_db_error()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/Subs-Db-mysql.php on line 495
#1: smf_db_query()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/DbPackages-mysql.php on line 476
#2: smf_db_change_column()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/DbPackages-mysql.php on line 346
#3: smf_db_add_column()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Packages/temp/install.php on line 354
#4: require()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/Packages.php on line 1064
#5: PackageInstall()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/Subs.php on line 6016
#6: call_helper()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/Packages.php on line 91
#7: Packages()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/Sources/Admin.php on line 499
#8: AdminMain()
Called from /home/wealdencaving.org.uk/public_html/forum2/smf/index.php on line 191

@rjen

Running Latest TP on SMF2.1 at: www.fjr-club.nl

tino

Quote from: @rjen on November 25, 2022, 02:36:13 PM
I remembered reading this...

https://github.com/SimpleMachines/SMF/pull/7604

Yep going back to SMF 2.1.2 shows this in the logs when I install

ALTER TABLE smf_tp_articles CHANGE COLUMN `id` `id` int(11) NOT NULL auto_increment


tino

Quote from: tty456 on November 25, 2022, 02:29:28 PM
Edit: just seen your updated while was posting, left below in case helps!

Thanks, although not ideal you should be able to update to SMF 2.1.2 then install the latest TinyPortal and install the patch to SMF 2.1.3. This should mean you are on the latest TinyPortal and SMF. It's a bit of a fudge but should work.

tty456

Thanks for your help, I will hold off upgrading live for now then and keep an eye on that ticket before trying again.

O0