SV: Tablecrash
yes, its gone in 4.0.16
-Ursprungligt meddelande-
Från: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Skickat: den 23 oktober 2003 21:12
Till: [EMAIL PROTECTED]
Ämne: Re: Tablecrash
Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] wrote:
I got a table that crashes everytime that I use delete from
hvd_layout_settings
This is a infinite loop:
while (1)
{
check table hvd_layout_settings = table ok
delete from hvd_layout_settings
check table hvd_layout_settings = table crashed
repair table hvd_layout_settings
}
Im using mysql 4.0.15a.
Seems your bug is the same as http://bugs.mysql.com/bug.php?id=1321
Which OS do you use?
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored
by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: GRANT problem
GRANT insert,delete,update,select on phonewatch.* to [EMAIL PROTECTED] identified by 'password' works. GRANT insert,delete,update,select on phonewatch.* to datatal identified by 'password' do not. Can someone please test this and verify if it's a bug? -Ursprungligt meddelande- Från: Nathaniel Mallet [mailto:[EMAIL PROTECTED] Skickat: den 24 oktober 2003 03:04 Till: Rory McKinley; Datatal AB - Gauffin, Jonas Kopia: [EMAIL PROTECTED] Ämne: RE: GRANT problem I was having the same problems trying to get a user added to a new DB, so here's are the commands I used: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES; where user and password are whatever you need it to be. I've never had much luck with % as host, so specify localhost for you user, and you should be all set. Nat -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:21 AM To: Datatal AB - Gauffin, Jonas Cc: [EMAIL PROTECTED] Subject: Re: GRANT problem Ok. I'm stumped. If I'm reading the manual correctly, you were right about not hving to specify the host..the only difference between your version and the manual is that they use single quotes around the user name but I can't see how that makes any difference. I normally just specfiy hosts because using wildcards for hosts makes me nervous - hey, even paranoid people have enemies :) If anyone knows the answer to this I would be interested in knowing what it is too Sorry I can't be of more help Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:05 PM Subject: SV: GRANT problem it works if I specify the host. Why doesn't % work as host? -Ursprungligt meddelande- Från: Rory McKinley [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:56 Till: Datatal AB - Gauffin, Jonas Ämne: Re: GRANT problem thinking. In one of your follow up posts I see that you have the command as this : C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD Try C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p And enter password at the prompt Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:45 AM Subject: SV: GRANT problem Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] forums.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: GRANT problem
GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; yes. no diffrence. Specifying '%' or no host doesn't work for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: gemini
On Fri, Oct 24, 2003 at 05:04:28PM +1000, Chris wrote: They gave up on it eh? Was that more because of the legal problems that happened, or due to BDB and InnoDB continuing to improve and provide better solutions? Leagal issues, probably. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication redirector
On Fri, Oct 24, 2003 at 04:00:07AM +0200, Benjamin KRIEF wrote: Is your bottleneck CPU, I/O, or lock contention? Is your slave running on worse hardware than your master? my bottleneck is CPU , definitely. the load can go up to 35 in peak hours. no swapping occurs , my key efficiency is at 99,89 . The load average often has little relationship to the actual CPU utilization. Is your CPU at 100% most of the time? Or are your procs blocked on disk I/O. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,693,767 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB
On Thu, Oct 16, 2003 at 05:13:58PM -0300, Leonardo Rodrigues wrote: Hello all, The referencial integrity control don´t apply to a simple table, do it? What's a simple table? My DB is growing, and growing, and I´m very concerned about my host limitions of disk usage. So... You should be! Another question: postgreeSQL has a SQL command called VACUUM, which optimize and reduce the table´s used space disk. I discover for mySQL the commands OPTIMIZE TABLE and ANALYZE TABLE, but both don´t run on InnoDB. ANALYZE table runs on InnoDB. My question are: are these commands similar to VACUUM? What´s the difference? How can I run on InnoDB? I don't know much about PostgreSQL, so I'm not sure. If you describe what VACUUM does, I may be able to help explain how to do the same thing with MySQL. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,867,850 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I do this in one select sql?
Hi everybody; I have a table like this; || NAME || IF_PURCHASED || COUNT || --- tom true5 tom false 7 tom false 3 sam true3 sam true4 sam false 2 ben true1 i want to a select querry that can do this; || NAME || TRUE_TOTAL_COUNT || FALSE_TOTAL_COUNT || tom 5 10 sam 7 2 ben 1 0 I did this with 2 temporary tables. one of selects true total count , other selects false total counts then I JOINed them with UNION Can I do this in one select sql? __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I do this in one select sql?
Hi everybody; I have a table like this; || NAME || IF_PURCHASED || COUNT || --- tom true5 tom false 7 tom false 3 sam true3 sam true4 sam false 2 ben true1 i want to a select querry that can do this; || NAME || TRUE_TOTAL_COUNT || FALSE_TOTAL_COUNT || tom 5 10 sam 7 2 ben 1 I did this with 2 temporary tables. one of selects true total count , other selects false total counts then I JOINed them with UNION Can I do this in one select sql? __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB
VACUUM is basically a workalike for: ALTER TABLE table_name TYPE=InnoDB; There are various types of VACUUM commands as well. VACUUM FULL basically locks the table and shrinks the on-disk table space. The normal VACUUM (normal since 7.2 I think), does not lock the table (similar to REORGANISE in some other databases, while REORGANISE in some databases like SQLBase REORGANISE locks everything) but does a basic defrag. Hope this helps! Regards, Chris On Fri, 24 Oct 2003 05:23 pm, Jeremy Zawodny wrote: On Thu, Oct 16, 2003 at 05:13:58PM -0300, Leonardo Rodrigues wrote: Hello all, The referencial integrity control don´t apply to a simple table, do it? What's a simple table? My DB is growing, and growing, and I´m very concerned about my host limitions of disk usage. So... You should be! Another question: postgreeSQL has a SQL command called VACUUM, which optimize and reduce the table´s used space disk. I discover for mySQL the commands OPTIMIZE TABLE and ANALYZE TABLE, but both don´t run on InnoDB. ANALYZE table runs on InnoDB. My question are: are these commands similar to VACUUM? What´s the difference? How can I run on InnoDB? I don't know much about PostgreSQL, so I'm not sure. If you describe what VACUUM does, I may be able to help explain how to do the same thing with MySQL. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,867,850 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE semantics
Hi all, Does anyone have a list of what affect ALTER TABLE has on each different type of MySQL table? If I have read correctly, ALTER TABLE locks MyISAM tables until completeion. Am I right on this? How about InnoDB and BDB? If memory serves correctly, HEAP doesn't support ALTER TABLE. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help obtaining DBI binaries
Please help, where do I download DBI module binaries thanks Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can I do this in one select sql?
this should work: select NAME, sum(if(IF_PURCHASED='true',COUNT,0)), sum(if(IF_PURCHASED='false', COUNT,0)) from TABLENAME group by NAME; eva -Original Message- From: cengiz tahan [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:29 AM To: [EMAIL PROTECTED] Subject: Can I do this in one select sql? Hi everybody; I have a table like this; || NAME || IF_PURCHASED || COUNT || --- tom true5 tom false 7 tom false 3 sam true3 sam true4 sam false 2 ben true1 i want to a select querry that can do this; || NAME || TRUE_TOTAL_COUNT || FALSE_TOTAL_COUNT || tom 5 10 sam 7 2 ben 1 I did this with 2 temporary tables. one of selects true total count , other selects false total counts then I JOINed them with UNION Can I do this in one select sql? __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: 1016 Can't open file: 'tablename.MYI.
Hello! I get this error from my MySQL-database: Error: 1016 Can't open file: 'tablename.MYI. (errno: 145). I know what to do to fix this, but I need to know what causes this error. If anyone know, please respond. Best regards, Marius R - Marius Røstad System Developer Intrapoint AS Beddingen 8 N-7014 Trondheim Norway Phone +47 73 600 800 Fax +47 73 600 801 Mobile +47 924 23 780 www.intrapoint.no
Off Topic: Translation Mysql Manual
Hello :P Only one question. A friend translated the 6 chapter of mysql manual to spanish. And I want know if this translate is posible or have any problem with the mysql licence or mysql manual licence. The translate is here: http://amauta.ourproject.org/contrib/ Well. -- | .---. |http://www.unimauro.pe.kz | | |º_º| | Carlo Mauro Cárdenas Fernández | | |:_/| | irc.freenode.net: apesol tdlp-es | | // \\ | irc.gnome.org: gnome-hispano | |(| |) | telf: 051-001-4582877 | |/'\_ _/'\ | Jr.Las Cidras 664, Lima Peru | |\__) (__/ |Lo hacemos por diversion.Linus T| |__|__| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML support for MySQL
On Mon, Oct 13, 2003 at 11:45:33PM -0600, Prasad Budim Ram wrote: Is it possible to direct xml support for queryiing a SQL statement and get the result in also in xml? Nope. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,533,760,097 queries (440/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auditing MySQL (EAI)
Dear list, I currently have the task to write an adapter for MySQL (for SapDB as well btw) that should track changes on certain tables. I know that this question has been asked several times before but unfortunately didn't find no suitable answer: Is it possible to monitor/ audit changes in certain tables (create, update, delete) with MySQL and e.g. raise external programs? If it's not possible now, does anyone know of plans to integrate such a feature in one of the future versions of MySQL? Kind regards, Stephan -- NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien... Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService Jetzt kostenlos anmelden unter http://www.gmx.net +++ GMX - die erste Adresse für Mail, Message, More! +++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Potential Bug with inserting nulls with MySQL 4.1.1 nightly snaps hot
David Ritter [EMAIL PROTECTED] wrote: I think I have found a bug in the MySQL 4.1.1 C Binding API with inserting NULL values using the bind API. Can someone please send me an address that I can send a test case to the MySQL development staff so they can take a look at it? Alternatively can someone tell me if this is the appropriate forum to post such items? You can report bug to the bugs database http://bugs.mysql.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] Book: The Definitive Guide to MySQL, 2nd ed
I would like to announce the 2nd edition of The Definitive Guide to MySQL by Michael Kofler apress 2004 The book covers many aspects of MySQL application development, including - installation (Windows, Linux) - GUIs (MySQL Control Center, phpMyAdmin) - database design - SQL - security - administration (backups, logging, replication ...) - InnoDB, transactions, foreign key rules - programming with PHP, Perl, Java, C, C++, VB6, VB.NET, C# The book also contains a compact reference of SQL commands, mysql_xxx options and some APIs (PHP, Perl, JDBC, C). The book is up-to-date to MySQL 4.0.14 / MySQL 4.1 (snapshot from end of July 2003). For more information, a detailed TOC (PDF), a sample chapter (PDF) etc. please have a look at: http://www.kofler.cc/mysql/mysqlbook.html Link to amazon: http://www.amazon.com/exec/obidos/ASIN/1590591445/michaelkofler-20 Link to apress (publisher): http://www.apress.com/book/bookDisplay.html?bID=229 There is also a German edition of this book: http://www.kofler.cc/mysql/mysqlbuch.html Finally, for all who are considering a migration from Microsoft SQL Server to MySQL, I wrote mssql2mysql, a free (GPL) script which might help you: http://www.kofler.cc/mysql/mssql2mysql.html Thank you for your time, Michael Kofler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MATCH ... AGAINST('...' WITH QUERY EXPANSION) syntax?
Hi! On Oct 23, Matt W wrote: Hi Sergei, More full-text questions from me since I just noticed your code and doc changes. :-) What does this new WITH QUERY EXPANSION syntax do? More relevant results? More flexible? Faster? Is it for NLQ, boolean, or both (since both ft_[nlq | boolean]_search.c are changed)? Does it have something to do with 2 level indexes, or aren't they being used yet? Sorry for all the questions! First - it's not pushed yet :) Then - no it does not have anything to do with 2 level indexing, it's for NLQ only, slower, unrelated, yes. The idea - well known in Information Retrieval science - basically is to perform a search, take top N documents, add them to the query, and redo the search. It is expected to improve results for short queries (short query text in AGAINST). I said expected because all test collections that I have use very long queries, so though query expansion did increase recall significantly, overall results were worse. So, I need to get test collections with short queries and tune the algorithm somewhat. And yes, it makes the search slower - sometimes noticeably slower. This will also be fixed, when I'll implement so called unsafe optimization for NL search. It is this optimization that relies on 2-level index structure, but, again, I need to get new test collections to do it, to adjust thresholds for best results/speed. Also noticed that ft_max_word_len_for_sort has become a constant, instead of run-time definable, and ft_query_expansion_limit replaces it, though they don't sound related. I'm wondering about max_..._for_sort because, at least in 4.0, I need to lower it to 10-12 to keep the temp files smaller when building the index. :-( Are the temp files going to get too big in 4.1 when I can't adjust ft_max_word_len_for_sort or is the algorithm different when indexing? If the temp files are the same size as 4.0, I wish ft_max_word_len_for_sort would be restored or I'm going to have problems. :-( I removed it because I thought it's too complex and is never used - so it's better to remove it for not to confuse users and keep number of variables manageable. If I'm wrong here - I'll put it back, of course :) Making it 10-12 to keep temp files smaller - you will not need it in 4.1, as 4.1 uses strlen(word)+const bytes per word in temp file, not ft_max_word_len_for_sort bytes. (By the way, this nice feature applies to normal indexes on VARCHAR/CHAR columns too :). So, the main reason for ft_max_word_len_for_sort (reduce i/o significantly - from 255 to 20 bytes/word) was removed. Another reason still applies (that's why it's defined to be 20, and not 255) - in memory each word occupies ft_max_word_len_for_sort bytes, so the smaller this value the more words will fit in one sort chunk. But it doesn't impact performance as much as i/o. Words that are longer than ft_max_word_len_for_sort will not be added to index during repair_by_sort - so they have to be added later using old and slow method (simply inserted into b-tree). The way to choose this value properly is to run ft_dump -l and set that threshold so that almost all the words are shorter, but no memory is wasted on few extra long words. 20 is almost always an ok value here. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving Binary Files to mysql
Timotius Alfa [EMAIL PROTECTED] wrote: How to save binary files to mysql ? I want to save *.exe whole in a binary files, after that I want to save it to mysql. thanks You can store binary data in the BLOB columns: http://www.mysql.com/doc/en/BLOB.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: need example of create table syntax
suwandy [EMAIL PROTECTED] wrote: i'm currently learning mysql and interesting to constraint, relational and check function. could anyone give some example please ? CHECK clause is parsed but it doesn't do anything. Info about FOREIGN KEY CONSTRAINTS you can find at: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple contains query question
Dale Hans [EMAIL PROTECTED] wrote: I have a field in the table, call it 'stuff', and it contains a list of values that are comma separated. i.e. 'Books,DVDs,Video Tapes,CDs' I want to SELECT out all records that have an item. I know IN works the other way, so is there something that will get what I want like this: SELECT * FROM Inventory WHERE stuff CONTAINS 'Books' ORDER BY name Take a look at FIND_IN_SET() function: http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mysql supports russion language or any other language?
Louie Miranda [EMAIL PROTECTED] wrote: Hi, i have this form that goes directly.. to a database. I have some clients who dont use english. If they enter on a form a special russion or chinese character will mysql translate it properly? If you use a Web form, you should try to transform the input with something like htmlentities() in PHP. This will translate all non ASCII characters to encoded full ASCII strings which will then be language independent. On the other side, you might try to use mysql-4.1.x with UTF8 rows Jean-Pierre -- Powered by Linux From Scratch - http://schwicky.net/ PGP Key ID: 0xEE6F49B4 - AIM/Jabber: Schwicky - ICQ: 4690141 Nothing is impossible... Everything is relative! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE tables error
I've created several merge tables from the command prompt. All seems well, the .frm and .MRG files are created. The contents of the .MRG files are accurate. I flushed the tables (even restarted MySQL). I can see the newly created merge tables, but when I try to run a DESCRIBE or a SELECT COUNT(*) to verify the records counts, I get the following error message: ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143) All table structures are identical. Am I missing a basic error numbers page in the online docs? I've found a few, but nothing comprehensive, like a page where I can enter an error number and find the detailed message, probable cause, recommdned action, etc. The only thing that I have been able to find is: http://www.mysql.com/doc/en/Error-returns.html which essentially confirms that an error 1016 is a problem opening a file. I am using MySQL version 3.23.56-nt on Windows2000. Any help, links or guidance would be greatly appreciated. /Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MERGE tables error
Error 143 suggests that you have conflicting table definitions. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 7:08 AM To: [EMAIL PROTECTED] Subject: MERGE tables error I've created several merge tables from the command prompt. All seems well, the .frm and .MRG files are created. The contents of the .MRG files are accurate. I flushed the tables (even restarted MySQL). I can see the newly created merge tables, but when I try to run a DESCRIBE or a SELECT COUNT(*) to verify the records counts, I get the following error message: ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143) All table structures are identical. Am I missing a basic error numbers page in the online docs? I've found a few, but nothing comprehensive, like a page where I can enter an error number and find the detailed message, probable cause, recommdned action, etc. The only thing that I have been able to find is: http://www.mysql.com/doc/en/Error-returns.html which essentially confirms that an error 1016 is a problem opening a file. I am using MySQL version 3.23.56-nt on Windows2000. Any help, links or guidance would be greatly appreciated. /Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SV: GRANT problem
What do you mean by ' doesn't work ' ? Have you tried connecting from another machine? Datatal AB - Gauffin, Jonas wrote: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; yes. no diffrence. Specifying '%' or no host doesn't work for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to MySQL server on '[server]' (10061) - unix tcp/ip sockets
Hi, I've recently set up a new linux box and installed MySQL 4 (for the record, Knoppix/Debian and the 'unstable' MySQL 4 package). I've had no problems connecting using phpMyAdmin, but when I try to connect remotely using MySQL Control Center or SQLyog (from Windows) I get the same error (near enough) from both: SQLyog: --- SQLyog --- Error No. 2003 Can't connect to MySQL server on '[server]' (10061) --- MySQL Control Center: [server] ERROR 2003: Can't connect to MySQL server on '[server]' (10061) Searching through the mysql.com documentation led me to http://www.mysql.com/doc/en/Can_not_connect_to_server.html, which talks about unix and tcp/ip sockets. Running mysqladmin version gives me the following output: mysqladmin Ver 8.40 Distrib 4.0.13, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 2 hours 6 min 24 sec Threads: 3 Questions: 450 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 5 Queries per second avg: 0.059 Since the documentation (I forget where) showed the version output showing the tcp port the server was running on (and mine doesn't), I am led to believe the server isn't running on a tcp/ip socket. From what I can tell though, my.cnf does have the necessary lines for doing this - port=3306 (as per default) is in there, uncommented-out. Can anyone suggest what I might need to change in my configuration to enable tcp/ip connections? All the search results I've seen so far appear only to relate to people running MySQL (server) under windows... Cheers, Ben Darlow ---Disclaimer--- Unless obviously public, this email is confidential to the intended recipient(s). If you received it in error please tell the sender and then delete it. We check emails from dyslexic.com and iansyst.co.uk, but you should virus check incoming emails. Emails do not always represent our official policy or a contract. Errors and omissions are excepted. iANSYST Ltd, Fen House, Fen Road, CAMBRIDGE, CB4 1UN. T +44(0)1223 420101; Fax +44(0) 1223 42 66 44; [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easiest way to output to a Latex2e tabular format???
You would be better off getting a good MySQL book, a good Perl book, and a good Latex book. Spend a week or two reading, try the examples in the books, and integrate what you have learned. Then ask questions. You want a very specific answer to a question you can't even express yet. Abner Gershon wrote: I am relatively new to mysql, latex, and linux in general. I have created a small table of about one hundred entries that I would like to print out as a table in a latex document. The form of the latex file needs to be something like: field1 field2 field3 \\ \hline As I am a novice please provide an example verbatim of the command I need to enter to accomplish this. Thank you. -Abner __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: SV: GRANT problem
I get access denied (from mysql.exe and myodbc) when specifying no host or % as host in the grant statement. -Ursprungligt meddelande- Från: gerald_clark [mailto:[EMAIL PROTECTED] Skickat: den 24 oktober 2003 15:11 Till: Datatal AB - Gauffin, Jonas Kopia: [EMAIL PROTECTED] Ämne: Re: SV: GRANT problem What do you mean by ' doesn't work ' ? Have you tried connecting from another machine? Datatal AB - Gauffin, Jonas wrote: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; yes. no diffrence. Specifying '%' or no host doesn't work for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1 installation problems
Hi, I have mysql 4.0 installed on redhat 9.0.I want to install mysql 4.1 and keep 4.0 as it is.I get the follownig message whent I try to install 4.1 mysql_install_db : line 166:0:command not found. Installing privilege tables mysqld :ERROR :unknown option --log-bin # required for replication. mysql_install_db : line 443 :9596 broken pipe ... how do I fix this problem thanx phani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE semantics
Chris, for MyISAM and InnoDB, ALTER TABLE normally locks the table to be altered in a read-only mode. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables ... From: Chris Nolan ([EMAIL PROTECTED]) Subject: ALTER TABLE semantics This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2003-10-24 00:39:40 PST Hi all, Does anyone have a list of what affect ALTER TABLE has on each different type of MySQL table? If I have read correctly, ALTER TABLE locks MyISAM tables until completeion. Am I right on this? How about InnoDB and BDB? If memory serves correctly, HEAP doesn't support ALTER TABLE. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB
All, the VACUUM in PostgreSQL removes delete-marked records from the database. In InnoDB, the VACUUM is called the purge. In InnoDB purge happens automatically in the background, the user does not need to explicitly run VACUUM, like in PostgreSQL. Also in Oracle purge is automatic. The way to compact, reorganize, or optimize, an InnoDB table is to run ALTER TABLE tablename TYPE=InnoDB; It rebuilds the table. The command ANALYZE TABLE tablename; in InnoDB updates the table statistics for the SQL optimizer. InnoDB constantly updates the row count estimate, but only updates the index cardinality estimates for join optimization at a mysqld startup, or when ANALYZE TABLE is called. This is similar to MyISAM. But remember that in InnoDB the estimates are not precise, but based on 8 random dives into the index trees. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables From: Chris Nolan ([EMAIL PROTECTED]) Subject: Re: Optimizing table (so-called VACUUM on postgreSQL) and InnoDB View: Complete Thread (3 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-10-24 00:35:58 PST VACUUM is basically a workalike for: ALTER TABLE table_name TYPE=3DInnoDB; There are various types of VACUUM commands as well. VACUUM FULL basically locks the table and shrinks the on-disk table space. The normal VACUUM (normal since 7.2 I think), does not lock the table (similar to REORGANISE in some other databases, while REORGANISE in some databases like SQLBase REORGANISE locks everything) but does a basic defrag. Hope this helps! Regards, Chris On Fri, 24 Oct 2003 05:23 pm, Jeremy Zawodny wrote: On Thu, Oct 16, 2003 at 05:13:58PM -0300, Leonardo Rodrigues wrote: Hello all, The referencial integrity control don=B4t apply to a simple table, do it? What's a simple table? My DB is growing, and growing, and I=B4m very concerned about my host limitions of disk usage. So... You should be! Another question: postgreeSQL has a SQL command called VACUUM, which optimize and reduce the table=B4s used space disk. I discover for mySQL= the commands OPTIMIZE TABLE and ANALYZE TABLE, but both don=B4t run on InnoDB. ANALYZE table runs on InnoDB. My question are: are these commands similar to VACUUM? What=B4s the difference? How can I run on InnoDB? I don't know much about PostgreSQL, so I'm not sure. If you describe what VACUUM does, I may be able to help explain how to do the same thing with MySQL. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,867,850 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Chris, 1. Peter Zaitsev ran some tests which suggested that multiple .ibd files are not slower than one ibdata file. But it will depend on the OS version. Some OS'es may have bad fsync() implementation, which can cause slowdown with a large number of files. 2. Clustering? If you mean the fact that InnoDB stores the row data in the PRIMARY KEY index (= clustered index), that is also true for .ibd files. The only thing that changes is the file, not internal storage formats of tables. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables From: Chris Nolan ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-22 09:19:19 PST Hello Heikki!!! Multiple table spaces eh? Funky! Of course, as you've no doubt become aware, I am one of these annoying people that has many questions to ask the list the moment something like this comes along! Please feel free to ignore these questions though, as we're all hanging out for 4.1.1. 1. Are there any performance implications (either way) with the use of multiple table spaces? Are there any other implications that you think are worthy of note (other than the obviously easier backup options). 2. Is there any way that clustering can still happen with multiple table spaces enabled? Best regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SV: SV: GRANT problem
Use 'localhost' as the host unless you are specifying the grant to be for users on another machine. '%' stands for all machines except 'localhost'. Datatal AB - Gauffin, Jonas wrote: I get access denied (from mysql.exe and myodbc) when specifying no host or % as host in the grant statement. -Ursprungligt meddelande- Från: gerald_clark [mailto:[EMAIL PROTECTED] Skickat: den 24 oktober 2003 15:11 Till: Datatal AB - Gauffin, Jonas Kopia: [EMAIL PROTECTED] Ämne: Re: SV: GRANT problem What do you mean by ' doesn't work ' ? Have you tried connecting from another machine? Datatal AB - Gauffin, Jonas wrote: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; yes. no diffrence. Specifying '%' or no host doesn't work for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hi Heikki, As you know - I'm a transaction zealot :-) Is there a way to get a signal back to the client when a transaction has started? Or perhaps something like @@trancount in MSSQL? Is there a way to turn ON/OFF auto-commit? And, are metadata operations under transaction control? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: SV: SV: GRANT problem
Use 'localhost' as the host unless you are specifying the grant to be for users on another machine. '%' stands for all machines except 'localhost'. I know. And the is exactly what I want. Datatal AB - Gauffin, Jonas wrote: I get access denied (from mysql.exe and myodbc) when specifying no host or % as host in the grant statement. -Ursprungligt meddelande- Från: gerald_clark [mailto:[EMAIL PROTECTED] Skickat: den 24 oktober 2003 15:11 Till: Datatal AB - Gauffin, Jonas Kopia: [EMAIL PROTECTED] Ämne: Re: SV: GRANT problem What do you mean by ' doesn't work ' ? Have you tried connecting from another machine? Datatal AB - Gauffin, Jonas wrote: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; yes. no diffrence. Specifying '%' or no host doesn't work for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hello Heikki, thanks for your reply. It was just my curiosity, with the MyISAM concept it's good for the OS, because it'll cache only the tables most useds, and not all the tablespace file. And how to administer the multiple tablespace support? I'm worried in the space unused by some tables, example: - Multiple tablespace support active - Minium tablespace size is 500MB (in the my.cnf) - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and table2 have 600MB Then you'll have: - table1.ibd with 10kb or 500MB? - 2 x table2.ibd files with 500MB each or - Just one table2.ibd with 600MB or 1GB? Thanks again, Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:55 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Eduardo, - Alkuperäinen viesti - Lähettäjä: Eduardo D Piovesam [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Friday, October 24, 2003 6:17 PM Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hello Heikki, thanks for your reply. It was just my curiosity, with the MyISAM concept it's good for the OS, because it'll cache only the tables most useds, and not all the tablespace file. but InnoDB only caches the most used 16 kB pages in the tablespaces to the buffer pool. MyISAM does not cache .MYD file contents at all, but relies on the OS file cache for them. The MyISAM key_buffer caches .MYI contents. And how to administer the multiple tablespace support? I'm worried in the space unused by some tables, example: - Multiple tablespace support active - Minium tablespace size is 500MB (in the my.cnf) The minimum size for an .ibd file is 64 kB. It grows page by page to 1 MB, up to 32 MB in 1 MB chunks, and after that in 8 MB chunks. .ibd files are always 'auto-extending' tablespaces. You do not specify their size in my.cnf. - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and table2 have 600MB Then you'll have: - table1.ibd with 10kb or 500MB? - 2 x table2.ibd files with 500MB each or - Just one table2.ibd with 600MB or 1GB? Thanks again, Eduardo Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:55 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hi! I'm not the almighty Heikki, but I might be able to fill you in to some extent. Your first and third questions I cannot answer with certainty, but I'm pretty sure the answer is no. Regarding the first one, I'm not too sure as to why you'd need it. Looking at the output of SHOW VARIABLES might help though. As for the second question, AUTOCOMMIT is able to be set at any time without a problem. Additionally, issuing BEGIN or it's workalikes (see the MySQL manual or the reference manual at www.innodb.com) will act in the same way as having AUTOCOMMIT set to 0. Hope this helps! Regards, Chris Martijn Tonies wrote: Hi Heikki, As you know - I'm a transaction zealot :-) Is there a way to get a signal back to the client when a transaction has started? Or perhaps something like @@trancount in MSSQL? Is there a way to turn ON/OFF auto-commit? And, are metadata operations under transaction control? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Heikki, Thanks for clarifying, and as you see, I'm new to MySQL (came from Oracle). I'll read all the docs first (I'm doing it right now). ;) We're evaluating MySQL (InnoDB) on NetWare65, we'll move all our Oracle servers (also, on NetWare) to it, and then start testing. Currently we have only 2 problems : view + stored procedures, but we can do some workaround because we have only a few of them. Regards, Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:23 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, - Alkuperäinen viesti - Lähettäjä: Eduardo D Piovesam [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Friday, October 24, 2003 6:17 PM Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hello Heikki, thanks for your reply. It was just my curiosity, with the MyISAM concept it's good for the OS, because it'll cache only the tables most useds, and not all the tablespace file. but InnoDB only caches the most used 16 kB pages in the tablespaces to the buffer pool. MyISAM does not cache .MYD file contents at all, but relies on the OS file cache for them. The MyISAM key_buffer caches .MYI contents. And how to administer the multiple tablespace support? I'm worried in the space unused by some tables, example: - Multiple tablespace support active - Minium tablespace size is 500MB (in the my.cnf) The minimum size for an .ibd file is 64 kB. It grows page by page to 1 MB, up to 32 MB in 1 MB chunks, and after that in 8 MB chunks. .ibd files are always 'auto-extending' tablespaces. You do not specify their size in my.cnf. - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and table2 have 600MB Then you'll have: - table1.ibd with 10kb or 500MB? - 2 x table2.ibd files with 500MB each or - Just one table2.ibd with 600MB or 1GB? Thanks again, Eduardo Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:55 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hi Chris, I'm not the almighty Heikki, but I might be able to fill you in to some extent. :-) Your first and third questions I cannot answer with certainty, but I'm pretty sure the answer is no. Regarding the first one, I'm not too sure as to why you'd need it. Looking at the output of SHOW VARIABLES might help though. Yeah, I have been looking at SHOW VARIABLES - but it isn't exactly what I'm looking for. As for the second question, AUTOCOMMIT is able to be set at any time without a problem. Additionally, issuing BEGIN or it's workalikes (see the MySQL manual or the reference manual at www.innodb.com) will act in the same way as having AUTOCOMMIT set to 0. Ugh - read across that one... Woops :-) Thanks for that. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 subqueries!
Simon Gentile [EMAIL PROTECTED] wrote: in version 4.1 states it supports subqueries like SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2); it doesn't support 'IN' [Dissertation] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Worked fine for me: mysql select * from t2 where (1,2,3) in (select a,b,c from t1); +--+ | id | +--+ |2 | |3 | +--+ 2 rows in set (0.02 sec) Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MERGE tables error
* [EMAIL PROTECTED] [...] ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143) All table structures are identical. Maybe not..? :) Am I missing a basic error numbers page in the online docs? I've found a few, but nothing comprehensive, like a page where I can enter an error number and find the detailed message, probable cause, recommdned action, etc. The only thing that I have been able to find is: http://www.mysql.com/doc/en/Error-returns.html which essentially confirms that an error 1016 is a problem opening a file. I am using MySQL version 3.23.56-nt on Windows2000. Run the perror utility, located in the mysql\bin directory: C:\mysql\binperror 143 Error code 143: Unknown error 143 = Conflicting table definition between MERGE and mapped table -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Really slow query (compared with Visual FoxPro)
Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Héctor Villafuerte D. wrote: Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hi! One thing that you have to remember is that FoxPro will always index some fields, while MySQL will only ever index the primary key field automatically. Indexes can be on fields that are distinct or not distinct. The only difference that exists is the nature of the index, which is one of those details the RDBMS handles by itself. VFP has Rushmore, which does a bunch of funky things. At the moment (and the MySQL docs currently agree with me on this), MySQL only has one real weakness - it's optimiser. If you consider this fact (Oracle's optimiser is really it's greatest strength for example), the speed of MySQL is even greater an achievement. The great Monty himself names the optimiser as amongst the hardest things to get right for an RDBMS. It's not to say that the optimiser is bad, it's just to say that MySQL AB have quite a few things planned for improvement in that particular area. Regards, Chris Héctor Villafuerte D. wrote: Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow query (compared with Visual FoxPro)
Indexes are your friend. Perhaps your best friend (as far as databases go) --- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index). Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique). As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) -- If you're that new to db design, I would recommend going out and looking for some books/sites on the subject... There have been a few mentioned on this mailing list recently (Michael Kofler and Paul Dubois), http://www.databaseanswers.com/, and Database Design for Mere Mortals and finally Mike ([EMAIL PROTECTED]) mentioned the following online tutorials: Try one of these MySQL tutorials: http://www.mysql.com/doc/en/Tutorial.html http://www.analysisandsolutions.com/code/mybasic.htm http://www.devshed.com/Server_Side/MySQL http://www.sqlcourse.com/ http://www.w3schools.com/sql/default.asp http://www.juicystudio.com/tutorial/mysql/ http://www.justphukit.com/mysql/mysql-tutorials-1.php http://sqlzoo.net/ http://www.troobloo.com/tech/mysql.shtml http://perl.about.com/cs/mysql/index.htm http://www.anu.edu.au/web/authors/mysql/manual_Tutorial.html Reference: http://www.mysql.com/doc/en/ (the MySQL manual is quite good too) http://tiger.la.asu.edu/Quick_Ref/MySQL_QuickRef.pdf (MySQL Quick Reference Card) Related links http://dmoz.org/Computers/Software/Databases/MySQL/Tutorials/ -Original Message- From: Héctor Villafuerte D. [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 1:40 PM To: [EMAIL PROTECTED] Subject: Re: Really slow query (compared with Visual FoxPro) Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SUBSELECT QUESTION?
Hi, I have a table SalaryInfo as below Salary | Department | Level 50 | RD| Director 3 | Maintenance| Groundsman ... I want to know what level in each department makes the highest salary and how much that salary is? Something like: SELECT Salary, Level, Department FROM SalaryInfo WHERE Salary=Max(Salary) Would using MySQL 4.1 that has support for nested select help my case? Thanks in advance prashant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question.
I have a table (menus) with the following fields:
TABLE: MENUS
id int(11)
url varchar(100)
items varchar(35)
An example row:
ID URL ITEMS
158 programs/graduate 21,22,23,24,25,26,27,28,160
I want to select the ITEMS and then do another subselect that can uses
each ITEM as the ID in the subselect.
'select items from menus where id = 158' returns:
21,22,23,24,25,26,27,28,160
Each of these ITEMS is an id in the table also. I want to get each row
that corresponds to id = 21, 22, 23, 24, 25 ...
I know how to do this in php, but I need to do this on the command line.
Remember that ITEMS is a varchar type, so I'm not sure if there's a way
to do some kind of loop within this to select the items or create the
long query string with OR id = 21 OR id = 22 OR id = 23 ... which I
started to do:
select CONCAT('id = ', substring(items,1,LOCATE(',', items, 1)-1), ' OR
id = ', substring(items, 4,locate(',', items,2)-1)) AS STR from menus
where items != '' and id = 158;
but this can be pretty long since it only grabs the first two digits
from ITEMS!
The ITEMS always has NO spaces between commas and numbers and some
numbers may be single digit, two digits or even three in the row.
thanks in advance,
craig.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication redirector
hi Jeremy , thanks (again, tell me if it's to much=) for helping me. i've got to admit here that i'm reaching the maximum of my linux skills. i've used top,free,iostat and sar , without finding the answers i need. for instance , on the master (dual P3 1Ghz , 1G ram, running only mysql, with 353Mo of data): uptime : -- 18:46:44 up 123 days, 23:07, 1 user, load average: 21.20, 14.22, 13.93 free : totalusedfree shared buffers cached Mem:901156 890080 11076 05860 731076 -/+ buffers/cache: 153144 748012 Swap: 1951888 226356 1725532 top : -- 19:02:26 up 24 days, 17:54, 1 user, load average: 2.12, 3.19, 2.84 418 processes: 416 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 58.1% user, 7.0% system, 0.0% nice, 34.9% idle Mem:900084K total, 884928K used,15156K free,70076K buffers Swap: 979956K total, 243256K used, 736700K free, 341272K cached mytop (thanks again =) : -- MySQL on localhost (3.23.49-log) up 29+10:33:22 [18:48:43] Queries Total: 13,593,786 Avg/Sec: 5.34 Now/Sec: 8.54 Slow: 189,308 Threads Total: 356 Active: 27Cached: 0 Key Efficiency: 99.93% Bytes in: 1,328,945,210 Bytes out: 940,236,299 my.cnf extract: -- set-variable= thread_stack=128K set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=128 set-variable= sort_buffer=1M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=500 thanks to top , i can presume that the cpu's are ok :34.9% idle (there's a bug in the debian procps , top show only one proc , but cat /proc/cpuinfo show me the 2 cpu , so they are both effectively used by the system) i'm asking myself : why is the kernel using so much cache (730M). shouldn't he (AFAIK) gives this space to mysql instead of swapping (226M) ? then i think maybe he uses the cache to manage the swap space. but heh , 730M for cache , and only 153 for the applications ! where am i wrong? (let's stay humble : i'm wrong , not the linux kernel :) do you think there is some simple things i could tune to improve the system performance ? (the slave is now ok , thanks to the horrible regexps system i use to redirect part of the queries to the master =) bye, Ben. * Benjamin KRIEF * Directeur Technique * IGUANE Studio Tel:01.56.55.54.20 * 5-7-9 passage des Cloys Fax:01.56.55.54.24* 75018 PARIS Gsm:06.12.56.50.41 * mailto:[EMAIL PROTECTED] * On Fri, Oct 24, 2003 at 04:00:07AM +0200, Benjamin KRIEF wrote: Is your bottleneck CPU, I/O, or lock contention? Is your slave running on worse hardware than your master? my bottleneck is CPU , definitely. the load can go up to 35 in peak hours. no swapping occurs , my key efficiency is at 99,89 . The load average often has little relationship to the actual CPU utilization. Is your CPU at 100% most of the time? Or are your procs blocked on disk I/O. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,693,767 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1 in production
Hey all, are people using mysql 4.1.0 of 4.1.1 on production servers yet, or should we wait for a more stable release? i know this is somewhat subjective to the use. I have a major upgrade to my database system coming soon, and i want to coordinate it with my server upgrade, so im trying to figure out if it is wise to upgrade mysql yet. thanks much, sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: question.
You probably want to restructure your tables (if that's an option)
if it's a heirachal menu system (one parent only), then you'll need to put a
parent_id column, and then map each child to the parent.
then once you have your parent menu (158), you select * from menus where parent_id =
158
If it's a cross-menu system (multiple parents per item), you'll need another table,
say menu_relations (source_id, item_id) and put a row in there for each relationship.
then you select * from menus, menu_relations where id = item_id and source_id = 158)
Dunno if this helps
-Original Message-
From: Craig Harding [mailto:[EMAIL PROTECTED]
Sent: Friday, October 24, 2003 12:36 PM
To: [EMAIL PROTECTED]
Subject: question.
I have a table (menus) with the following fields:
TABLE: MENUS
idint(11)
url varchar(100)
items varchar(35)
An example row:
IDURL ITEMS
158 programs/graduate 21,22,23,24,25,26,27,28,160
I want to select the ITEMS and then do another subselect that
can uses
each ITEM as the ID in the subselect.
'select items from menus where id = 158' returns:
21,22,23,24,25,26,27,28,160
Each of these ITEMS is an id in the table also. I want to get
each row
that corresponds to id = 21, 22, 23, 24, 25 ...
I know how to do this in php, but I need to do this on the
command line.
Remember that ITEMS is a varchar type, so I'm not sure if
there's a way
to do some kind of loop within this to select the items or create the
long query string with OR id = 21 OR id = 22 OR id = 23 ... which I
started to do:
select CONCAT('id = ', substring(items,1,LOCATE(',', items,
1)-1), ' OR
id = ', substring(items, 4,locate(',', items,2)-1)) AS STR from menus
where items != '' and id = 158;
but this can be pretty long since it only grabs the first two digits
from ITEMS!
The ITEMS always has NO spaces between commas and numbers and some
numbers may be single digit, two digits or even three in the row.
thanks in advance,
craig.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help obtaining DBI binaries
www.cpan.org or www.rpmfind.net comes in a package. Most perl distributions have this installed by default. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Daniel [mailto:[EMAIL PROTECTED] --Sent: Thursday, October 23, 2003 7:20 PM --To: '[EMAIL PROTECTED]' --Subject: Help obtaining DBI binaries -- --Please help, where do I download DBI module binaries --thanks --Daniel -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: XML support for MySQL
There is a project that was started to do this though. Do a google search, on master thesis XML and MYSQL. Maybe they have some code that can be contributed or you can use for a private branch. In the mean time it's easy to generate XML on a simple DTD in side your code. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Friday, October 24, 2003 1:17 AM --To: Prasad Budim Ram --Cc: [EMAIL PROTECTED] --Subject: Re: XML support for MySQL -- --On Mon, Oct 13, 2003 at 11:45:33PM -0600, Prasad Budim Ram wrote: -- Is it possible to direct xml support for queryiing a SQL statement and -- get the result in also in xml? -- --Nope. --Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! --[EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- --MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,533,760,097 queries --(440/sec. avg) -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auditing MySQL (EAI)
I am working on a Veritas backup solution where the data can exist on a remote file system and the snapshot exist elsewhere. This could be used in theory to attain your goal, with some additional code. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Stephan Stapel [mailto:[EMAIL PROTECTED] --Sent: Friday, October 24, 2003 2:11 AM --To: [EMAIL PROTECTED] --Subject: Auditing MySQL (EAI) -- --Dear list, -- --I currently have the task to write an adapter for MySQL (for SapDB as --well --btw) that should track changes on certain tables. --I know that this question has been asked several times before but --unfortunately didn't find no suitable answer: --Is it possible to monitor/ audit changes in certain tables (create, --update, --delete) with MySQL and e.g. raise external programs? --If it's not possible now, does anyone know of plans to integrate such a --feature in one of the future versions of MySQL? -- --Kind regards, -- --Stephan -- --NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien... --Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService -- --Jetzt kostenlos anmelden unter http://www.gmx.net -- --+++ GMX - die erste Adresse für Mail, Message, More! +++ -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Space efficiency for values repeated across rows
I am working on a mysql-based search facility. In order to search for connected phrases, it would be very convenient to have a subtable structure to express a set of rows in which only one small column changes. If I didn't have to worry about space, I might have a table like this: create table windex ( wordnum int unsigned not null, docnum int unsigned not null, pos smallint unsigned not null, [various keys]); Then to search for a phrase like pork and beans, I would have a query like: select docnum from windex as a,windex as b,windex as c where a.wordnum = 7 and b.wordnum = 10 and c.wordnum = 20 and a.docnum = b.docnum and b.docnum = c.docnum and c.pos = a.pos+2 and c.pos = b.pos+1 (This is assuming that pork = 7, and = 10, and beans = 20.) Note that this select statement illustrates a nuisance of the syntax for multiple joins. AFAIK, I have to say x = y and y = z, when I would like to say x = y = z. Or if it is not possible to interpret such a phrase Python-style, I would like to have a function like equal(x,y,z). Anyway, the syntax of this query is not the main point. The real problem is that the above table requires ten bytes for each occurrence of each word in each document, plus significant extra B-tree structure. Not only would it require more disk space than I will probably have, it would also make searches highly disk-intensive. So instead I plan to have a table like this: create table windex ( wordnum int unsigned not null, docnum int unsigned not null, positions blob not null, [various keys]); Now I can encode the positions of each word in each document as two bytes per occurrence. The drawback is that to search for pork and beans, I have to retrieve all occurrences of pork AND and AND beans, and then inspect each document for a position match. This is not ideal, but it looks like the best option at the moment. The ideal solution would be an intelligent record structure that omits wordnum and docnum when they repeat. I realize that this looks a lot like a MyISAM compressed table. But I can't use that because my search index will change incrementally every day. I also see that MySQL has its own full-text search function. Well, my project has some special requirements in regard to both search semantics and scalability. I don't think that I could make do with just a simple wrapper for MySQL's full-text search. Maybe I could exploit the full-text search function for the narrow problem of finding a connected phrase. It would be nice to know the data structures and algorithms for the full-text search facility. -- /\ Greg Kuperberg (UC Davis) / \ \ / Visit the Math ArXiv Front at http://front.math.ucdavis.edu/ \/ * All the math that's fit to e-print * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Support Contract
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi guys , I'm sorry for this very OT post , but i guess that someone in the list can help me . I've made contact with a sales representant of mysql to purchase a mysql support contract , but the communication seems to be a little slow ( my company is in Chile - South America ). In fact , I already know what kind of support i want to pay, but , i still can't contact him to finish the transaction ( this is a kind of non OT thing in a SQL list :-) ) There's any chance that someone could give me a name or a mail where i can send my info and finish this ? Thanks in Advance ( And sorry for my poor english ) Saludos / Regards, Alvaro Avello. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org iD8DBQE/mWRgR9NZaw5tbc0RAoc8AJ992nQRYLnVpnsuQBi7h+EGKE7pjQCfXFtG nxo+nX+l55JREqcLaPl9Lbk= =Zyd8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUBSELECT QUESTION?
First of all, you query is not correct and this is not a subselect query. you can try this: SELECT Salary, Level, Department FROM SalaryInfo ORDER BY Salary DESC LIMIT 1; second of all, for subselect MySQL 4.1 can help you. Mojtaba - Original Message - From: Prashant Pai [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:00 PM Subject: SUBSELECT QUESTION? Hi, I have a table SalaryInfo as below Salary | Department | Level 50 | RD| Director 3 | Maintenance| Groundsman ... I want to know what level in each department makes the highest salary and how much that salary is? Something like: SELECT Salary, Level, Department FROM SalaryInfo WHERE Salary=Max(Salary) Would using MySQL 4.1 that has support for nested select help my case? Thanks in advance prashant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case statement
I'm trying to do something like the following: SELECT CASE status WHEN IN ( 'BR', 'VQ', 'RQ', 'NG' ) THEN 'binding' WHEN IN ( 'NA', 'NG', 'RA' ) THEN 'approving' END as action FROM master_info WHERE (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR ( status IN ( 'NA', 'NG', 'RA' ))); But this query is obviously going to fail. But there _has_ to be a more elegant way than doing: WHEN 'BR' THEN 'binding' WHEN 'VQ' THEN 'binding' ... Is there? If so, I can't figure out what it would be. Any suggestions? thnx, Chris -mysql,query,blah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB errors
I've searched the archives and was unable to find anything that seemed pertinent. Earlier today I had to stop and start mysql. When I issued mysqladmin shutdown, however, the process did not die cleanly (reason unknown), and I ended up having to kill -9 mysqld and mysqld_safe. When the DB came back up, I now have the following in my log: 031024 11:06:04 mysqld started 031024 11:06:05 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 697473956 InnoDB: Doing recovery: scanned up to log sequence number 0 697473956 031024 11:06:06 InnoDB: Flushing modified pages from the buffer pool... 031024 11:06:06 InnoDB: Started 031024 11:06:06 Found an entry in the 'db' table with empty database name; Skipped 031024 11:06:06 Found an entry in the 'db' table with empty database name; Skipped ... 031024 11:07:53 mysqld started 031024 11:07:54 InnoDB: Started 031024 11:07:54 Found an entry in the 'db' table with empty database name; Skipped 031024 11:07:54 Found an entry in the 'db' table with empty database name; Skipped as you can see, on the second restart, I did not get warnings about having to recover the tables, but I still get the 'empty database name' errors. How can I determine what the cause of this error is an get it fixed? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reproducible error 17
Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI' pointing at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query 'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not NULL default 0'. Default database: 'ff_recent' | 0| 346296822 | 369493795 Perror says Error code 17: File exists I have 2 alters submitted on the master in sequential order. On the slave it executes in the same order but produces error 17. Somehow the tempfile already exists, but when I go to the datadir this temp file is not displayed in the directory. Performing a slave stop slave start does not work. Restarting the mysql server on the slave is the only method I found to recover from the error. Version of mySQL: 4.0.15 Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14 13:50:35 PDT 2002 i686 unknown *PATCHED* - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
Rekall V2.1.0 stable has be released
Hi We are pleased to announce the release of Rekall V2.1.0 stable for Linux and Windows Please note, the stable release of Rekall V2.1.0 is only available from either http://www.totalrekall.co.uk and http://www.rygannon.com What is Rekall? Rekall is a programmable (using Python), database independant, GUI database client. Rekall, includes Form, Report and Query Designers, a Python Script editor and debugger, Macros, Wizards, re-useable components, and lots more. Included with the standard edition are drivers for MySQL, PostgreSQL and xBase. Drivers for DB2, Oracle, and ODBC are available separately Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Dan Greene wrote: Indexes are your friend. Perhaps your best friend (as far as databases go) --- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index). Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique). As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) -- Ok, I've found the optimization chapter in the manual and I'm still reading it. Here's what I've done so far: mysql alter table traf_oper add index (tel), add index (telefb); mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; ++-+---+--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | ALL | NULL | NULL |NULL | NULL | 5014313 | Using temporary; Using filesort | ++-+---+--+---+--+-+--+-+-+ 1 row in set (0.50 sec) mysql describe traf_oper; ++--+---+--+-+-+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+-+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+-+---+ 10 rows in set (0.00 sec) So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow query (compared with Visual FoxPro)
As you are selecting all records (no where clause), it will scan the whole table every time, I believe... does anyone know if he added the other columns to his index, or had 4 seperate indexes (one per column) would they be used in this operation? Ok, I've found the optimization chapter in the manual and I'm still reading it. Here's what I've done so far: mysql alter table traf_oper add index (tel), add index (telefb); mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; ++-+---+--+---+--+ -+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+ -+--+-+-+ | 1 | SIMPLE | traf_oper | ALL | NULL | NULL |NULL | NULL | 5014313 | Using temporary; Using filesort | ++-+---+--+---+--+ -+--+-+-+ 1 row in set (0.50 sec) mysql describe traf_oper; ++--+---+--+-+ -+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+ -+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+ -+---+ 10 rows in set (0.00 sec) So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hi Hector, Umm, it looks like you're simply doing a SELECT in the first query and *populating a new table* in the second. Of course inserting 3.3 million rows is going to take extra time! How can you even compare the 2 when they're doing different things? Matt - Original Message - From: Héctor Villafuerte D. Sent: Friday, October 24, 2003 12:10 PM Subject: Really slow query (compared with Visual FoxPro) Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case statement
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
- Original Message -
From: Chris Boget [EMAIL PROTECTED]
To: MySQL [EMAIL PROTECTED]
Sent: Friday, October 24, 2003 2:19 PM
Subject: Case statement
I'm trying to do something like the following:
SELECT
CASE status
WHEN
IN ( 'BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
WHEN
IN ( 'NA', 'NG', 'RA' )
THEN
'approving'
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
But this query is obviously going to fail. But there _has_ to be
a more elegant way than doing:
WHEN
'BR'
THEN
'binding'
WHEN
'VQ'
THEN
'binding'
...
Is there? If so, I can't figure out what it would be. Any suggestions?
thnx,
Chris
-mysql,query,blah
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hector, I migrated from Visual FoxPro too. VFP has different style to work with a table. MySQL gives you some advantage which you don't have it in VFP. for example, try to connect to the work server from home and run this query. it will take 5 days to finish it, but still MySQL takes 3 min.s :) believe me because I did this test and our company decided to stop using VFP and rewrite everything in VB and MySQL. BUT my experience: try to change the logic of your report not to retrieve large number of records. user LIMIT to create the reports page by page. this is the best and even better for the user. good luck - Original Message - From: Matt W [EMAIL PROTECTED] To: Héctor Villafuerte D. [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 24, 2003 3:00 PM Subject: Re: Really slow query (compared with Visual FoxPro) Hi Hector, Umm, it looks like you're simply doing a SELECT in the first query and *populating a new table* in the second. Of course inserting 3.3 million rows is going to take extra time! How can you even compare the 2 when they're doing different things? Matt - Original Message - From: Héctor Villafuerte D. Sent: Friday, October 24, 2003 12:10 PM Subject: Really slow query (compared with Visual FoxPro) Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: reproducible error 17
*** 1. row *** Master_Host: 10.2.12.224 Master_User: rep Master_Port: 3306 Connect_retry: 60 Master_Log_File: ef224-bin.020 Read_Master_Log_Pos: 409223612 Relay_Log_File: ef242-relay-bin.016 Relay_Log_Pos: 330063332 Relay_Master_Log_File: ef224-bin.020 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 25 Last_error: Error 'Can't create symlink './ff_recent/#sql-17f7_c.MYI' pointing at '/var/lib/mysql/ff_recent/#sql-17f7_c.MYI' (Error 17)' on query 'ALTER TABLE recentmembers_20 add have_children tinyint unsigned not NULL default 0'. Default database: 'ff_recent' Skip_counter: 0 Exec_master_log_pos: 348632736 Relay_log_space: 390689457 - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] --Sent: Friday, October 24, 2003 11:27 AM --To: [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: reproducible error 17 -- --Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI' pointing --at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query --'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not NULL --default 0'. Default database: 'ff_recent' | 0| 346296822 --| 369493795 -- -- --Perror says --Error code 17: File exists -- --I have 2 alters submitted on the master in sequential order. On the --slave it executes in the same order but produces error 17. Somehow the --tempfile already exists, but when I go to the datadir this temp file is --not displayed in the directory. -- --Performing a slave stop slave start does not work. --Restarting the mysql server on the slave is the only method I found to --recover from the error. -- -- --Version of mySQL: 4.0.15 --Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14 --13:50:35 PDT 2002 i686 unknown --*PATCHED* -- -- --- Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Q: MERGE table limitations
Does anyone know of any limitations on the number of underlying tables in a MERGE table? Seems to be a topic of scant coverage in the books and online docs. ANY help or ideas would be appreciated. /Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case statement
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
Sadly, that didn't work:
Your MySQL connection id is 208517 to server version: 4.0.14-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql SELECT
- CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
- THEN
- 'binding'
- CASE WHEN status IN('NA', 'NG', 'RA')
- END as action
- FROM master_info WHERE
- (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
- ( status IN ( 'NA', 'NG', 'RA' )));
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CASE
WHEN status IN('NA', 'NG', 'RA') END as action FROM master
Do you have something similar that works? What does it look like?
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MERGE table limitations
OS/FileSystem INODE limitation. How many files can fit into a directory? 64K on Redhat if you modify file-max - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Friday, October 24, 2003 12:26 PM --To: [EMAIL PROTECTED] --Subject: Q: MERGE table limitations -- --Does anyone know of any limitations on the number of underlying tables in --a MERGE table? Seems to be a topic of scant coverage in the books and --online docs. -- --ANY help or ideas would be appreciated. -- --/Tony -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case statement
I didn't have your table so I tried to write the same command by one of my
tables. my query which worked, is:
SELECT CASE WHEN left( workorder, 1 )
IN ( 'C', 'T' )
THEN 'group1'
WHEN left( workorder, 1 )
IN ( '3', 'R' )
THEN 'gorup2'
ELSE
'group3'
END FROM workfile;
- Original Message -
From: Chris Boget [EMAIL PROTECTED]
To: Mojtaba Faridzad [EMAIL PROTECTED]; MySQL
[EMAIL PROTECTED]
Sent: Friday, October 24, 2003 3:34 PM
Subject: Re: Case statement
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
Sadly, that didn't work:
Your MySQL connection id is 208517 to server version: 4.0.14-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql SELECT
- CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
- THEN
- 'binding'
- CASE WHEN status IN('NA', 'NG', 'RA')
- END as action
- FROM master_info WHERE
- (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
- ( status IN ( 'NA', 'NG', 'RA' )));
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'CASE
WHEN status IN('NA', 'NG', 'RA') END as action FROM master
Do you have something similar that works? What does it look like?
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case statement [SOLVED]
I didn't have your table so I tried to write the same command by one of my
tables. my query which worked, is:
SELECT CASE WHEN left( workorder, 1 )
IN ( 'C', 'T' )
THEN 'group1'
WHEN left( workorder, 1 )
IN ( '3', 'R' )
THEN 'gorup2'
ELSE
'group3'
END FROM workfile;
which is different from what you wrote in your previous email:
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
The difference being the CASE preceding the second WHEN in your
first suggestion.
The query that I got to work is as follows:
SELECT
CASE
WHEN
status IN( 'BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
WHEN
status IN( 'NA', 'NG', 'RA' )
THEN
'approving'
END as action, status
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
Thank you very much for your help, Mojtaba!
Chris
-mysql,query,blah
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case statement
oops, I found what was my mistake. I copied and paste a line to write the
command for you but I forgot to remove CASE :) so write your query like
this:
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
- Original Message -
From: Mojtaba Faridzad [EMAIL PROTECTED]
To: Chris Boget [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Sent: Friday, October 24, 2003 3:43 PM
Subject: Re: Case statement
I didn't have your table so I tried to write the same command by one of my
tables. my query which worked, is:
SELECT CASE WHEN left( workorder, 1 )
IN ( 'C', 'T' )
THEN 'group1'
WHEN left( workorder, 1 )
IN ( '3', 'R' )
THEN 'gorup2'
ELSE
'group3'
END FROM workfile;
- Original Message -
From: Chris Boget [EMAIL PROTECTED]
To: Mojtaba Faridzad [EMAIL PROTECTED]; MySQL
[EMAIL PROTECTED]
Sent: Friday, October 24, 2003 3:34 PM
Subject: Re: Case statement
SELECT
CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
THEN
'binding'
CASE WHEN status IN('NA', 'NG', 'RA')
END as action
FROM master_info WHERE
(( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
( status IN ( 'NA', 'NG', 'RA' )));
Sadly, that didn't work:
Your MySQL connection id is 208517 to server version: 4.0.14-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql SELECT
- CASE WHEN status IN('BR', 'VQ', 'RQ', 'NG' )
- THEN
- 'binding'
- CASE WHEN status IN('NA', 'NG', 'RA')
- END as action
- FROM master_info WHERE
- (( status IN ( 'BR', 'VQ', 'RQ', 'NG' )) OR
- ( status IN ( 'NA', 'NG', 'RA' )));
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'CASE
WHEN status IN('NA', 'NG', 'RA') END as action FROM master
Do you have something similar that works? What does it look like?
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory consumed by mysql to process a query
Hi: I am current measuring the performance of mysql. Is there a way of getting memory consumed by mysql to process a query? Any suggestion is highly aprreciated. Best Regards Chuang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: XML support for MySQL
Jeremy, Prasad, On Mon, Oct 13, 2003 at 11:45:33PM -0600, Prasad Budim Ram wrote: Is it possible to direct xml support for queryiing a SQL statement and get the result in also in xml? Nope. At least, you can start the MySQL client program with an option so that it outputs XML: shell mysql --xml mysql SHOW TABLES FROM test; ?xml version=1.0? resultset statement=SHOW TABLES FROM test row Tables_in_testbooltest/Tables_in_test /row row Tables_in_testdaten/Tables_in_test /row ... Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enquiry on rhe use of MySQL
Hi, I've just installed MySQL on my PC but do not know which program to access in order to use it. Does anyone knows ? Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting from two different severs
Hi Mike, Simple; you don't. :-) Someone please correct me if I'm wrong. BTW, this isn't a Windows specific question, so I'm sending it to the General list too. Regards, Matt - Original Message - From: Mike Karplus Sent: Friday, October 24, 2003 5:04 PM Subject: selecting from two different severs How do I join two tables in two different servers ? ( btw, in different locations, thousands of km away...) Thank you in advance, and nice weekend, Mike Karplus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update from other table
Does MySQL support update from other table? I run this query and get error: UPDATE TableA SET Division=TableB.Division FROM TableB WHERE TableA.Code=TableB.Code Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
