SELECT consumers.id FROM consumers, cases
WHERE
consumers.id=cases.consumers_id
AND consumers.date_of_birth = ?
AND cases.last_name = ?
AND cases.first_name = ?
John A. McCaskey
-Original Message-
From: Courtney Braafhart [mailto:[EMAIL PROTECTED]
Sent: Monday, February 27, 2006
Hi everyone,
I'm running MySQL 4.0.18 on Debian with a 2.6 linux kernel using ext3 as
the underlying filesystem for the database storage.
I currently have some InnoDB tables with the following structure:
Log_20060101 {
Monitor_id medium int,
Timestamp
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, January 13, 2006 9:41 AM
To: John McCaskey
Cc: MySQL
Subject: Re: Huge number of tables with InnoDB
To reply to this, I think we have to understand why you have chosen to
split the tables at all. It seems to me
Yes! Please stop spamming us; we would signup for a Lasso list if we cared to
get these tips not a MySQL list.
John A. McCaskey
Software Development Engineer
Klir Technologies, Inc.
[EMAIL PROTECTED]
206.902.2027
-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL
Jon,
I can't offer any great insight into your problem I'm afraid. But I'd
encourage you not to add the error to the ignore list as the UPDATE won't
actually get replicated then and your databases will be out of sync. Maybe
this is a filesystem problem at the OS level? What OS and
PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tue 10/11/2005 6:52 AM
To: Jeremiah Gowdy
Cc: John McCaskey; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: Re: How thread-safe is mysql_real_connect()?
Jeremiah Gowdy [EMAIL PROTECTED] wrote on 10/11/2005 03:08:40 AM:
The Windows DLL is thread safe. You
Sean,
First let me thank you for all the great posts and info I've seen you
put on this list for others.
I've been working in C with MySQL in a very multithreaded environment
for several years and think I can explain the thread safety issues
clearly. Rather than try to respond point by point to
Hi,
On 9/28/05, Lefteris Tsintjelis [EMAIL PROTECTED] wrote:
John McCaskey wrote:
Hello again,
I modified your threading code to use a thread pool. Here are my
results:
Hello,
I modified the thread pool a bit to get rid of that lock ASAP. It is safe
to get rid of that lock right
Hi,
I think I can shed a bit of light on the topic. There are several reasons
why your multithreaded code is not a good example and would be slower.
1) locking/unlocking mutexes of course does add *some* overhead
2) you have a single database connection and are passing it around between
threads
Hi again,
On 9/27/05, Lefteris Tsintjelis [EMAIL PROTECTED] wrote:
John McCaskey wrote:
Hi,
I think I can shed a bit of light on the topic. There are several
reasons
why your multithreaded code is not a good example and would be slower.
1) locking/unlocking mutexes of course does
Hello again,
I modified your threading code to use a thread pool. Here are my results:
pooled-threading.c:
[EMAIL PROTECTED]:~$ gcc -lmysqlclient_r -lpthread pooled-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.068s
user 0m0.041s
sys 0m0.097s
[EMAIL PROTECTED]:~$ time
Ok,
So I had another developer come to me today complaining mysql wouldn't
set a column to NULL. I figured out it was because instead of comma
delimitating his fields to SET he was delimiting with AND, however mysql
took this query and didn't generate any error. I'm assuming this is
like ==
Hey,
I have an application using the C API that is doing a REPLACE command
into an innodb table that has other tables with cascading deletes
relying on it's entries. Rather than use an UPDATE/Check
affected/Insert/Check success/repeat method we have wrapped the REPLACE
query in a SET
of legitimate mysql setup error on our part or a bug in mysql.
John A. McCaskey
-Original Message-
From: John McCaskey [mailto:[EMAIL PROTECTED]
Sent: Monday, July 11, 2005 9:46 AM
To: mysql@lists.mysql.com
Subject: SET FOREIGN_KEY_CHECKS=0 being ignored
Hey,
I have an application using
like a mysql bug then right?
John A. McCaskey
-Original Message-
From: John McCaskey
Sent: Monday, July 11, 2005 9:51 AM
To: John McCaskey; mysql@lists.mysql.com
Subject: RE: SET FOREIGN_KEY_CHECKS=0 being ignored
Oh, I should also mention we have binary logging on and I verified
as succeeding
right before the replace though.
John A. McCaskey
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, July 11, 2005 11:42 AM
To: John McCaskey
Cc: mysql@lists.mysql.com
Subject: Re: SET FOREIGN_KEY_CHECKS=0 being ignored
Some additional info
If you use InnoDB then the InnoDB Hot-backup tool works very well. It's not
free, but its inexpensive and if you are in an environment where you need to do
large fast hot backups you will probably find it well worthwhile.
We normally do backups as follows:
Production Server A - Production
On Tue, 2005-05-10 at 14:56 -0400, Frank Bax wrote:
At 02:22 PM 5/10/05, Paul Halliday wrote:
Now, as time progresses the queires are getting slower and slower.
I know this is expected,
I don't think so. I thought that if the number of rows returned does not
change and an index is
INSERT INTO table (field1, field2) VALUES (1, 2), (3, 4), (5, 6), (7,
8);
That would insert 4 rows first row with field1=1, field2=2, second
field1=3, field2=4, etc.
This is documented on the INSERT Syntax page of the manual, but it may
be kind of hard to read for a beginner as it just says
On Fri, 2005-02-18 at 08:08 -0800, Jason Martin wrote:
On Fri, Feb 18, 2005 at 10:06:38AM +0100, Anton Kornexl wrote:
There should be tools on this website, but i see only a message from
Infektion Group.
What happened ?
Looks like the website got hacked.
I guess they should have
Did you upgrade to a 2.6.x linux kernel as well? Threads get hidden in a
normal ps aux command starting in 2.6 and show as a single process. If
this is the case do ps aux -L and you will see the threads as well.
On Tue, 2005-01-18 at 13:15 -0500, Eben Goodman wrote:
I used to run mysql 3.x on
See below:
mysql (SELECT avg FROM event_log_5minute_20050104 WHERE
monitor_id=479139 AND monitor_server_id=1 AND timestamp =
2005010408 AND timestamp 2005010508 order by avg);
+-+
| avg |
+-+
|NULL |
|NULL |
|NULL |
|NULL |
|NULL |
| 55854.1 |
|
On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote:
- Original Message -
From: John McCaskey [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, January 04, 2005 5:22 PM
Subject: not all rows returned when using order by and null values?
See below:
mysql (SELECT avg FROM
FYI, I have now verified this bug (?) occurs in 4.0.23 as well.
On Tue, 2005-01-04 at 15:03 -0800, John McCaskey wrote:
On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote:
- Original Message -
From: John McCaskey [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, January 04
not actually have
a union?
On Tue, 2005-01-04 at 15:40 -0800, John McCaskey wrote:
FYI, I have now verified this bug (?) occurs in 4.0.23 as well.
On Tue, 2005-01-04 at 15:03 -0800, John McCaskey wrote:
On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote:
- Original Message -
From
FYI, I created a bug for this (http://bugs.mysql.com/bug.php?id=7672) which has
now been updated to verified. So it looks like it is in fact a mysql bug.
From: John McCaskey [mailto:[EMAIL PROTECTED]
Sent: Tue 1/4/2005 3:59 PM
Cc: mysql@lists.mysql.com
Subject
Ahhh, thats very good to know. Thank you.
On Wed, 2004-12-15 at 19:09 -0500, Harrison Fisk wrote:
No.
In InnoDB an UPDATE is done as a DELETE/INSERT internally because it
is
multiversioning and it has to be able to rollback in case of a
problem.
So the UPDATE effectively does the
I'm currently doing a large number of REPLACE queries, I know that these
evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is
true on a disk io level as well with extra io occuring for the delete,
and then re-insertion, vs what would occur with an UPDATE.
The way it works
On Wed, 2004-12-15 at 11:46 -0600, gerald_clark wrote:
John McCaskey wrote:
I'm currently doing a large number of REPLACE queries, I know that these
evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is
true on a disk io level as well with extra io occuring for the delete
Yes, mysql clustering is a ram only database. It does not make sense to
use it if you have a very large database.
You can use master/slave functionality and use whatever table type you
like. But using the newer clustering technology you have no choice but
to use the ndb table type which is ram
You can't automatically update it, that would require triggers which are
not supported in mysql, you would need some sort of script that runs
once a day and manually uses the functions described in the linke
Bernard sent you to update the field.
However I would recommend a different table
The answer is you create one user for the PHP (webserver) process to
use.
Then you keep track of user permissions in your application code. This
probably involves creating your own user table within your own database
and storing users of your site there. Then in the other tables you
associate
No, it is not true.
After repeated failed connection attempts from a host that host will be blocked until
a flush hosts command is executed. The number allowed before this blocking is
specified by the variable max_connect_errors.
See http://dev.mysql.com/doc/mysql/en/Blocked_host.html
John
So I gather you are creating a table, and doing some work in it, but
even though it isn't declared 'temporary' it really is and you don't
want it replicated?
If this is the case you can create the table in a separate database, and
in your mysql configuration tell the binary logging to exclude
by the query that created the streaming result will be
locked until all of the results have been read or the connection
closed.
John McCaskey
On Wed, 2004-10-20 at 14:20 -0500, [EMAIL PROTECTED] wrote:
MySQL 5.0 Alpha
Jdbc Driver: Connector J
OS: Windows 2000 Professional
Table Size 1 Mil. Records
I believe that what you described is perfectly acceptable. The thing to
keep in mind is the thread_init allocates thread specific memory for
mysql, and the thread_end clears it. As such you should never execute
any other mysql commands unless you have executed an init, and you
should never init
consistent.
John McCaskey
On Tue, 2004-10-12 at 21:19 +0200, Frank Fischer wrote:
Hi
i'm using MySQL version 4.0.20d.
I was able to set up a simple replication between a master and a slave.
To fully understand the replication mechanism of MySQL i would like to ask
some questions (the manual
If you could post your table schema (SHOW CREATE TABLE table_name) and
then give an example of the query that is slow on InnoDB that would help
us give a better analysis. Right now it sounds like something is wrong,
InnoDB is likely to be slightly slower than MyISAM because of
transaction
One thing you could do, which may not be the best, is insert one (or
some set limit) of rows at a time, then after each sleep for .25 seconds
or something, so that your inserts get spread out more over time, and
there is idle time between them for the selecting clients to complete.
Obviously this
As far as I know memory usage between the two table types is roughly the
same. The way memory is setup/used is somewhat different however. For
myisam the primary memoy buffer to accelerate queries is the key_buffer
which caches data for keys. In innodb you have more options to set with
the main
you cannot
set both types of databases to have a lot of memory allocated to them.
Right?
On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote:
As far as I know memory usage between the two table types is roughly the
same. The way memory is setup/used is somewhat
You may use Alias's if you use HAVING instead of WHERE this is one of
the defined difrerences between the two clauses. Having is also slower
and will not be optimized, but if you are placing a complex function
like this in your where you obviously aren't expecting great speed.
John
On Mon,
)Foreign Key Contstraints
InnoDB cons:
1)Higher disk footprint
2)Slightly slower in non high concurrency situations due to transaction overhead key
constraint checking etc
I've had a very positive experience with using InnoDB tables in a production
environment with a several gigabyte database.
John
not your fault, but I believe you will save yourself
considerable headache by refactoring the table rather than working
around the poor design.
John McCaskey
On Thu, 2004-10-07 at 13:03 -0400, [EMAIL PROTECTED] wrote:
I would strongly recommend refactoring as the string transformation you
I never got a reply for this, and I'm still trying to figure out the
best way to handle it. Anyone?
John A. McCaskey
-Original Message-
From: John McCaskey [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 25, 2004 2:17 PM
To: [EMAIL PROTECTED]
Subject: INSERT IGNORE like feature
I have a logging table where I insert a large number of rows every 5
minutes. For performance reasons this occurs in bulk inserts of about
5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...))
One of the fields in the table is an id that connects it to another
table. It is
Did your client side host change? The user/pass are sometimes setup to
only allow access from a specific host subnet or single ip address.
John A. McCaskey
-Original Message-
From: Jean Zhong [mailto:[EMAIL PROTECTED]
Sent: Monday, August 02, 2004 1:27 PM
To: [EMAIL PROTECTED]
I've had no problems partitioning data in this exact same manner.
However my timestamp column is always pre-computed in the application
code because it is neccesary to round it to the last 5 minute interval
so I would not encounter the issue you mention. I'd recommend simply
computing the
or omitted to be taken in reliance upon the
contents of this email by unauthorised recipients is prohibited and may
be unlawful.
--
-Original Message-
From: John McCaskey [mailto:[EMAIL PROTECTED]
Sent: 21 July 2004 16
MyISAM tables are sometimes faster than InnoDB, but for most
applications the difference is going to be negligible. MyISAM tables
also use less disk space (more compressed row format). These are the
only 2 advantages I'm aware of. InnoDB on the other hand offers you
foreign keys and transaction
It sounds like the values you want to index our timestamps. If this is
the case you can do something tricky like using an integer column, and
storing -(unixtimesamp) values so that what mysql sees as ASC will
really be your data in DESC order. Of course there is some overhead
involved now in
I don't think its possible in one query. One thing you can do is lock
the table when you select the 20 rows and determine whether to do the
insert. Then unlock when done. This avoids the concurrency issue you
are having, but it may cause unacceptable perfomance if you have a lot
of queries
about that before.
Thanks,
John A. McCaskey
-Original Message-
From: Michael McTernan [mailto:[EMAIL PROTECTED]
Sent: Friday, June 18, 2004 11:16 AM
To: John McCaskey
Cc: [EMAIL PROTECTED]
Subject: RE: GROUP BY across UNION
Hi John,
Depending on the size of your datasets, you could
Give the below a shot:
slave stop;
set global sql_slave_skip_counter=1;
slave start;
Good luck.
John A. McCaskey
-Original Message-
From: Henry Chang [mailto:[EMAIL PROTECTED]
Sent: Monday, June 14, 2004 3:34 PM
To: [EMAIL PROTECTED]
Subject: How to Fix Broken Replication
I have
Unless outlook is just formatting your message strangley it looks like
the actual name of the ID field is `ID ` with two space char's
included. As such you probably want to reccreate the table using `ID`
in the create statement so that it will get created as you expect
without these extra chars.
One possibility is that the OS has the portion of disk that the row is
stored in cached in memory via its normal disk caching after the first
execution. Another possibility is that the key for the table is in
mysql's key_buffer after the first execution. If you are using innodb
then it might be
Try, SELECT * FROM job_log_2004 ORDER BY JobID DESC LIMIT 1. This is
simpler than your sub select method and probably faster. Sub selects
don't work in MySQL 4.x so if you are not using a newer beta build that
is probably why it fails.
John A. McCaskey
-Original Message-
From: James
The master pushes data to the slave as soon as it has executed the query
itself. It is not a periodic push, but an asyncrounous push as soon as
data is ready to be sent. So the gap would only be as great as the
latency between your two servers.
If the servers are disconnected or unable to
I believe Gowtham's response is accurate.
If the link goes down it is going to unclear which update becomes the
final one, and it will be possible for one side to contain the row after
one update, and the other to contain the row after the other update,
thus being out of sync.
Mysql has no
Yes,
Lets call the two serves A, and B. You set A as the master for B, and you set B as
the master for A. In this way you can insert/delete/update on either side and both
sides will be kept in sync. If you plan to actively use both at the same time you do
need to be weary of some
it with a join? Like SELECT
AVG(avg) FROM table_a, table_b GROUP BY id_field.
Respectfully,
Ligaya Turmelle
John McCaskey [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
First of all I am using mysql 4.0.18 and I am not free to upgrade to 4.1
or 5.0.
I have two tables:
table_a
I attempted to create a merge table across two identical InnoDB tables
and it seemed to work without error. But when I went to use the table I
got a file not found my_table.MRG (error number 2) error. The file does
in fact exist, but it is only 54 bytes.
I'm guessing that I can only create
First of all I am using mysql 4.0.18 and I am not free to upgrade to 4.1
or 5.0.
I have two tables:
table_a, and table_b these two tables have the same structure:
CREATE table_a (
id_field mediumint(8) unsigned NOT NULL,
avg float default NULL
)
What I want to do is get the
Yes, I think the most straight forward way is to simply put in a series of
grouped OR statements. See below.
SELECT * from pages WHERE
changelog.agent = pages.agent AND
changelog.company = pages.company AND
changelog.magazine = pages.magazine AND
(
changelog.orig_id = pages.mls_1 OR
The updates on the slave will not syncronize to the master if you have a one
direction master-slave relationship setup. It is possible however to setup
each server as a master and as a slave so that server 1 is the master for
server 2 and server 2 is the master for server 1. Then queries will
clarify for us once more.
John McCaskey
On Sat, 2004-01-17 at 12:44, Aftab Jahan Subedar wrote:
Hey wait a minute. Where did you get the my_free(), may be you are
trying to say mysql_free(), but then that is used only if result set is
used/called.
But the code does not show any result set
I have the following code:
//try the mysql connection
mysql_init(mysql_connection);
if(!mysql_real_connect(mysql_connection, db_host, db_user, db_pass,
db_db, 0, NULL, 0)) {
flockfile(stderr);
fprintf(stderr, %s: Failed to connect to
Try forming the query with only the first array element, then iteratring
through the rest concatinating OR clauses onto the end of the query. And
then after the loop sending the query to the db. See my below pseudo code.
String query = UPDATE users SET status = no WHERE name = array[0]
Hello, I'm currently testing out replication on a high volume innodb based
database. This has been working great for several weeks, but when I came in
this morning I found my slave had the following error:
031212 23:30:24 Slave: query 'UPDATE monitor_tunnel_cisco_phase_2 SET
counter=0,
69 matches
Mail list logo
