[ic] Query problem with shared database

Jamie Neil interchange-users@icdevgroup.org
Tue Jun 3 09:18:00 2003


> -----Original Message-----
> From: interchange-users-admin@icdevgroup.org
> [mailto:interchange-users-admin@icdevgroup.org]On Behalf Of Jamie Neil
> Sent: 03 June 2003 13:31
> To: interchange-users@icdevgroup.org
> Subject: [ic] Query problem with shared database
>
>
> Hi All,
>
> While testing my (hopefully soon to go live) site, I've just noticed a
> serious problem relating to my use of the query tag with a shared
> database.
>
> My main site uses a single mysql database to store all it's data, and this
> works fine. I also have another site that gets it's product, variants and
> inventory (plus some other) tables from the same database, but uses a
> separate database to store it's users, transactions etc.
>
> The second site uses the separate database as it's default
> (SQLDSN, SQLUSER
> and SQLPASS), and the tables which are shared are explicitly
> defined in the
> corresponding .mysql file.
>
> I _thought_ this was working fine, until I realised that some of
> my queries
> on the second site were getting their data from the wrong database.
>
> For example the second site's merchandising table is defined in
> merchandising.mysql as:
>
> 	Database  merchandising  merchandising.txt __SQLDSN__
>
> and the admin interface displays and accesses this table with no problems.
>
> But when my promo component does:
>
> 	[query arrayref=main
>       	sql="
>             	SELECT sku,timed_promotion,start_date,finish_date
>             	FROM merchandising
>             	WHERE featured = '[control promo_type featured]'
> 	"]
>
>
>
> it returns data from the merchandising table of the main site!
>
> I have double checked all my config files and they seem fine. The only
> explaination I can think of is that an existing connection to the main
> site's sql database is being incorrectly reused to issue the query, and
> because the merchandising table exists in both databases it is returning
> valid data.
>
> Is this an IC bug, a limitation of the DBI modules, or just something I'm
> doing wrong that I haven't spotted yet? Can anyone confirm this behavoir?
>
> I'm using IC v4.9.7-200305260658, DBI v1.37 and MySQL v3.23.56
>
> Jamie Neil
> Versado I.T. Services Ltd.
>

I've managed to work around the problem by tightening up the database
permissions. Previously, the second site could access all the tables in the
main database, whereas now it can only access the shared tables.

Although this solves my problem, I still think there is a bug here (although
it could be DBI or MySQL rather than IC). IC is still trying to access the
wrong table initially - it is only the database permissions that prevent it
making it retry using the correct DSN.

One to be aware of anyway.

Jamie