[freeside-devel] MYSQL Support

[Dale Hege]

> On Tue, May 28, 2002 at 05:27:09PM -0400, Dale Hege wrote:
>>
>> I have been working on the mysql patches and have the cust_main
>> searching worked out.
>
> That's good news!
>
>> I just want an opinion of how to generate a unique id for the
>> table names as you suggested in the ticket.
>> http://pouncequick.420.am/rt/Ticket/Display.html?id=300
>
> So, temporary tables don't work?  I'd try that first.  The MySQL manual
> (appendix 6.3) says "You can't use temporary tables more than once in
> the same query" but is unclear on whether that means you can't use
> multiple columns in WHERE clause.  If temporary tables work the whole
> unique naming thing is moot.

Temp tables do work but they are not removed until the DB connection is
closed. If you use connection caching and persistant connections you can
run into trouble. Everything works fine unless for some reason the script
fails to finish and drop the temp tables. I might just add a drop before I
try and create them just to make sure all the stuff from another run is
gone.
The concerns in the mysql manual don't seem to get in the way. For the
record I am testing with mysql 3.23.49 (with the InnoDB table type).
>
>> The only other concern I'd have would be on concurrent access; the
>> "temp1" tables would need to be constructed of a unique identifier.
>> Something unique plus the current process id should work to prevent
>> different apache instances from trampling on each other's tables.
>>
>> I have the pid included
>
> The pid is enough.
>
>> but whould like more since when running with
>> mod_perl there is a good chance that you will hit the same apache pid.
>
> Not concurrently, though.
>

True.

>> I have it working but from time to time I run into non unique tables.
>
> I don't know what you mean by this, but I suspect there's something
> else not quite right if the pid isn't enough to guarantee concurrent
> uniqueness.

Not sure. I may have run into the trouble before I added the pid. :)

-Dale