[TYPO3] Typo3 / DBAL / ADODB / MSSQL: Violation of primary Key
Ries van Twisk
typo3 at rvt.dds.nl
Tue Jun 13 13:24:02 CEST 2006
Stefan,
it is not a good solution but it might get you going.
Create an insert trigger on the cache hash table to runs the datathrough
an stored-procedure. This stored procedure will then do a insert or a
update for you
depending if the hash is already there.
In a perfect world it's better to create a core patch for it if it's a
core problem.
However core patches are not easily accepted and this just might get you
going
running typo3 on mssql. I expect the same for postgresql....
I am still in the process of migrating data when I have time :)
Ries
> Hi,
>
> I am still struggling with the combination of Typo3, DBAL, ADODB and MSSQL,
> running on an IIS server.
>
> After setting up everything and defining a new page from fresh (which all
> works without major problems), I am getting the following messages once I am
> viewing the new page:
>
> Warning: mssql_query(): message: Violation of PRIMARY KEY constraint
> 'PK__cache_hash__37A5467C'. Cannot insert duplicate key in object
> 'cache_hash'. (severity 14) in
> C:\...\typo3\typo3conf\ext\adodb\adodb\drivers\adodb-mssql.inc.php on line
> 701
>
> Warning: mssql_query(): Query failed in
> C:\...\typo3\typo3conf\ext\adodb\adodb\drivers\adodb-mssql.inc.php on line
> 701
>
> These are the facts of the installation:
> OS: MS Windows Server 2003 Std Edition (NT 5.2 - 3790)
> MSSQL: SQL Server Std Edition Version 9.00.194 (RTM), English MS Internet
> Information Services 6.0 PHP 4.4.1 MS Internet Explorer 6.0
>
> I am trying to clarify some things you asked for / mentioned:
>
>
>> What does your connection string look like?
>>
>
> I am connecting to the MSSQL server using the pipe name (name of pipe is
> "PIPE_SQL_INTRANET"). I am entering this pipe name as host within the
> installation tool of typo3, and typo3 connects to this server and the
> selected database fine (without any warning/error messages).
>
>
>> did you set you database access to utf8?
>>
>
> I am not sure where to do this. I am not a real expert in MSSQL
> administration. I have made some PHP applications working together with the
> same MSSQL server but there was never the need to get deeper into the MSSQL
> administration stuff. It would be nice if you could give me a hint what to
> check or change here.
>
>
>> Can you enable the DBAL (query) logging?
>> Try to find the INSERT statement that produces the error.
>>
>
> I have enabled the debug mode in DBAL and have found the following SQL
> statement in the table tx_dbal_debuglog (this is the last INSERT INTO
> cache_hash statement that can be found in that table):
>
> INSERT INTO "cache_hash"
> (
> "hash",
> "content",
> "ident",
> "tstamp"
> ) VALUES (
> '1a72ae4ad2a532804d40be11df1e3fae',
> null,
> 'SHORT TC',
> '1149841058'
> ) WITH 1 BLOB FIELDS: "content"
>
>
> The log table has captured 25 SQL statements thereafter (according to the
> uid number) which are these:
>
>
> SELECT top 1 *
> FROM "sys_template"
> WHERE
> "pid" = 2 AND "deleted" = 0 AND "hidden" =
> 0 AND ("starttime" <= 1149841056) AND ("endtime" = 0 OR "endtime" >
> 1149841056)
> ORDER BY "sorting"
>
> SELECT "content"
> FROM "cache_pagesection"
> WHERE
> "page_id" = 2 AND "mpvar_hash" = 222419149
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 54
>
> SELECT top 1 *
> FROM "sys_template"
> WHERE
> "pid" = 2 AND "deleted" = 0 AND "hidden" =
> 0 AND ("starttime" <= 1149841057) AND ("endtime" = 0 OR "endtime" >
> 1149841057)
> ORDER BY "sorting"
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 23
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 54
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 23
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 43
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 43
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 45
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 46
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 45
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 44
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 46
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 68
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 44
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 57
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 68
>
> SELECT "content"
> FROM "cache_hash"
> WHERE
> "hash" = '3e7cec8583620e9c3476b59035a50550'
>
> SELECT *
> FROM "static_template"
> WHERE
> "uid" = 57
>
> DELETE FROM "cache_pagesection"
> WHERE
> "page_id" = 2 AND "mpvar_hash" =
> 222419149
>
> SELECT "content"
> FROM "cache_hash"
> WHERE
> "hash" = '3e7cec8583620e9c3476b59035a50550'
>
> INSERT INTO "cache_pagesection"
> (
> "page_id",
> "mpvar_hash",
> "content",
> "tstamp"
> ) VALUES (
> '2',
> '222419149',
> null,
> '1149841056'
> ) WITH 1 BLOB FIELDS: "content"
>
> DELETE FROM "cache_pagesection"
> WHERE
> "page_id" = 2 AND "mpvar_hash" =
> 222419149
>
> INSERT INTO "cache_pagesection"
> (
> "page_id",
> "mpvar_hash",
> "content",
> "tstamp"
> ) VALUES (
> '2',
> '222419149',
> null,
> '1149841057'
> ) WITH 1 BLOB FIELDS: "content"
>
>
>
>> That would mean you cannot work at all (just try creating more than one
>>
> pages or content elements).
>
> I can create more pages or more content elements without any problems. The
> message appears only when I try to view the results as front end web site.
>
>
> To give you the complete picture, I have done the following steps before:
>
> 1 - Installation of Typo3 4.0 package, with Dummy
> 2 - Copy DBAL & ADODB extensions in /typo3conf/ext
> 3 - Configure DBAL localconf.php (dbal/handlerCfg) according to DBAL
> documentation (part 1.4): DBAL Type = "adodb" / Driver = "mssql"
> 4 - Change /typo3/install/index.php (inactivate row which prevents install
> script to be executed)
> 5 - Create new MSSQL database with name "typo"
> 6 - With MSSQL Enterprise Manager change properties of new database "typo":
> Set options to "Use quoted identifiers" and "ANSI NULL default"
> 7 - Create new MSSQL user with name "typo"; give all access rights on the
> new database to this user
> 8 - Run typo3 installation script from browser (/typo3/install/index.php)
> 9 - Enter "Basic Configuration", enter username, password and host (MSSQL
> pipe) for database, confirm with "update localconf.php"
> 10 - Back to "Basic Configuration", all databases on host are shown in drop
> down list, database "typo" is selected
> 11 - Enter Database Analyzer, it says "connected to SQL database
> successfully, database typo has 0 tables". Click on "COMPARE" to update
> required tables, a lot of "CREATE TABLE" statements are displayed,
> automatically ticked and confirmed with "Write to database"
> 12 - "Changing fields" is proposed, but not done as I think this might be a
> problem between typo3 and MSSQL
> 13 - Database Analyzer: Select "IMPORT" on Dump static data; select tick box
> "Import the whole file CURRENT_STATIC directly", confirm with "Write to
> database"
> 14 - Database Analyzer: Create admin user (message: "User created")
> 15 - Login to backend of typo3 with freshly created admin user (working
> fine)
> 16 - Create new page (using wizard) and new page content on that page
> (regular text element)
> 17 - Select "Template", typo3 says "No Template"; select "template;
> BUSINESS" from drop down list, confirm with "Create template for a new
> site". "Are you sure?" is confirmed with OK, next page is confirmed with
> update
> 18 - Select "View", getting the mentioned error messages.
>
>
> Thanks for your help again,
>
> Stefan
>
>
> _______________________________________________
> TYPO3-english mailing list
> TYPO3-english at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-english
>
--
Ries van Twisk
Freelance Typo3 Developer
=== Private:
email: ries at vantwisk.nl
web: http://www.rvantwisk.nl/freelance-typo3.html
skype: callto://r.vantwisk
=== Work:
email: ries at livetravelguides.com
web: http://www.livetravelguides.com
More information about the TYPO3-english
mailing list