Benchmark
I have the following setup : Apache 2 + mod_perl 2 mysql 3.23.54 I need to is measure the perfomance of a ticketing system (written in perl)which has web interface (html::mason, apache2) with Mysql as a backend. Users of the ticketing system can only connect to the backend via the web interface and they usually login to the system at the begining of the the day and remain connected untill they knock of. I have setup two test machines, one with Pg and the other with Mysql. Both machine have the same data (sample). I am looking for a benchmark utilty that the simulate a user session. For example, a user login in, displaying a ticket and searching for tickets; all these invlove a user connecting to the a url, like for search, a user needs to open somehost.domain/path/to/search.html?with=arguments. The utiltly needs to simulate these actions. The following tools currently have so far caught my attention: Apache Jmeter ab I need suggestions for other utilities. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log-bin not created
I am using mysql version 4.0.12 on a solaris sparc box. My server has stopped creating the log-bin files although it is defined in my.cnf. The directory permissions of the data directory are fine. What is wrong with the setup? Sohail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions tutorial
Try Microsoft's Inside SQL Server 2000 by Delaney. -Original Message- From: Thomas Svenson [mailto:[EMAIL PROTECTED] Sent: 15 August 2003 01:44 To: MySQL list Subject: Transactions tutorial I'm looking for any online resources/tutorials and such about transactions. Preferable for MySQL, but others will do. Other than that, is there any good book about transactions. Again preferable for MySQL. /T -- 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]
notification when replication stops.....
hi every now and then my replication stops. i now about this when i logon 2 the slave and check the status. what is the best procedure i would follow 2 automate a notification message when replication stops? i ryn mysql 4.0.13 on rh linux tks _ ___ _ __ /______/ / ___/ / \ // / / / // / / ^ v / / / / /__/ / / / \/ / / /_ / /__ / /_ / /_ / Cell: 083 440 2213 Ph: 021 980 4969
Query with inner join (Help!)
Hello! I ' m trying to extra some information from 2 tables using inner join, but receive an error warning. Am newbie so I' m not able to feature out why my queries don't work. Scenario: I have 2 tables: Customers and orders. The have following structures: Customers Orders cust_id Product Name Price City cust_id All that am trying to get out of these tables is to use a cust_id of a given customer to extract their orders. So I use something like this: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id Can anyone help me on this? Thanks
Re: Query with inner join (Help!)
Hi Fongo, http://www.mysql.com/doc/en/JOIN.html Should work like this (untested): SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers, Orders WHERE Customers.cust_id = Orders.cust_id AND cust_id = 2 inner join Best regards Nils Valentin Tokyo/Japan 2003 8 15 16:44B. Fongo : Hello! I ' m trying to extra some information from 2 tables using inner join, but receive an error warning. Am newbie so I' m not able to feature out why my queries don't work. Scenario: I have 2 tables: Customers and orders. The have following structures: Customers Orders cust_id Product Name Price City cust_id All that am trying to get out of these tables is to use a cust_id of a given customer to extract their orders. So I use something like this: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id Can anyone help me on this? Thanks -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with inner join (Help!)
Hi, You need to put the Join in the FROM section of the query, not the WHERE. That is to say, move this WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id to FROM Customers inner join Orders ON Customers.cust_id = Orders.cust_id You could acutally use this method instead, as the joining fields are the same FROM Customers inner join Orders USING (cust_id) You might want to check out the mySQL manual on www.mysql.com - its damn useful for getting the syntax of queries right! ;) Also, you'll need to tell mySQL which custid field you want to filter by - the WHERE clause needs a tweak. Currently its WHERE cust_id = 2 - however, mySQL needs to know *which* cust_id to use, the Customers or Orders. Simply add Customers. before it and all is sorted. So, the completed query would be: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders USING (cust_id) WHERE customers.cust_id = 2 HTH. Regards Steve. - Original Message - From: B. Fongo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 15, 2003 8:44 AM Subject: Query with inner join (Help!) Hello! I ' m trying to extra some information from 2 tables using inner join, but receive an error warning. Am newbie so I' m not able to feature out why my queries don't work. Scenario: I have 2 tables: Customers and orders. The have following structures: Customers Orders cust_id Product Name Price City cust_id All that am trying to get out of these tables is to use a cust_id of a given customer to extract their orders. So I use something like this: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id Can anyone help me on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with inner join (Help!)
B. Fongo [EMAIL PROTECTED] wrote: I ' m trying to extra some information from 2 tables using inner join, but receive an error warning. Am newbie so I' m not able to feature out why my queries don't work. Scenario: I have 2 tables: Customers and orders. The have following structures: Customers Orders cust_id Product Name Price City cust_id All that am trying to get out of these tables is to use a cust_id of a given customer to extract their orders. So I use something like this: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id Can anyone help me on this? SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id WHERE cust_id = 2; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log-bin not created
Sohail Hasan [EMAIL PROTECTED] wrote: I am using mysql version 4.0.12 on a solaris sparc box. My server has stopped creating the log-bin files although it is defined in my.cnf. The directory permissions of the data directory are fine. What is wrong with the setup? Is extention of log files incremented after FLUSH LOGS? Show me the output of SHOW MASTER LOGS before and after FLUSH LOGS. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is query possible? (Newbie)
Grant, Could you please be a bit more specific? What do you mean when you say I want to create a query that will take all the fields in. If no end_date exists then set to NULL.? Thanks -Original Message- From: Grant Cooper [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2003 4:11 AM To: [EMAIL PROTECTED] Subject: Is query possible? (Newbie) I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. -- 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]
AW: Query with inner join (Help!)
It works! SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id WHERE cust_id = 2; Thanks a lot. -Ursprüngliche Nachricht- Von: Egor Egorov [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 15. August 2003 10:14 An: [EMAIL PROTECTED] Betreff: Re: Query with inner join (Help!) B. Fongo [EMAIL PROTECTED] wrote: I ' m trying to extra some information from 2 tables using inner join, but receive an error warning. Am newbie so I' m not able to feature out why my queries don't work. Scenario: I have 2 tables: Customers and orders. The have following structures: Customers Orders cust_id Product Name Price City cust_id All that am trying to get out of these tables is to use a cust_id of a given customer to extract their orders. So I use something like this: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id Can anyone help me on this? SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id WHERE cust_id = 2; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication blues
Primaria Falticeni SDU [EMAIL PROTECTED] wrote: I met the same problem Problem with auto_increment column? If so I wasn't able to repeat it with master_connect_retry=2. and I noticed that you need, on the slave, set connect_retry to 2 (connect_retry is the time after which the slave retries to connect to master). It's a replication problem met by me on MySQL 4.0.14 on Linux Red Hat 9. I manually managed the replication conflicts until then. I mean conflicts from the late of the update slave - master. Iulian - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 9:49 PM Subject: Re: replication blues Bogdan TARU [EMAIL PROTECTED] wrote: And data is inserted into it with simple inserts, w/o specifing the id (it's autoincrementing). With a little debugging, I have located the problem. If I run 'alter table xxx auto_increment=1' on both the master and the slave (this table is empty at the time on both machines), and then I insert datas into the master, they look like: On master: +++--+++--+---+- ---+ | 1 | 3 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 2 | 4 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 3 | 5 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 4 | 6 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 5 | 13 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 6 | 14 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 7 | 18 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 8 | 19 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 9 | 20 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 10 | 21 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | +++--+++--+--++ But on slave it looks like: +++--+++--+--++ | id | dialer | uid | action | acc_no | template | name | status | +++--+++--+--++ | 10 | 3 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 11 | 4 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 12 | 5 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 13 | 6 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 14 | 13 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 15 | 14 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 16 | 18 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 17 | 19 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 18 | 20 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | | 19 | 21 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY | +++--+++--+--++ Why does it start on the id=10 on the slave? Of course, this is the cause for the replication failures later on, because datas are deleted on the master with 'delete from xxx where id=3', for example, action which doesn't delete anything on the slave (because there is no id=3 entry), thus inconsistency. I'm using 4.0.13 on both machines. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I simply can't compile this
CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT '88866', MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN) )TYPE = INNODB; ALTER TABLE EMPLOYEE ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE EMPLOYEE # here is the buggy code ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE; ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MGRSSN)REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE ; Please tell me what could be wrong, Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with inner join (Help!)
Hi Fongo, Steve gave a perfect explanation ( and made me realize my and Egor's small mistake ;-) 2003 8 15 17:08Nils Valentin : Hi Fongo, http://www.mysql.com/doc/en/JOIN.html Should work like this (untested): SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers, Orders WHERE Customers.cust_id = Orders.cust_id AND cust_id = 2 inner join SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price from Customers, Orders WHERE Customers.cust_id = Orders.cust_id AND customers.cust_id = 2; So you have 3 possibilities (1 above and 2 below ): SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price FROM Customers inner join Orders ON customers.cust_id=Orders.cust_id WHERE customers.cust_id = 2; SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price FROM Customers inner join Orders USING (cust_id) WHERE customers.cust_id = 2; That should wrap it up nicely ;-). Best regards Nils Valentin Tokyo/Japan 2003 8 15 16:44B. Fongo : Hello! I ' m trying to extra some information from 2 tables using inner join, but receive an error warning. Am newbie so I' m not able to feature out why my queries don't work. Scenario: I have 2 tables: Customers and orders. The have following structures: Customers Orders cust_id Product Name Price City cust_id All that am trying to get out of these tables is to use a cust_id of a given customer to extract their orders. So I use something like this: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from Customers WHERE cust_id = 2 inner join Orders ON Customers.cust_id = Orders.cust_id Can anyone help me on this? Thanks -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character case conversion problem
Hi,
I'm running MySQL 3.23.55 on WinXP and
have some problem using non latin charset.
I've added these 2 lines under [mysqld] group
of my.ini:
character-sets-dir = c:/mysql/share/charsets/
default-character-set=cp1251
Then restarted server and created a simple database
with a string fields. 'myisamchk' utility shows that
tables in above db have cp1251 charset.
After I used some PHP page to insert a few records
with a russian cyrillic and english texts into the db table.
But SELECTing back them with a char case conversion
(UPPER(),LOWER(),UCASE(),LCASE()) failed.
All english texts were correctly converted, but russian
strings had only 3-4 char-s (from total 33 letters) converted
and other char-s had original case. I tried a simple select:
SELECT UPPER('...all russian/win1251 lower letters...');
or
SELECT LOWER('...all russian/win1251 upper letters...');
They returns almost same strings exept of 3-4 letters
correctly case converted. Other ones remain intact.
Also I have mixed russian and english letters like:
SELECT UPPER('... russian/win1251 lower letters plus some english
char-s...');
In result, the english char-s were converted correctly but russian had
same problem.
What did I do wrong in using non default charset?
Thanks,
Tumurbaatar S.
P.S. It seems that collating (ORDER BY) works well for cp1251
charset, i.e. char(0xA8) correctly placed after char(0xC5).
Only problem in case conversion.
Also I examined cp1251.conf file and did not find anything wrong.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About Embedded MySQL server
I've installed the last stable MySQL version (4.0.14-standard) form the binary distribution. I'm interested on implementing the embedded server within my clients programs but the distribution do not have included the libmysqld.a library. There is a possible solution to this without having to get the source and compile it. Thanks Vladimir -- BSc. Vladimir Espinosa Angarica Researcher National Bioinformatics Center Ministry of Science Technology and the Environment Industria y San José, Habana Vieja Capitolio Nacional CUBA Fax: (53-7) 8609614 Tel: (53-7) 8604397 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: notification when replication stops.....
Tom Roos wrote: hi every now and then my replication stops. i now about this when i logon 2 the slave and check the status. what is the best procedure i would follow 2 automate a notification message when replication stops? i ryn mysql 4.0.13 on rh linux tks _ ___ _ __ /______/ / ___/ / \ // / / / // / / ^ v / / / / /__/ / / / \/ / / /_ / /__ / /_ / /_ / Cell: 083 440 2213 Ph: 021 980 4969 Tom, I'd use a perl script. You could either write it where it runs in a continuous loop checking every X seconds or use the cron daemon to launch the script. The advantage to using the cron daemon is that daemon will email you if something goes wrong in your script. Just my $.02 walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: I simply can't compile this
Hi! I know older versions of MySQL do not support foreign keys. Find out weather your version do. If not, you may have to do without the foreign keys in your definitions. Best regards Babs -Ursprüngliche Nachricht- Von: Morten Gulbrandsen [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 15. August 2003 12:05 An: [EMAIL PROTECTED] Betreff: I simply can't compile this CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT '88866', MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN) )TYPE = INNODB; ALTER TABLE EMPLOYEE ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE EMPLOYEE # here is the buggy code ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE; ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MGRSSN)REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE ; Please tell me what could be wrong, Yours Sincerely Morten Gulbrandsen -- 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]
Better query method?
Hi all, Long time reader, first time poster, I hope that I get this right. I am setting up a database that reflects a relationship between two records without using the primary key as the tie between the two. I need to make a list of these based on the number of children each has, and I am having a problem with the query set up. Currently I have the query set up to run at number of users +1, and that doesn't seem to be very efficient. The records are something like this. user ID numberName Parent 1John 0 2Don 1 3Joe 1 4Jack 2 5Jill 4 6Jane 4 okay, now I need to be able to make a list of the names, with the number of children that they have, then sort them in order of how many children they have. Basically, I want to make a list that has in numerical order the names of the parent, and how many children they have. I hope that it makes sense the way that I have described it. I have it working, but if my list of parents and children gets long, then it will kill me i think. The above list would display as NameNumber of Children John2 Jack2 Don 1 Joe 0 Jill 0 Jane0 The way that I have it working right now, is I run a query to find out the number of records, then I run a query based on the first user (1), find all the children that belong to that user and store it in an array, then another query based on the second record to discover how many children that user has, so on and so forth until the end of the table is reached. Then i sort the array based on the number of children, the person with the most children goes at the top, and it descends from there. As you can see, I am running a query to find out the number of records, then a query based on each record. If my table gets to 50,000 users then that will be a nightmare I think. Any help to point me in the right direction would be appreciated, I am using MySql 4.x, my local testing server is a Win2k machine, but my web server is a Linux box. I am mainly just looking for a concept for the better query, not the actual code to write it. I just can't seem to think of a better way to structure it. Thanks!
MySQL to syslog
Is there now a way within MySQL to log to syslog? I was reading an archived email conversation between Sinisa Milivojevic and Vincent Stoessel in which they seemed to indicate that there was no need to log to syslog. As Vincent only had a request for timestamps in the error log, Sinisa simply satisfied that demand and moved on. However, I have a different need. I'm setting up several machines on a network, and wish for them all to send logs through stunneled syslog connections to a central logging server, where I can run logcheck et al to generate reports of system abuse and ensure that all services are running smoothly. However, with MySQL logging to it's own file, this presents a problem to me. Some solutions I can see are simply rsync'ing the logs over daily or mounting the directory over NFS and copying the logs. However, both of these require me to set up new services, and/or change already-existing firewall rules, and write new scripts to do the fetching/retrieval, all of which takes time and effort--not to mention, needlessly complicates the system. Have other people expressed a need to do this? I can't imagine that I'm the first person to run into this limitation, so all I can reason is that either a) the functionality's in there, and my Google skills have failed me, or b) there are valid reasons for the lack of inclusion, and there's a simple workaround. If either is the case, please, let me know! Thanks in advance. -- Stephen Touset [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: Better query method?
suppose your table is people. select people.Name, count( *) as cParent from people left join people as p on ( people.ID=p.Parent) group by people.Name order by cParent I have not tested this. You have to make indexes on Parent and ID. Santino At 8:36 -0500 15-08-2003, John Macon wrote: Hi all, Long time reader, first time poster, I hope that I get this right. I am setting up a database that reflects a relationship between two records without using the primary key as the tie between the two. I need to make a list of these based on the number of children each has, and I am having a problem with the query set up. Currently I have the query set up to run at number of users +1, and that doesn't seem to be very efficient. The records are something like this. user ID numberName Parent 1John 0 2Don 1 3Joe 1 4Jack 2 5Jill 4 6Jane 4 okay, now I need to be able to make a list of the names, with the number of children that they have, then sort them in order of how many children they have. Basically, I want to make a list that has in numerical order the names of the parent, and how many children they have. I hope that it makes sense the way that I have described it. I have it working, but if my list of parents and children gets long, then it will kill me i think. The above list would display as NameNumber of Children John2 Jack2 Don 1 Joe 0 Jill 0 Jane0 The way that I have it working right now, is I run a query to find out the number of records, then I run a query based on the first user (1), find all the children that belong to that user and store it in an array, then another query based on the second record to discover how many children that user has, so on and so forth until the end of the table is reached. Then i sort the array based on the number of children, the person with the most children goes at the top, and it descends from there. As you can see, I am running a query to find out the number of records, then a query based on each record. If my table gets to 50,000 users then that will be a nightmare I think. Any help to point me in the right direction would be appreciated, I am using MySql 4.x, my local testing server is a Win2k machine, but my web server is a Linux box. I am mainly just looking for a concept for the better query, not the actual code to write it. I just can't seem to think of a better way to structure it. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is query possible? (Newbie)
If I understand your question correctly, what you want is: SELECT first_table.*, second_table.end_date FROM first_table LEFT JOIN second_table ON first_table.primary_key=second_table.primary_key cheers, Jan Sometime recently Grant Cooper said: I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Janice Wright Ingenta plc [EMAIL PROTECTED] Tel: +44 (0) 01865 799114 http://www.ingentaselect.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character case conversion problem
Tumurbaatar S. [EMAIL PROTECTED] wrote:
I'm running MySQL 3.23.55 on WinXP and
have some problem using non latin charset.
I've added these 2 lines under [mysqld] group
of my.ini:
character-sets-dir = c:/mysql/share/charsets/
default-character-set=cp1251
Then restarted server and created a simple database
with a string fields. 'myisamchk' utility shows that
tables in above db have cp1251 charset.
After I used some PHP page to insert a few records
with a russian cyrillic and english texts into the db table.
But SELECTing back them with a char case conversion
(UPPER(),LOWER(),UCASE(),LCASE()) failed.
All english texts were correctly converted, but russian
strings had only 3-4 char-s (from total 33 letters) converted
and other char-s had original case. I tried a simple select:
SELECT UPPER('...all russian/win1251 lower letters...');
or
SELECT LOWER('...all russian/win1251 upper letters...');
They returns almost same strings exept of 3-4 letters
correctly case converted. Other ones remain intact.
Also I have mixed russian and english letters like:
SELECT UPPER('... russian/win1251 lower letters plus some english
char-s...');
In result, the english char-s were converted correctly but russian had
same problem.
What did I do wrong in using non default charset?
Hmm .. I tested on my Win box with MySQL 4.0.14 and both functions LOWER() and UPPER()
worked like a charm with cp1251. Show me exactly queries which are wrong for you.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL to syslog
I am not sure that MySQL can directly log to syslog but Unix provide this logger - make entries in the system log logger [-t tag] [-p pri] [-i] [-f file] [message...] So you use MysQL to put messages in a file and then use logger to send it to syslogs. This is how we do it too. Regards, -Original Message- From: Stephen Touset [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2003 10:28 AM To: [EMAIL PROTECTED] Subject: MySQL to syslog Is there now a way within MySQL to log to syslog? I was reading an archived email conversation between Sinisa Milivojevic and Vincent Stoessel in which they seemed to indicate that there was no need to log to syslog. As Vincent only had a request for timestamps in the error log, Sinisa simply satisfied that demand and moved on. However, I have a different need. I'm setting up several machines on a network, and wish for them all to send logs through stunneled syslog connections to a central logging server, where I can run logcheck et al to generate reports of system abuse and ensure that all services are running smoothly. However, with MySQL logging to it's own file, this presents a problem to me. Some solutions I can see are simply rsync'ing the logs over daily or mounting the directory over NFS and copying the logs. However, both of these require me to set up new services, and/or change already-existing firewall rules, and write new scripts to do the fetching/retrieval, all of which takes time and effort--not to mention, needlessly complicates the system. Have other people expressed a need to do this? I can't imagine that I'm the first person to run into this limitation, so all I can reason is that either a) the functionality's in there, and my Google skills have failed me, or b) there are valid reasons for the lack of inclusion, and there's a simple workaround. If either is the case, please, let me know! Thanks in advance. -- Stephen Touset [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I simply can't compile this
Morten Gulbrandsen [EMAIL PROTECTED] wrote: [skip] ALTER TABLE EMPLOYEE # here is the buggy code ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE; ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MGRSSN)REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE ; Please tell me what could be wrong, ON DELETE SET DEFAULT is not supported. That is why you can't create foreign key constraints. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: Delete subselect
[EMAIL PROTECTED] wrote: I know that MySQL 3.23.nnn did not support a delete subselect, just wondering what the best/most efficient way to do the following is: delete from table_a where table_a.column_1 in ( select column_1 from table_b); Assuming that column_1 is the same data type and size in both table_a and table_b. Retrieve data using programming language and then delete data in cycle. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bi-directional Replication
Can anyone tell me if your product can or will do Bi-Directional Replication. We are currently using MS-Access which has A Desiggn Master and Replica's that can exchange information (data) back and forth between replica's. Is there any way to do this with MySQL ? We are looking for a database system to replace MS-Access Glen Boyer IT Manager Pacific Event Productions 6989 Corte Santa Fe San Diego, CA 92121 858 450 7763 (direct) 858 458 1173 (fax) www.pacificevents.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bi-directional Replication
mySQL can do that but will break on primary key violations. Also mySQL a slave can only have one master. Try log-slave-updates in my.cnf on the slave so A - B - A or you can have a replication ring A- B - C - A Make sure your server-id is unique among all master / slaves. ---Original Message- --From: Glen Boyer [mailto:[EMAIL PROTECTED] --Sent: Friday, August 15, 2003 11:41 AM --To: '[EMAIL PROTECTED]' --Subject: Bi-directional Replication -- --Can anyone tell me if your product can or will do Bi-Directional --Replication. -- --We are currently using MS-Access which has A Desiggn Master and Replica's --that can exchange information (data) back and forth between replica's. --Is --there any way to do this with MySQL ? -- --We are looking for a database system to replace MS-Access -- --Glen Boyer --IT Manager --Pacific Event Productions --6989 Corte Santa Fe --San Diego, CA 92121 --858 450 7763 (direct) --858 458 1173 (fax) --www.pacificevents.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bi-directional Replication
Glen Boyer wrote: Can anyone tell me if your product can or will do Bi-Directional Replication. I did this some time ago. Host A and B You just configure host A as master and host B as slave of host A. Then you do the same vice versa. There is a description in the manual. A BIs a circle. In theory you should be able to chain any (?) number of hosts in such a circle. You just have to route updates from the origninating host O all the way around in one direction. A -- B -- C -- ... --- O --- ... --- Z It's a pitty that one can't construct a star formed structures though. S2 I S1 -- X -- S3 Have a nice weekend -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About Embedded MySQL server
I've installed the last stable MySQL version (4.0.14-standard) under Linux, (Suse-8.0) kernel 2.4.28-64GB SMP, form the binary distribution that i downloaded from www.mysql.com. I'm interested on implementing the embedded server within my clients programs but the distribution do not have included the libmysqld.a library. There is a possible solution to this without having to get the source and compile it. Thanks Vladimir -- BSc. Vladimir Espinosa Angarica Researcher National Bioinformatics Center Ministry of Science Technology and the Environment Industria y San José, Habana Vieja Capitolio Nacional CUBA Fax: (53-7) 8609614 Tel: (53-7) 8604397 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bi-directional Replication
On 08/15/03 16:01, Andreas wrote: I did this some time ago. Host A and B You just configure host A as master and host B as slave of host A. Then you do the same vice versa. There is a description in the manual. A BIs a circle. I have this set up, but I also have other slaves off of A like this: C ^ | | A B | | V D (note that C and D are simple slaves of A). The problem is that if you update B, it never reaches C and D unless you enable log-slave-updates, but then A and B seem to get into a loop so if you do an update on A, it updates on B which then send the update back to A, etc. I thought that MySQL wasn't supposed to do this (they have different server-id's), but it did. I would love to get this to work. It's a pitty that one can't construct a star formed structures though. S2 I S1 -- X -- S3 This would be even better. -m -- ## Mark T. Dame, VP, Internet Application Development ## MFM Communication Software: http://www.mfm.com/ ## E-mail: mailto:[EMAIL PROTECTED] WWW: http://www.mfm.com/~mdame/ Eagles may soar, but weasels don't get sucked into jet engines. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Secure Database Design Part II
Hey folks - Thanks to everyone who gave input to my concerns. Of course, we don't intend to have the mysql port open to the world. We will have Apache/PHP connect on a unix socket, or to another machine with a cross-cable on non-routeable IPs. But now I have another question. We are working on a web database to allow our suppliers to log on and submit information that they would otherwise fax, email, or phone to us. It would reduce work in our office, and reduce errors in duplication of our information. But, we are very concerned about security! We aren't worried so much about outside hackers as we are about legit users trying to gain access to information they shouldn't. Some of our suppliers are overseas and we think they have no qualms about trying to hack the system, knowing the stunts they have pulled in the past. It would be extremely difficult to pursue any problems legally, since it would be international, and the damage would be already done. So, here's my question. Good database design dictates that I normalize my tables. So, in this simplified example, we have a table of supplier quotes: supplier_id part_id quote_price quote_date All of our suppliers would be drawing from the same table, via php. I'm worried that good database design might be more susceptible to information 'spilling over' -- what if I make a simple mistake and put the wrong supplier_id with a new user's logon? That new user would see all the parts that belong to whatever company I mistakenly associate them with. I'm not so worried about, say, suppliers seeing sales data. All the php pages will be protected by Unix filesystem permissions, so I can be reasonably certain that only those belonging to the suppliers group will be able to execute supplier_*.php. Even if they do load some sales_*.php page, then the MySQL user permissions will stop them from actually seeing any data on the page. So there are two layers of security between sales and suppliers, for example. I would have to make two mistakes for them to have access to sales data. But, when all suppliers are accessing the same pages, it's up to my careful hands to make sure they are pulling only their records out of the table. If I make a mistake in a query, it might pull up other records, or even all records! Of course if I design it completely perfectly the first time, I don't have to worry about anything. But I'm not perfect and I don't make perfect things. So, I'm thinking I should violate good design principles, and setup identical tables for each supplier, salesperson, customer, etc. That way, since they share the same PHP pages, they aren't all pulling data from the same table. If there is any mixup in the query, the user doesn't have the MySQL permission to pull data from another suppliers table. Does this make sense? Steve Lefevre Network Administrator IMI International, Inc. 614.839.2500 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL embedded server example bug
Hi Team: I've found a bug in the example presented in the Official MySQL Manual in the section 9.1.15.6 A Simple Embedded Server Example. I tried to compile the program following the instructions depicted in the manual and a compiling error arose. The error was: gcc -g -W -Wall -I/usr/local/mysql/include -D_THREAD_SAFE -D_REENTRANT -c -o test_libmysqld.o test_libmysqld.c test_libmysqld.c: In function `db_do_query': test_libmysqld.c:125: `res' undeclared (first use in this function) test_libmysqld.c:125: (Each undeclared identifier is reported only once test_libmysqld.c:125: for each function it appears in.) make: *** [test_libmysqld.o] Error 1 I came back to the program and I realized that in the function db_do_query the variable res was declared within the if block, so it's out of scope when it is called in the statement: line 125: mysql_free_result(res); A simple movement of the declaration line to the beginning of the function solve the problem. Regards Vladimir -- BSc. Vladimir Espinosa Angarica Researcher National Bioinformatics Center Ministry of Science Technology and the Environment Industria y San José, Habana Vieja Capitolio Nacional CUBA Fax: (53-7) 8609614 Tel: (53-7) 8604397 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bi-directional Replication
---Original Message- --From: Mark T. Dame [mailto:[EMAIL PROTECTED] --Sent: Friday, August 15, 2003 1:38 PM --To: MySQL Mailing List --Subject: Re: Bi-directional Replication -- --On 08/15/03 16:01, Andreas wrote: -- -- I did this some time ago. -- -- Host A and B -- You just configure host A as master and host B as slave of host A. -- Then you do the same vice versa. -- There is a description in the manual. -- -- -- A BIs a circle. -- -- --I have this set up, but I also have other slaves off of A like this: -- --C -- --^ --| --| -- --A B -- --| --| --V -- --D -- --(note that C and D are simple slaves of A). -- --The problem is that if you update B, it never reaches C and D unless you --enable log-slave-updates, but then A and B seem to get into a loop so if --you do an update on A, it updates on B which then send the update back to --A, etc. I thought that MySQL wasn't supposed to do this (they have --different server-id's), but it did. I would love to get this to work. Sounds like a bug. If a query originated from A and ends back at A it should stop and not get inserted into A's binary log. There might be a server-id logic flaw that is allowing infinite replication loops. I would verify if this is the case (your bin-log should grow very fast with the same query but different server-ids) and write a bug report on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
querie assitance
I need some help with a query. I have two tables in one data base that both have a SSN field. The ssn field in one table contains slashes and the ssn field in the other does not. I know how to use CONCAT() and RIGHT(),MID(), and LEFT() on one of the ssn fields to obtain a result with digits only. But, I need to obtain a result set that includes a field from each table matched by ssn with one field from each. For example, let's say that table 'one' looks like this: '111/22/','John Q Public' Table 'two' looks like this: '11122333','somecode' What I'd like to achieve is a result set of: '11122','John Q Public','somecode' I guess what's throughing me off is using CONCAT() with the other substring items. Can this be done with one query? Mucho appreciation for any advice or assistance. Thanks! ~Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: querie assitance
At 15:52 -0500 8/15/03, Rob Yelvington wrote: I need some help with a query. I have two tables in one data base that both have a SSN field. The ssn field in one table contains slashes and the ssn field in the other does not. I know how to use CONCAT() and RIGHT(),MID(), and LEFT() on one of the ssn fields to obtain a result with digits only. But, I need to obtain a result set that includes a field from each table matched by ssn with one field from each. For example, let's say that table 'one' looks like this: '111/22/','John Q Public' Table 'two' looks like this: '11122333','somecode' What I'd like to achieve is a result set of: '11122','John Q Public','somecode' I guess what's throughing me off is using CONCAT() with the other substring items. Can this be done with one query? Mucho appreciation for any advice or assistance. Thanks! ~Rob For the table that has the field with dashes, sounds like you want to use REPLACE(ssn,'/','') to remove the slashes. That'd probably be simpler than what it sounds like you're doing now. Without seeing your original query, it's difficult to know for sure, but I'd guess you'll want to do something like this: SELECT whatever-fields-you-want FROM t1, t2 WHERE t1.ssn = REPLACE(t2.ssn,'/','') ... -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing imports
I need to have a web application be able to import large amounts of data (400,000 rows of 10 columns). I know how to script it and have it running in the background. However I want to know how I can optimize my insert statements to try to speed things up. Will it help if I insert multiple rows at a time? If so, is there a magic number or range? The same would also go for updates. Thanks, -Jackson -- jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing imports
On 15 Aug 2003 at 16:34, Jackson Miller wrote: However I want to know how I can optimize my insert statements to try to speed things up. Have you read this? http://www.mysql.com/doc/en/Insert_speed.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication and internals
Replicating queries where re-written to fix various bugs, most notably time and auto-increment bugs. Why doesn't replication also enforce rewriting INSERT INTO into INSERT DELAYED / etc. I've notice that the replicating thread in 3.23.5x will wait for a table instead of going onto another log event iff that table is being queried. I would think adding DELAYED under the covers for replication would be a big win for mysql, in keeping a slave consistent and up to date. What would the drawback be? It's not waiting for an auto increment value to be returned is there some other intrinsic problem? This should be a win win for both 3.5x and 4.x
Re: Bi-directional Replication
Eric wrote: Hi, Do you know of a database that can do such a thing? sorry, no I don't. MySQL is the only DBMS with replication I found, yet. There is a replication project with PostgreSQL but it's not in the official distribution the last time I checked. In the announcement of the cooperation of SAP DB with MySQL is replication one point which they explicitely mentioned. I didn't closely look into the other big things like db2, oracle, firebird, adabas and what else there is. Firebird is free too so probaply it could be a candidate. AFAIK replication the way mysql does it is problematic anyway on slow or sporadic connections. I know that implementing replication is not trivial and mysql's way is often a good solution. What about concurring updates ? t0 : host A = host B but they are disconnected (road warrior) t1 : A runs : UPDATE bla SET blub = 42 WHERE id = 4711; t2 : B runs : UPDATE bla SET blub = blub + 1 WHERE id = 4711; t3 : they connect and replicate in both directions t4 : What is the result of : SELECT blub FROM bla WHERE id = 4711; Are the results of both systems the same ? On fast connections ring structured replication should limit the vulnerable timeframe down but what about our traveling dude who dials up every evening with his notebook. Not to mention that a second site with a fast line would have to wait for our road warrior to get it's updates from the main site since the ... Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bi-directional Replication
I'll forward this conversation to the list since I'm no mysql guru and others with more knowledge might step in. Glen Boyer wrote: Let me ask you this, If I may ? You have Server A in San Diego and Server B in New York You have replication between A --- B You have replication between B --- A What happens when your connection is broke, People Still Update A in San Diego while People Still Update B in New York No problem that wasn't there before the break up. Slaves keep asking even if the get no answer for some time. You set up the time intervals in which the slave asks the master. Those intervals could AFAIK be days if you want. What happens when communication is restored? The client knows until where it read the log, e.g. line 4711. So it asks the master for everything after line 4711 the next time they chat again. It is important that the master keeps the logs until every slave has seen them. That is a log rotation problem that gets discussed in the manual, I think. What happens to auto-increment fields? Just avoid them. They are evil if you rely on them as keys. I have the same problem and I think it can only be solved by giving number ranges to the sites like Site 1 : 1 ... 1000 Site 2 : 1001 ... 2000 . . or use 2 field keys to make it somewhat easier: (site_id, sub_id) Still no AUTO_INCREMENT AUTO_INCREMENT get replicated but I wouldn't trust that the other box didn't insert records in the table already and this info merely didn't reach your side, yet. and what happens with the data? Try to be always lucky. ;) A plain slave that mirrors it's master should be OK. It gets all stuff send that happend while it was disconnected. 2 masters may have created update issues in the meantime. I didn't check this in detail, yet but I suppose there could happen anything if both masters work on the same data. What if both update the same record. Then exchange the SQL. Probaply their data is different after they repeat the SQL of their peer. The time between the replication queries is always just relatively short. Probaply locking queries and transactions get replicated but there is a certain timeframe until they reach the other host in this time the other side could change data and those changes could get lost because of replication. I guess you need a environment where the involved sites generally don't touch the other sites data beyond reading. Absolutly reliable distributed databases is by no way a trivial problem. MySQL's approach is good as backup or for load balancing but if you use it beyond that you have to feel pretty lucky or know exacly what your application will do in updates. I can't claim to know mysql well and I'd love to have someone with better insight who tells me I'm wrong. ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure Database Design Part II
Hi Steven, I believe I understand your concerns. I think the only way to reduce the risk of associating the wrong data is tripple checking it by different persons or even better make two tables which you can compare against each other. The second table only readable to you. This way you could always double check the correctness of the distrubutor table (as long as they are in sync ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 16 05:36Lefevre, Steven : Hey folks - Thanks to everyone who gave input to my concerns. Of course, we don't intend to have the mysql port open to the world. We will have Apache/PHP connect on a unix socket, or to another machine with a cross-cable on non-routeable IPs. But now I have another question. We are working on a web database to allow our suppliers to log on and submit information that they would otherwise fax, email, or phone to us. It would reduce work in our office, and reduce errors in duplication of our information. But, we are very concerned about security! We aren't worried so much about outside hackers as we are about legit users trying to gain access to information they shouldn't. Some of our suppliers are overseas and we think they have no qualms about trying to hack the system, knowing the stunts they have pulled in the past. It would be extremely difficult to pursue any problems legally, since it would be international, and the damage would be already done. So, here's my question. Good database design dictates that I normalize my tables. So, in this simplified example, we have a table of supplier quotes: supplier_id part_id quote_price quote_date All of our suppliers would be drawing from the same table, via php. I'm worried that good database design might be more susceptible to information 'spilling over' -- what if I make a simple mistake and put the wrong supplier_id with a new user's logon? That new user would see all the parts that belong to whatever company I mistakenly associate them with. I'm not so worried about, say, suppliers seeing sales data. All the php pages will be protected by Unix filesystem permissions, so I can be reasonably certain that only those belonging to the suppliers group will be able to execute supplier_*.php. Even if they do load some sales_*.php page, then the MySQL user permissions will stop them from actually seeing any data on the page. So there are two layers of security between sales and suppliers, for example. I would have to make two mistakes for them to have access to sales data. But, when all suppliers are accessing the same pages, it's up to my careful hands to make sure they are pulling only their records out of the table. If I make a mistake in a query, it might pull up other records, or even all records! Of course if I design it completely perfectly the first time, I don't have to worry about anything. But I'm not perfect and I don't make perfect things. So, I'm thinking I should violate good design principles, and setup identical tables for each supplier, salesperson, customer, etc. That way, since they share the same PHP pages, they aren't all pulling data from the same table. If there is any mixup in the query, the user doesn't have the MySQL permission to pull data from another suppliers table. Does this make sense? Steve Lefevre Network Administrator IMI International, Inc. 614.839.2500 -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trying to install MySQL on Win98
Hi, I downloaded MySQL 3.23.57 for Windows as I wish to learn MySQL locally and then use the one I already have installed on my UNIX server. I ran setup and rebooted but the WinMySQLadmin program will not start the Server as it has a MyODBC error Driver 3.51 Not Found. I cannot find any reference to this in the books I have, at MySQL.com, or any UserGroups. Any ideas ?? Thanks, Nick. . . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP or Perl?
As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. Does PHP have comprable libraries (especially LWP) and how difficult is it to move from Perl. I hope I don't start a relegious war here I just want some advice. Mark Healey [EMAIL PROTECTED] This account is only for lists to which I've subscribed. Any spammers invite the worst revenge I think I can get away with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
