Loading...
 
Architecture / Installation

Architecture / Installation


SQL error in 1.8 upgrade

posts: 4656 Japan

I'm trying to update my db tables from 1.7.4 to 1.8, but errors are stopping the process when I try to run comments_fix1.7to1.8.sql. The existing tiki_comments table gets renamed to old_tiki_comments and the new tiki_comments gets made. But the next SQL statement

"# Move over all the md5 stuff to the new format.
insert into tiki_comments select
tc.threadId,
concat('wiki page', tp.pagename) 'object',
tc.parentId,
tc.userName,
tc.commentDate,
tc.hits,
tc.type,
tc.points,
tc.votes,
tc.average,
tc.title,
tc.data,
tc.hash,
tc.user_ip,
tc.summary,
tc.smiley,
tc.message_id,
tc.in_reply_to
from old_tiki_comments tc, tiki_pages tp
where tc.object = md5'wiki page', tp.pageName;"

produces this error:

"SQL-query : 
INSERT INTO tiki_comments
SELECT tc.threadId, concat( 'wiki page', tp.pagename ) 'object', tc.parentId, tc.userName, tc.commentDate, tc.hits, tc.type, tc.points, tc.votes, tc.average, tc.title, tc.data, tc.hash, tc.user_ip, tc.summary, tc.smiley, tc.message_id, tc.in_reply_to
FROM old_tiki_comments tc, tiki_pages tp
WHERE tc.object = md5( concat( 'wiki page', tp.pageName ) )
MySQL said:

Unknown column 'tc.message_id' in 'field list'"

My server setup is Linux/MySQL 4.0.15 and I'm using phpMyAdmin 2.5.2. Does anyone have an idea why this statement is failing? Thanks for your help.

-- Gary




posts: 2881 United Kingdom

Hi

Have you run the normal upgrade script to 1.7to1.8 first?

Damian

posts: 4656 Japan

> Damian:
> Hi
>
> Have you run the normal upgrade script to 1.7to1.8 first?
>
> Damian

Hi,

Thanks for your quick reply.

Yes, I just double-checked to make sure about the 1.7to1.8 upgrade. Then fix_comments1.7to1.8.sql makes a new tiki_comments table but can't do the inserts, as the error shows. Has this been working OK for other people?

-- Gary


posts: 4656 Japan

> Damian:
> Hi
>
> Have you run the normal upgrade script to 1.7to1.8 first?
>
> Damian

Hi,

Thanks for your quick reply.

Yes, I just double-checked to make sure about the 1.7to1.8 upgrade. Then fix_comments1.7to1.8.sql makes a new tiki_comments table but can't do the inserts, as the error shows. Has this been working OK for other people?

-- Gary


posts: 1

I have the same problem running on Windows XP.
I tried running the script from the command line, and it didn't work either.
Couldn't upgrade.
Any ideas?


posts: 4656 Japan

I ran a test with a fresh, virgin Tiki 1.7.4 database, upgrading it as described on the UpgradeTo18 page. And in this case the upgrade worked. There were a few redundancies in the permissions being submitted by the tiki1.7to1.8 file, but the comment and structure fixes were made with no errors. (All done at a hosted site running Linux, using phpMyAdmin to work with the database.)

So now why won't the fix files work with my actual site database on the same server and using the same tools? I guess I'll check to compare the tables again.

-- Gary


posts: 1001 Canada

Whatever you find out, you've got a cool avatarlol
Can we trade!

posts: 4656 Japan

> Chealer9:
> Whatever you find out, you've got a cool avatarlol
> Can we trade!

Heh, if you look like me, then maybe so. wink

-- Gary (chibaguy)

posts: 2881 United Kingdom

> chibaguy:
> > Chealer9:
> > Whatever you find out, you've got a cool avatarlol
> > Can we trade!
>
> Heh, if you look like me, then maybe so. wink
>
> — Gary (chibaguy)
>
>

Hi,

luciash reported that on RC3 you had to run the upgrade1.7to1.8 sql a second time to fix forum posts. I thought this was addressed in 1.8 final. You may need to try that again.

Damian

posts: 4656 Japan

> luciash reported that on RC3 you had to run the upgrade1.7to1.8 sql a second time to fix forum posts. I thought this was addressed in 1.8 final. You may need to try that again.


Well, I've been trying the sequence described on the upgrade to 1.8 page. There it seems to say that running the upgrade1.7to1.8 comes last, after the "fix" statements.

What I don't get (and I'm pretty much an sql novice), is the error message: "Unknown column 'tc.message_id' in 'field list'". I understand the period is used to link components of multiple identifiers, like 'table.column', or 'database.table', etc. Where is the table 'tc' that is supposed to contain the 'message_id' column in this statement? Does 'old_tiki_comments tc' mean "old_tiki_comments" is abbreviated as "tc"? If so, I wonder why my server setup isn't recognizing that? I wonder what would happen if I replaced 'tc' with 'old_tiki_comments' (and 'tp' with 'tiki_pages') throughout the fix statement...

(A short time later) ...well, that didn't help because there is no 'message_id' column in 'old_tiki_comments', just like the error message says.

-- gary_c


posts: 4656 Japan

> Chealer9:
> Whatever you find out, you've got a cool avatarlol
> Can we trade!

Heh, if you look like me, then maybe so. wink

-- Gary (chibaguy)

posts: 2881 United Kingdom

> chibaguy:
> > Chealer9:
> > Whatever you find out, you've got a cool avatarlol
> > Can we trade!
>
> Heh, if you look like me, then maybe so. wink
>
> — Gary (chibaguy)
>
>

Hi,

luciash reported that on RC3 you had to run the upgrade1.7to1.8 sql a second time to fix forum posts. I thought this was addressed in 1.8 final. You may need to try that again.

Damian

posts: 4656 Japan

> luciash reported that on RC3 you had to run the upgrade1.7to1.8 sql a second time to fix forum posts. I thought this was addressed in 1.8 final. You may need to try that again.


Well, I've been trying the sequence described on the upgrade to 1.8 page. There it seems to say that running the upgrade1.7to1.8 comes last, after the "fix" statements.

What I don't get (and I'm pretty much an sql novice), is the error message: "Unknown column 'tc.message_id' in 'field list'". I understand the period is used to link components of multiple identifiers, like 'table.column', or 'database.table', etc. Where is the table 'tc' that is supposed to contain the 'message_id' column in this statement? Does 'old_tiki_comments tc' mean "old_tiki_comments" is abbreviated as "tc"? If so, I wonder why my server setup isn't recognizing that? I wonder what would happen if I replaced 'tc' with 'old_tiki_comments' (and 'tp' with 'tiki_pages') throughout the fix statement...

(A short time later) ...well, that didn't help because there is no 'message_id' column in 'old_tiki_comments', just like the error message says.

-- gary_c


posts: 4656 Japan

I decided to just leave my old tiki_comments data in the old_tiki_comments table, where the fix statement put them, and create a new tiki_comments table using the 1.8.sql file. This is because nothing I tried enabled me to run the comments_fix1.7to1.8.sql file. Maybe the problem is that I'm using http and ftp tools, as this is a hosted site; I don't have shell access; I couldn't use the "-f" parameter with the fix files, and phpMyAdmin and other web-based tools consistently returned an error and stalled out as shown.

Then I tried to run the structures_fix1.7to1.8.sql file, but it hangs in the same way as the comments fix, claiming there's no such column in the field list. So again I just added a new table from the 1.8 tiki.sql.

This is on my family web site, with relatively few comments and no structures, so there wasn't much content to worry about. But I wanted to figure this out just to know why it doesn't work, and also because I have another site with around a hundred records in tiki_comments, plus some structures, and I'd like to avoid dumping all those.

I'd like to know if anyone has successfully updated from 1.7+ to 1.8 using only web tools and not shell access. I'll be pretty surprised (and hungry for advice) if anyone has. Also, if anyone has the time to explain how the fix files work, with a focus on the part where the error occurs for me, as quoted above, I'd appreciate that very much.

-- chibaguy


posts: 4656 Japan

exclaim I looked again at the SQL error message and decided to try adding the columns to old_tiki_comments that the script was trying to find. I added message_id and in_reply_to columns to the old_tiki_comments table, getting their details from the 1.8 tiki.tpl. I ran the fix statement again and this time no abort. Sure enough, my forums now have their posts again, etc. I guess the moral is to pay attention to the MySQL error message when a statement aborts (this is when using phpMyAdmin) and try providing what it's asking for. I haven't done the structures fix yet, but I image it will go the same way.

If other phpMyAdmin users have this 1.7 to 1.8 upgrade problem too, I think the fix_comments file should be edited to modify old_tiki_comments, adding the missing columns. Anyway, it's something Robin Powell or whoever maintains the file should look at, I believe.

-- Gary (chibaguy)


Upcoming Events

1)  18 Apr 2024 14:00 GMT-0000
Tiki Roundtable Meeting
2)  16 May 2024 14:00 GMT-0000
Tiki Roundtable Meeting
3)  20 Jun 2024 14:00 GMT-0000
Tiki Roundtable Meeting
4)  18 Jul 2024 14:00 GMT-0000
Tiki Roundtable Meeting
5)  15 Aug 2024 14:00 GMT-0000
Tiki Roundtable Meeting
6)  19 Sep 2024 14:00 GMT-0000
Tiki Roundtable Meeting
7) 
Tiki birthday
8)  17 Oct 2024 14:00 GMT-0000
Tiki Roundtable Meeting
9)  21 Nov 2024 14:00 GMT-0000
Tiki Roundtable Meeting
10)  19 Dec 2024 14:00 GMT-0000
Tiki Roundtable Meeting