A little Database construction help
I need a little help in constructing an order tracking database. We've decided to use MySQL mostly because it's the best supported database out in the wild. Does anyone have an example of an order tracking datamap? A link to a site with the basic flowchart would be a great help. The application is an everyday drycleaner shop. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select query
Dear all, Here I have a query... my table looks like this : code size 1n3j 14 1n3j 32 1n3j 37 1n9j 14 1n9j 32 1n9j 14 1nm4 14 1nm4 37 1nm4 32 1nmi14 1nmi14 1oo314 1oo314 1oo414 1oo414 I want to select those rows with 'size' 14, 32 and 37. This is easy..but I also want only those uniq codes which have all the 'size' (14,32 and 37) associated with it. Please note that none of the feilds are uniq. Here in this case I want result like 1n3j 14 1n3j 32 1n3j 37 1nm4 14 1nm4 37 1nm4 32 because 1n3j and 1nm4 are the only two codes which have all the 'size's requested ie., 14,32, 37. Hope my question is clear. Any kind of help is highly appreciated. Best Rajesh -- Ratna Rajesh Thangudu Research Scholar Laboratoire de Biochimie et Génétique Moléculaire Université de La Réunion 15 avenue René Cassin, BP 7151 97715 Saint Denis Messag Cedex 09 La Réunion, France Tel : +262 262 93 8641 Fax : +262 262 93 8237 Email : [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore Replication Temp Tables
On Tue, 20 Jan 2004, Todd Burke wrote: Is there any way to disable replication of all temp tables using replicate-ignore-table or some other means? The names of the temp tables are generated randomly by a script. Thanks If you could have all temporary tables starting with tmp or something like that (since the script generates the names it should be easy just to tag something at the start or end that makes them stand out), then you can use: (from manual: http://www.mysql.com/doc/en/Replication_Options.html) --replicate-wild-ignore-table=db_name.table_name Tells the slave thread to not replicate a query where any table matches the given wildcard pattern. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates. Please read the notes that follow this option list. Example: --replicate-wild-ignore-table=foo%.bar% will not do updates to tables in databases that start with foo and whose table names start with bar. Note that if you do --replicate-wild-ignore-table=foo%.% then the rule will be propagated to CREATE DATABASE and DROP DATABASE, that is, these two statements will not be replicated if the database name matches the database pattern (foo% here) (this magic is triggered by % being the table pattern). Escaping wildcard characters _ and %: see notes in the description of replicate-wild-do-table just above. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too slow recovering mysqldump files
Mikel, what about playing with the autocommit setting? I guess you use the mysql client for the restore, so you could set autocommit off and perform a commit yourself every 1 rows or so. In our case this boosts recovery by a factor of 5 to 10. Regards, Frank. Mikel - schrieb: I will review the log files, I guess that I set the correct size of my innodb log files, but I'll check it out again. Best Regards Mikel P.S. How about of InnoDB Hot Backup? does it do much faster? From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Mon, 19 Jan 2004 22:44:50 +0200 Mikel, have you set the size of the InnoDB log files as recommended in the manual? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 19, 2004 7:25 PM Subject: Too slow recovering mysqldump files Hi list, does anyone know a faster way to recover a mysqldump file cause When I recovered one dump file it took 26 hours ! to finish, I think it's too slow. Thnx in advanced, greetings MySQL server 3.23.58 RedHat 7.3 4GB RAM 2 scsi disk via fiber channel (333GB each) 2 processor Xeon 1.6GHZ dump file size: 2.5 GB ibdata: 11GB innodb tables key_buffer=850M innodb_buffer_pool_size=850M table_cache=1500 _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- 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] _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.microsoft.com/es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query
Ratna Rajesh Thangudu said: my table looks like this : code size 1n3j 14 1n3j 32 1n3j 37 1n9j 14 1n9j 32 1n9j 14 1nm4 14 1nm4 37 1nm4 32 1nmi14 1nmi14 1oo314 1oo314 1oo414 1oo414 I want to select those rows with 'size' 14, 32 and 37. This is easy..but I also want only those uniq codes which have all the 'size' (14,32 and 37) associated with it. This was discussed yesterday: http://lists.mysql.com/mysql/157911 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving queries - small indexes and tables
Noamn wrote: Judging by some of the comments posed on this list, I wonder whether the following statements are true: 1. There is no point having an index on a field if that field can only have a few values Not for query speed reasons. There may be other reasons (uniqueness for instance), but for speed it only makes sense if the DB can use multiple indexes on one table simultaneously or can use the field as a predicate for inclusion in another index (partial indexes: http://citeseer.nj.nec.com/cachedpage/67014/1) 2. A table should have at least ten entries, in order to prevent all the table being scanned to find a match in a query. Where does the number 10 come from? And on a higher level: why would you even want to prevent a tablescan on a small table? I sometimes even remove indexes on small tables because a tablescan is the only sensible approach and it removes options to consider for the planner. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default character set
Hi. Try to set character set to other not latin1 with MySQL 5 (windows binary) it not work can some one tell me how to do this. Thanks Dr.K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select on indexed columns
Drop the 'sex_index'
Basically when you have few unique values in a column
indexing it doesn't always help.
I setup this table with the 1,000,000 random entries
and query time is nearly the same for your 2 queries
below.
CREATE TABLE sex (
id int(11) unsigned NOT NULL auto_increment,
sex char(1) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
You might also try setting 'sex' to ENUM('F','M')
and also try NOT NULL as well
if needed you could use 'U' for unknown
sex ENUM('F','M','U') NOT NULL default 'U'
olinux
--- Balazs Rauznitz [EMAIL PROTECTED] wrote:
While doing some benchmarks the other day, I saw
surprisingly slow
query results on columns that were indexed.
Here's the table definition:
create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);
Then I loaded a million rows, id was from 1 to
1_000_000, sex was
randomly 'F' or 'M'.
When searching on 'id' everything is snappy:
mysql select count(*) from sex where id459000 and
id =46;
+--+
| count(*) |
+--+
| 1000 |
+--+
1 row in set (0.00 sec)
However when the 'sex' column is involved:
mysql select count(*) from sex where id459000 and
id =46 and sex = 'M';
+--+
| count(*) |
+--+
| 504 |
+--+
1 row in set (5.09 sec)
Any way to make this faster ?
I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz
AMD CPU and 640M RAM.
Insert jokes about sex making MySQL slow here
Thanks,
Balazs
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installing mysql-standard-4.0.17-pc-linux-i686 in redhat-9 help me urg!
hi, i am trying to install Mysql in RH-9.I follow your instructions .But when i typed ./configure --prefix=/usr/local/mysql after some screens it displays Starting mysqld daemonwith databases from/usr/local/src/mysql-standard-4.0.17-pc-linux-i686/data 040121 11:55:41 mysqld ended after that i got cursor blinking.Even after 30 minutes i can't get back my command prompt.When i pressed enter key i got command prompt by your instructions i typed make it displays make***No targets not specfied and no make file found I tried many times,but the same error appears What is the error? is mysql-versin-1i686 is okay for RH-9(P-4 processor)? Plz help me Thanks in Advance. Nagapsg Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installing mysql-standard-4.0.17-pc-linux-i686 in redhat-9 help me urg!
On Wednesday 21 January 2004 10:20, naga rajan wrote: hi, i am trying to install Mysql in RH-9.I follow your instructions .But when i typed Since you have RedHat, might I suggest that you use the RPM versions, which are pre-compiled and should set everything up properly for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query hangs when connection broken
Hi! I'm using Visual Foxpro 6.0 for develop my application whitch is connected to MySQL 4.1 via MyODBC (3.2.51) driver. I'm using several methods in my program to monitor server state and switch from one server to another if server or connection failure occurs. There is one big problem about connection failures at the query executing time - query hangs (and program interface too) and I can't switch to offline mode (in my program). Is it possible to set maximum query execution time per session/query like I can do this by connecting to database (in foxpro with SQLSETPROP() function)? Or is it MyODBC driver-relalted issue? = SQLSETPROP(sqlh,ConnectTimeOut,1)works for me = SQLSETPROP(sqlh,QueryTimeOut,10) doesn't do that bregs Hendrik === tel: 046 31634 DC Doora AS gsm: 051 51789 Heltermaa mnt 6 fax: 046 31455 Kärdla, Hiiumaa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to start mysql after rpm installation
hi, I had just installed mysql RPM in RedHat-9. It doesn't shows any errors but what nextstep i have to do work with mysql. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to start mysql after rpm installation
On Wednesday 21 January 2004 10:56, naga rajan wrote: hi, I had just installed mysql RPM in RedHat-9. It doesn't shows any errors but what nextstep i have to do work with mysql. RT(f)M: http://www.mysql.com/documentation/mysql/bychapter/manual_Installing.html#Unix_post-installation Pretty much anything you need to know can be found in the manual. For the RPM install, you may be able to type (as root) service mysql start. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default character set
Kittiphum Worachat [EMAIL PROTECTED] wrote: Try to set character set to other not latin1 with MySQL 5 (windows binary) it not work can some one tell me how to do this. If you want to set character set on the server level, you should start mysqld with --default-character-set option: http://www.mysql.com/doc/en/Charset-server.html What exactly doesn't work for you? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function in Where clause
Hello, I have a client using a already made calendar component on my server... The problem seems to be the SQL code. This code works (i believe) with an MS Access or MS SQL server DB... MySQL version is 3.23.58 The code is: SELECT * FROM events WHERE day(theDate) = '#dayVal#' and month(theDate) = '#session.month#' and year(theDate) = '#session.year#' Can the day() function be used in this way in the WHERE statement? Is there a better way to achieve this? Thanks, Yves -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API and unicode
Hi all, I have an application that works fine with MySQL 3.23 and the C API (everything on Linux and only on Linux). I need now to support unicode (I mean chinese, japanese, russian... all kind of characters) so I am converting the application to use wchar_t and wstring instead of char and string. I've installed MySQL 4.1.1 which support unicode (I don't have any problem with it yet). The problem is that it seems that the C API doesn't support unicode. I couldn't find any trace of wchar_t or wstring in the include files. What should I do ? For now I have problem compiling something like this mysql_query (_connection, _query) with _query = (wchar_t *) malloc(QUERY_LEN * sizeof(wchar_t *)); What it the solution ? using wcstombs ? Won't it loose anything ? Steel ** PROTEGEZ VOS E-MAILS !** Avec Tiscali SuperMail, vos e-mails en toute sécurité ! Anti Spam personnalisable Anti Virus actualisé en permanence et de nombreux bonus... Pour en savoir plus, rendez-vous sur http://www.tiscali.fr/supermail/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function in Where clause
function can b used, but value parameter seems syntactically wrong, check that. '#session.month#' to '$month' which is the language,u r using anyway? - Original Message - From: Yves Arsenault [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 6:37 PM Subject: Function in Where clause Hello, I have a client using a already made calendar component on my server... The problem seems to be the SQL code. This code works (i believe) with an MS Access or MS SQL server DB... MySQL version is 3.23.58 The code is: SELECT * FROM events WHERE day(theDate) = '#dayVal#' and month(theDate) = '#session.month#' and year(theDate) = '#session.year#' Can the day() function be used in this way in the WHERE statement? Is there a better way to achieve this? Thanks, Yves -- 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: Function in Where clause
Thanks for your response, I'm using ColdFusion along side of MySQL to server dynamic content to some websites. When I run the page that this code is in, I get an SQL syntax error: Syntax error or access violation: You have an error in your SQL syntax near '(theDate) = '31' and month(theDate) = '9' and year(theDate) = '2003'' The right values are displayed from ColdFusion server, but I keep getting the syntax error. Yves At 10:35 2004-01-21, you wrote: function can b used, but value parameter seems syntactically wrong, check that. '#session.month#' to '$month' which is the language,u r using anyway? - Original Message - From: Yves Arsenault [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 6:37 PM Subject: Function in Where clause Hello, I have a client using a already made calendar component on my server... The problem seems to be the SQL code. This code works (i believe) with an MS Access or MS SQL server DB... MySQL version is 3.23.58 The code is: SELECT * FROM events WHERE day(theDate) = '#dayVal#' and month(theDate) = '#session.month#' and year(theDate) = '#session.year#' Can the day() function be used in this way in the WHERE statement? Is there a better way to achieve this? Thanks, Yves -- 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: unable to update root password or connect.
root can only connect through localhost until GRANTed permissions over the network. Leave off the '-h inglewood'. If you have not set the password yet, then you also should leave off the '-p' option. [EMAIL PROTECTED] wrote: This is a new install of mysql 3.23 on a SuSE8.0 Box. I installd everythign via suse rpms. I ran mysql_install_db when I to run the update root password I get the following error. I'm logged into the server via ssh as root while exicuting the commands. any ideas. inglewood:/etc # mysqladmin -u root -h inglewood -p password '**' Enter password: mysqladmin: connect to server at 'inglewood' failed error: 'Host 'inglewood.studio3arc.com' is not allowed to connect to this MySQL server' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function in Where clause
Yves Arsenault wrote: SELECT * FROM events WHERE day(theDate) = '#dayVal#' and month(theDate) = '#session.month#' and year(theDate) = '#session.year#' Can the day() function be used in this way in the WHERE statement? Yes, but only from version 4.1.1 Use DayOfMonth() or preferably Extract() (which is the method from the SQL standard) per the manual. Is there a better way to achieve this? Loose the single quotes and start using cfqueryparam. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function in Where clause
On Wed, 21 Jan 2004, Yves Arsenault wrote: Thanks for your response, I'm using ColdFusion along side of MySQL to server dynamic content to some websites. When I run the page that this code is in, I get an SQL syntax error: Syntax error or access violation: You have an error in your SQL syntax near '(theDate) = '31' and month(theDate) = '9' and year(theDate) = '2003'' The right values are displayed from ColdFusion server, but I keep getting the syntax error. In 3.23 you have to use DAYOFMONTH(), DAY() isn't available til 4.1.1 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does mysqldump take care of stored procedures?
I'm testing backup and restore on MySQL 5.0. I did mysqldump first and then dropped a table and a stored procedure on purpose. After doing 'mysql -h host -u user -p database dump-file', the dropped table could be restored back in the database. But the dropped procedure stayed missing. How should I do to get the stored procedures backed up as well? Any suggestions/ideas appreciated. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select count from three tables
Hi All I have the following situation: the DB has three tables 'users', 'links' and 'searches'. Each table has a common key named 'userid' What I want to do is, for each user in the 'lguser' table I'd like to count the number of corresponding records in EACH of the 'lghyperlink' and 'lgsearch' tables. I have the following query which counts the number of records in `lghypoerlink` for each record in `lguser` but I can't figure out how to incorporate the `lgsearch` table and count the rows. SELECT u . username , count( l.username ) AS clicks FROM `lguser` AS u LEFT JOIN `lghyperlink` AS l ON u.username = l.username GROUP BY u.username ORDER BY clicks DESC I'm trying for output like: username | clicks | searches test | 12 | 45 anon | 20 | 23 Any help or suggestions would be much appreciated regards, Bill Stennett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mysqldump take care of stored procedures?
Hi, since MySQL stores stored procs in mysql db you need to make backup of mysql db. But it would be nice to have a possibility to make backup of stored procs in readable format. Best regards, Mikhail. - Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:42 PM Subject: does mysqldump take care of stored procedures? I'm testing backup and restore on MySQL 5.0. I did mysqldump first and then dropped a table and a stored procedure on purpose. After doing 'mysql -h host -u user -p database dump-file', the dropped table could be restored back in the database. But the dropped procedure stayed missing. How should I do to get the stored procedures backed up as well? Any suggestions/ideas appreciated. Bing -- 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]
#1205 - Lock wait timeout exceeded; Try restarting transaction
I am using tables INNODB. After one query, the MySQL does not liberate the LOCK, and when I try to execute same query it appears the message. #1205 - Lock wait timeout exceeded; Try restarting transaction whait urgently one helps. thanks, Bruno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RegExp Help
Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: does mysqldump take care of stored procedures?
Stored procedure versioning/backup/restoring has always been a pain in the butt for all dbs that support them. What is done 9/10 times is the sql script that creates them is stored, versioned, and used for backup... now a 'show create stored procedure blah_blah' function would be nice... ;) -Original Message- From: Mikhail Entaltsev [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 10:49 AM To: Bing Du; [EMAIL PROTECTED] Subject: Re: does mysqldump take care of stored procedures? Hi, since MySQL stores stored procs in mysql db you need to make backup of mysql db. But it would be nice to have a possibility to make backup of stored procs in readable format. Best regards, Mikhail. - Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:42 PM Subject: does mysqldump take care of stored procedures? I'm testing backup and restore on MySQL 5.0. I did mysqldump first and then dropped a table and a stored procedure on purpose. After doing 'mysql -h host -u user -p database dump-file', the dropped table could be restored back in the database. But the dropped procedure stayed missing. How should I do to get the stored procedures backed up as well? Any suggestions/ideas appreciated. Bing -- 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: does mysqldump take care of stored procedures?
Ah, I forgot to dump the 'mysql' db. Thanks so much, Mikhail, for waking me up :). Bing Hi, since MySQL stores stored procs in mysql db you need to make backup of mysql db. But it would be nice to have a possibility to make backup of stored procs in readable format. Best regards, Mikhail. - Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:42 PM Subject: does mysqldump take care of stored procedures? I'm testing backup and restore on MySQL 5.0. I did mysqldump first and then dropped a table and a stored procedure on purpose. After doing 'mysql -h host -u user -p database dump-file', the dropped table could be restored back in the database. But the dropped procedure stayed missing. How should I do to get the stored procedures backed up as well? Any suggestions/ideas appreciated. Bing -- 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: select count from three tables
* compuserve aka Bill Stennett I have the following situation: the DB has three tables 'users', 'links' and 'searches'. Each table has a common key named 'userid' This does not match the table/column names you describe below... are you trying to confuse us? ;) What I want to do is, for each user in the 'lguser' table I'd like to count the number of corresponding records in EACH of the 'lghyperlink' and 'lgsearch' tables. I have the following query which counts the number of records in `lghypoerlink` for each record in `lguser` but I can't figure out how to incorporate the `lgsearch` table and count the rows. SELECT u . username , count( l.username ) AS clicks FROM `lguser` AS u LEFT JOIN `lghyperlink` AS l ON u.username = l.username GROUP BY u.username ORDER BY clicks DESC I'm trying for output like: username | clicks | searches test | 12 | 45 anon | 20 | 23 (This seems to be ordered by clicks ASC or searches DESC...?) Have you tried something like this: SELECT u.username, count(l.username) AS clicks, count(s.username) AS searches FROM `lguser` AS u LEFT JOIN `lghyperlink` AS l ON u.username = l.username LEFT JOIN `lgsearch` AS s ON u.username = s.username GROUP BY u.username ORDER BY clicks DESC -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too slow recovering mysqldump files
I'll set up those parameters in my.cnf and try again the recovery from the dump file. I'll hope these changes make faster the recovery. Thanks again Heikki, Mikel P.S. Do you recommend the innodb hot backup tool, does it do faster than the other options, or is a combination of both? From: Heikki Tuuri [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Wed, 21 Jan 2004 05:10:27 +0200 Mikel, it is apparently disk-bound. I recommend setting innodb_buffer_pool_size as high as 1.4 GB during such big import. Adjust innodb_log_file_size accordingly, and make key_buffer smaller during the big import. Help is coming: Marko Mäkelä is writing a compressed InnoDB table format, which can squeeze a typical table to 1 / 4 the size of a normal InnoDB table. I believe the compressed format will be available in October 2004. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:32 AM Subject: Re: Too slow recovering mysqldump files Here we are my options: set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M I follow the example that came here: http://www.mysql.com/doc/en/InnoDB_start.html; Greetings and best regards Mikel From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Mon, 19 Jan 2004 22:44:50 +0200 Mikel, have you set the size of the InnoDB log files as recommended in the manual? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 19, 2004 7:25 PM Subject: Too slow recovering mysqldump files Hi list, does anyone know a faster way to recover a mysqldump file cause When I recovered one dump file it took 26 hours ! to finish, I think it's too slow. Thnx in advanced, greetings MySQL server 3.23.58 RedHat 7.3 4GB RAM 2 scsi disk via fiber channel (333GB each) 2 processor Xeon 1.6GHZ dump file size: 2.5 GB ibdata: 11GB innodb tables key_buffer=850M innodb_buffer_pool_size=850M table_cache=1500 _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- 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] _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting Rows from related tables in MySQL 3.2.3
I am getting a syntax error from MySQL when executing the following query (names substituted): Delete From Table1 WHERE Related_ID IN (SELECT Related_ID FROM Table2 WHERE Another_ID='1'); What is going wrong? My guess is that the SELECT is considered a subquery, but how else do you get the selected rows for the IN() argument? Here is the relevant section from the manual: A.5.5 Deleting Rows from Related Tables As MySQL doesn't support subqueries (prior to Version 4.1), nor the use of more than one table in the DELETE statement (prior to Version 4.0), you should use the following approach to delete rows from 2 related tables: SELECT the rows based on some WHERE condition in the main table. DELETE the rows in the main table based on the same condition. DELETE FROM related_table WHERE related_column IN (selected_rows). TIA, zaxpaw at comcast dot net
Re: Shared Physical Database Question
As to the direct question of two servers accessing the same file via SAN, I don't know. But here is an option we are using. I have two Linux Servers, a Web Server and SQL server. Our web server resides on both the internal external networks (two nics), with some firewall software installed (IPTables). The outside network accesses the webserver and the webserver accesses the SQL server. Therefore no one on the outside can directly access the internal network the SQL server. Anyone using a SQL GUI interface or and scripting language would not have direct access to the SQL server. They would need to access the webserver which has programs on that allows limited in-direct access to the SQL server. Hope this helps. Patrick Shoaf IT Manager At 02:41 PM 1/19/2004, [EMAIL PROTECTED] wrote: Could someone please tell me if tyhe following is possible or if a solution accomplishing the same thing is available? I would like to build a database using two MySQL servers accessing the same physical file on a common Drive attached to each computer via a SAN. Can this be done or is there data integrity issues and database file locking issues?. Our main goal is to provide the data gathered on a secure network and allow it to be seen on an insecure network. I do understand that this can be accomplished using firewalls and other networking tools, but our management has been very firm in their insistence that a user has no direct network access to our internal/secure LAN. Thanks for any help anyone can provide. Matthew Harris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared Physical Database Question
In the last episode (Jan 19), [EMAIL PROTECTED] said: Could someone please tell me if tyhe following is possible or if a solution accomplishing the same thing is available? I would like to build a database using two MySQL servers accessing the same physical file on a common Drive attached to each computer via a SAN. Can this be done or is there data integrity issues and database file locking issues?. Our main goal is to provide the data gathered on a secure network and allow it to be seen on an insecure network. I do understand that this can be accomplished using firewalls and other networking tools, but our management has been very firm in their insistence that a user has no direct network access to our internal/secure LAN. You might want to check out replication, so that you can push a read-only copy of the tables to a mysqld running on the web server. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Shared Physical Database Question
The one major design spec, my management has requested, is lack of TCP/IP connectivity between the two servers using the data. One network is completely isolated from the outside world/internet, however we are trying to find a secure way to allow outside users to query historical data that currently resides on the isolated network. Thanks, Matthew Harris Systems Engineer Peoples Energy - Gas Control (312) 240-4752 [EMAIL PROTECTED] -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:48 AM To: Harris, Matt Cc: [EMAIL PROTECTED] Subject: Re: Shared Physical Database Question In the last episode (Jan 19), [EMAIL PROTECTED] said: Could someone please tell me if tyhe following is possible or if a solution accomplishing the same thing is available? I would like to build a database using two MySQL servers accessing the same physical file on a common Drive attached to each computer via a SAN. Can this be done or is there data integrity issues and database file locking issues?. Our main goal is to provide the data gathered on a secure network and allow it to be seen on an insecure network. I do understand that this can be accomplished using firewalls and other networking tools, but our management has been very firm in their insistence that a user has no direct network access to our internal/secure LAN. You might want to check out replication, so that you can push a read-only copy of the tables to a mysqld running on the web server. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Rows from related tables in MySQL 3.2.3
* Zaxpaw
I am getting a syntax error from MySQL when executing the following
query (names substituted):
Delete From Table1 WHERE Related_ID IN (SELECT Related_ID FROM Table2
WHERE Another_ID='1');
What is going wrong?
Version 3.x does not support sub-selects.
My guess is that the SELECT is considered a subquery, but how else do
you get the selected rows for the IN() argument?
You can do it in multiple steps. First, get the id's from the first SELECT
in the example from the manual, quoted below. You need a programming
language for this. Are you using a programming language with mysql?
Second, you put these id's in a string, separated by comma, so it would look
like this: $ids = 234,634,434,6235,32
..then you construct the delete statement:
DELETE FROM related_table
WHERE related_column IN ($ids)
... and finally execute it after it is expanded to this:
DELETE FROM related_table
WHERE related_column IN (234,634,434,6235,32)
Here is the relevant section from the manual:
A.5.5 Deleting Rows from Related Tables
As MySQL doesn't support subqueries (prior to Version 4.1), nor the use
of more than one table in the DELETE statement (prior to Version 4.0),
you should use the following approach to delete rows from 2 related
tables:
SELECT the rows based on some WHERE condition in the main table.
DELETE the rows in the main table based on the same condition.
DELETE FROM related_table WHERE related_column IN (selected_rows).
If you don't use a programming language, you can also do this using only the
standard mysql client, but using two instances...
SELECT CONCAT('DELETE FROM Table1 WHERE Related_ID = ', Table1.Related_ID,
';')
FROM Table1, Table2
WHERE Another_ID='1' AND Table1.Related_ID = Table2.Related_ID;
If you put this (or similar) query in a text file called del_tab1.sql, you
can execute the deletion with a double mysql invocation similar to this:
mysql --skip-column-names dbname del_tab1.sql | mysql dbname
You could try it without actually doing the delete by executing this:
mysql --skip-column-names dbname del_tab1.sql
This sould list all delete statements to the screen only, without executing
them. You could redirect this output to a file, and then execute this file:
mysql --skip-column-names dbname del_tab1.sql do_delete.sql
mysql dbname do_delete.sql
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- 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: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- 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]
char count
I'm try to write a query that will return the number of times a specified character appears in a string (I want known how many times the character M appears), but I cannot seem to figure out how to do that. The select statement is the following: SELECT structure_aa_sequence from structure where apc_id=APC1114; and this will return the following: +--+ | structure_aa_seq | +--+ | GGFVPNWYQHPDPALKYADDMEVYDYYQQYEAAKKAAREASTSSKKTAATSPALPRAKPHVTIA | +--+ Thanks Joseph S. Brunzelle, Ph.D. Life Sciences CAT Dept of Mol. Pharm. and Biol. Chem. Feinberg School of Medicine Northwestern University Phone (630)252-0629 FAX (630)252-0625 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select count from three tables
Thanks for the suggestion but unfortunately this seems to produce the product of the two tables `lghyperlink` and `lgsearch`. e.g. if user bill and 10 entries in `lghyperlinks` and 15 entries in `lgsearch` then what is returned is username clicks searches == bill 150 150 instead of username clicks searches == bill 10 15 Any other suggestions gratefully received - maybe this has to be done in two queries and the data manipulated in the application: less than ideal but if there is no other way... best Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 21 January 2004 16:51 To: [EMAIL PROTECTED] Cc: compuserve Subject: Re: select count from three tables * compuserve aka Bill Stennett I have the following situation: the DB has three tables 'users', 'links' and 'searches'. Each table has a common key named 'userid' This does not match the table/column names you describe below... are you trying to confuse us? ;) What I want to do is, for each user in the 'lguser' table I'd like to count the number of corresponding records in EACH of the 'lghyperlink' and 'lgsearch' tables. I have the following query which counts the number of records in `lghypoerlink` for each record in `lguser` but I can't figure out how to incorporate the `lgsearch` table and count the rows. SELECT u . username , count( l.username ) AS clicks FROM `lguser` AS u LEFT JOIN `lghyperlink` AS l ON u.username = l.username GROUP BY u.username ORDER BY clicks DESC I'm trying for output like: username | clicks | searches test | 12 | 45 anon | 20 | 23 (This seems to be ordered by clicks ASC or searches DESC...?) Have you tried something like this: SELECT u.username, count(l.username) AS clicks, count(s.username) AS searches FROM `lguser` AS u LEFT JOIN `lghyperlink` AS l ON u.username = l.username LEFT JOIN `lgsearch` AS s ON u.username = s.username GROUP BY u.username ORDER BY clicks DESC -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Redhat Alternatives for MySQL
Hello, For some time we've been running MySQL (from 4.0.1 to 4.0.17) on Redhat (v. 7.2 9.0). We use our linux server for one thing: MySQL server. We are far from linux experts, so Redhat made sense, particularly the ease of installation and the bugfixes via up2date. Now that Redhat is ending support, we need an alternative that doesn't cost $349+ per year. We never need formal tech support; we only need access to reliable security bugfixes/updates. Fedora seems out of the question because of the frequent upgrade cycles. Can anyone recommend a linux vendor that 1) offers an easy bugfix/update system and 2) is affordable for a small business with limited linux expertise. We want a reliable solution for our MySQL server(s) as we grow. I realize this request may be a little off-topic, but since the health, security and future of our database server is in question, we felt we needed input from other MySQL users. Thanks! - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
Re: unsupported driver trouble
i've never used MT, but this kinda sounds as if you dont have the DBI installed: $ perl -MCPAN -eshell install DBI cheers, M. tait sanders wrote: hi all, not sure if this is the appropriate place to post this -- so please point me in the right direction if not here... I have a G4 with os10.3.2 server, mysql v4.0.16, and MoveableType 2.6 I'm trying to initialise the moveabletype system by running mt-load.cgi and am recieving the following error: Unsupported driver MT::ObjectDriver::DBI::mysql: Can't locate DBI.pm in @INC... I've checked in my mt.cfg file and I have added: ObjectDriver DBI::mysql Database /var/mysql/mtweblog DBUser labadmin so I don't understand what's going on. please help ta tait tait sanders computer technician sir robert webster bldg trc unsw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Redhat Alternatives for MySQL
Openna.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:55 AM To: [EMAIL PROTECTED] Subject: Redhat Alternatives for MySQL Hello, For some time we've been running MySQL (from 4.0.1 to 4.0.17) on Redhat (v. 7.2 9.0). We use our linux server for one thing: MySQL server. We are far from linux experts, so Redhat made sense, particularly the ease of installation and the bugfixes via up2date. Now that Redhat is ending support, we need an alternative that doesn't cost $349+ per year. We never need formal tech support; we only need access to reliable security bugfixes/updates. Fedora seems out of the question because of the frequent upgrade cycles. Can anyone recommend a linux vendor that 1) offers an easy bugfix/update system and 2) is affordable for a small business with limited linux expertise. We want a reliable solution for our MySQL server(s) as we grow. I realize this request may be a little off-topic, but since the health, security and future of our database server is in question, we felt we needed input from other MySQL users. Thanks! - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: Id Title Address City State Zip 1 Joe 1313 Mockingbird Lane TransylvaniaPA 02098 ^ To: Id Title Address City State Zip 1 Joe 1313 Mockingbird Lane TransylvaniaPA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
wow, one post, two mistakes. how right you are. sorry. update 02093_xdir_links SET title = REPLACE(\,,title); you may/may not need to escape the . hth Jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] ve.com cc: Subject: RE: RegExp Help 01/21/2004 01:47 PM Please respond to bcohen you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^ To: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Shared Physical Database Question
Thanks for your input on all points. Let me explain my suggested setup first and then tell me if it still seems absurd. Point 1: We have an isolated network performing critical functionality. This network has absolutely no connectivity to the outside world/internet Point 2: Data from this network would be replicated to a MySQL server connected to a SAN Point 3: The TCP/IP connection between the production machine and the MySQL server would be protected via a firewall/access control Point 4: This SAN would ONLY be responsible for holding the replicated/non-production data. The OS for the MySQL server would reside on local hard-drive Point 5: An external MySQL server having it's own local OS hard-drive, but sharing the database stored on the SAN drive Point 6: Firewalls/Access Control would be used to grant/deny access to the External MySQL server, thereby adding an additional layer of security at the network level. Point 7: Essentially, A DMZ has been created encapsulating the two MySQL servers and the SAN they would both access. I was not very clear when I originally submitted this issure to the group. Thanks, Matthew Harris Systems Engineer Peoples Energy - Gas Control (312) 240-4752 [EMAIL PROTECTED] -Original Message- From: root [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:37 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Harris, Matt Subject: Re: Shared Physical Database Question I have to comment on this one... You don't know me so feel free to discard :) I am not sure your management folks have any idea of what they are requesting... They request that you have no TCP/IP access but it seems like direct FIBER CHANNEL is ok? I can't think of a worse security problem than direct block access to a file system. On a compromised server that is like giving the cracker a free ride. I am sure that their concern is security and that is understandable, but having a computer on the outside of your TCP/IP network that is directly connected to your SAN is no more secure. Your SAN is most likely a separate network (Unless you are using ISCSI) but it is still a network. A cracked server then has direct access to your SAN file system... Not so good. As far as solving your problem goes... I can't think of a single way to automatically update the exterior server without some type of network, either access to the SAN or the IP network. I guess you could manually copy the MySQL data (mysqldump) to a CD and then manually copy them to the exterior server. You might point out the labor overhead involved with that scenario to your management people. Time to take the design spec tools away from your managers :) Jason McKnight Mgr. Information Services The InSite Group,LLC [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: The one major design spec, my management has requested, is lack of TCP/IP connectivity between the two servers using the data. One network is completely isolated from the outside world/internet, however we are trying to find a secure way to allow outside users to query historical data that currently resides on the isolated network. Thanks, Matthew Harris Systems Engineer Peoples Energy - Gas Control (312) 240-4752 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Dan Nelson [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Wednesday, January 21, 2004 11:48 AM To: Harris, Matt Cc: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Subject: Re: Shared Physical Database Question In the last episode (Jan 19), [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] said: Could someone please tell me if tyhe following is possible or if a solution accomplishing the same thing is available? I would like to build a database using two MySQL servers accessing the same physical file on a common Drive attached to each computer via a SAN. Can this be done or is there data integrity issues and database file locking issues?. Our main goal is to provide the data gathered on a secure network and allow it to be seen on an insecure network. I do understand that this can be accomplished using firewalls and other networking tools, but our management has been very firm in their insistence that a user has no direct network access to our internal/secure LAN. You might want to check out replication, so that you can push a read-only copy of the tables to a mysqld running on the web server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Redhat Alternatives for MySQL
SuSE Pro. Automatic online updates/bugfixes. Cheap. Only pay for the support you need. I've been running MySQL on SuSE for 18+mths with no problems, and SuSE in general for about 3 yrs for allsorts of thing. Steve [EMAIL PROTECTED] wrote: Hello, For some time we've been running MySQL (from 4.0.1 to 4.0.17) on Redhat (v. 7.2 9.0). We use our linux server for one thing: MySQL server. We are far from linux experts, so Redhat made sense, particularly the ease of installation and the bugfixes via up2date. Now that Redhat is ending support, we need an alternative that doesn't cost $349+ per year. We never need formal tech support; we only need access to reliable security bugfixes/updates. Fedora seems out of the question because of the frequent upgrade cycles. Can anyone recommend a linux vendor that 1) offers an easy bugfix/update system and 2) is affordable for a small business with limited linux expertise. We want a reliable solution for our MySQL server(s) as we grow. I realize this request may be a little off-topic, but since the health, security and future of our database server is in question, we felt we needed input from other MySQL users. Thanks! - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: char count
At 12:17 PM -0600 1/21/04, Joseph S Brunzelle wrote: I'm try to write a query that will return the number of times a specified character appears in a string (I want known how many times the character M appears), but I cannot seem to figure out how to do that. The select statement is the following: SELECT structure_aa_sequence from structure where apc_id=APC1114; and this will return the following: +--+ | structure_aa_seq | +--+ | GGFVPNWYQHPDPALKYADDMEVYDYYQQYEAAKKAAREASTSSKKTAATSPALPRAKPHVTIA | +--+ Thanks Well, this query should do it, but I suspect it's too inefficient to run on a regular basis: select length(replace(structure_aa_sequence,'M','MM'))-length(structure_aa_sequence) from structure; I just replace the character of interest with 2 characters (in the query; it doesn't affect the database), and check the length difference. To use the counts on a regular basis, it's probably easier to count them when you insert or update the record, and store those values in a separate column. That's what I ended up doing on a few genetics databases I was working on, anyway - steve Joseph S. Brunzelle, Ph.D. Life Sciences CAT Dept of Mol. Pharm. and Biol. Chem. Feinberg School of Medicine Northwestern University Phone (630)252-0629 FAX (630)252-0625 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | [EMAIL PROTECTED]: 1001 Work units on 23 oct 2002 | | 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Redhat Alternatives for MySQL
Hello! If you live in europe or in the states you can get SuSE 9, for 79$ the box, i recently spent cristhmas in the states and see SuSE boxes every where i went i even saw suse boxes in wallmart :s SuSE is quite a nice choice IMHO. On Wed, 2004-01-21 at 14:49, Ferguson, Michael wrote: Openna.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 11:55 AM To: [EMAIL PROTECTED] Subject: Redhat Alternatives for MySQL Hello, For some time we've been running MySQL (from 4.0.1 to 4.0.17) on Redhat (v. 7.2 9.0). We use our linux server for one thing: MySQL server. We are far from linux experts, so Redhat made sense, particularly the ease of installation and the bugfixes via up2date. Now that Redhat is ending support, we need an alternative that doesn't cost $349+ per year. We never need formal tech support; we only need access to reliable security bugfixes/updates. Fedora seems out of the question because of the frequent upgrade cycles. Can anyone recommend a linux vendor that 1) offers an easy bugfix/update system and 2) is affordable for a small business with limited linux expertise. We want a reliable solution for our MySQL server(s) as we grow. I realize this request may be a little off-topic, but since the health, security and future of our database server is in question, we felt we needed input from other MySQL users. Thanks! - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too slow recovering mysqldump files
Mikel, - Original Message - From: Mikel - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 7:19 PM Subject: Re: Too slow recovering mysqldump files I'll set up those parameters in my.cnf and try again the recovery from the dump file. I'll hope these changes make faster the recovery. Dr. Ullrich suggested that you should set innodb_flush_log_at_trx_commit=0 during the big import, or wrap the big import inside a transaction. Otherwise, the speed may be restricted to 1 row / disk rotation = 100 rows/second. Thanks again Heikki, Mikel P.S. Do you recommend the innodb hot backup tool, does it do faster than the other options, or is a combination of both? InnoDB Hot Backup takes a BINARY backups of your database. If you need to restore from a backup taken with InnoDB Hot Backup, you do not need to import table dumps. Thus restoration is much faster than if you use table dumps as a backup (table dump = LOGICAL backup). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ From: Heikki Tuuri [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Wed, 21 Jan 2004 05:10:27 +0200 Mikel, it is apparently disk-bound. I recommend setting innodb_buffer_pool_size as high as 1.4 GB during such big import. Adjust innodb_log_file_size accordingly, and make key_buffer smaller during the big import. Help is coming: Marko Mäkelä is writing a compressed InnoDB table format, which can squeeze a typical table to 1 / 4 the size of a normal InnoDB table. I believe the compressed format will be available in October 2004. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:32 AM Subject: Re: Too slow recovering mysqldump files Here we are my options: set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M I follow the example that came here: http://www.mysql.com/doc/en/InnoDB_start.html; Greetings and best regards Mikel From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Mon, 19 Jan 2004 22:44:50 +0200 Mikel, have you set the size of the InnoDB log files as recommended in the manual? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 19, 2004 7:25 PM Subject: Too slow recovering mysqldump files Hi list, does anyone know a faster way to recover a mysqldump file cause When I recovered one dump file it took 26 hours ! to finish, I think it's too slow. Thnx in advanced, greetings MySQL server 3.23.58 RedHat 7.3 4GB RAM 2 scsi disk via fiber channel (333GB each) 2 processor Xeon 1.6GHZ dump file size: 2.5 GB ibdata: 11GB innodb tables key_buffer=850M innodb_buffer_pool_size=850M table_cache=1500 _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- 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] _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB Designer 4
Hi, I've learned from fabFORCE.net that DB Designer 4 is bought by MySQL AB. Could any one from MySQL AB please comment on the new acquisition and shed some light on integration plans with MySQLCC? Thanks. Hassan
RE: InnoDB locking 'non-existence' of a row
Hello Heikki, Thank you for your reply and your explanation. It clarifies things and explains some of the unexpected behavior I've observed (such as my own inability to insert to the gap after locking it). In general, is there a place where I can find a good discussion on the various locks used by InnoDB? I've seen references to various locks in InnoDB status output, but wasn't sure what each kind was (S-locks, X-locks, etc.). This would be a great aid for helping troubleshoot these kinds of problems. Thank you for your time, Alex Zeltser List: MySQL General Discussion Previous MessageNext Message From: Heikki Tuuri Date: January 21 2004 4:32am Subject: Re: InnoDB locking 'non-existence' of a row Alex, diagram: record1 'gap' record2 (User A holds a next-key lock on record2) InnoDB can lock the non-existence of a row in the 'gap'. But it cannot presently make another user B to wait before B acquires a lock on the gap. The reason is that B's cursor has already passed the gap when B ends up waiting for a next-key lock on record2. If we would allow user A to insert to the gap, then the cursor of B should be moved backwards, so that B's cursor would see the inserted record when A commits. Currently, InnoDB does not move a cursor backwards when a lock wait ends. Locks on gaps are purely inhibitive. That is, you can prevent other users from inserting to the gap, but you cannot guarantee that you yourself will be able to insert. In the general case, we cannot prevent 2 users acquiring conflicting locks on the same gap: gap1 delete_marked_record gap2 If A holds an X-lock on gap1 and B holds an X-lock on gap2, and purge removes the delete_marked_record, then the gaps merge, and both A and B hold an X-lock on the gap. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 20, 2004 9:20 PM Subject: RE: InnoDB locking 'non-existence' of a row Hi Joe, Thanks for your reply. Actually, in my experience (and according to the = docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row = for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert = the row from another transaction will block or fail (until the first one does a commit or a = rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction = 'lock' the non-existence of a row with a select, and another wait until the lock is released--also = with a select. I've considered doing what you propose with a separate lock table, and may still do just = that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi,=20 Selecting a non-existent row won't acquire any locks that prevents = inserts from happening. One way to accomplish what you want is to create a separate insert lock table = consisting of a table name and a lock counter. Add a row for each table that you want to have these = insert locks on, and before performing any inserts, either update the corresponding row in the = insert lock table or select it for an update.=20 joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, =20 Thanks for the response and the suggestions. Doesn't SERIALIZABLE=20 level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than=20 that works just like the default REPEATABLE READ level? I've tried by = example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but=20 the results were the same. =20 Is there any way to make the second session block when both it and the = first one are 'locking' non-existence of a row? =20 Thanks in advance, =20 Alex =20 -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row =20 =20 Hi Alex! =20 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, =20 I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to = do this by using 'select ... for update', using the 'mysql' client=20 from two separate sessions as shown below: =20 Session 1: set AUTOCOMMIT=3D0; begin;
MySql database design.
I need a little help in constructing an order tracking database. We've decided to use MySQL mostly because it's the best supported database out in the wild. Does anyone have an example of an order tracking datamap? A link to a site with the basic flowchart would be a great help. The application is an everyday drycleaner shop.
INSERT performance
Hi, I am trying to find out that if I have a table without any Foreign keys and I am doing a lot of Inserts to it, should the performance be different if I have indexes or not? If I don't have any indexes, shouldn't I expect the performance for the Inserts to be better than it would be if I had some indexes? I am not seeing this behavior, the performance somehow seems to degrade without any indexing, would someone have a possible explanation (I do not have any Updates or Selects on this table.) Thanks _ Check out the coupons and bargains on MSN Offers! http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient SQL Statement
* Hassan Shaikh Which one of the following statements is more efficient? SELECT * FROM COUNTRY WHERE LEFT(CNNAME,1)='B'; Or SELECT * FROM COUNTRY WHERE CNNAME LIKE 'B%'; The second statement will normally be the most effective, because the server don't need to perform a function on the column for each row. However, the LIKE operator is relatively heavy, the _most_ effective in this case is probably: SELECT * FROM COUNTRY WHERE CNNAME='B' AND CNNAME'C' or SELECT * FROM COUNTRY WHERE CNNAME BETWEEN 'B' AND 'Bzzz' -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
Balazs Rauznitz wrote: However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? Well, MySql can only use 1 index per table to optimize a query. It's apparently using the index on ID, so it then needs to examine all records in the right ID range to see if they meet the sex='M' condition. You could build an index on both fields as one index, and MySql should be able to use it to resolve both parts of the query. create index id_and_sex_index on sex (id,sex); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Designer 4
Does anyone know if this or mycc has been compiled for Mac OS X? On Wednesday, January 21, 2004, at 10:40 AM, Hassan Shaikh wrote: Hi, I've learned from fabFORCE.net that DB Designer 4 is bought by MySQL AB. Could any one from MySQL AB please comment on the new acquisition and shed some light on integration plans with MySQLCC? Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count from three tables
* Bill - compuserve Thanks for the suggestion but unfortunately this seems to produce the product of the two tables `lghyperlink` and `lgsearch`. Yes, sorry about that. [...] Any other suggestions gratefully received - maybe this has to be done in two queries and the data manipulated in the application: less than ideal but if there is no other way... I have done some experimenting, but I have not found any better ways. Luckily it is relatively easy to do it in the app. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
Chuck Gadd said: Balazs Rauznitz wrote: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? Well, MySql can only use 1 index per table to optimize a query. It's apparently using the index on ID, so it then needs to examine all records in the right ID range to see if they meet the sex='M' condition. You could build an index on both fields as one index, and MySql should be able to use it to resolve both parts of the query. create index id_and_sex_index on sex (id,sex); Does that really matter? We are talking about 5 byte rows (+ overhead). What is the minimum size you get back from a disk read? 512 bytes? 8192 bytes? How many records will there be in 1 disk read? What is the chance that all records are M or F and thus the read would not be necessary? RAM might be cheap nowadays, but wouldn't you loose more by crowding out the key bufer as you gain by reducing in-memory comparisons (I seriously doubt it will save you any disk I/O)? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: char count
In the last episode (Jan 21), Steve Edberg said: At 12:17 PM -0600 1/21/04, Joseph S Brunzelle wrote: I'm try to write a query that will return the number of times a specified character appears in a string (I want known how many times the character M appears), but I cannot seem to figure out how to do that. To use the counts on a regular basis, it's probably easier to count them when you insert or update the record, and store those values in a separate column. That's what I ended up doing on a few genetics databases I was working on, anyway - You could also write a UDF to do it, which will be faster than the replace() idea, but requires you to write C. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Efficient SQL Statement
You could just use the benchmark function?
select BENCHMARK(1000, 'dfsfsdfs' like 'F%' ) /* 0.45 sec. */
select BENCHMARK(1000, 'dfsfsdfs' between 'F' and 'Fzzz' ) /* 0.55
sec. */
select BENCHMARK(1000, left('dfsfsdfs',1)='F' ) /* 0.79 sec. */
The times go up a little if the strings are a match.
Ed
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 21, 2004 1:11 PM
To: [EMAIL PROTECTED]
Cc: Hassan Shaikh
Subject: Re: Efficient SQL Statement
* Hassan Shaikh
Which one of the following statements is more efficient?
SELECT * FROM COUNTRY WHERE LEFT(CNNAME,1)='B';
Or
SELECT * FROM COUNTRY WHERE CNNAME LIKE 'B%';
The second statement will normally be the most effective, because the
server
don't need to perform a function on the column for each row. However,
the
LIKE operator is relatively heavy, the _most_ effective in this case
is
probably:
SELECT * FROM COUNTRY WHERE CNNAME='B' AND CNNAME'C'
or
SELECT * FROM COUNTRY WHERE CNNAME BETWEEN 'B' AND 'Bzzz'
--
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]
SELECT
Hi, How to see all tables in connected database? thx -seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
RE: SELECT
\u database SHOW TABLES Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- Ninety percent of this game is half mental. - Yogi Berra --- -Original Message- From: Seena Blace [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:04 PM To: [EMAIL PROTECTED] Subject: SELECT Hi, How to see all tables in connected database? thx -seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
RE: SELECT
mysql SHOW TABLES; Hope this helps. ~~Nick --- On Wed 01/21, Seena Blace [EMAIL PROTECTED] wrote: From: Seena Blace [mailto: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Wed, 21 Jan 2004 13:03:51 -0800 (PST) Subject: SELECT Hi,brHow to see all tables in connected database?brthx -seenabr brbrbr-brDo you Yahoo!?brYahoo! Hotjobs: Enter the Signing Bonus Sweepstakes ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help w/ a Query
I need to get total number of all rows in a table and also select some rows from the same table. I know how to do this using two queries, but was wondering if there is any way to do it in one query (one trip to the database). Thanks, -Jalil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locking 'non-existence' of a row
Alex, - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 10:28 PM Subject: RE: InnoDB locking 'non-existence' of a row Hello Heikki, Thank you for your reply and your explanation. It clarifies things and = explains some of the unexpected behavior I've observed (such as my own inability to insert to = the gap after locking it). In general, is there a place where I can find a good discussion on the = various locks used by InnoDB? I've seen references to various locks in InnoDB status output, but = wasn't sure what each kind was (S-locks, X-locks, etc.). This would be a great aid for helping = troubleshoot these kinds of problems. /mysql/innobase/lock/lock0lock.c contains a lengthy comment at the start of the file. And the full source code of the algorithms, too :). Papers by IBM researcher C. Mohan may also be of interest. Thank you for your time, Alex Zeltser Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange problem with index
Grzegorz Paszka [EMAIL PROTECTED] wrote: Hi. I use MySQL 4.0.17 from rpm. When I want insert new row by perl script to one of my table I get such error : DBD::mysql::st execute failed: Duplicate entry '- Modified the spec file provided by ...' for key 3 Table looks : mysql desc spak; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | id_lacz | int(11)| YES | MUL | NULL| | | selektor | int(4) | YES | MUL | NULL| | | data | mediumtext | YES | MUL | NULL| | +--++--+-+-+---+ 3 rows in set (0.01 sec) mysql show index from spak; +---++---+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+-+---+-+--++--++-+ | spak | 1 | spak_id_lacz |1 | id_lacz | A | 206999 | NULL | NULL | YES | BTREE | | | spak | 1 | spak_selektor |1 | selektor| A | 5 | NULL | NULL | YES | BTREE | | | spak | 1 | spak_data |1 | data| A | 344999 | 200 | NULL | YES | BTREE | | | spak | 1 | spak_fdata|1 | data| A | 1034998 |1 | NULL | YES | FULLTEXT | | +---++---+--+-+---+-+--++--++-+ 4 rows in set (0.04 sec) mysql select count(*) from spak; +--+ | count(*) | +--+ | 1034998 | +--+ 1 row in set (0.00 sec) Data length in data column is rather big; I did myisamchk -r , optimize table, repair table, mysqldump and insert it again, but problem still exists. I read that such error occurs when I insert no unique value to column with unique property. But as you can see I haven't UNIQUE key anywhere. I need help becouse I don't know what to do. Please, upload gzipped dump and insert query to the ftp://support.mysql.com/pub/mysql/Incoming and let me know a filename. -- 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: Efficient SQL Statement
* ed aka emierzwa at micron.com
You could just use the benchmark function?
select BENCHMARK(1000, 'dfsfsdfs' like 'F%' ) /* 0.45 sec. */
select BENCHMARK(1000, 'dfsfsdfs' between 'F' and 'Fzzz' ) /* 0.55
sec. */
select BENCHMARK(1000, left('dfsfsdfs',1)='F' ) /* 0.79 sec. */
I get a different result. On my (slow) computer, the BETWEEN operator is the
fastest:
mysql select BENCHMARK(1000, 'dfsfsdfs' like 'F%' );
1 row in set (2.17 sec)
mysql select BENCHMARK(1000, 'dfsfsdfs' between 'F' and 'Fzzz' );
1 row in set (2.06 sec)
mysql select BENCHMARK(1000, left('dfsfsdfs',1)='F' );
1 row in set (2.68 sec)
mysql select BENCHMARK(1000, 'dfsfsdfs' = 'F' and 'dfsfsdfs'
'Fzzz' );
1 row in set (2.13 sec)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select count from three tables
thanks for taking the time to look best bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 21 January 2004 20:19 To: [EMAIL PROTECTED] Cc: Bill - compuserve Subject: Re: select count from three tables * Bill - compuserve Thanks for the suggestion but unfortunately this seems to produce the product of the two tables `lghyperlink` and `lgsearch`. Yes, sorry about that. [...] Any other suggestions gratefully received - maybe this has to be done in two queries and the data manipulated in the application: less than ideal but if there is no other way... I have done some experimenting, but I have not found any better ways. Luckily it is relatively easy to do it in the app. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Efficient SQL Statement
I'm on WinXP, 2.6ghz. mysqld-nt Alpha 4.1.2
Either way, I was surprised to see the like to be in the top
performers and left() to be last.
Ed
---
* ed aka emierzwa at micron.com
You could just use the benchmark function?
select BENCHMARK(1000, 'dfsfsdfs' like 'F%' ) /* 0.45 sec. */
select BENCHMARK(1000, 'dfsfsdfs' between 'F' and 'Fzzz' ) /* 0.55
sec. */
select BENCHMARK(1000, left('dfsfsdfs',1)='F' ) /* 0.79 sec. */
I get a different result. On my (slow) computer, the BETWEEN operator is
the
fastest:
mysql select BENCHMARK(1000, 'dfsfsdfs' like 'F%' );
1 row in set (2.17 sec)
mysql select BENCHMARK(1000, 'dfsfsdfs' between 'F' and 'Fzzz' );
1 row in set (2.06 sec)
mysql select BENCHMARK(1000, left('dfsfsdfs',1)='F' );
1 row in set (2.68 sec)
mysql select BENCHMARK(1000, 'dfsfsdfs' = 'F' and 'dfsfsdfs'
'Fzzz' );
1 row in set (2.13 sec)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient SQL Statement
Roger Baklund wrote: Either way, I was surprised to see the like to be in the top performers and left() to be last. I suppose the LIKE operator is optimized for the case when it begins with a constant: mysql select BENCHMARK(1000, 'dfsfsdfs' like '%F%' ); 1 row in set (3.43 sec) MySql will use indexes to optimize LIKE queries if the expression does not start with a wildcard character. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too slow recovering mysqldump files
Thanks for your fast response, I'll adjust the autocommit parametr, too, and I'll will let you know if this improves the restore of my data base. Greetings Mikel From: Heikki Tuuri [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Wed, 21 Jan 2004 21:21:18 +0200 Mikel, - Original Message - From: Mikel - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 7:19 PM Subject: Re: Too slow recovering mysqldump files I'll set up those parameters in my.cnf and try again the recovery from the dump file. I'll hope these changes make faster the recovery. Dr. Ullrich suggested that you should set innodb_flush_log_at_trx_commit=0 during the big import, or wrap the big import inside a transaction. Otherwise, the speed may be restricted to 1 row / disk rotation = 100 rows/second. Thanks again Heikki, Mikel P.S. Do you recommend the innodb hot backup tool, does it do faster than the other options, or is a combination of both? InnoDB Hot Backup takes a BINARY backups of your database. If you need to restore from a backup taken with InnoDB Hot Backup, you do not need to import table dumps. Thus restoration is much faster than if you use table dumps as a backup (table dump = LOGICAL backup). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ From: Heikki Tuuri [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Wed, 21 Jan 2004 05:10:27 +0200 Mikel, it is apparently disk-bound. I recommend setting innodb_buffer_pool_size as high as 1.4 GB during such big import. Adjust innodb_log_file_size accordingly, and make key_buffer smaller during the big import. Help is coming: Marko Mäkelä is writing a compressed InnoDB table format, which can squeeze a typical table to 1 / 4 the size of a normal InnoDB table. I believe the compressed format will be available in October 2004. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:32 AM Subject: Re: Too slow recovering mysqldump files Here we are my options: set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M I follow the example that came here: http://www.mysql.com/doc/en/InnoDB_start.html; Greetings and best regards Mikel From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Too slow recovering mysqldump files Date: Mon, 19 Jan 2004 22:44:50 +0200 Mikel, have you set the size of the InnoDB log files as recommended in the manual? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mikel - [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 19, 2004 7:25 PM Subject: Too slow recovering mysqldump files Hi list, does anyone know a faster way to recover a mysqldump file cause When I recovered one dump file it took 26 hours ! to finish, I think it's too slow. Thnx in advanced, greetings MySQL server 3.23.58 RedHat 7.3 4GB RAM 2 scsi disk via fiber channel (333GB each) 2 processor Xeon 1.6GHZ dump file size: 2.5 GB ibdata: 11GB innodb tables key_buffer=850M innodb_buffer_pool_size=850M table_cache=1500 _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- 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] _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ _ MSN Fotos: la forma más fácil de compartir e imprimir fotos.
Re: Efficient SQL Statement
* Chuck Gadd Roger Baklund wrote: Either way, I was surprised to see the like to be in the top performers and left() to be last. I suppose the LIKE operator is optimized for the case when it begins with a constant: mysql select BENCHMARK(1000, 'dfsfsdfs' like '%F%' ); 1 row in set (3.43 sec) MySql will use indexes to optimize LIKE queries if the expression does not start with a wildcard character. Yes, but that is not relevant in the case with the BENCHMARK() function, is it? No table is involved... -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Kill alter table process?
I run alter today on table with 380,000 records. 'Alter' was adding
column to the table. It took 10 minutes and server load went up to
15.
alter table info add column interactive_email enum('on','off') NOT NULL
default 'on';
Here comes my questions:
#1 If I kill the process after it was running for 5 minutes. Would it
update some records and some records not?
#2 How dangerous is to stop alter?
#3 What is a good way to stop alter or update without damaging database?
Thanks
_
Find high-speed net deals comparison-shop your local providers here.
https://broadband.msn.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help w/ a Query
* jalil I need to get total number of all rows in a table and also select some rows from the same table. I know how to do this using two queries, but was wondering if there is any way to do it in one query (one trip to the database). From version 4 you can use the SQL_CALC_FOUND_ROWS option for the SELECT statement in combination with LIMIT. You have to do a second select (SELECT FOUND_ROWS()), but you only read the table once. URL: http://www.mysql.com/doc/en/SELECT.html In some cases the MAX-CONCAT trick can be used to get specific records in GROUP BY queries. This could be used to get the count and for instance the oldest row and the newest row in the same query. URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html If SQL_CALC_FOUND_ROWS can't be used in your case, I think you are better of doing two queries, SELECT COUNT(*) FROM tablename is very fast without a WHERE clause. URL: http://www.mysql.com/doc/en/GROUP-BY-Functions.html#IDX1414 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kill alter table process?
At 05:34 PM 1/21/2004, you wrote:
I run alter today on table with 380,000 records. 'Alter' was adding
column to the table. It took 10 minutes and server load went up to
15.
alter table info add column interactive_email enum('on','off') NOT NULL
default 'on';
Here comes my questions:
#1 If I kill the process after it was running for 5 minutes. Would it
update some records and some records not?
#2 How dangerous is to stop alter?
#3 What is a good way to stop alter or update without damaging database?
Thanks
Mike,
Alter creates a copy of the table so stopping it in the middle
won't hurt anything. If the Alter succeeds, it renames the new table to the
old table name.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on 2 different tables
This example is simplified. I hope you understand Each table has one field called ID which is an integer and is the primary key Table 1 List of Programs 1 2 3 4 5 6 7 8 9 10 Table 2 Programs members have joined 1 5 8 Here is the scenario. I want to compare the values in table one and Table 2 if the value in table one is not in table 2 then display the number to the screen? Can somebody show me what the sql statement would look like? Thanks Randy
Re: Question on 2 different tables
I believe this would work select table1.* table1 left join table2 on (table1.id=table2.id) where tab2.id not null The left join will match the values together. Null values are inserted where matches are not made. Since 4 will not make a match null values will be put in place of the table2 values. Joe On Wednesday, January 21, 2004, at 08:26 PM, Randy Johnson wrote: This example is simplified. I hope you understand Each table has one field called ID which is an integer and is the primary key Table 1 List of Programs 1 2 3 4 5 6 7 8 9 10 Table 2 Programs members have joined 1 5 8 Here is the scenario. I want to compare the values in table one and Table 2 if the value in table one is not in table 2 then display the number to the screen? Can somebody show me what the sql statement would look like? Thanks Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kill alter table process?
Thanks for good explnation.
Some more questions:
#1Does 'update' also create temporary table?
#2What happend with old table after alter?
From: mos [EMAIL PROTECTED]
To: Mike Mapsnac [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: Kill alter table process? Date: Wed, 21 Jan 2004 19:12:07
-0600
At 05:34 PM 1/21/2004, you wrote:
I run alter today on table with 380,000 records. 'Alter' was adding
column to the table. It took 10 minutes and server load went up to
15.
alter table info add column interactive_email enum('on','off') NOT NULL
default 'on';
Here comes my questions:
#1 If I kill the process after it was running for 5 minutes. Would it
update some records and some records not?
#2 How dangerous is to stop alter?
#3 What is a good way to stop alter or update without damaging database?
Thanks
Mike,
Alter creates a copy of the table so stopping it in the middle
won't hurt anything. If the Alter succeeds, it renames the new table to the
old table name.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Learn how to choose, serve, and enjoy wine at Wine @ MSN.
http://wine.msn.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on 2 different tables
SELECT * FROM tableone WHERE value NOT IN (SELECT column FROM tabletwo) ; On Wed, 2004-01-21 at 15:26, Randy Johnson wrote: This example is simplified. I hope you understand Each table has one field called ID which is an integer and is the primary key Table 1 List of Programs 1 2 3 4 5 6 7 8 9 10 Table 2 Programs members have joined 1 5 8 Here is the scenario. I want to compare the values in table one and Table 2 if the value in table one is not in table 2 then display the number to the screen? Can somebody show me what the sql statement would look like? Thanks Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kill alter table process?
At 09:04 PM 1/21/2004, you wrote:
Thanks for good explnation.
Some more questions:
#1Does 'update' also create temporary table?
Definitely not. Otherwise if an Update changed a single row it would have
to create a temporary table, and of course it doesn't do that. Boy,
wouldn't that be slow.bg
One more thing to think about when using Alter Table is if you have a
large table, it needs enough disk space to create a duplicate of the table
(data) that you are altering . So if you have a 4gb table, you better have
at least another 4gb free in your database directory.
#2What happend with old table after alter?
It is dropped if the alter table is successful and the temp table is
renamed to the old table name.
Mike
From: mos [EMAIL PROTECTED]
To: Mike Mapsnac [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: Kill alter table process? Date: Wed, 21 Jan 2004 19:12:07
-0600
At 05:34 PM 1/21/2004, you wrote:
I run alter today on table with 380,000 records. 'Alter' was adding
column to the table. It took 10 minutes and server load went up to
15.
alter table info add column interactive_email enum('on','off') NOT NULL
default 'on';
Here comes my questions:
#1 If I kill the process after it was running for 5 minutes. Would it
update some records and some records not?
#2 How dangerous is to stop alter?
#3 What is a good way to stop alter or update without damaging database?
Thanks
Mike,
Alter creates a copy of the table so stopping it in the middle
won't hurt anything. If the Alter succeeds, it renames the new table to
the old table name.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Non-Root Installation on Solaris
I would like to install MySQL on a Solaris 8(SPARC) system without the need to have 'root' previliges. I've installed it into my own directory (ie./home/my_place/) with any problems. I'm able to run'mysql_install_db and 'mysqld_safe' also without any problems. BUT when I try to run something like 'mysqlshow mysql' I get a message stating I do not have access! How do I setup/configure/stop/start MySQL without the need for 'root' access? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql database design.
You can find a number of good starting point data models here: http://www.databaseanswers.com/data_models You might also check out some of the applications on sites like www.hotscripts.com and www.sourceforge.net to see how they have structured their database for similar projects. (or maybe you will find something ready to use). olinux --- Brian Duke [EMAIL PROTECTED] wrote: I need a little help in constructing an order tracking database. We've decided to use MySQL mostly because it's the best supported database out in the wild. Does anyone have an example of an order tracking datamap? A link to a site with the basic flowchart would be a great help. The application is an everyday drycleaner shop. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary table in 4.0.17 on Windows W2000 and NT4
We have a problem to have a long life for the temporary table on innodb mode. We create the table, but after some minutes of inactivity the sistem drops the table. My.ini in as follow. Which is the parameter to correct the problem ? Tks Massimi - # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable=key_buffer=16K set-variable=max_allowed_packet=16M set-variable=thread_stack=64K set-variable=table_cache=4 set-variable=sort_buffer=1024K set-variable=net_buffer_length=2K #PER REPLICA server-id=19 master-host=pissarro report-host=pissarro master-user=root #skip-slave-start replicate-wild-ignore-table=OMTWRK.WRK_% replicate-wild-ignore-table=omtwrk.wrk_% set-variable=slave-net-timeout=172800 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir=C:\mysql\ #datadir=C:\AnnaNoBck\DATI\MySQL\data\ datadir=D:\applicaz\mysql\Data\ # log-error=d:\applicaz\mysql\data\mysql.err # Uncomment the following if you are NOT using BDB tables skip-bdb # Uncomment the following if you are using Innobase tables innodb_data_file_path=\InnoData\HymnOMT:10M:autoextend #innodb_data_home_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB #innodb_log_group_home_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB\InnoLog #innodb_log_arch_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB\InnoLog innodb_data_home_dir=D:\applicaz\mysql\InnoDB innodb_log_group_home_dir=D:\applicaz\mysql\InnoDB\InnoLog innodb_log_arch_dir=D:\applicaz\mysql\InnoDB\InnoLog set-variable=innodb_mirrored_log_groups=1 set-variable=innodb_log_files_in_group=3 set-variable=innodb_log_file_size=5M set-variable=innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable=innodb_buffer_pool_size=16M set-variable=innodb_additional_mem_pool_size=10M set-variable=innodb_file_io_threads=4 set-variable=innodb_lock_wait_timeout=50 default-table-type=innodb #solo =4.0.14 permette di mantenere alla definizione di max_binlog_size la dimensione del file di relay #set-variable=max_relay_log_size=0 [mysqldump] quick set-variable=max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable=key_buffer=8M set-variable=sort_buffer=8M [myisamchk] set-variable=key_buffer=8M set-variable=sort_buffer=8M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe QueryInterval=10 - Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
So, how do you REALLY install MySQL in Mac OS X (Panther)?!
Ok, I've read the release notes, the manual, the threads on this mailing list, installed/removed/re-installed about 5 times, tried 4.0.1, 4.1, 5.0, and I can't get MySQL to run on my Mac (dual-proc 867MHz, 768MB RAM). When I run configure, it tells me everything is hunky-dory and that I should just be happy and run mysqld. I try to run mysqld and it immediately aborts and tells me that /tmp/mysql.sock isn't set up. If I try to run mysqladmin -u root password password just like the manual tells me to, it aborts as above and tells me to run mysqld (or mysqld_safe). The manual tells me I can just click on the dmg file and follow the instructions. I've done that for both the distribution and the startup item several times (and re-started several times in between). I've read the manual about security and the root user account as the message instructed me to do. No help. I even tried to compile the control center using the instructions I found for that, installed Qt as instructed, compiled it (I think, the instructions for that were along the lines of: Now just compile it and you're all done! Hah! Do you hear me? Hah, I say.) The instructions don't tell me where to install it or how to test for a valid installation. When I try to compile CC it says install Qt first. I did. 3 times. In 3 different places. Copied all of the include and lib files all over the freakin' hard drive to try to make it happy. Not happy. Ok, now, are there REAL instructions for installing some working version of MySQL on a Mac somewhere? What do I REALLY have to do? Has someone actually documented the entire process or does the documentation consist of the vague, scattered hints I've been able to find? I'd love to check out MySQL, but do I have to learn a secret handshake or pledge allegiance to Open Source or something? Thanks for any help. Sorry to rant, but I've been working on this for three days now. - Dan P.S. Any notes on using it with Xcode would be appreciated, too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
