Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Hi Mike, I've asked this question a year ago on the Borland forums but didn't get any replies. Do you know of any websites (non-programming related) that are using Interbase/Firebird for their primary webserver database? I don't mean programmer hangouts like www.mers.com or www.tamaracka.com etc., but Fortune 1000 companies who are using IB/FB on the web to serve up web pages? It would be interesting to get a list of who's using it online. TIA I have no idea :-) ... With a chat I recently had with someone from Opera Software, he told me that they recently migrated their entire my.opera.com to Firebird, but that's the only one I can come up with right now. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
Hi, On Tue, Dec 16, 2003 at 10:23:05PM +1100, Chris Nolan wrote : How heavy is your usage of TEMPORARY TABLES? I don't use them much myself, but I'm sure that the others on the list will have something to say in that regard. Here are the relevant numbers: Created_tmp_disk_tables| 21911 Created_tmp_tables | 329778 This is from an uptime of about 40 hours which makes the Created_tmp_disk_tables around 6% during this time. To get a better look at MySQL's usage of memory, you could try looking at the output of SHOW STATUS . I don't want to look silly, but actually having the show status output in front of me doesn't tell me much about the memory usage; at least I can't find it ;) There are things like Qcache_lowmem_prunes Qcache_free_memory but this are the only ones related to memory. Can you advise me where to look at exactly? - Markus Ps: I'm using version 4.0.14 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
On Tue, Dec 16, 2003 at 10:38:14AM -0600, Dan Nelson wrote : In the last episode (Dec 16), Markus Fischer said: I'm investigating a performance problem with mysql server set up. The server is running linux with 1GB ram. I'ld like to tune the configuration of the server to use as much RAM as possible without swapping to the disc because of the big slow down. [mysqld] set-variable= key_buffer=16M Way too low; this says only cache 16MB of index data. Try 256MB. set-variable= max_allowed_packet=200M Probably way too high, but doesn't hurt since it won't actually allocate that much memory unless you have a 200MB field someplace. Thanks for pointing this out. I've compensated max_allowed_packet and increased the key_buffer. I'm also not sure whether the database is swapping temporary tables to the disc or not; is there a way to verify if this is happening? show status like 'created_tmp%'; Raising sort_buffer_size and join_buffer_size may also help if your queries pull a lot of records. From what I read from the manual, sort_buffer_size is only used for the isamchk tools, isn't it? I've adapted join_buffer_size though; thanks. - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Run Time Error-'3027'-Cannot Update Database or Object is Read Only
Hello All, I am Swetha.I am facing the above mentioned problem while adding/updating a recordset in the my_dao table.I tried a lot i am not getting the reason for this.So please let me know the reason the solution for this problem. With Regards, Swetha. - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
please give me help in important decision!
hello, i'm working on a java-jsp-page with mysql-connection. now i've created a test-system with myisam-tables where the tables are just read. my plans for next weeks are, to make the owner of the site capable of entering/changing his data by his own - web-based and multi-user-capable. as this is - for me - a big jump, i want to ensure myself that i go best ways right from the beginning. therefore i hope to get some useful hints from you. my questions are especially: * should i use innodb-tables for the entering-system (because of transaction-safty-reasons...)? * if yes: how can i change them for the real-system (the homepage), that uses packed myisam-tables (because there is no writing, only reading from db!). * what are the best ways to get the data from entering-system to real-system? do i have to shut-down the mysql-server each time the data is updated? * what else must i think of? thank you very much for your hints! greetings hans horwath -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please give me help in important decision!
Hi Hans, i'm working on a java-jsp-page with mysql-connection. now i've created a test-system with myisam-tables where the tables are just read. my plans for next weeks are, to make the owner of the site capable of entering/changing his data by his own - web-based and multi-user-capable. as this is - for me - a big jump, i want to ensure myself that i go best ways right from the beginning. therefore i hope to get some useful hints from you. my questions are especially: * should i use innodb-tables for the entering-system (because of transaction-safty-reasons...)? If you want to avoid locks and if you want to update multiple tables in a single logical transaction: YES. * if yes: how can i change them for the real-system (the homepage), that uses packed myisam-tables (because there is no writing, only reading from db!). Change what? Your current tables to InnoDB? * what are the best ways to get the data from entering-system to real-system? do i have to shut-down the mysql-server each time the data is updated? That depends - - Can you update directly in the real system? Perhaps by replication? * what else must i think of? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Run Time Error-'3027'-Cannot Update Database or Object is Read Only
hi swetha, Pl. check the privilege tables for the user access. -Aman. Swetha S.N. wrote: Hello All, I am Swetha.I am facing the above mentioned problem while adding/updating a recordset in the my_dao table.I tried a lot i am not getting the reason for this.So please let me know the reason the solution for this problem. With Regards, Swetha. - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL with SSL enabled
rmck [EMAIL PROTECTED] wrote: I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL enabled. Does this feature come enabled with the binary download? Or do I need to download the source and compile it? Official binaries are compiled without SSL support. Any good links ot setting up MySQL with SSL enabled? Check MySQL manual: http://www.mysql.com/doc/en/Secure_connections.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: Fw: Bug in 4.1.1 Internationalization on Windows2000 ?
Juri Shimon [EMAIL PROTECTED] wrote: Thanks for reply! 1. mysql -u root -e create database t; show create database t; drop database t 2. mysql -u root -e use_mysql; create database t; show create database t; drop database t 1.| t| CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET cp1251 */ 2.| t| CREATE DATABASE `t` /*!40100 DEFAULT CHARACTER SET latin1 */ It's not a bug. character_set_database indicates the current database character set. May be, it wasn't clear. I say: when a current database (result of use ...) in latin1, then create database ... create database in latin1. But default mysql character set is cp1251! Database, IMHO, must be created in DAFAULT charset. And, IMHO, this is a bug 8) Yes. It's a bug. Thanks for report. Another things: 1. mysql -u root -e select SUBSTRING_INDEX(USER(),'@',1) ERROR 1266 at line 1: Illegal mix of collations (utf8_general_ci,IMPLICIT) and ( cp1251_general_ci,COERCIBLE) for operation 'substr_index' Function USER() has utf8 character set, but '@' has server character set (cp1251). You can use: SELECT SUBSTRING_INDEX(CONVERT(USER() USING cp1251),'@',1); or SELECT SUBSTRING_INDEX(USER(),_utf8'@',1); 2. when I create enum fields in cp1251, then show columns from ... return a '','',... while all checks, inserts and so work ok. I don't know, 2.1. it's common ciryllic troubles (console in cp866, gui in cp1251) 2.2. it's my local troubles (english win 2 with ciryllic charset) 2.3 it's mysql troubles (system charset utf8, mysql charset cp1251). NB. in table.frm I see all my enums in selected charset. I think, there is 'phantom' recharacting utf8-cp1251. Seems it's related to the http://bugs.mysql.com/bug.php?id=2077 -- 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: please give me help in important decision!
Hi Hans, Please reply to the MySQL list instead of personally. hi martijn, thank you very much for your help. my replies are further down... * if yes: how can i change them for the real-system (the homepage), that uses packed myisam-tables (because there is no writing, only reading from db!). Change what? Your current tables to InnoDB? no, the other way round: because i use the packed myisam-tables with the homepage, and so i think i have to convert the innodb-tables, in which the acutal data is worked in (added, changed, deleted), at last - for usage with the homepage - to the mentioned packed-myisam-tables. I'm pretty sure you can simply extract a DDL script via some MySQL tool and change all your table-type clauses to InnoDB. Perhaps by replication? well i see, i have to learn a lot, but i want to: where shall i look for information to this replication? The MySQL manuals is a good start :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sporadic myisam table corruption.
Hi, either this should be properly debugged (if the problem persists with 4.0.17) or it is a hardware glitch. MyIsam tables have proven quite stable anch changing to InnoDB might be looking for trouble (if hardware is the culprit). But it's true that InnoDB could be much more efficient in your environment. Thomas Spahni On Fri, 12 Dec 2003, Victor Medina wrote: Change myisam format to something else, like InoDB or BDB Best Regards! On Fri, 2003-12-12 at 15:05, James E Hicks III wrote: I'm having sporadic myisam table corruption. This table is constantly being added to, updated, and deleted from. PHPMyAdmin reports that The table is in use when I try to access this table after corruption. After I perform this step in the correct directory everything goes back to normal. myisamchk --recover troubled_table After reading the mysql manual page I changed all the VARCHAR fields to CHAR fields, hoping my problem would dissapear. This change has had no effect on my problem. The only things in the error log are start ups and shutdowns performed nightly. Here's the table definition. CREATE TABLE troubled_table ( office smallint(4) unsigned NOT NULL default '0', ticket_id char(30) NOT NULL default '', item_quantity mediumint(8) unsigned NOT NULL default '0', from_face char(30) NOT NULL default '', from_down decimal(6,1) unsigned NOT NULL default '0.0', from_up decimal(6,1) unsigned NOT NULL default '0.0', from_depth decimal(6,1) unsigned NOT NULL default '0.0', to_face char(30) NOT NULL default '', to_down decimal(6,1) unsigned NOT NULL default '0.0', to_up decimal(6,1) unsigned NOT NULL default '0.0', to_depth decimal(6,1) unsigned NOT NULL default '0.0', associated_document char(30) NOT NULL default '', PRIMARY KEY (office,ticket_id), KEY move_id (ticket_id) ) TYPE=MyISAM COMMENT='Material Move Orders'; mysql --version says: mysql Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686) What can I try now? James Hicks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show processlist
Hi, show processlist displays processes with state statistics, what does this status means. Documentation doesn't say much A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sporadic myisam table corruption.
Hi all! I also agree with you, it might be a hardware problem, but i also must note, that in a environment with very often writes to the db, myisam tables are not always the best choice, that's why i suggested a change to InnoDB tables, a problem with sql or logic is more likely to happen that a hardware glitch Just my two cents here :) On Thu, 2003-12-18 at 07:51, Thomas Spahni wrote: Hi, either this should be properly debugged (if the problem persists with 4.0.17) or it is a hardware glitch. MyIsam tables have proven quite stable anch changing to InnoDB might be looking for trouble (if hardware is the culprit). But it's true that InnoDB could be much more efficient in your environment. Thomas Spahni On Fri, 12 Dec 2003, Victor Medina wrote: Change myisam format to something else, like InoDB or BDB Best Regards! On Fri, 2003-12-12 at 15:05, James E Hicks III wrote: I'm having sporadic myisam table corruption. This table is constantly being added to, updated, and deleted from. PHPMyAdmin reports that The table is in use when I try to access this table after corruption. After I perform this step in the correct directory everything goes back to normal. myisamchk --recover troubled_table After reading the mysql manual page I changed all the VARCHAR fields to CHAR fields, hoping my problem would dissapear. This change has had no effect on my problem. The only things in the error log are start ups and shutdowns performed nightly. Here's the table definition. CREATE TABLE troubled_table ( office smallint(4) unsigned NOT NULL default '0', ticket_id char(30) NOT NULL default '', item_quantity mediumint(8) unsigned NOT NULL default '0', from_face char(30) NOT NULL default '', from_down decimal(6,1) unsigned NOT NULL default '0.0', from_up decimal(6,1) unsigned NOT NULL default '0.0', from_depth decimal(6,1) unsigned NOT NULL default '0.0', to_face char(30) NOT NULL default '', to_down decimal(6,1) unsigned NOT NULL default '0.0', to_up decimal(6,1) unsigned NOT NULL default '0.0', to_depth decimal(6,1) unsigned NOT NULL default '0.0', associated_document char(30) NOT NULL default '', PRIMARY KEY (office,ticket_id), KEY move_id (ticket_id) ) TYPE=MyISAM COMMENT='Material Move Orders'; mysql --version says: mysql Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686) What can I try now? James Hicks -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Run Time Error-'3027'-Cannot Update Database or Object is Read Only
Hello All, I am Swetha.I am facing the above mentioned problem while adding/updating a recordset in the my_dao table using DAO recordset.I tried a lot i am not getting the reason for this.So please let me know the reason the solution for this problem. With Regards, Swetha. - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Query help
I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID namecompany 12347771joe null 12347772marynull 43210001bob null Company Table ID name 1234Acme 4321Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help 2
Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID namecompany 12347771joe null 12347772marynull 43210001bob null Company Table ID name 1234Acme 4321Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How boolean full-text search finds matches?
Hi! On Dec 17, Matt W wrote: Hi, Just have a couple more full-text search inquiries here. :-) I'm not exactly clear on how matching rows are found when searching for 2 or more required words: '+word1 +word2'. I understand that it can't currently know which word occurs less, so that it can be searched first -- this optimization will come with 4.1's 2-level indexes. :-) I just want to know, when it finds a match for whichever word is tried first, how does it check if the other required word(s) are present in the same row? Say that word1 and word2 are each present in 100,000 rows. 1) Surely it doesn't check the 100,000 entries for word2 for EACH word1 match to see if they're in the same row, does it? No it does not :) 2) It *seems* the best way would be to do a lookup for (word2 + rowid for word1) and see if there's a match. Is this what's done? I'm not sure it's possible though with the way the index is structured... it is possible, but it is only sensible if word1 is much more rare than word1. This could be done with 2-level indexes :) 3) Or, and I'm thinking *maybe* this is how it's done from what I've heard, does it get all the matches for word1, then for word2, and then intersect them to find ones which are present in the same row? If so, how will the 2-level index optimization change things? Will it do #2? Yes to both questions, without the word then. First, one match is found for each word. Then read_next is called for the word with the lowest rowid, etc. The advantage is that matches are found and returned earlier - a user don't have to wait for the index scan to complete. Also LIMIT, if used, cuts off more work, that is LIMIT is more effective. But when one word is much more common than the second one, it is better to do #2, and it's what I'll probably do. Next question is... a few weeks ago I was doing some test searches like '+word1 +word2'. Actually, maybe I was only using 1 word, I can't remember, but I don't think it matters. Anyway, I happened to try changing the query to '+word1* +word2*' -- e.g. adding a wild-card to the end of the same word(s) -- and I was amazed at how much faster the query was! (And no, there's no query cache; and they were both run many times so the index was cached. :-)) Can't remember how much faster, but it wasn't insignificant. Then I tried adding a wild-card to the end of words in another search (the wild-card did not make more rows match as far as I know), but that made it a little slower (I'd expect that, if anything). Is there any explanation for why adding wild-cards would make a search faster? Yes :) Looking on the description above how +word1 +word2 works, you can see that it relies on the fact that key entries for each word are sorted by rowid. But it is how MyISAM indexes work, they are always ordered by all keyparts, and rowid is always the last keypart, so fulltext index is always ORDER BY word, rowid. But when the search is done for the word prefix only, this order breaks, the index is, of course, not ORDER BY LEFT(word,5), rowid. Consider the index aa, 5 aaabbb, 10 aaaccc, 3 The workaround was to exclude prefixes from index scan whenever possible (that is in +word +pref1* +pref2* ... prefixes are not looked up in the index at all, in +pref1* +pref2* ... only the first prefix is looked up in the index) and to add a row scan (in the LIKE sense and as you described in #1), to remove half-matched rows. Thus it is sometimes slower - as more row data are read from the disk, and sometimes faster - as there are less index scans performed. Obvious optimization is to choose the most rare prefix for the scan, not the first one. It is in the todo, of course :) P.S. Sergei, if you see this, in one of your replies to my full-text suggestions back in September ( http://lists.mysql.com/mysql/149644 ), you said Another reply will follow... I never saw another reply though. :-/ It's OK, I was just wondering what other interesting things you were going to say! :-) Oops, checking... Yes, sorry. But I have to admit, I absolutely do not remember what I had in mind when I wrote it and what should've followed :( Sorry for this. 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: MySQL on NetWare?
Hi Chris We're running MySQL on NetWare6.5 with good performance stability. About 20GB with InnoDB and a simple replication enabled (M - S). This database will grow up to 200GB in a year (with 400 active connections). We did tests and we liked. We'll set up another 2 servers to run a 100GB database (with 300 active connections) cluster-enabled. About the file size limit, your friend is wrong. You have 2 FS with NW5.0+: - TFS (old file system), but _does_ support 4GB. - NSS 64bit file system (yes, it support files 4GB+). And it's the default filesystem since NW6.0. Our databases runs only with InnoDB, but with 21 files x 1 GB. Personally I don't like to have big files in any OS. But if I want, I can create just one file with 21GB, no problem. An engineer at Novell told me about tests with a 4TB database. So, don't worry. We're fine. Regards, Eduardo - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 10:12 PM Subject: MySQL on NetWare? Hi all! I was wondering if anyone on the list currently runs MySQL on Novell NetWare. Any comments about performance relative to MySQL on similar hardware running a different OS? Additionally, I've been told (by someone of dubious authority) that NetWare has (and has had for a long time) a 2 GB file size limit. Is this correct? I was under the impression that the native NetWare FS was actually very impressive in all respects... Regards, Chris -- 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]
Problem with EQ_REF and ALL
Someone please help me... I have 2 queries (in the end I end up joining them with a LEFT JOIN, but they don't work properly) SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id = b.batch_lever_id LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id This one gives ALL on tbl_v_levering l SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_bestelbon bb ON bb.ver_id = b.batch_bestel_id LEFT JOIN tbl_klanten k1 ON k1.klant_id = bb.ver_klant_id This one, on the other hand is correct and gives EQ_REF ! How is this possible ? It is almost exactly the same table structure :s... Somebody please help..
RE: Comparing date fields (Delphi programmers take note)
Thanks for all those who helped me with this a few days ago (I've only now had the chance to get back to programming). As someone correctly surmised, I am using Delphi and connecting to mySQL via a TQuery (actually a TZQuery, via Zeos components). What I have discovered is that 1. mySQL doesn't like queries with date parameters (one must pass the date as a literal) 2. the date format has to be changed to that of mySQL. 3. if one uses a table alias (p in my case), one is not allowed to use the full table name - projects.closeddate causes an error, but p.closeddate is ok. So I wrote a small function (called date2sql) which turns 18/12/2003 into 2003-12-18 (complete with double quotation marks), and then in my code I write with query1 do begin close; sql[3]:= 'where p.closeddate between ' + date2sql (edFromDate.text) + ' and ' + date2sql (edTillDate.text); open end; This works as it should. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 4:47 PM To: [EMAIL PROTECTED] Subject: Re: Re[2]: Comparing date fields mysql select projects.name from projects JB - where projects.closeddate between :fd and :td; JB ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at JB line JB 2 JB What are those colons? He probably uses Delphi or C++Builder to connect to mysql via TQuery component. TQuery has a property SQL where sql statement is stored. The colon ':' means that fd is a parameter whose value should be inserted into TQuery.SQL statement just before executing the query. The solution is to substitute :fd with a real value directly by accessing TQuery.SQL property. Query.SQL.Clear; Query.SQL.Add('select ...'); Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02'); Query.Open; I very much doubt this works: 01 divided by 01 :-) I guess you wanted to write: '01/01/01' With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sporadic myisam table corruption.
torsdag 18. desember 2003, 13:24, skrev Victor Medina: I also agree with you, it might be a hardware problem, but i also must note, that in a environment with very often writes to the db, myisam tables are not always the best choice, that's why i suggested a change to InnoDB tables, a problem with sql or logic is more likely to happen that a hardware glitch I frequently got corruption on a 3GB tablefile when running out of memory. Installing more memory solved the problem. Arne -- Arne K. Haaje | www.drlinux.no Bregneveien 9 | 1825 Tomter | M: 92 88 44 66 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing date fields (Delphi programmers take note)
Hi, Interesting. Thanks for all those who helped me with this a few days ago (I've only now had the chance to get back to programming). As someone correctly surmised, I am using Delphi and connecting to mySQL via a TQuery (actually a TZQuery, via Zeos components). What I have discovered is that 1. mySQL doesn't like queries with date parameters (one must pass the date as a literal) Hmmm... I do make use of parameters, but I noticed MySQL doesn't provide me with the right datatype (like Firebird or InterBase do). So I'm passing everything as a string to the parameter. :-/ 2. the date format has to be changed to that of mySQL. Well, that makes sense of course :-) 3. if one uses a table alias (p in my case), one is not allowed to use the full table name - projects.closeddate causes an error, but p.closeddate is ok. That's according to the SQL standard: if you use an alias, address the table by it's alias only. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with EQ_REF and ALL
Hi, Have you indexed the fields you're using in the JOIN clauses? Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Koen Van Mulders [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 15:45 Subject: Problem with EQ_REF and ALL Someone please help me... I have 2 queries (in the end I end up joining them with a LEFT JOIN, but they don't work properly) SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id = b.batch_lever_id LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id This one gives ALL on tbl_v_levering l SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_bestelbon bb ON bb.ver_id = b.batch_bestel_id LEFT JOIN tbl_klanten k1 ON k1.klant_id = bb.ver_klant_id This one, on the other hand is correct and gives EQ_REF ! How is this possible ? It is almost exactly the same table structure :s... Somebody please help.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Bug in 4.1.1 Internationalization on Windows2000 ?
- Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 1:36 PM Subject: Re: Fw: Bug in 4.1.1 Internationalization on Windows2000 ? You can use: SELECT SUBSTRING_INDEX(CONVERT(USER() USING cp1251),'@',1); SELECT SUBSTRING_INDEX(USER(),_utf8'@',1); Thanks! In this case I need to rewrite all client apps, something a kind of SELECT SUBSTRING_INDEX(USER(),/*!40100 _utf8*/'@',1); - at this point I rely on it that server ALWAYS must be under utf8. IMHO, explicit using of such constants in program code is very bad style: servers as well as clients can have different codepages (cp1251, koi8r etc (for me)). Why not return result of system functions according 'character_set_server' variable by deault? IMHO, it would be more correctly. WBR! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID namecompany 12347771joe null 12347772marynull 43210001bob null Company Table ID name 1234Acme 4321Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Countries, cities, states database
Hi, I searching for a database, with the 'complete' world Countries, cities, states Just like http://www.datingplace.com/servlet/NewRegistration I need for an academic study. Thanks, Augusto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with EQ_REF and ALL
I think they are all indexed, yes. I have an index on : bb.ver_id b.batch_bestel_id k.klant_id (k1 is the same) b.batch_lever_id l.ver_lev_id Tnx in advance
Fw: Problem with EQ_REF and ALL
- Original Message - From: Dobromir Velev To: Koen Van Mulders ; [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 3:14 PM Subject: Re: Problem with EQ_REF and ALL Hi, Have you indexed the fields you're using in the JOIN clauses? Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Koen Van Mulders [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 15:45 Subject: Problem with EQ_REF and ALL Someone please help me... I have 2 queries (in the end I end up joining them with a LEFT JOIN, but they don't work properly) SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id = b.batch_lever_id LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id This one gives ALL on tbl_v_levering l SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_bestelbon bb ON bb.ver_id = b.batch_bestel_id LEFT JOIN tbl_klanten k1 ON k1.klant_id = bb.ver_klant_id This one, on the other hand is correct and gives EQ_REF ! How is this possible ? It is almost exactly the same table structure :s... Somebody please help.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Problem with EQ_REF and ALL
I think they are all indexed, yes. I have an index on : bb.ver_id b.batch_bestel_id k.klant_id (k1 is the same) b.batch_lever_id l.ver_lev_id Tnx in advance I am new to this mailinglist thing, so i don't know how to reply :-$ Sorry... - Original Message - From: Dobromir Velev To: Koen Van Mulders ; [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 3:14 PM Subject: Re: Problem with EQ_REF and ALL Hi, Have you indexed the fields you're using in the JOIN clauses? Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Koen Van Mulders [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 15:45 Subject: Problem with EQ_REF and ALL Someone please help me... I have 2 queries (in the end I end up joining them with a LEFT JOIN, but they don't work properly) SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id = b.batch_lever_id LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id This one gives ALL on tbl_v_levering l SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_bestelbon bb ON bb.ver_id = b.batch_bestel_id LEFT JOIN tbl_klanten k1 ON k1.klant_id = bb.ver_klant_id This one, on the other hand is correct and gives EQ_REF ! How is this possible ? It is almost exactly the same table structure :s... Somebody please help.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transactions and create table (was Questions about MySQL implementation)
What is the official word on doing a CREATE TABLE inside a transaction? Can I do one without causing the transaction to commit? By experiment, it appears that 4.0.14 allows this, although, even if the CREATE TABLE is for an InnoDB table, a ROLLBACK doesn't remove the created table. It appears that CREATE TABLE caused a commit of the current transaction in 3.23.49. I'd like to create a temporary table during a transaction--mostly to be able to emulate things like subqueries and views that will come in some future production version of MySQL. I don't have a problem with the table creation not being rolled back. I tried to RTFM. I did find a note on how transactions are treated for CREATE TABLE SELECT..., but I wasn't able to find a clear statement that CREATE TABLE will no longer force a commit. From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Questions about MySQL implementation Date: Thu, 18 Dec 2003 01:18:39 +0200 Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, December 13, 2003 7:24 AM Subject: Questions about MySQL implementation [snip] 2. I've been told on good authority (by persons on this fine list) that Sybase and PostgreSQL (and, from personal experience, SQLBase) support ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE etc. From what I can gather, neither BDB nor InnoDB do this. Does anyone know what sort of technical challenges making the above statements undoable involve over and above INSERT, DELETE and UPDATE statements? Would this functionality be something that MySQL AB / Innobase Oy would be interested in developing should it be sponsored? Not very difficult: we could keep the 'old' table until the transaction commit. In a rollback we would fall back to the old table. But the demand for such a feature is so low that most databases do not have a rollback of DDL statements. [snip] Chris Best regards, Heikki Tuuri Innobase Oy [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions and create table (was Questions about MySQL implementation)
Bill, http://www.innodb.com/ibman.php#Implicit_commit: 8.7 When does MySQL implicitly commit or rollback a transaction? MySQL has the autocommit mode switched on in a session if you do not do SET AUTOCOMMIT=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (commits only if version MySQL-4.0.13 and MySQL binlogging is used), ALTER TABLE, BEGIN, START TRANSACTION, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. If you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: Bill Easton [EMAIL PROTECTED] Vastaanottaja: [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Thursday, December 18, 2003 5:02 PM Aihe: transactions and create table (was Questions about MySQL implementation) What is the official word on doing a CREATE TABLE inside a transaction? Can I do one without causing the transaction to commit? By experiment, it appears that 4.0.14 allows this, although, even if the CREATE TABLE is for an InnoDB table, a ROLLBACK doesn't remove the created table. It appears that CREATE TABLE caused a commit of the current transaction in 3.23.49. I'd like to create a temporary table during a transaction--mostly to be able to emulate things like subqueries and views that will come in some future production version of MySQL. I don't have a problem with the table creation not being rolled back. I tried to RTFM. I did find a note on how transactions are treated for CREATE TABLE SELECT..., but I wasn't able to find a clear statement that CREATE TABLE will no longer force a commit. From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Questions about MySQL implementation Date: Thu, 18 Dec 2003 01:18:39 +0200 Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, December 13, 2003 7:24 AM Subject: Questions about MySQL implementation [snip] 2. I've been told on good authority (by persons on this fine list) that Sybase and PostgreSQL (and, from personal experience, SQLBase) support ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE etc. From what I can gather, neither BDB nor InnoDB do this. Does anyone know what sort of technical challenges making the above statements undoable involve over and above INSERT, DELETE and UPDATE statements? Would this functionality be something that MySQL AB / Innobase Oy would be interested in developing should it be sponsored? Not very difficult: we could keep the 'old' table until the transaction commit. In a rollback we would fall back to the old table. But the demand for such a feature is so low that most databases do not have a rollback of DDL statements. [snip] Chris Best regards, Heikki Tuuri Innobase Oy [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Countries, cities, states database
Hello Augusto, You might be able to use the list of weather stations available from WMO at URL: http://www.wmo.ch/index-en.html Among other thing it contains the country and station name (or city name). I know that NWS/NOAA also have that list but it seems that the list is not available right now. Good luck, Bernard On Thursday 18 December 2003 09:32, Augusto Cesar Castoldi wrote: Hi, I searching for a database, with the 'complete' world Countries, cities, states Just like http://www.datingplace.com/servlet/NewRegistration I need for an academic study. Thanks, Augusto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Long Query Times
Hi all, I am curious to know if the query times I'm seeing are reasonable or not. Here's my situation: I am using a database to keep up with web statistics. The table is currently growing at about 2 million records a day. The sample table I am working with has right at 4 million rows. When I execute the query below, it takes approximately 8.5 seconds to return. Is this reasonable? Because the way I see it, this time is going to get out of control after a week, or month, or year. SELECT DATE_FORMAT(time, %Y-%m-%d %H:%i) AS date, SUM(sbytes) AS bytes FROM log GROUP BY date ORDER BY date Here is some info about my system: Single Processor Intel Xenon 3.06GHz, 1 GB RAM, RAID 0 SCSI 15K RPM running MySQL ver 11.18 distrib 3.23.58 on RedHat 9. My table currently looks like this: 3 unsigned ints, 4 char(100), 1 char(15), and a datetime column. I am not using a primary key or indexes. I'm positive a better table design will drastically improve query time. My concern is the rate at which the time grows. Can someone who has worked with a table of this size let me know what kind of times I should be expecting. Any information you guys can provide will be greatly appreciated. Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with EQ_REF and ALL
Can you please send the tables structure. This happens when the fields you are using in the JOIN clause are with different types fro example when trying to join a VARCHAR with INT Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Koen Van Mulders [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 16:54 Subject: Problem with EQ_REF and ALL I think they are all indexed, yes. I have an index on : bb.ver_id b.batch_bestel_id k.klant_id (k1 is the same) b.batch_lever_id l.ver_lev_id Tnx in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with MySQL 4.1.1 and german charset
Hi! I compiled mysql 4.1.1 today and used the following config: #v+ --with-character-set=latin1 \ --with-collation=latin1_german1_ci \ --with-extra-charsets=all #v- ./configure and make worked fine, but make test failed: #v+ cd mysql-test ; ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --language=../sql/share/english/ 031218 17:57:19 COLLATION 'latin1_german1_ci' is not valid for CHARACTER SET 'latin1' 031218 17:57:19 Aborting 031218 17:57:19 ../sql/mysqld: Shutdown Complete Error executing mysqld --bootstrap Error: Could not install master test DBs make: *** [test] Error 1 #v- Why is the collation not valid for charset latin1? It certainly should be... Greetings from Germany Sebastian Wiesinger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
yes, it should work, but should not be 1,1 ? or simply: UPDATE RemoteStation SET company=LEFT(ID, 4); ? gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID name company 12347771 joe null 12347772 mary null 43210001 bob null Company Table ID name 1234 Acme 4321 Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- 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: Performance problems using 1GB Linux server and trying to avoid swapping
In the last episode (Dec 18), Markus Fischer said: On Tue, Dec 16, 2003 at 10:38:14AM -0600, Dan Nelson wrote : Raising sort_buffer_size and join_buffer_size may also help if your queries pull a lot of records. From what I read from the manual, sort_buffer_size is only used for the isamchk tools, isn't it? I've adapted join_buffer_size though; thanks. It's used for any sorting: * `sort_buffer_size' Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster `ORDER BY' or `GROUP BY' operations. *Note Temporary files::. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1
W2000 Server I installed the 4.0.17-nt version. I downloaded the 4.1, there is no Setup or install .exe !!! So I copied the files which was in the zip file on the 4.0 files, ans I can t start the service next: too long to start Do you know the problem, and why there is no setup file in the zip file downloaded ? Boris
Problem Installation
I have a installation problem in the my server. After of command ./scripts/mysql_install_db appear the o error: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) 031217 14:32:34 Warning: Changed limits: max_connections: 100 table_cache: 73031217 14:32:35 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h gray.ecmal.br password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com please, somebody help me!!! *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem Installation
Here's another hintfrom the mysql site: http://www.mysql.com/information/presentations/presentation-oscon2000-27 19/index.html Increase number of open files for system and for the SQL server. (add ulimit -n # in the safe_mysqld script). Also -- http://www.mysql.com/documentation/mysql/bychapter/manual_toc.html#Server_pa rameters Any help? Simon - Original Message - From: Carlos André Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 6:10 PM Subject: Problem Installation I have a installation problem in the my server. After of command ./scripts/mysql_install_db appear the o error: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) 031217 14:32:34 Warning: Changed limits: max_connections: 100 table_cache: 73031217 14:32:35 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h gray.ecmal.br password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com please, somebody help me!!! *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- 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]
convert dynamic page to html page
Hi I build a online catalog linked to mysql db. Now my client wants the site on a cdrom. What is the fastest way to convert the product pages into static html format? -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert dynamic page to html page
On Thursday 18 December 2003 17:46, Paul Godard wrote: Hi I build a online catalog linked to mysql db. Now my client wants the site on a cdrom. What is the fastest way to convert the product pages into static html format? -- wget --mirror ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
DId I type that? I meant 1,4 I left it as substring because that was what he tried. ¿n©_ ¡E¢X ¡¸ wrote: yes, it should work, but should not be 1,1 ? or simply: UPDATE RemoteStation SET company=LEFT(ID, 4); ? gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID name company 12347771 joe null 12347772 mary null 43210001 bob null Company Table ID name 1234 Acme 4321 Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- 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]
Help me - please
Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) 031217 14:32:34 Warning: Changed limits: max_connections: 100 table_cache: 73031217 14:32:35 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h gray.ecmal.br password 'new-password' See the manual for more instructions.You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com please, somebody help me!!! *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
On Thu, Dec 18, 2003 at 10:37:46AM -0600, Dan Nelson wrote : In the last episode (Dec 18), Markus Fischer said: On Tue, Dec 16, 2003 at 10:38:14AM -0600, Dan Nelson wrote : Raising sort_buffer_size and join_buffer_size may also help if your queries pull a lot of records. From what I read from the manual, sort_buffer_size is only used for the isamchk tools, isn't it? I've adapted join_buffer_size though; thanks. It's used for any sorting: * `sort_buffer_size' Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster `ORDER BY' or `GROUP BY' operations. *Note Temporary files::. Of course, sorry, I got confused then. thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: localhost
* Dan V [EMAIL PROTECTED] [2003-12-17 02:03 +0100]: When I check the status it says [EMAIL PROTECTED] How do I change that to [EMAIL PROTECTED] What's wrong with localhost? Connecting to localhost makes your client use a socket or pipe, which is faster and more secure than the tcp connection it chooses for non-localhost servers. If you really still want to connect to the hostname, you should - make sure there is no skip-networking in my.cnf, - grant access to [EMAIL PROTECTED] and - run mysql -u SOMEUSERNAME -p -h servername.foo.org. -- Johannes Franken MySQL Professional mailto:[EMAIL PROTECTED] http://www.jfranken.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database output
When I make request from mysql database, I want each resulting value to appear on a particular table cell - that is, a user would see the results placed appropiately in relevant table cells like color under color heading, size under size heading etc. Can someone give me some clue on how I can achieve this aim Thanks Ike __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: MySQL GUI tool
I'm using DBTools DBManager Professional - it's free : http://www.dbtools.com.br/EN/dbmanagerpro.php Freddie -Ursprüngliche Nachricht- Von: Amanullah [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 18. Dezember 2003 03:57 An: [EMAIL PROTECTED] Cc: MySQL List Betreff: Re: MySQL GUI tool Hi, I'm Using the Same, the Tool is very much useful to me to export data from one server to another online, and for much more activities. Excellent... -Aman. Mike Blezien wrote: Hello, Was wondering if any one on the list has or is using PremiumSoft Navicat's MySQL Administration Tool for windows, and if so, any thoughts on it... good, excellent, poor...etc. Appreciate the feedback ;) TIA -- 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: Connector/J 3.0.9 Unexpected end of input
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bill Ataras wrote: I upgraded to Connector/J 3.09 from 2.0.14. After some hours, the driver looses it's connection and will not reconnect (despite setting reconnect=true). The error starts as follows below. I rollback to 2.0.14 and everything is stable again. This is using mysql 4.0.16-standard. Is connector/j 3.0x production release? Yes, Connector/J 3.0.x is a production release. Perhaps you missed the following information in the README that deals with the fact that autoReconnect=true behavior has changed? The driver now has fail-over support. This allows the driver to fail-over to any number of slave hosts and still perform read-only queries. Fail-over only happens when the connection is in a autoCommit(true) state, because fail-over can not happen reliably when a transaction is in progress. Most good application servers and connection pools set autoCommit to 'true' at the end of every transaction/connection use. I need to re-work the docs to say that this applies to autoReconnect as well, however, the following troubleshooting section from the README also tells you how to handle this (and why relying on autoReconnect might not be a good idea for most people): Issue: I have a servlet/application that works fine for a day, and then stops working overnight. Resolution: MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the autoReconnect parameter (see USAGE AND INSTALLATION). Also, you should be catching SQLExceptions in your application and dealing with them, rather than propagating them all the way until your application exits, this is just good software development. MySQL Connector/J will set the SQLState (see java.sql.SQLException.getSQLState() in your APIDOCS) to 08S01 when it encounters network-connectivity issues during the processing of a query. Your application code should then attempt to re-connect to MySQL at this point. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/4g9RtvXNTca6JD8RAtRGAJ9IkWznogxvmoFfFVdqtWsMMHCV0gCfYL/0 otNjauYKCQn98p5oOvM/uzo= =NUmy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long Query Times
At 09:43 AM 12/18/2003, you wrote: Hi all, I am curious to know if the query times I'm seeing are reasonable or not. Here's my situation: I am using a database to keep up with web statistics. The table is currently growing at about 2 million records a day. The sample table I am working with has right at 4 million rows. When I execute the query below, it takes approximately 8.5 seconds to return. Is this reasonable? Because the way I see it, this time is going to get out of control after a week, or month, or year. SELECT DATE_FORMAT(time, %Y-%m-%d %H:%i) AS date, SUM(sbytes) AS bytes FROM log GROUP BY date ORDER BY date Here is some info about my system: Single Processor Intel Xenon 3.06GHz, 1 GB RAM, RAID 0 SCSI 15K RPM running MySQL ver 11.18 distrib 3.23.58 on RedHat 9. My table currently looks like this: 3 unsigned ints, 4 char(100), 1 char(15), and a datetime column. I am not using a primary key or indexes. I'm positive a better table design will drastically improve query time. My concern is the rate at which the time grows. Can someone who has worked with a table of this size let me know what kind of times I should be expecting. Any information you guys can provide will be greatly appreciated. Thanks, Dan Dan, You don't need the Order By since the Group By is already sorting it in that order. As far as speed is concerned, you are duplicating a lot of calculations you've already done the day before. I think you need to store the results that are returned from your daily query, into a summary table (called Summary) so you only need to summarize the current day's raw data once. So just execute your Select statement and append the results to the Summary table, then archive the raw daily data because you won't need it any more (write it to DVD each day or archive it to another database). Do this once a day. So instead of working with 2 million rows a day*365 days=730 million rows, you are only adding 24*60 rows (1 for each minute) to the Summary table * 365 days=525,600 rows = 1388 times faster! Quite an improvement, don't you think? :-) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combining tables
I have a live feed datasource that inserts into 8 different tables( but they are all the same layout only differennt regions) and i am not allowed to change this since it is from a customer. As with Oracle you can create a materialized view that will combine those eight tables into one physical table and those will be refreshed on the fly or on certain times. Is this also possible in MySQL since we want to query on one table to find the record instead off eight.
Cantonese Chinese character set
I am building a site that will call up text data stored in a db by language and need to add Cantonese to the list of languages it will use. What sources would be suggested to find out what I need to do to get this to work. And I think this will be stored as a multi-byte character set. So, what considerations do I need to know about for this? Larry R. Sieting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combining tables
In the last episode (Dec 18), Arnoldus Th.J. Koeleman said: I have a live feed datasource that inserts into 8 different tables( but they are all the same layout only differennt regions) and i am not allowed to change this since it is from a customer. As with Oracle you can create a materialized view that will combine those eight tables into one physical table and those will be refreshed on the fly or on certain times. Is this also possible in MySQL since we want to query on one table to find the record instead off eight. As long as all the tables have the exact same column definitions, you can use a MERGE table to provide a combined view of them all. I have found that they only perform well under MySQL 4.1.1, though. Older versions had a tendency to perform full table scans across the lower tables instead of using indexes. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long Query Times
an index on any appropriate data (date, sum) should also help tremendously. Thomas Danny Melton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi all, I am curious to know if the query times I'm seeing are reasonable or not. Here's my situation: I am using a database to keep up with web statistics. The table is currently growing at about 2 million records a day. The sample table I am working with has right at 4 million rows. When I execute the query below, it takes approximately 8.5 seconds to return. Is this reasonable? Because the way I see it, this time is going to get out of control after a week, or month, or year. SELECT DATE_FORMAT(time, %Y-%m-%d %H:%i) AS date, SUM(sbytes) AS bytes FROM log GROUP BY date ORDER BY date Here is some info about my system: Single Processor Intel Xenon 3.06GHz, 1 GB RAM, RAID 0 SCSI 15K RPM running MySQL ver 11.18 distrib 3.23.58 on RedHat 9. My table currently looks like this: 3 unsigned ints, 4 char(100), 1 char(15), and a datetime column. I am not using a primary key or indexes. I'm positive a better table design will drastically improve query time. My concern is the rate at which the time grows. Can someone who has worked with a table of this size let me know what kind of times I should be expecting. Any information you guys can provide will be greatly appreciated. Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me - please
On Thu, Dec 18, 2003 at 05:28:30PM -0200, Carlos Andr? Moura de Amorim wrote: 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) It might be worth putting a ulimit -n 1024 (or some other decent number) in the rc.d script that starts mysql; then (assuming the kernel is going to allow it) mysql will be able to change the number of file descriptors it can open, to the best of my knowledge. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regd Sub Queries
hello every one, i am in the process of converting my database from access to mysql ... now tat i have started working with mysql i have realized that my subqueries wont work here ... i read the article on how to handle some of the simple subqueries with joins ... my subquery is as follow and i dont think i couldnt really convert it ... Select Payment_Date from payments where Payment_Id in (select max(Payment_id) from payments where Payment_Claim_Id=#Claim_Id#) wat option do i have other than using 2 queries ... Also i am doing my developement using mysql 4.0 version ... i see that 4.1 is in its alpha does anyone have an idea when it be ready to use for production ... (if it could be used for production then ... i dont have to do the painfull conversions ) Sandeep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binaries for PocketPC
Hi, Does anyone know of a MySQL distribution for the PocketPC? Thanks, Matt --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.543 / Virus Database: 337 - Release Date: 11/21/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regd Sub Queries
Sandeep N Seshadri wrote: hello every one, i am in the process of converting my database from access to mysql ... now tat i have started working with mysql i have realized that my subqueries wont work here ... i read the article on how to handle some of the simple subqueries with joins ... my subquery is as follow and i dont think i couldnt really convert it ... Select Payment_Date from payments where Payment_Id in (select max(Payment_id) from payments where Payment_Claim_Id=#Claim_Id#) wat option do i have other than using 2 queries ... Also i am doing my developement using mysql 4.0 version ... i see that 4.1 is in its alpha does anyone have an idea when it be ready to use for production ... (if it could be used for production then ... i dont have to do the painfull conversions ) Sandeep From what I gather on the mailing list, MySQL-4.1.1 should be stable enough. I'm holding off on upgrading here because of the changes to the authentication system. If you want to use 4.1.x now, you have to set up your accounts under 4.0.x and then upgrade to 4.1.x. But this makes me a little nervous with regards to disaster recovery. Once a version of MyODBC that supports the new authentication system in 4.1.1 is out, I would have no problem recommending 4.1.1. Until then, you can do it, and you'll probably be fine, but don't look at me if things go wrong and you're offline for a few hours while you figure out how to restore from a backup and set up authentication again... As for the 2 queries option, I find this works quite well. In Access, you can set up 'pass through' queries, which is where Access doesn't handle the query at all - it passes the SQL straight to MySQL, and then receives the results back as a read-only recordset. You can then use this query in your 2nd query. I've found also that if you turn on MySQL's query cache, this speeds up ENORMOUSLY ... Access seems to re-issue the same query over over again for each record it finds in the other table, and the query cache handles this quite nicely :) Post back if you want help setting up pass-through queries. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regd Sub Queries
Could you please tell me more about the pass through queries. i dont have an idea abt it sandeep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
? about user and db table
Hi, I'm trying to uderstand the difference between these tables (mysql.user, mysql.db) v.4.0. I want user usera from hostname1 to connect to hostname2 which is where the Mysql db is running and have only select priv on db db_1. Hostname1 has mysql clients on it. Host: mysql select * from user where Host = 'hostname1'; +---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ | hostname1 | usera | 016 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N | ANY || | | 0 | 0 | 0 | +---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ 1 row in set (0.00 sec) mysql db: mysql select * from db where Host = ' hostname1'; +---++-+-+-+-+-+-+---++-+++---+--+ | Host | Db | User| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | +---++-+-+-+-+-+-+---++-+++---+--+ | hostname1 | db_1 | usera | Y | N | N | N | N | N | N | N | N | N | N | N| +---++-+-+-+-+-+-+---++-+++---+--+ 1 row in set (0.00 sec) mysql Which table gives user usera from hostname1 permission to just do select statements on the table db_1 on hostname2? I think its db? But since user says 'N' for all options for this user from hostname1 does that override the db table options?? I'v looked at the manual, but I'm just not getting it... Can someone break it down for me... Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regd Sub Queries
Sandeep N Seshadri wrote: Could you please tell me more about the pass through queries. i dont have an idea abt it sandeep Sure. Create a new query in Access. Instead of adding tables to the query design view like normal, simply click the 'query' menu, and select 'SQL Specific', and then 'Pass-through'. The query builder window will disappear, and leave you with a plain text editor where you can enter the SQL. Remember not to put the usual garbage that Access puts around field names, ie [square brackets], as MySQL doesn't like them. Then click the 'view' menu, and select 'Properties'. The 2nd field should be 'ODBC connection string'. Click inside this field, and a little builder icon ( ... ) appears to the side. Click the builder, and you should get your list of ODBC data sources. Select your data source for MySQL, and you're done. When you run the query, it will get passed straight to MySQL. You can edit the SQL of the query later in VB if you have to, for example: dim sql_string as string sql_string = select * from MyTable where ID=456 and thingy=3 currentdb.querydefs(NameOfMyPassthroughQuery).sql = sql_string The above code will update the SQL of your pass-through query, so when you open it next time, it will return the records you're after. Pretty cool, eh? Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating table1 from table2
Hi: I am new to MySQL and can not find how to do this. I am running 4.0.16. Table1 has the correct values for a common field. Table 2 (the real one) needs to be updated from t1 via a common field. Can this be done with joins. And if not what is the best way. Thanks for any suggestions. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FIXED: Access crash when connected to MySQL and left inactive
Hi all. Microsoft have *finally* fixed the long-standing bug in Access where if you left it connected to MySQL ( and apparently Oracle too ) and inactive for a few minutes, it would crash, apologising: Microsoft is incredibly sorry for the inconvenience ... please contact us regarding this issue for a partial refund in line with our 'cash for crash' program. Yeah well not quite. But anyway, Service Pack 8 of the MS Jet system fixes it. Go get it, all Access users! Download: http://www.microsoft.com/downloads/details.aspx?FamilyID=1e268b39-533e-48b0-b8d7-1781befde1f8DisplayLang=en Summary of Service Pack: http://support.microsoft.com/default.aspx?scid=kb;[LN];829558 Summary of the bug that concerns us: http://support.microsoft.com/default.aspx?kbid=282349 Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary install instructions wrong?
Has anyone done this before? I can't be the only one that likes custom paths/directories! :) [EMAIL PROTECTED] Thanks to the remote control I have the attention span of a gerbil! There are 10 types of people in the world. Those who understand binary, and those who don't. - Original Message - From: Dave Best [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 11:57 PM Subject: Binary install instructions wrong? Hey all, I don't like the default locations that mysql tries to install into.. I like putting stuff in user accounts, with a version directory. Easier to maintain, yada yada. Anyways, as per the instructions you have to change a path in ./bin/mysqlaccess I did this but when I run the scripts/mysql_install_db I get the following... What am I missing? mkdir: cannot create directory `/var/lib/mysql': Permission denied chmod: failed to get attributes of `/var/lib/mysql': No such file or directory mkdir: cannot create directory `/var/lib/mysql/mysql': No such file or directory chmod: failed to get attributes of `/var/lib/mysql/mysql': No such file or directory mkdir: cannot create directory `/var/lib/mysql/test': No such file or directory chmod: failed to get attributes of `/var/lib/mysql/test': No such file or directory Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 031217 23:57:16 Warning: Can't create test file /var/lib/mysql/localhost.lower-test ./bin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2) 031217 23:57:16 Aborting 031217 23:57:16 ./bin/mysqld: Shutdown Complete Installation of grant tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! [EMAIL PROTECTED] Thanks to the remote control I have the attention span of a gerbil! There are 10 types of people in the world. Those who understand binary, and those who don't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help me!!!how to write such sql in mysql?
in sql: insert into table select from ... can select from a remote data base server such as sqlserver using odbc as 'SELECT * into test01 FROM Fund IN [ODBC] [ODBC;Driver=SQL Server;UID=admin;PWD=;Server=(lacal);DataBase=CMBXMDM;]'in access. thanks! _ MSN Hotmail http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating table1 from table2
Try something like this: INSERT INTO table1 (common_field) SELECT common_field FROM table 2; - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 19, 2003 10:42 AM Subject: Updating table1 from table2 Hi: I am new to MySQL and can not find how to do this. I am running 4.0.16. Table1 has the correct values for a common field. Table 2 (the real one) needs to be updated from t1 via a common field. Can this be done with joins. And if not what is the best way. Thanks for any suggestions. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- 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]
Question
Dear friends, I downloaded mysql on windows 98. I entered cd c;\mysql_install_db It is responding as too many parameters Can anyone advice how to fix it, please. Delmar
Re: Question
At 09:34 PM 12/18/2003, [EMAIL PROTECTED] wrote: Dear friends, I downloaded mysql on windows 98. I entered cd c;\mysql_install_db It is responding as too many parameters Can anyone advice how to fix it, please. Delmar Take out the semi colon and put in a colon if you are trying to change to a directory called mysql_install_db. Else if you are trying to change directory to the root and running a program called mysql_install_db, then do: cd c:\ mysql_install_db Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show processlist
Hi Andrius, Yes, I've wondered about this before too, but wasn't exactly sure what it meant either. :-) So I just decided to see where this state is set in the code, and it's when the make_join_statistics() function is called. I think that function checks key distribution and things to see which index to use, if any, when looking up rows in a table. Now we both know. ;-) Hope that helps. Matt - Original Message - From: Andrius Jakas Sent: Thursday, December 18, 2003 6:08 AM Subject: show processlist Hi, show processlist displays processes with state statistics, what does this status means. Documentation doesn't say much A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory trap
031218 20:53:48 InnoDB: WARNING: over 4 / 5 of the buffer pool is occupied by InnoDB: lock heaps or the adaptive hash index! Check that your InnoDB: transactions do not set too many row locks. Starting InnoDB InnoDB: Monitor to print diagnostics, including lock heap and hash index InnoDB: sizes. 031218 20:53:48 InnoDB: WARNING: over 4 / 5 of the buffer pool is occupied by InnoDB: lock heaps or the adaptive hash index! Check that your InnoDB: transactions do not set too many row locks. Starting InnoDB InnoDB: Monitor to print diagnostics, including lock heap and hash index InnoDB: sizes. 031218 20:53:48 InnoDB: ERROR: over 9 / 10 of the buffer pool is occupied by InnoDB: lock heaps or the adaptive hash index! InnoDB: We intentionally generate a seg fault to print a stack trace 031218 20:53:48 InnoDB: Assertion failure in thread 10 in file buf0lru.c line 219 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 031218 20:54:11 mysqld restarted 031218 20:54:13 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. 031218 20:54:20 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 2898284579 InnoDB: Doing recovery: scanned up to log sequence number 0 2898284579 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: Trx id counter is 0 1792 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 1298 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking the tables
Hi all, I have mysql verison 3.23.58 and all tables in a database say test_database are of MyIsam types. I want to put write lock on a(set of) table(s), so that only i can insert into it and no body else unless i remove the write lock. How can i achieve it?? Please help me out .. Thanks in advance Binay
Re: Locking the tables
Hello Binay. Friday, December 19, 2003, 1:53:01 PM, you wrote: B I want to put write lock on a(set of) table(s), so that only i B can insert into it and no body else unless i remove the write lock. B How can i achieve it?? AFAIR, you usu. do that with: LOCK TABLE mytbl WRITE; // do smth. here UNLOCK TABLE mytbl; -- See you, Andrey. [ [EMAIL PROTECTED] | ICQ# 114087545 | 2:5090/[EMAIL PROTECTED] ] ...I know on which side my bread is buttered. -- John Heywood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]