Re: Select from two tables not returning the expected number of rows
mysqlselect count(*) from x.table_a,y.table_b WHERE x.table_a.svr = y.table_b.svr and x.table_a.started = y.table_b.started and x.table_a.ended = y.table_b.ended and x.table_a.volume = y.table_b.volume and x.table_a.who = y.table_b.who; you didn't say that (svr,started,ended,volume,who) is a unique key... If not, it would explain the result and the extra rows you get. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed this up?
Alex Pilson wrote: I have this query below than seems to be running really slow. I have tried indexing key JOIN fields but it seems to have not helped. Any pointers would be greatly appreciated. (ver: MySQL 3.23.53) side note: seems to run fairly quick in 4.0.18 SELECT dl.email as download_email, CONCAT(dl.first, , dl.last) as download_name, o.id as orderID, c.id as cust_id, dl.id as dlId, c.email as customer_email FROM download_users as dl LEFT JOIN orders as o ON dl.email = o.email LEFT JOIN customers as c ON (dl.email=c.email OR c.id = o.cust_id OR concat(dl.first, dl.last) = concat(c.bill_first,c.bill_last)) GROUP BY download_email ORDER BY orderID DESC, cust_id DESC As far as I know, MySQL 3.23 doesn't use index for a query with OR clause (exept rare cases). so you end up with a full table scan.. (quiet slow, isn't it ?). I don't see anything to speed this up. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my_thread_init
Hi,
I'm using the C api within a multithread environement. Is that allright
if I have a function that looks like this :
saveParam(){
my_thread_init();
[connexion/query/close]
my_thread_end();
}
Can I call it several time from the same thread ? Or do I have to do
my_thread_init/end only once for each thread ?
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Messure time including milliseconds
Hi, I think it is better to implement this measurment in your application - thus you'll have an estimate of the time needed to execute your procedures including the mysql calls. Otherwise you can use the BENCHMARK MySQL function http://mysql.online.bg/doc/mysql/en/Information_functions.html for example mysql select benchmark(1000,procedure_name); +--+ | benchmark(1000,procedure_name) | +--+ |0 | +--+ 1 row in set (0.15 sec) the average time to execute the procedure will be 0.15/1000 = 0.00015 seconds HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Tuesday 12 October 2004 19:47, Thomas Schager wrote: Hi, I need to messure the time needs of my procedures in MySQL. How can I reach a messurement of time intervals that include milliseconds, written in MySQL SQL statements? Thanks for any ideas, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to flush process which are in sleep state
Hi, when i issue show processlist command it gives very long list of process most of them are sleep state . is there a way to clear those processs with out killing them Thanks in advace ANil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: outer join w/restriction
In article [EMAIL PROTECTED],
Christopher J. Mackie [EMAIL PROTECTED] writes:
There's something I'm not getting about how to put a SELECT restriction on a query
with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = ''
AND C.Reviewer.ID = 2;
returns only Applicants who have reviews from Reviewer # 2. What I want is *all*
applicants who meet the other two criteria (Active, and SCode =...), and *any*
reviews by Reviewer 2 for any of those applicants (if Reviewer 2 hasn't written for
Applicant a, then a should still be in the result set, but with the Reviews.*
columns as NULL).
When I remove the final ReviewerID = 2 restriction, all of the right applicants
are in the dataset--but with a lot of extra rows due to reviews by other reviewers.
How do I get rid of Reviewers {1, 3...n}, without losing all the applicants who've
never met Reviewer #2?
If C.Reviewer.ID is a typo for Reviews.ReviewerID, the solution is
simple:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND C.Reviewer.ID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By Question
In article [EMAIL PROTECTED], Fan, Wellington [EMAIL PROTECTED] writes: Hello Listfolk, I have a table with a 'category_fk' column and a 'status' column. 'Status' has but a tiny handful of known values, kinda like an enum. I'd like to form a query that would give me results like: category_fk | status=1 | status=2 | status=3 | toys | 23|45|0 | games | 12|0 |0 | books | 5 |1 |3 | Where the non-fk columns represent the counts of records with that category_fk with a certain 'status' I've got something close: SELECT category_fk, count(*) as n, status FROM myTable GROUP BY category_fk, status But this gives me a record for each category_fk/status. SELECT category_fk, sum(case status when 1 then 1 else 0 end) AS 'status=1', sum(case status when 2 then 1 else 0 end) AS 'status=2', sum(case status when 3 then 1 else 0 end) AS 'status=3' FROM myTable GROUP BY category_fk; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cross database joins performance hit?
In article [EMAIL PROTECTED], Jason [EMAIL PROTECTED] writes: I've tried to find references to if there are any design flaws with using multiple databases or not however was unable to locate anything (but I was told by a previous co-worker that there were performance hits). Are there any performance hits or design flaws by separating a large database into separate databases then cross-database joining the tables? (these tables have anywhere between 1m and 5m+ rows) Performance hits: AFAIK no. Design flaws: yes, absolutely! Although MySQL lets you get away with it, don't do it - other DBMSs prohibit it, and rightly so. If two tables have something in common (as expressed by a JOIN), they should be in the same database. If you need a finer-grained structuring mechanism, some DBMSs have SCHEMAs. MySQL doesn't, but you could encode the schema name into the table name, something like CREATE TABLE myschema_table1 (...). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from two tables not returning the expected number of rows
you didn't say that (svr,started,ended,volume,who) is a unique key... If not, it would explain the result and the extra rows you get. Well... that combination of fields *should* be unique. Does a unique index need to be specified on those columns? It is possible that (svr,started,ended,volume,who) may not be unique, and that duplicate data was inserted into the table. I was assuming that my beginning SQL skills were to blame for the unexpected results. If I understand your comment correctly, if the combination of columns shown above is indeed a unique key, then the results of my query should have been what I expected. Please correct me if that is not the case. Thanks for the feedback, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: keep field names unique across database?
background info There are two main camps in database design style. You could almost say there are three if you count the newbies who haven't found one of the other two yet. One camp derives their style from object oriented development. These people use tables with singular names with simple (non-unique) names for fields. Referring to one of these fields in a query is similar in format to saying that the object (table) person has a property (field) of name because you would refer to both as person.name. The second camp, as is described in the article, are more data-oriented. This practice was created in the original days of databases and programming design where ALL variables, including table names and field names, were global and needed to be absolutely unique. In this case you give tables plural names (They do hold lists of things, right? In conversation you would say This is the 'vendors' list for todays event or the addresses table, referring to a table of addresses). You uniquely identify all fields for several reasons: You avoid the need to use table.fieldname in any query If you see a field name in a query that someone else wrote, you can instantly deduce which table it comes from. Different fields with the same short name could have different data types. For example: on the person table the name field is varchar(20) but on the vendor table the name field is varchar(128). If you expect to recieve 20 characters but got 128, that could cause some headaches, right? There are even styles within the style. Some people split the table prefix from their field name with an underscore, some use all proper case, others use lower case for the prefix and proper case the field name. I look at the issue as a matter of style and coding convention. I know there are strong opinions about both styles and I am not advocating one over the other. It is important that you conform to the coding styles and conventions of your organization. Being an odd-ball could make your designs harder to read and maintain. However, if you know that there is chaos in your organization, you **could** help things by picking a style and sticking to it. As everyone else begins to notice how readable and maintainable your code is, they will slowly come around. /background answer Finally, to answer your question: Yes, the author of that article is suggesting that you uniquely name ALL of your fields (so that no two tables in the same database could possibly contain identically named fields) by prepending the table name to the simple name of each and every field. /answer Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris W. Parker [EMAIL PROTECTED] wrote on 10/12/2004 07:40:14 PM: hello, continuing my quest to build a better database, i'd like to ask a question that i haven't been able to find an answer to. here is an excerpt from an article on evolt (http://www.evolt.org/article/Beginning_Database_Design_Part_I/18/27137/ ): (and where i got the idea as well) You'll probably see some duplicate field names, such as 'Name' in both the 'Companies' and 'People' tables. Let's make them unique across the database. You might choose 'ContactName' and 'CompanyName' but whatever you use, stick to the guidelines above, and be consistent. is this person suggesting that *all* fields within the 'Companies' table be prepended with Company (i.e. CompanyName, CompanyAddress1, CompanyZip, etc.) or is he suggesting that only fields which have the same name in multiple tables have Company added? THIS: +-+ | COMPANIES | +-+ | CompanyName | | CompanyDate | | CompanySize | +-+ +-+ | CONTACTS| +-+ | ContactName | | ContactHeight | | ContactWeight | +-+ OR THIS: +-+ | COMPANIES | +-+ | CompanyName | | Date| | Size| +-+ +-+ | CONTACTS| +-+ | ContactName | | Height | | Weight | +-+ 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: Q: outer join w/restriction
Sadly, it was a typo in the email (apologies for that), but not in the
query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND Reviews.ReviewerID = 2
AND ApplicantStatus.SCode = '';
produces the result I described. --Chris
If C.Reviewer.ID is a typo for Reviews.ReviewerID, the solution is
simple:
There's something I'm not getting about how to put a SELECT
restriction on a query with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual FROM ApplicantStatus INNER JOIN
Applicants
ON Applicants.AppID = ApplicantStatus.AppID LEFT JOIN Reviews ON
Reviews.AppID = Applicants.AppID WHERE ApplicantStatus.Active = 1 AND
ApplicantStatus.SCode = ''
AND Reviews.ReviewerID = 2;
returns only Applicants who have reviews from Reviewer # 2. What I
want is *all* applicants who meet the other two criteria (Active, and
SCode =...), and *any* reviews by Reviewer 2 for any of those applicants
(if Reviewer 2 hasn't written for Applicant a, then a should still be in
the result set, but with the Reviews.* columns as NULL).
When I remove the final ReviewerID = 2 restriction, all of the
right applicants are in the dataset--but with a lot of extra rows due to
reviews by other reviewers. How do I get rid of Reviewers {1, 3...n},
without losing all the applicants who've never met Reviewer #2?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: outer join w/restriction
A SQL AND is a restrictive filter
In other words
Your resultset will yield results based on how you structure your query
If you structure your resultset which includes only applicants who have been
seen by Reviewer2 then state
SELECT ... FROM
WHERE (CONDITION1 AND Applicant.Reviewer = 'Reviewer2')
If you want applicants which includes ALL reviewers INCLUDING those who have
been seen by Reviewer2
SELECT ... FROM
WHERE (CONDITION1 OR Applicant Reviewer='Reviewer2')
HTH,
Martin
To some extent.. sanity is a form of conformity..
~John Nash PhD~
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 8:37 AM
Subject: Re: Q: outer join w/restriction
In article
[EMAIL PROTECTED],
Christopher J. Mackie [EMAIL PROTECTED] writes:
There's something I'm not getting about how to put a SELECT restriction
on a query with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = ''
AND C.Reviewer.ID = 2;
returns only Applicants who have reviews from Reviewer # 2. What I want
is *all* applicants who meet the other two criteria (Active, and SCode
=...), and *any* reviews by Reviewer 2 for any of those applicants (if
Reviewer 2 hasn't written for Applicant a, then a should still be in the
result set, but with the Reviews.* columns as NULL).
When I remove the final ReviewerID = 2 restriction, all of the right
applicants are in the dataset--but with a lot of extra rows due to reviews
by other reviewers. How do I get rid of Reviewers {1, 3...n}, without
losing all the applicants who've never met Reviewer #2?
If C.Reviewer.ID is a typo for Reviews.ReviewerID, the solution is
simple:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND C.Reviewer.ID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
--
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 from two tables not returning the expected number of rows
Ted, You didn't even say that (svr, started, ended, volume, who, action) was a unique combination of values for either table. If that is the case then you will have a hard time matching rows of one table uniquely to rows of the other table. Try this query and you may be able to see where the duplicate rows are coming from: select x.table_a.*, y.table_b.* from x.table_a,y.table_b WHERE x.table_a.svr = y.table_b.svr and x.table_a.started = y.table_b.started and x.table_a.ended = y.table_b.ended and x.table_a.volume = y.table_b.volume and x.table_a.who = y.table_b.who; LIMIT 100; Concentrate on those rows that are duplicated from table_a and compare values left-to-right between the two tables, you should be able to see where there are duplicate rows on the a side that match rows on the b or duplicate rows on the b side that match a rows on the a side. Creating a unique index on the combination (svr, started, ended, volume, who) will help you in the future, but not right now, as you already have duplicates in your data. You will have to winnow them out of your data before you can create the index. Once that combination of fields is unique for both tables, your query should work as you wanted. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ted Byrne [EMAIL PROTECTED] wrote on 10/13/2004 08:59:04 AM: you didn't say that (svr,started,ended,volume,who) is a unique key... If not, it would explain the result and the extra rows you get. Well... that combination of fields *should* be unique. Does a unique index need to be specified on those columns? It is possible that (svr,started,ended,volume,who) may not be unique, and that duplicate data was inserted into the table. I was assuming that my beginning SQL skills were to blame for the unexpected results. If I understand your comment correctly, if the combination of columns shown above is indeed a unique key, then the results of my query should have been what I expected. Please correct me if that is not the case. Thanks for the feedback, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: outer join w/restriction
Martin, you are correct in how you determine when to use AND and when to
use OR, but that's not what the original query was trying to find If
you re-read his original post, he wants this query:
SELECT Applicants.AppID,
Applicants.Name,
Applicants.Email
FROM ApplicantStatus
INNER JOIN Applicants
ON Applicants.AppID = ApplicantStatus.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
with two additional columns containing information from the reviews
table. BUT! he only wants those columns populated if the reviewer was
reviewer #2. That's why Harald's answer is correct.
To repeat Harald's answer:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND ReviewerID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
With the ReviewerID condition into the ON clause of the LEFT JOIN (and not
as a condition in the WHERE clause), the query will not join ANY row from
reviews for ANY OTHER reviewer except #2. Good call Harald!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Martin Gainty [EMAIL PROTECTED] wrote on 10/13/2004 10:13:46 AM:
A SQL AND is a restrictive filter
In other words
Your resultset will yield results based on how you structure your query
If you structure your resultset which includes only applicants who have
been
seen by Reviewer2 then state
SELECT ... FROM
WHERE (CONDITION1 AND Applicant.Reviewer = 'Reviewer2')
If you want applicants which includes ALL reviewers INCLUDING those who
have
been seen by Reviewer2
SELECT ... FROM
WHERE (CONDITION1 OR Applicant Reviewer='Reviewer2')
HTH,
Martin
To some extent.. sanity is a form of conformity..
~John Nash PhD~
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 8:37 AM
Subject: Re: Q: outer join w/restriction
In article
[EMAIL PROTECTED],
Christopher J. Mackie [EMAIL PROTECTED] writes:
There's something I'm not getting about how to put a SELECT
restriction
on a query with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = ''
AND C.Reviewer.ID = 2;
returns only Applicants who have reviews from Reviewer # 2. What I
want
is *all* applicants who meet the other two criteria (Active, and SCode
=...), and *any* reviews by Reviewer 2 for any of those applicants (if
Reviewer 2 hasn't written for Applicant a, then a should still be in the
result set, but with the Reviews.* columns as NULL).
When I remove the final ReviewerID = 2 restriction, all of the
right
applicants are in the dataset--but with a lot of extra rows due to
reviews
by other reviewers. How do I get rid of Reviewers {1, 3...n}, without
losing all the applicants who've never met Reviewer #2?
If C.Reviewer.ID is a typo for Reviews.ReviewerID, the solution is
simple:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND C.Reviewer.ID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
--
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]
Table creation Time stamp
Hi, where can we find the time stamp of table creation i.e when that table was created Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Group By Question
Perfect! Thank you. -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED] Subject: Re: Group By Question SELECT category_fk, sum(case status when 1 then 1 else 0 end) AS 'status=1', sum(case status when 2 then 1 else 0 end) AS 'status=2', sum(case status when 3 then 1 else 0 end) AS 'status=3' FROM myTable GROUP BY category_fk; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table creation Time stamp
use the command: SHOW TABLE STATUS http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Anil Doppalapudi [EMAIL PROTECTED] wrote on 10/13/2004 10:42:11 AM: Hi, where can we find the time stamp of table creation i.e when that table was created Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table creation Time stamp
SHOW TABLE STATUS LIKE 'your_table_name'; See the manual for details http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html. Michael Anil Doppalapudi wrote: Hi, where can we find the time stamp of table creation i.e when that table was created Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from two tables not returning the expected number of rows
You didn't even say that (svr, started, ended, volume, who, action) was a unique combination of values for either table. If that is the case then you will have a hard time matching rows of one table uniquely to rows of the other table. Shawn, Sorry for the lack of clarity on my part. The combination should be unique for each of the tables, and there should be one record in table_b having the same combination as the each record in table_a. It is looking like there are duplicated rows, and I'm going to have to get them cleaned out. Thanks for your assistance, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Configuraton on Redhat
Hi, This is the only place where I can ask these questions, and would greatly appreciate if anyone can give me some clue on how to resolve these issues. I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Command: [EMAIL PROTECTED] home]# mysqladmin -u root flush-privileges password abcdef Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Q1. How can I change the password for root ? Q2. How do I know if root IS the admin for MySql ? Q3. How can I add a Database ? Q4. How can I add a New User to a New Database ? Thanks in Advance, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_insert_id() for UPDATE?
I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). If the record did not exist and UPDATE performed as INSERT, then I would theoretically have no problem. Hower, since the record that I'm updating already exists, the AUTO_INCREMENT value also already exists and that value is not being re-generated. Therefore, the value that mysql_insert_id() is returning is zero. Is there a similar way to capture the id of the record that is being updated? -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT SUM + 2 JOINS
I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew
RE: MySql Configuraton on Redhat
use the command below mysqladmin -u root -p options supply the passwd when prompted Anil DBA -Original Message- From: Kamal Ahmed [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 8:35 PM To: Kamal Ahmed; [EMAIL PROTECTED] Subject: MySql Configuraton on Redhat Hi, This is the only place where I can ask these questions, and would greatly appreciate if anyone can give me some clue on how to resolve these issues. I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Command: [EMAIL PROTECTED] home]# mysqladmin -u root flush-privileges password abcdef Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Q1. How can I change the password for root ? Q2. How do I know if root IS the admin for MySql ? Q3. How can I add a Database ? Q4. How can I add a New User to a New Database ? Thanks in Advance, -Kamal. -- 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 Configuraton on Redhat
Have you gone through all of the troubleshooting options outlined here: http://dev.mysql.com/doc/mysql/en/Access_denied.html There are many links on that page to various other corrective actions you can take in order to restore your access. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 10/13/2004 11:05:01 AM: Hi, This is the only place where I can ask these questions, and would greatly appreciate if anyone can give me some clue on how to resolve these issues. I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Command: [EMAIL PROTECTED] home]# mysqladmin -u root flush-privileges password abcdef Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Q1. How can I change the password for root ? Q2. How do I know if root IS the admin for MySql ? Q3. How can I add a Database ? Q4. How can I add a New User to a New Database ? Thanks in Advance, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table creation Time stamp
By Jove! you are right! Sorry! I can't think of an easy way to find those dates. Mr. Tuuri, any ideas? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Anil Doppalapudi [EMAIL PROTECTED] wrote on 10/13/2004 11:04:53 AM: i see the time stamp only for Myisam table type and not for InnoDB table type. where can we find for Innodb Table type Thanks Anil DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 8:18 PM To: Anil Doppalapudi Cc: [EMAIL PROTECTED] Subject: Re: Table creation Time stamp use the command: SHOW TABLE STATUS http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Anil Doppalapudi [EMAIL PROTECTED] wrote on 10/13/2004 10:42:11 AM: Hi, where can we find the time stamp of table creation i.e when that table was created Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT SUM + 2 JOINS
It's awfully hard for anyone to help without seeing the SQL that you are trying to execute. Ideally, the full descriptions of the tables involved and a few rows of sample data would make it a lot easier for us to be able to visualize what you are doing but you sometimes get a useful answer from the SQL alone. The other thing that is vital for a good answer to this kind of question is knowing which version of MySQL you are running; later versions have much greater SQL capabilities than earlier versions. Rhino - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:22 AM Subject: SELECT SUM + 2 JOINS I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my_thread_init
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 more than once without first ending, but assuming you
get them all matched up right it is alright to have multiple init/end
pairs within a thread.
I do this in my own code, which has worked well for sometime now, so
while the manual doesn't make it completely clear this is ok, I'm pretty
sure it is.
One note, I see you write my_thread_init/my_thread_end in your message,
the correct names are mysql_thread_init and mysql_thread_end, the
my_init function should be called once per process, not in each thread.
John
On Wed, 2004-10-13 at 10:39 +0100, Philippe Poelvoorde wrote:
Hi,
I'm using the C api within a multithread environement. Is that allright
if I have a function that looks like this :
saveParam(){
my_thread_init();
[connexion/query/close]
my_thread_end();
}
Can I call it several time from the same thread ? Or do I have to do
my_thread_init/end only once for each thread ?
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from two tables not returning the expected number of rows
Ted Byrne wrote: you didn't say that (svr,started,ended,volume,who) is a unique key... If not, it would explain the result and the extra rows you get. Well... that combination of fields *should* be unique. Does a unique index need to be specified on those columns? It is possible that (svr,started,ended,volume,who) may not be unique, and that duplicate data was inserted into the table. I was assuming that my beginning SQL skills were to blame for the unexpected results. If I understand your comment correctly, if the combination of columns shown above is indeed a unique key, then the results of my query should have been what I expected. Please correct me if that is not the case. Sorry, I wasn't clear. When you do your query : select count(*) from x.table_a,y.table_b WHERE x.table_a.svr = y.table_b.svr and x.table_a.started = y.table_b.started and x.table_a.ended = y.table_b.ended and x.table_a.volume = y.table_b.volume and x.table_a.who = y.table_b.who; I understand that you expect the combination of (svr,started,ended,volume,who) be unique accross all rows. To my opinion, that's not the case. Try remove duplicate entry, with the help of previous post here. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB and foreign keys
Hello people, does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? regards Uli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_insert_id() for UPDATE?
At 11:10 -0400 10/13/04, Michael Ragsdale wrote: I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). If the record did not exist and UPDATE performed as INSERT, then I would theoretically have no problem. That condition doesn't apply, because UPDATE never performs an INSERT. Hower, since the record that I'm updating already exists, the AUTO_INCREMENT value also already exists and that value is not being re-generated. Therefore, the value that mysql_insert_id() is returning is zero. Is there a similar way to capture the id of the record that is being updated? Not in the way you suggest. However, if you're updating a single record, I would think that you're doing so on the basis of some primary key that you specify in WHERE clause. If the primary key is your AUTO_INCREMENT column, then you already know the value you want. If it's not your AUTO_INCREMENT column, you can use it in a select to retrieve the AUTO_INCREMENT column. I suppose another way to do this would be to add ai_col=LAST_INSERT_ID(ai_col) to the SET clause of your UPDATE statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_insert_id() for UPDATE?
Didn't you have to specify the ID of the record in order to UPDATE it? If not, and you updated several records, and assuming that the function LAST_INSERT_ID worked as you had expected it to work, you would have still only gotten one of the ID values for one of the updated records, right? What would you have done to track the other, non-reported records? If you need the IDs of the records you are updating, you may need to perform this in two steps surrounded by either TABLE LOCKS or within a TRANSACTION so that things don't change between when you query for a list of IDs to update and actually perform the updates. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael Ragsdale [EMAIL PROTECTED] wrote on 10/13/2004 11:10:12 AM: I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). If the record did not exist and UPDATE performed as INSERT, then I would theoretically have no problem. Hower, since the record that I'm updating already exists, the AUTO_INCREMENT value also already exists and that value is not being re-generated. Therefore, the value that mysql_insert_id() is returning is zero. Is there a similar way to capture the id of the record that is being updated? -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT SUM + 2 JOINS
I believe what you want needs two queries, one to total up on the order# the other to total up by customer. I wouldn't recommend trying to do it all at once as they are two different (but similarly asked) questions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Kuebler [EMAIL PROTECTED] wrote on 10/13/2004 11:22:37 AM: I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew
Re: MySql Configuraton on Redhat
Kamal Ahmed wrote: Hi, This is the only place where I can ask these questions, and would greatly appreciate if anyone can give me some clue on how to resolve these issues. I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Command: [EMAIL PROTECTED] home]# mysqladmin -u root flush-privileges password abcdef Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Q1. How can I change the password for root ? Q2. How do I know if root IS the admin for MySql ? Q3. How can I add a Database ? Q4. How can I add a New User to a New Database ? If you have installed the database yourself, you should know the password, _or_ RH set a default password, in which case you should try the redhat list. Try using the password that you actually use for root. (we never know...) And try both : mysql -u root -h localhost mysql -u root but, there is nothing else we can do to help. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Q: outer join w/restriction
Right you are Shawn; Harald's answer was dead-on. Of course, I missed
it the first time, b/c I was distracted by the typo and his answer was
subtle--it took your reply to Martin to make me realize that he'd
rearranged the clauses.
Many thanks to all three of you! --Chris
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 10:33 AM
To: Martin Gainty
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Q: outer join w/restriction
Martin, you are correct in how you determine when to use AND and when to
use OR, but that's not what the original query was trying to find If
you re-read his original post, he wants this query:
SELECT Applicants.AppID,
Applicants.Name,
Applicants.Email
FROM ApplicantStatus
INNER JOIN Applicants
ON Applicants.AppID = ApplicantStatus.AppID WHERE
ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
with two additional columns containing information from the reviews
table. BUT! he only wants those columns populated if the reviewer was
reviewer #2. That's why Harald's answer is correct.
To repeat Harald's answer:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND ReviewerID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
With the ReviewerID condition into the ON clause of the LEFT JOIN (and
not as a condition in the WHERE clause), the query will not join ANY row
from reviews for ANY OTHER reviewer except #2. Good call Harald!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Martin Gainty [EMAIL PROTECTED] wrote on 10/13/2004 10:13:46 AM:
A SQL AND is a restrictive filter
In other words
Your resultset will yield results based on how you structure your
query If you structure your resultset which includes only applicants
who have
been
seen by Reviewer2 then state
SELECT ... FROM
WHERE (CONDITION1 AND Applicant.Reviewer = 'Reviewer2') If you want
applicants which includes ALL reviewers INCLUDING those who
have
been seen by Reviewer2
SELECT ... FROM
WHERE (CONDITION1 OR Applicant Reviewer='Reviewer2') HTH, Martin To
some extent.. sanity is a form of conformity..
~John Nash PhD~
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 8:37 AM
Subject: Re: Q: outer join w/restriction
In article
[EMAIL PROTECTED]
,
Christopher J. Mackie [EMAIL PROTECTED] writes:
There's something I'm not getting about how to put a SELECT
restriction
on a query with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = ''
AND C.Reviewer.ID = 2;
returns only Applicants who have reviews from Reviewer # 2. What
I
want
is *all* applicants who meet the other two criteria (Active, and SCode
=...), and *any* reviews by Reviewer 2 for any of those applicants (if
Reviewer 2 hasn't written for Applicant a, then a should still be in
the
result set, but with the Reviews.* columns as NULL).
When I remove the final ReviewerID = 2 restriction, all of the
right
applicants are in the dataset--but with a lot of extra rows due to
reviews
by other reviewers. How do I get rid of Reviewers {1, 3...n}, without
losing all the applicants who've never met Reviewer #2?
If C.Reviewer.ID is a typo for Reviews.ReviewerID, the solution
is
simple:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND C.Reviewer.ID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
--
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 dynamic librairie client
Hello, :) I have installed the MySQL binary package .tar.gz version 4.1.5. It runs very good. But in this package there isn't dynamic librairie client in the directory lib/ which terminated with .so. I have just this: carotte:/usr/local/mysql/lib# ls libdbug.a libmysql.imp libmysqlclient_r.a libmystrings.a libmygcc.a libmysqlclient.a libmysqld.a libmysys.a carotte:/usr/local/mysql/lib# But I must have the dynamic librairie client because courier-imap don't see .a librairie. How can I get a dynamic librairie client which run on my system? (I have debian on a x86 architecture with a AMD proc) Thanks you, Romain Sorry for my bad english :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Messure time including milliseconds
At 13:09 +0300 10/13/04, Dobromir Velev wrote: Hi, I think it is better to implement this measurment in your application - thus you'll have an estimate of the time needed to execute your procedures including the mysql calls. Otherwise you can use the BENCHMARK MySQL function http://mysql.online.bg/doc/mysql/en/Information_functions.html for example mysql select benchmark(1000,procedure_name); +--+ | benchmark(1000,procedure_name) | +--+ |0 | +--+ 1 row in set (0.15 sec) the average time to execute the procedure will be 0.15/1000 = 0.00015 seconds Actually, that won't work. BENCHMARK() always returns 0. The time you see is wallclock time determined by the mysql client program. In your own applications, you need to measure the time yourself. HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Tuesday 12 October 2004 19:47, Thomas Schager wrote: Hi, I need to messure the time needs of my procedures in MySQL. How can I reach a messurement of time intervals that include milliseconds, written in MySQL SQL statements? Thanks for any ideas, Thomas -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize query help
Hi,
We log all sessions on our webserver to a table, and then periodically run a
batch process to analyze the visitors ip addresses to determine the
city/region/country of origin.
we use MySQL 4.1.1-alpha-standard-log on Redhat AS, installed from the mysql
RPM.
The query below takes about 1 second to run, which is fine on its own, but
it has to be run for 35+ locations.
I *think* I've created all possible indexes to speed this up, but if anyone
could help it would be much appreciated.
cheers, chris
QUERY
--
/* The concat is required to stop Coldfusion seeing this column as binary
data */
SELECT CONCAT(country.name, '') AS country, ipaddress.region,
ipaddress.city,
/* Calculate distance in miles of visitor to city they are visiting */
ROUND(3957 * 2 *
ATAN2(SQRT(POW((SIN(0.0174*(ipaddress.latitude-56.847)/2)),2)
+ COS(0.0174*56.847) * COS(0.0174*ipaddress.latitude)
* POW((SIN(0.0174*(ipaddress.longitude--2.477)/2)),2)),SQRT(1
-(POW((SIN(0.0174*(ipaddress.latitude-56.847)/2)),2)
+ COS(0.0174*56.847) * COS(0.0174*56.847)
* POW((SIN(0.0174*(ipaddress.longitude--2.477)/2)),2) AS
distance,
COUNT(ipaddress.ipnumber) AS visits
FROM (country INNER JOIN (ipaddress
INNER JOIN sessionLog ON ipaddress.ipnumber = sessionLog.ipnumber)
ON country.code = ipaddress.country)
INNER JOIN userAgent ON sessionLog.browser_id = userAgent.id
WHERE sessionLog.initialDirectory = 1
AND userAgent.searchEngine = 0
AND sessionLog.sessionStartTime BETWEEN {ts '2004-09-01 00:00:00'}
AND {ts '2004-09-30 23:59:59'}
GROUP BY country, region, city
ORDER BY visits DESC
EXPLAIN OUTPUT
++-+++--
--++-+--
--+--+-+
| id | select_type | table | type | possible_keys
| key| key_len | ref| rows |
Extra |
++-+++--
--++-+--
--+--+-+
| 1 | SIMPLE | country| ALL| [NULL]
| [NULL] | [NULL] | [NULL] | 192 |
Using temporary; Using filesort |
| 1 | SIMPLE | userAgent | eq_ref | PRIMARY
| PRIMARY| 4 | ukcd.sessionLog.browser_id |1 |
Using where |
| 1 | SIMPLE | sessionLog | ref|
idx_sessionloginitdirid,idx_sessionlogstarttime,idx_sessionlogipnumber |
idx_sessionlogipnumber | 4 | ukcd.ipaddress.ipnumber| 30 | Using
where |
| 1 | SIMPLE | ipaddress | ref| PRIMARY,idx_country
| idx_country| 2 | ukcd.country.code | 52 |
|
++-+++--
--++-+--
--+--+-+
TABLE DEFS
---
# contains 192 rows
CREATE TABLE `country` (
`id` int(11) NOT NULL default '0',
`code` char(2) default NULL,
`name` char(100) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
# 10,000 rows
CREATE TABLE `ipaddress` (
`ipnumber` int(10) unsigned NOT NULL default '0',
`country` char(2) NOT NULL default '',
`region` varchar(25) NOT NULL default '',
`city` varchar(25) NOT NULL default '',
`postalCode` varchar(8) NOT NULL default '',
`latitude` double NOT NULL default '0',
`longitude` double NOT NULL default '0',
`dmacode` varchar(100) NOT NULL default '',
`areacode` varchar(100) NOT NULL default '',
`isp` varchar(100) NOT NULL default '',
`errcode` varchar(100) NOT NULL default '',
`organisation` varchar(100) NOT NULL default '',
PRIMARY KEY (`ipnumber`),
KEY `idx_country` (`country`),
KEY `idx_city` (`city`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
# 250,000 rows
CREATE TABLE `sessionLog` (
`session_id` varchar(70) NOT NULL default '',
`browser_id` int(11) NOT NULL default '0',
`sessionStartTime` datetime NOT NULL default '-00-00 00:00:00',
`initialDirectory` int(11) NOT NULL default '0',
`ipnumber` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`session_id`),
KEY `idx_sessionloginitdirid` (`initialDirectory`),
KEY `idx_sessionlogstarttime` (`sessionStartTime`),
KEY `idx_sessionlogipnumber` (`ipnumber`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
# 2,000 rows
CREATE TABLE `userAgent` (
`id` int(11) NOT NULL auto_increment,
`uaString` varchar(255) NOT NULL default '',
`searchEngine` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_ua` (`uaString`)
) TYPE=MyISAM
RE: SELECT SUM + 2 JOINS
OK, that makes better sense. Please respond to the list (you should always try keep any list responses CC:ed to the list so that everyone else can help and learn from the exchange, too) with the information that Rhino requested and I am sure someone will be able to help you work out your query. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Kuebler [EMAIL PROTECTED] wrote on 10/13/2004 11:53:27 AM: Sorry, I said it wrong. I want the total purchased in an order and the total charged for that order. I am only looking for totals at an order level, not by customer. Best Regards, Andrew Kuebler AK Communications, Inc. Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) http://www.akcomm.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:37 AM To: Andrew Kuebler Cc: [EMAIL PROTECTED] Subject: Re: SELECT SUM + 2 JOINS I believe what you want needs two queries, one to total up on the order# the other to total up by customer. I wouldn't recommend trying to do it all at once as they are two different (but similarly asked) questions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Kuebler [EMAIL PROTECTED] wrote on 10/13/2004 11:22:37 AM: I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew
Re: InnoDB and foreign keys
I am not certain that Foreign Keys (FKs) are even considered when evaluating a SELECT statement. I know that the indexes that the FKs point to in either table play a major role in determining the execution plan but I don't think that the FKs actually participate in SELECT queries. Now, does having those tables so well-indexed play a role in improving query performance? Probably, but you should use the EXPLAIN command to find out for sure: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ulrich Seppi [EMAIL PROTECTED] wrote on 10/13/2004 11:33:09 AM: Hello people, does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? regards Uli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and foreign keys
does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? Why should it increase performance? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and foreign keys
On Wednesday 13 October 2004 10:33 am, Ulrich Seppi wrote: Hello people, does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. Huh? You might want to read what a foreign key is.. It has nothing to do with speeding anything up.. http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html Jeff pgpeUrEIaz9k1.pgp Description: PGP signature
RE: SELECT SUM + 2 JOINS
I've been asked some questions, so here is some add'l info: Here is a snapshot of the order contents: mysql SELECT * FROM OrderContents WHERE OrderID = 3116; +---+-+-+---+-+--+-- ---+ | ContentID | OrderID | InventoryID | Price | Qty | QtyAvail | ShipDate| +---+-+-+---+-+--+-- ---+ | 2425 |3116 | 317 | 19.50 | 1 |1 | 2004-09-21 13:44:34 | | 2426 |3116 | 347 | 19.50 | 1 |1 | 2004-09-21 13:44:34 | | 2427 |3116 | 367 | 19.50 | 1 |1 | 2004-09-21 13:44:34 | | 2428 |3116 | 587 | 31.50 | 1 |1 | 2004-09-21 13:44:34 | | 2429 |3116 | 627 | 31.50 | 1 |1 | 2004-09-21 13:44:34 | | 2430 |3116 | 923 | 38.50 | 1 |1 | 2004-10-05 12:30:43 | | 2431 |3116 |1199 | 42.50 | 1 |1 | 2004-09-30 10:28:18 | | 2432 |3116 |1236 | 44.50 | 1 |1 | 2004-10-05 12:30:43 | | 2433 |3116 |1263 | 44.50 | 1 |1 | 2004-09-21 13:44:34 | | 2434 |3116 |1492 | 46.50 | 1 |1 | 2004-10-05 12:30:43 | | 2435 |3116 |1505 | 38.50 | 1 |1 | 2004-10-05 12:30:43 | +---+-+-+---+-+--+-- ---+ Here is a snapshot of the charge details: mysql SELECT ANID, PaymentID, OrderID, ApprovalCode, Amount, mysql TransactionType FROM AuthorizeNet WHERE OrderID = 3116; +--+---+-+--+++ | ANID | PaymentID | OrderID | ApprovalCode | Amount | TransactionType| +--+---+-+--+++ | 2188 | 660 |3116 | 165627 | 183.00 | prior_auth_capture | | 2832 | 660 |3116 | 127447 | 42.50 | auth_capture | | 3379 | 660 |3116 | 164026 | 168.00 | auth_capture | +--+---+-+--+++ 6 rows in set (0.01 sec) I'm trying to get the two total amounts by OrderID in 1 Query... I'm using MySQL 4.1.4 Best Regards, Andrew Kuebler AK Communications, Inc. Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) http://www.akcomm.com http://www.akcomm.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:58 AM To: Andrew Kuebler Cc: [EMAIL PROTECTED] Subject: RE: SELECT SUM + 2 JOINS OK, that makes better sense. Please respond to the list (you should always try keep any list responses CC:ed to the list so that everyone else can help and learn from the exchange, too) with the information that Rhino requested and I am sure someone will be able to help you work out your query. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Kuebler [EMAIL PROTECTED] wrote on 10/13/2004 11:53:27 AM: Sorry, I said it wrong. I want the total purchased in an order and the total charged for that order. I am only looking for totals at an order level, not by customer. Best Regards, Andrew Kuebler AK Communications, Inc. Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) http://www.akcomm.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:37 AM To: Andrew Kuebler Cc: [EMAIL PROTECTED] Subject: Re: SELECT SUM + 2 JOINS I believe what you want needs two queries, one to total up on the order# the other to total up by customer. I wouldn't recommend trying to do it all at once as they are two different (but similarly asked) questions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Kuebler [EMAIL PROTECTED] wrote on 10/13/2004 11:22:37 AM: I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew
Re: mysql_insert_id() for UPDATE?
- Original Message - From: Michael Ragsdale [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:10 AM Subject: mysql_insert_id() for UPDATE? I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). I can't imagine why mysql_insert_id() would be changed by an UPDATE statement. I can't help but wonder if this is simply an error in the documentation. If you look at the article at http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html, it also states that mysql_insert_id() is changed by INSERT and UPDATE. However, when it lists the exact circumstances under which mysql_insert_id() changes, it doesn't list any involving UPDATE. Also, if you look at the article on the UPDATE statement, there is no mention of mysql_insert_id() changing as a result of UPDATE. If the record did not exist and UPDATE performed as INSERT, then I would theoretically have no problem. As far as I know, an UPDATE in MySQL *never* does an INSERT, it only changes values on the rows that satisfy the update statement. Hower, since the record that I'm updating already exists, the AUTO_INCREMENT value also already exists and that value is not being re-generated. Therefore, the value that mysql_insert_id() is returning is zero. According to http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html, if the previous statement returned an error, the value of mysql_insert_id() is undefined. Is it possible that your statement failed and that is why you are getting zero in mysql_insert_id()? For example, perhaps you tried to set an integer column to a value that was too big for an integer column. Is there a similar way to capture the id of the record that is being updated? How many rows are you changing in your UPDATE statement? Let me take a minute to review some SQL basics with you. I'm not clear how knowledgeable you are so forgive me if you know this already. Your update statement controls which row or rows are being updated. For example: --- update employees set salary = salary * 1.05 where empno = 123 --- The preceding statement gives a 5% salary increase to the person whose employee number is 123. Assuming that empno is a unique (or primary) key, then only that one row got changed. [If empno is NOT a unique or primary key, all rows that had an empno of 123 are updated. I feel strongly that every table should have a primary key although there might, very rarely, be a case that justifies a table with no primary key.] If your updates are based on a primary or unique key, the statement itself tells you exactly which row was updated by the statement. --- update employees set salary = salary * 1.05 where deptno = 'D21' --- The preceding statement gives a 5% raise to every employee in department D21, regardless of how many there are. --- update employees set salary = salary * 1.05 --- The preceding statement gives a 5% raise to every employee that is in the table at the time the update takes place. If you execute the second and third examples, it should be self-evident that potentially thousands or millions of rows will be affected. Would you really expect MySQL to display a list of all the keys of the rows that are being changed? I expect not. Why would you want only part of the list of keys that was updated, either the first or last one? Offhand, I can't think of a good reason for having either one of them. Are you executing this update from a command line or in a script or a program? If you are using a program, what language are you using? Also, what version of MySQL are you running? I'm sorry if my reply is a bit rambly; I'm really just thinking out loud because I'm not very clear on what you are doing. If you could explain a bit more about what you are trying to do, particularly the number of rows you are attempting to update, we might be able to make some useful suggestions. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: InnoDB and foreign keys
HELLO does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? Why should it increase performance? At the moment I have the hole database with MyIsam tables but there is very much data on it. I have more tables with over 500.000 record and over 100 MB but until now all queries are fast. The problem is that sometime happens that tables are corrupt and I have to REPAIR they. (mysql 4.1.5). All operations on the corrupt tables until REPAIR will fail and this is a big problem because more hundred people are working on the database at the same time and then much data will be lost. Now, I tried on a testdatabase to convert the tables to InnoDB to be more stable and to have transactions. The result is that all operations (select queries) are verry verry slow. Not as fast as on MyISAM. How could I increase the performance of my InnoDB, now? I hoped that it could be done with foreign keys but it not seems so... does anybody have other ideas for solving the problem? thanks... Uli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: SELECT SUM + 2 JOINS
*Please* send your followups to the list. This enables others to help rather than placing the burden solely on me. It also ensures that the conversation, including the solution, appear in the mailing list archives where they can be of benefit to others down the road. If you want to copy me on your followup, that is okay. Rhino - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 12:11 PM Subject: RE: SELECT SUM + 2 JOINS I forgot, I'm using MySQL 4.1 Best Regards, Andrew Kuebler AK Communications, Inc. Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) http://www.akcomm.com -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:32 AM To: Andrew Kuebler; [EMAIL PROTECTED] Subject: Re: SELECT SUM + 2 JOINS It's awfully hard for anyone to help without seeing the SQL that you are trying to execute. Ideally, the full descriptions of the tables involved and a few rows of sample data would make it a lot easier for us to be able to visualize what you are doing but you sometimes get a useful answer from the SQL alone. The other thing that is vital for a good answer to this kind of question is knowing which version of MySQL you are running; later versions have much greater SQL capabilities than earlier versions. Rhino - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:22 AM Subject: SELECT SUM + 2 JOINS I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew -- 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_insert_id() for UPDATE?
At 11:36 AM 10/13/2004, Paul DuBois wrote:
At 11:10 -0400 10/13/04, Michael Ragsdale wrote:
I've been using mysql_insert_id() with great success, but now I've got a
problem. I'm using UPDATE to, well, update a record in a database and
according to the docs...
mysql_insert_id() is updated after INSERT and UPDATE statements that
generate an AUTO_INCREMENT value or that set a column value to
LAST_INSERT_ID(expr).
If the record did not exist and UPDATE performed as INSERT, then I would
theoretically have no problem.
That condition doesn't apply, because UPDATE never performs an INSERT.
DOH! Sorry, I was thinking REPLACE, not UPDATE.
Hower, since the record that I'm updating already exists, the
AUTO_INCREMENT value also already exists and that value is not being
re-generated. Therefore, the value that mysql_insert_id() is returning
is zero. Is there a similar way to capture the id of the record that is
being updated?
Not in the way you suggest. However, if you're updating a single record,
I would think that you're doing so on the basis of some primary key that
you specify in WHERE clause. If the primary key is your AUTO_INCREMENT
column, then you already know the value you want. If it's not your
AUTO_INCREMENT column, you can use it in a select to retrieve the
AUTO_INCREMENT column.
Yes, I am actually using a two-column key, but it is not the AUTO_INCREMENT
column. I realize I can execute another SELECT to get that column, but was
hoping for something similar to $sth-{'mysql_insertid'} that may have been
more optimal. I guess not.
Thanks anyway.
-Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Some basic and advanced replication questions
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 does not answer all my questions): (a) in 4.0.20d there is no way for a kind of Master-Master replication, means, that both databases replicate to each other, right? (b) Will there be such a feature in Version 5 (and does anyone know when it is planned to declare version 5 as stable and productive)? (c) Or is clustering a kind of Master-Master replication? (d)Is it possible to have a MySQL database server being Slave of a Master A and serving as Master for a Slave B at the same time and for the same database? (e) When i have a Master-Slave replication, what happens when i delete a replicated record on the Slave - will the record than be replicated again or will the record remain deleted? So far my questions and i'm aware that some of them sound quiet wired, so i will try do explain what i'm trying to do: I have an application that runs on a server using a MySQL database that is also installed on that server. Besides i have the same server with the same configuration as a hot standby in case of something bad happens to my first (productive) server. Let's refer to them as Server A (active) and B (hot standby). Both servers have a public Network interface and a private Network interface. On the public side, they have the SAME IP Address. To avoid collisions, the are connected to a managed switch where only the port to Server A is active and is switch to port to Server B in case of emergency. On the private Interface both servers carry a own, unique private IP Address. Since Server B acts as hot standby, it's database should always be up to date, so i planned to use replication between Server A and B. A would act as Master, B as Slave. So far, so good. Now there is a third MySQL server (let's call it C) at a different location (interconnection by a VPN over the internet). This server needs the data from a table of my database in almost-realtime. So i thought, replication would be a good mechanism to do that. Now there are some problems: (a) I know how to setup a replication A Master, C Salve, but what happens if A goes down and we have to switch to the hot standby B? B is configured as Slave in relation to A, so C would not be able to get Data from B. Is there a way to setup a Master-Slave relation from B to C so C could replicate the same data from B as it did before from A? (b) The Application on Server C is going to delete records that have been replicated. What happens then, will C replicate the deleted records again or will they remain deleted on C? On A or B they should never be deleted, regardless of what is done on C? (c) After an case of emergency when i want to bring A up again and let it server as active Server again, how would i be able to replicate the records that have been created on B during the absence of A? At the moment, i see no other way as to do this manually, as long as there is nothing like Master-Master replication. Any other ideas? Maybe i'm on a totally wrong track, maybe all some of you cracks out there have a better idea how to solve this? Would clustering be such a solution? Are there any other kind of synchronisation/replication tools that would allow me to setup a configuration as needed? I'm very thankful for any kind of support. Thanks a lot Frank
Fw: SELECT SUM + 2 JOINS
*Please* send your followups to the list. This enables others to help rather than placing the burden solely on me. It also ensures that the conversation, including the solution, appear in the mailing list archives where they can be of benefit to others down the road. If you want to copy me on your followup, that is okay. Rhino - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 12:10 PM Subject: RE: SELECT SUM + 2 JOINS Here is a snapshot of the order contents: mysql SELECT * FROM OrderContents WHERE OrderID = 3116; +---+-+-+---+-+--+-- ---+ | ContentID | OrderID | InventoryID | Price | Qty | QtyAvail | ShipDate| +---+-+-+---+-+--+-- ---+ | 2425 |3116 | 317 | 19.50 | 1 |1 | 2004-09-21 13:44:34 | | 2426 |3116 | 347 | 19.50 | 1 |1 | 2004-09-21 13:44:34 | | 2427 |3116 | 367 | 19.50 | 1 |1 | 2004-09-21 13:44:34 | | 2428 |3116 | 587 | 31.50 | 1 |1 | 2004-09-21 13:44:34 | | 2429 |3116 | 627 | 31.50 | 1 |1 | 2004-09-21 13:44:34 | | 2430 |3116 | 923 | 38.50 | 1 |1 | 2004-10-05 12:30:43 | | 2431 |3116 |1199 | 42.50 | 1 |1 | 2004-09-30 10:28:18 | | 2432 |3116 |1236 | 44.50 | 1 |1 | 2004-10-05 12:30:43 | | 2433 |3116 |1263 | 44.50 | 1 |1 | 2004-09-21 13:44:34 | | 2434 |3116 |1492 | 46.50 | 1 |1 | 2004-10-05 12:30:43 | | 2435 |3116 |1505 | 38.50 | 1 |1 | 2004-10-05 12:30:43 | +---+-+-+---+-+--+-- ---+ Here is a snapshot of the charge details: mysql SELECT ANID, PaymentID, OrderID, ApprovalCode, Amount, TransactionType FROM AuthorizeNet WHERE OrderID = 3116; +--+---+-+--+++ | ANID | PaymentID | OrderID | ApprovalCode | Amount | TransactionType| +--+---+-+--+++ | 2188 | 660 |3116 | 165627 | 183.00 | prior_auth_capture | | 2832 | 660 |3116 | 127447 | 42.50 | auth_capture | | 3379 | 660 |3116 | 164026 | 168.00 | auth_capture | +--+---+-+--+++ 6 rows in set (0.01 sec) I'm trying to get the two total amounts by OrderID in 1 Query... Best Regards, Andrew Kuebler AK Communications, Inc. Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) http://www.akcomm.com -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:32 AM To: Andrew Kuebler; [EMAIL PROTECTED] Subject: Re: SELECT SUM + 2 JOINS It's awfully hard for anyone to help without seeing the SQL that you are trying to execute. Ideally, the full descriptions of the tables involved and a few rows of sample data would make it a lot easier for us to be able to visualize what you are doing but you sometimes get a useful answer from the SQL alone. The other thing that is vital for a good answer to this kind of question is knowing which version of MySQL you are running; later versions have much greater SQL capabilities than earlier versions. Rhino - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:22 AM Subject: SELECT SUM + 2 JOINS I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped something they haven't paid for. Everything is ok until I do the left join on the payments which there could be more than one of for each order. Is it possible to do what I am asking in one query? I hope I have made sense. Best Regards, Andrew -- 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: AW: InnoDB and foreign keys
Suggestions? Check your indexes. It sounds as though they aren't what they used to be. When you converted to InnoDB, did you accidentally move the data to a slower drive? If so, you may want to move it back to where you had the MyISAM tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ulrich Seppi [EMAIL PROTECTED] wrote on 10/13/2004 12:29:22 PM: HELLO does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? Why should it increase performance? At the moment I have the hole database with MyIsam tables but there is very much data on it. I have more tables with over 500.000 record and over 100 MB but until now all queries are fast. The problem is that sometime happens that tables are corrupt and I have to REPAIR they. (mysql 4.1.5). All operations on the corrupt tables until REPAIR will fail and this is a big problem because more hundred people are working on the database at the same time and then much data will be lost. Now, I tried on a testdatabase to convert the tables to InnoDB to be more stable and to have transactions. The result is that all operations (select queries) are verry verry slow. Not as fast as on MyISAM. How could I increase the performance of my InnoDB, now? I hoped that it could be done with foreign keys but it not seems so... does anybody have other ideas for solving the problem? thanks... Uli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some basic and advanced replication questions
(a) You are mistaken. You can easily setup both as masters and then as slaves to each other. Simply enable the neccesary binary logging on both sides, then use the CHANGE MASTER TO command to set each to point at the other. Now when you do an insert or delete on either it gets replicated to the other. (b) Its already in 4.x as described above, so yes it will be in 5.0 as well. (c) Clustering, is not supported, but you can setup a type of ring network with all updates getting sent from master-slave all the way around the ring so that all servers 2-n get all updates/deletes/inserts. The issue is detecting when a node goes down, and routing around it is a manual process. (d) Yes, see above comments. Just set them all to do binary logging, if you want to have a ring rather than a dual master pair you should set the log-slave-updates option so that each server logs not only its own updates, but those it recieves from its master. (e) If you had just a one way master-slave relationship and you delete data on the slave, that is very bad. The slave is now not the same as the master, the record will not be re-inserted, if you go to update the record on the master, when the command gets replicated to the slave an error will generat and replication will halt awaiting you to manually fix it. However, if you have setup a master/slave pair where replication goes both ways as described in (a) or a ring as described in (c) then you have no issue as all servers will be kept 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 does not answer all my questions): (a) in 4.0.20d there is no way for a kind of Master-Master replication, means, that both databases replicate to each other, right? (b) Will there be such a feature in Version 5 (and does anyone know when it is planned to declare version 5 as stable and productive)? (c) Or is clustering a kind of Master-Master replication? (d)Is it possible to have a MySQL database server being Slave of a Master A and serving as Master for a Slave B at the same time and for the same database? (e) When i have a Master-Slave replication, what happens when i delete a replicated record on the Slave - will the record than be replicated again or will the record remain deleted? So far my questions and i'm aware that some of them sound quiet wired, so i will try do explain what i'm trying to do: I have an application that runs on a server using a MySQL database that is also installed on that server. Besides i have the same server with the same configuration as a hot standby in case of something bad happens to my first (productive) server. Let's refer to them as Server A (active) and B (hot standby). Both servers have a public Network interface and a private Network interface. On the public side, they have the SAME IP Address. To avoid collisions, the are connected to a managed switch where only the port to Server A is active and is switch to port to Server B in case of emergency. On the private Interface both servers carry a own, unique private IP Address. Since Server B acts as hot standby, it's database should always be up to date, so i planned to use replication between Server A and B. A would act as Master, B as Slave. So far, so good. Now there is a third MySQL server (let's call it C) at a different location (interconnection by a VPN over the internet). This server needs the data from a table of my database in almost-realtime. So i thought, replication would be a good mechanism to do that. Now there are some problems: (a) I know how to setup a replication A Master, C Salve, but what happens if A goes down and we have to switch to the hot standby B? B is configured as Slave in relation to A, so C would not be able to get Data from B. Is there a way to setup a Master-Slave relation from B to C so C could replicate the same data from B as it did before from A? (b) The Application on Server C is going to delete records that have been replicated. What happens then, will C replicate the deleted records again or will they remain deleted on C? On A or B they should never be deleted, regardless of what is done on C? (c) After an case of emergency when i want to bring A up again and let it server as active Server again, how would i be able to replicate the records that have been created on B during the absence of A? At the moment, i see no other way as to do this manually, as long as there is nothing like Master-Master replication. Any other ideas? Maybe i'm on a totally wrong track, maybe all some of you cracks out there have a better idea how to solve this? Would clustering be such a solution? Are there any other kind of synchronisation/replication tools that would allow me
Re: AW: InnoDB and foreign keys
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 overhead and row level locking for simple selects. But, it should not be 'very very slow' if MyISAM is 'very very fast' as it sounds like you are saying. John On Wed, 2004-10-13 at 18:29 +0200, Ulrich Seppi wrote: HELLO does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? Why should it increase performance? At the moment I have the hole database with MyIsam tables but there is very much data on it. I have more tables with over 500.000 record and over 100 MB but until now all queries are fast. The problem is that sometime happens that tables are corrupt and I have to REPAIR they. (mysql 4.1.5). All operations on the corrupt tables until REPAIR will fail and this is a big problem because more hundred people are working on the database at the same time and then much data will be lost. Now, I tried on a testdatabase to convert the tables to InnoDB to be more stable and to have transactions. The result is that all operations (select queries) are verry verry slow. Not as fast as on MyISAM. How could I increase the performance of my InnoDB, now? I hoped that it could be done with foreign keys but it not seems so... does anybody have other ideas for solving the problem? thanks... Uli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: outer join w/restriction
You should generally not have any conditions in the ON part that are used to restrict
which rows you want in the result set, but rather specify these conditions in the
WHERE clause
Forgive me for following the documentation!!
Martin
- Original Message -
From: [EMAIL PROTECTED]
To: Martin Gainty
Cc: [EMAIL PROTECTED] ; [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 10:33 AM
Subject: Re: Q: outer join w/restriction
Martin, you are correct in how you determine when to use AND and when to use OR, but
that's not what the original query was trying to find If you re-read his original
post, he wants this query:
SELECT Applicants.AppID,
Applicants.Name,
Applicants.Email
FROM ApplicantStatus
INNER JOIN Applicants
ON Applicants.AppID = ApplicantStatus.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
with two additional columns containing information from the reviews table. BUT! he
only wants those columns populated if the reviewer was reviewer #2. That's why
Harald's answer is correct.
To repeat Harald's answer:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND ReviewerID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
With the ReviewerID condition into the ON clause of the LEFT JOIN (and not as a
condition in the WHERE clause), the query will not join ANY row from reviews for
ANY OTHER reviewer except #2. Good call Harald!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Martin Gainty [EMAIL PROTECTED] wrote on 10/13/2004 10:13:46 AM:
A SQL AND is a restrictive filter
In other words
Your resultset will yield results based on how you structure your query
If you structure your resultset which includes only applicants who have been
seen by Reviewer2 then state
SELECT ... FROM
WHERE (CONDITION1 AND Applicant.Reviewer = 'Reviewer2')
If you want applicants which includes ALL reviewers INCLUDING those who have
been seen by Reviewer2
SELECT ... FROM
WHERE (CONDITION1 OR Applicant Reviewer='Reviewer2')
HTH,
Martin
To some extent.. sanity is a form of conformity..
~John Nash PhD~
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 8:37 AM
Subject: Re: Q: outer join w/restriction
In article
[EMAIL PROTECTED],
Christopher J. Mackie [EMAIL PROTECTED] writes:
There's something I'm not getting about how to put a SELECT restriction
on a query with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = ''
AND C.Reviewer.ID = 2;
returns only Applicants who have reviews from Reviewer # 2. What I want
is *all* applicants who meet the other two criteria (Active, and SCode
=...), and *any* reviews by Reviewer 2 for any of those applicants (if
Reviewer 2 hasn't written for Applicant a, then a should still be in the
result set, but with the Reviews.* columns as NULL).
When I remove the final ReviewerID = 2 restriction, all of the right
applicants are in the dataset--but with a lot of extra rows due to reviews
by other reviewers. How do I get rid of Reviewers {1, 3...n}, without
losing all the applicants who've never met Reviewer #2?
If C.Reviewer.ID is a typo for Reviews.ReviewerID, the solution is
simple:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
AND C.Reviewer.ID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '';
--
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]
Insert Delayed
Does Insert Delayed work on any version of MySql for INNODB tables? The docs state that it does but I keep getting the error that the engine doesn't support this. TIA Dan
speed issue - inserts slowing down selects
We have what is called a data grabber for quotes that is inserting rows into both InnoDb and MyIsam tables. When the data is being imported ( inserted ), it is dramatically slowing clients down that are running queries against the table. Our first attempt at a solution was to insert one row at a time. This doesn't seem to make a difference. The next thing we tried is the Insert Delayed which as I stated in an earlier post, doesn't seem to work for InnoDb tables. We are also looking at Insert Low_Priority. Does anyone have any suggestions? TIA Dan
Creating primary and foreign keys for MySQL
Hi All I am MySQL newby with an Oracle background. Is the following syntax suported? alter table members add primary key (id); alter table members add constraint members_uk unique (name); Thanx Herman __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speed issue - inserts slowing down selects
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 means the total number of inserts you can support is lower, but it sounds like that may be ok, and you just don't want them consuming all resources for short period when they could spread out and still let client requests be fast. I'm not sure why insert delayed didn't work, the manual clearly says it should, I'm going to try it hout shortly and see if it works for me. John On Wed, 2004-10-13 at 12:02 -0700, Daniel Cummings wrote: We have what is called a data grabber for quotes that is inserting rows into both InnoDb and MyIsam tables. When the data is being imported ( inserted ), it is dramatically slowing clients down that are running queries against the table. Our first attempt at a solution was to insert one row at a time. This doesn't seem to make a difference. The next thing we tried is the Insert Delayed which as I stated in an earlier post, doesn't seem to work for InnoDb tables. We are also looking at Insert Low_Priority. Does anyone have any suggestions? TIA Dan
Re: Creating primary and foreign keys for MySQL
You haven't found the online manual yet, have you? http://dev.mysql.com/doc/mysql/en/index.html Specifically the documentation for the command you are curious about is found here: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Herman Scheepers [EMAIL PROTECTED] wrote on 10/13/2004 02:54:17 PM: Hi All I am MySQL newby with an Oracle background. Is the following syntax suported? alter table members add primary key (id); alter table members add constraint members_uk unique (name); Thanx Herman __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert Delayed
Thanks for verifying that. Dan -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 12:23 PM To: Daniel Cummings Cc: [EMAIL PROTECTED] Subject: Re: Insert Delayed I verified the same error for myself, and then found: http://bugs.mysql.com/bug.php?id=5777 Sounds like the documentation is just wrong, and it is not supported for innodb period. John On Wed, 2004-10-13 at 11:53 -0700, Daniel Cummings wrote: Does Insert Delayed work on any version of MySql for INNODB tables? The docs state that it does but I keep getting the error that the engine doesn't support this. TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign key error
I tried to create a foreign key using: ALTER TABLE msg_recipients ADD CONSTRAINT msg_recipient_member_fk FOREIGN KEY (recipient_member_id) REFERENCES members (id); and got this error: Can't create table '.\wapmagic\#sql-c50_48.frm' (errno: 150) Any ideas? Thanx Herman __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key error
run the command: SHOW INNODB STATUS; whenever you get those errors and it will give you more details. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Herman Scheepers [EMAIL PROTECTED] wrote on 10/13/2004 03:42:34 PM: I tried to create a foreign key using: ALTER TABLE msg_recipients ADD CONSTRAINT msg_recipient_member_fk FOREIGN KEY (recipient_member_id) REFERENCES members (id); and got this error: Can't create table '.\wapmagic\#sql-c50_48.frm' (errno: 150) Any ideas? Thanx Herman __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user defined table constraint
Hi all,
I have a table with these fields:
user_id
dept_id
is_primary ('Y' or 'N')
I want to make sure that there are never two rows in this table with the
same user_id and is_primary='Y'. For any user_id, there can only be one
primary record. In MS SQL I would define a user constraint on the table.
Does MySQL have anything similar, or do I need to check the data in
every place I do an insert into this table? Thanks.
fetch certain number of matches
Is there a way to ask mysql to return certain number of matches? This is very useful for search functionality.
Re: Foreign key error
:perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed This is usually due to a missing index. To make a foreign key, both of the involved columns must come first in an index. Do you have indexes on msg_recipients.recipient_member_id and members.id? Michael [EMAIL PROTECTED] wrote: run the command: SHOW INNODB STATUS; whenever you get those errors and it will give you more details. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Herman Scheepers [EMAIL PROTECTED] wrote on 10/13/2004 03:42:34 PM: I tried to create a foreign key using: ALTER TABLE msg_recipients ADD CONSTRAINT msg_recipient_member_fk FOREIGN KEY (recipient_member_id) REFERENCES members (id); and got this error: Can't create table '.\wapmagic\#sql-c50_48.frm' (errno: 150) Any ideas? Thanx Herman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user defined table constraint
That depends, can the user have more than 2 records? as in only 1 yes
record and 1 no record? If that were the case you could create a unique
index on (user_id, is_primary). However, I suspect that is not the case.
If I remember my M$ $QL correctly, User Constraints are evaluated during
INSERT or UPDATE. This implies that they had their own trigger for those
events. Triggers are not *yet* implemented in MySQL (see the TODO lists
for versions =5 ) so I believe that you will need to enforce the only 1
primary record constraint in your application code until the server can
take over in some future version.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Josh Howe [EMAIL PROTECTED] wrote on 10/13/2004 03:45:56 PM:
Hi all,
I have a table with these fields:
user_id
dept_id
is_primary ('Y' or 'N')
I want to make sure that there are never two rows in this table with the
same user_id and is_primary='Y'. For any user_id, there can only be one
primary record. In MS SQL I would define a user constraint on the table.
Does MySQL have anything similar, or do I need to check the data in
every place I do an insert into this table? Thanks.
Re: fetch certain number of matches
Could you possibly describe what you would like to find? Table definitions, sample data, and sample queries (especially ones that you have already tried with explanations of what was wrong with their output) would all help us to answer your question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Elim Qiu [EMAIL PROTECTED] wrote on 10/13/2004 03:55:35 PM: Is there a way to ask mysql to return certain number of matches? This is very useful for search functionality.
Re: user defined table constraint
Excuse me for top-posting but Outlook Express won't put revision bars in
front of your original remarks and I'm too lazy to type them all in myself
;-)
Anyway, if you define one of your columns, such as user_id as a primary key,
you can be sure that there will never be two rows with the same user_id
value, let alone the same user_id value and is_primary value. Wouldn't that
solve your problem without the need for a table constraint?
Rhino
- Original Message -
From: Josh Howe [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 3:45 PM
Subject: user defined table constraint
Hi all,
I have a table with these fields:
user_id
dept_id
is_primary ('Y' or 'N')
I want to make sure that there are never two rows in this table with the
same user_id and is_primary='Y'. For any user_id, there can only be one
primary record. In MS SQL I would define a user constraint on the table.
Does MySQL have anything similar, or do I need to check the data in
every place I do an insert into this table? Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: speed issue - inserts slowing down selects
Daniel, also consider selecting as high priority. That will cause select queries to jump ahead of pending insert queries. That will allow each select to only have to wait for at most one insert to complete. Cheers, -Dana -Original Message- From: Daniel Cummings [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 2:02 PM To: [EMAIL PROTECTED] Subject: speed issue - inserts slowing down selects We have what is called a data grabber for quotes that is inserting rows into both InnoDb and MyIsam tables. When the data is being imported ( inserted ), it is dramatically slowing clients down that are running queries against the table. Our first attempt at a solution was to insert one row at a time. This doesn't seem to make a difference. The next thing we tried is the Insert Delayed which as I stated in an earlier post, doesn't seem to work for InnoDb tables. We are also looking at Insert Low_Priority. Does anyone have any suggestions? TIA Dan - ** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Stores, Inc. Confidential ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fetch certain number of matches
I have a table got about 20 rows. I want my web application be able to perform search (dynamic query on this table). the search can be quite complex because we need to look at other tables through relationships. So the query may be an expensive one. But the ideal thing is to get the count of the matches and then just fetch, say 25 rows at a time to be displayed on a web page. And my question is, give a query, how to let mysql just return certain number of matches? Of course I can let the web app ignore what not be displayed (hide them, say), but there may be expensive memory usage and cpu usage to do so. - Original Message - From: [EMAIL PROTECTED] To: Elim Qiu Cc: [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 2:18 PM Subject: Re: fetch certain number of matches Could you possibly describe what you would like to find? Table definitions, sample data, and sample queries (especially ones that you have already tried with explanations of what was wrong with their output) would all help us to answer your question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Elim Qiu [EMAIL PROTECTED] wrote on 10/13/2004 03:55:35 PM: Is there a way to ask mysql to return certain number of matches? This is very useful for search functionality.
Re: mysql dynamic librairie client
gcc linker will hanlde .a files using -lmysqlclient and -L /path/to/libmysqlclient.a -Eric On Wed, 13 Oct 2004 17:54:09 +0200, Romain Moyne [EMAIL PROTECTED] wrote: Hello, :) I have installed the MySQL binary package .tar.gz version 4.1.5. It runs very good. But in this package there isn't dynamic librairie client in the directory lib/ which terminated with .so. I have just this: carotte:/usr/local/mysql/lib# ls libdbug.a libmysql.imp libmysqlclient_r.a libmystrings.a libmygcc.a libmysqlclient.a libmysqld.a libmysys.a carotte:/usr/local/mysql/lib# But I must have the dynamic librairie client because courier-imap don't see .a librairie. How can I get a dynamic librairie client which run on my system? (I have debian on a x86 architecture with a AMD proc) Thanks you, Romain Sorry for my bad english :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Login error
You need to finish reading the install guide here: http://dev.mysql.com/doc/mysql/en/Post-installation.html -Eric On Tue, 12 Oct 2004 16:31:48 -0400, Kamal Ahmed [EMAIL PROTECTED] wrote: Hi, I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Command: [EMAIL PROTECTED] home]# mysqladmin -u root flush-privileges password abcdef Result: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Q1. How can I change the password for root ? Q2. How do I know if root IS the admin for MySql ? Q3. How can I add a Database ? Q4. How can I add a New User to a New Database ? Thanks in Advance, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2003 server problem
I have gone back to installing 4.02. I have found that no log file or my.ini is been written either. I have already successfully installed mysql 4.02 on another windoze 2003 server. so I don't quite understand why the installation is falling over. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and LOAD DATA LOCAL INFILE
My best guess would be that your slave tables have slightly different data/structure than your master table. Try taking a fresh snapshot from the master and trying again. -Eric On Tue, 12 Oct 2004 10:33:24 -0700, Kenneth Lim [EMAIL PROTECTED] wrote: Hi - I'm seeing these error/warning messages on my replication slave: [ERROR] Slave: load data infile on table 'DeviceItem' at log position 38844696 in log 'db1-bin.01' produced 6 warning(s). Default database: 'senvidsysdb' I was wondering if anyone has seen similar messages. I have master-slave replication setup on 2 Linux boxes running MySQL 4.1.5-gamma. On a Windows client, I execute a script that contains a bunch of LOAD DATA LOCAL INFILE statements. The master error log does not complain. But the slave error log has the error/warning messages above. The data seems to be the same on master and slave. Are there special considerations for using LOAD DATA LOCAL INFILE statements with a replication setup? Thanks. -ken Kenneth Lim Software Engineer Senvid, Inc. 2445 Faber Place, Suite #200 Palo Alto, CA 94303 phone: 650-354-3612 fax: 650-354-8890 email: [EMAIL PROTECTED] http://www.senvid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to flush process which are in sleep state
Sleeping 'processes' are connections left open by clients. Simply change your clients to close the connection whent hey are done (don't use pconnect). Also your wait_timeout is probably still set to the default 28800 seconds. This should be set to match the idle timeout of your client (apache or whatever) -Eric On Wed, 13 Oct 2004 16:48:21 +0530, Anil Doppalapudi [EMAIL PROTECTED] wrote: Hi, when i issue show processlist command it gives very long list of process most of them are sleep state . is there a way to clear those processs with out killing them Thanks in advace ANil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cross database joins performance hit?
I've worked on projects before where splitting up the schema into databases was used. Joins across DB's are fine, but there is another place that the performance can hit you. If you use something like perl's Apache::DBI, you will increase the number of open connections to your database. That's assuming that you'll have connections to each database in your application. On Tue, 12 Oct 2004 17:43:11 -0700, Jason [EMAIL PROTECTED] wrote: I've tried to find references to if there are any design flaws with using multiple databases or not however was unable to locate anything (but I was told by a previous co-worker that there were performance hits). Are there any performance hits or design flaws by separating a large database into separate databases then cross-database joining the tables? (these tables have anywhere between 1m and 5m+ rows) Thanks for any pointers in advance. If theres a major design flaw to splitting a database up into separate databases I'd like to know before getting to far down the path on this project :-) Thanks much for any answers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some basic and advanced replication questions
(e) If you had just a one way master-slave relationship and you delete data on the slave, that is very bad. The slave is now not the same as the master, the record will not be re-inserted, if you go to update the record on the master, when the command gets replicated to the slave an error will generat and replication will halt awaiting you to manually fix it. However, if you have setup a master/slave pair where replication goes both ways as described in (a) or a ring as described in (c) then you have no issue as all servers will be kept consistent. Does anyone have scripts for checking the integrity of their slave servers? I've been writing some pretty in depth stuff in perl and I plan on releasing it eventually. It's pretty ugly right, even for perl. I will eventually clean it up. Some of the tests I do are: - make sure the same tables exist on the master and slaves - compare the schema (columns defs, keys, table types) - check that the row counts match - randomly check a percentage of rows in each table for large tables - for 'small' tables perform the checksumming as found in Sys Admin Mag's Taming the Distributed Database Problem: A Case Study Using MySQL (http://samag.com/articles/2004/0408/) Has anyone already gone through this effort already? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP address to searchable number
In the last episode (Oct 13), Scott Haneda said: Given a IP range such as: 12.8.197.10 - 12.8.197.100 I want to store those 2 values in a database, before I insert a new value, I would like to test for the new values existence. Any idea what I should convert a IP address into in order to be able to operate on it with simple greater than, less than and equal to math? INTEGER UNSIGNED. Use inet_ntoa() and inet_aton() if the language you're using to insert/query can't convert ip addresses itself. http://dev.mysql.com/doc/mysql/en/Miscellaneous_functions.html#IDX1519 -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT and LIMIT question
Why do you think it doesn't work with LIMIT? Do you want 5 of each? Michael Jay K wrote: Hi, I have multiple queries like this: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 desc LIMIT 5 and SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 2 ORDER BY col3 desc LIMIT 5 The only difference is in the WHERE clause table1.col2 = x. I would like to have one statement instead of multiple statements and though about using IN clause like: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 IN (1,2,3) ORDER BY col3 desc LIMIT 5 It works without LIMIT, but I need to use the LIMIT. Any help is appreciated. Thanks in advance, Jay ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join syntax
What do you mean, it doesn't work? Michael Marco wrote: The query select * from a join b on a.x = b.y; works on mySQL 3.23.56 but doesn't work on mySQL 3.23.58. I had to change it in select * from a,b where a.x = b.y; Is it a well-known behavior or is it a bit strange? Thanks, Marco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date range with empty rows (Was: Intra-table join)
On Wed, 13 Oct 2004 20:25:48 -0700, Chris [EMAIL PROTECTED] wrote: create table test_table (d date not null, name char(32) not null, loc integer not null, type integer not null, amount integer, primary key (d, name, loc, type)); snip snip This is how I would do it: SELECT d as day, SUM(amount) as total, SUM(IF(1=loc,amount,0)) as loc1_total, SUM(IF(2=loc,amount,0)) as loc2_total FROM test_table GROUP BY d ; I'm not sure this is the best way to go, as that SUM(IF()) functionality seems a bit unclean to me, but it works. Thanks, that works great! Now, on a somewhat related note, I'm trying to get the data from mysql in a ready to use format with no post-processing required... I'm graphing the data from this query using dates on the x axis. The input to my graph module (GD::Graph) requires a constant-length list. So if any days in my selection range have no data, I need to fill the space with an empy value. For example, say my date range is '2004-10-01' to '2004-10-05' and the query returns: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-04, 250 I have to do some date manipulation in perl afterward to check for missing values... If I could get a query that returned: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-03, NULL 2004-10-04, 250 2004-10-05, NULL That would be so much nicer in some cases. I'm thinking it would be sort of like the output from a LEFT JOIN if I had a table containing just a bunch of sequential dates...but I don't...and the dates can actually be arbitrary, so it would have to be a pretty big table, with no real data in it. Does anyone know of some way to fake this date table? (again, I'm using mysql 4.0.16) ...stuck on these LEFT JOINS lately for some reason ;-) Thanks, -partap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
