Simple Machines Community Forum

SMF Support => SMF 1.1.x Support => Topic started by: archbullseye on June 26, 2008, 12:53:58 AM

Title: Unknown column 'lb.logTime' in 'where clause'
Post by: archbullseye on June 26, 2008, 12:53:58 AM
Upgrading...
Converting activity logs...
Unsuccessful!
This query:

    SELECT lmr.ID_BOARD, lmr.ID_MEMBER, lmr.logTime
    FROM smf_log_mark_read AS lmr
    LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = lmr.ID_BOARD AND lb.ID_MEMBER = lmr.ID_MEMBER)
    WHERE lb.logTime < lmr.logTime;

Caused the error:

    Unknown column 'lb.logTime' in 'where clause'


Help me!!  Ok.. well it all started a few days ago when I had a 'corrupt' sql entry.. it was "smf_sessions"  So in a not so smart move I dropped the data in the table..  The board came back online but alot of it was not working..  So I decided to upgrade it from 1.1.rc3 to the newest version.. the error above is what I get when I try to finish the upgrade...
Seeing that it wont go thou I used the repair utility to bring it out of Maintenance mode and nothing is working at all now..  I get an error msg that says to report this error msg to the admin..  :(

PhP Info
http://www.allthingsnotconsidered.us/longevity/guild/bb/phpinfo.php

Thanks in advance..
Archer
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: aldo on June 26, 2008, 02:28:07 AM
Try:

    SELECT lmr.ID_BOARD, lmr.ID_MEMBER, lmr.logTime, lb.logTime
    FROM smf_log_mark_read AS lmr
    LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = lmr.ID_BOARD AND lb.ID_MEMBER = lmr.ID_MEMBER)
    WHERE lb.logTime < lmr.logTime;

You need to make sure each um, "column" you are going to use in your Query is in the SELECT part.

Hope this helps, I only learned LEFT JOINs a few days ago :)
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: ThorstenE on June 26, 2008, 05:21:08 AM
Unknown column 'lb.logTime' in 'where clause'
the table smf_log_boards has no column logTime.. (i verified with a SMF 1.1.5 Installation)

the upgrade-script uses upgrade_1-0.sql which is the wrong when  upgrading from 1.1.(x)
go to phpmyadmin and open table smf_settings..

which ist the value for smfVersion?


Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: archbullseye on July 01, 2008, 02:12:41 PM
Quote from: ThorstenE on June 26, 2008, 05:21:08 AM
Unknown column 'lb.logTime' in 'where clause'
which ist the value for smfVersion?

There is no column for version for this..  Is there anyway I can take the raw data (users, boards, posts etc...) and import it into a new board?
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: greyknight17 on July 01, 2008, 09:56:25 PM
Do you have a backup of that database before you did the upgrade? How about another backup prior to deleting that smf_sessions table? NEVER, EVER drop or delete anything in the database without making a backup first. You never know when you pull too fast a trigger on something until it's too late.

If you have a backup of both prior and after the session issue, try restoring just the sessions table to the backup that had the smf_sessions table dropped. That should fix it for SMF 1.1rc3.

Are you using the SMF 1.1.5 Large Upgrade package to do the upgrade?

For the smfVersion question, do the following to get the version:

Go into phpMyAdmin (http://www.simplemachines.org/community/index.php?topic=21919.0) and click on your database. Then click on the SQL tab and copy paste the following code and run it:

SELECT * FROM smf_settings where `variable`='smfVersion'

I'm not sure how much the change was, so hopefully they have the smfVersion variable for that old version.
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: archbullseye on July 05, 2008, 02:38:06 PM
QuoteMySQL returned an empty result set (i.e. zero rows). (Query took 0.0070 sec)

SQL query:
SELECT *
FROM smf_settings
WHERE `variable` = 'smfVersion'
LIMIT 0 , 30

This is what I get when I try to run the sql command.  I do have a backup of the database I saved to my computer.. when I try to import it to a new database all I get is:
Quote
Error

SQL query:

CREATE TABLE `smf_log_online` (
SESSION varchar( 32 ) NOT NULL default '',
logTime timestamp NOT NULL default 'CURRENT_TIMESTAMP',
ID_MEMBER mediumint( 8 ) unsigned NOT NULL default 0,
ip int( 10 ) unsigned NOT NULL default 0,
url text NOT NULL default '',
PRIMARY KEY ( SESSION ) ,
KEY online( logTime, ID_MEMBER ) ,
KEY ID_MEMBER( ID_MEMBER )
) TYPE = MYISAM ;

MySQL said: Documentation
#1067 - Invalid default value for 'logTime'

It kills the process and only imports 18 of the tables.
Any ideas?

EDIT:  This backup was done before I tried the upgrade and was done thou the forum maintenance tab. (so the forums were still working at that time.)
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: greyknight17 on July 05, 2008, 03:35:11 PM
Open up the backup and replace that create table section with the following:

CREATE TABLE `smf_log_online` (
SESSION varchar( 32 ) NOT NULL default '',
logTime timestamp NOT NULL default CURRENT_TIMESTAMP,
ID_MEMBER mediumint( 8 ) unsigned NOT NULL default 0,
ip int( 10 ) unsigned NOT NULL default 0,
url text NOT NULL default '',
PRIMARY KEY ( SESSION ) ,
KEY online( logTime, ID_MEMBER ) ,
KEY ID_MEMBER( ID_MEMBER )
) TYPE = MYISAM ;


See if you can restore it now.
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: archbullseye on July 05, 2008, 08:32:29 PM
Nope.. just got more errors...

Error

SQL query:

#
# Dumping data in `smf_log_search_subjects`
#
INSERT INTO `smf_log_search_subjects` (
`word` ,
`ID_TOPIC`
)
VALUES ( 05, 381 ) , ( 06, 232 ) , ( 07, 208 ) , ( 07, 212 ) , ( 07, 216 ) , ( 07, 222 ) , ( 07, 234 ) , ( 07, 235 ) , ( 07, 240 ) , ( 07, 242 ) , ( 07, 243 ) , ( 07, 245 ) , ( 07, 330 ) , ( 07, 366 ) , ( 07, 379 ) , ( 07, 381 ) , ( 1, 397 ) , ( 1, 444 ) , ( 10, 240 ) , ( 10, 330 ) , (
'10-27-07', 337
), ( 1000, 122 ) , ( 101, 27 ) , ( 11, 212 ) , ( 11, 366 ) , ( 11, 379 ) , (
'11-2-07', 348
), (
'11-3-07', 349
), (
'11-4-07', 350
), ( 12, 137 ) , ( 12, 242 ) , ( 12, 243 ) , ( 12, 381 ) , ( 13, 245 ) , (
'13-16', 274
), ( 14, 216 ) , ( 14, 246 ) , ( 14, 247 ) , ( 15, 102 ) , ( 15, 241 ) , (
'15th', 11
), ( 16, 241 ) , ( 16, 366 ) , ( 17, 58 ) , ( 17, 241 ) , ( 17, 278 ) , ( 18, 251 ) , ( 18, 257 ) , (
'18-slots', 192
), ( 19, 138 ) , ( 19, 261 ) , (
'1st-30th', 13
), ( 2, 232 ) , ( 2, 373 ) , ( 2, 444 ) , ( 2, 461 ) , ( 2, 498 ) , ( 2, 514 ) , ( 20, 330 ) , ( 2006, 59 ) , ( 2008, 505 ) , ( 22, 117 ) , ( 23, 278 ) , (
'24th', 75
), ( 25, 509 ) , ( 25, 512 ) , ( 26, 222 ) , ( 3, 373 ) , ( 3, 467 ) , ( 30, 379 ) , (
'4th', 272
), ( 5, 133 ) , ( 5, 208 ) , ( 5, 212 ) , ( 5, 216 ) , ( 5, 222 ) , ( 500, 244 ) , (
'5th', 517
), ( 6, 84 ) , ( 6, 232 ) , ( 6, 234 ) , ( 6, 235 ) , ( 6, 240 ) , ( 6, 242 ) , ( 6, 243 ) , ( 6, 245 ) , ( 6, 246 ) , ( 6, 247 ) , ( 6, 251 ) , ( 6, 257 ) , ( 6, 261 ) , ( 6, 270 ) , ( 6, 273 ) , ( 60, 147 ) , ( 7, 208 ) , ( 7, 270 ) , ( 7, 273 ) , (
'7pm', 58
), (
'7pm', 102
), (
'7th', 59
), ( 8, 270 ) , ( 8, 273 ) , (
'a', 25
), (
'a', 41
), (
'a', 135
), (
'a', 160
), (
'a', 253
), (
'a', 284
), (
'a', 311
), (
'a', 343
), (
'a', 351
), (
'a', 366
), (
'a', 381
), (
'a', 386
), (
'a', 397
), (
'a', 420
), (
'a', 432
), (
'a', 451
), (
'a', 524
), (
'a', 551
), (
'a', 555
), (
'a', 562
), (
'a-team', 348
), (
'a-team', 349
), (
'a-team', 350
), (
'about', 373
), (
'above', 147
), (
'absence', 440
), (
'account', 8
), (
'account', 320
), (
'action', 136
), (
'activities', 568
), (
'add', 7
), (
'add-ons', 327
), (
'addon', 304
), (
'addon', 309
), (
'addon', 477
), (
'addons', 21
), (
'addons', 422
), (
'addons', 430
), (
'address', 206
), (
'adjustments', 314
), (
'after', 92
), (
'again', 567
), (
'ahh', 543
), (
'aid', 29
), (
'aivo', 265
), (
'alchemy', 527
), (
'all', 351
), (
'all', 399
), (
'all', 412
), (
'alts', 43
), (
'aman', 367
), (
'aman', 479
), (
'aman', 529
), (
'amazing', 548
), (
'an', 99
), (
'analyzer', 499
), (
'and', 14
), (
'and', 21
), (
'and', 34
), (
'and', 53
), (
'and', 181
), (
'and', 230
), (
'and', 252
), (
'and', 256
), (
'and', 265
), (
'and', 297
), (
'and', 315
), (
'and', 327
), (
'and', 331
), (
'and', 347
), (
'and', 363
), (
'and', 373
), (
'and', 397
), (
'and', 401
), (
'and', 406
), (
'and', 422
), (
'and', 517
), (
'and', 523
), (
'and', 533
), (
'and', 565
), (
'announcing', 200
), (
'another', 221
), (
'another', 309
), (
'answer', 64
), (
'answer', 233
), (
'any', 373
), (
'anyway', 402
), (
'apology', 46
), (
'april', 517
), (
'aq20', 10
), (
'arcatraz', 231
), (
'arch', 96
), (
'arch', 239
), (
'arch', 313
), (
'archs', 113
), (
'are', 85
), (
'are', 114
), (
'are', 194
), (
'are', 265
), (
'are', 268
), (
'are', 519
), (
'are', 561
), (
'arena', 209
), (
'art', 13
), (
'artist', 497
), (
'as', 25
), (
'as', 497
), (
'as', 520
), (
'assistance', 14
), (
'at', 399
), (
'attempt', 279
), (
'attempt', 320
), (
'attractiveness', 193
), (
'attuned', 345
), (
'attunement', 189
), (
'attunement', 201
), (
'attunement', 439
), (
'attunements', 141
), (
'av', 84
), (
'availability', 512
), (
'away', 142
), (
'awhile', 265
), (
'b', 382
), (
'b', 383
), (
'b', 387
), (
'b', 389
), (
'b', 390
), (
'b', 393
), (
'baaacck', 342
), (
'back', 136
), (
'back', 237
), (
'back', 483
), (
'bags', 192
), (
'bah', 345
), (
'bank', 31
), (
'bank', 226
), (
'bc', 141
), (
'bc', 154
), (
'bc', 219
), (
'be', 342
), (
'be', 533
), (
'beat', 560
), (
'been', 522
), (
'been', 569
), (
'before', 5
), (
'before', 207
), (
'being', 134
), (
'beorht', 148
);

MySQL said: Documentation
#1062 - Duplicate entry '6-232' for key 1
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: archbullseye on July 06, 2008, 08:39:30 PM
So would it work if I were to do a fresh install and then somehow import the data into the new forum?
Title: Re: Unknown column 'lb.logTime' in 'where clause'
Post by: ThorstenE on July 07, 2008, 01:29:50 AM
first go to admin - forum maintanance - Empty out unimportant logs. This should truncate the table smf_log_search_subjects (which causes the error). after that try the upgrade again.

Quote from: archbullseye on July 06, 2008, 08:39:30 PM
So would it work if I were to do a fresh install and then somehow import the data into the new forum?

try it out, i think this should work because the database structure from 1.1.RC3 and 1.1.5 are identical..