Little help please
I'm looking for a webbased interface that will allow me to managae mysql databases. However, phpmyadmin has been ruled out due to the fact it requires the username and password to be stored in the config file and that it doesn't have any security to protect the average joe from stumbling across it. So can anyone point me in the direction of a utility that requires a login that checks of the users database then throws that information into the config for the mysql management? I would be greatful for any help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP] Little help please
I'm looking for a webbased interface that will allow me to managae mysql databases. However, phpmyadmin has been ruled out due to the fact it requires the username and password to be stored in the config file No it doesn't. and that it doesn't have any security to protect the average joe from stumbling across it. Ummm... yes it does. Read the installation directions a little more carefully. So can anyone point me in the direction of a utility that requires a login that checks of the users database then throws that information into the config for the mysql management? How about PHPMyAdmin? You can configure it so that it checks the mysql.user table. ---John Holmes... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What different is occur Between InnoDB and other table type
Dear everyone: Above MySQL 4.0 version ,InnoDB type is added ,But I have a confusion about these ! Can somebody tell their difference ,or give me a pointer to find it! Thanks in advantage for your appreciated help! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The Security of MySQL
Hi Dyego, On Wed, Feb 26, 2003 at 04:04:36PM -0300, Dyego Souza do Carmo wrote: MM You should use the filesystem security your operating system provides to MM prevent common users from copying the database files. The directory that MM the database files resides in only needs to allow access by the user MM that the MySQL server is running as. If you do this, only users who know MM the MySQL user's password can 'copy' the databases. I work with notary officers on brazil ... and my product i sell ! , the users (competitors) can be copy my system to sell to other users... this is terrible to my software house... the MySQL is not prepared for this ? exists the method to secure a table with password ? or the secure is only the filesystem permissions ? Securing a table with a password or even using encryption would make things more difficult for your users but by no means impossible. You give them the application that contains the password or encryption key. They just need to find it in your application then, which is more difficult but not impossible. Sue them! (that's what copyright laws are for) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What different is occur Between InnoDB and other table type
Check it out at: http://www.mysql.com/doc/en/Table_types.html --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 10:40 AM To: [EMAIL PROTECTED] Subject: What different is occur Between InnoDB and other table type Dear everyone: Above MySQL 4.0 version ,InnoDB type is added ,But I have a confusion about these ! Can somebody tell their difference ,or give me a pointer to find it! Thanks in advantage for your appreciated help! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
displaying constraints
Hye the list, My tables are MyIsam type. I put some contraints for compatibility. When I enter a desc table ; I cannot see which contraints belong to this table. What is the SQL command line to do this ? Thanks a lot. Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: problem: Loosing .MYD Files
Dear Joshua, the server has been running for 77 days straight. I checked the boot log and saw that it had found errors caused by a hard down on the same partition that has problems now. fsck being done at booting reported that it corrected the errors. Now, however, fsck still reports errors on this partition, reason: hard downing. So it either never fixed the errors or something caused the fs to look like it hadn't been unmounted before shutdown within the last 77 days, although a shutdown never occured. Still I think it's weird that there are only these 6 specific files, MYD and MYI of the same 3 tables, missing. I will try fix the filesystem and see what happens. Thanks for your help! Sincerely Daniel -Original Message- From: Joshua J.Kugler [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 9:41 PM To: Daniel Geske; Jerry; [EMAIL PROTECTED] Subject: Re: problem: Loosing .MYD Files Daniel - This might be a long shot, but since I've actually run into this problem before, here goes: Check your partitions. I had a server on which this very thing happened, and it turned out there were overlapping partitions. This was all fine and dandy until data was written to those overlapping areas, and created BIG problems when fsck was run. It sounds like your server might have gone down hard without a proper shut down, and then run fsck upon startup. Check your boot logs. It is also possible that there was severe corruption on the filesystem as a result of the system going down hard, and fsck did a best guess fix, which blew away your data. I may be totally off base, but your symptoms at least match what mine were. Hope that helps. j- k- On Wednesday 26 February 2003 00:50, Daniel Geske wrote: No, I didn't do anything at all. The server ran since months without any problems, and then suddenly this.. I just found something that may be related: A program started writing errors to a log file saying it couldn't insert into one of the tables that is now missing and the log file grew up to 2 GB. Certainly the mysql problem occured before the disk was full, else the log couldn't have been written. Could mysql delete data files if there's no more diskspace? My monitoring server says there have been 1.3GB left on the server at 18:05. At 18:01 the problem with mysql started. The log ends at 20:13, that must have been when the monitoring program shut down for a yet unknown reason, the pid file still exists. The MySQL server never stopped running. Now I have 12GB free, 2GB less compared to before the problem occured. These 2GB are the log of the monitoring program. Here's the question: who created 10GB of data and where is that data now? The 10GB pretty sure weren't the 3 missing tables - the entire database never gets bigger than 1MB. To get back running for now, is it OK to 'touch tablen.MYD' to recreate the missing files. As I said, the frm files still exist. Hope to hear from you soon. Sincerely, Daniel Geske -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CREATE INDEX is sooo slow! any ideas?
Hi! On Feb 27, Sebastian Stan wrote: OK. but what about this : (AND i don't have a fulltext field !) Here's my table: (lac CHAR (3), ano CHAR (7), bno CHAR (18), cty CHAR (4), dat CHAR (8), tim CHAR (6), dur CHAR (8), ccu CHAR (8), loc CHAR (20), ccl CHAR (12), isdn CHAR (1), ddi char(4)); ..which have 5-6mil records. When I do the following it takes 5-6 hours. After the index it's done the processes list shows mysqld-nt.exe (i use Win2k Server) with a lot of Mem. Usage. Usually it uses 3-4,000k . When i create the index, it's goes to 11,000k and after it's done it takes about a DAY!!! to go down to 3-4,000. You can imagine how frustrating the users are and how my phone gets on fire :) CREATE INDEX ANO ON FACTDET20028 (ANO) ; CREATE INDEX BNO ON FACTDET20028 (BNO) ; CREATE INDEX CTY ON FACTDET20028 (CTY) ; CREATE INDEX DAT ON FACTDET20028 (DAT) ; CREATE INDEX ANOCTYDAT ON FACTDET20028 (ANO, CTY, DAT) ; It's wrong in two ways. First, each time you add an index, MySQL has to rebuild the index file - and all existing indexes as well! So index ANO gets rebuilt 5 times, index BNO - 4 times, etc ! Second - index ANO is absolutely not necessary as it's the prefix of index ANOCTYDAT. It's only wasting space and time. To build indexes use ALTER TABLE FACTDET20028 ADD INDEX BNO (BNO), ADD INDEX CTY (CTY), ADD INDEX DAT (DAT), ADD INDEX ANOCTYDAT (ANO, CTY, DAT); I've tried to create the indexes two ways : 1.before loading the date into table (LOAD DATA local INFILE ... ) 2. after that. Both ways it's the same thing. Still that ALTER TABLE shouldn't be any better than creating indexes on empty table before load data. What SHOW PROCESSLIST says ? It should be repair-by-sorting. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: JOIN compared to WHERE clause
hello i will explain , in my design , i usually join tables with id's table 1 catID - primary unique category table 2 productID - primary unique product_name catID what is the best way to select i usually do select t2.product_name, t1.category, t1.catID from table2 t2 LEFT JOIN table1 t1 ON t2.catID=t1.catID then sometimes select t2.product_name, t1.category, t1.catID from table2 t2 LEFT JOIN table1 t1 ON t2.catID=t1.catID where t2.productID=1 how can i optimise this further , is doing this just to get a category name for example from table2 using an id which i dont usually set as a key be slow ? should i setup catID on table2 as an index key and how ? is doing it this way faster and get the same results ? select t2.product_name, t1.category, t1.catID from table2 t2, table1 t1 where t2.catID=t1.catID and t2.productID=1 i have found the way i have been doing this very slow on some massive tables and would like to optimise the join if possible -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: Wednesday, February 26, 2003 11:26 PM To: [EMAIL PROTECTED] Subject: Re: JOIN compared to WHERE clause In article [EMAIL PROTECTED], Dan Rossi [EMAIL PROTECTED] writes: hi i was wondering which statement is quicker getting results when joining tables ? i presume something like FROM foo f LEFT JOIN bar b ON f.id=b.id is quiker than WHERE f.id=b.id ? Your question does not make sense. A LEFT JOIN returns different results than an INNER JOIN, so there's no point in performance comparisons. [Filter fodder: SQL query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Duplicate keys?
Hello Have anyone got this error before: ERROR 1062: Duplicate entry '2147483647' for key 1 I get it when I do a simple insert query. The primary key is an auto_increment field and are not specified in the insert. Thanks, Jonas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate keys?
Dnia Thu, 27 Feb 2003 11:22:57 +0100 Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] zezna/a co nastpuje: Hello Have anyone got this error before: ERROR 1062: Duplicate entry '2147483647' for key 1 I get it when I do a simple insert query. The primary key is an auto_increment field and are not specified in the insert. Looks like you have reached maximum number allowed for int. Try to change type of this column to bigint. -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to Join tables in the right way
Hi all, I've got following question: I've got three tables: TABLE t_project (id, name,dossier_id, PRIMARY KEY(id)) TABLE t_subproject (id, name,project_id, PRIMARY KEY(id)) TABLE t_time (id, minutes, subproject_id,PRIMARY KEY(id)) Now I want to dp a SELECT which has following effect: Projectname =A6 minutes testname=A6 300 In this example testname should be a value of the column name from the table t_project. '300' should be the SUM of all 'minutes' (from t_time) which have the the 'subproject_id' of subprojects which have the 'project_id' of the project'name': 'testname' I tried following query: SELECT p.name, SUM( z.minutes ) FROM ( ( t_zeit z INNER JOIN t_subproject sp ON sp.id =3D z.subproject_id ) INNER JOIN t_project p ON= sp.project_id =3D p.id ) GROUP BY p.name But it has not the efeect I want... What is wrong? I hope you understood my problem (sorry for the bad English) and can send me your suggestions Thanks in advance Sorin Marti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in the index used by mysql/Innodb in the search
Description: Hello Alexander, You wrote: From EXPLAIN result output you can do only opposite conclusion: With InnoDB MySQL chooses to use Index TipoFeVCod for which it expects to match 9417 rows. This is about twice less rows than expected with PRIMARY key in second explain 19472 But the fact is that the query SELECT TIPO,DOC,NRE FROM GIROS WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE'01/01') OR (TIPO='R' AND DOC'ZA03003996') OR TIPO'R' ORDER BY TIPO DESC, DOC DESC, NRE DESC LIMIT 1 returns only one record (limit 1), and in MyIsam it returns the record in 0.02 secs. while in innoDB it returns the record in 0.20 secs. (10 times slower) regardless of InnoDB expects to match only 9417 (I think the optimizer is wrong). The number of records in the table Giros is 19507. There are 15278 records of tipo='E' and 4229 of tipo='R'. I think the optimizer is wrong when it expects to match 9417 rows and the WHERE CONDITION matches fully whith the PRIMARY INDEX, so I don´t understand why it chooses the other index. --- On the other hand, let's see the following query which is like the above query: SELECT TIPO,DOC,NRE FROM GIROS WHERE CONCAT(TIPO,DOC,NRE)='RZA0300399601/01' ORDER BY TIPO DESC,DOC DESC,NRE DESC LIMIT 1 INNODB/MyIsam Time: 0.02 secs. INNODB/MyIsam Explain: table type possible_keys key key_len ref rows Extra GIROS index NULL PRIMARY 16 NULL 19516 Using where; Using index Both MyIsam and InnoDB return the record in the same time and use the same index PRIMARY. This query is the same that the above query. --- Also, in this query (I deleted the third condition of the where clause TIPO'R') SELECT TIPO,DOC,NRE FROM GIROS WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE'01/01') OR (TIPO='R' AND DOC'ZA03003996') ORDER BY TIPO DESC, DOC DESC,NRE DESC limit 1 Both MyIsam and InnoDB use the index PRIMARY. InnoDB Time:0.03 secs. Explain: table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 1 Const 1933 Using where; Using index MyIsam Time: 0.02 secs. table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 Null 3910 Using where; Using index I don´t understand why if I add the third condition: OR TIPO'R' InnoDB isn´t still using the PRIMARY INDEX. --- Finally, SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC There is no LIMIT. Table type MyIsam: Returned records: 19486 in 0.59 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index Table type InnoDb: Returned records: 19486 in 1.18 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort With InnoDB, the optimizer believes it must examine 9417 records, but actually it returns 19486 records, the same records as MyIsam but twice slower, so I think the optimizer is okey in MyIsam and wrong in InnoDB. Thanks in advance, Rafa How-To-Repeat: Select ... from giros ... Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.11 Gamma(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: Duplicate keys?
No, the last inserted id is just 1051 or something like that.
Must be something else.
Dunno where '2147483647' comes from.
Is there a way to reset the auto_increment value or something?
First, check out with myisamchk if the table isn't crashed.
Then you can use SET INSERT_ID=value (or similar, I don't
remember exactly)
I've used CHECK TABLE, REPAIR TABLE and no errors were reported.
I've used:
mysql SET INSERT_ID=1034;
Query OK, 0 rows affected (0.00 sec)
mysql alter table user_info change column userid userid int not null
auto_increment;
Query OK, 1026 rows affected (0.02 sec)
Records: 1026 Duplicates: 0 Warnings: 0
And then inserting:
mysql insert into user_info (nickname) values('test');
Query OK, 1 row affected (0.00 sec)
mysql insert into user_info (nickname) values('test');
ERROR 1062: Duplicate entry '2147483647' for key 1
And it get's fuckedup again. Why do the counter freakout??
How do I rebuild the indexfile?
I do not have access to the files, only to the consoleutilities (using
shell acount to my isp).
query = nospam
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.11 perl scripts problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 26 Feb 2003, Alexandre Vaniachine wrote: Perl scripts mysql_convert_table_format mysql_explain_log mysql_find_rows mysql_fix_extensions mysql_fix_privilege_tables mysql_setpermission mysql_tableinfo mysql_zap mysqld_multi mysqlaccess mysqldumpslow mysqlhotcopy from the mysql-standard-4.0.11-gamma-sun-solaris2.8-sparc.tar.gz tarball start with the line: #!/my/gnu/bin/perl the 4.0.9 release had a more traditional (and working) perl location: #!/usr/local/bin/perl Is that a bug or a feature? A bug - thanks for noticing this! We recently moved to a new Solaris 8 build host and it seems like the Perl installation needs to be verified... Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+XfGbSVDhKrJykfIRAkNwAJ9JQDmHEusxRTLkEXS4EJIKzV6nVACeJYs9 LDo6smSgM6SWINd8M5TKzq4= =rDz2 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Text fields Full text search
Does anyone know how to use text fields (BLOB) in the full text search? Hugo PS. sql,query,queries,smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to Join tables in the right way
Hi, Try the following: SELECT p.name, SUM( t.minutes ) FROM t_project p LEFT JOIN t_subproject sp ON (p.id = sp.project_id) LEFT JOIN t_time t ON (sp.id = t.subproject_id) GROUP BY p.id; (i prefer grouping by id than by name). I didn't understand why your query didn't result, don't know the data you have inserted. I tested the above query and i think it gives the results you want. The big difference is that i used LEFT JOIN instead of INNER JOIN because i assumed that you may have projects which have or may have not subprojects, (and you may have defined subprojects which doesn't already have the minutes defined). Hope this helps. On Thu, 2003-02-27 at 10:32, Sorin Marti wrote: Hi all, I've got following question: I've got three tables: TABLE t_project (id, name,dossier_id, PRIMARY KEY(id)) TABLE t_subproject (id, name,project_id, PRIMARY KEY(id)) TABLE t_time (id, minutes, subproject_id,PRIMARY KEY(id)) Now I want to dp a SELECT which has following effect: Projectname =A6 minutes testname=A6 300 In this example testname should be a value of the column name from the table t_project. '300' should be the SUM of all 'minutes' (from t_time) which have the the 'subproject_id' of subprojects which have the 'project_id' of the project'name': 'testname' I tried following query: SELECT p.name, SUM( z.minutes ) FROM ( ( t_zeit z INNER JOIN t_subproject sp ON sp.id =3D z.subproject_id ) INNER JOIN t_project p ON= sp.project_id =3D p.id ) GROUP BY p.name But it has not the efeect I want... What is wrong? I hope you understood my problem (sorry for the bad English) and can send me your suggestions Thanks in advance Sorin Marti -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Crystal 9 cannot see the tables of a mysql database
Hello, I am using mysql 4.0.11 and MyODBC 3.51.05 and Crystal 9 cannot see the tables of a database. Any ideas? thanks, Rafa. __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Text fields Full text search
- Original Message - From: Hugo Wetterberg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 1:45 PM Subject: Text fields Full text search Does anyone know how to use text fields (BLOB) in the full text search? Hugo You may create a fulltext indexes form char, varchar and text columns and you may perform full-text search only ot these indexed columns.. unless you use the IN BOOLEAN MODE modifier, that does not require the filed to be full-text indexed.. so maybe it will work on blob fields as well. PS. sql,query,queries,smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php == Stanimir Dzharkalov Developer Internet Division MobilTel EAD email: [EMAIL PROTECTED] == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: is there a better way to store comments in mysql table?
At 20:43 -0600 2/26/03, Paul DuBois wrote: At 21:32 -0500 2/26/03, Jianping Zhu wrote: I have a guest book which have three fields. user name email comments I want to mysql to store the information, but commnet maybe very long, is there a better way to handle it instead of set a varchar(2000) or more for a field comment in the table? VARCHAR has a maximum length of 2000. In MySQL 4.1, it will be converted automatically to TEXT. But you can use TEXT in any version of MySQL. Is that suitable for what you want? Replying to myself here. Sometimes it's best to engage the brain before replying: VARCHAR has a maximum length of *255*, not 2000. If you declare a VARCHAR with a longer length, it is converted to an appropriate TEXT type in MySQL 4.1. Prior to 4.1, you get an error. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Errors on Compile
I'm attempting to install MySQL 3.23.55 from source on a Red Hat Linux 7.3 machine. I have recently built and installed GCC 3.2.2 and GNU make 3.80 successfully. Following the steps given on mysql.com for a source installation, I can go through the install up to and including ./configure --prefix=/usr/local/mysql. However, when I run make, the program terminates early, giving these messages: ./gen_lex_hash lex_hash.h ./gen_lex_hash: error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory make[4]: *** [lex_hash.h] Error 127 make[4]: Leaving directory `/src/mysql-3.23.55/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/src/mysql-3.23.55/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/src/mysql-3.23.55/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-3.23.55' make: *** [all] Error 2 I have run locate libstdc++.so.5, and it is located in /usr/local/lib. A different computer, running Red Hat 7.3, GCC 2.96, and make 3.8, compiles without a problem, but locate libstdc++.so.5 indicates that the file does not exist on that machine. Any thoughts? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql replication across platforms
Hello, I am unsuccessfully trying to start mysql replication between an Sun Solaris server and a RedHat Linux server. Can this be done ? What about the fact that the binary files for the logs that need to be transferred over upon replication startup are from different OS's. Does anyone have any experience doing this ? Thank You, floyd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
fulltext indexes
I having some strange things occur with doing fulltext searches. I'm not exactly sure how to tackle this problem, so I figured I would send it out and see if anyone has any ideas or has seen this before. Background: I have been investigating the use of MySQL to do fulltext searches on emails. The client I work for operates several professional discussion lists. Right now, list archive searching is done using WAIS, but we are looking into the possibility of using MySQL instead. Problem: I created table 'discussions' to hold emails from multiple lists... mysql show create table discussions; CREATE TABLE `discussions` ( `ID` int(11) NOT NULL auto_increment, `list` varchar(25) default NULL, `sender` varchar(200) default NULL, `subject` varchar(255) default NULL, `body` text, `send_date` datetime default NULL, PRIMARY KEY (`ID`), FULLTEXT KEY `subbody_idx` (`subject`,`body`), FULLTEXT KEY `body_idx` (`body`), KEY `list_idx` (`list`(10)) ) TYPE=MyISAM I did some tests of fulltext searches against the subject and body columns. I used a search phrase that was SURE to return results. Everything seemed to go fairly well except that it went a little slow when trying to do this fulltext searches for two lists rather than just one list. I always got results, but I wanted to try to increase the speed of the searches. You have to understand that I did not particularly care about the results (just that I got some). I was more concerned with speed because the main reason the WAIS solution has hung around for so long is because it searches very quickly. So in the interest of a possible speed increase I decided to try putting emails into a separate table for each discussion list. Each table for each list was given the same structure... mysql show create table listA; CREATE TABLE `listA` ( `ID` int(11) NOT NULL auto_increment, `sender` varchar(200) default NULL, `subject` varchar(255) default NULL, `body` text, `send_date` datetime default NULL, PRIMARY KEY (`ID`), FULLTEXT KEY `subbody_idx` (`subject`,`body`), FULLTEXT KEY `body_idx` (`body`), KEY `sender` (`sender`(10)) ) TYPE=MyISAM Data was then placed in each of the tables with no errors or warnings reported. Now here's where the problem occurs. Fulltext searches do not produce any results when using the exact same search phrase on most of the new tables. These tables hold emails for lists that returned results from the larger 'discussions' table. So, I can't quite understand what is occurring. I have tried repairing the tables, dropping and recreating indexes, different methods of inserting data into the tables. All to no avail. The fact that one of the tables is returning results would make me think there is something wrong with the content possibly. But the fact that the tables that don't return results now contain the same content that was in the aggregated discussions table and did return results for those same lists (searching within lists was done using list like 'listA' for the discussions table) would seem to suggest that there is nothing wrong with the content. I have encountered the same problem on three installations of MySQL. - Ver 8.23 Distrib 3.23.49, for sun-solaris2.6 on sparc - Ver 8.23 Distrib 3.23.54, for intel-linux on i686 - Ver 8.39 Distrib 4.0.9-gamma, for intel-linux on i686 Throughout my tests and attempts, I have received no errors or warnings. So, I am really at a loss. I've looked in the manual but haven't seen anything. Any ideas, hints, or solutions would be greatly appreciated. Thanks, Duncan --- Duncan Salada Titan Systems Corporation 301-925-3222 x375 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Segmentation Fault Closing Database
I am receiving a Segmentation Fault error when closing the connection to a
database. Using MySQL 4.0.10-0 on Redhat linux 7.3
static void closeDatabase( DBMS_STATE_INFO *dbmsInfo )
{
mysql_close( dbmsInfo-connection );
dbmsInfo-connection = NULL;
}
Thank you,
rcs
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql replication across platforms
What versions of MySQL are on each machine ? Jerry - Original Message - From: Floyd Wellershaus [EMAIL PROTECTED] To: Mysqllist (E-mail) [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 1:55 PM Subject: mysql replication across platforms Hello, I am unsuccessfully trying to start mysql replication between an Sun Solaris server and a RedHat Linux server. Can this be done ? What about the fact that the binary files for the logs that need to be transferred over upon replication startup are from different OS's. Does anyone have any experience doing this ? Thank You, floyd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FULLTEXT index on two tables and many columns
Maybe you should rethink your whole database structure. In your simplified example, you really only have two pieces of data, the text and a qualifier (a, b, c, d, e, ...). So instead of separating your text into different columns, keep all your text in one column and add another column that acts as the qualifier. You could even merge your tables since they would both end up having the exact same structure. You can then do self joins instead of your cross table joins. Finally, this structure allows you to create unlimited qualifiers without having to modified the structure of your tables or indexes. Of course, this is all based on your simplified example. On Thursday, February 27, 2003, at 02:52 AM, Grzegorz Paszka wrote: Hi. I know that for fulltext index are some limitations as: All parameters to the MATCH() function must be columns from the same table that is part of the same FULLTEXT index, unless the MATCH() is IN BOOLEAN MODE. But A boolean full-text search can also work even without a FULLTEXT index, although it would be SLOW. I've such situation: create table abc ( a text, b text, c text ); create table de ( d text, e text ); Size of database files is greater than amount of RAM. Twice. I want execute query with fulltext search through columns a and b, c and d, a and b and c and d and e . So on. There are 31 combinations. First problem: creating fulltext index on columns from two tables. Second problem: IMHO 31 fulltext indexes is too much. Even I merge this two tables. I still must create 31 indexes... My suggestion is: In cases when there is need to perform search on many columns than I should create 5 indexes on a,b,c,d and e. These should to be enough. Maybe search won't be so fast as with 31 indexes but should be faster than search without 31 indexes. Regards. -- Grzegorz mysql,sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fulltext indexes
Hello Duncan, Thursday, February 27, 2003, 4:00:31 PM, you wrote: SD I having some strange things occur with doing fulltext searches. I'm not SD exactly sure how to tackle this problem, so I figured I would send it out SD and see if anyone has any ideas or has seen this before. SD Background: SD I have been investigating the use of MySQL to do fulltext searches on SD emails. The client I work for operates several professional discussion SD lists. Right now, list archive searching is done using WAIS, but we are SD looking into the possibility of using MySQL instead. SD Problem: SD I created table 'discussions' to hold emails from multiple lists... SD mysql show create table discussions; SD CREATE TABLE `discussions` ( SD `ID` int(11) NOT NULL auto_increment, SD `list` varchar(25) default NULL, SD `sender` varchar(200) default NULL, SD `subject` varchar(255) default NULL, SD `body` text, SD `send_date` datetime default NULL, SD PRIMARY KEY (`ID`), SD FULLTEXT KEY `subbody_idx` (`subject`,`body`), SD FULLTEXT KEY `body_idx` (`body`), SD KEY `list_idx` (`list`(10)) SD ) TYPE=MyISAM SD I did some tests of fulltext searches against the subject and body columns. SD I used a search phrase that was SURE to return results. Everything seemed SD to go fairly well except that it went a little slow when trying to do this SD fulltext searches for two lists rather than just one list. I always got SD results, but I wanted to try to increase the speed of the searches. You SD have to understand that I did not particularly care about the results (just SD that I got some). I was more concerned with speed because the main reason SD the WAIS solution has hung around for so long is because it searches very SD quickly. SD So in the interest of a possible speed increase I decided to try putting SD emails into a separate table for each discussion list. Each table for each SD list was given the same structure... SD mysql show create table listA; SD CREATE TABLE `listA` ( SD `ID` int(11) NOT NULL auto_increment, SD `sender` varchar(200) default NULL, SD `subject` varchar(255) default NULL, SD `body` text, SD `send_date` datetime default NULL, SD PRIMARY KEY (`ID`), SD FULLTEXT KEY `subbody_idx` (`subject`,`body`), SD FULLTEXT KEY `body_idx` (`body`), SD KEY `sender` (`sender`(10)) SD ) TYPE=MyISAM SD Data was then placed in each of the tables with no errors or warnings SD reported. SD Now here's where the problem occurs. Fulltext searches do not produce any SD results when using the exact same search phrase on most of the new tables. SD These tables hold emails for lists that returned results from the larger SD 'discussions' table. So, I can't quite understand what is occurring. I SD have tried repairing the tables, dropping and recreating indexes, different SD methods of inserting data into the tables. All to no avail. The fact that SD one of the tables is returning results would make me think there is SD something wrong with the content possibly. But the fact that the tables SD that don't return results now contain the same content that was in the SD aggregated discussions table and did return results for those same lists SD (searching within lists was done using list like 'listA' for the SD discussions table) would seem to suggest that there is nothing wrong with SD the content. SD I have encountered the same problem on three installations of MySQL. SD - Ver 8.23 Distrib 3.23.49, for sun-solaris2.6 on sparc SD - Ver 8.23 Distrib 3.23.54, for intel-linux on i686 SD - Ver 8.39 Distrib 4.0.9-gamma, for intel-linux on i686 SD Throughout my tests and attempts, I have received no errors or warnings. SD So, I am really at a loss. SD I've looked in the manual but haven't seen anything. Any ideas, hints, or SD solutions would be greatly appreciated. SD Thanks, SD Duncan SD --- SD Duncan Salada SD Titan Systems Corporation SD 301-925-3222 x375 SD - SD Before posting, please check: SDhttp://www.mysql.com/manual.php (the manual) SDhttp://lists.mysql.com/ (the list archive) SD To request this thread, e-mail [EMAIL PROTECTED] SD To unsubscribe, e-mail [EMAIL PROTECTED] SD Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Maybe this could help? Try changing the name of the index on the second table you 've created -- Best regards, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database Market Information
Hi, I'm a belgian student in engineering and I'm now in my last year. I have a course about strategy where we have to analyse an industry and a particular company inside that industry. I think MySQL would be a good choice because I am very interested in free and open source software and it is a great exemple of success in that field. But it seems hard to find enough documentation about the database industry in general. Could you perhaps direct me in my search for information ? I already saw the press section of the MySQL website but I fear it won't be enough. Thank you very much. Pierre-Yves Dyon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slow FULLTEXT searches
I'm having a problem with FULLTEXT searches going much more slowly
than I expect, and need. It seems that this is perfectly straightforward
so I can't see why it's taking so long; other people on this list have
been reporting almost instantaneous results from FULLTEXT searches.
I'm using MySQL 4.0.10 on FreeBSD 4.7, on a 1.4 GHz PIII with 1G RAM.
It's a lightly loaded server most of the time.
The table in question is:
mysql show create table q\G
*** 1. row ***
Table: q
Create Table: CREATE TABLE `q` (
`id` int(10) unsigned NOT NULL default '0',
`cit_id` int(10) unsigned NOT NULL default '0',
`qt` text,
`note` text,
PRIMARY KEY (`id`),
KEY `cit_id` (`cit_id`),
FULLTEXT KEY `qt` (`qt`)
) TYPE=MyISAM
1 row in set (0.00 sec)
There are about 2.3M rows in this table, and it takes up about 400M.
I did shorten the ft_min_word_length to 2, since I need to search on
short words.
Here's a sample:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt) AGAINST ('computer');
+--+
| COUNT(*) |
+--+
|11892 |
+--+
1 row in set (16.43 sec)
Boolean searches are also slow:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 44 |
+--+
1 row in set (1.71 sec)
I don't get anything useful from EXPLAINs for searches like these:
mysql EXPLAIN SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE)\G
*** 1. row ***
table: q
type: fulltext
possible_keys: qt
key: qt
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.00 sec)
While a 1.7-second search may not be the end of the world, a 16-second
search is getting closer to it, and this is just the simplest case. In
practice, this would be an element of a larger search that's joining in
a number of other tables, and with a number of concurrent users. Is there
anything I can do to speed things up, or any explanation of why this is
so slow?
Thanks very much.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow query, indexes not used
You may not have enough rows to make it worthwhile to use an index.
Sebastian Bergmann wrote:
For this query
mysql EXPLAIN
- SELECT COUNT(accesslog.document_id) AS item_count,
- data_table.stringAS item
-
-FROM pot_accesslog accesslog,
- pot_visitors visitors,
- pot_documents data_table
-
- WHERE accesslog.accesslog_id = visitors.accesslog_id
- AND accesslog.client_id= 1
- AND accesslog.document_id = data_table.data_id
- GROUP BY accesslog.document_id,
-data_table.string
- ORDER BY item_count DESC;
+++--+-+-+---+--+--+
| table | type | possible_keys| key | key_len |
ref | rows | Extra|
+++--+-+-+---+--+--+
| accesslog | ALL| accesslog_id,client_time,document_id | NULL|NULL |
NULL |6 | Using where; Using temporary; Using filesort |
| visitors | index | PRIMARY | PRIMARY | 4 |
NULL |3 | Using where; Using index |
| data_table | eq_ref | PRIMARY | PRIMARY | 4 |
accesslog.document_id |1 | |
+++--+-+-+---+--+--+
3 rows in set (0.01 sec)
none of the possible indexes of the pot_accesslog table
CREATE TABLE pot_accesslog (
accesslog_id int(11) NOT NULL,
client_id int(10) unsignedNOT NULL,
timestamp int(10) unsignedNOT NULL,
document_id int(11) NOT NULL,
exit_target_idint(11) DEFAULT '0' NOT NULL,
entry_documentenum('0','1') NOT NULL,
KEY accesslog_id (accesslog_id),
KEY client_time (client_id, timestamp),
KEY document_id (document_id)
) DELAY_KEY_WRITE=1;
is used.
Any help on improving the performance of this would be appreciated,
Sebastian
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: rowid
MySQL supports _rowid. _rowid is defined as a synonym for the primary key if the primary key consists of one column and is an integer. Hope this helps, John Griffin -Original Message- From: geeta varu [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:54 PM To: [EMAIL PROTECTED] Subject: rowid i would like to use rowid in my query does mySQL support this ..if s how do i give in query please help... __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: fulltext indexes
snip Maybe this could help? Try changing the name of the index on the second table you 've created /snip Thanks for the idea, but I think I've discovered the problem. Perhaps I chose my search phrase a little TOO well. It seems the problem occurs because of the 50% restriction for fulltext searching. From the manual... The search for the word MySQL produces no results in the above example, because that word is present in more than half the rows. As such, it is effectively treated as a stopword (that is, a word with zero semantic value). I have tried the fulltext searching on the offending tables again, but have used IN BOOLEAN MODE (which removes the 50% restriction) in the 4.0.9 server. Doing this returns results as expected. Duncan --- Duncan Salada Titan Systems Corporation 301-925-3222 x375 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What command to restore MySQL DataBase ?
Dear sir, I used mysql-4.0.10-gamma on RedHat-8.0. I used my.cnf from support-files/my-large.cnf. When I backup all DataBases I used command below. mysqldump --all-databases all_databases.sql I want to know what command to restore Database ? thank you very much. regards, Somsak. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Didn't receive answer to message below. Could someone please take a look? TIA. - Sheryl - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Performance, and Availibility Questions
These are very subjective questions and I know there is no hard answer, I'm just seeking opinions from the community to get an idea to see if these are worth testing. 1. Dedicated DB server vs clustered DB/WWW servers. setup a: 2 servers running both WWW and DB. Each server queries it's local database which is replicated from a master. setup b: 1 DB server and 1 WWW server. The DB server only serves remote queries for the WWW server. The biggest variable is the CPU cycles that the WWW application uses. Lets say it's a PHP application that uses a lot of caching, (php-accel, pear_db) as a rough idea. Which to you think will out preform the other? 2. Availability I have a large table with fulltext indices and it takes near 8 hours to repair it if it gets corrupted. And I need to be able to repair it while still serving in a read only mode. If I go with the one dedicated DB server I need to find a way to repair tables while still serving. With the distributed schema I can take down the master DB/WWW server and repair it while the others are serving, then when it's repaired I can replicate the DB to the mirrors if they were corrupt as well. The only limiting function is that the databases were 'read only' during the repair, but that's fine in my environment, the data only changes at specified times. With only 1 DB server I was trying to find a way to accomplish this. And I wonder if running a second instance of mysql on the same server and replicating the data to that instance would work. This would allow me to serve off the backup (read only) instance while I take down the primary instance and repair the data, most likely by NFS mounting the data files on another machine and using it's CPU cycles to repair the data. I seek your opinion of that solution. You may ask why on earth would I want to go from multiple db servers to 1 db server, the reason is that I'm evaluating managed hosting and there are tons of benefits, the one of the few drawbacks is that hardware is expensive so I need to utilize each machine to it's fullest capacity. I anxiously await your responses. -- Jeff Bearer, RHCE Webmaster, PittsburghLIVE.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What command to restore MySQL DataBase ?
Very easy... just copy paste the content of your file (from mysqldump) into your mysql-client command line. That's all. --- Somsak RAKTHAI [EMAIL PROTECTED] wrote: Dear sir, I used mysql-4.0.10-gamma on RedHat-8.0. I used my.cnf from support-files/my-large.cnf. When I backup all DataBases I used command below. mysqldump --all-databases all_databases.sql I want to know what command to restore Database ? thank you very much. regards, Somsak. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What command to restore MySQL DataBase ?
At 22:04 +0700 2/27/03, Somsak RAKTHAI wrote: Dear sir, I used mysql-4.0.10-gamma on RedHat-8.0. I used my.cnf from support-files/my-large.cnf. When I backup all DataBases I used command below. mysqldump --all-databases all_databases.sql I want to know what command to restore Database ? If you look at the dump file, you'll see that it contains SQL statements. Just feed the file to mysql to process it: mysql all_databases.sql thank you very much. regards, Somsak. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow FULLTEXT searches
Jesse,
this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
many many times.
SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
should be fast. Make sure to use a key_buffer_size as big as you can
afford, possibly keeping the whole index in memory.
Thomas Spahni
(sql, query)
On Thu, 27 Feb 2003, Jesse Sheidlower wrote:
I'm having a problem with FULLTEXT searches going much more slowly
than I expect, and need. It seems that this is perfectly straightforward
so I can't see why it's taking so long; other people on this list have
been reporting almost instantaneous results from FULLTEXT searches.
I'm using MySQL 4.0.10 on FreeBSD 4.7, on a 1.4 GHz PIII with 1G RAM.
It's a lightly loaded server most of the time.
The table in question is:
mysql show create table q\G
*** 1. row ***
Table: q
Create Table: CREATE TABLE `q` (
`id` int(10) unsigned NOT NULL default '0',
`cit_id` int(10) unsigned NOT NULL default '0',
`qt` text,
`note` text,
PRIMARY KEY (`id`),
KEY `cit_id` (`cit_id`),
FULLTEXT KEY `qt` (`qt`)
) TYPE=MyISAM
1 row in set (0.00 sec)
There are about 2.3M rows in this table, and it takes up about 400M.
I did shorten the ft_min_word_length to 2, since I need to search on
short words.
Here's a sample:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt) AGAINST ('computer');
+--+
| COUNT(*) |
+--+
|11892 |
+--+
1 row in set (16.43 sec)
Boolean searches are also slow:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 44 |
+--+
1 row in set (1.71 sec)
I don't get anything useful from EXPLAINs for searches like these:
mysql EXPLAIN SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE)\G
*** 1. row ***
table: q
type: fulltext
possible_keys: qt
key: qt
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.00 sec)
While a 1.7-second search may not be the end of the world, a 16-second
search is getting closer to it, and this is just the simplest case. In
practice, this would be an element of a larger search that's joining in
a number of other tables, and with a number of concurrent users. Is there
anything I can do to speed things up, or any explanation of why this is
so slow?
Thanks very much.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow query, indexes not used
gerald_clark wrote: You may not have enough rows to make it worthwhile to use an index. That may be true for the example query I posted that uses the Unit Test data, which is not much. A user of phpOpenTracker reports the same EXPLAIN result for the query performed on tabled with 1.000.000+ rows. -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
binary install of mysql_install_db hangs
Description: I've tried several times to get mysql to install on my linux box, to no avail. Everytime, the mysql_install_db script hangs when it tries to run mysqld. Running mysqld by hand hangs the same way as well. I've seen over a dozen reports of this on the mysql mailing list in the last year and no real answers have ever been given. - Output from mysql_install_db [root]# scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables - Output from ps [root]# ps -elf | grep my 100 S root 19592 19173 0 69 0 - 548 wait4 10:26 pts/1 00:00:00 /bin/sh scripts/mysql_install_db 100 S root 19597 19592 0 69 0 - 5132 rt_sig 10:26 pts/1 00:00:00 ./bin/mysqld --bootstrap --skip-grant-tables --basedir=. --datadir=./data --skip 040 S root 19598 19597 0 69 0 - 5132 do_pol 10:26 pts/1 00:00:00 ./bin/mysqld --bootstrap --skip-grant-tables --basedir=. --datadir=./data --skip 040 S root 19599 19598 0 69 0 - 5132 rt_sig 10:26 pts/1 00:00:00 ./bin/mysqld --bootstrap --skip-grant-tables --basedir=. --datadir=./data --skip 100 S root 19603 19316 0 70 0 - 406 pipe_w 10:27 pts/2 00:00:00 grep my - Output from last lines of strace 19255 clone(child_stack=0x83a8c38, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND) = 19256 19255 write(4, \v\0\0\0\5\0\0\0\1\0\0\0\0\0\0\2\0\0\0\0/\0\0\0\20\27\0 ..., 148) = 148 19255 rt_sigprocmask(SIG_SETMASK, NULL, [HUP QUIT PIPE TERM TSTP RT_0], 8) = 0 19255 write(4, [EMAIL PROTECTED] ..., 148) = 148 19255 rt_sigprocmask(SIG_SETMASK, NULL, [HUP QUIT PIPE TERM TSTP RT_0], 8) = 0 19255 rt_sigsuspend([HUP QUIT PIPE TERM TSTP] - End A quick google search on rt_sigsuspend indicates that the pthreads library may be at fault. How-To-Repeat: scripts/mysql_install_db Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: none Synopsis: mysql_install_db hangs Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.11-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux island.dyksterhouse.org 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 19 Feb 12 2002 /lib/libc.so.5 - /lib/libc.so.5.3.12 -rwx--1 root root 699832 Feb 12 2002 /lib/libc.so.5.3.12 lrwxrwxrwx1 root root 13 Jul 26 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange bug
Version: MySQL 4.0.11-gamma-nt OS: Windows NT4 Server I have a test table like this: CREATE TABLE test ( created date NOT NULL default '-00-00', duration smallint(5) unsigned NOT NULL default '0' ) TYPE=MyISAM; INSERT INTO test VALUES(2003-01-01, 365); INSERT INTO test VALUES(2003-01-01, 0); I'd like to compute created+duration-CURDATE(), ie. the remaining days of the item (result 0 means item expired): select to_days(created) + duration as expiration, to_days(curdate()) as current, to_days(created) + duration - to_days(curdate()) as remaining from test; With the following result: expiration, current, remaining Notes: 731946, 731638, 308 Ok, 308 days remain until item expires 731581, 731638, 0 Mh, I'd expect -57, ie. item expired, what's your guess!? Seems like A+B works, A+B-C doesn't... Cheers, .SMK. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Sheryl, Sorry I don't time to fully address your question but I will try to give you some feedback. The DISTINCT functionality will give you exact that, every distinct instance of the columns you SELECTED. SELECT DISTINCT a.Author FROM author a Will give you each and every author but only once. Since your Royalties table has pointers to both Authors and Programs, if you run DISTINCT *, you will get every single instance of every combination. Before you go any further, I would attempt to answer one question. Are you trying to determine which Author had the highest royalties or are you trying to determine which Author has the highest royalties in each program(I am assuming an author can work on multiple programs)? This will make a difference in how your structure your query. Also, I don't think you want SELECT DISTINCT * I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES. etc. is better Also remember, a.AUTHOR will give you different results than r.AUTHOR.. Hope this helps. At 10:13 AM 2/27/03 -0500, Sheryl Canter wrote: Didn't receive answer to message below. Could someone please take a look? TIA. - Sheryl - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow FULLTEXT searches
On Thu, Feb 27, 2003 at 04:45:37PM +0100, Thomas Spahni wrote:
Jesse,
this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
many many times.
SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
should be fast. Make sure to use a key_buffer_size as big as you can
afford, possibly keeping the whole index in memory.
This may be the answer for why it's _that_ slow for that one
query, but in general I'm afraid that's not it. I executed
your above query on my development server (to ensure the cache
was cleared), which is a somewhat slower machine, and it took
2.61 seconds--better than 16, but still problematic.
And when I changed this:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 44 |
+--+
1 row in set (1.71 sec)
to this:
mysql SELECT * FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
, it took 4.76 seconds--again, on a slower server, but this is
returning only 44 results.
It's certainly possible, and perhaps likely, that users will
need to do fulltext searches on extremely common words--more
common than computer in the above example--though limited by
requirements in other tables not shown here, and it would be
rather problematic if these searches are going to take over a
second each.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: The Security of MySQL
Are you using Windows? If so, this root/mysql user talk will be meaningless. You can still make the directory secure and only touchable by the user that mysql is running as. Is this what you need? -Original Message- From: Dyego Souza do Carmo [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 12:13 PM To: [EMAIL PROTECTED] Subject: The Security of MySQL I'm using MySQL-Pro+InnoDB 4.0.11 and i have a BIG problem... My users is hacking the database because the MySQL system tables are stored in .MYD format and to hack database is simple , only rename the database and copy the blank database... restart MySQL and the permissions is FULL FOR ALL USERS... Exists in MySQL routines to ENCRYPT tables ? or the data inside tables ? the functions like ENCODE and DECODE print a password in log file ( IN CLEAR TEXT) and this is terrible for me ! Exists the PASSWORD on CREATE TABLE STATEMENT but i'm using and is same without the clause. Please MySQL-Team and users... The security of MySQL is too simple ? only rename and the database is opened for world ? please help in advance ;) Tanks Tanks very much sql,query,innodb,mysql -- --- ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento -- --- E S C R I B A I N F O R M A T I C A -- --- The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 -- --- Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with registers import
People I have a txt file with 250.000 lines but, I'm import only 32767 lines. The database don't say anything (errors) I'm using MySQL 3.23.53 in the Linux Slackware 8 system Anyone can help me to solve this? Regards Paulino Michelazzo [EMAIL PROTECTED] ICQ: 2911392 NASA = Need Another Seven Astronauts - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
I'm not sure if this is HTML question or PHP...
I've had great success with my first PHP/mySql project. It's a catalog to showcase my embroidery designs. My embroidery database is working, the php code I embedded in my FrontPage2002 pages is working great, I can click on my admin button and upload info into the database. Because of the pictures that come up with each line item of design detail ...I'm concerned that when the member searches for the designs, if it brings up too many on one screen that the users will be able to take a nap while the screen loads. Is there a way for me to tell the web page to only load, lets say 6 on a page and have a button to go to the next 6 until all the designs that fit the criteria are gone through? Thanks to those out there who have responded to my previous issues ... it's nice to know that there are members of this group willing to take time to answer questions from such greenies out there like me with respect and kindness. You've given me the courage to continue and I'm almost done with my first adventure into this wonderful new world for me. Renee :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Hi Frank, I had two main questions: (1) What is the rule that SELECT DISTINCT uses when deciding which of multiple instances to return? Does it return the first one? (2) Is there a way to write a SELECT statement to return the record for the author with the highest royalty percent (a different field in the table)? That is the one that I want. Details on the database structure are below. Thanks. - Sheryl - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:10 AM Subject: Re: SELECT DISTINCT question Sheryl, Sorry I don't time to fully address your question but I will try to give you some feedback. The DISTINCT functionality will give you exact that, every distinct instance of the columns you SELECTED. SELECT DISTINCT a.Author FROM author a Will give you each and every author but only once. Since your Royalties table has pointers to both Authors and Programs, if you run DISTINCT *, you will get every single instance of every combination. Before you go any further, I would attempt to answer one question. Are you trying to determine which Author had the highest royalties or are you trying to determine which Author has the highest royalties in each program(I am assuming an author can work on multiple programs)? This will make a difference in how your structure your query. Also, I don't think you want SELECT DISTINCT * I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES. etc. is better Also remember, a.AUTHOR will give you different results than r.AUTHOR.. Hope this helps. At 10:13 AM 2/27/03 -0500, Sheryl Canter wrote: Didn't receive answer to message below. Could someone please take a look? TIA. - Sheryl - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with registers import
Have you by any chance defined an unique index on a SMALLINT field ? In this case have a look at: http://www.mysql.com/doc/en/Numeric_types.html Regards Joseph Bueno Paulino Michelazzo wrote: People I have a txt file with 250.000 lines but, I'm import only 32767 lines. The database don't say anything (errors) I'm using MySQL 3.23.53 in the Linux Slackware 8 system Anyone can help me to solve this? Regards Paulino Michelazzo [EMAIL PROTECTED] ICQ: 2911392 NASA = Need Another Seven Astronauts - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Universal joins, universal transmission shaft
mysql Dear Sir/Madam: We would like to introduce the our new product for you, If you do not want to receive such kind of information, I'm sorry, Please delete it, thank you! Yueqing heavy transmission shaft factory,specially produce universal transmission shaft We products: WSD Type,WS type,wss type,WHL type,WSL type,LQA type,SWP type,SWC type cross universal coupling. GXTC type,GXTCA type,GXL type,GXZL type,GXGL type Curved-pin coupling. G2CL type,G2CLZ type,WGT type Curved-tooth coupling. ML,MLZ,MLS Type elasticity coupling If your interest,please contact us. [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.yue-zhong.com TEL0086-577-62312811 FAX0086-577-62312909 Yours faithfully --- http://www.shangpala.com/unsub/unsubscribe.asp?id=5532language=gb2312 ! ; , http://tkhome.ohgo.com/ [EMAIL PROTECTED] QQ36768830 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow FULLTEXT searches
Jesse
But then something else must be terribly wrong. As long as you are pulling
ten thousands of hits from the server, it may be slow. But when you reduce
the number of results with (let's say) 'LIMIT 100' I expect typical serch
times of 0.02 sec. That's what I see on a comparable machine holding 200
MB of text plus index.
Can you check for the response time on a not so common single word?
Thomas Spahni
(sql, query)
On Thu, 27 Feb 2003, Jesse Sheidlower wrote:
On Thu, Feb 27, 2003 at 04:45:37PM +0100, Thomas Spahni wrote:
Jesse,
this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
many many times.
SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
should be fast. Make sure to use a key_buffer_size as big as you can
afford, possibly keeping the whole index in memory.
This may be the answer for why it's _that_ slow for that one
query, but in general I'm afraid that's not it. I executed
your above query on my development server (to ensure the cache
was cleared), which is a somewhat slower machine, and it took
2.61 seconds--better than 16, but still problematic.
And when I changed this:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 44 |
+--+
1 row in set (1.71 sec)
to this:
mysql SELECT * FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
, it took 4.76 seconds--again, on a slower server, but this is
returning only 44 results.
It's certainly possible, and perhaps likely, that users will
need to do fulltext searches on extremely common words--more
common than computer in the above example--though limited by
requirements in other tables not shown here, and it would be
rather problematic if these searches are going to take over a
second each.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The MySQL and InnoDB FKs
Dyego, - Original Message - From: Dyego Souza do Carmo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:42 PM Subject: The MySQL and InnoDB FK´s Hi, I would like to know if is there a way to delete constraints without dropping and recreating a table. If there isn't, I would like to know when do you plan to implement this important feature. I promised to someone it would come in summer 2003. I agree it is important, and is alaos rather easy to implement. Thanks in advance, sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento Best regards, Heikki Innobase Oy - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with registers import
Paulino, do you have any auto_increment index of type SMALLINT ??? .. and what's in the error.log ? Thomas Spahni (sql, query) On Thu, 27 Feb 2003, Paulino Michelazzo wrote: People I have a txt file with 250.000 lines but, I'm import only 32767 lines. The database don't say anything (errors) I'm using MySQL 3.23.53 in the Linux Slackware 8 system Anyone can help me to solve this? Regards Paulino Michelazzo [EMAIL PROTECTED] ICQ: 2911392 NASA = Need Another Seven Astronauts - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using MySQL to store email
We have used procmail and PHP combination to achieve this for our department mail management and associated workflow. Sendmail invokes procmail which delivers the mail to the normal inbox and invokes a PHP script thru LYNX The PHP script opens an IMAP connection to the inbox, reads the mail and puts in a text data col in the database. This is working successfully for all our 5 departments and over 500-1000 mails a day. Except for big attachments where we have respective limitations (to some extent configurable - maxpacketsize options etc) in PHP as well as MYSQL. vikash -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 12:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Using MySQL to store email First, which MYSQL, the program or the company. Assuming you mean a mysql database I think you will need to run it through a program which will parse it and load it into the database tables. Does anyone know where I can read more on how to redirect incoming email (via Sendmail) into MySQL for a given email address? I understand that I can use [EMAIL PROTECTED] | /path/mysql ... in the /etc/aliases file to do this, but I would like to see if anyone has had success with this. I also would like to know if this is not as simple as I may think. Thanks for any thoughts on this, Jeff William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Text fields Full text search
On Thursday 27 February 2003 13:45, Hugo Wetterberg wrote: Does anyone know how to use text fields (BLOB) in the full text search? You can create a fulltext index only on the VARCHAR and TEXT columns, but not on the BLOB. How to use fulltext index with TEXT columns look at: http://www.mysql.com/doc/en/Fulltext_Search.html -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: What command to restore MySQL DataBase ?
On Thursday 27 February 2003 17:04, Somsak RAKTHAI wrote: I used mysql-4.0.10-gamma on RedHat-8.0. I used my.cnf from support-files/my-large.cnf. When I backup all DataBases I used command below. mysqldump --all-databases all_databases.sql I want to know what command to restore Database ? shell mysql all_databases.sql -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: rowid
On Thursday 27 February 2003 06:54, geeta varu wrote: i would like to use rowid in my query does mySQL support this ..if s how do i give in query please help... From the MySQL manual: If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, you can also refer to it as _rowid (new in Version 3.23.11). -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: MySQL 4.0 and UNICODE
On Wednesday 26 February 2003 18:49, Juan wrote: Does the last binary version available of MySQL (4.0xx) support UNICODE? Unicode will be available since 4.1. There is no binaries for 4.1 yet. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: displaying constraints
On Thursday 27 February 2003 11:05, gregory lefebvre wrote: My tables are MyIsam type. I put some contraints for compatibility. When I enter a desc table ; I cannot see which contraints belong to this table. What is the SQL command line to do this ? MyISAM doesn't support foreign key constraints, on InnoDB tables you can see them with SHOW TABLE STATUS command. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: trouble setting key_buffer_size on a debian slave
On Thursday 27 February 2003 05:41, Liz Derr wrote: I'm using MySQL 3.23.49-log on Debian Linux 2.4.18-bf2.4. I am using replication over ssh tunnels, and one of the slaves is apparently in need of performance tuning. After reviewing the status and variable settings (detailed below) and the MySQL online manual, I decided that I needed to up the key_buffer_size. Unfortunately, I've not been successful in doing this. I tried setting it at the command line: mysql set key_buffer_size=32M; ERROR 1064: You have an error in your SQL syntax near 'key_buffer_size=32M' at line 1 You can't set up this variable via command-line client. I tried setting it in /etc/mysql/my.cnf (detailed below), restarted mysqld, and did a show variables, and it was still 16M. I thought maybe this is variable that is dependent upon the master db. So I changed the master /etc/mysql/my.cnf to set the key_buffer_size to 32M, and restarted it. I did a show variables on the master, and indeed it now has a key_buffer_size of 32M. I went back to the slave and restarted it, but it *STILL* has a key_buffer_size of 16M (and it still has the 32M setting in the my.cnf). So, does anyone have any suggestions? Of course, this is predicated on my possibly naive idea that changing the key_buffer_size will help. If anyone else has suggestions on other tuning, those would be much appreciated, too. Has anyone done much performance tuning on replicated databases? I imagine that there might be some differences between that and non-replicated db tuning. my.cnf must be located in the /etc dir instead of /etc/mysql. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with registers import
Hey Joseph and people Thank you very much for the advice. A stupid little mistake here :) Yes, I have a smallint key field :) Thanks a lot and regards from Brazil ** Em 27/2/2003 Joseph Bueno escreveu: Have you by any chance defined an unique index on a SMALLINT field ? In this case have a look at: http://www.mysql.com/doc/en/Numeric_types.html Regards Joseph Bueno Paulino Michelazzo wrote: People I have a txt file with 250.000 lines but, I'm import only 32767 lines. The database don't say anything (errors) I'm using MySQL 3.23.53 in the Linux Slackware 8 system Anyone can help me to solve this? Regards Paulino Michelazzo [EMAIL PROTECTED] ICQ: 2911392 NASA = Need Another Seven Astronauts - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Cant start MySQL when used my-huge.cnf.
Dear sir, I used mysql-4.0.10-gamma on RedHat-8.0. My system have memory 1G therefore I used my.cnf from support-files/my-huge.cnf. But I cant start MySQL. It has error messages below. 030227 10:15:21 mysqld started /usr/local/mysql/libexec/mysqld: ERROR: unknown option '--log-bin # required for replication' 030227 10:15:21 mysqld ended In my-huge.cnf have this line below that I think cause error. # Replication Master Server (default) log-bin # required for replication server-id = 1 # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted How to solve this problem ? thank you very much. regards, Somsak. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
On 27-Feb-2003 Sheryl Canter wrote: snip SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? You'll probably need an 'ORDER BY' somewhere in there. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installing/Unistalling MySQL-3.23.55-1
Hi, I downloaded the rpm file for this and installed using rpm -i MySQL-3.23.55-1.rpm. It went fine and said I should run mysqladmin to set root password but all I got was 'command not found'. I then tried to uninstall it, without much luck: [EMAIL PROTECTED] Downloads]# rpm -e MySQL-3.23.55-1 error reading information on service mysql: No such file or directory error: %preun(MySQL-3.23.55-1) scriptlet failed, exit status 1 Can anyone help? Thanx, NC ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I'm not sure if this is HTML question or PHP...
The technique you are looking for is usually referred to as recordset paging. A Google search for PHP Recordset Paging brought back a slew of hits. Find one that appeals to you/explains what it does so you can use it. HTH, Tore. - Original Message - From: Stitchin' [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:48 AM Subject: I'm not sure if this is HTML question or PHP... I've had great success with my first PHP/mySql project. It's a catalog to showcase my embroidery designs. My embroidery database is working, the php code I embedded in my FrontPage2002 pages is working great, I can click on my admin button and upload info into the database. Because of the pictures that come up with each line item of design detail ...I'm concerned that when the member searches for the designs, if it brings up too many on one screen that the users will be able to take a nap while the screen loads. Is there a way for me to tell the web page to only load, lets say 6 on a page and have a button to go to the next 6 until all the designs that fit the criteria are gone through? Thanks to those out there who have responded to my previous issues ... it's nice to know that there are members of this group willing to take time to answer questions from such greenies out there like me with respect and kindness. You've given me the courage to continue and I'm almost done with my first adventure into this wonderful new world for me. Renee :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
except?
I'm working on learning mySQL, and SQL in general. I have a database book from college (just last year, so it's not an old one) and was looking for a way to do a difference between two groups. In my book, it says to use the EXCEPT operation, but either I did it wrong or it doesn't exist. Here's roughly what I typed: (select Computer.CompID, Computer.Location, Computer.User from Computer where Computer.OS like Microsoft*) except all (select Software.CompID from Software where Software.Name = Excel); Basically I want to know what computers in my list don't have the program Excel. Did I do this right, or did I miss something? I did check the manual, but I didn't find anything that looked correct. Brian Ronk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cant start MySQL when used my-huge.cnf.
At 0:32 +0700 2/28/03, Somsak RAKTHAI wrote: Dear sir, I used mysql-4.0.10-gamma on RedHat-8.0. My system have memory 1G therefore I used my.cnf from support-files/my-huge.cnf. But I cant start MySQL. It has error messages below. 030227 10:15:21 mysqld started /usr/local/mysql/libexec/mysqld: ERROR: unknown option '--log-bin # required for replication' 030227 10:15:21 mysqld ended In my-huge.cnf have this line below that I think cause error. # Replication Master Server (default) log-bin # required for replication server-id = 1 # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted Delete the comments at the ends of the lines. A comment can be written beginning with #, but must appear on a line by itself or it causes a syntax error. How to solve this problem ? thank you very much. regards, Somsak. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: I'm not sure if this is HTML question or PHP...
Thank you ... I'm usually pretty good at researching stuff (comes from my prior life as a CPA trying to find IRS tax regulations) but when you don't know the right terminology, all the searching in the world won't turn up a spider if you're using amphibian instead of arachnid! Renee :) -Original Message- From: Tore Bostrup [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 1:58 PM To: [EMAIL PROTECTED] Subject: Re: I'm not sure if this is HTML question or PHP... The technique you are looking for is usually referred to as recordset paging. A Google search for PHP Recordset Paging brought back a slew of hits. Find one that appeals to you/explains what it does so you can use it. HTH, Tore. - Original Message - From: Stitchin' [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:48 AM Subject: I'm not sure if this is HTML question or PHP... I've had great success with my first PHP/mySql project. It's a catalog to showcase my embroidery designs. My embroidery database is working, the php code I embedded in my FrontPage2002 pages is working great, I can click on my admin button and upload info into the database. Because of the pictures that come up with each line item of design detail ...I'm concerned that when the member searches for the designs, if it brings up too many on one screen that the users will be able to take a nap while the screen loads. Is there a way for me to tell the web page to only load, lets say 6 on a page and have a button to go to the next 6 until all the designs that fit the criteria are gone through? Thanks to those out there who have responded to my previous issues ... it's nice to know that there are members of this group willing to take time to answer questions from such greenies out there like me with respect and kindness. You've given me the courage to continue and I'm almost done with my first adventure into this wonderful new world for me. Renee :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: trouble setting key_buffer_size on a debian slave
On Thursday 27 February 2003 05:41, Liz Derr wrote: I'm using MySQL 3.23.49-log on Debian Linux 2.4.18-bf2.4. I am using replication over ssh tunnels, and one of the slaves is apparently in need of performance tuning. After reviewing the status and variable settings (detailed below) and the MySQL online manual, I decided that I needed to up the key_buffer_size. Unfortunately, I've not been successful in doing this. I tried setting it at the command line: mysql set key_buffer_size=32M; ERROR 1064: You have an error in your SQL syntax near 'key_buffer_size=32M' at line 1 You can't set up this variable via command-line client. I tried setting it in /etc/mysql/my.cnf (detailed below), restarted mysqld, and did a show variables, and it was still 16M. I thought maybe this is variable that is dependent upon the master db. So I changed the master /etc/mysql/my.cnf to set the key_buffer_size to 32M, and restarted it. I did a show variables on the master, and indeed it now has a key_buffer_size of 32M. I went back to the slave and restarted it, but it *STILL* has a key_buffer_size of 16M (and it still has the 32M setting in the my.cnf). So, does anyone have any suggestions? Of course, this is predicated on my possibly naive idea that changing the key_buffer_size will help. If anyone else has suggestions on other tuning, those would be much appreciated, too. Has anyone done much performance tuning on replicated databases? I imagine that there might be some differences between that and non-replicated db tuning. my.cnf must be located in the /etc dir instead of /etc/mysql. Not under debian. Using a .deb they would go in /etc/mysql -- 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 William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: except?
(select Computer.CompID, Computer.Location, Computer.User from Computer where Computer.OS like Microsoft*) except all (select Software.CompID from Software where Software.Name = Excel); I think your trying to do what mySQL considers a sub-select and mySQL can't handle those yet(AFAIK). There are ways to get around doing sub-selects but I don't what they are. Please someone correct me if I'm wrong HTH, Ryan __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP mysql_server_init embedded in a DLL?
I am posting this again, in hope that I will get a response.
I tried doing the following (see VB code below. I got this code from another
site):
I get the same error message as if I was calling the DLL I created in C
which calls mysql_server_init.
Is it impossible to call this function from VB or embedded in another DLL
from VB?
Can I only use the embedded mysql library in C or C++?
Thanks
Eric
Public Declare Function mysql_server_init Lib
C:\mysql40\source\lib_release\libmysqld.dll _
(ByVal argc As Long, _
ByVal argv As Byte, _
ByVal groups As Byte) As Long
Private Sub cmdmysq_server_init_Click()
Dim argv(0) As Byte, groups(0) As Byte
Dim i As Long
argv(0) = Asc(0)
groups(0) = Asc(0)
i = mysql_server_init(0, argv, groups)
End Sub
From: Derick Smith [EMAIL PROTECTED]
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: mysql_server_init embedded in a DLL?
Date: Wed, 26 Feb 2003 14:34:28 +
Hi!
I am trying to create a DLL that will hide embedded mySQL functions for use
with VB. I am using mysqlserver.lib. I am able to compile and execute the
following code. This has to be done in C because VB is too slow :-(
If mysql_server_init function is run in the DLL from Visual Basic. I get
the following error message: ..referenced memory at 0x00. The
memory could not be read.
Without the mysql_server_init function it will run correctly. It will also
run correctly when this is compiled as a .lib file linked to a C program.
Is it possible to embed mysql_server_init in a DLL? (So the DLL can be used
in VB)
Thanks in advance for any responses.
Thanks
Eric
header1.h
#define GPAPI __stdcall
header2.h
typedef unsigned long DBS;
#define NULLDBS (DBS)0
api.c
GPAPI DBS dbNewSet (char * basedir, char * datadir, char * dbname)
{
return NewSet(basedir,datadir,dbname);
}
newset.c
extern DBS NewSet(char * basedir, char * datadir, char * dbname)
{
DBSET* pSet = CreateSet();
char*args[3];
char strArgs[3][1024];
int iRtn;
int argc = 3;
strcpy(strArgs[0],this_program);
sprintf(strArgs[1],--basedir=%s,basedir);
sprintf(strArgs[2],--datadir=%s,datadir);
args[0]= strArgs[0];
args[1]= strArgs[1];
args[2]= strArgs[2];
/* If I comment out this line of code,
the DLL will run correctly.
If mysql_server_init function is run in the DLL. I get the following
error message: ..referenced memory at 0x00. The
memory could not be read */
iRtn = mysql_server_init(3, args, server_groups);
if (pSet ==0 ) return NULLDBS;
memset(pSet, 0, sizeof(DBSET)); /* Zero the structure.*/
pSet-size = sizeof(DBSET);/* Assign structure size. */
pSet-magic = DBS_MAGIC; /* Assign magic number. */
return (DBS)(void*)pSet;
}
_
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Why Is my dbase slow
Hi Im fairly new but thought I was doing ok. I have a single table with 123 columns and currently it holds 49,000 records My ISP is running MySql v: 3.23.38 I use MySql Front to access the table The following query took 32 seconds to retrieve 941 records the primary key is the ID field and each records has a unique field, records are grouped into sets by this field. select id from global where uniqueref = '2' Im on a 1MB broadband link and this type of query was taking 1 or 2 seconds when I had 30,000 records, now it seems ot have slowed right down. Any thoughts would be appreciated. Regards John Berman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FULLTEXT index on two tables and many columns
On Thu, Feb 27, 2003 at 09:27:41AM -0500, Brent Baisley wrote: Maybe you should rethink your whole database structure. In your simplified example, you really only have two pieces of data, the text and a qualifier (a, b, c, d, e, ...). So instead of separating your text into different columns, keep all your text in one column and add another column that acts as the qualifier. You could even merge your tables since they would both end up having the exact same structure. You can then do self joins instead of your cross table joins. Finally, this structure allows you to create unlimited qualifiers without having to modified the structure of your tables or indexes. Your idea is very interesting. Of course, this is all based on your simplified example. Now I must think over if I am able to implement it in my more complex situation :) Thanks. -- Grzegorz mysql,sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: trouble setting key_buffer_size on a debian slave
William, Egor, thanks for your attention on this. William is correct, on debian the standard place for my.cnf is in /etc/mysql, and we aren't having problems not reading my.cnf (it worked perfectly well to change the param there for the master). In desperation, I figured I'd just redo the slave database, so I took a fresh snapshot of the master (now with the larger key_buffer_size), scp'ed to the slave, shutdown mysqld on the slave, untarred it into the mysql dirs, deleted master.info, confirmed my.cnf was how I wanted it (with the larger buffer specified), started up mysqld, reset the slave, and did a change master to to set the correct new logging parameters. Alas, no luck, we *still* have a 16M key buffer size! Any other suggestions on what I could try? thanks, Liz On Thursday 27 February 2003 05:41, Liz Derr wrote: I'm using MySQL 3.23.49-log on Debian Linux 2.4.18-bf2.4. I am using replication over ssh tunnels, and one of the slaves is apparently in need of performance tuning. After reviewing the status and variable settings (detailed below) and the MySQL online manual, I decided that I needed to up the key_buffer_size. Unfortunately, I've not been successful in doing this. I tried setting it at the command line: mysql set key_buffer_size=32M; ERROR 1064: You have an error in your SQL syntax near 'key_buffer_size=32M' at line 1 You can't set up this variable via command-line client. I tried setting it in /etc/mysql/my.cnf (detailed below), restarted mysqld, and did a show variables, and it was still 16M. I thought maybe this is variable that is dependent upon the master db. So I changed the master /etc/mysql/my.cnf to set the key_buffer_size to 32M, and restarted it. I did a show variables on the master, and indeed it now has a key_buffer_size of 32M. I went back to the slave and restarted it, but it *STILL* has a key_buffer_size of 16M (and it still has the 32M setting in the my.cnf). So, does anyone have any suggestions? Of course, this is predicated on my possibly naive idea that changing the key_buffer_size will help. If anyone else has suggestions on other tuning, those would be much appreciated, too. Has anyone done much performance tuning on replicated databases? I imagine that there might be some differences between that and non-replicated db tuning. my.cnf must be located in the /etc dir instead of /etc/mysql. Not under debian. Using a .deb they would go in /etc/mysql -- 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 William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem connecting to mysqld
I am having problems using MYSQL on Windows ME operating system. It installs fine with no problems, but at the point where I try to connect to mysqld i get the error of bad command or file name at the dos propmt. therefore when I try to create a database I keep getting Error-2003: Can't connect to MYSQL server on 'local host' (10061) . I have tried uninstalling MYSQL and installing it again, and checked for the files that suppose to be there and everything seems to be fine. Please help for I need this ASAP. Thanks you _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL codes
I would have thought that in a well designed 3rd normal form database there would not be much worth grtouping in the rows. Even 1st normal form tries to ensure there is nothing to group in the rows. This isn't an early April Fool thing is it? John Bonnett -Original Message- From: DiAnNe iRiS aLeRta [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 February 2003 2:07 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: MySQL codes i am to make a feature for mysql which is horizontal aggregation for our thesis. is this existing already? if not, is vertical aggregation existing? can you show me the codes for it? i need it to have a basis for developing the horizontal aggregation. horizontal aggregation is collecting values in a ROW based on some grouping criterion and displaying it in horizontal manner. vertical aggregation is collecting values in a COLUMN based on some grouping criterion. thank you so much! _ Visit Atenista.Net, Your Portal to the Atenean Community! Click here: http://www.atenista.net _ Select your own custom email address for FREE! Get [EMAIL PROTECTED] w/No Ads, 6MB, POP more! http://www.everyone.net/selectmail?campaign=tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Why Is my dbase slow
Jeremy select id from global where uniqueref = '2'. In this case uniqueref = 2 relates to uk civil birth, deaths and marriage records Please see info as requested. Field,Type,Null,Key,Default,Extra ID,int(11),,PRI,NULL,auto_increment UNIQUEREF,double,YES,,NULL, JGSGBCERTNO,varchar(255),YES,,NULL, DISTRICTREG,varchar(255),YES,,NULL, REGDISTRICT,varchar(255),YES,,NULL, COUNTY,varchar(255),YES,,NULL, NOTICEOFMARRDATE,varchar(255),YES,,NULL, dob,varchar(255),YES,,NULL, PLACEOFBIRTH,varchar(255),YES,,NULL, FATHERSNAME,varchar(255),YES,,NULL, FATHEROCCUP,varchar(255),YES,,NULL, MOTHERGNAME,varchar(255),YES,,NULL, MOTHERMNAME,varchar(255),YES,,NULL, MOTHERPSURNAME,varchar(255),YES,,NULL, CONGREGATION,varchar(255),YES,MUL,NULL, SYNAGOGUEREGISTER,varchar(255),YES,,NULL, UNDERTAKER,varchar(255),YES,,NULL, UNDERTAKERSPAGENO,varchar(255),YES,,NULL, CEMETERY,varchar(255),YES,,NULL, CEMETERYADDRESS,varchar(255),YES,,NULL, PLOTLOCATION,varchar(255),YES,,NULL, dode,varchar(255),YES,,NULL, dodh,varchar(255),YES,,NULL, DATEOFBURIAL,varchar(255),YES,,NULL, DATEOFSTONESETTING,varchar(255),YES,,NULL, OCCUPATION,varchar(255),YES,,NULL, TITLE,varchar(255),YES,,NULL, SURNAME,varchar(255),YES,MUL,NULL, MAIDENNAME,varchar(255),YES,,NULL, OTHERSURNAMES,varchar(255),YES,,NULL, GIVENNAMES,varchar(255),YES,,NULL, HEBREWNAMES,varchar(255),YES,,NULL, GENDER,varchar(255),YES,,NULL, AGEATDEATH,varchar(255),YES,,NULL, ADDRESS,varchar(255),YES,,NULL, ADDITIONALADDRESS,varchar(255),YES,,NULL, RANKORPROFESSION,varchar(255),YES,,NULL, DATEOFBIRTH,varchar(255),YES,,NULL, PLACEOFBIRTHa,varchar(255),YES,,NULL, PLACEOFDEATH,varchar(255),YES,,NULL, CAUSEOFDEATH,varchar(255),YES,,NULL, SPOUSENAMESe,varchar(255),YES,,NULL, SPOUSENAMESh,varchar(255),YES,,NULL, SPOUSERANK,varchar(255),YES,,NULL, FATHERNAMES,varchar(255),YES,,NULL, FATHERNAMEh,varchar(255),YES,,NULL, MOTHERNAMES,varchar(255),YES,,NULL, MOTHERNAMESh,varchar(255),YES,,NULL, OTHERSURNAME,varchar(255),YES,,NULL, MOUNRNEDBY,varchar(255),YES,,NULL, SURNAMEOFINFORMANT,varchar(255),YES,,NULL, GIVENNAMEINFORMANT,varchar(255),YES,,NULL, ADDRESSOfINFORMANT,varchar(255),YES,,NULL, RELATIONSHIP,varchar(255),YES,,NULL, MARRIAGEDATE,varchar(255),YES,,NULL, HEBREWMARRIAGEDATE,varchar(255),YES,,NULL, GROOMGIVENNAMES,varchar(255),YES,,NULL, GROOMHEBREWNAME,varchar(255),YES,,NULL, GROOMSURNAME,varchar(255),YES,,NULL, OTHERWISE,varchar(255),YES,,NULL, GROOMAGE,varchar(255),YES,,NULL, GROOMCONDITION,varchar(255),YES,,NULL, GROOMRANK,varchar(255),YES,,NULL, GROOMADDRESS,varchar(255),YES,,NULL, GROOMFATHERGIVENNAME,varchar(255),YES,,NULL, GROOMFATHERHEBREWNAME,varchar(255),YES,,NULL, GROOMFATHERSURNAME,varchar(255),YES,,NULL, GROOMFATHERCONDITION,varchar(255),YES,,NULL, GROOMFATHERRANK,varchar(255),YES,,NULL, BRIDEGIVENNAME,varchar(255),YES,,NULL, BRIDEHEBREWNAME,varchar(255),YES,,NULL, BRIDESURNAME,varchar(255),YES,,NULL, BRIDEPREVIOUSSURNAME,varchar(255),YES,,NULL, BRIDEAGE,varchar(255),YES,,NULL, BRIDECONDITION,varchar(255),YES,,NULL, BRIDERANK,varchar(255),YES,,NULL, BRIDEADDRESS,varchar(255),YES,,NULL, BRIDEFATHERGIVENNAME,varchar(255),YES,,NULL, BRIDEFATHERHEBREWNAME,varchar(255),YES,,NULL, BRIDEFATHERSURNAME,varchar(255),YES,,NULL, BRIDEFATHERCONDITION,varchar(255),YES,,NULL, BRIDEFATHERRANK,varchar(255),YES,,NULL, BRIDESMOTHERGIVENNAME,varchar(255),YES,,NULL, BRIDEMOTHERHEBREWNAME,varchar(255),YES,,NULL, BRIDEMOTHERMAIDENNAME,varchar(255),YES,,NULL, PLACEOFMARRIAGE,varchar(255),YES,,NULL, SURNAMEWITNESS,varchar(255),YES,,NULL, GIVENNAMEWITNESS,varchar(255),YES,,NULL, SURNAMEWITNESSa,varchar(255),YES,,NULL, GIVENNAMEWITNESSa,varchar(255),YES,,NULL, MINISTERREGISTRARSURNAME,varchar(255),YES,,NULL, MINISTERREGISTRARGIVENNAME,varchar(255),YES,,NULL, PARENTGUARDIAN,varchar(255),YES,,NULL, PARENTORGUARDIANOCCUPATION,varchar(255),YES,,NULL, RELIGIONENTERED,varchar(255),YES,,NULL, AGENOTES,varchar(255),YES,,NULL, MARITALSTATUS,varchar(255),YES,,NULL, CHILDREN,varchar(255),YES,,NULL, PARENTS,varchar(255),YES,,NULL, SIBLINGS,varchar(255),YES,,NULL, YEARSMARRIED,varchar(255),YES,,NULL, JCPUBLICATIONDATE,varchar(255),YES,,NULL, POSITION,varchar(255),YES,,NULL, TRADE,varchar(255),YES,,NULL, DIRECTORYSECTION,varchar(255),YES,,NULL, LOCATION,varchar(255),YES,,NULL, DATEREGISTERED,varchar(255),YES,,NULL, YEAR,varchar(255),YES,,NULL, PHOTOFILENAME,varchar(255),YES,,NULL, PROPOSEDRELEASE,double,YES,,NULL, UNIQUEGEOGRAPHICALREFERENCE,varchar(255),YES,,NULL, NOTES,varchar(255),YES,,NULL, LEDGERNO,varchar(255),YES,,NULL, AMOUNTDUE,varchar(255),YES,,NULL, FUTURE4,varchar(255),YES,,NULL, FUTURE5,varchar(255),YES,,NULL, FUTURE6,varchar(255),YES,,NULL, FUTURE7,varchar(255),YES,,NULL, FUTURE8,varchar(255),YES,,NULL, FUTURE9,varchar(255),YES,,NULL, regiment,varchar(255),YES,,NULL, JCPAGENUMBER,varchar(255),YES,,NULL, displaysource,varchar(255),YES,,NULL Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality ,Sub_part,Packed,Comment global,0,PRIMARY,1,ID,A,NULL,NULL,NULL,
exhaust limit of auto_increment int on mysql
Hi All Suppose I have a table in mysql with a column name ID int(5) primary key auto_increment 1 What happens when the upperr limit of the integer is reached? 2 What are those limits? I am not a hardcore database person, though do some database stuff for a company, so accept the novice question :) Thank you -- Aman Raheja AGF Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication
hi there , is it possible to have both slave and master on the same machine ? how do i go about setting that up for testing purposes ? sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: exhaust limit of auto_increment int on mysql
On Thu, Feb 27, 2003 at 04:37:16PM -0600, Aman Raheja wrote: Hi All Suppose I have a table in mysql with a column name ID int(5) primary key auto_increment 1 What happens when the upperr limit of the integer is reached? Error. I don't know the specific error, because I've never tried it. But if you do it with a signed tinyint, you'll only need 128 inserts to find out. :-) 2 What are those limits? Depends on the column type. Check here for sizes: http://www.mysql.com/doc/en/Storage_requirements.html An INT is 4 bytes. If you declare it unsigned, that's 2**32 or 2**31 if signed. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 24 days, processed 749,803,197 queries (354/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: exhaust limit of auto_increment int on mysql
At 16:37 -0600 2/27/03, Aman Raheja wrote: Hi All Suppose I have a table in mysql with a column name ID int(5) primary key auto_increment The (5) is irrelevant. That is the display width, which has nothing to do with the range of the underlying column type. 1 What happens when the upperr limit of the integer is reached? You get a duplicate key error. 2 What are those limits? The upper range of the column type. These limits are documented in the manual. If you make the column UNSIGNED, the range is higher, so you should do that for an AUTO_INCREMENT column. I am not a hardcore database person, though do some database stuff for a company, so accept the novice question :) Thank you -- Aman Raheja AGF Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication
At 9:42 +1100 2/28/03, Dan Rossi wrote: hi there , is it possible to have both slave and master on the same machine Sure. ? how do i go about setting that up for testing purposes ? sql Same way you'd set up multiple servers even they weren't being used for replication: Give them different data directories, make sure they're listening on different TCP/IP ports and socket files, make sure they log to different files, etc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
speeding up simple SELECT statements with the C api?
I'm in the process of writing a report writer for mysql which is necessary for the migration from the current database of my company's product to mysql (due to the amount of reports dependent on the current database report writer). Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. I was originally using the C++ library but changed it to the C api after seeing that the C++ api was giving a much larger performance penalty likely due to it duplicating the row data (vtune said about 3x penalty but it was more like a 1.5x penalty). Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. The select statements are very simple and usually take a form like select field1, field2, field3 from table where field4 = (some value) order by field1 and sometimes like this: select field1, field2, field3 from table where field4 = (some value) LIMIT 1 Most often there will only be one result returned and only one table is ever queried at a time. I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. Thanks, Gary Hertel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speeding up simple SELECT statements with the C api?
Oh, and just as a sidenote all the queries have indexes that match the order by and where conditions (they're the same indexes as the original database). -Gary Hertel - Original Message - From: Gary Hertel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 2:52 PM Subject: speeding up simple SELECT statements with the C api? I'm in the process of writing a report writer for mysql which is necessary for the migration from the current database of my company's product to mysql (due to the amount of reports dependent on the current database report writer). Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. I was originally using the C++ library but changed it to the C api after seeing that the C++ api was giving a much larger performance penalty likely due to it duplicating the row data (vtune said about 3x penalty but it was more like a 1.5x penalty). Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. The select statements are very simple and usually take a form like select field1, field2, field3 from table where field4 = (some value) order by field1 and sometimes like this: select field1, field2, field3 from table where field4 = (some value) LIMIT 1 Most often there will only be one result returned and only one table is ever queried at a time. I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. Thanks, Gary Hertel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem connecting to mysqld
At 22:05 27/02/2003 +, Ahmed Shams wrote: I am having problems using MYSQL on Windows ME operating system. It installs fine with no problems, but at the point where I try to connect to mysqld i get the error of bad command or file name at the dos propmt. therefore when I try to create a database I keep getting Error-2003: Can't connect to MYSQL server on 'local host' (10061) . I have tried uninstalling MYSQL and installing it again, and checked for the files that suppose to be there and everything seems to be fine. Please help for I need this ASAP. Thanks you Try: mysql -uroot -p when you will prompted by the password left it blank (the default install) so after you read the Manual about security change the it. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ São Paulo - Brazil /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: QUESTION.....
From: [EMAIL PROTECTED] To: tegun bahq [EMAIL PROTECTED] Subject: Re: QUESTION. Date: Fri, 28 Feb 2003 00:33:06 +0100 Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: our LICENSE NO. is 46 we are running an e-commerce site on a linux machine can the data base on a windows machine..? ¹Ú Å°Ç.. _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication
ahh ok cool , i am installing 4.0.11 now so one is going to a directory called mysqlmaster and another mysqlslave and slave on 3307 cool thanks and each sock file goes to their own var dir -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, February 28, 2003 9:53 AM To: Dan Rossi; [EMAIL PROTECTED] Subject: RE: replication At 9:42 +1100 2/28/03, Dan Rossi wrote: hi there , is it possible to have both slave and master on the same machine Sure. ? how do i go about setting that up for testing purposes ? sql Same way you'd set up multiple servers even they weren't being used for replication: Give them different data directories, make sure they're listening on different TCP/IP ports and socket files, make sure they log to different files, etc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Load data infile warnings?!?! STILL not in 4.1?
After wrestling to get everything set up with 4.1, it looks like it still does not give the VITAL information of what problems were encountered in data loads (on 4.1 2/25/03) - mysql load data infile '/Users/jason/Desktop/Hoodata/National1-subset.tab' into table tri; Query OK, 91515 rows affected (3.27 sec) Records: 91515 Deleted: 0 Skipped: 0 Warnings: 5559 mysql show warnings; Empty set (0.00 sec) mysql show errors; Empty set (0.00 sec) When will this ever be fixed? I do not know a SINGLE MySQL DBA who has not cursed this lacking as a fundamental design flaw. Not a 'wouldn't it be nice' feature, but a 'oh my god, you must be kidding me' kind of problem.. It continues to make DBAs lives difficult. Even Oracle got this one right. The periodic suggestion of just 'select'ing into an outfile and comparing doesn't work too well for most folks who are dealing with text fields that may have had trailing spaces, dates in other formats, decimal/float numbers that may be displayed differently, etc. It is truly a nightmare that seems to never be resolved. Is this on track for MySQL 2020, or should I just give up and learn to love Oracle and SQL-Plus? Jason. p.s. - I am not an ungrateful user of Open Source. For years I was a paying MySQL customer and felt confident that this issue would be resolved (since it was on the 'Things That Must be Done in the Near Future' list), but three or four years later it is still there. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Invoicing (paper)
Hello, I think this is fairly on-topic, but does anyone know of or use a [free] invoice program that uses MySQL? I need to setup something for my Dad that is quick and easy to use. Doesn't need to have all that much, just simple things, as I would probably modify it down to what it needs to be. Thanks, Bryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Sheryl, See my comments below... (1) What is the rule that SELECT DISTINCT uses when deciding which of multiple instances to return? Does it return the first one? How it selects the distinct items is outside my area of knowledge. I would not assume it is the first one or the last one, or anywhere in between. If there are multiple records for one author in a table and you run a distinct, your results set will include one record for that one author, regardless of where he/she is in your table. (2) Is there a way to write a SELECT statement to return the record for the author with the highest royalty percent (a different field in the table)? That is the one that I want. I would try the MAX() function... see link... http://www.mysql.com/doc/en/Group_by_functions.html#IDX1359 If you use ORDER BY, it will give you all the authors in order (I don't think you want that, do you?). ... but you still need to answer the question that I posed before Good luck. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Frank, Before you go any further, I would attempt to answer one question. Are you trying to determine which Author had the highest royalties or are you trying to determine which Author has the highest royalties in each program(I am assuming an author can work on multiple programs)? This will make a difference in how your structure your query. I'm trying to determine which author has the highest royalty percent FOR EACH PROGRAM, not overall. I'm displaying a list of programs and authors, and when there is more than one author, I want to show the principal author (i.e., the one earning the highest royalty percent). Also, I don't think you want SELECT DISTINCT * I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES. etc. is better Also remember, a.AUTHOR will give you different results than r.AUTHOR.. How will the results differ? I guess I'm not clear on how DISTINCT works. Will it only look at combinations of a.AUTHOR and r.ROYALTIES if I write it that way? I would try the MAX() function... see link... http://www.mysql.com/doc/en/Group_by_functions.html#IDX1359 If you use ORDER BY, it will give you all the authors in order (I don't think you want that, do you?). I don't want all the authors in order of royalty percent. I want the them to be in groups by Program ID and ordered by royalty percent within that (or just take the max within each group). Another person on this list showed me how a subselect could achieve this result, but MySQL doesn't support subselects. - Sheryl - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speeding up simple SELECT statements with the C api?
Hello Gary, The MySQL server uses the myisam libraries to access MyISAM tables at the lower level. It works similarly to and just as quickly as C-ISAM, though the API is fairly different (IMHO). A myisam user manual is available, but is not complete (I know, since I wrote it). You could use that method for much faster navigation when your application reads just a row at a time. MySQL (4.0?) also provides a HANDLER syntax for doing row at a time IO but more at the SQL level. It is slower than myisam, but probably much faster than normal SQL for your purposes. I recommend you try (order of preference): - Rewrite the reports to be set based not row based. Or - See if HANDLER syntax is quick enough. I think it will be. Or - See if you can cope with the myisam libraries. Or - Beef up your hardware and tweak everything. Good luck, Stephen Brownlow, Sydney, Australia. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load data infile warnings?!?! STILL not in 4.1?
At 19:08 -0500 2/27/03, [EMAIL PROTECTED] wrote: After wrestling to get everything set up with 4.1, it looks like it still does not give the VITAL information of what problems were encountered in data loads (on 4.1 2/25/03) - mysql load data infile '/Users/jason/Desktop/Hoodata/National1-subset.tab' into table tri; Query OK, 91515 rows affected (3.27 sec) Records: 91515 Deleted: 0 Skipped: 0 Warnings: 5559 mysql show warnings; Empty set (0.00 sec) mysql show errors; Empty set (0.00 sec) When will this ever be fixed? I do not know a SINGLE MySQL DBA who has not cursed this lacking as a fundamental design flaw. Not a 'wouldn't it be nice' feature, but a 'oh my god, you must be kidding me' kind of problem.. It continues to make DBAs lives difficult. Even Oracle got this one right. The periodic suggestion of just 'select'ing into an outfile and comparing doesn't work too well for most folks who are dealing with text fields that may have had trailing spaces, dates in other formats, decimal/float numbers that may be displayed differently, etc. It is truly a nightmare that seems to never be resolved. Well, while you're waiting, and if you have Perl DBI installed, have a look here: http://www.kitebird.com/mysql-cookbook/examples-ld.php Is this on track for MySQL 2020, or should I just give up and learn to love Oracle and SQL-Plus? Jason. p.s. - I am not an ungrateful user of Open Source. For years I was a paying MySQL customer and felt confident that this issue would be resolved (since it was on the 'Things That Must be Done in the Near Future' list), but three or four years later it is still there. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query optimisation
hi there i was wondering if there was a must get mysql book or paper covering query optimisation ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Inner join question!
All, I have two tables like the following table A date num1 num2 time table B date num1 num2 time When I run a query like the following select * from A as a inner join B as b on a.num1 = b.num1 and a.date=b.date and a.num2=b.num2 and a.time=b.time I am getting the results repeated 4 times, could someone tell me why I am getting 4 rows and how can I elliminate it? Thanks. Ramesh __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
rollback a table?
Hi, Something bad happened the other day, a query hosed all the data in my table, but luckily I had an original dump of the table from 4 months ago and binlogs from then on. I had to load the original table into a separate db and then grep through the binlogs for queries to update it with, stopping at the one that hosed my data. Anyway, it was a project and it seems like there should be a better way. Is there? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: rollback a table?
Mark wrote: Hi, Something bad happened the other day, a query hosed all the data in my table, but luckily I had an original dump of the table from 4 months ago and binlogs from then on. I had to load the original table into a separate db and then grep through the binlogs for queries to update it with, stopping at the one that hosed my data. Anyway, it was a project and it seems like there should be a better way. Is there? Kinda. Firstly, I use the human-readable log, which is produced with the --log-update option of mysqld. Secondly, back up your data more often. Every night is good... Most likely using the --log-update option slows things down a little during inserts / updates, but it's so much easier to read than the binary log. I activate this backup script via crond every night: #/bin/sh DATE=`/bin/date +%d-%m-%y` cd /root/sql/backups for I in EnergyShop NUS ebills irm mysql sales Fuel do /usr/local/mysql/bin/mysqldump -v --opt $I $I.dump -pMyPasswordGoesHere done /usr/local/mysql/bin/mysqladmin shutdown -pMyPasswordGoesHere /usr/bin/nice -n -10 /usr/local/mysql/bin/mysqld_safe --enable-locking --log-update --log-slow-queries --log-long-format cd .. tar -zcvf backups_$DATE.tar.gz backups echo Backup of MySQL databases complete! -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inner join question!
The only reason I could see for this would be if you have duplicates (across date num1 num2 time) in both tables, or quadruplicates in one. You join looks correct, so take a closer look at your data. Try running the following queries: SELECT A.date, num1, num2, A.time, Count(*) FROM A GROUP BY A.date, num1, num2, A.time HAVING Count(*) 1 SELECT B.date, num1, num2, B.time, Count(*) FROM B GROUP BY B.date, num1, num2, B.time HAVING Count(*) 1 HTH, Tore. - Original Message - From: Ramesh Pillai [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 10:36 PM Subject: Inner join question! All, I have two tables like the following table A date num1 num2 time table B date num1 num2 time When I run a query like the following select * from A as a inner join B as b on a.num1 = b.num1 and a.date=b.date and a.num2=b.num2 and a.time=b.time I am getting the results repeated 4 times, could someone tell me why I am getting 4 rows and how can I elliminate it? Thanks. Ramesh __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RAND() isn't :)
Hi all, On my LAN server (FreeBSD, MySQL 3.32) the following query works fine, and seems to return 2 random rows from the table: SELECT * FROM disc ORDER BY RAND() LIMIT 2 However, when I upload the scripts to the live server (Linux, MySQL 3.32), the results are VERY un-random -- only very occasionally do the results differ between each query, usually returning the same two rows. The data in the two tables isn't identical, but both tables DO contain the same number of rows, with the same IDs. Where should I be looking for reasons why the RAND() isn't very random??? TIA Justin sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
DISTINCT works on the result set and across the entire select list. It will suppress duplicate result *rows*. This is an interesting problem when using MySQL due to the lack of support for nested SELECTs. In other database systems, I'd use a correlated subquery in the where clause, but with MySQL a different solution would be required. There may be a more direct way, but the following works on both version 4 and 3.23: DROP TABLE IF EXISTS tmproymax; CREATE TEMPORARY TABLE tmproymax (ProgramID int, Royalty decimal(10,2)); INSERT INTO tmproymax SELECT ProgramID, Max(Royalty) as MaxRoyalty FROM royalties GROUP BY ProgramID; SELECT R.ProgramID, R.Royalty, A.AuthorID, A.FirstName, A.LastName FROM authors as A INNER JOIN royalties as R ON A.AuthorID = R.AuthorID INNER JOIN tmproymax as RM ON R.ProgramID = RM.ProgramID AND R.Royalty = RM.Royalty; For using this with PHP, I'm pretty sure you'll have to run each statement separately, but using the same connection, and you should get the correct result from the last select. I'd be interested to hear if there is another trick to working without a correlated subquery for finding details off of a row identified by Min(), Max(), etc. I tried using a CREATE TEMPORARY TABLE tmproymax SELECT ... but couldn't get the aggergate column named (in version 4), so I could use it in the join in the last statement. For version 3.23, that construct would work: DROP TABLE IF EXISTS tmproymax; CREATE TEMPORARY TABLE tmproymax SELECT ProgramID, Max(Royalty) as Royalty FROM royalties GROUP BY ProgramID; SELECT R.ProgramID, R.Royalty, A.AuthorID, A.FirstName, A.LastName FROM authors as A INNER JOIN royalties as R ON A.AuthorID = R.AuthorID INNER JOIN tmproymax as RM ON R.ProgramID = RM.ProgramID AND R.Royalty = RM.Royalty; HTH, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
database connectivity
Can anyone plz help. Is database connectivity between Mysql (Linux) and Visual C++ posible, which has to be achived from a remote server. How can I achive this functionality. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
