Re: B-tree index question
Hi Gary, Yeah... we thought about those. Sergei said: "you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows." So say key base is 200, log 200 (10^9) = 3.91 Splitting it into 10 smaller tables would make log 200 (10^9) = 3.47, which isn't a huge amount of difference I guess. Still, worth testing to see how it performs in practice I guess. Thanks for the tip :) Cheers, -Phil - Original Message - From: "Gary Richardson" <[EMAIL PROTECTED]> To: "Phil Bitis" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, October 21, 2004 2:45 AM Subject: Re: B-tree index question If you are using MyISAM tables, have you thought about using MERGE tables instead? You could partition your table into several smaller tables. I don't know how the performance would be on a billion record table, but from my understanding it would shrink your index down. http://dev.mysql.com/doc/mysql/en/MERGE.html On Wed, 20 Oct 2004 11:09:43 +0100, Phil Bitis <[EMAIL PROTECTED]> wrote: Thanks for the informative reply Sergei, We're actually just using an INT field at the moment, we were going to move over to BIGINT when we start using 64-bit MySQL (soon). Do you know where I should look for information on writing our own table handler? Thanks, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
From: "Sergei Golubchik" <[EMAIL PROTECTED]> But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. Hi again, does the key page size differ depending on the type of the column (BIGINT, INT, etc)? Is there any way I can work out the key page size, or configure it? Cheers, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Thanks for the informative reply Sergei, We're actually just using an INT field at the moment, we were going to move over to BIGINT when we start using 64-bit MySQL (soon). Do you know where I should look for information on writing our own table handler? Thanks, -Phil - Original Message - From: "Sergei Golubchik" <[EMAIL PROTECTED]> To: "Phil Bitis" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, October 20, 2004 9:23 AM Subject: Re: B-tree index question Hi! On Oct 23, Phil Bitis wrote: Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. Corect. But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? Hmm, MyISAM can only do B-tree indexes. It won't be easy to add a completely different index algorithm to it. Writing your own table handler could be easier. In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Right, assuming all records have the same length, you can just write nth record at the offest n * recordsize on inserts, and use the value of n as a key on reads. Of course, it's a very specialized storage engine, not that much of general use - but it's very specialized to handle your case, so it can be the fastest solution. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Thanks for the reply. We're actually using DELAY_KEY_WRITE on our tables. The manual says (7.2.15): Declaring a MyISAM table with the DELAY_KEY_WRITE=1 table option makes index updates faster because they are not flushed to disk until the table is closed. Does this work with all types of indexes, including primary keys? - Original Message - From: "mos" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 20, 2004 4:20 AM Subject: Re: B-tree index question Phil, The fastest method to load data into a table is to use "Load Data Infile". If the table is empty when the command is executed, then the index is not updated until after the command completes. Otherwise if you are loading a lot of data, you may want to drop the index and rebuild it later. Unfortunately "Alter Table table_name disable keys" won't work on unique indexes (primary). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
B-tree index question
Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Thanks in advance, -Phil
Looking up duplicate record or adding new unique record
CREATE TABLE tbllayer ( LayerID int(11) NOT NULL default '0', LayerSize int(11) NOT NULL default '0', IceTypeID int(11) NOT NULL default '0', Fingerprint char(16) binary default NULL, PRIMARY KEY (LayerID), UNIQUE KEY Fingerprint (Fingerprint), KEY IceTypeID (IceTypeID) ) TYPE=MyISAM; We have an internet monitoring application which stores objects in the above table, with the fingerprint an MD4 of the object. In general about 30% of the time an object monitored is already in the table, in which case we don't want to re-insert it, we just want to find out it's ID. The percentage may vary between 10% and 50% though. Currently we have a cache in our application which works like this: - object is monitored and its fingerprint taken - is the fingerprint in the cache? if so, take its ID from there - if not, do a SELECT on the table - if a match is found add it to the cache and use its ID - if not, INSERT the record into the tablem use its ID and possibly add it to the cache too Ok. In general, is it better to: - do a SELECT to see if the record exists and if not INSERT it or - do an INSERT, and if it fails then do a SELECT to locate the record What about if the duplicate ratio is high or low? Cheers, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table
For what it's worth, I get the same problem with 4.0.13, and have posted the same question a few times with no response. It works fine at the command line, but not through the mysql++ API. It doesn't work on my home/work machines (both running XP), though my colleague doesn't experience the problem at all even though he's using 4.0.13 on 2000/XP. - Original Message - From: "Russell R Snella" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 23, 2003 5:57 PM Subject: Create Temporary Table > Hello, > > Mysql Version 4.0.11a-gamma > > When I try and run the following query > > > create temporary table russ1 as > > select rcust.* > > from rcust; > > > and I receive the error message: > > Error Code : 1044 > > Access denied for user: '[EMAIL PROTECTED]' to database 'customer' > > (0 ms taken) > > > > I can run the query from the command line of the server and the query > works correctly. > > I have run the query GRANT ALL PRIVILEGES ON customer.* TO > [EMAIL PROTECTED] > > > > In addition, I flushed the privileges, and I stopped and restarted the > database. > > > > Thank you, for you help in advance. > > > > > > Russell R. Snella > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT : Benchmark
Does your university have a webpage indicating what advice is acceptable and not considered plagarism? - Original Message - From: "Antonio Jose Rodrigues Neto" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, July 22, 2003 9:54 PM Subject: URGENT : Benchmark > Help me ... > > Antonio Jose Rodrigues Neto <[EMAIL PROTECTED]> wrote:Hi All, > > I almost finish my MSC Thesis and my thesis is iSCSI Performance. I would like to make tests using Fibre Channel and iSCSI with MYSQL. Please I will need informations (cookbook) how does implement tunning on MySQL - Linux RedHat 9. > > I install sql-bench and I ran tests against iscsi and nfs (just a test) and generate the files RUN-iscsi and RUN-nfs. How does use the tool compare-results? > > What book (the best book) do you recommend for me about mysql? > > Please help me? > > > - > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > - > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More duhh! questions
I've been having the same problem, using mysql++ with mysql 4.0.13. It works just fine entered into mysql-front or mysql.exe, but not through mysql++ Can I check the CREATE TEMPORARY TABLES privilege through the API? - Original Message - From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 16, 2003 8:43 AM Subject: Re: More duhh! questions > "Degan, George E, JR, MGSVC" <[EMAIL PROTECTED]> wrote: > > I am attempting to create a temporary table to do a complex query and I get an error: > > error 1044: Access denied for user: '@localhost' to database 'shopsample' > > what can I do to keep this from happening? I am using the production version of mySQL 4.0.13 in windows 2000. > > Does user have CREATE TEMPORARY TABLES privilege? > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Victoria Reznichenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
Yeah, put LIMIT 20 on the end. - Original Message - From: "Jim McAtee" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, July 14, 2003 11:12 PM Subject: SELECT TOP > What's wrong with the following query? The application used to use Access via > ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. > > // Return last 20 searches > SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname > FROM history h > INNER JOIN servers s ON h.serverid = s.serverid > WHERE h.employeeid = 9 > ORDER BY h.historyid DESC > > Works fine once I remove the "TOP 20" from the query. If this isn't supported, > is there an equivalent? > > Thanks, > Jim > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This list
Might it be worth looking at the mailing list manager software for this list? ACCU's mailing lists use Majordomo and add this line to the rfc822 headers: Reply-To: [EMAIL PROTECTED] You can still see the sender's email address if you want to reply directly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving insertion performance by locking tables
Is there a limit to the number of records I can insert in a multiple-value insert? - Original Message - From: "Rudy Metzger" <[EMAIL PROTECTED]> To: "Phil Bitis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, July 14, 2003 10:18 AM Subject: RE: Improving insertion performance by locking tables >From what I know is, that MySQL always locks the MyISAM table before you insert, update or delete something from it. So the key here is not so much if you should lock the table, but how you insert the data (single inserts vs multi inserts). Multi inserts are the way to go. By locking the table you actually would loose time. /rudy -----Original Message- From: Phil Bitis [mailto:[EMAIL PROTECTED] Sent: zaterdag 12 juli 2003 19:00 To: [EMAIL PROTECTED] Subject: Improving insertion performance by locking tables Hello, We've got an application which does many multiple-value inserts to different tables. Is it worth locking a table before doing a multiple-value insert with say 50 records? If so, what is the number of records that makes it worthwhile? If not, is it worth locking a table before doing 2 seperate such inserts? How do these locks affect select statements involving the locked tables? Thanks in advance, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help!!!
Hiya. I take it you mean ancestors rather than descendants.
For finding descendants I've been using tables like this:
ID ParentIDLineage
1000/100
101100/100/101
102100/100/102
103101/100/101/103
104103/100/101/103/104
You can find children by using LEFT(). A problem with this scheme is you
need to know the maximum length of the IDs in the Lineage field. I've been
using 8 hex digits as the width of the IDs there just to be safe. Not sure
if this would work for what you need.
Another thing you could do is create another table to hold hierarchy
relationships.
The data above would appear in this table like so:
ID AncestorID
101 100
102 100
103 101
103 100
104 100
104 101
104 103
As you can see the deeper a record is in the hierarchy, the more records it
would generate.
- Original Message -
From: "bruce" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, July 14, 2003 2:29 AM
Subject: query help!!!
> Hi...
>
> I have a questiona problem actually!!!
>
> I'm trying to figure out how to determine the descendents of a given child
> would be. The table defs are...:
>
> CREATE TABLE ItemTbl
> (
> id int(5) NOT NULL auto_increment,
> itemname varchar(25) NOT NULL default '',
> unique (itemname),
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
>
> #
> # assettypetbl
> #
> # b. douglas july 10,03
> # added status. allows user from web to modify the asset obj
> # type. user is able to set items within an asset type
> # to be added/deleted from asset type.
> #
> #
> # Used to store the name/parentid of a given item. the table is used
> # in conjunction with the ItemTbl. This table permits duplicate
> # item names, as a given item can be a child of multiple parent items.
> # In other words, the item can belong to multiple item structures
> #
> #
> # The table contains the following information:
> #
> # itemname varchar(25) NOT NULL default '',(this gets the image/thumb)
> # parent_id int(5) NOT NULL default '',
> #
>
> CREATE TABLE atbl
> (
> itemname varchar(25) NOT NULL default '', #(this gets the image/thumb)
> parent_id int(5) NOT NULL default '',
> ) TYPE=MyISAM;
>
>
> #
> # test data
> #
> insert into itemtbl (itemname) values ('tire');
> insert into itemtbl (itemname) values ('rim');
> insert into itemtbl (itemname) values ('hub');
> insert into itemtbl (itemname) values ('wheel');
> insert into itemtbl (itemname) values ('car');
> insert into itemtbl (itemname) values ('engine');
> insert into itemtbl (itemname) values ('window');
> insert into itemtbl (itemname) values ('airplane');
>
> insert into atbl (itemname, parent_id) values ('tire', 4);
> insert into atbl (itemname, parent_id) values ('rim', 4);
> insert into atbl (itemname, parent_id) values ('hub', 4);
> insert into atbl (itemname, parent_id) values ('wheel', 5);
> insert into atbl (itemname, parent_id) values ('car', 0);
> insert into atbl (itemname, parent_id) values ('engine', 5);
> insert into atbl (itemname, parent_id) values ('window', 5);
> insert into atbl (itemname, parent_id) values ('airplane', 0);
> insert into atbl (itemname, parent_id) values ('wheel', 8);
>
> The atbl contains the parent item relationships... for the items...
>
> Baisically, I need a way of determining what the
> parents/grandparents/great-grandparents/etc... are for a given item My
> concern is that a user might add an item and a parent, and I would get
into
> an item being a parent of itself...
>
> So..does anyone have a good way that I could create a query to generate
the
> descendents of a given item??
>
> Thanks for any pointers/information that might help!!!
>
> Regards,
>
> Bruce
> [EMAIL PROTECTED]
> (925) 866-2790
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mySQL support a boolean data type?
Presumably if you don't specify a display size it defaults to the maximum. I'm just quoting from the manual, have a look at "6.2 Column Types" - Original Message - From: <[EMAIL PROTECTED]> To: "Phil Bitis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, July 13, 2003 7:40 PM Subject: Re: does mySQL support a boolean data type? ehm, why do i say INT(1) then, and not just INT? but one could also take a 1 char type and perform some AND and OR on that one by hand. it's not really fast but it's possible right now. and it should be possible to convert the table later (for version 5.1 maybe) and with a little abstraction in the programme code, it shouldn't be too hard. -yves -Ursprüngliche Nachricht- Von: "Phil Bitis" <[EMAIL PROTECTED]> An: <[EMAIL PROTECTED]> Gesendet: Sonntag, 13. Juli 2003 19:57 Betreff: Re: does mySQL support a boolean data type? > int(1) takes up 4 bytes worth of space, and just displays 1 character. > BIT or TINYINT(1) take up 1 byte. > > - Original Message - > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, July 13, 2003 6:05 PM > Subject: Re: does mySQL support a boolean data type? > > > why don't you use int(1) and set it to 0 or 1? > > -yves > > > -Ursprüngliche Nachricht- > Von: "Dan Anderson" <[EMAIL PROTECTED]> > An: <[EMAIL PROTECTED]> > Gesendet: Sonntag, 13. Juli 2003 18:41 > Betreff: does mySQL support a boolean data type? > > > > I ran a search through the mySQL manual and google and could not find a > > satisfactory answer. Does mySQL support the declaration of a boolean > > data type? Currently I am using VARCHAR(6)s with either 'TRUE' or > > 'FALSE' and would like to cut down on the storage. > > > > Thanks in advance, > > > > Dan Anderson > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mySQL support a boolean data type?
int(1) takes up 4 bytes worth of space, and just displays 1 character. BIT or TINYINT(1) take up 1 byte. - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, July 13, 2003 6:05 PM Subject: Re: does mySQL support a boolean data type? why don't you use int(1) and set it to 0 or 1? -yves -Ursprüngliche Nachricht- Von: "Dan Anderson" <[EMAIL PROTECTED]> An: <[EMAIL PROTECTED]> Gesendet: Sonntag, 13. Juli 2003 18:41 Betreff: does mySQL support a boolean data type? > I ran a search through the mySQL manual and google and could not find a > satisfactory answer. Does mySQL support the declaration of a boolean > data type? Currently I am using VARCHAR(6)s with either 'TRUE' or > 'FALSE' and would like to cut down on the storage. > > Thanks in advance, > > Dan Anderson > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mySQL support a boolean data type?
You can use "BIT" or "BOOL", but these are currently just synonyms for TINYINT(1). Still your best bet though. The manual says under new features planned for 5.1: "Optimise BIT type to take 1 bit (now BIT takes 1 char)" - Original Message - From: "Dan Anderson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, July 13, 2003 6:21 PM Subject: Re: does mySQL support a boolean data type? > > why don't you use int(1) and set it to 0 or 1? > > Storage space is an issue because I am designing a very large database > for a client. Every byte I save per row will translate into many many > megs. So if all I need is a single bit for true or false I want to get > as close to that single bit as possible. (Although most bools end up > being more then a single bit because of architecture issues). > > So, to put a long story short, I am trying to make every column's > internal data structure as tiny as possible. > > -Dan > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Improving insertion performance by locking tables
Hello, We've got an application which does many multiple-value inserts to different tables. Is it worth locking a table before doing a multiple-value insert with say 50 records? If so, what is the number of records that makes it worthwhile? If not, is it worth locking a table before doing 2 seperate such inserts? How do these locks affect select statements involving the locked tables? Thanks in advance, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table problem
> > Further to this, I should point out everything works fine in mysql-front
> > or at the mysql console.
> >
> > The problem shows up when using mysql++, a BadQuery exception is thrown.
> >
> > query.reset();
> > query << "CREATE TEMPORARY TABLE " << sTemporary << " TYPE=HEAP
> > MAX_ROWS=1 " << subselect;
> >
> > try
> > {
> > query.parse();
> > query.execute();
> > }
> >
>
> This is simple to solve.
>
> As recommended in MySQL++ manual, use stream only for queries returning
result set. For the queries like above use exec() method.
Point taken, changed it to just use exec() and the same problem occurs. It
was working previously with the code above though (mysql 3), and it works
fine entered at the mysql console.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table problem
Further to this, I should point out everything works fine in mysql-front or
at the mysql console.
The problem shows up when using mysql++, a BadQuery exception is thrown.
query.reset();
query << "CREATE TEMPORARY TABLE " << sTemporary << " TYPE=HEAP
MAX_ROWS=1 " << subselect;
try
{
query.parse();
query.execute();
}
----- Original Message -
From: "Phil Bitis" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, July 09, 2003 12:41 PM
Subject: Create Temporary Table problem
> As a way of getting around the lack of subselect (I'm aware this is coming
> soon) we're parsing sql queries, running subselects and storing their
> results in a temporary table and replacing the subselect in the sql with
the
> temporary table name.
>
> This has been working fine, but on upgrading to v4.0.13 the "CREATE
> TEMPORARY TABLE " part is failing with this error:
>
> Access denied for user: '@localhost' to database 'uclan_database'
>
> Any ideas?
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Temporary Table problem
As a way of getting around the lack of subselect (I'm aware this is coming soon) we're parsing sql queries, running subselects and storing their results in a temporary table and replacing the subselect in the sql with the temporary table name. This has been working fine, but on upgrading to v4.0.13 the "CREATE TEMPORARY TABLE " part is failing with this error: Access denied for user: '@localhost' to database 'uclan_database' Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
