There are very rare cases where DB2 Sample source is platform-specific, but I doubt this is one
of those cases. (I used to be the Coordinator of the development & maintenance of the DB2
Samples).
I changed the SQLINTEGER to SQL_C_NUMERIC and it did fix that particular error (though I don't
see why), but it continues to fail to compile with various other errors. (Note, to prevent
clutter I've temporarily commented out the "7" functions, but they do get the same errors as
the "6" functions.)
Quote:
tbread.c
tbread.c(229) : error C2146: syntax error : missing ';' before identifier 'Prod_Num'
tbread.c(229) : error C2144: syntax error : '<Unknown>' should be preceded by '<Unknown>'
tbread.c(229) : error C2144: syntax error : '<Unknown>' should be preceded by '<Unknown>'
tbread.c(229) : error C2143: syntax error : missing ';' before 'identifier'
tbread.c(229) : error C2065: 'Prod_Num' : undeclared identifier
tbread.c(229) : error C2059: syntax error : ']'
tbread.c(247) : error C2143: syntax error : missing ';' before 'type'
tbread.c(247) : error C2143: syntax error : missing ';' before 'type'
tbread.c(247) : error C2143: syntax error : missing ')' before 'type'
tbread.c(247) : error C2143: syntax error : missing ';' before 'type'
tbread.c(247) : error C2065: 'i' : undeclared identifier
tbread.c(247) : warning C4552: '<=' : operator has no effect; expected operator with side-effect
tbread.c(247) : error C2059: syntax error : ')'
tbread.c(247) : error C2143: syntax error : missing ';' before '{'
tbread.c(248) : error C2109: subscript requires array or pointer type
tbread.c(248) : warning C4047: 'function' : 'SQLINTEGER' differs in levels of indirection from 'void *'
tbread.c(248) : error C2198: 'SQLBindParameter' : too few arguments for call through pointer-to-function
Microsoft (R) Incremental Linker Version 7.10.3077
Copyright (C) Microsoft Corporation. All rights reserved.
tbread.obj : error LNK2019: unresolved external symbol _gettimeofday referenced
in function _TbBasicSelectUsingFetchAndGetData6
tbread.exe : fatal error LNK1120: 1 unresolved externals
I'll hack away at this over the weekend & see what I can do with it.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2006-09-30 01:11
OK, I think I almost got it.
I had to move the SQLINTEGER Prod_Num[] declaration up to before any actual executable code
in the function. Once you do any actual execution, even a printf(), you can no longer
declare any new variables.
I also had to add the declaration of integer variable "i" up to before any actual executable
code in the function, and remove the declaration from the "for" loop. Same reason as above.
Now it compiles, but fails to link:
Quote:
tbread.obj : error LNK2019: unresolved external symbol __imp__timeGetTime@0 referenced in function _gettimeofday
tbread.exe : fatal error LNK1120: 1 unresolved externals
Like I said, I'll keep hacking at it over the weekend.
[Edit: Just fixing a typo.]
73blazer
Joined: 2006-05-12
Posts: 79
Posted: Sat, 2006-09-30 01:29
Hmmm...I thought AIX was tough.
Failed to link, when that happens with db2 apps on AIX, it's usally because the person who installed DB2 didn't use db2setup, and installed the filesets directly, which would miss putting the libdb2.a (.so in windows case) shared library in /usr/lib and the application fails to link.
But, with these samples, the bldapp thing has -L$DB2PATH/$LIB $LIB being lib32 or 64.
perhaps bldapp is differet per platform and has a different path linking (-L -l) setup?
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2006-09-30 02:00
This link error is unrelated to DB2. This is because there's no gettimeofday function on
Windows.
I found a gettimeofday function for Windows on the web and put it in the tbread code, and that
allowed it to at least compile, but it's generating the link error. So I'm going to ditch it
and look for a 'supported' way to do time functions on Win32.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2006-09-30 02:34
Here's a good example of how to do timing on Windows:
I should have your CLI program running on Windows shortly.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2006-09-30 02:55
It works! I'm attaching a copy here.
Here's the output on Windows:
Connecting to gallery2...
Connected to gallery2.
TO PERFORM A BASIC SELECT USING SQLGetData6:
Directly execute the statement
Data retrieved.
QueryTime= 66.153566 microseconds.TO PERFORM A BASIC SELECT USING SQLGetData7:
Directly execute the statement
Data retrieved.
QueryTime= 48.389013 microseconds.
====================================
====================================
====================================
====================================
====================================
TO PERFORM A SELECT PREP/EXEC WITH 6 PARAMETERS
Prepare the statement
Binding params to the statement
Data retrieved.
QueryTime= 45.723490 microseconds.TO PERFORM A SELECT PREP/EXEC WITH 7 PARAMETERS:
Prepare the statement
Binding params to the statement
Data retrieved.
QueryTime= 45.755119 microseconds.
As you can see, I don't see the difference you see.
If you wanted to pursue this you could post it in a DB2 newsgroup like "comp.databases.ibm-db2",
but I suspect the ultimate cause of it is going to be, um, specific to your system.
73blazer
Joined: 2006-05-12
Posts: 79
Posted: Sat, 2006-09-30 03:16
Sorry about that. Looking through the php code to find how they get time, I still only see gettimeofday
they must have some include i'm not seeing to case out windows somewhere else and add their own gettimeofday...
73blazer
Joined: 2006-05-12
Posts: 79
Posted: Sat, 2006-09-30 03:25
Hmmm..
Well, I don't think there's a umask problem there.
That could be a 64 bit thing. I did compile in 32 and 64 bit and run it respecitvly under my 32bit clients instance, or 64 bit clients instance.
I'll take it to my customers brand new 16-way power5 machine on monday(will be later in the week, so until then, it's a play machine!), pair down the IN clause to some basic info in a basic table and still see if I see a difference.
Can we try something..if your up to it?
Let me open up port 50000 on my network (snyderworld.org) to the world, and cataog my database (wwwbd) to your box there. Then run it against my database from your windows machine?
Or send me your compiled windows CLI program, I've got db2 client on my ibm laptop I can try. Just want to get as many scenario's as possible, the guys in the newsgroup (Serge, Knut) tend to lambast people that don't have enough information. Their help has been invaluable though..
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2006-09-30 13:28
Sure, I'm up for either one. Just let me know the hostname to catalog. I'm also attaching my
Windows executable here. Rather than post any sensitive info here, you can email me... larry
dot menard at rogers dot com.
Yes, some people in the forums can be pretty snippity if they aren't given enough information,
and for the most part I don't blame them... I hate being expected to read minds. I think it's
not generally known that forum support is *not* their real job (at least in the case of Serge
and Knut)... they are developers of DB2, and I know from personal experience how incredibly
challenging that can be. Those guys should be very respected for the awesome support they give
over and above their real jobs. Especially with the Oracle guys sandbagging there too, just
looking for an opportunity to get their opinions in. (I should be careful what I say about
that... one of those Oracle guys was very helpful to me with an Oracle question I once had. )
Something else occurred to me. The currenet symptoms could indicate a bad Access Plan being
chosen by the Optimizer once you cross the 7-predicate line. However, I would expect that to be
the same when using exec or prepare/execute. You may want to play around with those queries
using the EXPLAIN tools like "Visual Explain" or "dynexpln".
73blazer
Joined: 2006-05-12
Posts: 79
Posted: Thu, 2006-10-12 16:55
Ok, it's clear I will probably need to compile this.
If I run the library you have it says:
The procedure entry point sqlogstt@16 could not be located in the dynamic link library DB2APP.dll
I probably need some db2 development stuff on my windows box? I've never compiled anything on windows, are there any nice easy free compilers for it. I've not searched yet, I'm sure there's a bunch out there.
I'll send a PM to try directly if your still up for it.
Yeah,no I totally applaud the DB2 guys for responding to stuff in USENET, they usally have the answers if you ask the proper question. I just don't want to put anything there unless I'm sure and have all the info required to produce and backup the claim.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2006-10-12 21:59
Hmm, I compiled it on DB2 V9, that probably explains the runtime error you get.
Believe it or not, Microsoft does provide a free version of Visual Studio, which
includes a C compiler.
I haven't used the free version in a few years (I have a commercial version of "Visual
Studio .Net"), but when I used it last it worked fine for client apps, but not Stored
Procedures.
There is probably also a gcc for Windows, and it probably will even work with DB2,
but in case it doesn't, I doubt it's supported by DB2 so help could be hard to come by.
sphericus
Joined: 2005-11-06
Posts: 55
Posted: Fri, 2007-03-09 13:59
Hi.
Just me again.
I remember I was planning on running a few sets of unit tests on DB2 and what not, I ran into some issues with resource contention.
I recently moved the database off that server onto a different server to ease my resource contention. One of the things that happened was that I remembered about the unit tests. I ran a few different sets, firstly I ran the core set by itself, which completed without incident. However, I think due to my migration, the Comments system is now broken. if I run the full suite, it gets much further than last time, but crashes on one of the comments errors.
Any idea, what the database migration could have done?
The process i used was to perform a backup in DB2, restore it on the new machine, then manually install the JAR file drop the procedure and recreate the G2_BIT_OR UDF.
For some weird reason, all of the core tests passes sucessfully now, which wasnt the case IIRC.
Hope this helps you guys.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2007-03-09 14:22
- not sure if you use your productional gallery 2 for unit tests: don't! you may lose your data and your g2 may get corrupted by running unittests.
- if it crashes due to comments, i suggest a clean install of your development gallery2 (reinstall).
Folks, I apologize but I don't have a whole lot of time to do Gallery work lately... I once again have a full-time job, and my girlfriend and I are in process of fixing up and selling our respective condos and looking for a house to buy, not to mention planning to have a family.
Sphericus, without the actual error message(s) I can't even guess at what the problem might be. And even if you did provide the error message, as I said, I currently don't have the time to dig into it very deeply.
Your database migration steps sound perfectly fine to me. I don't see how that would affect the unit tests, but I admit that I'm not at all familiar with multi-site Gallery.
sphericus
Joined: 2005-11-06
Posts: 55
Posted: Sat, 2007-03-10 07:31
I did a reinstall of the multisite, now all the commenting is broken, before only some of the commenting unit tests fail, now they all fail.
I think I will deploy a very fresh gallery if I cant fix it tonight. As the problem seems to be spreading and getting worse and worse at everyturn.
sphericus
Joined: 2005-11-06
Posts: 55
Posted: Sat, 2007-03-10 08:24
All the core test passes this time. (I reinstalled the mutltisite over the old one this time i was thorough and deleted the old data folder.)
The only one it failed on was
# SessionTest.testExpiringStatusMessages
* Pass 1, status 1: xdf8d8713 should not be here
Mismatch At: [] type (NULL) !== Array type (array)
null type:NULL
array (
'i' => 7,
)
type:array
* Pass 2, status 1: xdf8d8713 should not be here
Mismatch At: [] type (NULL) !== Array type (array)
null type:NULL
array (
'i' => 7,
)
type:array
sphericus
Joined: 2005-11-06
Posts: 55
Posted: Sat, 2007-03-10 08:49
Also this is quite DB2 unrelated but I will post here, since its all in the same thread for me,
I am getting these randomly
[client 192.168.111.28] PHP Fatal error: Call to undefined method ConfirmImportControllerTestUrlGenerator::getCurrentUrlDir() in /opt/g2/gallery2/modules/remote/GalleryRemote.inc on line 827, referer: http://gset.mergo.net/lib/tools/phpunit/index.php?filter=core
Or something similar like this one
[client 192.168.111.28] PHP Fatal error: Call to undefined method ConfirmImportControllerTestUrlGenerator::getCurrentUrlDir() in /opt/g2/gallery2/modules/linkitem/test/phpunit/LinkItemTest.class on line 215, referer: http://gset.mergo.net/lib/tools/phpunit/index.php?filter=%21%28core%29
if i run them manually it passes.
Also, so far, I can only find NLS issues, which shouldnt be too hard to fix, but I am not yet able to run a whole set of tests unattended since i get that error.
galleryfan
Joined: 2007-03-22
Posts: 9
Posted: Thu, 2007-03-22 16:59
Could you please explain me the SQL generation for current G2 structure .. There was post from valiant for this on 2003-01-05. Basically the main things for which I am confused are
1. From where xml-src folder comes in GalleryStorage folder ? Is it automatically generated or the xml files inside it are created by us ?
2. If I want to add a support for a new database which all xml/tpl/inc files do i need to create on my own ?
3. Is there any difference between BITAND and BIT_AND ?
4. I read in one post that now we don't need bit_or function. In case they are not available what are the things that we should consider before starting implementing them ?
thanks
galleryfan
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2007-03-22 18:09
1. you should download g2's development package or use the SVN version. modules/*/classes/GalleryStorage/xml-src/ is stuff that you need to create manually.
it's a database-independent way to describe alter table and drop table instructions for g2's upgrade code.
2. none. you need to add SQL generation code to lib/tools/bin/ for the new database system.
other xml is generated by g2.
cd modules/core/clases/ && make
this extracts the table definitions from entity .class files, generates Entities.inc, Maps.inc and generates modules/core/classes/GalleryStorage/schema.tpl.
the tools to extract the XML from class files and to generate the DDL SQL (schema.tpl) from the xml are in lib/tools/bin/
3. different databases have different names for functions.
what g2 needs is a bit-and operator to bit-and two values in SQL.
4. most DBMS supported by g2 have a in-database aggregate (column) function to bit_or a whole column of values.
but for DBMS that don't support this function, we have fallback code in php to do the bit_or in php.
Today I looked at the schema.tpl file in GalleryStorage. For MySQL entries I found the following :
# A_GalleryGroup_1.0
ALTER TABLE DB_TABLE_PREFIXGroup
MODIFY COLUMN DB_COLUMN_PREFIXgroupName varchar(128);
...
...
# GalleryGroup
CREATE TABLE DB_TABLE_PREFIXGroup(
DB_COLUMN_PREFIXid int(11) NOT NULL,
DB_COLUMN_PREFIXgroupType int(11) NOT NULL,
DB_COLUMN_PREFIXgroupName varchar(128),
PRIMARY KEY(DB_COLUMN_PREFIXid),
UNIQUE (DB_COLUMN_PREFIXgroupName)
) DB_TABLE_TYPE
/*!40100 DEFAULT CHARACTER SET utf8 */;
1. Here we can see that the type of column DB_COLUMN_PREFIXgroupName is already varchar(128). Then why there is a need to modify its type ? It means that either the type is being changed in some other file to file I haven't come across or overlooked OR this ALTER statement is not required here. Kindly clarify my query ?
2. Also when I looked at the mysql entries in this file I found that out of 19 ALTER TABLE statements 6 add or drop index. Is it absolutely necessary to use indices apart from the fact that we may loose some performance ? The following statement also holds true :
Indexes are something extra that you can enable on your MySQL tables to increase performance, but they do have some downsides. When you create a new Index MySQL builds a separate block of information that needs to be updated everytime there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.
Moreover as far as gellery's design is concerned, why is it so necessary/useful/imp to have ALTER TABLE statements ?
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2007-03-23 20:02
> Then why there is a need to modify its type ?
because schema.tpl is for NEW installations while the # A_GalleryGroup_1.0 stuff is for UPGRADES.
2. of course it's a possibility to not use any indexes for sqlite, but doesn't sqlite support indices? if so, we need them.
btw:
please create a new forum topic and don't continue this one.
this one is about DB2.
[client 192.168.111.28] PHP Fatal error: Call to undefined method ConfirmImportControllerTestUrlGenerator::getCurrentUrlDir() in /opt/g2/gallery2/modules/remote/GalleryRemote.inc on line 827, referer: http://gset.mergo.net/lib/tools/phpunit/index.php?filter=core
Or something similar like this one
[client 192.168.111.28] PHP Fatal error: Call to undefined method ConfirmImportControllerTestUrlGenerator::getCurrentUrlDir() in /opt/g2/gallery2/modules/linkitem/test/phpunit/LinkItemTest.class on line 215, referer: http://gset.mergo.net/lib/tools/phpunit/index.php?filter=%21%28core%29
Any light anyone can shed on these php errors?
Thanks.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Sun, 2007-05-20 04:06
- sounds like you're running the test suite.
- sounds like some test overrode the url generator and it wasn't restored properly.
since i can't reproduce that test failure, maybe upgrade to the latest svn version of g2.
- sounds like you're running the test suite.
- sounds like some test overrode the url generator and it wasn't restored properly.
since i can't reproduce that test failure, maybe upgrade to the latest svn version of g2.
Yeah running the test suite which was requested a few pages back, I recently checked out a fresh copy from svn, I assume I only need to put it over the top of my current g2 installation?
Any comments on the test results from earlier?
Thanks.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Mon, 2007-06-04 14:10
i can't reproduce the problem (fatal php error). see my previous reply as to why this may have happened.
yes, please try the current svn version.
see: FAQ: How to upgrade Gallery2?
if the test failure persists, please try to isolate the problem (you can run ranges of unit tests).
db2 error: [42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected
token "g_mimeType" was found following "g2_MimeTypeMap DROP". Expected
tokens may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104] in
EXECUTE("ALTER TABLE g2_MimeTypeMap DROP g_mimeType")
(db2): ALTER TABLE g2_MimeTypeMap DROP g_mimeType
42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token
"g_mimeType" was found following "g2_MimeTypeMap DROP". Expected tokens
may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104
ADODB_db2._Execute(ALTER TABLE
g2_MimeTypeMap DROP g_mimeType, false)
% line 891, file: adodb.inc.php
ADODB_db2.Execute(ALTER TABLE g2_MimeTypeMap
DROP g_mimeType) % line 1552, file: GalleryStorageExtras.class
GalleryStorageExtras._executeSql(ALTER TABLE
DB_TABLE_PREFIXMimeTypeMap ADD COLUMN DB_COLUMN_PREFIXmimeTypeTemp
VARCHAR(128);
UPDATE DB_TABLE_PREFIXMimeTypeMap ...)
% line 1324, file: GalleryStorageExtras.class
GalleryStorageExtras.configureStore(core,
Array[1]) % line 759, file: GalleryStorage.class
Db2Storage.configureStore(core, Array[1]) % line 139, file: Db2Storage.class
realpath(/opt/g2/gallery2/modules/core/classes/../../../)
realpath(/opt/g2/gallery2/)
Error: Failed to upgrade the core module, this is the error stack trace:
Error (ERROR_STORAGE_FAILURE) : Error trying to run query: ALTER TABLE
g2_MimeTypeMap DROP g_mimeType in
modules/core/classes/GalleryStorage/GalleryStorageExtras.class at
line 1556 (GalleryStorageExtras::error)
in modules/core/classes/GalleryStorage/GalleryStorageExtras.class
at line 1324 (GalleryStorageExtras::_executeSql)
in modules/core/classes/GalleryStorage.class at line 759
(GalleryStorageExtras::configureStore)
in modules/core/classes/GalleryStorage/Db2Storage.class at
line 139 (Db2Storage::configureStore)
in modules/core/CoreModuleExtras.inc at line 2177
(Db2Storage::configureStore)
in modules/core/module.inc at line 495 (CoreModule::upgrade)
in modules/core/classes/GalleryModule.class at line 166
(CoreModule::upgrade)
in upgrade/steps/UpgradeCoreModuleStep.class at line 85
(CoreModule::installOrUpgrade)
in upgrade/index.php at line 185
(UpgradeCoreModuleStep::processRequest)
realpath(/opt/g2/gallery2/modules/core/classes/../../../)
realpath(/opt/g2/gallery2/)
Not sure if there is a problem with my install or not.
However, I did find that Larry encountered 42703 errors in the past. Although for NULL usages which probably doesn't apply to the one below, but thought I would put it in incase it needs to be debugged or amended somehow.
Also I had to manually remove the UDF's and remove the JAR file manually before I could get this far.
db2 error: [42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N
"G2_USER.G_LOCKED" is not valid in the context where it is used.
SQLSTATE=42703 SQLCODE=-206] in EXECUTE("SELECT g2_User.g_userName,
g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email,
g2_User.g_language, g2_User.g_locked, g2_Entity.g_id,
g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId,
g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber,
g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers FROM g2_User, g2_Entity
WHERE g2_User.g_id IN (6) AND g2_Entity.g_id=g2_User.g_id")
(db2): SELECT g2_User.g_userName, g2_User.g_fullName,
g2_User.g_hashedPassword, g2_User.g_email, g2_User.g_language,
g2_User.g_locked, g2_Entity.g_id, g2_Entity.g_creationTimestamp,
g2_Entity.g_isLinkable, g2_Entity.g_linkId,
g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber,
g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers FROM g2_User, g2_Entity
WHERE g2_User.g_id IN (6) AND g2_Entity.g_id=g2_User.g_id
42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N "G2_USER.G_LOCKED" is not
valid in the context where it is used. SQLSTATE=42703 SQLCODE=-206
ADODB_db2._Execute(SELECT g2_User.g_userName,
g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email,
g2_User.g_language, g2_User.g_locked, ...) % line 872, file: adodb.inc.php
ADODB_db2.Execute(SELECT g2_User.g_userName,
g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email,
g2_User.g_language, g2_User.g_locked, ..., Array[1]) % line 165, file: GalleryStorageExtras.class
GalleryStorageExtras.loadEntities(Array[1]) % line 303, file: GalleryStorage.class
Db2Storage.loadEntities(Array[1]) % line 71, file: GalleryEntityHelper_simple.class
GalleryEntityHelper_simple.loadEntitiesById(6,
GalleryUser) % line 2290, file: GalleryCoreApi.class
Unable to load admin user. Using in-memory user object as fallback
GalleryModule::installOrUpgrade core module
In bootstrap mode (core module)
Configure store for core module
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Sat, 2007-06-09 18:41
@user table error:
as the debug message says, this error is expected and handled fine.
@mimetype map:
the SQL from schema.tpl for the mimetype map table upgrade for DB2 is:
ALTER TABLE DB_TABLE_PREFIXMimeTypeMap ADD COLUMN DB_COLUMN_PREFIXmimeTypeTemp VARCHAR(128);
UPDATE DB_TABLE_PREFIXMimeTypeMap SET DB_COLUMN_PREFIXmimeTypeTemp = CAST(DB_COLUMN_PREFIXmimeType AS VARCHAR(128));
ALTER TABLE DB_TABLE_PREFIXMimeTypeMap DROP DB_COLUMN_PREFIXmimeType;
ALTER TABLE DB_TABLE_PREFIXMimeTypeMap RENAME DB_COLUMN_PREFIXmimeTypeTemp to DB_COLUMN_PREFIXmimeType;
DB_COLUMN_PREFIX and DB_TABLE_PREFIX are both replaced with your own values.
this SQL is very, very wrong for DB2. obviously, it has never been tested before.
I'm fixing this.
Hopefully we can include this fix in G2.2.2 too.
Updated to the latest svn revision that has those changes in them.
However, still experiencing the following problems.
I will try to see what debugging I can help provide you with as well.
db2 error: [42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected
token "COLUMN" was found following "g2_MimeTypeMap DROP". Expected tokens
may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104] in EXECUTE("ALTER
TABLE g2_MimeTypeMap DROP COLUMN g_mimeType")
--------------------------------------------------------------------------------
(db2): ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType
--------------------------------------------------------------------------------
42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "COLUMN"
was found following "g2_MimeTypeMap DROP". Expected tokens may include:
"CONSTRAINT". SQLSTATE=42601 SQLCODE=-104
ADODB_db2._Execute(ALTER TABLE
g2_MimeTypeMap DROP COLUMN g_mimeType, false) % line 891, file: adodb.inc.php
ADODB_db2.Execute(ALTER TABLE g2_MimeTypeMap
DROP COLUMN g_mimeType) % line 1558,
file: GalleryStorageExtras.class
GalleryStorageExtras._executeSql(ALTER TABLE
DB_TABLE_PREFIXMimeTypeMap ADD COLUMN DB_COLUMN_PREFIXmimeTypeTemp
VARCHAR(128);
UPDATE DB_TABLE_PREFIXMimeTypeMap ...)
% line 1324, file: GalleryStorageExtras.class
GalleryStorageExtras.configureStore(core,
Array[1]) % line 759, file: GalleryStorage.class
Db2Storage.configureStore(core, Array[1]) % line 139, file: Db2Storage.class
realpath(/opt/g2/gallery2/modules/core/classes/../../../)
realpath(/opt/g2/gallery2/)
Error: Failed to upgrade the core module, this is the error stack trace:
Error (ERROR_STORAGE_FAILURE) : Error trying to run query: ALTER TABLE
g2_MimeTypeMap DROP COLUMN g_mimeType in
modules/core/classes/GalleryStorage/GalleryStorageExtras.class at
line 1562 (GalleryStorageExtras::error)
in modules/core/classes/GalleryStorage/GalleryStorageExtras.class
at line 1324 (GalleryStorageExtras::_executeSql)
in modules/core/classes/GalleryStorage.class at line 759
(GalleryStorageExtras::configureStore)
in modules/core/classes/GalleryStorage/Db2Storage.class at
line 139 (Db2Storage::configureStore)
in modules/core/CoreModuleExtras.inc at line 2177
(Db2Storage::configureStore)
in modules/core/module.inc at line 495 (CoreModule::upgrade)
in modules/core/classes/GalleryModule.class at line 181
(CoreModule::upgrade)
in upgrade/steps/UpgradeCoreModuleStep.class at line 85
(CoreModule::installOrUpgrade)
in upgrade/index.php at line 185
(UpgradeCoreModuleStep::processRequest)
realpath(/opt/g2/gallery2/modules/core/classes/../../../)
realpath(/opt/g2/gallery2/)
sphericus
Joined: 2005-11-06
Posts: 55
Posted: Sun, 2007-06-10 04:38
Just out of curiosity, why was the following command not used if all that was required was to change the field to varchar(128)?
ALTER TABLE PG2USER.G2_MIMETYPEMAP ALTER COLUMN G_MIMETYPE SET DATA TYPE VARCHAR ( 128 ) ;
- and apart from the simple case of changing the data-type, the syntax is different. handling the different syntax would mean a greater risk of introducing new bugs.
other than that i agree that having a single "ALTER TABLE x ALTER COLUMN y SET ..." statement would be better. maybe we can do that later.
[code]
(db2): ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType
--------------------------------------------------------------------------------
42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "COLUMN"
was found following "g2_MimeTypeMap DROP". Expected tokens may include:
"CONSTRAINT". SQLSTATE=42601 SQLCODE=-104
Can you explain why this simple statement fails for you but not for me?
Has "DROP COLUMN" support been added just lately and you're using an older DB2 version?
Indeed, DB2 introduced "DROP COLUMN" support in DB2 v9.
from the online migration docs for DB2 v9:
Quote:
Other new clauses are DROP COLUMN, ALTER COLUMN SET DATA TYPE, SET NOT NULL, and DROP NOT NULL.
And several online discussions from earlier (v7 / v8) point out that there's no DROP COLUMN statement and that one has to recreate the whole table to emulate it.
And RENAME COLUMN isn't even available in all platforms! So good that we removed our dependency on that clause.
We need DROP COLUMN support not just as a workaround for changing columns. We allow dropping columns as part of our table upgrades too.
I guess we need to increase our DB2 requirement from v8.2 to v9. It's not like this has changed, it always was like that, we just didn't know yet.
If we wanted to allow that for DB2 v8, we'd have to recreate the whole table, insert all the old data into the new table, etc.
I've started a discussion on the gallery-devel mailing list. Hopefully Larry can chim in on the issue. But I guess we'll go with incrementing the requirements, requiring v9 or later instead of v8.2 or later.
the above URL didn't work for me but i found a google result for rsql0600.
what exact section of that page is about this issue here?
and why should a reorg table command be required before doing normal DML SQL on that table?
is it required every time or just after ALTERing the table?
i thought we did already a reorg for all altered tables in the upgrade code...
Operation not allowed for reason code reason-code on table table-name.
Explanation:
Access to table table-name is restricted. The cause is based on the following reason codes reason-code:
<snip>
7
The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation.
<snip>
7
Reorganize the table using the REORG TABLE command (note that INPLACE REORG TABLE is not allowed for a table that is in the reorg pending state).
My understanding is that, the REORG is needed due to the DROP COLUMN followed (loosely) by the UPDATE. I think there should be an overide command since, according to the error message for reason-code 7. A "ALTER TABLE statement containing a REORG-recommended operation" was used. from looking at it, and reproducing the issue, the command I think that is the issue for is the the DROP COLUMN. Hopefully we can find a workaround for it. Otherwise, would there be a way to drop in the REORG? Since even if we do workaround, a REORG would lead to better performance of the database overall.
DROP COLUMN
Drops the identified column from the table. The table must not be a typed table (SQLSTATE 428DH).
The table cannot have data capture enabled (SQLSTATE 42997).
Dropping a column requires table reorganization before further table access is allowed.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Tue, 2007-06-12 11:24
we do this already.
this is what we run on upgrade for all tables that have been altered:
1. 'CALL ADMIN_CMD (\'REORG TABLE ' . $this->_username . '.%s\')'
2. 'CALL ADMIN_CMD (\'RUNSTATS ON TABLE ' . $this->_username . '.%s WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL\')'
%s is replaced with the table name.
but we do this after the upgrade, at the end of the upgrade request.
e.g. there's a window for problems with DB2 after the ALTER TABLE, the upgrade code could actually query the table before the upgrade finishes.
but that's not the problem you've experienced. your error occurs when running the unit tests after the uprade, right?
Well, I am having this problem during the upgrade request.
As I have put the excerpt in below, when you are altering the table, DB2 requires a reorg on the table before you can do the "UPDATE" since you did an ALTER TABLE DROP COLUMN.
Unfortuneately, the REORG TABLE that you run is after the upgrade, one is required in the middle, as according to documentation, you cannot run queries against a table after you have run DROP COLUMN without first doing a REORG.
It is a bit weird, but I never used DROP COLUMN before. So maybe someone more familiar with the new v9.1 commands might want to comment.
(db2): SELECT g_name, g_major, g_minor FROM g2_Schema
(db2): ALTER TABLE g2_MimeTypeMap ADD COLUMN g_mimeTypeTemp VARCHAR(128)
(db2): UPDATE g2_MimeTypeMap SET g_mimeTypeTemp = CAST(g_mimeType AS
VARCHAR(128))
(db2): ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType
(db2): ALTER TABLE g2_MimeTypeMap ADD COLUMN g_mimeType VARCHAR(128)
db2 error: [57016: [IBM][CLI Driver][DB2/LINUX] SQL0668N Operation not
allowed for reason code "7" on table "PG2USER.G2_MIMETYPEMAP".
SQLSTATE=57016 SQLCODE=-668] in EXECUTE("UPDATE g2_MimeTypeMap SET
g_mimeType = g_mimeTypeTemp")
(db2): UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp
57016: [IBM][CLI Driver][DB2/LINUX] SQL0668N Operation not allowed for
reason code "7" on table "PG2USER.G2_MIMETYPEMAP". SQLSTATE=57016
SQLCODE=-668
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Tue, 2007-06-12 22:27
I see, i thought we're already passed the upgrade issues.
Ok, the fix is easy. Add reorg table statements after dropping columns (or after altering columns, for that matter).
I've debugged G2/DB2 upgrade issues today and got all fixes in the pipeline. It upgrades fine with the fixes.
They should be in SVN by tomorrow.
It seems that some other code was also changed? As now the upgrade fails at a much earlier point
setParameter core.repositories for core plugin
db2 error: [42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N
"G2_SCHEMA.G_PLUGINID" is not valid in the context where it is used.
SQLSTATE=42703 SQLCODE=-206] in EXECUTE("SELECT g_info FROM g2_PluginMap,
g2_Schema
WHERE g2_PluginMap.g_pluginId = g2_Schema.g_pluginId
AND g2_Schema.g_type = 'map' AND g2_PluginMap.g_active = 1")
(db2): SELECT g_info FROM g2_PluginMap, g2_Schema
WHERE g2_PluginMap.g_pluginId = g2_Schema.g_pluginId
AND g2_Schema.g_type = 'map' AND g2_PluginMap.g_active = 1
42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N "G2_SCHEMA.G_PLUGINID" is
not valid in the context where it is used. SQLSTATE=42703
SQLCODE=-206
With a simple glance, I am not sure as to why it is doing that, but I am happy to help you troubleshoot it.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Sat, 2007-06-16 14:53
arg...thanks for testing. this wasn't a DB2 issue specifically.
fixed in r16640.
sorry, we fought with some issues this week back and forth.
Also, on a side note, I am not able to upgrade my primary site as I followed the instructions on http://codex.gallery2.org/Gallery2:DB2
And I did not have FP9 at the time, so I used the following instructions. It would seem that the TABLE g2_Schema was not using the G2_TS_32K tablespace anymore. I am not sure if that is a result of your code, or it happened when the database was updated from 8.2 to 9.1
If you could let me know if all of the tables would have been using G2_TS_32K as the tablespace or if they would have used the default tablespace USERSPACE1. That would be appreciated.
db2 "CREATE DATABASE gallery2 USING CODESET UTF-8 TERRITORY US"
db2 "CONNECT TO gallery2"
db2 "CREATE BUFFERPOOL g2_bp_32k SIZE 250 PAGESIZE 32 K"
db2 "CREATE TABLESPACE g2_ts_32k PAGESIZE 32 K MANAGED BY SYSTEM USING ('G2_TS_32K') BUFFERPOOL g2_bp_32k"
db2 "CREATE TEMPORARY TABLESPACE g2_tempts_32k PAGESIZE 32 K MANAGED BY SYSTEM USING ('G2_TEMPTS_32K') BUFFERPOOL g2_bp_32k"
db2 "CONNECT RESET"
db2 error: [54010: [IBM][CLI Driver][DB2/LINUX] SQL0670N The row length of
the table exceeded a limit of "4005" bytes. (Table space "USERSPACE1".)
SQLSTATE=54010 SQLCODE=-670] in EXECUTE("ALTER TABLE g2_Schema ADD COLUMN
g_createSql VARCHAR(10000)")
(db2): ALTER TABLE g2_Schema ADD COLUMN g_createSql VARCHAR(10000)
54010: [IBM][CLI Driver][DB2/LINUX] SQL0670N The row length of the table
exceeded a limit of "4005" bytes. (Table space "USERSPACE1".)
SQLSTATE=54010 SQLCODE=-670
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Sun, 2007-06-17 12:55
g2 doesn't alter / specify tablespaces. if it changed, then it must have happened in your db2 v8 -> v9 migration.
and yes, the above error looks like it doesn't use your 32k pagesize tablespace anymore.
the new version of g2 requires a pagesize of ~ 27k for the Schema table (row length).
FYI: i've tested various upgrades yesterday and ran all tests with db2 and everything worked fine.
Also any chance of some more verbose logging? As my test site currently complains database error, and does not start.
However, I am not able to find an error in any log file for php or db2.
Posts: 757
There are very rare cases where DB2 Sample source is platform-specific, but I doubt this is one
of those cases. (I used to be the Coordinator of the development & maintenance of the DB2
Samples).
I changed the SQLINTEGER to SQL_C_NUMERIC and it did fix that particular error (though I don't
see why), but it continues to fail to compile with various other errors. (Note, to prevent
clutter I've temporarily commented out the "7" functions, but they do get the same errors as
the "6" functions.)
I'll hack away at this over the weekend & see what I can do with it.
Posts: 757
OK, I think I almost got it.
in the function. Once you do any actual execution, even a printf(), you can no longer
declare any new variables.
code in the function, and remove the declaration from the "for" loop. Same reason as above.
Now it compiles, but fails to link:
Like I said, I'll keep hacking at it over the weekend.
[Edit: Just fixing a typo.]
Posts: 79
Hmmm...I thought AIX was tough.
Failed to link, when that happens with db2 apps on AIX, it's usally because the person who installed DB2 didn't use db2setup, and installed the filesets directly, which would miss putting the libdb2.a (.so in windows case) shared library in /usr/lib and the application fails to link.
But, with these samples, the bldapp thing has -L$DB2PATH/$LIB $LIB being lib32 or 64.
perhaps bldapp is differet per platform and has a different path linking (-L -l) setup?
Posts: 757
This link error is unrelated to DB2. This is because there's no gettimeofday function on
Windows.
I found a gettimeofday function for Windows on the web and put it in the tbread code, and that
allowed it to at least compile, but it's generating the link error. So I'm going to ditch it
and look for a 'supported' way to do time functions on Win32.
Posts: 757
Here's a good example of how to do timing on Windows:
http://www.weizmann.ac.il/home/chaipi/TimeTest.htm
I should have your CLI program running on Windows shortly.
Posts: 757
It works! I'm attaching a copy here.
Here's the output on Windows:
As you can see, I don't see the difference you see.
If you wanted to pursue this you could post it in a DB2 newsgroup like "comp.databases.ibm-db2",
but I suspect the ultimate cause of it is going to be, um, specific to your system.
Posts: 79
Sorry about that. Looking through the php code to find how they get time, I still only see gettimeofday
they must have some include i'm not seeing to case out windows somewhere else and add their own gettimeofday...
Posts: 79
Hmmm..
Well, I don't think there's a umask problem there.
That could be a 64 bit thing. I did compile in 32 and 64 bit and run it respecitvly under my 32bit clients instance, or 64 bit clients instance.
I'll take it to my customers brand new 16-way power5 machine on monday(will be later in the week, so until then, it's a play machine!), pair down the IN clause to some basic info in a basic table and still see if I see a difference.
Can we try something..if your up to it?
Let me open up port 50000 on my network (snyderworld.org) to the world, and cataog my database (wwwbd) to your box there. Then run it against my database from your windows machine?
Or send me your compiled windows CLI program, I've got db2 client on my ibm laptop I can try. Just want to get as many scenario's as possible, the guys in the newsgroup (Serge, Knut) tend to lambast people that don't have enough information. Their help has been invaluable though..
Posts: 757
Sure, I'm up for either one. Just let me know the hostname to catalog. I'm also attaching my
Windows executable here. Rather than post any sensitive info here, you can email me... larry
dot menard at rogers dot com.
Yes, some people in the forums can be pretty snippity if they aren't given enough information,
)
and for the most part I don't blame them... I hate being expected to read minds. I think it's
not generally known that forum support is *not* their real job (at least in the case of Serge
and Knut)... they are developers of DB2, and I know from personal experience how incredibly
challenging that can be. Those guys should be very respected for the awesome support they give
over and above their real jobs. Especially with the Oracle guys sandbagging there too, just
looking for an opportunity to get their opinions in. (I should be careful what I say about
that... one of those Oracle guys was very helpful to me with an Oracle question I once had.
Something else occurred to me. The currenet symptoms could indicate a bad Access Plan being
chosen by the Optimizer once you cross the 7-predicate line. However, I would expect that to be
the same when using exec or prepare/execute. You may want to play around with those queries
using the EXPLAIN tools like "Visual Explain" or "dynexpln".
Posts: 79
Ok, it's clear I will probably need to compile this.
If I run the library you have it says:
The procedure entry point sqlogstt@16 could not be located in the dynamic link library DB2APP.dll
I probably need some db2 development stuff on my windows box? I've never compiled anything on windows, are there any nice easy free compilers for it. I've not searched yet, I'm sure there's a bunch out there.
I'll send a PM to try directly if your still up for it.
Yeah,no I totally applaud the DB2 guys for responding to stuff in USENET, they usally have the answers if you ask the proper question. I just don't want to put anything there unless I'm sure and have all the info required to produce and backup the claim.
Posts: 757
Hmm, I compiled it on DB2 V9, that probably explains the runtime error you get.
Believe it or not, Microsoft does provide a free version of Visual Studio, which
includes a C compiler.
http://msdn.microsoft.com/vstudio/express/visualC/default.aspx
I haven't used the free version in a few years (I have a commercial version of "Visual
Studio .Net"), but when I used it last it worked fine for client apps, but not Stored
Procedures.
There is probably also a gcc for Windows, and it probably will even work with DB2,
but in case it doesn't, I doubt it's supported by DB2 so help could be hard to come by.
Posts: 55
Hi.
Just me again.
I remember I was planning on running a few sets of unit tests on DB2 and what not, I ran into some issues with resource contention.
I recently moved the database off that server onto a different server to ease my resource contention. One of the things that happened was that I remembered about the unit tests. I ran a few different sets, firstly I ran the core set by itself, which completed without incident. However, I think due to my migration, the Comments system is now broken. if I run the full suite, it gets much further than last time, but crashes on one of the comments errors.
Any idea, what the database migration could have done?
The process i used was to perform a backup in DB2, restore it on the new machine, then manually install the JAR file drop the procedure and recreate the G2_BIT_OR UDF.
For some weird reason, all of the core tests passes sucessfully now, which wasnt the case IIRC.
Hope this helps you guys.
Posts: 32509
- not sure if you use your productional gallery 2 for unit tests: don't! you may lose your data and your g2 may get corrupted by running unittests.
- if it crashes due to comments, i suggest a clean install of your development gallery2 (reinstall).
--------------
Doumentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
I run a multi-site configuration, and the unit tests are run in one of the multi-sites. Is that isolated enough or is that still dangerous?
Posts: 32509
that's 100% isolated. but if you run into problems, i'd reinstall the multisite.
--------------
Doumentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 757
Folks, I apologize but I don't have a whole lot of time to do Gallery work lately... I once again have a full-time job, and my girlfriend and I are in process of fixing up and selling our respective condos and looking for a house to buy, not to mention planning to have a family.
Sphericus, without the actual error message(s) I can't even guess at what the problem might be. And even if you did provide the error message, as I said, I currently don't have the time to dig into it very deeply.
Your database migration steps sound perfectly fine to me. I don't see how that would affect the unit tests, but I admit that I'm not at all familiar with multi-site Gallery.
Posts: 55
I did a reinstall of the multisite, now all the commenting is broken, before only some of the commenting unit tests fail, now they all fail.
I think I will deploy a very fresh gallery if I cant fix it tonight. As the problem seems to be spreading and getting worse and worse at everyturn.
Posts: 55
All the core test passes this time. (I reinstalled the mutltisite over the old one this time i was thorough and deleted the old data folder.)
The only one it failed on was
Posts: 55
Also this is quite DB2 unrelated but I will post here, since its all in the same thread for me,
I am getting these randomly
if i run them manually it passes.
Also, so far, I can only find NLS issues, which shouldnt be too hard to fix, but I am not yet able to run a whole set of tests unattended since i get that error.
Posts: 9
Could you please explain me the SQL generation for current G2 structure .. There was post from valiant for this on 2003-01-05. Basically the main things for which I am confused are
1. From where xml-src folder comes in GalleryStorage folder ? Is it automatically generated or the xml files inside it are created by us ?
2. If I want to add a support for a new database which all xml/tpl/inc files do i need to create on my own ?
3. Is there any difference between BITAND and BIT_AND ?
4. I read in one post that now we don't need bit_or function. In case they are not available what are the things that we should consider before starting implementing them ?
thanks
galleryfan
Posts: 32509
1. you should download g2's development package or use the SVN version. modules/*/classes/GalleryStorage/xml-src/ is stuff that you need to create manually.
it's a database-independent way to describe alter table and drop table instructions for g2's upgrade code.
2. none. you need to add SQL generation code to lib/tools/bin/ for the new database system.
other xml is generated by g2.
cd modules/core/clases/ && make
this extracts the table definitions from entity .class files, generates Entities.inc, Maps.inc and generates modules/core/classes/GalleryStorage/schema.tpl.
the tools to extract the XML from class files and to generate the DDL SQL (schema.tpl) from the xml are in lib/tools/bin/
3. different databases have different names for functions.
what g2 needs is a bit-and operator to bit-and two values in SQL.
4. most DBMS supported by g2 have a in-database aggregate (column) function to bit_or a whole column of values.
but for DBMS that don't support this function, we have fallback code in php to do the bit_or in php.
--------------
Doumentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 9
Today I looked at the schema.tpl file in GalleryStorage. For MySQL entries I found the following :
# A_GalleryGroup_1.0
ALTER TABLE DB_TABLE_PREFIXGroup
MODIFY COLUMN DB_COLUMN_PREFIXgroupName varchar(128);
...
...
# GalleryGroup
CREATE TABLE DB_TABLE_PREFIXGroup(
DB_COLUMN_PREFIXid int(11) NOT NULL,
DB_COLUMN_PREFIXgroupType int(11) NOT NULL,
DB_COLUMN_PREFIXgroupName varchar(128),
PRIMARY KEY(DB_COLUMN_PREFIXid),
UNIQUE (DB_COLUMN_PREFIXgroupName)
) DB_TABLE_TYPE
/*!40100 DEFAULT CHARACTER SET utf8 */;
1. Here we can see that the type of column DB_COLUMN_PREFIXgroupName is already varchar(128). Then why there is a need to modify its type ? It means that either the type is being changed in some other file to file I haven't come across or overlooked OR this ALTER statement is not required here. Kindly clarify my query ?
2. Also when I looked at the mysql entries in this file I found that out of 19 ALTER TABLE statements 6 add or drop index. Is it absolutely necessary to use indices apart from the fact that we may loose some performance ? The following statement also holds true :
Indexes are something extra that you can enable on your MySQL tables to increase performance, but they do have some downsides. When you create a new Index MySQL builds a separate block of information that needs to be updated everytime there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.
Moreover as far as gellery's design is concerned, why is it so necessary/useful/imp to have ALTER TABLE statements ?
Posts: 32509
> Then why there is a need to modify its type ?
because schema.tpl is for NEW installations while the # A_GalleryGroup_1.0 stuff is for UPGRADES.
2. of course it's a possibility to not use any indexes for sqlite, but doesn't sqlite support indices? if so, we need them.
btw:
please create a new forum topic and don't continue this one.
this one is about DB2.
--------------
Doumentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Any light anyone can shed on these php errors?
Thanks.
Posts: 32509
- sounds like you're running the test suite.
- sounds like some test overrode the url generator and it wasn't restored properly.
since i can't reproduce that test failure, maybe upgrade to the latest svn version of g2.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Yeah running the test suite which was requested a few pages back, I recently checked out a fresh copy from svn, I assume I only need to put it over the top of my current g2 installation?
Any comments on the test results from earlier?
Thanks.
Posts: 32509
i can't reproduce the problem (fatal php error). see my previous reply as to why this may have happened.
yes, please try the current svn version.
see: FAQ: How to upgrade Gallery2?
if the test failure persists, please try to isolate the problem (you can run ranges of unit tests).
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Had this error during the upgrade.
db2 error: [42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "g_mimeType" was found following "g2_MimeTypeMap DROP". Expected tokens may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104] in EXECUTE("ALTER TABLE g2_MimeTypeMap DROP g_mimeType") (db2): ALTER TABLE g2_MimeTypeMap DROP g_mimeType 42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "g_mimeType" was found following "g2_MimeTypeMap DROP". Expected tokens may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104 ADODB_db2._Execute(ALTER TABLE g2_MimeTypeMap DROP g_mimeType, false) % line 891, file: adodb.inc.php ADODB_db2.Execute(ALTER TABLE g2_MimeTypeMap DROP g_mimeType) % line 1552, file: GalleryStorageExtras.class GalleryStorageExtras._executeSql(ALTER TABLE DB_TABLE_PREFIXMimeTypeMap ADD COLUMN DB_COLUMN_PREFIXmimeTypeTemp VARCHAR(128); UPDATE DB_TABLE_PREFIXMimeTypeMap ...) % line 1324, file: GalleryStorageExtras.class GalleryStorageExtras.configureStore(core, Array[1]) % line 759, file: GalleryStorage.class Db2Storage.configureStore(core, Array[1]) % line 139, file: Db2Storage.class realpath(/opt/g2/gallery2/modules/core/classes/../../../) realpath(/opt/g2/gallery2/) Error: Failed to upgrade the core module, this is the error stack trace: Error (ERROR_STORAGE_FAILURE) : Error trying to run query: ALTER TABLE g2_MimeTypeMap DROP g_mimeType in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 1556 (GalleryStorageExtras::error) in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 1324 (GalleryStorageExtras::_executeSql) in modules/core/classes/GalleryStorage.class at line 759 (GalleryStorageExtras::configureStore) in modules/core/classes/GalleryStorage/Db2Storage.class at line 139 (Db2Storage::configureStore) in modules/core/CoreModuleExtras.inc at line 2177 (Db2Storage::configureStore) in modules/core/module.inc at line 495 (CoreModule::upgrade) in modules/core/classes/GalleryModule.class at line 166 (CoreModule::upgrade) in upgrade/steps/UpgradeCoreModuleStep.class at line 85 (CoreModule::installOrUpgrade) in upgrade/index.php at line 185 (UpgradeCoreModuleStep::processRequest) realpath(/opt/g2/gallery2/modules/core/classes/../../../) realpath(/opt/g2/gallery2/)Not sure if there is a problem with my install or not.
However, I did find that Larry encountered 42703 errors in the past. Although for NULL usages which probably doesn't apply to the one below, but thought I would put it in incase it needs to be debugged or amended somehow.
Also I had to manually remove the UDF's and remove the JAR file manually before I could get this far.
db2 error: [42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N "G2_USER.G_LOCKED" is not valid in the context where it is used. SQLSTATE=42703 SQLCODE=-206] in EXECUTE("SELECT g2_User.g_userName, g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email, g2_User.g_language, g2_User.g_locked, g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers FROM g2_User, g2_Entity WHERE g2_User.g_id IN (6) AND g2_Entity.g_id=g2_User.g_id") (db2): SELECT g2_User.g_userName, g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email, g2_User.g_language, g2_User.g_locked, g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers FROM g2_User, g2_Entity WHERE g2_User.g_id IN (6) AND g2_Entity.g_id=g2_User.g_id 42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N "G2_USER.G_LOCKED" is not valid in the context where it is used. SQLSTATE=42703 SQLCODE=-206 ADODB_db2._Execute(SELECT g2_User.g_userName, g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email, g2_User.g_language, g2_User.g_locked, ...) % line 872, file: adodb.inc.php ADODB_db2.Execute(SELECT g2_User.g_userName, g2_User.g_fullName, g2_User.g_hashedPassword, g2_User.g_email, g2_User.g_language, g2_User.g_locked, ..., Array[1]) % line 165, file: GalleryStorageExtras.class GalleryStorageExtras.loadEntities(Array[1]) % line 303, file: GalleryStorage.class Db2Storage.loadEntities(Array[1]) % line 71, file: GalleryEntityHelper_simple.class GalleryEntityHelper_simple.loadEntitiesById(6, GalleryUser) % line 2290, file: GalleryCoreApi.class Unable to load admin user. Using in-memory user object as fallback GalleryModule::installOrUpgrade core module In bootstrap mode (core module) Configure store for core modulePosts: 32509
@user table error:
as the debug message says, this error is expected and handled fine.
@mimetype map:
the SQL from schema.tpl for the mimetype map table upgrade for DB2 is:
DB_COLUMN_PREFIX and DB_TABLE_PREFIX are both replaced with your own values.
this SQL is very, very wrong for DB2. obviously, it has never been tested before.
I'm fixing this.
Hopefully we can include this fix in G2.2.2 too.
Thanks for reporting the issue!
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 32509
Could you please SVN upgrade to see if it fixes things for you?
see:
http://svn.sourceforge.net/viewvc/gallery?rev=16569&view=rev
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Updated to the latest svn revision that has those changes in them.
However, still experiencing the following problems.
I will try to see what debugging I can help provide you with as well.
db2 error: [42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "COLUMN" was found following "g2_MimeTypeMap DROP". Expected tokens may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104] in EXECUTE("ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType") -------------------------------------------------------------------------------- (db2): ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType -------------------------------------------------------------------------------- 42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "COLUMN" was found following "g2_MimeTypeMap DROP". Expected tokens may include: "CONSTRAINT". SQLSTATE=42601 SQLCODE=-104 ADODB_db2._Execute(ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType, false) % line 891, file: adodb.inc.php ADODB_db2.Execute(ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType) % line 1558, file: GalleryStorageExtras.class GalleryStorageExtras._executeSql(ALTER TABLE DB_TABLE_PREFIXMimeTypeMap ADD COLUMN DB_COLUMN_PREFIXmimeTypeTemp VARCHAR(128); UPDATE DB_TABLE_PREFIXMimeTypeMap ...) % line 1324, file: GalleryStorageExtras.class GalleryStorageExtras.configureStore(core, Array[1]) % line 759, file: GalleryStorage.class Db2Storage.configureStore(core, Array[1]) % line 139, file: Db2Storage.class realpath(/opt/g2/gallery2/modules/core/classes/../../../) realpath(/opt/g2/gallery2/) Error: Failed to upgrade the core module, this is the error stack trace: Error (ERROR_STORAGE_FAILURE) : Error trying to run query: ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 1562 (GalleryStorageExtras::error) in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 1324 (GalleryStorageExtras::_executeSql) in modules/core/classes/GalleryStorage.class at line 759 (GalleryStorageExtras::configureStore) in modules/core/classes/GalleryStorage/Db2Storage.class at line 139 (Db2Storage::configureStore) in modules/core/CoreModuleExtras.inc at line 2177 (Db2Storage::configureStore) in modules/core/module.inc at line 495 (CoreModule::upgrade) in modules/core/classes/GalleryModule.class at line 181 (CoreModule::upgrade) in upgrade/steps/UpgradeCoreModuleStep.class at line 85 (CoreModule::installOrUpgrade) in upgrade/index.php at line 185 (UpgradeCoreModuleStep::processRequest) realpath(/opt/g2/gallery2/modules/core/classes/../../../) realpath(/opt/g2/gallery2/)Posts: 55
Just out of curiosity, why was the following command not used if all that was required was to change the field to varchar(128)?
That worked fine for my setup.
Posts: 32509
- i looked at it yesterday and it looks like "ALTER TABLE ALTER COLUMN" isn't as powerful as the original column-definition statement used in "ALTER TABLE ADD" and "CREATE TABLEE".
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000888.htm (alter table)
vs.
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000888.htm (create table)
e.g. it doesn't allow a lot of data types.
- and apart from the simple case of changing the data-type, the syntax is different. handling the different syntax would mean a greater risk of introducing new bugs.
other than that i agree that having a single "ALTER TABLE x ALTER COLUMN y SET ..." statement would be better. maybe we can do that later.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 32509
Question:
[code]
(db2): ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType
--------------------------------------------------------------------------------
42601: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "COLUMN"
was found following "g2_MimeTypeMap DROP". Expected tokens may include:
"CONSTRAINT". SQLSTATE=42601 SQLCODE=-104
Can you explain why this simple statement fails for you but not for me?
Has "DROP COLUMN" support been added just lately and you're using an older DB2 version?
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 32509
Indeed, DB2 introduced "DROP COLUMN" support in DB2 v9.
from the online migration docs for DB2 v9:
And several online discussions from earlier (v7 / v8) point out that there's no DROP COLUMN statement and that one has to recreate the whole table to emulate it.
And RENAME COLUMN isn't even available in all platforms! So good that we removed our dependency on that clause.
We need DROP COLUMN support not just as a workaround for changing columns. We allow dropping columns as part of our table upgrades too.
I guess we need to increase our DB2 requirement from v8.2 to v9. It's not like this has changed, it always was like that, we just didn't know yet.
If we wanted to allow that for DB2 v8, we'd have to recreate the whole table, insert all the old data into the new table, etc.
I've started a discussion on the gallery-devel mailing list. Hopefully Larry can chim in on the issue. But I guess we'll go with incrementing the requirements, requiring v9 or later instead of v8.2 or later.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Thanks for chasing that up.
I am indeed running 8.2 FP 13.
Your reluctance to support <v9.1 is understandable. I will have a look at our other apps and evaluate a migration up to 9.1.
Will let you know how that goes.
Posts: 55
That was easy, all the new versions of the apps we run all require 9.1.
We should have a v9.1 box up shortly.
Thanks for clearing that up.
Posts: 55
Got a new error.
db2 error: [57016: [IBM][CLI Driver][DB2/LINUX] SQL0668N Operation not allowed for reason code "7" on table "PG2USER.G2_MIMETYPEMAP". SQLSTATE=57016 SQLCODE=-668] in EXECUTE("UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp") (db2): UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp 57016: [IBM][CLI Driver][DB2/LINUX] SQL0668N Operation not allowed for reason code "7" on table "PG2USER.G2_MIMETYPEMAP". SQLSTATE=57016 SQLCODE=-668 ADODB_db2._Execute(UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp, false) % line 891, file: adodb.inc.php ADODB_db2.Execute(UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp) % line 1558, file: GalleryStorageExtras.class GalleryStorageExtras._executeSql(ALTER TABLE DB_TABLE_PREFIXMimeTypeMap ADD COLUMN DB_COLUMN_PREFIXmimeTypeTemp VARCHAR(128); UPDATE DB_TABLE_PREFIXMimeTypeMap ...) % line 1324, file: GalleryStorageExtras.class GalleryStorageExtras.configureStore(core, Array[1]) % line 759, file: GalleryStorage.class Db2Storage.configureStore(core, Array[1]) % line 139, file: Db2Storage.classI was able to make the problem go away by running
reorg table g2_MimeTypeMapbefore the
UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTempHere is the page on the error btw.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.doc/core/rsql0600.htm
Hope that helps.
Posts: 32509
the above URL didn't work for me but i found a google result for rsql0600.
what exact section of that page is about this issue here?
and why should a reorg table command be required before doing normal DML SQL on that table?
is it required every time or just after ALTERing the table?
i thought we did already a reorg for all altered tables in the upgrade code...
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Sorry,
try this one.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.msg-search.doc/doc/sql0668-sch.htm
This is the excerpt that is in question.
Operation not allowed for reason code reason-code on table table-name. Explanation: Access to table table-name is restricted. The cause is based on the following reason codes reason-code: <snip> 7 The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation. <snip> 7 Reorganize the table using the REORG TABLE command (note that INPLACE REORG TABLE is not allowed for a table that is in the reorg pending state).My understanding is that, the REORG is needed due to the DROP COLUMN followed (loosely) by the UPDATE. I think there should be an overide command since, according to the error message for reason-code 7. A "ALTER TABLE statement containing a REORG-recommended operation" was used. from looking at it, and reproducing the issue, the command I think that is the issue for is the the DROP COLUMN. Hopefully we can find a workaround for it. Otherwise, would there be a way to drop in the REORG? Since even if we do workaround, a REORG would lead to better performance of the database overall.
Posts: 55
Some more links for you.
This one is about the implications of running a REORG-recommended operation
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0023297.htm
Here is the full breakdown of the ALTER TABLE command
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000888.htm
Our problem is right here.
Posts: 32509
we do this already.
this is what we run on upgrade for all tables that have been altered:
1. 'CALL ADMIN_CMD (\'REORG TABLE ' . $this->_username . '.%s\')'
2. 'CALL ADMIN_CMD (\'RUNSTATS ON TABLE ' . $this->_username . '.%s WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL\')'
%s is replaced with the table name.
but we do this after the upgrade, at the end of the upgrade request.
e.g. there's a window for problems with DB2 after the ALTER TABLE, the upgrade code could actually query the table before the upgrade finishes.
but that's not the problem you've experienced. your error occurs when running the unit tests after the uprade, right?
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Well, I am having this problem during the upgrade request.
As I have put the excerpt in below, when you are altering the table, DB2 requires a reorg on the table before you can do the "UPDATE" since you did an ALTER TABLE DROP COLUMN.
Unfortuneately, the REORG TABLE that you run is after the upgrade, one is required in the middle, as according to documentation, you cannot run queries against a table after you have run DROP COLUMN without first doing a REORG.
It is a bit weird, but I never used DROP COLUMN before. So maybe someone more familiar with the new v9.1 commands might want to comment.
(db2): SELECT g_name, g_major, g_minor FROM g2_Schema (db2): ALTER TABLE g2_MimeTypeMap ADD COLUMN g_mimeTypeTemp VARCHAR(128) (db2): UPDATE g2_MimeTypeMap SET g_mimeTypeTemp = CAST(g_mimeType AS VARCHAR(128)) (db2): ALTER TABLE g2_MimeTypeMap DROP COLUMN g_mimeType (db2): ALTER TABLE g2_MimeTypeMap ADD COLUMN g_mimeType VARCHAR(128) db2 error: [57016: [IBM][CLI Driver][DB2/LINUX] SQL0668N Operation not allowed for reason code "7" on table "PG2USER.G2_MIMETYPEMAP". SQLSTATE=57016 SQLCODE=-668] in EXECUTE("UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp") (db2): UPDATE g2_MimeTypeMap SET g_mimeType = g_mimeTypeTemp 57016: [IBM][CLI Driver][DB2/LINUX] SQL0668N Operation not allowed for reason code "7" on table "PG2USER.G2_MIMETYPEMAP". SQLSTATE=57016 SQLCODE=-668Posts: 32509
I see, i thought we're already passed the upgrade issues.
Ok, the fix is easy. Add reorg table statements after dropping columns (or after altering columns, for that matter).
I've debugged G2/DB2 upgrade issues today and got all fixes in the pipeline. It upgrades fine with the fixes.
They should be in SVN by tomorrow.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 32509
The fixes are in svn.
- Added REORG TABLE when chaning / dropping columns
- Removed CAST() when assigning a value from a varchar to a clob column.
Also fixed:
Core upgrade code no longer deactivates other modules / removed possible infinite loop.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Got some new errors today in the upgrade.
It seems that some other code was also changed? As now the upgrade fails at a much earlier point
setParameter core.repositories for core plugin db2 error: [42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N "G2_SCHEMA.G_PLUGINID" is not valid in the context where it is used. SQLSTATE=42703 SQLCODE=-206] in EXECUTE("SELECT g_info FROM g2_PluginMap, g2_Schema WHERE g2_PluginMap.g_pluginId = g2_Schema.g_pluginId AND g2_Schema.g_type = 'map' AND g2_PluginMap.g_active = 1") (db2): SELECT g_info FROM g2_PluginMap, g2_Schema WHERE g2_PluginMap.g_pluginId = g2_Schema.g_pluginId AND g2_Schema.g_type = 'map' AND g2_PluginMap.g_active = 1 42S22: [IBM][CLI Driver][DB2/LINUX] SQL0206N "G2_SCHEMA.G_PLUGINID" is not valid in the context where it is used. SQLSTATE=42703 SQLCODE=-206With a simple glance, I am not sure as to why it is doing that, but I am happy to help you troubleshoot it.
Posts: 32509
arg...thanks for testing. this wasn't a DB2 issue specifically.
fixed in r16640.
sorry, we fought with some issues this week back and forth.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Thanks.
The test multi site has sucessfully upgraded, although now I am getting a weird error, that doesn't tell me much.
Is there a way to enable more verbose logging?
Also you are more than welcome to have a look at http://gset.mergo.net
Also, on a side note, I am not able to upgrade my primary site as I followed the instructions on http://codex.gallery2.org/Gallery2:DB2
And I did not have FP9 at the time, so I used the following instructions. It would seem that the TABLE g2_Schema was not using the G2_TS_32K tablespace anymore. I am not sure if that is a result of your code, or it happened when the database was updated from 8.2 to 9.1
If you could let me know if all of the tables would have been using G2_TS_32K as the tablespace or if they would have used the default tablespace USERSPACE1. That would be appreciated.
db2 "CREATE DATABASE gallery2 USING CODESET UTF-8 TERRITORY US" db2 "CONNECT TO gallery2" db2 "CREATE BUFFERPOOL g2_bp_32k SIZE 250 PAGESIZE 32 K" db2 "CREATE TABLESPACE g2_ts_32k PAGESIZE 32 K MANAGED BY SYSTEM USING ('G2_TS_32K') BUFFERPOOL g2_bp_32k" db2 "CREATE TEMPORARY TABLESPACE g2_tempts_32k PAGESIZE 32 K MANAGED BY SYSTEM USING ('G2_TEMPTS_32K') BUFFERPOOL g2_bp_32k" db2 "CONNECT RESET"db2 error: [54010: [IBM][CLI Driver][DB2/LINUX] SQL0670N The row length of the table exceeded a limit of "4005" bytes. (Table space "USERSPACE1".) SQLSTATE=54010 SQLCODE=-670] in EXECUTE("ALTER TABLE g2_Schema ADD COLUMN g_createSql VARCHAR(10000)") (db2): ALTER TABLE g2_Schema ADD COLUMN g_createSql VARCHAR(10000) 54010: [IBM][CLI Driver][DB2/LINUX] SQL0670N The row length of the table exceeded a limit of "4005" bytes. (Table space "USERSPACE1".) SQLSTATE=54010 SQLCODE=-670Posts: 32509
g2 doesn't alter / specify tablespaces. if it changed, then it must have happened in your db2 v8 -> v9 migration.
and yes, the above error looks like it doesn't use your 32k pagesize tablespace anymore.
the new version of g2 requires a pagesize of ~ 27k for the Schema table (row length).
FYI: i've tested various upgrades yesterday and ran all tests with db2 and everything worked fine.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 55
Also any chance of some more verbose logging? As my test site currently complains database error, and does not start.
However, I am not able to find an error in any log file for php or db2.
Thanks.