Subqueries in version 4.1
Hi We installed version 4.1.0-alpha-max-nt and try to run a query with a subquery and get the message [DB_BAS_LOCAL] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Are subqueries supporeted in 4.1 as is mentioned in the doc or not? Thanks in advance. Wouter Coppieters Department of Genetics Faculty of Veterinary Medicine University of Liege Bd de Colonster B43 B4000 Liege, Belgium 32-43-66.41.59 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database permission
Dear lists, How do I assign user and password to a database that i just created ? Thanks
Re: database permission
Hi Ketvin, after you created your database kelvin_db for example, you can set a account to use this database in this manner : GRANT ALL PRIVILEGES ON user_name.* TO @localhost identified by 'password_for_user'; GRANT ALL PRIVILEGES ON user_name.* TO @% identified by 'password_for_user'; Are useful basic information in the MySQL documentation. Read this once at least. Regards Thierno6C - MySQL 4.0.15 - Original Message - From: ketvin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 10, 2003 8:37 AM Subject: database permission Dear lists, How do I assign user and password to a database that i just created ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: practical MySQL database size limits
our database is about 20 Gb and growing daily. so far, I still see nearly constant time query performance on tables with ~10M rows. I don't think mysql is limited by file size per se. I guess performance depends a lot on what your tables look like, and your hardware, obviously. From my own experience MySQL (MyISAM) scales very well up to 20M rows, but 100M rows turned out to be too much... -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sincronization
Try SQLyog at http://www.webyog.com. It has a very good schedulable Data Sync Tool. --- Roger Baklund [EMAIL PROTECTED] wrote: * Andreas [...] On the other hand one could use intervals for key assignment. Every time a server runs out of keys it throws a warning at the admin and rejects further inserts until it gets a new key interval. It's a way to go at least with a low number of servers. Maybe even give a warning some time _before_ you run out of keys...? ;) You should never run out of keys. Every time you sync, you also check if there are many keys left to use... if you sync once a day, as soon as you have less than 10 times the expected daily usage of keys left to use, you request a new set of keys. You still would have to handle duplicates. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Database Workbench 2.4 released
Ladies, gentlemen, There's a new tool in town - Upscene Productions is proud to announce: Database Workbench for MySQL Download a trial: http://www.upscene.com Documentation: http://www.upscene.com/documentation/dbw For years, the workbench series has been helping InterBase and Firebird database developers all over the world - and now, it has become an even more versatile tool. Version 2.4 adds MySQL support, becoming a cross-database engine development tool for your needs. Features include: * Multi Engine - MySQL 4 - MS SQL Server 7, 2000, MSDE 1 MSDE 2 - Borland InterBase 4.x, 5.x, 6.x, 7.x - Firebird 1.x, 1.5 * Schema Migrator tool - convert from legacy ADO/ODBC datasources - quickly copy existing objects to other databases * Datapump - transfer data from MS SQL, ADO or ODBC datasources - schedule the command line version to do this regularly * Object Editors - easily create, alter and drop database objects - editors help to avoid errors - no need to write error prone SQL/DDL statements - BLOB editor to add/change/view your binary data * Test Data Generator - generate thousands of rows of meaningfull test data - includes a large repository of real-life names, addresses etc * Metadata Printing - print lists of tables, indices and other database objects - many options to customize the output * Import/Export - Import data from CSV - Export to many different formats, including CSV and XML * Many other usefull features and tools available... Download your trial at http://www.upscene.com If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] Thank you your support, Martijn Tonies Database Workbench - the database developer tool for professionals 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: ANN: Database Workbench 2.4 released
Hi Martjin, How do I install this tool on a Unix, Linux or BSD based machine ? Why does it say on the page 1) Receiving upgrades is not time limited. and 2) The license itself isn't time limited - but there is a 20% yearly renewal fee for maintenance and support (updates etc) on the driver after the first year. ??? I found this a) confusing and b) I would prefer a one time fee. The module system is probably a nice idea, but shouldnt that allow to offer the drivers for a reasonable price ? I found 435$ for the full module software to expensive. I can get similar and better software easily for under 100$ - ( sometimes even free) which offer more features and reliability than your product. If you have the luck to find customers which are willing to pay this, than you are probably the most terrific sales person around for a while. All power to you. Best regards -- Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ On Monday 10 November 2003 19:01, Martijn Tonies wrote: Ladies, gentlemen, There's a new tool in town - Upscene Productions is proud to announce: Database Workbench for MySQL Download a trial: http://www.upscene.com Documentation: http://www.upscene.com/documentation/dbw For years, the workbench series has been helping InterBase and Firebird database developers all over the world - and now, it has become an even more versatile tool. Version 2.4 adds MySQL support, becoming a cross-database engine development tool for your needs. Features include: * Multi Engine - MySQL 4 - MS SQL Server 7, 2000, MSDE 1 MSDE 2 - Borland InterBase 4.x, 5.x, 6.x, 7.x - Firebird 1.x, 1.5 * Schema Migrator tool - convert from legacy ADO/ODBC datasources - quickly copy existing objects to other databases * Datapump - transfer data from MS SQL, ADO or ODBC datasources - schedule the command line version to do this regularly * Object Editors - easily create, alter and drop database objects - editors help to avoid errors - no need to write error prone SQL/DDL statements - BLOB editor to add/change/view your binary data * Test Data Generator - generate thousands of rows of meaningfull test data - includes a large repository of real-life names, addresses etc * Metadata Printing - print lists of tables, indices and other database objects - many options to customize the output * Import/Export - Import data from CSV - Export to many different formats, including CSV and XML * Many other usefull features and tools available... Download your trial at http://www.upscene.com If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] Thank you your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database permission
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thierno, et al -- ...and then Thierno Cissé said... % % Hi Ketvin, % after you created your database kelvin_db for example, % you can set a account to use this database in this manner : % GRANT ALL PRIVILEGES ON user_name.* TO @localhost identified by % 'password_for_user'; % GRANT ALL PRIVILEGES ON user_name.* TO @% identified by % 'password_for_user'; Um, shouldn't that be something more like grant all privileges on kelvin_db.* to [EMAIL PROTECTED] identified... instead? % % Are useful basic information in the MySQL documentation. Read this once at % least. Agreed. It has everything you need. HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/r2g+Gb7uCXufRwARAqfKAJ9XXUh62kzeRRHiwoU/b1nQBl+20wCgkQe5 sfsWcjtEBM0+JnwfuC+AfkE= =nrHd -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subqueries in version 4.1
Wouter Coppieters [EMAIL PROTECTED] wrote: We installed version 4.1.0-alpha-max-nt and try to run a query with a subquery and get the message [DB_BAS_LOCAL] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Are subqueries supporeted in 4.1 as is mentioned in the doc or not? Yup, but as mentioned in the error LIMIT currently is not supported in the IN/ALL/ANY/SOME subquery. -- 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: ANN: Database Workbench 2.4 released
Hi Nils, How do I install this tool on a Unix, Linux or BSD based machine ? The tool itself is Windows based - however, people are running it under Wine and similar emulators. See http://www.upscene.com/documentation/dbw_freebsd_wine.htm for an example. Why does it say on the page 1) Receiving upgrades is not time limited. and 2) The license itself isn't time limited - but there is a 20% yearly renewal fee for maintenance and support (updates etc) on the driver after the first year. The (2) part comes from a different product: InterXpress for Firebird and is not related to Database Workbench. There's a different header on the webpage above that part. I found this a) confusing and b) I would prefer a one time fee. If you read it like that, it actually is confusing :-) ... Is your web-page view perhaps messed up? The page is tested with IE and Opera and looks fine in both. The module system is probably a nice idea, but shouldnt that allow to offer the drivers for a reasonable price ? You are mixing two things, the driver product has nothing to do with Database Workbench. I found 435$ for the full module software to expensive. I can get similar and better software easily for under 100$ - ( sometimes even free) which offer more features and reliability than your product. I'm surprised you can say something like that just a few hours after release :-) Take another look: if you want to use Database Workbench for MySQL only, you can purchase a license for Euro 109,- not Euro 435,- ... If you want more database engines supported, the license price will increase. If you have the luck to find customers which are willing to pay this, than you are probably the most terrific sales person around for a while. All power to you. The InterBase and Firebird modules of Database Workbench have been successfull for quite some years and include many useful tools not offered in order applications. The per database engine modules allow you to make a difference between a one size fits all product, but still have the ability to use tools like the Test Data Generator or Schema Migration tool without the need to buy licenses for hundreds or thousands of Euros. 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: About MySQL Documentation
Vladik Zakhozhai [EMAIL PROTECTED] wrote: My name is Vlad. I'm begginer with MySQL. And now I should like to know where can I take good documentation, tutor or others about MySQL. Check the following link: http://www.mysql.com/documentation/index.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select option from associated id query
Hi All MySQL users I am having a slight problem and getting inot a bit of a tizz. I have two tables and I am trying to produce a dropdown select menu whilst creating a join. The two tables are as follows... CREATE TABLE itinerary ( i_id int(3) unsigned NOT NULL auto_increment, i_title varchar(100) default NULL, i_description mediumtext, i_category int(3) unsigned default NULL, i_ordernumber int(3) unsigned default NULL, i_text mediumtext, i_image1 varchar(255) default NULL, i_image2 varchar(255) default NULL, i_image3 varchar(255) default NULL, PRIMARY KEY (i_id), UNIQUE KEY i_id (i_id), KEY i_id_2 (i_id) ) TYPE=MyISAM; and CREATE TABLE dates2 ( d_id int(3) unsigned NOT NULL auto_increment, d_objid int(3) unsigned default NULL, d_startdate date default NULL, d_enddate date default NULL, PRIMARY KEY (d_id), UNIQUE KEY d_id (d_id), KEY d_id_2 (d_id) ) TYPE=MyISAM; The two fields that are associated are dates2.d_objid and itinerary.i_id what I want to do is pull out the dates for a selected itinerary.i_id and present them in a select menu. I am using php but it's been a while and have only got so far inot a tizz :) Can anyone shed a little light on the quesry or a bit of guidance on how to approach this? Thank you Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query woes
Leo, Thanks for the quick reply. There was a typo but I fixed it. Below is what I used after correcting it : select mgr.company, building.bldgname, tenant.id from customers mgr left join customers building on building.pid=mgr.id left join customers tenant on tenant.pid=building.id group by mgr.id, building.id, tenant.id order by mgr.company, building.bldgname, tenant.company; This was closer. Problem now is that it took 6.56 seconds and returned 610 rows. I have no idea how I now have 610 rows where there are only 279. Any thing else you would suggest? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Leo [EMAIL PROTECTED] Date: Mon, 10 Nov 2003 13:17:50 +0700 To: Steffan A. Cline [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Complex query woes have you try left join? select mgr.company, building.bldgname, tenant.id from customers mgr left join customers building on building.pid=mgr.id left join costumers tenant on tenant.pid=building.id group by mgr.id, building.id, tenant.id order by mgr.company, building.bldgname, tenant.company hopefully it work :) -leo- - Original Message - From: Steffan A. Cline To: [EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:56 PM Subject: Complex query woes Basically I have a table that contains 3 types of records. Property managers, buildings and tenants. They are related upon insert by an ID and a PID (parent id). For example : ID PID CategoryName - 1 PM ABC Management 2 1 BldgGlen Heights 3 2 tenant Joe's salon Hopefully this shows how they are related. My goal is to ultimately on a Lasso (like php) page to render them like this : ABC Management Glen Heights Joe's salon Some other building Some other tenant I am able to handle the formatting fine the issue is how to get the data returned like this. I tried the following : select mgr.company, building.bldgname, tenant.company from customers as mgr,customers as building, customers as tenant where building.pid = mgr.id and tenant.pid = building.id order by mgr.company,building.bldgname,tenant.company; But it only returns 173 rows are there are 279. As you will see in http://phattwelve.hldns.com:90/workorder/findaccount2.lasso there are some property managers with buildings and no tenants, also property manager with no buildings. These get omitted by the above sql. Currently I am doing this with nested statements via lasso but is getting ridiculously slow on the live system as they add more and more clients. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select option from associated query
Hi All MySQL users I am having a slight problem and getting inot a bit of a tizz. I have two tables and I am trying to produce a dropdown select menu whilst creating a join. The two tables are as follows... CREATE TABLE itinerary ( i_id int(3) unsigned NOT NULL auto_increment, i_title varchar(100) default NULL, i_description mediumtext, i_category int(3) unsigned default NULL, i_ordernumber int(3) unsigned default NULL, i_text mediumtext, i_image1 varchar(255) default NULL, i_image2 varchar(255) default NULL, i_image3 varchar(255) default NULL, PRIMARY KEY (i_id), UNIQUE KEY i_id (i_id), KEY i_id_2 (i_id) ) TYPE=MyISAM; and CREATE TABLE dates2 ( d_id int(3) unsigned NOT NULL auto_increment, d_objid int(3) unsigned default NULL, d_startdate date default NULL, d_enddate date default NULL, PRIMARY KEY (d_id), UNIQUE KEY d_id (d_id), KEY d_id_2 (d_id) ) TYPE=MyISAM; The two fields that are associated are dates2.d_objid and itinerary.i_id what I want to do is pull out the dates for a selected itinerary.i_id and present them in a select menu. I am using php but it's been a while and have only got so far inot a tizz :) Can anyone shed a little light on the quesry or a bit of guidance on how to approach this? Thank you Andrew This is what I have used for the query and the php ? echo select name=\StartDate\ size=\1\ class='menuForm'; $result=mysql_query(select dates2.d_objid ,dates2.d_startdate,dates2.d_enddate from dates2 left join itinerary.i_id on dates2.d_objid = itinerary.i_id where dates2.d_startdate and dates2.d_enddate= . $Date . group by dates2.d_startdate); while ($row = mysql_fetch_array($result)) { $d_startdate=$row['StartDate']; $d_enddate=$row['EndDate']; echo option value=\$Date\$d_startdate - $d_enddate /option; } echo /select; ? which is clearly wrong somewhere :( Any help would be nice Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Processes Closed, but max_connections exceeded...
Joe Lewis [EMAIL PROTECTED] wrote: I've got a question for you. I've got a MySQL db running in FreeBSD 4.8, and have encountered the following problem. When I try to connect to a database by a specific user, I get the following response : #mysql -p -u comet Enter password: ERROR 1226: User 'comet' has exceeded the 'max_connections' resource (current value: 100) Now, that is exactly what I'd expect to see, IF the user had 100 connections. Instead, when viewing the processlist, I get : If you set up MAX_CONNECTIONS_PER_HOUR 100 for user it means that user can open 100 new connections per our. #mysqladmin -p processlist Enter password: +-+--+---++-+--+---+---+ | Id | User | Host | db | Command | Time | State | Info | +-+--+---++-+--+---+---+ | 469 | root | localhost || Query | 0| | show processlist | +-+--+---++-+--+---+---+ I think that the code that had established all of those connections didn't properly dispose of the connections (a PHP script that one of our clients is using). Is there a way to clear those? Execute FLUSH USER_RESOURCES command or mysqladmin reload to flush current values. You can read about limiting resources at: http://www.mysql.com/doc/en/User_resources.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query with a select output?
Hi All MySQL users I am having a slight problem and getting inot a bit of a tizz. I have two tables and I am trying to produce a dropdown select menu whilst creating a join. The two tables are as follows... CREATE TABLE itinerary ( i_id int(3) unsigned NOT NULL auto_increment, i_title varchar(100) default NULL, i_description mediumtext, i_category int(3) unsigned default NULL, i_ordernumber int(3) unsigned default NULL, i_text mediumtext, i_image1 varchar(255) default NULL, i_image2 varchar(255) default NULL, i_image3 varchar(255) default NULL, PRIMARY KEY (i_id), UNIQUE KEY i_id (i_id), KEY i_id_2 (i_id) ) TYPE=MyISAM; and CREATE TABLE dates2 ( d_id int(3) unsigned NOT NULL auto_increment, d_objid int(3) unsigned default NULL, d_startdate date default NULL, d_enddate date default NULL, PRIMARY KEY (d_id), UNIQUE KEY d_id (d_id), KEY d_id_2 (d_id) ) TYPE=MyISAM; The two fields that are associated are dates2.d_objid and itinerary.i_id what I want to do is pull out the dates for a selected itinerary.i_id and present them in a select menu. I am using php but it's been a while and have only got so far inot a tizz :) Can anyone shed a little light on the quesry or a bit of guidance on how to approach this? Thank you Andrew This is what I have used for the query and the php ? echo select name=\StartDate\ size=\1\ class='menuForm'; $result=mysql_query(select dates2.d_objid ,dates2.d_startdate,dates2.d_enddate from dates2 left join itinerary.i_id on dates2.d_objid = itinerary.i_id where dates2.d_startdate and dates2.d_enddate= . $Date . group by dates2.d_startdate); while ($row = mysql_fetch_array($result)) { $d_startdate=$row['StartDate']; $d_enddate=$row['EndDate']; echo option value=\$Date\$d_startdate - $d_enddate /option; } echo /select; ? which is clearly wrong somewhere :( Any help would be nice Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: ANN: Database Workbench 2.4 released
Hi List members, I just forward this for completeness of the thread . Best regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql - Hi Nils, How do I install this tool on a Unix, Linux or BSD based machine ? The tool itself is Windows based - however, people are running it under Wine and similar emulators. See http://www.upscene.com/documentation/dbw_freebsd_wine.htm for an example. That was 1 customer, yes thats right, but you dont want to argue that the performance will be the same ;-). From what I've heard, the performance is good. Wine is a good product, but it is more or less for an emergency and not for the everyday basis. There are already some products running on, but within limits. I'm also not saying that it should run on Unix, Linux or FreeBSD. I'm not argueing about it either. It's a Windows based tool, plain and simple. You can connect, of course, to other systems running MySQL. perhaps messed up? The page is tested with IE and Opera and looks fine in both. Thank you for asking. I am using Mozilla, Opera and Konqueror. It looks fine in all3 browsers, but it was not clear for me that the second note was only for the InterXpress for Firebird product. Thank you for confirming that at least the pages look the same :-) I believ it would help to list them up on separate pages or perhaps change the background colour slightly to make it more obvious. Perhaps a more detailed difference would enhance the page. Thank you for the suggestion. The module system is probably a nice idea, but shouldnt that allow to offer the drivers for a reasonable price ? You are mixing two things, the driver product has nothing to do with Database Workbench. I found 435$ for the full module software to expensive. I can get similar and better software easily for under 100$ - ( sometimes even free) which offer more features and reliability than your product. I'm surprised you can say something like that just a few hours after release :-) Take another look: if you want to use Database Workbench for MySQL only, you can purchase a license for Euro 109,- not Euro 435,- ... If you want more database engines supported, the license price will increase. The most critical point to me is that the combination of closed source/ features/price seesm to be a bit unlucky. Did you take a look at the features available? First it doesnt seem to be an open source product (and please correct me if I am wrong). Secodnly, I dont like the idea, that I have to buy for what is supposed to be an addon. That should be included. And the modul type should easily allow cheaper prices for the whole package. I'm unsure what you mean by addon in this particular context? Have a look at other products and let me know what you think; www.business.com/search/rslt_default.asp?r4=tquery=database +development+tool (over 100 tools introduced www.10xsoftware.com/products/ I'm unsure if any of their products are comparable. www.fabforce.net Case tool, without after-case development tools. www.minq.se Nice tool, but different feature set, $99. www.dbtools.com.br/EN/dbmanagerpro.php The enterprise version isn't available for purchase yet. www.ems-hitech.com $135 - which a lot less features, but does have a case tool (visual design) www.quest.com/solutions/database_management_products.asp If you have more than $2000,- then there are some nice tools available, but none of them MySQL related. For example, their test-data-generator tool is more expensive then Database Workbench including all modules. (10 tools introduced) www.linuxadvisor.net/doc/12029 ? www.macosguru.de/ MacOS tool. Looks pretty nice. www.easysoft.com/solutions/osx.phtml ODBC driver for MacOS. www.hallogram.com/dezign/ DeZign is a case tool There are 100^s more. Not all of them are open source. It depends strongly on which platform you are running the software. There are more - you're right about that. Most of the ones you listed are not open source though. And there are few good open source products out there - I'm not saying there aren't. Not at all. If you prefer open source, the one thing I can say: stick with it. There's no law telling you to buy tools. But have you tried Database Workbench before you wrote this email? If you're missing features, feel free to write me and they might be included in the next version. The InterBase and Firebird modules of Database Workbench have been successfull for quite some years and include many useful tools not offered in order applications. The per database engine modules allow you to make a difference between a one size fits all product, but still have the ability to use tools like the Test Data Generator The Test data generator is nowadays integrated in quite a few tools. or Schema Migration tool without the need to buy licenses for hundreds or
query problem
Hi All MySQL users I am having a slight problem and getting inot a bit of a tizz. I have two tables and I am trying to produce a dropdown select menu whilst creating a join. The two tables are as follows... CREATE TABLE itinerary ( i_id int(3) unsigned NOT NULL auto_increment, i_title varchar(100) default NULL, i_description mediumtext, i_category int(3) unsigned default NULL, i_ordernumber int(3) unsigned default NULL, i_text mediumtext, i_image1 varchar(255) default NULL, i_image2 varchar(255) default NULL, i_image3 varchar(255) default NULL, PRIMARY KEY (i_id), UNIQUE KEY i_id (i_id), KEY i_id_2 (i_id) ) TYPE=MyISAM; and CREATE TABLE dates2 ( d_id int(3) unsigned NOT NULL auto_increment, d_objid int(3) unsigned default NULL, d_startdate date default NULL, d_enddate date default NULL, PRIMARY KEY (d_id), UNIQUE KEY d_id (d_id), KEY d_id_2 (d_id) ) TYPE=MyISAM; The two fields that are associated are dates2.d_objid and itinerary.i_id what I want to do is pull out the dates for a selected itinerary.i_id and present them in a select menu. I am using php but it's been a while and have only got so far inot a tizz :) Can anyone shed a little light on the quesry or a bit of guidance on how to approach this? Thank you Andrew This is what I have used for the query and the php ? echo select name=\StartDate\ size=\1\ class='menuForm'; $result=mysql_query(select dates2.d_objid ,dates2.d_startdate,dates2.d_enddate from dates2 left join itinerary.i_id on dates2.d_objid = itinerary.i_id where dates2.d_startdate and dates2.d_enddate= . $Date . group by dates2.d_startdate); while ($row = mysql_fetch_array($result)) { $d_startdate=$row['StartDate']; $d_enddate=$row['EndDate']; echo option value=\$Date\$d_startdate - $d_enddate /option; } echo /select; ? which is clearly wrong somewhere :( Any help would be nice Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: ANN: Database Workbench 2.4 released
Hi List members, I just forward this for completeness of the thread . Best regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql -- Forwarded Message -- Subject: Re: ANN: Database Workbench 2.4 released Date: Tuesday 11 November 2003 23:47 From: Nils Valentin [EMAIL PROTECTED] To: Martijn Tonies [EMAIL PROTECTED] Hi Martjin, On Monday 10 November 2003 19:50, Martijn Tonies wrote: Hi Nils, How do I install this tool on a Unix, Linux or BSD based machine ? The tool itself is Windows based - however, people are running it under Wine and similar emulators. See http://www.upscene.com/documentation/dbw_freebsd_wine.htm for an example. That was 1 customer, yes thats right, but you dont want to argue that the performance will be the same ;-). Wine is a good product, but it is more or less for an emergency and not for the everyday basis. There are already some products running on, but within limits. Why does it say on the page 1) Receiving upgrades is not time limited. and 2) The license itself isn't time limited - but there is a 20% yearly renewal fee for maintenance and support (updates etc) on the driver after the first year. The (2) part comes from a different product: InterXpress for Firebird and is not related to Database Workbench. There's a different header on the webpage above that part. I found this a) confusing and b) I would prefer a one time fee. If you read it like that, it actually is confusing :-) ... Is your web-page view perhaps messed up? The page is tested with IE and Opera and looks fine in both. Thank you for asking. I am using Mozilla, Opera and Konqueror. It looks fine in all3 browsers, but it was not clear for me that the second note was only for the InterXpress for Firebird product. I believ it would help to list them up on separate pages or perhaps change the background colour slightly to make it more obvious. The module system is probably a nice idea, but shouldnt that allow to offer the drivers for a reasonable price ? You are mixing two things, the driver product has nothing to do with Database Workbench. I found 435$ for the full module software to expensive. I can get similar and better software easily for under 100$ - ( sometimes even free) which offer more features and reliability than your product. I'm surprised you can say something like that just a few hours after release :-) Take another look: if you want to use Database Workbench for MySQL only, you can purchase a license for Euro 109,- not Euro 435,- ... If you want more database engines supported, the license price will increase. The most critical point to me is that the combination of closed source/ features/price seesm to be a bit unlucky. First it doesnt seem to be an open source product (and please correct me if I am wrong). Secodnly, I dont like the idea, that I have to buy for what is supposed to be an addon. That should be included. And the modul type should easily allow cheaper prices for the whole package. Have a look at other products and let me know what you think; www.business.com/search/rslt_default.asp?r4=tquery=database +development+tool (over 100 tools introduced www.10xsoftware.com/products/ www.fabforce.net www.minq.se www.dbtools.com.br/EN/dbmanagerpro.php www.ems-hitech.com www.quest.com/solutions/database_management_products.asp (10 tools introduced) www.linuxadvisor.net/doc/12029 www.macosguru.de/ www.easysoft.com/solutions/osx.phtml www.hallogram.com/dezign/ There are 100^s more. Not all of them are open source. It depends strongly on which platform you are running the software. If you have the luck to find customers which are willing to pay this, than you are probably the most terrific sales person around for a while. All power to you. The InterBase and Firebird modules of Database Workbench have been successfull for quite some years and include many useful tools not offered in order applications. The per database engine modules allow you to make a difference between a one size fits all product, but still have the ability to use tools like the Test Data Generator The Test data generator is nowadays integrated in quite a few tools. or Schema Migration tool without the need to buy licenses for hundreds or thousands of Euros. That might be correct, but I still believe that you must be a terrific sales person. Open source products can be installed on unlimited machines without buying any licenses ;-) (if you want to do so). Dont get me wrong if it is a good product it might well be worth paying for it, but right now I cant see that yet when I compare this to the 100^s of open source products out there. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com kind regards -- Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/
Fwd: Re: ANN: Database Workbench 2.4 released
Hi List members, I just forward this for completeness of the thread . Best regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql -- Forwarded Message -- Subject: Re: ANN: Database Workbench 2.4 released Date: Monday 10 November 2003 21:08 From: Martijn Tonies [EMAIL PROTECTED] To: [EMAIL PROTECTED] Hi Nils, How do I install this tool on a Unix, Linux or BSD based machine ? The tool itself is Windows based - however, people are running it under Wine and similar emulators. See http://www.upscene.com/documentation/dbw_freebsd_wine.htm for an example. That was 1 customer, yes thats right, but you dont want to argue that the performance will be the same ;-). From what I've heard, the performance is good. Wine is a good product, but it is more or less for an emergency and not for the everyday basis. There are already some products running on, but within limits. I'm also not saying that it should run on Unix, Linux or FreeBSD. I'm not argueing about it either. It's a Windows based tool, plain and simple. You can connect, of course, to other systems running MySQL. perhaps messed up? The page is tested with IE and Opera and looks fine in both. Thank you for asking. I am using Mozilla, Opera and Konqueror. It looks fine in all3 browsers, but it was not clear for me that the second note was only for the InterXpress for Firebird product. Thank you for confirming that at least the pages look the same :-) I believ it would help to list them up on separate pages or perhaps change the background colour slightly to make it more obvious. Perhaps a more detailed difference would enhance the page. Thank you for the suggestion. The module system is probably a nice idea, but shouldnt that allow to offer the drivers for a reasonable price ? You are mixing two things, the driver product has nothing to do with Database Workbench. I found 435$ for the full module software to expensive. I can get similar and better software easily for under 100$ - ( sometimes even free) which offer more features and reliability than your product. I'm surprised you can say something like that just a few hours after release :-) Take another look: if you want to use Database Workbench for MySQL only, you can purchase a license for Euro 109,- not Euro 435,- ... If you want more database engines supported, the license price will increase. The most critical point to me is that the combination of closed source/ features/price seesm to be a bit unlucky. Did you take a look at the features available? First it doesnt seem to be an open source product (and please correct me if I am wrong). Secodnly, I dont like the idea, that I have to buy for what is supposed to be an addon. That should be included. And the modul type should easily allow cheaper prices for the whole package. I'm unsure what you mean by addon in this particular context? Have a look at other products and let me know what you think; www.business.com/search/rslt_default.asp?r4=tquery=database +development+tool (over 100 tools introduced www.10xsoftware.com/products/ I'm unsure if any of their products are comparable. www.fabforce.net Case tool, without after-case development tools. www.minq.se Nice tool, but different feature set, $99. www.dbtools.com.br/EN/dbmanagerpro.php The enterprise version isn't available for purchase yet. www.ems-hitech.com $135 - which a lot less features, but does have a case tool (visual design) www.quest.com/solutions/database_management_products.asp If you have more than $2000,- then there are some nice tools available, but none of them MySQL related. For example, their test-data-generator tool is more expensive then Database Workbench including all modules. (10 tools introduced) www.linuxadvisor.net/doc/12029 ? www.macosguru.de/ MacOS tool. Looks pretty nice. www.easysoft.com/solutions/osx.phtml ODBC driver for MacOS. www.hallogram.com/dezign/ DeZign is a case tool There are 100^s more. Not all of them are open source. It depends strongly on which platform you are running the software. There are more - you're right about that. Most of the ones you listed are not open source though. And there are few good open source products out there - I'm not saying there aren't. Not at all. If you prefer open source, the one thing I can say: stick with it. There's no law telling you to buy tools. But have you tried Database Workbench before you wrote this email? If you're missing features, feel free to write me and they might be included in the next version. The InterBase and Firebird modules of Database Workbench have been successfull for quite some years and include many useful tools not offered in order applications. The per database engine modules allow you to make a difference between a one size fits all product, but still have the ability to use tools
Fwd: Re: ANN: Database Workbench 2.4 released
Hi List members, I just forward this for completeness of the thread . Best regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql -- Forwarded Message -- Subject: Re: ANN: Database Workbench 2.4 released Date: Wednesday 12 November 2003 01:00 From: Nils Valentin [EMAIL PROTECTED] To: Martijn Tonies [EMAIL PROTECTED] Hi Martijn, thanks for the reply. You replied to me offline, so I keep it that way (if you want it so;-) I already mentioned that I like Open source products, there is a good reason for that, which I would like to explain. When I buy a product I want to use it for a long time. Ideally I want to be able to modify things within the software to my needs. An I believe that open source products are good for this, for other things as well. I installed the software on a Windows 98 machine. The lowest speck machine which I have (I always do this with new software). The speed seems to be reasonable (nothing to complain here ;-) I accessed a few Mysql databases when I got an Abstract error. I am not sure what caused the software to choke, but I had to restart my machine. When you choose the Blob Editor and try to access a database which you dont have access for then a screen tells me Access violation at address 4002F584 in module RTL70.BPL Read of address While the software contains possibly all kind of tools and features I dont think that the design of the interface is intuitive. The File menu has a point called Preferences. Is there a point in listing up the single sub sections when they lead to the same window containing all properties ? Also the functions should be grouped context sensitive. ( I believe DbVisualizer is a good sample here). See how the context of the menu changes with the user interactions (try the export functions). I would perhaps place the tool buttons on a vertical bar between the left and the right window, letting the image icons change depending which tool you have chosen to see. the tools would be shown in tabs, so that the user sees basically only 1 tab at a time (unless he wants to see more windows than he should be able to cascade the windows or arrange them on the screen). I dont believe in Error No xxx messages. The window should display the meaning for the user. f.e Not: error 1045 but: This combination of username, hostname,db and password is invalid. Also I dont like popup windows reminding me that I have to register the software evrytime when I start the software or click on a special feature. That can be mentioned decently in the statusbar once or always, but NO POPUPS - hope you got this ;-) The Wizard screens remind me of another software called SQLyog (available here http://www.webyog.com/) which was freely available for quite a while ;-) , its right now 30 - 50$. Perhaps the mixture of tools is unique the way you present them ;-) , but I certainly think that the GUI could need a good and thoroughly sorting for the functions. Think about it this way KISS - Keep it stupid simple. To many options is like trying to find your car key in the forest (or so ;-). Anyway thats my opinion. I could probaly go on like this for a while... At the end its the user who decides which products he likes and my result is that this product still needs lots of improvements regarding the GUIs intuitivity (and the pricing and the license model of course;-). Best regards Nils Valentin Tokyo/Japan On Monday 10 November 2003 21:08, you wrote: Hi Nils, How do I install this tool on a Unix, Linux or BSD based machine ? The tool itself is Windows based - however, people are running it under Wine and similar emulators. See http://www.upscene.com/documentation/dbw_freebsd_wine.htm for an example. That was 1 customer, yes thats right, but you dont want to argue that the performance will be the same ;-). From what I've heard, the performance is good. Wine is a good product, but it is more or less for an emergency and not for the everyday basis. There are already some products running on, but within limits. I'm also not saying that it should run on Unix, Linux or FreeBSD. I'm not argueing about it either. It's a Windows based tool, plain and simple. You can connect, of course, to other systems running MySQL. perhaps messed up? The page is tested with IE and Opera and looks fine in both. Thank you for asking. I am using Mozilla, Opera and Konqueror. It looks fine in all3 browsers, but it was not clear for me that the second note was only for the InterXpress for Firebird product. Thank you for confirming that at least the pages look the same :-) I believ it would help to list them up on separate pages or perhaps change the background colour slightly to make it more obvious. Perhaps a more detailed difference would enhance the page. Thank you for the suggestion. The module system is probably a nice idea, but shouldnt that allow
Fwd: Re: ANN: Database Workbench 2.4 released
Hi List members, I just forward this for completeness of the thread . Best regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql -- Forwarded Message -- Subject: Re: ANN: Database Workbench 2.4 released Date: Monday 10 November 2003 22:15 From: Martijn Tonies [EMAIL PROTECTED] To: [EMAIL PROTECTED] Hi Nils, thanks for the reply. You replied to me offline, so I keep it that way (if you want it so;-) Actually, you started it in the last reply :-) btw, thank you for your reply, I appreciate your comments. I already mentioned that I like Open source products, there is a good reason for that, which I would like to explain. When I buy a product I want to use it for a long time. Ideally I want to be able to modify things within the software to my needs. An I believe that open source products are good for this, for other things as well. They sure are good for that. Although I'm unsure if everything does it that way - on a personal account, I am using open source products as well, but never have felt the need to modify a single one of them. I installed the software on a Windows 98 machine. The lowest speck machine which I have (I always do this with new software). The speed seems to be reasonable (nothing to complain here ;-) I accessed a few Mysql databases when I got an Abstract error. If you could retrace your steps and explain what you're doing here, than it would be very useful. If you could give me simple instructions like step 1, do this, step 2, do that - I would very much like to have them. When you choose the Blob Editor and try to access a database which you dont have access for then a screen tells me Access violation at address 4002F584 in module RTL70.BPL Read of address Really? Can you explain in more detail? While the software contains possibly all kind of tools and features I dont think that the design of the interface is intuitive. The File menu has a point called Preferences. Is there a point in listing up the single sub sections when they lead to the same window containing all properties ? Actually, it has always been that way and no-one complained. Seems I gotta change the behaviour then :-) Also the functions should be grouped context sensitive. ( I believe DbVisualizer is a good sample here). See how the context of the menu changes with the user interactions (try the export functions). The main menu (top of the screen) is fixed. The context sensitive menus in the Enterprise Manager and Database Navigator change all the time. I would perhaps place the tool buttons on a vertical bar between the left and the right window, letting the image icons change depending which tool you have chosen to see. the tools would be shown in tabs, so that the user sees basically only 1 tab at a time (unless he wants to see more windows than he should be able to cascade the windows or arrange them on the screen). This is more of a UI preference. Many people like the current interface... I dont believe in Error No xxx messages. The window should display the meaning for the user. f.e Not: error 1045 but: This combination of username, hostname,db and password is invalid. Could you please clarify where this pops up? Would this error come straight from the MySQL client? Also I dont like popup windows reminding me that I have to register the software evrytime when I start the software or click on a special feature. That can be mentioned decently in the statusbar once or always, but NO POPUPS - hope you got this ;-) Sorry, that's a trial version... The Wizard screens remind me of another software called SQLyog (available here http://www.webyog.com/) which was freely available for quite a while ;-) , its right now 30 - 50$. Haha - I've seen that tool. You know what the biggest difference is between the wizards? My graphics were drawn by me while the SQLyog graphics were taken/copied from the Microsoft SQL Server Enterprise Manager tool. Every graphic in it. Perhaps the mixture of tools is unique the way you present them ;-) , but I certainly think that the GUI could need a good and thoroughly sorting for the functions. Think about it this way KISS - Keep it stupid simple. To many options is like trying to find your car key in the forest (or so ;-). Anyway thats my opinion. I could probaly go on like this for a while... At the end its the user who decides which products he likes and my result is that this product still needs lots of improvements regarding the GUIs intuitivity (and the pricing and the license model of course;-). Things improve over time, I believe that every release, beta-testers come up with new things that they like so and so. Of course, one can never argue about taste - I actually do get quite some comments saying that many tools can be found exactly where people expect them to be... :-) Could you please reply with a message with more info
Fwd: Re: ANN: Database Workbench 2.4 released
Hi List members, I just forward this for completeness of the thread . Best regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql -- Forwarded Message -- Subject: Re: ANN: Database Workbench 2.4 released Date: Wednesday 12 November 2003 01:26 From: Nils Valentin [EMAIL PROTECTED] To: Martijn Tonies [EMAIL PROTECTED] Hi Martijn , I was not aware of that I started to post offlist if its alright with you I post it also to the list. Give me moment. I will have dinner and then come back to each point so that you can follow it up and hopefully improve the product ;-). (I do this off the list from now) Nils Valentin Tokyo/Japan On Monday 10 November 2003 22:15, you wrote: Hi Nils, thanks for the reply. You replied to me offline, so I keep it that way (if you want it so;-) Actually, you started it in the last reply :-) btw, thank you for your reply, I appreciate your comments. I already mentioned that I like Open source products, there is a good reason for that, which I would like to explain. When I buy a product I want to use it for a long time. Ideally I want to be able to modify things within the software to my needs. An I believe that open source products are good for this, for other things as well. They sure are good for that. Although I'm unsure if everything does it that way - on a personal account, I am using open source products as well, but never have felt the need to modify a single one of them. I installed the software on a Windows 98 machine. The lowest speck machine which I have (I always do this with new software). The speed seems to be reasonable (nothing to complain here ;-) I accessed a few Mysql databases when I got an Abstract error. If you could retrace your steps and explain what you're doing here, than it would be very useful. If you could give me simple instructions like step 1, do this, step 2, do that - I would very much like to have them. When you choose the Blob Editor and try to access a database which you dont have access for then a screen tells me Access violation at address 4002F584 in module RTL70.BPL Read of address Really? Can you explain in more detail? While the software contains possibly all kind of tools and features I dont think that the design of the interface is intuitive. The File menu has a point called Preferences. Is there a point in listing up the single sub sections when they lead to the same window containing all properties ? Actually, it has always been that way and no-one complained. Seems I gotta change the behaviour then :-) Also the functions should be grouped context sensitive. ( I believe DbVisualizer is a good sample here). See how the context of the menu changes with the user interactions (try the export functions). The main menu (top of the screen) is fixed. The context sensitive menus in the Enterprise Manager and Database Navigator change all the time. I would perhaps place the tool buttons on a vertical bar between the left and the right window, letting the image icons change depending which tool you have chosen to see. the tools would be shown in tabs, so that the user sees basically only 1 tab at a time (unless he wants to see more windows than he should be able to cascade the windows or arrange them on the screen). This is more of a UI preference. Many people like the current interface... I dont believe in Error No xxx messages. The window should display the meaning for the user. f.e Not: error 1045 but: This combination of username, hostname,db and password is invalid. Could you please clarify where this pops up? Would this error come straight from the MySQL client? Also I dont like popup windows reminding me that I have to register the software evrytime when I start the software or click on a special feature. That can be mentioned decently in the statusbar once or always, but NO POPUPS - hope you got this ;-) Sorry, that's a trial version... The Wizard screens remind me of another software called SQLyog (available here http://www.webyog.com/) which was freely available for quite a while ;-) , its right now 30 - 50$. Haha - I've seen that tool. You know what the biggest difference is between the wizards? My graphics were drawn by me while the SQLyog graphics were taken/copied from the Microsoft SQL Server Enterprise Manager tool. Every graphic in it. Perhaps the mixture of tools is unique the way you present them ;-) , but I certainly think that the GUI could need a good and thoroughly sorting for the functions. Think about it this way KISS - Keep it stupid simple. To many options is like trying to find your car key in the forest (or so ;-). Anyway thats my opinion. I could probaly go on like this for a while... At the end its the user who decides which products he likes and my result is
undefined reference to `mysql_connect'
Dear Frineds, I need to connect MySql through C. I am able to connect when compile my ajit.c file with option '-lmysqlclient'. But when I am compiling a bigger module (sqwebmail) I dont know where to place this option in the Makefile. I am getting error undefined reference to `mysql_connect' no mattere where ever I place the option '-lmysqlclient' Anyone out there to help out? Thank you Ajit __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where do I download the dynamic libraries for other UNIX OS's?
Hello, Where can I download the shared library libmysqlclient.so for Solaris, AIX and HP-UX? I only see download links for Linux and Windows and the servers don't appear to ship with these libraries. Thank you in advance for your help! Dave Ritter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing mysql.sock
Michael Satterwhite wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This has got to be a common question, but I'd really appreciate a little help. I recently reinstalled my Linux (SuSE 8.2). I *KNOW* I don't have a cron run that deletes this. When I try to start mysql, I get the message Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Using locate, I see that, indeed, it is not there. I'm using MySQL 4.1.0 I've tried running mysqld_safe. It tries to start the mysqld, then I immediately get the message that mysqld ended. It doesn't start. How do I get it going again? Check the error logs in the data directory to see why the server is stopping. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL [Q] how to migrate 4.0 - 4.1
Andrey Kotrekhov [EMAIL PROTECTED] wrote: Hi, All. Other question is: Can I create case sensitive field with right ordering of national characters. Take a look at collation name: ci at the end of the collation name means case insensitive, cs - case sensitive, bin - binary: http://www.mysql.com/doc/en/Charset-MySQL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unicode order
Hi, I was trying to use ORDER BY on a unicode field. I can use 2 types of collation: binary and default. Both give wrong results. The binary puts accented letters on the end of the list, the default puts all of them between 'A' and 'B'. What I'd need is central-european collation support for the unicode character set, or something which can do the same (at reasonable speed). thanks, Agoston Horvath -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strategies for optimizing a read-only table
Matt W wrote: Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Just because a file is in the cache now, it doesn't follow that it will be in the cache 5 minutes from now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB lock in share mode on union
How do I lock rows in a union query so that I know they won't change during the rest of my transaction? I want to do the following query, using LOCK IN SHARE MODE: (select id from table1 where id 1) union (select id from table2 where id 1); If I try: (select id from table1 where id 1 LOCK IN SHARE MODE) union (select id from table2 where id 1); it doesn't appear to do the locking. Another process can delete a record from table1 which was contained in the result. It appears that I see a consistent snapshot, and the record is gone after I commit. (See below.) I get the same result if I use FOR UPDATE. MySQL doesn't allow me to put LOCK IN SHARE MODE after the second select or after the whole union. I'm using MySQL 4.0.13-nt-log. -- Example follows In the following, table1 and table2 have the following structure: CREATE TABLE `table1` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB mysql begin; Query OK, 0 rows affected (0.00 sec) mysql (select id from table1 where id 1 LOCK IN SHARE MODE) union (select id from table2 where id 1); ++ | id | ++ | 3 | | 2 | ++ 2 rows in set (0.00 sec) *** another process does: delete from table1 where id=3; mysql (select id from table1 where id 1 lock in share mode) union (select id from table2 where id 1); ++ | id | ++ | 3 | | 2 | ++ 2 rows in set (0.01 sec) mysql commit; Query OK, 0 rows affected (0.00 sec) mysql (select id from table1 where id 1 lock in share mode) union (select id from table2 where id 1); ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
offline replication with binlogs
Hello again... I am transferring binlogs between two networks that are not connected as an offline replication solution. I have two databases which I have configured the my.ini file to create binlog entries for. Do both databases get written to the same log or is there a separate log for each database. If both are written to the same log how do you use the mysqlbinlog command to update both databases? Do you just run it twice like this?... mysqlbinlog binlog_name db1 mysqlbinlog binlog_name db2 Any help would be greatly appreciated. Mauro
Re: Missing mysql.sock
On Sat, 8 Nov 2003, Michael Satterwhite wrote: This has got to be a common question, but I'd really appreciate a little help. I recently reinstalled my Linux (SuSE 8.2). I *KNOW* I don't have a cron run that deletes this. When I try to start mysql, I get the message Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Using locate, I see that, indeed, it is not there. I'm using MySQL 4.1.0 I've tried running mysqld_safe. It tries to start the mysqld, then I immediately get the message that mysqld ended. It doesn't start. How do I get it going again? If your server is not running (check with a `ps -aux` call) then check your error logs. If the server IS running however, it's likely that your client tries to connect through /var/lib/mysql/mysql.sock while the server listens somewhere else. To find out if that is the case, run `netstat -u | grep mysql.sock` and see where the server is listening. If the server is listening on, for example /tmp/mysql.sock , then you can make your client use that by editing your ~/.my.cnf (or /etc/my.cnf, or any of the other locations possible) with the row socket = /tmp/mysql.sock under the [client] group (or change the server's by entering the same row with the other path under the [mysqld] group). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE TABLE with 32 MB string corrupts ISAM
Craig Cummings [EMAIL PROTECTED] wrote: Description: When inserting large strings into ISAM table in an UPDATE TABLE command, my perl script generates an Out of memory error. I tracked this down using an SQL script (below). It seems that a 32777225 character string is tolerated, but a string one character larger is not. Worked like a charm for me on v4.0.16. Seems, this bug was fixed in v4.0.11: * Fixed bug in updating BLOB columns with long strings. http://www.mysql.com/doc/en/News-4.0.11.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- 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]
flush-hosts problem with 3.23.58 rpm?
Hi List, I've recently started seeing quite a few errors with MySQL servers I admin, they are all requests to run: mysqladmin flush-hosts The only change I can identify is our upgrade to 3.23.58 (mysql.com provided RPM - running on redhat 7.3) Is anyone aware of an issue with this version or a previous version (we may have been a few releases behind 3.23.x before we upgraded). I am seeing this all over the place and have not been able to figure out why. In lieu of an fix, can anyone recommend good ways to prevent it? Right now I just run flush-hosts from cron every 15 minutes but I do not like this messy solution (plus even running it every 15 minutes we get frequent problems still). Any ideas or advice? Thanks! __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maintaining size of a db
Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. Thanks!! Scott . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grouping
select max(comments.commentid) as tagger, comments.commentref, article.articlesubject, article.articlename, comments.commentfrom from comments LEFT JOIN article ON comments.commentref=article.articleid GROUP BY commentref ORDER by tagger DESC; I have this query above. I lists comments in order from the most active to the least active from articles. The comments returns might be 54 34 53 89 52 32 My problem is, I need another field from that comments table that is returned with the max. There poster's name is commentfrom, and the query is returning the FIRST poster for all the comments related to that specific article, not the last poster. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about MySQL and Oracle.
It depends a lot on the features that are needed. Right now we use mySQL for some things and Orcale for others. The vibes I have seen from both Oracle and MySQL is that for right now MySQL is a complementary product to Oracle. Soon I think MySQL will be right up there with Oracle. Check the following link to see a databse comparison done by eweek magazine. I hope this helps. http://www.eweek.com/article2/0,4149,1184846,00.asp Scott Pippin [EMAIL PROTECTED]
Re: Strategies for optimizing a read-only table
Harald Fuchs wrote: In article [EMAIL PROTECTED], gerald_clark [EMAIL PROTECTED] writes: Matt W wrote: Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Just because a file is in the cache now, it doesn't follow that it will be in the cache 5 minutes from now. If it gets thrown out of the FS cache, you don't have enough RAM, and then a ramdisk won't help much. If you have more data than ram and don't access that file's data often enough it will eventually be forced out of cache. The question then is: Is having this particular table in ram more important than having that other data in cache? You are probably right. If it is being forced out of cache, then it is probably not being accessed often enough to belong in a ram disk, and the cache might make better use of the ram. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL benchmarks on the G5: Jaguar vs. Panther
Look in the support-files directory in the mysql directory. There are a bunch of sample config files that you can look at that named pretty obviously: small,medium,large,huge. Each one has a description on what kind of system these should be used on, based on RAM and usage. For instance, huge is for systems with 512MB. If you have 2GB on your box, you may want to base your settings on the huge config file, which is for systems with 1-2GB. I'm no expert on the settings so you may have to dig in the online manual to customize for your needs. On Friday, November 7, 2003, at 06:32 PM, Jan Pieter Kunst wrote: At 13:21 -0500 07-11-2003, Gabriel Ricard wrote: [1.8 GHz G5:] select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys + 0.00 cusr 0.00 csys = 92.68 CPU) [dual 2.0 GHz G5:] select: Total time: 146 wallclock secs (42.52 usr 33.74 sys + 0.00 cusr 0.00 csys = 76.26 CPU) How the select portion of the test could be 10x slower, I'm not sure. Have you tuned MySQL at all? key_buffer, etc? Well, I'm looking for advice on how I should tune MySQL. These are the relevant parts of the my.cnf I used: [mysqld] skip-locking set-variable = key_buffer=64M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = net_buffer_length=8K set-variable = record_buffer=1M set-variable = thread_cache=8 set-variable = myisam_sort_buffer_size=8M log-bin server-id = 1 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M If you have any advice on what I could change here to get faster selects, I'm all ears. Thanks in advance, Jan Pieter Kunst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Processes Closed, but max_connections exceeded...
MAX_CONNECTIONS_PER_HOUR ? Now, I set the MAX_CONNECTIONS, but I didn't know about MAX_CONNECTIONS_PER_HOUR. Still, the problem has persisted when left for a day or two without restarting. In fact, the issues only go away when the server is restarted (though I did NOT try the flush). Thank you for the pointers. I will see what I can dig up. Thx, Joe Victoria Reznichenko wrote: Joe Lewis [EMAIL PROTECTED] wrote: I've got a question for you. I've got a MySQL db running in FreeBSD 4.8, and have encountered the following problem. When I try to connect to a database by a specific user, I get the following response : #mysql -p -u comet Enter password: ERROR 1226: User 'comet' has exceeded the 'max_connections' resource (current value: 100) Now, that is exactly what I'd expect to see, IF the user had 100 connections. Instead, when viewing the processlist, I get : If you set up MAX_CONNECTIONS_PER_HOUR 100 for user it means that user can open 100 new connections per our. #mysqladmin -p processlist Enter password: +-+--+---++-+--+---+---+ | Id | User | Host | db | Command | Time | State | Info | +-+--+---++-+--+---+---+ | 469 | root | localhost || Query | 0| | show processlist | +-+--+---++-+--+---+---+ I think that the code that had established all of those connections didn't properly dispose of the connections (a PHP script that one of our clients is using). Is there a way to clear those? Execute FLUSH USER_RESOURCES command or mysqladmin reload to flush current values. You can read about limiting resources at: http://www.mysql.com/doc/en/User_resources.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- 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: WANTED: lots of data
Mark, I have not that many records but I can offer you ~29 million rows of wordwide meteorological data. They can be bzip2'd to 300 MB and are very tricky to summarize in a meaningful way. If you're interested pls mail me off-line. Regards, Thomas Spahni On Sat, 8 Nov 2003, Mark Horton wrote: I'm working on some software that will generate all sorts of statistics on large volumes of data. I'm looking for data to experiment on. I'd like 100 million - 1 billion records. These records can be anything from log files to database records, or whatever. I just need tons of data. If you have lots of data and have been interested in getting it summarized, rolled-up, aggregated so that you can have nice summary reports on it then let me know. BTW, I'm not trying to sell anything. This software is just an idea and I'm looking for lots of real world data to test with. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
periodic corruption problem
I am running MySQL 4.0.16 on a Red Hat 7.3 server. I am having problems with tables periodically getting corrupted. The one it happens most often on is a table I use for logging various status messages. This table receives mostly inserts, and very few selects. It is about 400MB in size, with about 1.3M rows. I just had it happen to another table, that receives mostly selects, and few inserts or updates. It is only about 60MB in size, with about 95k rows. Both of these tables rarely have deletes. The last time, I believe the error message said something about the index being the wrong size. Sorry I didn't write it down, but I was in a hurry to get things working again. Can anyone provide some help on this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: periodic corruption problem
my experience with corrupted tables is usually followed by a failed harddrive aka the disk may be diing ... -- Quoting Jon Wagoner [EMAIL PROTECTED]: I am running MySQL 4.0.16 on a Red Hat 7.3 server. I am having problems with tables periodically getting corrupted. The one it happens most often on is a table I use for logging various status messages. This table receives mostly inserts, and very few selects. It is about 400MB in size, with about 1.3M rows. I just had it happen to another table, that receives mostly selects, and few inserts or updates. It is only about 60MB in size, with about 95k rows. Both of these tables rarely have deletes. The last time, I believe the error message said something about the index being the wrong size. Sorry I didn't write it down, but I was in a hurry to get things working again. Can anyone provide some help on this problem? -- 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 benchmarks on the G5: Jaguar vs. Panther
This is what I'm currently using on the dual 2GHz w/4GB of RAM: set-variable = query_cache_type=1 set-variable = query_cache_size=1024M set-variable = net_buffer_length=32760 set-variable = bulk_insert_buffer_size=32M set-variable= key_buffer=1024M set-variable= join_buffer_size=128M set-variable= max_allowed_packet=8M set-variable= table_cache=1024 set-variable= myisam_sort_buffer_size=1024M set-variable= thread_concurrency=16 set-variable = thread_cache_size=16 set-variable = sort_buffer=8M set-variable = read_rnd_buffer_size=8M set-variable= record_buffer=8M If you're using only MyISAM tables for your data, then key_buffer should generally have as much RAM as it can have (up to the point where it makes sense, i.e., only has much RAM as is needed to store your index data). myisam_sort_buffer_size ought to be about 1/4 of your RAM. If you're using InnoDB as well, or if you use InnoDB tables only, then you should give innodb_buffer_pool_size a good portion of your RAM, and perhaps 16-32MB or so for innodb_additional_mem_pool_size. - Gabriel On Friday, November 7, 2003, at 06:32 PM, Jan Pieter Kunst wrote: At 13:21 -0500 07-11-2003, Gabriel Ricard wrote: [1.8 GHz G5:] select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys + 0.00 cusr 0.00 csys = 92.68 CPU) [dual 2.0 GHz G5:] select: Total time: 146 wallclock secs (42.52 usr 33.74 sys + 0.00 cusr 0.00 csys = 76.26 CPU) How the select portion of the test could be 10x slower, I'm not sure. Have you tuned MySQL at all? key_buffer, etc? Well, I'm looking for advice on how I should tune MySQL. These are the relevant parts of the my.cnf I used: [mysqld] skip-locking set-variable = key_buffer=64M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = net_buffer_length=8K set-variable = record_buffer=1M set-variable = thread_cache=8 set-variable = myisam_sort_buffer_size=8M log-bin server-id = 1 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M If you have any advice on what I could change here to get faster selects, I'm all ears. Thanks in advance, Jan Pieter Kunst -- 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: max_user_connections problem after upgrading
That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: periodic corruption problem
It's been going on for months, so that doesn't seem very likely. On the other hand, we have had 2 or 3 other hard drives go bad with other servers at this host, so I'm not ruling it out. -Original Message- From: Big Brother [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:04 PM To: Jon Wagoner Cc: [EMAIL PROTECTED] Subject: Re: periodic corruption problem my experience with corrupted tables is usually followed by a failed harddrive aka the disk may be diing ... -- Quoting Jon Wagoner [EMAIL PROTECTED]: I am running MySQL 4.0.16 on a Red Hat 7.3 server. I am having problems with tables periodically getting corrupted. The one it happens most often on is a table I use for logging various status messages. This table receives mostly inserts, and very few selects. It is about 400MB in size, with about 1.3M rows. I just had it happen to another table, that receives mostly selects, and few inserts or updates. It is only about 60MB in size, with about 95k rows. Both of these tables rarely have deletes. The last time, I believe the error message said something about the index being the wrong size. Sorry I didn't write it down, but I was in a hurry to get things working again. Can anyone provide some help on this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help on my query statement
Similarly, you can also use GROUP BY SELECT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY GROUP BY WHAT The benefit of using GROUP BY as opposed to DISTINCT is that you can use the set of functions that work on groups like COUNT, SUM, MIN, MAX, etc... SELECT WHAT, COUNT(*) FROM TRANSACTION WHERE REGDATE Now()-INTERVAL 15 DAY GROUP BY WHAT Would let you know how many times WHAT happened 15 days ago. Regards, Erik Osterman http://osterman.com/ -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Sunday, November 09, 2003 10:06 AM To: [EMAIL PROTECTED] Cc: Admin-Stress Subject: Re: help on my query statement * Admin-Stress I have a table called TRANSACTION, it has some fields : REGDATE - date of the transaction WHAT - a string (NOT UNIQUE) CUSTOMER - customer code I want to query all WHAT that the date of the transaction was 15 days before. So, my sql query is : SELECT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY But, because of WHAT is not unique, then I could receive some duplicates (customer can send some WHAT in the same REGDATE). How can I query only the unique WHAT ? I need to list all WHAT (uniquely) that the date transaction was 15 days before. Looks like you need the DISTINCT keyword: URL: http://www.mysql.com/doc/en/Selecting_columns.html#IDX391 SELECT DISTINCT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY -- Roger -- 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: Aliases
However, you can use HAVING. HAVING is post-processed, in a brute force method (no indexes can be used). Select invno, invdate, invamt, left(invdate,2) as month from salesfile HAVING month = '01' But I would use Roger's example since it can take advantage of indexes. Regards, Erik Osterman -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Sunday, November 09, 2003 6:59 AM To: [EMAIL PROTECTED] Cc: David Katz Subject: Re: Aliases * David Katz I am trying to do a select statement where I am using one of the aliases in the where clause. I keep getting an error that the field does not exist. From the manual: Note that standard SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. URL: http://www.mysql.com/doc/en/Problems_with_alias.html example: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where month = '01' MySql keeps telling me that month is not a field. Is there a way to do this? Yes, simply repeat the expression: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where left(invdate,2) = '01' -- Roger -- 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: max_user_connections problem after upgrading
Hi, Have you tried netstat -a on the box with the MySQL server? This command (Linux) will show what is connected to where, and will help you double check that there really aren't any open connections to the server. Thanks, Mike -Original Message- From: Henrik Skotth [mailto:[EMAIL PROTECTED] Sent: 10 November 2003 18:54 To: [EMAIL PROTECTED] Subject: Re: max_user_connections problem after upgrading That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: periodic corruption problem
I had a similar problem with an Oracle database once. It turned out to be some memory in the process of going bad. John -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 2:24 PM To: [EMAIL PROTECTED] Subject: RE: periodic corruption problem It's been going on for months, so that doesn't seem very likely. On the other hand, we have had 2 or 3 other hard drives go bad with other servers at this host, so I'm not ruling it out. -Original Message- From: Big Brother [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:04 PM To: Jon Wagoner Cc: [EMAIL PROTECTED] Subject: Re: periodic corruption problem my experience with corrupted tables is usually followed by a failed harddrive aka the disk may be diing ... -- Quoting Jon Wagoner [EMAIL PROTECTED]: I am running MySQL 4.0.16 on a Red Hat 7.3 server. I am having problems with tables periodically getting corrupted. The one it happens most often on is a table I use for logging various status messages. This table receives mostly inserts, and very few selects. It is about 400MB in size, with about 1.3M rows. I just had it happen to another table, that receives mostly selects, and few inserts or updates. It is only about 60MB in size, with about 95k rows. Both of these tables rarely have deletes. The last time, I believe the error message said something about the index being the wrong size. Sorry I didn't write it down, but I was in a hurry to get things working again. Can anyone provide some help on this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API: undefined reference
Hello MySQL pros worldwide, ~/www -cat seeLog.c #define USE_OLD_FUNCTIONS #include stdio.h #include mysql/mysql.h int main() { MYSQL mysql; MYSQL *mysqldb = NULL; mysqldb = mysql_connect(mysql, geneofcube.net, USERID, PASSWORD); return 0; } ~/www -gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient /tmp/ccEj3tmv.o: In function `main': /tmp/ccEj3tmv.o(.text+0x2a): undefined reference to `mysql_connect' collect2: ld returned 1 exit status ~/www - As shown above, I'm having a problem getting rid of a compilation error (undefined reference) coming out with a very simple c code (seeLog.c) which I wrote for a testing purpose. Got no I idea what I'm doing wrong or missing here. I'd appreciate any of your suggestions. Best, hAj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: periodic corruption problem
I will switch to the secondary server tonight and see if the problems clear up. Thanks for the response. -Original Message- From: John Griffin [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 1:49 PM To: Jon Wagoner; [EMAIL PROTECTED] Subject: RE: periodic corruption problem I had a similar problem with an Oracle database once. It turned out to be some memory in the process of going bad. John -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 2:24 PM To: [EMAIL PROTECTED] Subject: RE: periodic corruption problem It's been going on for months, so that doesn't seem very likely. On the other hand, we have had 2 or 3 other hard drives go bad with other servers at this host, so I'm not ruling it out. -Original Message- From: Big Brother [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:04 PM To: Jon Wagoner Cc: [EMAIL PROTECTED] Subject: Re: periodic corruption problem my experience with corrupted tables is usually followed by a failed harddrive aka the disk may be diing ... -- Quoting Jon Wagoner [EMAIL PROTECTED]: I am running MySQL 4.0.16 on a Red Hat 7.3 server. I am having problems with tables periodically getting corrupted. The one it happens most often on is a table I use for logging various status messages. This table receives mostly inserts, and very few selects. It is about 400MB in size, with about 1.3M rows. I just had it happen to another table, that receives mostly selects, and few inserts or updates. It is only about 60MB in size, with about 95k rows. Both of these tables rarely have deletes. The last time, I believe the error message said something about the index being the wrong size. Sorry I didn't write it down, but I was in a hurry to get things working again. Can anyone provide some help on this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL 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]
Creating random data in a test table based on an existing table
I have an users table for a php application that I am programming. The current users table has about 1500 records. I would like to create a test table (i.e. users_test) with 10,000 records based on random data from the first table (i.e. random first name combined with a random last name, etc...). Is it possible to do this via a MySQL query? Luis R. Lebron Sigmatech, Inc
FW: Strategies for optimizing a read-only table
What about making the table INNODB? If you make INNODB's buffer large enough, the high activity data/index blocks are retained in memory. INNODB's non blocking read and single statement transaction default should make the transaction overhead minimal and INNODB doesn't have any of the HEAP table restrictions. Caching the data closer to the application layer normally gives better performance than OS level cache or Ram disk. -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Sunday, November 09, 2003 4:55 PM To: [EMAIL PROTECTED] Subject: Re: Strategies for optimizing a read-only table Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Jonathan, I think a packed table would actually make things slower once the data is cached (overhead of unpacking the data). It's just faster when the data has to be *physically* read from disk, since the data is smaller. If disk space isn't an issue and the table doesn't have any TEXT/BLOB columns, make the rows fixed length if they aren't (change VARCHAR CHAR). This will give a small performance improvement. Also, if you don't have any TEXT/BLOB columns, you could load the data into a HEAP table, which *may* make reads *slightly* faster -- depending on your queries. Remember, with HEAP tables, indexes can't be used in all cases that MyISAM can. You can only search on indexes with =, =, IS NULL, and IN (); no range searches with , , BETWEEN, etc.; and you can only use the full index length, no prefixes. However, none of this is true in 4.1+, since you can have BTREE indexes with HEAP tables, not just HASH. :-) But you know what should actually be the best thing for your read-only table? MySQL 4's query cache! :-) Have you thought about this? Or do your queries differ too much that the cache can't be used? Hope this helps. Matt - Original Message - From: Jeremy Zawodny Sent: Saturday, November 08, 2003 11:48 PM Subject: Re: Strategies for optimizing a read-only table On Tue, Nov 04, 2003 at 08:45:08PM -0500, Jonathan Terhorst wrote: I could have sworn I posted this once before, but apparently it got lost somewhere. Apologies if you're seeing this twice: I'm wondering what I can do with MySQL to optimize reads (SELECTs) on a read-only table where data will never be INSERTed or UPDATEd. Okay, that's not entirely correct--the database will be rebuilt every night but it's small (~20,000 rows) and all the writing will take place at once, when the DB is offline to users. In contrast we anticipate read activity on the DB to be high, making it worth putting some thought into this. So far my only thoughts have been a) myisampack and b) to index every single column that our application searches on, since the calculations needed to build said indices can be performed once and forgotten. (Disk space isn't really an issue but myisampack is said to speed up individual row retrieval.) Any other ideas? I've searched for a way to manually mark MySQL tables read-only, but to no avail. Thanks, Will the whole table fit comfortable in RAM? If so, you could store it in a ram disk to prevent disk I/O from ever getting in the way. Jeremy -- 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: Strategies for optimizing a read-only table
On Mon, Nov 10, 2003 at 05:03:35PM +0100, Harald Fuchs wrote: In article [EMAIL PROTECTED], gerald_clark [EMAIL PROTECTED] writes: Matt W wrote: Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Just because a file is in the cache now, it doesn't follow that it will be in the cache 5 minutes from now. If it gets thrown out of the FS cache, you don't have enough RAM, and then a ramdisk won't help much. A ramdisk can help even when you have enough RAM. Sometimes the OS isn't smart enough to know not to cache something that doesn't need caching, and can clear out data you wouldn't want it clearing out. Perhaps someone does a full table scan of a large table once in a while, or whatnot. (Maybe someone copies a db backup file from the machine, and in the process wipes out the cache.) A ramdisk makes sure the disk is never hit, effectively locking the pages in a type of cache. (Hopefully OSs are smart enough not to further cache ramdisk pages.) I haven't used a ramdisk in a db server yet; I don't have any one table that simply MUST be read out of memory every time, and the OS does a pretty good job of managing things. YMMV. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running 4.0.15 side-by-side with 4.1alpha
Hi, I am sure I say someone asking a similar question on the list before but I can't find anything in the archives, nor anything relevant in the mysql docs. Is it possible to have two versions of mysql running side-by-side on linux and if so, are there any installation instructions anywhere? I imagine that things such as port numbers, start-up daemons etc have to be configured differently from the standard install. Thanks Adam -- mySQL 4.1.0 + connector/J 3.0.9 + j2sdk 1.4.2 + Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and Oracle
Hi Everyone, I'm wondering is there any way we can make MySQL database directly 'talk' with Oracle database? For example, directly transfer data between each other. Thanks for any input.
RE: MySQL and Oracle
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Chen, Mao [mailto:[EMAIL PROTECTED] --Sent: Monday, November 10, 2003 2:25 PM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: MySQL and Oracle -- --Hi Everyone, -- -- -- --I'm wondering is there any way we can make MySQL database directly --'talk' with Oracle database? For example, directly transfer data --between each other. Directly no. Indirectly yes. -- -- -- --Thanks for any input. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with query comparing to a DATE on Linux OpenOffice
I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rekall and associated software go GPL
[EMAIL PROTECTED] wrote: Hi It gives me great pleasure to inform you that Rekall, the cross-platform alternative to MS Access, is now totally GPL. So please visit http://www.totalrekall.co.uk and download download a copy for yourself. Is there source code available? I can only find binary packages for the major distributions. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
anyone use Veritas Flash Snap?
Anyone use Veritas FlashSnap to back up data or even better yet mySQL databases over NFS? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
Re: Complex query woes
hi Steffan, i already email you about the null result, it should be eliminated with a where statement (not including null value) and about the long time it took, maybe you shuld add some index to your table... i suggest alter table customer add index parents (pid); alter table customer add index my_id (id); -leo- - Original Message - From: Steffan A. Cline To: Leo ; [EMAIL PROTECTED] Sent: Monday, November 10, 2003 8:12 PM Subject: Re: Complex query woes Leo, Thanks for the quick reply. There was a typo but I fixed it. Below is what I used after correcting it : select mgr.company, building.bldgname, tenant.id from customers mgr left join customers building on building.pid=mgr.id left join customers tenant on tenant.pid=building.id group by mgr.id, building.id, tenant.id order by mgr.company, building.bldgname, tenant.company; This was closer. Problem now is that it took 6.56 seconds and returned 610 rows. I have no idea how I now have 610 rows where there are only 279. Any thing else you would suggest? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member ---
Re: Maybe easy, maybe hard SELECT puzzle :)
At 07:45 PM 11/7/2003, Jonathan Terhorst wrote: snip One way I have found to implement this is SELECT Table1.id from Table1 JOIN Features USING(id) WHERE Features.FeatureCode='A01' OR Features.FeatureCode='B02' GROUP BY Table1.id HAVING COUNT(*)=2; e.g. counting the duplications of id and selecting those equal to the number of FeatureCodes I'm searching for. But this seems somehow inelegant, and I'm justing wondering if there's a better way that's staring me in the face. SELECT DISTINCT f.id FROM Table1 AS t, Features AS f WHERE f.id = t.id AND f.FeatureCode IN ('A01', 'B02'); HTH mysql, query, sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aliases
Hi Erik, No, Roger's method can't use an index. :-) But yes, using WHERE is better than HAVING. To get the WHERE to use an index, don't use a function in the comparison: SELECT invno, invdate, invamt, LEFT(invdate, 2) AS month FROM salesfile WHERE invdate LIKE '01%'; And I would guess that LIKE will be faster than the LEFT() function even if there is no index. Matt - Original Message - From: Erik Osterman Sent: Tuesday, November 11, 2003 6:39 AM Subject: RE: Aliases However, you can use HAVING. HAVING is post-processed, in a brute force method (no indexes can be used). Select invno, invdate, invamt, left(invdate,2) as month from salesfile HAVING month = '01' But I would use Roger's example since it can take advantage of indexes. Regards, Erik Osterman -Original Message- From: Roger Baklund Sent: Sunday, November 09, 2003 6:59 AM Cc: David Katz Subject: Re: Aliases * David Katz I am trying to do a select statement where I am using one of the aliases in the where clause. I keep getting an error that the field does not exist. From the manual: Note that standard SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. URL: http://www.mysql.com/doc/en/Problems_with_alias.html example: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where month = '01' MySql keeps telling me that month is not a field. Is there a way to do this? Yes, simply repeat the expression: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where left(invdate,2) = '01' -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Retrieving the system type that MySQL is running on.
I'm relatively new to MySQL, and I'm writing a PHP script that doesn't know what kind of system the MySQL database is on. The script and database don't have to be on the same system, so I need a way to determine what kind of system (*nix, Win, etc.) MySQL is running on. Is there a fool proof query to determine this? If so, what is it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aliases
From: Matt W [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 5:47 PM To: Erik Osterman; [EMAIL PROTECTED] No, Roger's method can't use an index. :-) But yes, using WHERE is better than HAVING. Ah... right indeed. In this case it wouldn't work. My mistake... :) To get the WHERE to use an index, don't use a function in the comparison: Though you can use functions on indexed columns so long as you (generally) aren't using table columns which lie in your domain. E.g. FROM_UNIXTIME(1068520546) or NOW() will use indexes, but FROM_UNIXTIME(col) will not -- since col is in your input domain. Unless we're talking about MIN/MAX functions and those are an exception! So for clarification, David, those functions that do operate on indexed columns will only work in WHERE clauses and not work in HAVING clauses. Thanks for the correction, Erik Osterman http://osterman.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe easy, maybe hard SELECT puzzle :)
SELECT DISTINCT f.id FROM Table1 AS t, Features AS f WHERE f.id = t.id AND f.FeatureCode IN ('A01', 'B02'); HTH what if i have a record that have 'A01' in the features, but not 'B02' wouldnt it still be displayed in the result? as far as i know, IN keyword act simply as OR keyword didnt Jonathan wrote : But it I want all records from Table1 that have features 'A01' _and_ 'B02' notice the _and_ ? -leo-
Re: Aliases
what if we use the function at the right side of the equation? such as select anything from table1, table2 where table1.id=left(table2.id,somenumber) having both id in table were indexed dont you think the index in table1 would still be used.. :) cmiiw -leo- - Original Message - From: Erik Osterman To: [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:17 AM Subject: RE: Aliases From: Matt W [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 5:47 PM To: Erik Osterman; [EMAIL PROTECTED] No, Roger's method can't use an index. :-) But yes, using WHERE is better than HAVING. Ah... right indeed. In this case it wouldn't work. My mistake... :) To get the WHERE to use an index, don't use a function in the comparison: Though you can use functions on indexed columns so long as you (generally) aren't using table columns which lie in your domain. E.g. FROM_UNIXTIME(1068520546) or NOW() will use indexes, but FROM_UNIXTIME(col) will not -- since col is in your input domain. Unless we're talking about MIN/MAX functions and those are an exception! So for clarification, David, those functions that do operate on indexed columns will only work in WHERE clauses and not work in HAVING clauses. Thanks for the correction, Erik Osterman http://osterman.com/
RE: Aliases
Well, that is correct although it would still need to iterate over all rows in table2. Given the nature of the original problem, we're dealing with a single table in which case it would always need to iterate over all rows of the table. Indeed another good clarification! A more accurate generalization: Indexes may be used where a function's input domain does not reside in the table itself. Unfortunately for David, he's still SOL. Let's see, what else am I neglecting? =) Regards, Erik Osterman http://osterman.com/ -Original Message- From: Leo [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 7:34 PM To: [EMAIL PROTECTED] Subject: Re: Aliases what if we use the function at the right side of the equation? such as select anything from table1, table2 where table1.id=left(table2.id,somenumber) having both id in table were indexed dont you think the index in table1 would still be used.. :) cmiiw -leo- - Original Message - From: Erik Osterman To: [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:17 AM Subject: RE: Aliases From: Matt W [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 5:47 PM To: Erik Osterman; [EMAIL PROTECTED] No, Roger's method can't use an index. :-) But yes, using WHERE is better than HAVING. Ah... right indeed. In this case it wouldn't work. My mistake... :) To get the WHERE to use an index, don't use a function in the comparison: Though you can use functions on indexed columns so long as you (generally) aren't using table columns which lie in your domain. E.g. FROM_UNIXTIME(1068520546) or NOW() will use indexes, but FROM_UNIXTIME(col) will not -- since col is in your input domain. Unless we're talking about MIN/MAX functions and those are an exception! So for clarification, David, those functions that do operate on indexed columns will only work in WHERE clauses and not work in HAVING clauses. Thanks for the correction, Erik Osterman http://osterman.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query comparing to a DATE on Linux OpenOffice
what a front-end is? And what OO1.1.0 provides? Marc [EMAIL PROTECTED] wrote:I'm using OpenOffice 1.1.0 as a front-end to MySQL 4.0.14 on Gentoo Linux and can't compare a field to CURDATE(). The error is, The field cannot be compared with a date. The column is formatted as a DATE, and I can perform the compare fine using mysql. I realize this is probably related only to OpenOffice, and not a mysql problem, but I haven't gotten any answers on the OOo lists. So I'm hoping some kind soul on the mysql/myodbc lists might have experienced this and have an answer. Thanks for any ideas. -- Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
In which version of MySQL was ADD COMMENT added
Hello Can somebody tell me the exact version of MySQL from which the functionality of adding COMMENTS to columsn was added. Karam __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
64bit G5 Panther compiles
Is there any definitive answer yet for the ability to compile a working, 64bit Mysql? The mysql documentation says that mysql performs far better for heavy queries using 64bit. I assume, also, that the new memory limits also help if you allow mysql to take advantage of them, as I have read in some recent ML entries. (which is not 8GB, btw,but 16GB according to the tech docs... 2GB sticks are just not really 'normal' yet.) I am trying to move a client with a very heavy load off a current linux box (Dual Athlon, Dual U160) to use a Dual G5 2Ghz as a primary server (3G currently, 5G shortly), using the linux box as a backupslave machine and for offloading mail and other services, and providing a hot rollover function. His site produces huge amounts of load currently, mostly due to large amounts of queries/page and large amounts of concurrent users. Some info I found below, and I was wondering what would be the best config to try to compile mysql with to produce the best results. http://developer.apple.com/technotes/tn/tn2087.html ... Take advantage of the full precision hardware square root The G5 has a full-precision hardware square root implementation. If your code executes square root, check for the availability of the hardware square root in the G5 and execute code calling the instruction directly (e.g. __fsqrt()) instead of calling the sqrt() routine. (Use __fsqrts() for single-precision.) You can use the GCC compiler flags-mpowerpc-gpopt and -mpowerpc64 to transform sqrt() function calls directly into the PPC sqrt instruction. Align hot code for maximum instruction dispatch Alignment of code in memory is a more significant factor for performance on the G5 than on the G4. The G5 fetches instructions in aligned 32 byte blocks. Therefore, it is often profitable to align hot loops, branches, or branch targets to fetch boundaries. GCC 3.3 offers the following flags to align code: -falign-functions=32, -falign-labels=32,-falign-loops=32, -falign-jumps=32. Additionally, you may need to specify -falign-[functions, labels, loops, jumps]-max-skip=[15,31] to get the desired alignment. ... I assume that adding -mpowerpc-gpopt and -mpowerpc64 to the gcc compiler will generally format the code towards the g5 and 64bit.. then again, I do not know much about compiling past using configure and make ;) I need it to be the fastest model, using the least resources but be stable... able to handle as many requests/sec as possible. Sort of a High Performance fork of the Apache2/php/mysql set. Mysql being the most important. ;) -- Adam Goldstein White Wolf Networks http://whitewlf.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HPVEE client for MySQL
Hi, Does anybody know of HPVEE client code for connecting to a MySQL database? Best regards, Per -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]