replication problem if network delay

[balaraju mandala]

Hi All,

I need small help from you. I am using Replication, i am using this
technique to give live support to Support team. But my Server and Slave are
not in LAN. So some times i am getting network delay. Can anybody tell me a
good suggestion to over come this.

regards,
balaraju

Re: Trouble with using "IN" for a sub-query statement

[Chris]

Chris White wrote:

On Monday 07 August 2006 12:13 pm, William DeMasi wrote:

I meant to have it say :

"select * from c2iedm_dev2.act where act_id IN (select obj_act_id from
c2iedm_dev2.act_functl_assoc where subj_act_id =24);"


What does the output of (the subselect):

select obj_act_id
from c2iedm_dev2.act_functl_assoc
where subj_act_id =24;

show?


Sorry about that, typo on my part, but I have tried it as above and I get
the error I mentioned.


What version of mysql are you using?

select version();

subselects don't work for versions before v5.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqld won't start

[Chris]

Charlie Stanton wrote:

From: root
To: mysql@lists.mysql.com
Subject: mysqld won't start

 >Description:
   In an attempt to upgrade to MySQL 5.0.24 it was necessary to use
   "rpm -e ..." to remove version 5.0.22 then "rpm -U ..." to install
   the shared,server and client modules.
   When I do a "rpm -qa" it shows that the new modules have been installed.
  When the system tries to start mysqld the following message is 
reported:

   Starting MySQLCouldn't find MySQL manager or server [FAILED]


What shows up in your /var/log/messages or /var/log/mysql*.log files ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL App development

[ravi.karatagi]

Hi All,

I need to develop an utility in C++ (don't want to use
mysql++ lib)  which provides services to other modules in the system
like createTable( ), createRow( ),

updateAttr( ) Etc. wherein these APIs are called dynamically by the
other modules to do the DB operations.



Is there anything called "MySQL application development environment
guide", as it is there DB2 (IBM) which explains how to work with MySQL
and C++ together?

Or can I refer DB2 App guide?



Please Help me



Thanks,

Regards,

Ravi K






The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email.

www.wipro.com

Re: Transactions and testing an Insert statement

[Chris]

Chris W. Parker wrote:

Hello,

Me again. Excuse for sending two questions so closely together.

I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).

Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?

I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.

As I imagine it in my head: 


TEST INSERT INTO `table` VALUES ('value', 'value');


You can't "test" an insert like this but you could do:

begin;
insert into table values(value1, value2);
rollback;

which does mean you need innodb tables. That will rollback any changes 
that the insert does, however I'm not sure what happens to an 
auto_increment field in that situation (ie does the value get rolled 
back or is it left incremented).


The mysql_query function in php returns a resource or failure, so you 
could do:


$result = mysql_query($my_query);
if (!$result) {
  echo "Query ${my_query} failed: " . mysql_error() . "";
} else {
  echo "Query ${my_query} worked!";
}

see php.net/mysql_query for more info.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Transactions and testing an Insert statement

[Chris W. Parker]

Hello,

Me again. Excuse for sending two questions so closely together.

I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).

Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?

I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.

As I imagine it in my head: 

TEST INSERT INTO `table` VALUES ('value', 'value');

And then get back a success or fail error code.

Using MySQL 4.1.


Thank you for your time,
Chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Delete one record, automatically delete other records...

[Chris W. Parker]

Chris 
on Monday, August 07, 2006 6:19 PM said:

> Foreign keys with an "on delete cascade" should do it.
> 
> http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html


Thanks everyone!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Delete one record, automatically delete other records...

[Chris]

Chris W. Parker wrote:

Hello,

I'm not sure if this is possible (or what it's called, and how to search
for it) at the db layer or if this has to be done at the application
layer... I would like to be able to delete one record in one table and
then automatically (without making an extra call to the db) delete other
records. If I'm using this term correctly, I think I'm trying to avoid
having orphan records.

In my specific case I want to delete a product from my database and then
automatically delete all associated records like the category and
manufacturer relationships.

BTW, I'm using MySQL 4.1.20.

I'm not really looking for code snippets but rather the name for this
idea and/or links so I can rtfm. :)


You'll need to use innodb tables.

Foreign keys with an "on delete cascade" should do it.

http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Delete one record, automatically delete other records...

[Tim Lucia]

You want cascade deletion via foreign keys.

http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

is one such place to learn more.

Tim

> -Original Message-
> From: Chris W. Parker [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 07, 2006 9:11 PM
> To: mysql@lists.mysql.com
> Subject: Delete one record, automatically delete other records...
> 
> Hello,
> 
> I'm not sure if this is possible (or what it's called, and how to search
> for it) at the db layer or if this has to be done at the application
> layer... I would like to be able to delete one record in one table and
> then automatically (without making an extra call to the db) delete other
> records. If I'm using this term correctly, I think I'm trying to avoid
> having orphan records.
> 
> In my specific case I want to delete a product from my database and then
> automatically delete all associated records like the category and
> manufacturer relationships.
> 
> BTW, I'm using MySQL 4.1.20.
> 
> I'm not really looking for code snippets but rather the name for this
> idea and/or links so I can rtfm. :)
> 
> 
> 
> Thanks,
> Chris.
> 
> --
> 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: Delete one record, automatically delete other records...

[Daniel da Veiga]

On 8/7/06, Chris W. Parker <[EMAIL PROTECTED]> wrote:

Hello,

I'm not sure if this is possible (or what it's called, and how to search
for it) at the db layer or if this has to be done at the application
layer... I would like to be able to delete one record in one table and
then automatically (without making an extra call to the db) delete other
records. If I'm using this term correctly, I think I'm trying to avoid
having orphan records.

In my specific case I want to delete a product from my database and then
automatically delete all associated records like the category and
manufacturer relationships.

BTW, I'm using MySQL 4.1.20.

I'm not really looking for code snippets but rather the name for this
idea and/or links so I can rtfm. :)



This is called foreign keys constraints, MySQL has that on InnoDB,
take a look at:

http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

Hope that helped.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL SQL statistics

[Daniel da Veiga]

On 8/7/06, 古雷 <[EMAIL PROTECTED]> wrote:

Thanks a lot for your reply.

I need know SQLs that consume most resources(CPU time, disk IO etc.) of 
database server.



If its an application, you can use EXPLAIN to find out how MySQL is
processing the most used queries, and use this knowledge to optimize
the queries, other than that, AFAIK there's no such feature in MySQL.


Sometimes the same SQL could be executed many times to hold most time of CPU.
Sometimes executed only one time.


Sorry, I missed that, couldn't understand what you mean.



Maybe I can say I am looking for something like STATSPACK of Oracle for MySQL.



Now I'm pretty sure there's no such thing for MySQL, but someone else
can point me wrong.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Delete one record, automatically delete other records...

[Chris W. Parker]

Hello,

I'm not sure if this is possible (or what it's called, and how to search
for it) at the db layer or if this has to be done at the application
layer... I would like to be able to delete one record in one table and
then automatically (without making an extra call to the db) delete other
records. If I'm using this term correctly, I think I'm trying to avoid
having orphan records.

In my specific case I want to delete a product from my database and then
automatically delete all associated records like the category and
manufacturer relationships.

BTW, I'm using MySQL 4.1.20.

I'm not really looking for code snippets but rather the name for this
idea and/or links so I can rtfm. :)



Thanks,
Chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL SQL statistics

[古雷]

Thanks a lot for your reply.

I need know SQLs that consume most resources(CPU time, disk IO etc.) of 
database server.

Sometimes the same SQL could be executed many times to hold most time of CPU.
Sometimes executed only one time.

Maybe I can say I am looking for something like STATSPACK of Oracle for MySQL.

Regards,

Gu Lei
- Original Message - 
From: "Daniel da Veiga" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, August 08, 2006 12:43 AM
Subject: Re: MySQL SQL statistics


> On 8/7/06, 古雷 <[EMAIL PROTECTED]> wrote:
>> Hello everyone:
>>
>> I want to get some SQL statistics including executions, CPU time for each 
>> SQL Statement.
> 
> The number of each type of query can be found using the "show status
> like 'Com%';", I don't believe there's a way to know how much CPU time
> MySQL is using for each query, for once, it would be different on each
> OS MySQL can run (there are a lot), also, it would take CPU time to
> check that ;-). I can't see any advantage of knowing this... Depending
> on your OS, you can check the whole MySQL process resources with
> specific commands like "top" on *ix or CTRL+ALT+DEL on windows.
> 
> -- 
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -BEGIN GEEK CODE BLOCK-
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> --END GEEK CODE BLOCK--
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

Re: Is MySQL 5.0 MyISAM table format backward compatible with 4.1?

[mos]

At 05:00 PM 8/7/2006, you wrote:

Mike, if this data is at all important to you, I'd highly recommend
doing your testing on copies of your data files, rather than your
originals.

If it's not important, then I'd go ahead and try it.  The MyISAM
storage engine is pretty mature and stable.  For safety's sake once
you get it back to 4.1 I'd suggest running CHECK TABLE tablename
EXTENDED on each to ensure integrity.

I've always had success going forward with releases, but haven't ever
tried going back that I recall.

Dan


That's what I ended up doing, at least for one table. All of the data for 
the other tables can be regenerated so if I do lose it, it's not the end of 
the world.


Have you noticed any speed improvements in MySQL 5.0? The only reason I'd 
want to upgrade is to improve the join or sort speeds, or rebuilding the 
index speeds. I'm trying MySQL 5.0 out on a Win2003 server box with 4gb of 
RAM. I'm still tweaking the mysql settings and I haven't noticed a 
difference yet.


Mike




On 8/7/06, mos <[EMAIL PROTECTED]> wrote:

I'm planning on installing MySQL 5.0 today and will try rebuilding some of
the indexes for one of my tables to conduct some benchmarks. I will
probably reboot back to MySQL 4.10 and continue using the database. Have
the table formats changed from 4.10 to 5.0? Or can I create/modify tables
in 5.0 and then use the same tables in 4.0?

Are there any speed improvements in 5.0 compared to 4.1?

TIA
Mike

--
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]

Load Data Infile and newlines

[Mark Nienberg]

I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 
table.  Some of the fields contain text that has newline characters in it.  After 
reading the manual to learn how special characters are treated, I altered the csv 
file so newlines are represented by '\\n', that is two backslashes followed by a 
lowercase n.


After loading the file, I find that the text fields do indeed contain '\n' as I hoped 
(one of the backslashes was stripped during import).  But for some reason these 
newlines aren't treated as such by mysql or php.  For example, the php function nl2br 
does not recognize and substitute them.


I have other similar tables in the same database that I successfully constructed 
using the same method, but that was with a previous version of mysql (4.something). 
Something is different now.


The command I used was:

mysql> load data infile '/share/store/library.csv' into table library
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n';

Any help appreciated.
Mark


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database design question

[David T. Ashley]

On 8/7/06, James Tu <[EMAIL PROTECTED]> wrote:


If I create a table for each user (I can potentially have hundreds of
thousands of users), will MySQL be able to handle this?
If I just have one table, I could potentially have millions of
records in one table.  Will MySQL be able to handle this?



Hi James,

There are really two elements to this problem.

The first element is how quickly MySQL can extract the messages you want
from a large table.  This requires that you know in advance the type of
queries you're going to do (all messages by one user?  all messages in a
certain time window?) and be sure that these queries are approximately O(log
N) rather than O(N) or worse.  You will need to change your database design
to fit the queries that you'll be doing.  O(log N) queries would generally
be characterized by the fields you're searching or sorting on being key
fields (i.e. MySQL makes an index or BTREE or whatever it makes rather than
having to go through the entire table linearly).

The second element is data presentation.  In developing web applications at
least, if the first data on a page is displayed by the browser while the
rest of the data is loading, the user perceives the load as being faster
than it really is because the user is looking at the first data while the
rest is loading.  So, to make things more "snappy", you might do more than
one query to avoid large result sets.

Dave.

Re: Is MySQL 5.0 MyISAM table format backward compatible with 4.1?

[Dan Buettner]

Mike, if this data is at all important to you, I'd highly recommend
doing your testing on copies of your data files, rather than your
originals.

If it's not important, then I'd go ahead and try it.  The MyISAM
storage engine is pretty mature and stable.  For safety's sake once
you get it back to 4.1 I'd suggest running CHECK TABLE tablename
EXTENDED on each to ensure integrity.

I've always had success going forward with releases, but haven't ever
tried going back that I recall.

Dan


On 8/7/06, mos <[EMAIL PROTECTED]> wrote:

I'm planning on installing MySQL 5.0 today and will try rebuilding some of
the indexes for one of my tables to conduct some benchmarks. I will
probably reboot back to MySQL 4.10 and continue using the database. Have
the table formats changed from 4.10 to 5.0? Or can I create/modify tables
in 5.0 and then use the same tables in 4.0?

Are there any speed improvements in 5.0 compared to 4.1?

TIA
Mike

--
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: MySQL Load Balancing

[Peter Zaitsev]

Ed Pauley II wrote:


  


Continuent's m/cluster will not work for me as it does not allow 
replication across a WAN. We have an offsite backup that needs to be in 
the replication (2-way to make switching back and forth easy) chain. I 
am thinking of a master, slave setup at each location where the masters 
continue to replicate both ways and then each replicates to it's own 
slaves. I would like to load balance these slaves on each end. I have 
not been able to find an appliance that will balance the reads for me. I 
have looked into possible solutions such as Sequoia, which I know would 
require a different setup. Is anyone actually using Sequoia? Does anyone 
use an appliance for load balancing MySQL? LVS seems like a good 
possibility but I would rather have an out-of-box solution since I will 
be repeating this setup at a remote location.




Sorry,  did not try Sequoia so can't tell how well it works


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Is MySQL 5.0 MyISAM table format backward compatible with 4.1?

[mos]

I'm planning on installing MySQL 5.0 today and will try rebuilding some of 
the indexes for one of my tables to conduct some benchmarks. I will 
probably reboot back to MySQL 4.10 and continue using the database. Have 
the table formats changed from 4.10 to 5.0? Or can I create/modify tables 
in 5.0 and then use the same tables in 4.0?


Are there any speed improvements in 5.0 compared to 4.1?

TIA
Mike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database design question

[James Tu]

Wow, I didn't know that can happen.  I'll definitely take that into  
consideration.  Thanks Brent.


On Aug 7, 2006, at 4:26 PM, Brent Baisley wrote:

If you're looking to be put at ease, I've got a table with 250+  
million records, but I've heard of people with larger tables than  
that on this list.
You might want to also looking into using a compound primary key,  
meaning userid+messageid.


Something like this:
CREATE TABLE `message` (
 `userid` int unsigned NOT NULL default '',
 `messageid` int unsigned NOT NULL auto_increment,
 `message` text,
...
 PRIMARY KEY  (`userid`,`messageid`)
)

What that does is give each user their own incrementing message  
id.  Then you can do things like allow users to enter a message id  
directly with a number that would be easy for them to remember.  
Just an idea.


- Original Message - From: "James Tu" <[EMAIL PROTECTED]>
To: 
Sent: Monday, August 07, 2006 4:11 PM
Subject: Re: Database design question



Thanks everyone.
Now I feel confident that one table will be fine (Tripp's stat of  
30  million records put me at ease :) ).


Cheers,
-James

On Aug 7, 2006, at 4:08 PM, John Meyer wrote:


One table,
USERS

Another table
MESSAGES
With a foreign key referencing users.
Maybe a second foreign key referencing the destinating user as well.


-Original Message-
From: James Tu [mailto:[EMAIL PROTECTED]
Sent: Monday, August 07, 2006 1:56 PM
To: mysql@lists.mysql.com
Subject: Database design question

I want to design a database for lots of users.  Each user will  
be  managing
their own messages.  Does it make sense to create a table for  
each  user

after they've registered?
Or should I just create one MESSAGES table and store messages  
there  keyed

off of their user_id?

If I create a table for each user (I can potentially have  
hundreds of

thousands of users), will MySQL be able to handle this?
If I just have one table, I could potentially have millions of   
records in

one table.  Will MySQL be able to handle this?

My gut feel is that MySQL will be fine with one table and  
millions of

records.

Thanks.
-James


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?  
[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?  
[EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database design question

[James Tu]

Thanks everyone.
Now I feel confident that one table will be fine (Tripp's stat of 30  
million records put me at ease :) ).


Cheers,
-James

On Aug 7, 2006, at 4:08 PM, John Meyer wrote:


One table,
USERS

Another table
MESSAGES
With a foreign key referencing users.
Maybe a second foreign key referencing the destinating user as well.


-Original Message-
From: James Tu [mailto:[EMAIL PROTECTED]
Sent: Monday, August 07, 2006 1:56 PM
To: mysql@lists.mysql.com
Subject: Database design question

I want to design a database for lots of users.  Each user will be  
managing
their own messages.  Does it make sense to create a table for each  
user

after they've registered?
Or should I just create one MESSAGES table and store messages there  
keyed

off of their user_id?

If I create a table for each user (I can potentially have hundreds of
thousands of users), will MySQL be able to handle this?
If I just have one table, I could potentially have millions of  
records in

one table.  Will MySQL be able to handle this?

My gut feel is that MySQL will be fine with one table and millions of
records.

Thanks.
-James


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Database design question

[John Meyer]

One table, 
USERS

Another table
MESSAGES
With a foreign key referencing users.
Maybe a second foreign key referencing the destinating user as well.


-Original Message-
From: James Tu [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 07, 2006 1:56 PM
To: mysql@lists.mysql.com
Subject: Database design question

I want to design a database for lots of users.  Each user will be managing
their own messages.  Does it make sense to create a table for each user
after they've registered?
Or should I just create one MESSAGES table and store messages there keyed
off of their user_id?

If I create a table for each user (I can potentially have hundreds of
thousands of users), will MySQL be able to handle this?
If I just have one table, I could potentially have millions of records in
one table.  Will MySQL be able to handle this?

My gut feel is that MySQL will be fine with one table and millions of
records.

Thanks.
-James


--
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: Database design question

[Philip Hallstrom]

I want to design a database for lots of users.  Each user will be managing 
their own messages.  Does it make sense to create a table for each user after 
they've registered?
Or should I just create one MESSAGES table and store messages there keyed off 
of their user_id?


If I create a table for each user (I can potentially have hundreds of 
thousands of users), will MySQL be able to handle this?
If I just have one table, I could potentially have millions of records in one 
table.  Will MySQL be able to handle this?


My gut feel is that MySQL will be fine with one table and millions of 
records.


One table, with a user_id field.  If you're worried about searching 
through millions of records, perhaps you could have archival tables that 
don't normally get searched and move messages from one to other after they 
get "old"...


-p

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Database design question

[James Tu]

I want to design a database for lots of users.  Each user will be  
managing their own messages.  Does it make sense to create a table  
for each user after they've registered?
Or should I just create one MESSAGES table and store messages there  
keyed off of their user_id?


If I create a table for each user (I can potentially have hundreds of  
thousands of users), will MySQL be able to handle this?
If I just have one table, I could potentially have millions of  
records in one table.  Will MySQL be able to handle this?


My gut feel is that MySQL will be fine with one table and millions of  
records.


Thanks.
-James


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with using "IN" for a sub-query statement

[Chris White]

On Monday 07 August 2006 12:13 pm, William DeMasi wrote:
> I meant to have it say :
> > "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from
> > c2iedm_dev2.act_functl_assoc where subj_act_id =24);"

What does the output of (the subselect):

select obj_act_id
from c2iedm_dev2.act_functl_assoc
where subj_act_id =24;

show?

> Sorry about that, typo on my part, but I have tried it as above and I get
> the error I mentioned.

-- 
Chris White
PHP Programmer/DBlinds
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with using "IN" for a sub-query statement

[Chris White]

On Monday 07 August 2006 12:02 pm, William DeMasi wrote:
> The select statement I am trying to run is:
> "select * from c2iedm_dev2.act where act_id =(select obj_act_id from
> c2iedm_dev2.act_functl_assoc where subj_act_id =24);"

Well the problem I'm seeing is that you're not using IN anywhere in the SQL 
clause.

-- 
Chris White
PHP Programmer/DBaby
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Trouble with using "IN" for a sub-query statement

[William DeMasi]

The select statement I am trying to run is:
"select * from c2iedm_dev2.act where act_id =(select obj_act_id from
c2iedm_dev2.act_functl_assoc where subj_act_id =24);"

But I get the error below:
"You have an error in your SQL syntax.  Check the manual that corresponds to
your MySQL server version for the right syntax to use near 'select
obj_act_id from c2iedm_dev2.act_functl_assoc where subj_"

I have looked at the documentation for MySQL and this seems to be the
correct syntax for using "IN".

Could someone please let me know what is wrong?

Thank you.

William DeMasi



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Load Balancing

[Ed Pauley II]

Peter Zaitsev wrote:

On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote:
  
I am looking into a scale-out solution for MySQL. I have read white 
papers and searched the web but I can't find a load balancer that claims 
to work well for MySQL.  MySQL's white paper shows NetScaler in the 
scale-out stack but nothing on Citrix.com mentions MySQL. I also read 
that friendster wrote a custom script for NetScaler to work in a MySQL 
environment. I would rather not have to do that. Is there an out-of-box 
solution for load balancing MySQL. My understanding is that MySQL is a 
little more complicated than HTTP load balancing, which we already do 
with Coyote Point Equalizers. I have thought about LVS. Has anyone had 
any experience with load balancing MySQL? Any recommendations? Thanks in 
advance.



As some people mentioned there is "Continuent" solution, this is what
was Emic networks previously.  


If you're building solution on your own such as master and number of
slaves there are plenty of things to think, regarding load balancing,
for example if replication breaks for any reason of falls behind on one
of the slaves you might want to kick it up.   


For very basic setup you even can use DNS for load balancing, which does
not solve many of the problems describe. 

The same kind of simple load balancing is build in MySQL JDBC Driver. 


In general everyone seems to implement something on their own, working
well for their application. 





  


Continuent's m/cluster will not work for me as it does not allow 
replication across a WAN. We have an offsite backup that needs to be in 
the replication (2-way to make switching back and forth easy) chain. I 
am thinking of a master, slave setup at each location where the masters 
continue to replicate both ways and then each replicates to it's own 
slaves. I would like to load balance these slaves on each end. I have 
not been able to find an appliance that will balance the reads for me. I 
have looked into possible solutions such as Sequoia, which I know would 
require a different setup. Is anyone actually using Sequoia? Does anyone 
use an appliance for load balancing MySQL? LVS seems like a good 
possibility but I would rather have an out-of-box solution since I will 
be repeating this setup at a remote location.



--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqld won't start

[Charlie Stanton]

From: root
To: mysql@lists.mysql.com
Subject: mysqld won't start

>Description:
   In an attempt to upgrade to MySQL 5.0.24 it was necessary to use
   "rpm -e ..." to remove version 5.0.22 then "rpm -U ..." to install
   the shared,server and client modules.
   When I do a "rpm -qa" it shows that the new modules have been installed.
   
   When the system tries to start mysqld the following message is reported:

   Starting MySQLCouldn't find MySQL manager or server [FAILED]
   
   When attempting to start with "# mysqld_safe start" I got this report:

   Starting mysqld daemon with databases from /var/lib/mysql
   STOPPING server from pid file /var/run/mysqld/mysqld.pid
   [date] [time] mysqld ended
   
>How-To-Repeat:

   
>Fix:
   

>Submitter-Id:
>Originator:root
>Organization:

>MySQL support: email
>Synopsis:mysqld won't start after upgrading to 5.0.24
>Severity:critical
>Priority:high
>Category:mysql
>Class:support<[ sw-bug | doc-bug | change-request | support ] 
(one line)>
>Release:mysql-5.0.24-standard (MySQL Community Edition - Standard 
(GPL))


>C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>C++ compiler:  gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>Environment:
   
System: Linux DEVBOX 2.6.9-34.0.2.EL #1 Fri Jun 30 10:23:19 EDT 2006 
i686 i686 i386 GNU/Linux

Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc 
/usr/bin/cc

GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.5/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man 
--infodir=/usr/share/info --enable-shared --enable-threads=posix 
--disable-checking --with-system-zlib --enable-__cxa_atexit 
--disable-libunwind-exceptions --enable-java-awt=gtk 
--host=i386-redhat-linux

Thread model: posix
gcc version 3.4.5 20051201 (Red Hat 3.4.5-2)
Compilation info: CC='gcc'  CFLAGS='-O2 -g -pipe -m32 -march=i386 
-mtune=pentium4'  CXX='gcc'  CXXFLAGS='-O2 -g -pipe -m32 -march=i386 
-mtune=pentium4'  LDFLAGS=''  ASFLAGS=''

LIBC:
lrwxrwxrwx  1 root root 13 Mar 24 12:49 /lib/libc.so.6 -> libc-2.3.4.so
-rwxr-xr-x  1 root root 1438668 Feb  8 19:12 /lib/libc-2.3.4.so
-rw-r--r--  1 root root 2415476 Feb  8 18:50 /usr/lib/libc.a
-rw-r--r--  1 root root 204 Feb  8 18:02 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' 
'--with-server-suffix=-standard' '--without-embedded-server' 
'--with-innodb' '--with-archive-storage-engine' '--without-bench' 
'--with-zlib-dir=bundled' '--with-big-tables' '--enable-assembler' 
'--enable-local-infile' '--with-mysqld-user=mysql' 
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic' 
'--prefix=/' '--with-extra-charsets=complex' '--with-yassl' 
'--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' 
'--sysconfdir=/etc' '--datadir=/usr/share' 
'--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' 
'--includedir=/usr/include' '--mandir=/usr/share/man' 
'--enable-thread-safe-client' '--with-comment=MySQL Community Edition - 
Standard (GPL)' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -g -pipe -m32 
-march=i386 -mtune=pentium4' 'CXXFLAGS=-O2 -g -pipe -m32 -march=i386 
-mtune=pentium4' 'CXX=gcc' 'LDFLAGS='



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ODBC - MySQL 5 bug!

[David Blake]

Good day,
After a whole day of researching I finally found an answer for Ms Access not
displaying the columns of more than one table in a MySQL 5 view...
The bugfix was posted in a mysql forum  here:
http://forums.mysql.com/read.php?65,52721,100434#msg-100434
which contains a link to a download file... BUT IT NO LONGER allows one to
go in!

A MySQL representative mentioned that you would fix this error here:
http://forums.mysql.com/read.php?65,52721,91805#msg-91805

has anything happened ?
The above mentioned forum (and I)   are still waiting for an answer!
Thank you,
Dave

Re: MySQL SQL statistics

[Daniel da Veiga]

On 8/7/06, 古雷 <[EMAIL PROTECTED]> wrote:

Hello everyone:

I want to get some SQL statistics including executions, CPU time for each SQL 
Statement.


The number of each type of query can be found using the "show status
like 'Com%';", I don't believe there's a way to know how much CPU time
MySQL is using for each query, for once, it would be different on each
OS MySQL can run (there are a lot), also, it would take CPU time to
check that ;-). I can't see any advantage of knowing this... Depending
on your OS, you can check the whole MySQL process resources with
specific commands like "top" on *ix or CTRL+ALT+DEL on windows.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems connecting using "-h hostname" with new installation

[Daniel da Veiga]

On 8/6/06, Ro BGCT <[EMAIL PROTECTED]> wrote:

Hello,

I installed MySQL on a remote server and am having a problem.  I am
connected to it via SSH and I can do "mysql -u root -h localhost" just
fine and connect.  However, when I try to connect via "mysql -u root
-h web1.server.com", I get the following message:

"ERROR 1130 (0): Host '75.120.X.X' is not allowed to connect to
this MySQL server"

Now the server's hostname is web1.server.com and it resolves to
75.120.X.X fine so I know it's not an issue with that.  Does anyone
have any clues why?  I have a "web1.server.com" added in the mysq.user
table so I know that's not the problem.

If I add 75.120.X.X to the mysql.user table, I am able to connect via
"mysql -u root -h web1.server.com" just fine.  But why won't it work
when web1.server.com is in the mysql.user table?


Hacking the mysql tables is not a good practice. Use the GRANT command
instead. Also, check this:

http://hackmysql.com/dns

Hope that helped.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: text mode listing

[obed]

On 8/6/06, Bill <[EMAIL PROTECTED]> wrote:

Hi

Can you remind me how to tell mysql to list vertically instead of horiz in
text mode?

Ex:

select all * from a_tablesends field1  field2 .
 I want

field1
field2
field...


Thanks



if you type help you can find a lot o information


mysql> help

For the complete MySQL Manual online, visit:
  http://www.mysql.com/documentation

For info on technical support from MySQL developers, visit:
  http://www.mysql.com/support

For info on MySQL books, utilities, consultants, etc., visit:
  http://www.mysql.com/portal

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set query delimiter.
edit  (\e) Edit command with $EDITOR.

ego   (\G) Send command to mysql server, display result vertically.

exit  (\q) Exit mysql. Same as quit.
go(\g) Send command to mysql server.
help  (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt(\R) Change your mysql prompt.
quit  (\q) Quit mysql.
rehash(\#) Rebuild completion hash.
source(\.) Execute a SQL script file. Takes a file name as an argument.
status(\s) Get status information from the server.
system(\!) Execute a system shell command.
tee   (\T) Set outfile [to_outfile]. Append everything into given outfile.
use   (\u) Use another database. Takes database name as argument.

For server side help, type 'help contents'


--

http://www.obed.org.mx ---> blog

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems connecting using "-h hostname" with new installation

[obed]

On 8/6/06, Ro BGCT <[EMAIL PROTECTED]> wrote:

Hello,

I installed MySQL on a remote server and am having a problem.  I am
connected to it via SSH and I can do "mysql -u root -h localhost" just
fine and connect.  However, when I try to connect via "mysql -u root
-h web1.server.com", I get the following message:

"ERROR 1130 (0): Host '75.120.X.X' is not allowed to connect to
this MySQL server"

Now the server's hostname is web1.server.com and it resolves to
75.120.X.X fine so I know it's not an issue with that.  Does anyone
have any clues why?  I have a "web1.server.com" added in the mysq.user
table so I know that's not the problem.

If I add 75.120.X.X to the mysql.user table, I am able to connect via
"mysql -u root -h web1.server.com" just fine.  But why won't it work
when web1.server.com is in the mysql.user table?



that is happening because when you try to connect to the server this
resolves the domain name, and check if the ip address has permision to
connect, may be it can be fixed if you add in your server in the file
/etc/host if it's linux your client ip and the domain name

example

75.120.1.2   web1.server.com

and if it's windows check the lmhost file



--

http://www.obed.org.mx ---> blog

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Code snippet to run at database service startup

[Daniel da Veiga]

On 8/7/06, Asif Lodhi <[EMAIL PROTECTED]> wrote:

Hi,

What if I want certain variables initialized and certain code to run
at the database service startup?  I am running 5.0.22 on Windows XP
Service Pack 2.  Are there any tirggers that can tell me that the
database is starting up?



You can write a simple application to monitor the error log file for
changes and check it to see if mysql was shutdown and/or started.
Accourding to the manual:

"The error log file contains information indicating when mysqld was
started and stopped "

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: determine safest value for max_connections

[Rithish Saralaya]

 > This might help you 
 > http://kjalleda.googlepages.com/maxconnbymysql
 > 
 > Kishore Jalleda 

Thanks Kishore.
Regards,
Rithish.

Code snippet to run at database service startup

[Asif Lodhi]

Hi,

What if I want certain variables initialized and certain code to run
at the database service startup?  I am running 5.0.22 on Windows XP
Service Pack 2.  Are there any tirggers that can tell me that the
database is starting up?

--
Thanks in advance,

Asif

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: where may I find sqlplus.hh??

[Warren Young]

Greg 'groggy' Lehey wrote:

 I have downloaded a code on c++ to talk to MySQL database from net which
included a file "sqlplus.hh" , where may I find the file?Please help me
soon.


That's the main header file for MySQL++ v1.7.9.  You can download that 
release from the official MySQL++ page:


http://tangentsoft.net/mysql++/

However, it is highly recommended that you upgrade the code to the 
current version.  MySQL++ 1.7.9 is broken in too many ways to tolerate 
now that we have something better.


If you have anything more to discuss on this topic, please take it up on 
the MySQL++ mailing list, not here.

--
Warren Young
MySQL++ Maintainer

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Text search + transactions?

[Lexington Luthor]

Peter Zaitsev wrote:

Right.   If you want  full text search to follow transaction isolation
as well you're in trouble.In most search applications however it is
not that critical.   



Thats a pity. I will have to port the application to PostgreSQL then.

Thanks anyway,
LL


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Text search + transactions?

[Peter Zaitsev]

On Mon, 2006-08-07 at 09:49 +0100, Lexington Luthor wrote:
> Peter Zaitsev wrote:
> > Hi,
> > 
> > One of approaches is to have  "shadow" MyISAM table in addition to your
> > Innodb table to perform full text search, which you can update in bulks
> > or via triggers.
> 
> How can I ensure isolation for queries on this "shadow" table? The 
> documentation says that the table type does not support transactions.

Right.   If you want  full text search to follow transaction isolation
as well you're in trouble.In most search applications however it is
not that critical.   

For some cases some extra filtering (ie by join with Innodb table) can
help to ensure row versions match each other. 

If even that one would not work you would need to implement your own
little search engine in SQL (ie creating dictionary table + word list
table) - with this one you can make it to follow transaction isolation
but it will be very slow.  



> Sorry for being a bit dense here, but what do you mean exactly? Will 
> updates to the "shadow" table only be visible in their own transaction 
> until commit? Will they be rolled back on transaction abort?

No. MyISAM does not support transactions. So you would need to ensure
shadow table updates handle it in some way.



> 
> > You also can try sphinx:  http://www.sphinxsearch.com/  which works with
> > any storage engine and also much faster. 
> 
>  From what I can tell from the Sphinx docs, it is not transactional 
> either. Not only that, it does not support SQL tables at all, it is 
> simply a wrapper for a search API using the mysql storage engine 
> interface. Can you please elaborate on what you mean?

Right.  I assumed you want to use Innodb tables  because you want
transactions but you did not really need search queries to follow same
isolation mode. 



-- 
Peter Zaitsev,  MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Text search + transactions?

[Lexington Luthor]

Peter Zaitsev wrote:

Hi,

One of approaches is to have  "shadow" MyISAM table in addition to your
Innodb table to perform full text search, which you can update in bulks
or via triggers.


How can I ensure isolation for queries on this "shadow" table? The 
documentation says that the table type does not support transactions.


Sorry for being a bit dense here, but what do you mean exactly? Will 
updates to the "shadow" table only be visible in their own transaction 
until commit? Will they be rolled back on transaction abort?



You also can try sphinx:  http://www.sphinxsearch.com/  which works with
any storage engine and also much faster. 


From what I can tell from the Sphinx docs, it is not transactional 
either. Not only that, it does not support SQL tables at all, it is 
simply a wrapper for a search API using the mysql storage engine 
interface. Can you please elaborate on what you mean?


Thanks,
LL


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: maximum files size for longblob - what is bigger?

[Peter Zaitsev]

On Mon, 2006-08-07 at 09:18 +0100, [EMAIL PROTECTED] wrote:
> what us the maximum filesize for longblobs in kb? Is there anything bigger?

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

As you can see in theory it is about 4GB.

It is however also limited by max_packet_size which is 16M by default. 

I would be very careful using blobs larger than 100MB.   MySQL will need
some 3 times of this size of memory allocated on the server for blob
processing. 



-- 
Peter Zaitsev,  MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: maximum files size for longblob - what is bigger?

[Chris]

[EMAIL PROTECTED] wrote:

what us the maximum filesize for longblobs in kb? Is there anything bigger?


That's answered in the documentation - http://dev.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query two databases

[brent]

Hi. I'm running MySQL on a Windows 2003 server and connecting using 
classic ASP.


I'm trying to connect and query two databases using a statement like:

strSQLCombo = "select sfproducts.products.sku as psku, 
sfproducts.products.sortName, 
sum(mcomorderentry.order_items.quantity-mcomorderentry.order_items.unitsCredited) 
as netUnits, 
sum((mcomorderentry.order_items.quantity-mcomorderentry.order_items.unitsCredited)*mcomorderentry.order_items.unitPrice) 
as tDollars from sfproducts.products, mcomorderentry.order_items where 
sfproducts.products.sku like 'ADIS%' and sfproducts.products.sku = 
mcomorderentry.order_items.sku and mcomorderentry.order_items.EntryDate 
between '2006-07-01' and '2006-07-31' or sfproducts.products.sku like 
'BKT%' and sfproducts.products.sku = mcomorderentry.order_items.sku and 
mcomorderentry.order_items.EntryDate between '2006-07-01' and 
'2006-07-31'  group by mcomorderentry.order_items.sku asc;"


I've tested the statement through MySQL Front and it works fine. But how 
do I connect to two DBs using ASP? My code typically would look like:


dim myConnProd
dim strConnProd
dim myConnSales
dim strConnSales
dim strSQLCombo
dim rsCombo
dim adCmdText

strConnProd = "sfProducts"
set myConnProd = server.createObject("ADODB.Connection")
myConnProd.Open strConnProd

strConnSales = "mComOrderEntry"
set myConnSales = server.createObject("ADODB.Connection")
myConnSales.Open strConnSales

set rsCombo = server.createObject("ADODB.Recordset")
rsCombo.cursorLocation = 3
rsCombo.open strSQLCombo, *-->what syntax do I use here?<--*, adCmdText

because typically, I'd use: rsCombo.open strSQLCombo, *myConnProd*, 
adCmdText


Or am I just hosed on this one and need to create a temporary table or 
query products and then loop through to get the sales?


TIA,

Brent

maximum files size for longblob - what is bigger?

[ross]

what us the maximum filesize for longblobs in kb? Is there anything bigger?

Ross

Re: Text search + transactions?

[Peter Zaitsev]

On Mon, 2006-08-07 at 08:49 +0100, Lexington Luthor wrote:
> Hi,
> 
> I need fast text searching on a transactional table. Is it possible to 
> use transactions and text-search on a table together yet in any 
> production stable version of mysql?

Hi,

One of approaches is to have  "shadow" MyISAM table in addition to your
Innodb table to perform full text search, which you can update in bulks
or via triggers.

You also can try sphinx:  http://www.sphinxsearch.com/  which works with
any storage engine and also much faster. 



-- 
Peter Zaitsev,  MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL SQL statistics

[古雷]

Hello everyone:

I want to get some SQL statistics including executions, CPU time for each SQL 
Statement.

How can I get it from MySQL?

regards,

Gu Lei

Text search + transactions?

[Lexington Luthor]

Hi,

I need fast text searching on a transactional table. Is it possible to 
use transactions and text-search on a table together yet in any 
production stable version of mysql?


Thanks,
LL


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]