porting Oracle schema to MySQL
Hi, I'm a newbie working on a big project. The idea is to transport an existing Oracle database at http://www.ebi.ac.uk/arrayexpress/ to a local MySQL server. The Schema, scripts and loader for Oracle can be found in the website. But how to create an exactly same database with mySQL? I have no clue. Really need help. Thanks in advance. Claire __ 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]
Re: porting Oracle schema to MySQL
look at http://www.ispirer.com it provides a tool SQLWays, which is a migrating tool to migrate any DataBase to MySQL with Data. greets, Arun. --- Claire Lee [EMAIL PROTECTED] wrote: Hi, I'm a newbie working on a big project. The idea is to transport an existing Oracle database at http://www.ebi.ac.uk/arrayexpress/ to a local MySQL server. The Schema, scripts and loader for Oracle can be found in the website. But how to create an exactly same database with mySQL? I have no clue. Really need help. Thanks in advance. Claire __ 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] 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: Select older than minutes
On Wednesday 28 January 2004 06:00, Scott Haneda wrote: I have done this so many times.. SELECT * FROM `support_start` WHERE TO_DAYS(NOW()) - TO_DAYS(date) 7 I have scoured (more like glanced :-)) at the docs on http://www.mysql.com/doc/en/Date_and_time_functions.html and can not seem to find how to get all records where a timestamp(14) is older than x minutes. You haven't said which version of MySQL you're using. That said: DATE_SUB. select * from support_start where time_stamp_field date_sub(now, interval x minute); Sub for time_stamp_field and x as needed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select older than minutes
on 01/28/2004 12:03 AM, Duncan Hill at [EMAIL PROTECTED] wrote: On Wednesday 28 January 2004 06:00, Scott Haneda wrote: I have done this so many times.. SELECT * FROM `support_start` WHERE TO_DAYS(NOW()) - TO_DAYS(date) 7 I have scoured (more like glanced :-)) at the docs on http://www.mysql.com/doc/en/Date_and_time_functions.html and can not seem to find how to get all records where a timestamp(14) is older than x minutes. You haven't said which version of MySQL you're using. That said: DATE_SUB. select * from support_start where time_stamp_field date_sub(now, interval x minute); Sub for time_stamp_field and x as needed. Version 3.x, I assume this is a 4.X feature since you brought it up? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Security
Hi, I'm newbie in MYSQL. Would please tell me about Mysql Security ? Generally you can find a lot of information about MySQL on http://www.mysql.com/doc/en/ About your question you can read here: http://www.mysql.com/doc/en/Privilege_system.html Mikhail. - Original Message - From: Timotius Alfa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 5:16 AM Subject: MYSQL Security Hi All, I'm newbie in MYSQL. Would please tell me about Mysql Security ? I used Windows2000 for mysql server. thank you - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Security
Timotius Alfa [EMAIL PROTECTED] wrote: Hi All, I'm newbie in MYSQL. Would please tell me about Mysql Security ? I used Windows2000 for mysql server. Privilege system of MySQL is described in the following section of the manual: http://www.mysql.com/doc/en/Privilege_system.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB on delete, on update
Hi out there, i´m using InnoDB with foreign key constraints and i´m looking for the default behavior of ON DELETE and ON UPDATE. Secondly, what does the ON UPDATE NO ACTION option means? Ist the way it is used in db2, that every child row must have some matching parent row. If this condition is not met,the Update fails and all changes are rolled back? Or is the Update done? What is the meaning of ON UPDATE CASCADE and ON UPDATE SET NULL?? Thanks for your help
available spaci in InnoDB data files !??!
I need help (ASAP). I'm using MySQL (4.0.15) with InnoDB. QUESTION: How can I check (or monitor) the available space in my InnoDB data files ??? my.cnf: ... innodb_data_file_path=ibdata1:650M;ibdata2:650M ... _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: available spaci in InnoDB data files !??!
Hi, show table status; In the last field (Comment) you could find it. Mikhail. - Original Message - From: Tile Tatonko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 10:48 AM Subject: available spaci in InnoDB data files !??! I need help (ASAP). I'm using MySQL (4.0.15) with InnoDB. QUESTION: How can I check (or monitor) the available space in my InnoDB data files ??? my.cnf: ... innodb_data_file_path=ibdata1:650M;ibdata2:650M ... _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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]
Group By Problem
Say I have the following table (TBL): KEY GRP VAL 1 A 2 2 A 3 3 A 1 4 B 2 5 B 1 6 B 3 select KEY, max(VAL) from TBL group by GRP gives: KEY max(VAL) 1 3 4 3 the desired result though is: KEY max(VAL) 2 3 6 3 any ideas on how to achieve this ? Apparently this query is invalid in other RDBMs (like Oracle) which don't allow it. The rule is that one should not have columns in your select that are not in the group by section, except for the grouping functions. MySQL though does allow it, but results can be strange as above. I realise that some selects don't make sense eg: select KEY, max(VAL), min(VAL) from TBL group by GRP (which KEY should be returned ?) I suppose one possibilty for solving my problem would be: 1. select GRP, max(VAL) from TBL group by GRP 2. for each row in 1.: select KEY from TBL where GRP=? and VAL=? Any other ideas or comments ? regards Jurgen __ http://www.webmail.co.za/dialup Webmail ISP - Cool Connection, Cool Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very big IN ( $value )
Hi, I do my query from Perl DBI, it has a very big IN ( $value ). The query looks like: SELECT id FROM user WHERE sex='m' AND hobby_id='3' AND id IN ( $value ); $value looks like: 102, 233, 445, 543 ... 3443, 332 very long list. It might be about 10,000 id in $value. What is the maximum value I can pass in a query? Is this the right way to do it? (I mean, using a very big value inside IN) Thank you, --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very big IN ( $value )
From: Batara Kesuma [EMAIL PROTECTED] It might be about 10,000 id in $value. What is the maximum value I can pass in a query? Is this the right way to do it? (I mean, using a very big value inside IN) WIth 10,000 items in an IN () condition, my guess is that the list is a result of another query. So either JOIN the two tables and combine the two queries, or create a temporary table (with appropriate indices!) and store the results of the first query in the temporary table. Then you can JOIN these tables on id. I don't know if there is a strict limit on the number of items in an IN() condition, but you will probably hit the limit of the total length of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very big IN ( $value )
On 28-Jan-2004 Batara Kesuma wrote: Hi, I do my query from Perl DBI, it has a very big IN ( $value ). The query looks like: SELECT id FROM user WHERE sex='m' AND hobby_id='3' AND id IN ( $value ); $value looks like: 102, 233, 445, 543 ... 3443, 332 very long list. It might be about 10,000 id in $value. What is the maximum value I can pass in a query? Is this the right way to do it? (I mean, using a very big value inside IN) You'll be limited to the 'max_allowed_packet' (1 megabyte) query string length. Assuming 6 digits in an id, you get: 10,000 * (6 + 2) [comma+space] = 80,000 bytes (+ rest of query) So you will be okay with that particular string, but that is going to put a nasty hit on the server unless you index the table with something like: KEY idx_hsi (hobby_id, sex, id). At a higher level you might rethink how/why you're doing it that way. This is a subselect done in two steps. Look if you could combine/modify your original query (that produced $value) and this query to a single JOIN query. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb firsttime startup error
I just ran mysql 4.0.15 on a Mac for the first time and got the following in my .err file: InnoDB: a new database to be created! 040128 7:40:24 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040128 7:40:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 20 MB InnoDB: Database physically writes the file full: wait... 040128 7:40:45 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 20 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040128 7:41:08 InnoDB: Started 040128 7:41:08 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040128 7:41:08 Aborting 040128 7:41:08 InnoDB: Starting shutdown... 040128 7:41:11 InnoDB: Shutdown completed 040128 7:41:11 /Library/MySQL/libexec/mysqld: Shutdown Complete 040128 07:41:11 mysqld ended How do I create the mysql.host file and where should it be located?
Re: my.cnf on Shared Hosting
Hassan Shaikh [EMAIL PROTECTED] wrote: Hi, I want --ansi option for MySQL setting to ensure ANSI compatibility. Most shared hosting providers don't allow access to my.cnf. Is there anyway to do this on the fly in my script, PHP/Perl sample would be appreciated. You can do it only from version 4.1 with SET statement: http://www.mysql.com/doc/en/ANSI_mode.html http://www.mysql.com/doc/en/Server_SQL_mode.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: porting Oracle schema to MySQL
sounds like I reinvented the wheel but here's how I did it: 1. reverse engineer Oracle database with ERWin 2. write a little perl program to map/convert column types and convert syntax for alter table clauses 3. write a sqlplus script which dynamically creates a select/spool for a given table from dba_tab_columns 4. use load data to suck in flat files from step 3 there were some things I had to do manually/one-off but that was surprisingly reliable for most of the migration. Sid Lane DBA - Site Operations TWCi |-+ | | Arunachalam | | | [EMAIL PROTECTED]| | | .uk | | || | | 01/28/2004 02:46 | | | AM | | || |-+ --| | | | To: Claire Lee [EMAIL PROTECTED] | | cc: [EMAIL PROTECTED] | | Subject: Re: porting Oracle schema to MySQL | --| look at http://www.ispirer.com it provides a tool SQLWays, which is a migrating tool to migrate any DataBase to MySQL with Data. greets, Arun. --- Claire Lee [EMAIL PROTECTED] wrote: Hi, I'm a newbie working on a big project. The idea is to transport an existing Oracle database at http://www.ebi.ac.uk/arrayexpress/ to a local MySQL server. The Schema, scripts and loader for Oracle can be found in the website. But how to create an exactly same database with mySQL? I have no clue. Really need help. Thanks in advance. Claire __ 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] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't login from nettwork
I have mysql 4.0.16 on my windows machine and have just installed suse linux on another machine. It comes with mysql 4.0.15. After I got the user setup I was able to use the mysql client on the windows machine to connect to mysql on the linux machine I even used mysqldump piped to mysql to dump a database to the new server. But now I can't seem to login anymore. I don't think I changed any settings on the linux machine so I am at a loss as to why I am getting this error. ERROR 2013: Lost connection to MySQL server during query That happens immediately after I type in mysql -h 192.168.1.2 -u cdw -pxx and hit enter. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting auto_increment field in an INNODB table
Hassan Shaikh [EMAIL PROTECTED] wrote: Hi, How do I reset an AUTO_INCREMENT column? My table type is InnoDB and the method mentioned in the manual is not applicable. I am using MySQL 4.0.17. If you want to start auto_increment sequence with value bigger than current counter value, you can just add dummy row and specify explicitly column value equal to needed_value-1. Then delete this row. Otherwise you should recreate the table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting auto_increment field in an INNODB table
How do I reset an AUTO_INCREMENT column? My table type is InnoDB and the method mentioned in the manual is not applicable. I am using MySQL 4.0.17. Otherwise you should recreate the table. Or, if you no longer need any of the data, simply use TRUNCATE. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comparing one field to another.
I have been beating my head against the wall for a couple of days now and cannot figure this out. I am hoping that it can be done. I have two tables each with only one field. Table 1 - emailAddress (this contains things like [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc) Table 2 - domains (this contains acme.com, test.com, funny.com, etc) What I need is the ability to count the number of email address and list them for each domain. These tables are coming from two different sources so I cannot control what I recieve. I tried to use a LIKE but that does not seem to work. I am hoping this makes sense. Thanks, -brandon
CALCULATING DATES
Hi, I want to be able to show the total of the subtraction of the returned date from the from date for each department for each month. This to be done on a range of a year, and then when the next month comes round for the calculation to take that month into account, so, shifting the range to calculate by a month forward. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By Problem
* Lightware Software Say I have the following table (TBL): KEY GRP VAL 1 A 2 2 A 3 3 A 1 4 B 2 5 B 1 6 B 3 select KEY, max(VAL) from TBL group by GRP gives: KEY max(VAL) 1 3 4 3 the desired result though is: KEY max(VAL) 2 3 6 3 any ideas on how to achieve this ? There is a special page in the manual for this problem, one of my favorite sql hacks is the MAX-CONCAT trick: URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb firsttime startup error
Kev [EMAIL PROTECTED] wrote: I just ran mysql 4.0.15 on a Mac for the first time and got the following in my .err file: InnoDB: a new database to be created! 040128 7:40:24 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040128 7:40:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 20 MB InnoDB: Database physically writes the file full: wait... 040128 7:40:45 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 20 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040128 7:41:08 InnoDB: Started 040128 7:41:08 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040128 7:41:08 Aborting 040128 7:41:08 InnoDB: Starting shutdown... 040128 7:41:11 InnoDB: Shutdown completed 040128 7:41:11 /Library/MySQL/libexec/mysqld: Shutdown Complete 040128 07:41:11 mysqld ended This error isn't related to the InnoDB. It means that you doesn't have privilege tables or MySQL doesn't have permission on the data dir. Check if privilege table files exist in the directory of the database mysql. If so, check permissions on the files and data dir. If files don't exist, you should run mysql_install_db script to install grant tables. How do I create the mysql.host file and where should it be located? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting auto_increment field in an INNODB table
Chris Boget [EMAIL PROTECTED] wrote: How do I reset an AUTO_INCREMENT column? My table type is InnoDB and the method mentioned in the manual is not applicable. I am using MySQL 4.0.17. Otherwise you should recreate the table. Or, if you no longer need any of the data, simply use TRUNCATE. TRUNCATE TABLE doesn't reset auto_increment value for InnoDB tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Innodb Database
In the MySQLdocumentation, it does not clearly explains how a Tablespace is created. Under Creating a Tablespace heading ( 14.4.4), it says how a Innodb database is created but not tablespace. Also how exactly you create an Innodb Database. Well it says in the documentation that when you enter the cmd mysqld it creates an Innodb Database, what if I want to have more than one Innodb Database within a server. Do I have to run multiple instances of mysqld. Can someone help here? Ansari,Raza GEFA GE Financial Assurance GE Insurance, Americas 6604 West Broad Street Richmond VA 23230-1702 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CALCULATING DATES
Hi, You can use TO_DAYS() and FROM_DAYS() functions for what you need. All details about Time and Date functions : http://www.mysql.com/doc/en/Date_and_time_functions.html Regards, Gelu _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER Permanent e-mail address : [EMAIL PROTECTED] -Original Message- From: Paul Hayer [mailto:[EMAIL PROTECTED] Sent: Wednesday, 28 January, 2004 4:46 PM To: '[EMAIL PROTECTED]' Subject: CALCULATING DATES Hi, I want to be able to show the total of the subtraction of the returned date from the from date for each department for each month. This to be done on a range of a year, and then when the next month comes round for the calculation to take that month into account, so, shifting the range to calculate by a month forward. Thanks in advance. -- 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: Creating Innodb Database
In the MySQLdocumentation, it does not clearly explains how a Tablespace is created. Under Creating a Tablespace heading ( 14.4.4), it says how a Innodb database is created but not tablespace. Also how exactly you create an Innodb Database. Well it says in the documentation that when you enter the cmd mysqld it creates an Innodb Database, what if I want to have more than one Innodb Database within a server. Do I have to run multiple instances of mysqld. Can someone help here? Add the following to your config file - Note: this is for 4.0.x - not sure if 4.1.1 is different. # Uncomment the following if you are using Innobase tables innodb_data_home_dir=/usr/local/mysql/libdata innodb_data_file_path=libdata1:1000M;libdata2:1000M;libdata3:1000M innodb_log_group_home_dir = /usr/local/mysql/innologs innodb_log_arch_dir = /usr/local/mysql/innologs 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=5M innodb_flush_log_at_trx_commit=2 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=200M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=120 This will create 3 files where the innodb tables will be put. With 4.0.x and before you can not assign tables to a particular file(tablespace). You can do this with 4.1 however. You can create as many databases as you need since it is the table that are InnoDB and not the database itself. I hope this helps. Soctt Pippin [EMAIL PROTECTED]
Re: updates on slave server??
Is there a way to check that data on slave server is the same as data on master ? Thanks From: Paul DuBois [EMAIL PROTECTED] To: Mike Mapsnac [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: updates on slave server?? Date: Mon, 26 Jan 2004 21:58:42 -0600 At 0:49 + 1/27/04, Mike Mapsnac wrote: Hello I found today that some data on slave server were different than on master server. I dont know how that can happen. Slave server was running, show slave status show no errors If I make some updates on slave server. Does this updates will be reflected on master server? Or slave cannot synchronize master server? You're not supposed to update the slave server, except by means of updates from the master. Updates go from master to slave, not the other way around. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ _ 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: InnoDB on delete, on update
Gitte und Ingolf [EMAIL PROTECTED] wrote: Hi out there, i.m using InnoDB with foreign key constraints and i.m looking for the default behavior of ON DELETE and ON UPDATE. Secondly, what does the ON UPDATE NO ACTION option means? Ist the way it is used in db2, that every child row must have some matching parent row. If this condition is not met,the Update fails and all changes are rolled back? Or is the Update done? Currently NO ACTION does nothing. In a future it will work the same as RESTRICT: if you update referenced column and there are any matching rows in the referencing table, the update will fail. What is the meaning of ON UPDATE CASCADE and ON UPDATE SET NULL?? If you specify ON UPDATE CASCADE, every time you update row in the parent table, InnoDB will automatically update corresponding foreign key column in all matching rows in the child table to the same value. ON UPDATE SET NULL means that every time you update row in the parent table, InnoDB will automatically set corresponding foreign key column in every matching rows of the child table to NULL. -- 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]
UNION equivilent required for 3.23.37
Hi, I have looked for answers on the net but havent managed to apply the suggestions to my example; I would appreciate any help! I have the following set up: root_table (root_table_id, table_one_id, table_two_id, date) table_one (table_one_id, col1) table_two (table_two_id, col2) I want to use one query to join root_table with both the other tables, getting col1 out if root_table.table_one_id is not NULL and col2 out if root_table.table_two_id is not NULL. I need to then ORDER BY root_table.date With a union, I would have: (SELECT root_table.col1, date FROM root_table INNER JOIN table_one ON root_table.table_one_id = table_one.table_one_id) UNION (SELECT root_table.col2, date FROM root_table INNER JOIN table_two ON root_table.table_two_id = table_two.table_two_id) ORDER BY date But I cant do UNION's in MySQL, so how can I do this? Any help appreciated, Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: CALCULATING DATES
Thanks for the reply, I think SUM(DAYOFYEAR) has solved it. However is there a way of subtracting weekends and showing departments even if they have no entries( ie to show the dept and 0 as it's result)? Cheers. -Original Message- From: Gelu Gogancea [mailto:[EMAIL PROTECTED] Sent: 28 January 2004 15:08 To: Paul Hayer; [EMAIL PROTECTED] Subject: RE: CALCULATING DATES Hi, You can use TO_DAYS() and FROM_DAYS() functions for what you need. All details about Time and Date functions : http://www.mysql.com/doc/en/Date_and_time_functions.html Regards, Gelu _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER Permanent e-mail address : [EMAIL PROTECTED] -Original Message- From: Paul Hayer [mailto:[EMAIL PROTECTED] Sent: Wednesday, 28 January, 2004 4:46 PM To: '[EMAIL PROTECTED]' Subject: CALCULATING DATES Hi, I want to be able to show the total of the subtraction of the returned date from the from date for each department for each month. This to be done on a range of a year, and then when the next month comes round for the calculation to take that month into account, so, shifting the range to calculate by a month forward. Thanks in advance. -- 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]
Online backup of InnoDB Tables
I see that InnoDB hot backup can be used so that a running InnoDB database can be backed up with out setting any locks. Can any Open File backup tool be used with MySQL InnoDB tables or does the hotbackup product do something special that others don't? What are some of the things that I should be considering when looking to do an online backup of InnoDB tables? Cheers, Mauro
RE: Very big IN ( $value )
I've had tens of thousands of items in an IN list without failure, but it seems that when you get that many it takes a very long time to parse. The speed thing bothers me, so I'm toying with a new design, but it's a big enough system that it's slow going. J -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 4:00 AM To: [EMAIL PROTECTED] Subject: Very big IN ( $value ) Hi, I do my query from Perl DBI, it has a very big IN ( $value ). The query looks like: SELECT id FROM user WHERE sex='m' AND hobby_id='3' AND id IN ( $value ); $value looks like: 102, 233, 445, 543 ... 3443, 332 very long list. It might be about 10,000 id in $value. What is the maximum value I can pass in a query? Is this the right way to do it? (I mean, using a very big value inside IN) Thank you, --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Online backup of InnoDB Tables
Bonjour Mauro, MM I see that InnoDB hot backup can be used so that a running InnoDB MM database can be backed up with out setting any locks. MM Can any Open File backup tool be used with MySQL InnoDB tables or does MM the hotbackup product do something special that others don't? What are MM some of the things that I should be considering when looking to do an MM online backup of InnoDB tables? It's perform a binary copy. So backup and restore are very fast. -- Cordialement, Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search multiple fields across multiple tables
Hi everyone, Sorry to ask this question, because this is going to get complicated... Okay - what I need to do is be able to search for data across multiple fields which are organized in multiple tables. Here is a basic description of my database schema (not quite all of the fields...): Table listings - ListingID - CatalogNumber* - Title* - ComposerID - ArrangerID - PublisherID - Price - CategoryID Table arrangers - ArrangerID - ArrangerLname* Table publishers - PublisherID - PublisherName* Table composers - ComposerID - ComposerLname* Table categories - CategoryID - Alias* (* = field to search against) My queries have been based on categories, so for a given category, I can easily pull out all of the appropriate records: ?php $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; ? But what I need to be able to do is search the CatalogNumber, Title, Arranger, Composer, Publisher, and Description for a given search string. I tried to put that into one big query and hung the database pretty badly. So I resorted to doing five separate queries, and then merging the result arrays into one array. This however, does not quite do what I need it to do, because now I have to group all of the search results according to their categories (all listings in category x displayed together, all listings in category y displayed together, etc). My problem is that I don't even know where to start. Can it be done with MySQL, or does it need to be done on the PHP side? -Erich- PS: If you want to see all of this in action, you can go to www.bvdpress.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing one field to another.
I'm not sure you can do that in a single select - one is a count, and the other is a list. In fact, finding the count requires a sub-select, or two seperate selects. Not sure what your columns are, so I will assume that emailAddress has a column called email, and domain has one called domain. SELECT count(*) FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)); This takes the length of domain and uses that value to grab those characters from the email address. So [EMAIL PROTECTED] becomes RIGHT([EMAIL PROTECTED], 9) which equals acme.com, which is the email-address domain, and thus you can do a compare. To select a list, just change the query to be, SELECT email FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)) AND domain=?; I am assuming you want to find all email addresses for a single domain of your choosing. Note that using RIGHT and LENGTH in the where clause will prevent the optimizer from using indexes. I don't have a database in front of me, so I can't check that the statements are perfect, but they should give you the idea. David. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 6:31 AM Subject: comparing one field to another. I have been beating my head against the wall for a couple of days now and cannot figure this out. I am hoping that it can be done. I have two tables each with only one field. Table 1 - emailAddress (this contains things like [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc) Table 2 - domains (this contains acme.com, test.com, funny.com, etc) What I need is the ability to count the number of email address and list them for each domain. These tables are coming from two different sources so I cannot control what I recieve. I tried to use a LIKE but that does not seem to work. I am hoping this makes sense. Thanks, -brandon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on a G5/OSX/MySql4.0.17
I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). 3) Give lots of memory to InnoDB, I'll share my settings below. 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join buffers.. I'd up the sort/read/join buffers to maybe 10MBytes, or even 20Mbytes, if you need to drop Key buffer to 1500M to give you the space for the others. We got OKish results on MyISAM with the larger sort/read/join buffers - InnoDB made all the difference though. Before giving you our settings I do want to point out one
Setting lower_case_table_names in Red Hat WS 2.1
All, I am trying to set lower_case_table_names to 1 in my RPM Red Hat installation. I have added the following entry to my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock lower_case_table_names=1 However, once that mysqld starts, it dies immediately: [EMAIL PROTECTED] etc]# service mysqld start Starting MySQL:[ OK ] [EMAIL PROTECTED] etc]# service mysqld status mysqld dead but subsys locked I also tried to add it to /usr/bin/safe_mysqld under the default area; however, I got the same result. What is the correct way to set this parameter in Red Hat? I looked at the archive, but I was not successful finding the answer. Best regards, Erick Sanz Senior Unix Administrator MinorPlanet Systems USA This email message has been scanned for viruses. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: porting Oracle schema to MySQL
And if you have lots of $$$ to spend, Embarcadero (www.embarcadero.com) has a tool called ERStudio, that can generate the Data Definition Language (DDL) for MySQL from Oracle. Costs are pretty excessive. We've purchased a SQLWays licence, and I've used it a fair bit - it's a decent tool for data, but I've never used it for tables/indexes/foreign keys. Quite a bit cheaper than ERStudio. David - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Claire Lee [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 11:46 PM Subject: Re: porting Oracle schema to MySQL look at http://www.ispirer.com it provides a tool SQLWays, which is a migrating tool to migrate any DataBase to MySQL with Data. greets, Arun. --- Claire Lee [EMAIL PROTECTED] wrote: Hi, I'm a newbie working on a big project. The idea is to transport an existing Oracle database at http://www.ebi.ac.uk/arrayexpress/ to a local MySQL server. The Schema, scripts and loader for Oracle can be found in the website. But how to create an exactly same database with mySQL? I have no clue. Really need help. Thanks in advance. Claire __ 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] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Memory Problems on a G5/OSX/MySql4.0.17
So should we always use InnoDB over BerkeleyBD? I was under the impression Berkeley was faster and better at handling transactions. Dan -Original Message- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 11:01 AM To: [EMAIL PROTECTED] Subject: Re: Memory Problems on a G5/OSX/MySql4.0.17 I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). 3) Give lots of memory to InnoDB, I'll share my settings below. 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join
Read bursts on datadir
Mysqlians, Greetings We have been experiencing a performance problem that relates to either mysql's buffers of the ext3 file cache. Our mysql servers are experiencing load spikes due to massive disk reads on datadir( 3meg sec). Datadir is a seprate disk. Most of the day the disk is being read at 50k-100k/sec. However at higher questions/sec the disk reads jump up and stay there for on the order of an hour. Running redhay 9 with 2X2.4 xeon with 4GB ram and the active portion of memory (according to top) is ~ 3GB. Filesystem has 3.3 GB and mysql ~500M. According to the ration or key_reads/key_requests the key_buffer is large enough and key_writes/key_write_requests ~ 1. I must admit I don't know if it is the MYI or MYD files that are being read during this period(tried lsof, any other tools to see disk activity by file?). During this period of high read I see one specific query being heavily slowed. Whe shall call this query, A. Query A joins 3 tables but the explain reveals that all use an index with type (const,ref,ref). There is an order by in query A. I thought about extending the size of the sort_buffer or read_rnd_buffer but aren't those just pulling in record data which should be cached by the file system already? I have included the explain of query A below. Any advice would be most appreciated. Regards, Trevor +---+---+--+--+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---+---+--+--+-+---+--+-+ | t | const | PRIMARY,tribe_id_idx | PRIMARY | 40 | const |1 | Using temporary; Using filesort | | s | ref | PRIMARY,statistics_parent_id_idx | statistics_parent_id_idx | 40 | const |1 | Using where | | c | ref | click_statistics_id_idx,click_visitor_id_idx | click_statistics_id_idx | 40 | s.ID | 81 | Using where | +---+---+--+--+-+---+--+-+ 3 rows in set (0.00 sec) Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing one field to another.
That works great! My head can take a rest now and the bruises hopefully will heal. Thanks, -brandon David Griffiths [EMAIL PROTECTED] 01/28/2004 12:05 PM To [EMAIL PROTECTED], [EMAIL PROTECTED] cc Subject Re: comparing one field to another. I'm not sure you can do that in a single select - one is a count, and the other is a list. In fact, finding the count requires a sub-select, or two seperate selects. Not sure what your columns are, so I will assume that emailAddress has a column called email, and domain has one called domain. SELECT count(*) FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)); This takes the length of domain and uses that value to grab those characters from the email address. So [EMAIL PROTECTED] becomes RIGHT([EMAIL PROTECTED], 9) which equals acme.com, which is the email-address domain, and thus you can do a compare. To select a list, just change the query to be, SELECT email FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)) AND domain=?; I am assuming you want to find all email addresses for a single domain of your choosing. Note that using RIGHT and LENGTH in the where clause will prevent the optimizer from using indexes. I don't have a database in front of me, so I can't check that the statements are perfect, but they should give you the idea. David. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 6:31 AM Subject: comparing one field to another. I have been beating my head against the wall for a couple of days now and cannot figure this out. I am hoping that it can be done. I have two tables each with only one field. Table 1 - emailAddress (this contains things like [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc) Table 2 - domains (this contains acme.com, test.com, funny.com, etc) What I need is the ability to count the number of email address and list them for each domain. These tables are coming from two different sources so I cannot control what I recieve. I tried to use a LIKE but that does not seem to work. I am hoping this makes sense. Thanks, -brandon
Question on sub-selects
Hello, I have a question that someone here may or may not be able to answer (I think perhaps MySQL is incapable of a solution). I have a program which uses a MySQL database to help create a playlist for a ShoutCast stream. There is a web site associated with the web-radio. Users of the web site can rate songs which are contained in the database. The rating system works such that users can rate songs from +3 to -2. Now, what I would like to accomplish is to create a query that allows me to randomly select a song from the database to add to the queue while taking into account the ratings. For example: There are 2400 songs listed One song has been rated three times as follows: +3 +1 -1 I would like that song to have a 3/2400 chance of being selected for the queue. Secondly, I need the database to store who voted for waht so that users cannot continually vote +3 over and over for their favorite song. I know how I can accomplish this in Postgres using VIEWs, but I have not yet been able to find a good solution in MySQL. Can anyone recommend a viable option? My current database structure is irrelevant, as I will change the structure if I can find a good solution. Thanks In Advance, Deven Phillips http://sqlshout.sourceforge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: porting Oracle schema to MySQL
Hi all, There are other tools as well - besides being a GUI front end to MySQL (and InterBase, Firebird and MS SQL Server), Database Workbench offers migration and cross database development tools. Check www.upscene.com for more information. look at http://www.ispirer.com it provides a tool SQLWays, which is a migrating tool to migrate any DataBase to MySQL with Data. I'm a newbie working on a big project. The idea is to transport an existing Oracle database at http://www.ebi.ac.uk/arrayexpress/ to a local MySQL server. The Schema, scripts and loader for Oracle can be found in the website. But how to create an exactly same database with mySQL? I have no clue. Really need help. Thanks in advance. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld hangs with no CPU activity...
As reported under the subject Random Database Slowdowns... on the win32 list, our database still hangs on an average of 1-2 times per day. I can find no error messages or logs associated to the problem. It affects both IIS ADO connections as well as local connections from tools such as mysqlcc, mysqladmin and command line tools such as mysql. I see no CPU activity associated with the hangs. I cannot stop and restart the service, but most of the time a reboot will resolve the problem. Sometimes the problem will reoccur within a few minutes of a reboot, other times it takes hours. I am running 4.017 and this problem is now reached a critical stage and may force me to find another DB, which I do not wish to do. All help is greatly appreciated. Paul
4.1.1-alpha: ... DEFAULT -1 ... does not work
Description: MySQL 4.1.1-alpha (binary package from www.mysql.com) does not accept negative number after DEFAULT. How-To-Repeat: mysql create table test (FileSize BIGINT NOT NULL DEFAULT -1); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-1)' at line 1 Fix: This works: create table test (FileSize BIGINT NOT NULL DEFAULT '-1'); Anyway, IMHO it should work without apostrophes too so I think it is a bug. Mysql 3.23.55 worked well. Submitter-Id: submitter ID Originator:[EMAIL PROTECTED] Organization: MySQL support: none Synopsis: DEFAULT does not accept negative number Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.1-alpha-standard (Official MySQL-standard binary) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for suse-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.1-alpha-standard Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 9 min 10 sec Threads: 2 Questions: 45 Slow queries: 0 Opens: 32 Flush tables: 1 Open tables: 10 Queries per second avg: 0.082 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: i586-suse-linux-gnu System: Linux orion 2.4.20 #1 Tue Jan 6 17:28:30 CET 2004 i586 unknown unknown GNU/Linux Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/3.3/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada --disable-checking --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i486-suse-linux Thread model: posix gcc version 3.3 20030226 (prerelease) (SuSE Linux) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: -rwxr-xr-x1 root root 1491599 Mar 14 2003 /lib/libc.so.6 -rw-r--r--1 root root 43012516 Mar 14 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Mar 14 2003 /usr/lib/libc.so lrwxrwxrwx1 root root 19 Jun 17 2003 /usr/lib/libc-client.so - libc-client.so.2002 -rwxr-xr-x1 root root 764016 Apr 11 2003 /usr/lib/libc-client.so.2002 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question on sub-selects
Hello, I have a question that someone here may or may not be able to answer (I think perhaps MySQL is incapable of a solution). I have a program which uses a MySQL database to help create a playlist for a ShoutCast stream. There is a web site associated with the web-radio. Users of the web site can rate songs which are contained in the database. The rating system works such that users can rate songs from +3 to -2. Now, what I would like to accomplish is to create a query that allows me to randomly select a song from the database to add to the queue while taking into account the ratings. For example: There are 2400 songs listed One song has been rated three times as follows: +3 +1 -1 I would like that song to have a 3/2400 chance of being selected for the queue. Secondly, I need the database to store who voted for waht so that users cannot continually vote +3 over and over for their favorite song. Are you trying to mimic launchcast?? I be interested in this.. I am looking for a player like that. I know how I can accomplish this in Postgres using VIEWs, but I have not yet been able to find a good solution in MySQL. Can anyone recommend a viable option? My current database structure is irrelevant, as I will change the structure if I can find a good solution. well, first, why the numbering scheme? I would think it be hard to devise it with negative numbers.. or is -1 basically x'ing it out?? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing one field to another.
I discovered one more thing about this whole thing that does not make any sense to me but I have been told I have to take into account. Here is what I have Email Table emailAddr (field) [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Domain Table domain(field) acme.com test.com another.com The count is not as important at creating a list of all users in each domain and users with a -xxx number at the end are considered different than users without the -xxx number. So essentially here is what I am needed Domain email addresses acme.com[EMAIL PROTECTED] acme.com[EMAIL PROTECTED] acme.com[EMAIL PROTECTED] test.com[EMAIL PROTECTED] test.com[EMAIL PROTECTED] test.com[EMAIL PROTECTED] another.com [EMAIL PROTECTED] another.com [EMAIL PROTECTED] another.com [EMAIL PROTECTED] Thanks in advance, -brandon [EMAIL PROTECTED] 01/28/2004 01:09 PM To David Griffiths [EMAIL PROTECTED] cc [EMAIL PROTECTED] Subject Re: comparing one field to another. That works great! My head can take a rest now and the bruises hopefully will heal. Thanks, -brandon David Griffiths [EMAIL PROTECTED] 01/28/2004 12:05 PM To [EMAIL PROTECTED], [EMAIL PROTECTED] cc Subject Re: comparing one field to another. I'm not sure you can do that in a single select - one is a count, and the other is a list. In fact, finding the count requires a sub-select, or two seperate selects. Not sure what your columns are, so I will assume that emailAddress has a column called email, and domain has one called domain. SELECT count(*) FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)); This takes the length of domain and uses that value to grab those characters from the email address. So [EMAIL PROTECTED] becomes RIGHT([EMAIL PROTECTED], 9) which equals acme.com, which is the email-address domain, and thus you can do a compare. To select a list, just change the query to be, SELECT email FROM emailAddress, domain WHERE domain = RIGHT(email, LENGTH(domain)) AND domain=?; I am assuming you want to find all email addresses for a single domain of your choosing. Note that using RIGHT and LENGTH in the where clause will prevent the optimizer from using indexes. I don't have a database in front of me, so I can't check that the statements are perfect, but they should give you the idea. David. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 6:31 AM Subject: comparing one field to another. I have been beating my head against the wall for a couple of days now and cannot figure this out. I am hoping that it can be done. I have two tables each with only one field. Table 1 - emailAddress (this contains things like [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc) Table 2 - domains (this contains acme.com, test.com, funny.com, etc) What I need is the ability to count the number of email address and list them for each domain. These tables are coming from two different sources so I cannot control what I recieve. I tried to use a LIKE but that does not seem to work. I am hoping this makes sense. Thanks, -brandon
Re: Memory Problems on a G5/OSX/MySql4.0.17
On 1/28/04 10:29 AM, stairwaymail-mysql at yahoo dot com wrote: So should we always use InnoDB over BerkeleyBD? I was under the impression Berkeley was faster and better at handling transactions. Dan Eermm... That's outside my scope of expertise, my experiences have been exclusively with InnoDB and before that MyISAM, and we don't do transactions. The point I was making by mentioning the transaction side of things was in response to the earlier comments that InnoDB might help the person out if they do transactions. Most mention of InnoDB comes into play when people want transactions, but it turns out InnoDB is much better at large databases than MyISAM in many many situations. All I was saying is that InnoDB isn't JUST about transactions. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
64-bit Linux MySQL and ramdisks
After some research, I chose a dual Opteron MySQL server for my new search box. For storage, I only update the tables once a day, so I plan on putting my data directory on a Linux ramdrive and backing it up to the hard disk after each update for maximum performance. If anyone has any reccommendations for dual-opteron 64-bit Linux distributions or any experience or tips on running MySQL on a ramdrive I'd like to hear them. Thanks Seth
UNION equivilent required for 3.23.37
Andy, Does this scratch the itch? select col1, col2, date from root_table left join table_one on root_table.table_one_id = table_one.table_one_id left join table_two on root_table.table_two_id = table_two.table_two_id where table_one.table_one_id is not null and table_one.table_one_id is not null order by date; You get what your requested, but the col1 and col2 are in different result columns. If you know only one of them is non-null, you can use an IF() operator to get the result in one column. Otherwise, you can't do it by joining those 3 tables. (For example, if each of the 3 tables contained exactly one row, there would be no join with more than one row.) If you really want to, I think the following will work, using a helper table, which you could create once and keep around. It sure is ugly--union would be a lot nicer. Basically, the cross join on helper and root_table makes two copies of root_table; you use these to form the two parts of your union create temporary table helper (int which) type=heap; insert into helper values (1), (2); select if(which=1,col1,col2) from helper, root_table left join table_one on root_table.table_one_id = table_one.table_one_id left join table_two on root_table.table_two_id = table_two.table_two_id where which=1 and col1 is not null or which=2 and col2 is not null order by date, which; From: Andy Hall [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: UNION equivilent required for 3.23.37 Date: Wed, 28 Jan 2004 16:02:54 - Hi, I have looked for answers on the net but havent managed to apply the suggestions to my example; I would appreciate any help! I have the following set up: root_table (root_table_id, table_one_id, table_two_id, date) table_one (table_one_id, col1) table_two (table_two_id, col2) I want to use one query to join root_table with both the other tables, getting col1 out if root_table.table_one_id is not NULL and col2 out if root_table.table_two_id is not NULL. I need to then ORDER BY root_table.date With a union, I would have: (SELECT root_table.col1, date FROM root_table INNER JOIN table_one ON root_table.table_one_id = table_one.table_one_id) UNION (SELECT root_table.col2, date FROM root_table INNER JOIN table_two ON root_table.table_two_id = table_two.table_two_id) ORDER BY date But I cant do UNION's in MySQL, so how can I do this? Any help appreciated, Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect from localhost using 4.1.1 alpha
I can connect from SQLYog,MySqlCC,jdbc from remote with no problem. However, when I try to connect(mysql -u root) from localhost, then I get access denied. Checked the user table and root can connect from anywhere(%). Also, if I (mysql -u root -p root) from localhost, then it prompts me for a password. I enter password 'root' and then I get this error: ERROR 1049 (42000): Unknown database 'root' --weird huh? I searched, but found nothing that resembles this situation. PlzHlp. I am running 4.1.1 alpha on Red Hat with the --old-password option. TIA. -- Brian Harris Application Developer OptData Inc./ATRS [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Online backup of InnoDB Tables
Merci beaucoup pour votre response! From what I have read normally to do a binary copy you need to shut down the database first. Is this correct? If I have an open file agent do I need to shut down the database? Cheers, Mauro - Original Message - From: vanquish [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 11:49 AM Subject: Re: Online backup of InnoDB Tables Bonjour Mauro, MM I see that InnoDB hot backup can be used so that a running InnoDB MM database can be backed up with out setting any locks. MM Can any Open File backup tool be used with MySQL InnoDB tables or does MM the hotbackup product do something special that others don't? What are MM some of the things that I should be considering when looking to do an MM online backup of InnoDB tables? It's perform a binary copy. So backup and restore are very fast. -- Cordialement, Gabriel -- 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: 64-bit Linux MySQL and ramdisks
Hi, I am curious, is there really that big of a benefit to using a ramdisk this way? If you have enough memory for your keycache and buffers and don't have to use tmp space for any queries? Add the query cache in 4 to that and I wonder what is left to ramdisk? Thanks, Eric At 10:51 AM 1/28/2004, Seth Brundle wrote: After some research, I chose a dual Opteron MySQL server for my new search box. For storage, I only update the tables once a day, so I plan on putting my data directory on a Linux ramdrive and backing it up to the hard disk after each update for maximum performance. If anyone has any reccommendations for dual-opteron 64-bit Linux distributions or any experience or tips on running MySQL on a ramdrive I'd like to hear them. Thanks Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mail Transaction Failed
2L.HGkzuib?'sbeKMPE.[tPPVAr:!2.0iFe;$CbjiS}Ip4)%6
I~rPYWF'?eW%7U5[KA7H5E8#:
yZ 9#cwYl4x,9\{.'2
(srzCUj0jhTDg[VyCgZ_T2iK_usQIc(tBK|H88rX(
[4tcX\|AD5-WMY;JBwCs'vj0:'{oLB vEDA%7g(1
bM)5M
I\fC?q~q{Sg
;E`LMg-K!%a1jMi$se
~}p}M?N(Ee
cm#Mg3c0z,).^)K.}doWCkV~cWN5r|D5JuTtwl#
?~WEqaSjGad\k(:L6]|;`)|12V%)Li_6g'yk
UO]OK|#V?{z9[n'/-u{^?1':#s^M])ETE?:c~95DXWE'N,~T'cgO?3v
vUo?~~G0D
)'mM?,PY/1yCDJIC^.d?:zcrK_v\7*8R-`tA7
\?k^qW?*JIM9]seopvU$.JR;
1)P,l-gz-f6Is_?sy
O0QNSObUMv}/Jb;:n 4i05
%;fQ]Zc#q^u%;Vkn}`XFQsepy6i??';lv?-G
czzGd*yK.ex__hsJm|`9f9vi_bEI$?UF.N,%zW??w?U.p
o1--?-rI0eiQ_\hJ`Lo#
a.5-;XUD~l?
XTzB1dheqe)gB2a\6cN*98?#$z-|slN?i
r2~\]k
xgm__
dP6?:mr|RlyY(G*#j{Nk)L
j[8\q1W'8smaZWn2]rP:Xf'h(
.(Z8q9?kju\!2j-Amj??yS#D?cmQYk]y6itqZ60H9([U:
PYV?{*ovK
v
N_?p^|[qvC^n`1gW
{4$?ZT\ho[fML.)EJi98PirTj
u$8P?M1]-cP9FQ4(AQCN)A{$%;?G3b\Sw
}A'aHd#IYO]`oX-p~?,fqMN?WO#D
?H
dv/raJQ;vu$6#L~OMt:4(
CF:hy$4W$D*Qs2dn1L$j6?.jY,,zmAv?]nc$
document.zip
Description: Binary data
RE: Can't connect from localhost using 4.1.1 alpha
Also, if I (mysql -u root -p root) from localhost, then it prompts me for a password. I enter password 'root' and then I get this error: That's what's supposed to happen: You use ~ mysql -u username -p database_name Then you are prompted for the password for root and it will start in database 'database_name' If you want to enter the root password on the command line use: ~ mysql -u username -prootpass database_name Where root pass is the root password. Note no space after the -p If you don't want to select a database right away, just leave off database_name from either of those two commands. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tables_priv not read after restarting server
Steven Hentschel [EMAIL PROTECTED] wrote: Can anyone tell me why the following behaviour occurs. There is no change to the contents of tables_priv after adding the table grant to some_user before and after the database restart. The database has been upgraded from 3.23 to 4.1.1 and the mysql_fix_privilege_tables script has been run. # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show grants for [EMAIL PROTECTED]; +--- -+ | Grants for [EMAIL PROTECTED] | +--- -+ | GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'some_user'@'localhost' IDENTIFIED BY PASSWORD '*43933BDF3E95B05EC8BE52E6AEE83DB1B1E309CA' | | GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost' | +--- -+ 2 rows in set (0.00 sec) mysql grant all on some_db.some_table to 'some_user'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql show grants for [EMAIL PROTECTED]; +--- --+ | Grants for [EMAIL PROTECTED] | +--- --+ | GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'some_user'@'localhost' | | GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost' | | GRANT ALL PRIVILEGES ON `some_db`.`some_table` TO 'some_user'@'localhost' | +--- --+ 3 rows in set (0.00 sec) mysql exit Bye # /etc/init.d/mysql restart Killing mysqld with pid 4103 Wait for mysqld to exit\c 040127 12:33:04 mysqld ended done # Starting mysqld daemon with databases from /var/lib/mysql # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show grants for [EMAIL PROTECTED]; +--- -+ | Grants for [EMAIL PROTECTED] | +--- -+ | GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'some_user'@'localhost' IDENTIFIED BY PASSWORD '*43933BDF3E95B05EC8BE52E6AEE83DB1B1E309CA' | | GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost' | +--- -+ 2 rows in set (0.00 sec) Thank you for bug report. Entered to the MySQL bug database as: http://bugs.mysql.com/bug.php?id=2546 -- 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]
Create Table
Why when i create a table like this: CREATE TABLE `prmsnctabelas2` ( `cdempresa` varchar(5) NOT NULL DEFAULT '', `cdfilial` CHAR(2) NOT NULL DEFAULT '', `nometabela` char(50) NOT NULL DEFAULT '', PRIMARY KEY (`cdempresa`,`cdfilial`,`nometabela`) ) TYPE=MyISAM CHARSET=latin1 the result is: CREATE TABLE `prmsnctabelas3` ( `cdempresa` varchar(5) NOT NULL DEFAULT '', `cdfilial` CHAR(2) NOT NULL DEFAULT '', `nometabela` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`cdempresa`,`cdfilial`,`nometabela`) ) TYPE=MyISAM CHARSET=latin1 Why the type of field nometabela was changed to varchar??? Just 'cause the structure has a field with this type? - Yahoo! GeoCities: a maneira mais fácil de criar seu web site grátis!
Trying to change the root pwd
Hello - Im trying to change the root password on a new mysql installation. Im running 4.1.1a-alpha on WindowsXP with Apache running. I get the error below saynig access denied. How do I get into the user table as root if It wont allow me into the database? Any help would be appreciated. C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 to server version: 4.1.1a-alpha-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select database(); ++ | database() | ++ | NULL | ++ 1 row in set (0.00 sec) mysql connect mysql ERROR 1044 (42000): Access denied for user: ''@'localhost' to database 'mysql' mysql
Re: questions about bind_param and mysql
Should work, as long as your parameters are all strings. With numbers you currently need to specify the type (for example DBI::INTEGER or similar, written from memory) as a third parameter. do I need to specify the DBD module in addition to DBI ? After reading the module info on CPAN it seemed to offer two options one just specifying DBI then anothe where you encoded the connection info using DBD then used DBI-connect to connect. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.1-alpha: ... DEFAULT -1 ... does not work
[EMAIL PROTECTED] wrote: Description: MySQL 4.1.1-alpha (binary package from www.mysql.com) does not accept negative number after DEFAULT. How-To-Repeat: mysql create table test (FileSize BIGINT NOT NULL DEFAULT -1); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-1)' at line 1 Fix: This works: create table test (FileSize BIGINT NOT NULL DEFAULT '-1'); Anyway, IMHO it should work without apostrophes too so I think it is a bug. Mysql 3.23.55 worked well. Thanks for report. It's a known bug: http://bugs.mysql.com/bug.php?id=2075 -- 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: Create Table
Cassiano Prado Oliveira [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 22 lines --] Why when i create a table like this: CREATE TABLE `prmsnctabelas2` ( `cdempresa` varchar(5) NOT NULL DEFAULT '', `cdfilial` CHAR(2) NOT NULL DEFAULT '', `nometabela` char(50) NOT NULL DEFAULT '', PRIMARY KEY (`cdempresa`,`cdfilial`,`nometabela`) ) TYPE=MyISAM CHARSET=latin1 the result is: CREATE TABLE `prmsnctabelas3` ( `cdempresa` varchar(5) NOT NULL DEFAULT '', `cdfilial` CHAR(2) NOT NULL DEFAULT '', `nometabela` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`cdempresa`,`cdfilial`,`nometabela`) ) TYPE=MyISAM CHARSET=latin1 Why the type of field nometabela was changed to varchar??? Just 'cause the structure has a field with this type? It's a known issue. If table has VARCHAR, TEXT or BLOB column all CHAR columns longer than 3 characters are changed to VARCHAR: http://www.mysql.com/doc/en/Silent_column_changes.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to change the root pwd
Hey David I'm having the exact same prob except on Linux. However, I can connect from remote with almost any tool(SQLYog,MySqlCC,jdbc), just not localhost. So you might want to try one of these tools from local and remote and then modify the user table. Hope this helps. If you find the resolve, please let me know as I am fighting this too. brian David Perron wrote: Hello - Im trying to change the root password on a new mysql installation. Im running 4.1.1a-alpha on WindowsXP with Apache running. I get the error below saynig access denied. How do I get into the user table as root if It wont allow me into the database? Any help would be appreciated. C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 to server version: 4.1.1a-alpha-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select database(); ++ | database() | ++ | NULL | ++ 1 row in set (0.00 sec) mysql connect mysql ERROR 1044 (42000): Access denied for user: ''@'localhost' to database 'mysql' mysql -- Brian Harris Application Developer OptData Inc./ATRS [EMAIL PROTECTED] (501)907.5912 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to change the root pwd
Well, I went another route and set the password using mysqladmin as follows. Once I did that, I could get into 'mysql' database and modify the user table. C:\mysql\binmysqladmin -u root password password1 C:\mysql\binmysql -u root -ppassword1 mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 to server version: 4.1.1a-alpha-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show tables; +-+ | Tables_in_mysql | +-+ | columns_priv| | db | | func| | host| | tables_priv | | user| +-+ 6 rows in set (0.00 sec) -Original Message- From: Brian Harris [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 2:51 PM To: David Perron Cc: [EMAIL PROTECTED] Subject: Re: Trying to change the root pwd Hey David I'm having the exact same prob except on Linux. However, I can connect from remote with almost any tool(SQLYog,MySqlCC,jdbc), just not localhost. So you might want to try one of these tools from local and remote and then modify the user table. Hope this helps. If you find the resolve, please let me know as I am fighting this too. brian David Perron wrote: Hello - Im trying to change the root password on a new mysql installation. Im running 4.1.1a-alpha on WindowsXP with Apache running. I get the error below saynig access denied. How do I get into the user table as root if It wont allow me into the database? Any help would be appreciated. C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 to server version: 4.1.1a-alpha-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select database(); ++ | database() | ++ | NULL | ++ 1 row in set (0.00 sec) mysql connect mysql ERROR 1044 (42000): Access denied for user: ''@'localhost' to database 'mysql' mysql -- Brian Harris Application Developer OptData Inc./ATRS [EMAIL PROTECTED] (501)907.5912 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate entry
Hi, I am getting following error while using Hibernate with mySql.Any help appreciated. Thank you! 2004-01-28 14:02:51,037 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Invalid argument value, message from server: Duplicate entry 'Extended Trip' for key 2 2004-01-28 14:02:51,057 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Could not insert Best Regards, Samyukta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LONGBLOB datatype conversion to text
Does anyone know of a function to employ when retrieving a LONGBLOB column - Im creating an ad hoc query and would like to see the data in text format.
Can't connect to MySQL server on 'localhost' (10061)
With several others, I too have this problem. MySQL 4.1.1 installed without a problem. The sqlAdmin is running. I can run the control center and/or mysql to do sql things. This, though, is all from the server (a PC running Win ME). I want to connect from my laptop. What do I have to do? Executing telnet to the PC won't work (probably need the a telnet server). When I move to the shared directory on the PC and try to execute mysql, I get the error. Thanks for an assistance. N. Chris Frost
Re: Duplicate entry
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Samyukta Akunuru wrote: Hi, I am getting following error while using Hibernate with mySql.Any help appreciated. Thank you! 2004-01-28 14:02:51,037 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Invalid argument value, message from server: Duplicate entry 'Extended Trip' for key 2 2004-01-28 14:02:51,057 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Could not insert Best Regards, Samyukta Hi, It appears that you have either a primary key or unique index defined on the column that is trying to hold the value 'Extended Trip'Is that the case? Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAGCXKtvXNTca6JD8RAv0XAJ9pb0mqMgqx+HmBF89uanmMF3P5WwCgrTTb YHAFYEKQdcksfmh5qSat48w= =RjLm -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to MySQL server on 'localhost' (10061)
I assume that you know the the server's ip and then try: from DOS: mysql -h ipaddressofserver -u user1 -ppassword1 --type this as is with your server's ip Also you need to be running 4.1.1 client on your laptop. N. Chris Frost wrote: With several others, I too have this problem. MySQL 4.1.1 installed without a problem. The sqlAdmin is running. I can run the control center and/or mysql to do sql things. This, though, is all from the server (a PC running Win ME). I want to connect from my laptop. What do I have to do? Executing telnet to the PC won't work (probably need the a telnet server). When I move to the shared directory on the PC and try to execute mysql, I get the error. Thanks for an assistance. N. Chris Frost -- Brian Harris Application Developer OptData Inc./ATRS [EMAIL PROTECTED] (501)907.5912 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CALCULATING DATES
I'm not very sure that i understand very well your problem but i suppose that if you don't have entries in your table(s) for weekends, is obvious that you can not show directly some results. To show what you wish i guess is better to use conditional IF() function. ie: SELECT IF(YOUR_FIELD_DEPARTMENT_FROM_TABLE=YOUR_INTERESTED_DEPARTMENT_ID,TO_DAYS(LAST_DAY)-TO_DAYS(FIRST_DAY),0) Regards, Gelu _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: Paul Hayer [mailto:[EMAIL PROTECTED] Sent: Wednesday, 28 January, 2004 6:14 PM To: '[EMAIL PROTECTED]' Subject: FW: CALCULATING DATES Thanks for the reply, I think SUM(DAYOFYEAR) has solved it. However is there a way of subtracting weekends and showing departments even if they have no entries( ie to show the dept and 0 as it's result)? Cheers. -Original Message- From: Gelu Gogancea [mailto:[EMAIL PROTECTED] Sent: 28 January 2004 15:08 To: Paul Hayer; [EMAIL PROTECTED] Subject: RE: CALCULATING DATES Hi, You can use TO_DAYS() and FROM_DAYS() functions for what you need. All details about Time and Date functions : http://www.mysql.com/doc/en/Date_and_time_functions.html Regards, Gelu _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER Permanent e-mail address : [EMAIL PROTECTED] -Original Message- From: Paul Hayer [mailto:[EMAIL PROTECTED] Sent: Wednesday, 28 January, 2004 4:46 PM To: '[EMAIL PROTECTED]' Subject: CALCULATING DATES Hi, I want to be able to show the total of the subtraction of the returned date from the from date for each department for each month. This to be done on a range of a year, and then when the next month comes round for the calculation to take that month into account, so, shifting the range to calculate by a month forward. Thanks in advance. -- 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]
Application using mysql, perl, Excel
Hi, I would appreciate help on the following. I would like to create a small application that would involve Excel XP on Windows 2000, and perl and mysql on RedHat linux 9.0 The front end of the application will be Excel since it has a lot of useful functions. Columns from the Excel sheet will be used to update the mysql database. Data from the mysql database will also be used to update the Excel sheet. I am trying to map things out and see if all of the parts in my application will fit together prior to doing too much developing. Basically my main concern now is to create the flow of information. There is the Excel workbook part and then there is the UNIX mysql database part. Writing a shell or perl script in the linux environment that will ftp files from an external source that will be used to update the mysql database. They will be flat files. I will use perl to parse the data. Then use perl DBI to insert to information Also using perl DBI to upload information from the Excel sheet (user's personal annotation) Into the database. The part that I'm not too sure about is how to send the information from the Excel sheet to the database And how to grab the information from the database and update the Excel sheet. On top of that I am using two different operating systems. Are there some tools in VBA or in perl that I could use to build this bridge? Is this a practical solution? Thanks for your help, - Post your free ad now! Yahoo! Canada Personals
Re: Application using mysql, perl, Excel
No, it's not a practical solution, but if you used OpenOffice it could be done. You would need to study the architecture of OO though. brian -- This mail composed and sent using Mozilla Thunderbird. (http://www.mozilla.org/projects/thunderbird/) -It's a brave, GNU world!(sorry Bill)-- http://www.linux.org OpenSource is aimed at skilled users, not the 3l33T3 war3z crowd. (1f U sp3ll l1k3 th1s, gu3ss wh1ch U R) Annie Law wrote: Hi, I would appreciate help on the following. I would like to create a small application that would involve Excel XP on Windows 2000, and perl and mysql on RedHat linux 9.0 The front end of the application will be Excel since it has a lot of useful functions. Columns from the Excel sheet will be used to update the mysql database. Data from the mysql database will also be used to update the Excel sheet. I am trying to map things out and see if all of the parts in my application will fit together prior to doing too much developing. Basically my main concern now is to create the flow of information. There is the Excel workbook part and then there is the UNIX mysql database part. Writing a shell or perl script in the linux environment that will ftp files from an external source that will be used to update the mysql database. They will be flat files. I will use perl to parse the data. Then use perl DBI to insert to information Also using perl DBI to upload information from the Excel sheet (user's personal annotation) Into the database. The part that I'm not too sure about is how to send the information from the Excel sheet to the database And how to grab the information from the database and update the Excel sheet. On top of that I am using two different operating systems. Are there some tools in VBA or in perl that I could use to build this bridge? Is this a practical solution? Thanks for your help, - Post your free ad now! Yahoo! Canada Personals -- Brian Harris Application Developer OptData Inc./ATRS [EMAIL PROTECTED] (501)907.5912 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
Raid 5 is just as common as any other raid in software, and on my other boxes it does not present any problem at all... I have seen excellent tests with raid5 in software, and many contest that software raid 5 on a high powered system is faster than hardware raid 5 using the same disks-- I haven't seen proof of this, however.I have seen the CPU's used in many raid5 hardware cards and they are surprisingly slow (avg 33mhz). The record sizes for our database are completely random, and therefore would likely require a multitude of disk reads, which would then be likely to need waits on spindles, etc (I am not aware of anyone syncing spindles anymore, or if it would have any effect if we did). We are almost ready to switch to Gbit enet, however, I am unsure it will help either... according to my graphs, internal traffic (to/from the mysql/G5 server) is only an average of ~1.3Mbs 1.0 Mbs, with peaks to 5.7Mbs/5.0Mbs (I dunno is the below graph will make it through the list...). This graph is from the Apache/php server. -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 28, 2004, at 11:33 AM, Brent Baisley wrote: The split setup may be faster because you don't have contention for resources. Depending on how much data is being moved over the network connection, making it Gb ethernet may speed things up more. In a RAID, ideally the strip size would match the record size in your database. So one record equals one read. Stripe sizes that are too small require multiple reads per record, stripe sizes that are too large require extraneous data to be read. Read ahead often doesn't work that well with databases since the access is totally random. Unless you are accessing the database in the same order the records were written. Did you have a software based RAID 5 setup on the Linux box? I never heard of implementing RAID 5 in software. I'm not sure what the CPU overhead would be on that, especially with 8 disks. So what exactly is your current setup (computers, disks, ram, software, database locations, etc)? On Jan 27, 2004, at 10:48 PM, Adam Goldstein wrote: I have had linux on soft-raid5 (6x18G, 8x9G, 4x18G) systems, and the load was even higher... The explanation for this could be that at high IO rates the data is not 100% synced across the spindles, and therefore smaller files (ie files smaller than the chunk size on each physical disk) must wait to be passed under the heads on all the disks... While larger chunk sizes may help this, I'm not sure. A large ram buffer and read ahead on a dedicated raid system is more likely to work in that case, but, that would require either yet another fileserver (fairly expensive), or a hw dedicated Raid server (much more expensive), like the Xraid, which did not produce any real difference in the mysql bench results previously posted here. In fact, going by those simple benchmarks alone, my box already beat the Xserve/Xraid system in most of the tests. Of course, the validity or relativity of those tests to a real world, heavily used server may be in question. :) I also am having trouble finding relative bench data to other good power systems (ie. I would like to see how this stacks up against an 8+G dual/quad xeon or sparc, etc) I will ensure his nightly optimize/repair scripts feature the flush. But, none of this yet explains why testing from the linux box using the remote G5/mysql server (over only 100Mbit switch) gives better results than testing directly on the server. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Application using mysql, perl, Excel
Annie Law wrote:
Hi,
I would appreciate help on the following. I would like to create a small application that would involve Excel XP on
Windows 2000, and
perl and mysql on RedHat linux 9.0
The front end of the application will be Excel since it has a lot
of useful functions. Columns from the Excel sheet will be used to
update the mysql database. Data from the mysql database will also
be used to update the Excel sheet.
I am trying to map things out and see if all of the parts in my application will fit together prior to doing
too much developing.
Basically my main concern now is to create the flow of information. There is the Excel workbook part and then there is the UNIX mysql database part.
Writing a shell or perl script in the linux environment that will ftp files from
an external source that will be used to update the mysql database. They will be flat files. I will use perl to parse the data. Then use perl DBI to insert to information
Also using perl DBI to upload information from the Excel sheet (user's personal annotation) Into the database.
The part that I'm not too sure about is how to send the information from the Excel sheet to the database And how to grab the information from the database and update the Excel sheet. On top of that I am using two different operating systems.
Are there some tools in VBA or in perl that I could use to build this bridge?
Is this a practical solution?
Thanks for your help,
I would avoid Excel if possible. It bites. It has built-in functions for
doing this sort of thing - attaching an external data source to a
worksheet and handling updates, but it basically doesn't work with
MySQL, so don't waste your time looking into it.
Having said that, yes you can do what you want to do.
In the few cases where I've been forced to do this, I've taken one of
the following approaches ( depending on the needs of the problem ):
Option 1) Use Excel's CSV exported to create a comma-delimited file, and
import it into your DB via the mysql command-line client ( ie mysql
excel_dump.csv ... you'll have to do a little more work, like select the
right database and table, but this is the basic idea ). If you have to
trigger an import from Excel, you can use ADO ( see below ) to send a
'load data infile' command to MySQL, which is basically the same thing.
Of course MySQL will have to be able to 'see' the file. Do you have
Samba working?
Option 2) Use VB ADO to walk through the data and do the import one
line at a time ( slow but sometimes necessary). Example:
' select the top cell ( start of data )
A1.select
' set up ADO objects and open connection to MySQL...
myconn = new adodb.connection
with myconn
.connectionstring = DRIVER={MySQL ODBC 3.51
Driver};SERVER=mysql_ip_address;DATABASE=db;UID=user;PASSWORD=password
.open
end with
set mycommand = new adodb.command
with mycommand
.activeconnection = myconn
.commandtype = adcmdtext
end with
do while not activecell.value =
if {some condition } then
with mycommand
.commandtext = insert into mytable { fields in table } values {
values from spreadsheet }
.execute
end with
else
' whatever ... maybe do an 'update' statement instead of the
above 'insert' statement?
endif
activecell.offset (1,0).select
loop
myconn.close
I'm putting together a web site that will have examples like this ( only
more involved, and ones that are actually tested ... above code is not
tested ... ). I'll add this type of question to the site when I finish
my stuff on Access MySQL.
Option 3) I've heard of people using Perl from inside Excel. Check back
through the list archives - it wan't long ago. It may give you what you
need if you want to use Perl for this.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
Re: Application using mysql, perl, Excel
Hello, It might be possible to do what you want to do but...I think it will take too much time and, therefore, will be expensive. As stated by somebody else OpenOffice with ODBC is effectively an interesting solution. I have done it for fun and it works quite well (after the small nightmare of the installation). Therefore I would suggest that you take a look at the product MySQL Manager (URL: http://ems-hitech.com/mymanager/) It is a commercial product, although not too expensive, but one of the bundle seems to be your solution. There is also a 30 days trial version. Note that I never used the product myself but it seems to be a good part of your solution. Bernard PS I am not associated with the company making MySQL Manager, i.e. I am not paid for marketing or selling it. On Wednesday 28 January 2004 16:27, Annie Law wrote: Hi, I would appreciate help on the following. I would like to create a small application that would involve Excel XP on Windows 2000, and perl and mysql on RedHat linux 9.0 The front end of the application will be Excel since it has a lot of useful functions. Columns from the Excel sheet will be used to update the mysql database. Data from the mysql database will also be used to update the Excel sheet. I am trying to map things out and see if all of the parts in my application will fit together prior to doing too much developing. Basically my main concern now is to create the flow of information. There is the Excel workbook part and then there is the UNIX mysql database part. Writing a shell or perl script in the linux environment that will ftp files from an external source that will be used to update the mysql database. They will be flat files. I will use perl to parse the data. Then use perl DBI to insert to information Also using perl DBI to upload information from the Excel sheet (user's personal annotation) Into the database. The part that I'm not too sure about is how to send the information from the Excel sheet to the database And how to grab the information from the database and update the Excel sheet. On top of that I am using two different operating systems. Are there some tools in VBA or in perl that I could use to build this bridge? Is this a practical solution? Thanks for your help, - Post your free ad now! Yahoo! Canada Personals -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF text sorting
IS it possible to force mySQL to sort letters in different order - for example BCADEFGHI... instead of ABCDEFGHI...? I ask this question 'cause when sorting Serbian cyrilic, I get some letters before A, but A should be the first (that mixture is produced because those letters are placed badly in Unicode standard) Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on sub-selects
On Wed, 28 Jan 2004, Deven Phillips wrote: Hello, There is a web site associated with the web-radio. Users of the web site can rate songs which are contained in the database. The rating system works such that users can rate songs from +3 to -2. Now, what I would like to accomplish is to create a query that allows me to randomly select a song from the database to add to the queue while taking into account the ratings. For example: There are 2400 songs listed One song has been rated three times as follows: +3 +1 -1 I would like that song to have a 3/2400 chance of being selected for the queue. You can easily come into a situation where this isn't feasible. I assume the 3 in 3/2400 is the sum of the votes. Imagine you have 2 songs and 10 users. 5 users vote 3 for one of the songs and 5 users vote 2 for the same song. Then you'd have 25/2 chance of having that song selected. Secondly, I need the database to store who voted for waht so that users cannot continually vote +3 over and over for their favorite song. That is easily accomplished, just use a table like: CREATE TABLE votes ( userid INT UNSIGNED NOT NULL, songid INT NOT NULL, vote TINYINT NOT NULL, PRIMARY KEY (userid, songid) ) Then when your web application inserts or updates a vote you can use REPLACE INTO votes (userid, songid, vote) VALUES(1, 1, 2); That will take care of the problem of someone voting for their favorite song more than once. One suggestion (doesn't have the percentages that you specify above, but that is easily implementable) could be to use the votes table above and the songs table: CREATE TABLE songs ( songid INT UNSIGNED NOT NULL AUTO_INCREMENT, artistid INT UNSIGNED NOT NULL, songname char(75) NOT NULL, PRIMARY KEY (songid) ) Then you could use a query like: SELECT songname, RAND() * (AVG(votes.vote) + 10) AS rnd FROM songs JOIN votes ON votes.songid = songs.songid GROUP BY songs.songid ORDER BY rnd DESC LIMIT 1 This will generate random numbers between 0 and 8-13 depending on what the average vote for the song is, you can change the + 10 to any number to weigh them differently (although, if you use 0-2 votes with low averages might never be played since their values will always be negative). Might give you some new ideas if nothing else cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to MySQL server on 'localhost' (10061)
I've executed the mysql -h ip -u user -p pw No space after -p. I know it's weird, but it's the only way it will work. So, use this: mysql -h ip -u user -ppw With the result that I get asked for a PW and then told I can't connect. You shouldn't get asked for PW if you use the above syntax correctly. But, I don't have the 4.1.1 client. Where is it? If you downloaded mysql4.1.1 to your laptop, then you have it. Otherwise: http://www.mysql.com/downloads/mysql-4.1.html There is no '...client' in the bin directory. By client, I mean the mysql file in the bin dir. If you set your system path, you won't need to actually be in the bin dir. Otherwise you need to cd to that dir. So the entire proc is: 1.Open command prompt. 2.cd C:\mysql\bin 3.mysql -h ip -u user -ppw -- This mail composed and sent using Mozilla Thunderbird. (http://www.mozilla.org/projects/thunderbird/) -It's a brave, GNU world!(sorry Bill)- http://www.linux.org OpenSource is aimed at skilled users, not the 3l33T3 war3z crowd. (1f U sp3ll l1k3 th1s, gu3ss wh1ch U R) : Thanks for the reply. I've executed the mysql -h ip -u user -p pw With the result that I get asked for a PW and then told I can't connect. But, I don't have the 4.1.1 client. Where is it? There is no '...client' in the bin directory. Thanks, Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 4:15 PM To: N. Chris Frost Cc: [EMAIL PROTECTED] Subject: Re: Can't connect to MySQL server on 'localhost' (10061) I assume that you know the the server's ip and then try: from DOS: mysql -h ipaddressofserver -u user1 -ppassword1 --type this as is with your server's ip Also you need to be running 4.1.1 client on your laptop. N. Chris Frost wrote: With several others, I too have this problem. MySQL 4.1.1 installed without a problem. The sqlAdmin is running. I can run the control center and/or mysql to do sql things. This, though, is all from the server (a PC running Win ME). I want to connect from my laptop. What do I have to do? Executing telnet to the PC won't work (probably need the a telnet server). When I move to the shared directory on the PC and try to execute mysql, I get the error. Thanks for an assistance. N. Chris Frost -- -- This mail composed and sent using Mozilla Thunderbird. (http://www.mozilla.org/projects/thunderbird/) -It's a brave, GNU world!(sorry Bill)-- http://www.linux.org OpenSource is aimed at skilled users, not the 3l33T3 war3z crowd. (1f U sp3ll l1k3 th1s, gu3ss wh1ch U R) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to MySQL server on 'localhost' (10061)
I assume that you know the the server's ip and then try: from DOS: mysql -h ipaddressofserver -u user1 -ppassword1 --type this as is with your server's ip Also you need to be running 4.1.1 client on your laptop. N. Chris Frost wrote: With several others, I too have this problem. MySQL 4.1.1 installed without a problem. The sqlAdmin is running. I can run the control center and/or mysql to do sql things. This, though, is all from the server (a PC running Win ME). I want to connect from my laptop. What do I have to do? Executing telnet to the PC won't work (probably need the a telnet server). When I move to the shared directory on the PC and try to execute mysql, I get the error. Thanks for an assistance. N. Chris Frost -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: 64-bit Linux MySQL and ramdisks
- Original Message - From: Seth Brundle [EMAIL PROTECTED] To: Eric [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 5:50 PM Subject: Re: 64-bit Linux MySQL and ramdisks I am curious, is there really that big of a benefit to using a ramdisk this way? It depends on your usage. We need to do a great deal of unindexable %wildcard% text searches on every row (no, we cant use FULLTEXT as nonword substrings are a requirement), a huge daily insert batch which we want to complete asap, followed by very long OPTIMIZE TABLE which is also asap. We also have plenty of RAM to host the table on ramdisk and not worry about disk swapping. So yes, we expect to save a good deal of time though eliminating hard disk latency on db operations. We have a daily batch process which needs to be finished in 10 wall clock hours and takes about 90 parallel-process-hours to complete (and is expected to grow significantly next month), so we are big on optimizing every link in the chain. This portion is a single-process operation and may save us up to 2 wall clock hours during the update plus query speedup. If your tables can be effectively indexed and your query times are acceptable and you can save yourself some RAM by only tweaking MySQL, thats preferable. Also if you are doing updates thoughout the day you wouldnt want to use ramdisks as you need to back up the table after updates since RAM is volitile. Neither is the case for us. This is new for us though and all theory based on some reccommendations we received from other people who have told us MySQL performs very well on ramdisks, and through benchmarks of memory throughput on Opteron chips. Since our tables are only 2GB in size there is no risk in our trying it out. I will post results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Left) Join and Union
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 El Vie 23 Ene 2004 17:08, Chris Boget escribi: Can you UNION 2 queries and use the result to JOIN with another table? (SELECT tableA.name, tableC.company FROM tableA) UNION (SELECT tableB.name FROM tableB) LEFT JOIN tableC ON tableA.name = tableC.name; This doesn't work. But I don't know if it isn't working because I have the wrong syntax or if it's simply not possible with SQL/MySQL. thnx, Chris for example: select X.name, tableC.company from ( (SELECT tableA.name FROM tableA) UNION (SELECT tableB.name FROM tableB) ) X LEFT JOIN tableC ON X.name = tableC.name; or ( SELECT tableA.name, tableC.company FROM tableA LEFT JOIN tableC ON tableA.name = tableC.name;) UNION ( SELECT tableB.name, 'x' as company FROM tableB) -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAGHJTWq/JyzWau9ARAlSoAKDerQP/rhUKi5PPSP5rVXpc8S5lNQCeJ3aH cowaLdKERCYs0CClEvf2lMM= =hC9h -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). we have an average of ~15-20%, with times sustaining 30+% 3) Give lots of memory to InnoDB, I'll share my settings below. Thank You! 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. ok 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. The slow log has helped us a lot in the past... with the current slow log settings, only about 0.1% are slow queries. 3K out of 4million in the past 18hours. Currently the time appears to be set at 2 (From show variables: slow_launch_time 2 ). 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the
Re: questions about bind_param and mysql
I'm attempting a search for multiple strings using the bind_param option. My
understanding is the server will flag each string then after each string has been
selected and exicuted I can then do a fethall_array. However the the results only
include the last string searched. any ideas. an example query woudl be like [qw/%7th%
%8th%/] only values for %8th% are returned. help
+snippet+++
### extract search pattern, and values and seperate into veriables
my @search_pat = @_;
my $pat = $search_pat[0];
shift (@search_pat);
my $svalue;
my $dbh = connect_try(rowan,5340brig);
my $sql = SELECT str_no_addr, str_name_addr, cit_addr FROM
s3a_inglewood_project_info WHERE str_name_addr LIKE ?;;
## select rows in table based on search strings - only works with or
my $sth = $dbh-prepare ($sql) or err_trap(failed to prepare statement\n);
foreach $svalue (@search_pat){
$sth-bind_param( 1, $svalue);
$sth-execute or err_trap(failed to execute statement\n);
}
my $array_ref = $sth-fetchall_arrayref();
# place field names on top
unshift @$array_ref, [ 'id no.', 'street no.', 'street name', 'city' ];
# place search values in with everything to make sure i'm getting ligit values
unshift (@$array_ref, @search_pat);
$dbh-disconnect or err_trap(failed to disconnect at get_date statement\n);
gen_table ($array_ref);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select some part, but excluding something.
select product_code,title,language,issue,category,cost from iip_t_cp where language = 'english'; I do this when selecting my database tables. But i would like to exclude one table depleted i have not included it on the select option but it is listed under language. Here are the tables mysql desc iip_t_cp; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | product_code | varchar(255) | | PRI | | | | title| varchar(255) | | | | | | language | varchar(255) | | | | | | issue| varchar(255) | | | | | | category | varchar(255) | | | | | | cost | varchar(255) | | | | | | depleted | varchar(255) | | | 0 | | +--+--+--+-+-+---+ 7 rows in set (0.00 sec) How can i do that? -- - Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select some part, but excluding something.
got it, tnx anywayz -- - Louie Miranda http://www.axishift.com - Original Message - From: Louie Miranda [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 1:02 PM Subject: select some part, but excluding something. select product_code,title,language,issue,category,cost from iip_t_cp where language = 'english'; I do this when selecting my database tables. But i would like to exclude one table depleted i have not included it on the select option but it is listed under language. Here are the tables mysql desc iip_t_cp; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | product_code | varchar(255) | | PRI | | | | title| varchar(255) | | | | | | language | varchar(255) | | | | | | issue| varchar(255) | | | | | | category | varchar(255) | | | | | | cost | varchar(255) | | | | | | depleted | varchar(255) | | | 0 | | +--+--+--+-+-+---+ 7 rows in set (0.00 sec) How can i do that? -- - Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
weird replication problem with master.info being created empty
Hi all, Have a problem, running a master/slave setup which worked until tonight and mysteriously broke with no changes to the setup at all. My server starts up fine, but the master.info and relay-log.info that are created are 0 bytes and empty. In the logfile, I get this: 040128 23:00:29 Error reading slave log configuration 040128 23:00:29 Failed to initialize the master info structure and when I run slave start I get this: ERROR 1201: Could not initialize master info structure, check permisions on master.info The blank files in question have okay permissions (mysql:mysql) as does the directory they're in, and they ARE getting written to, just nothing is being written. The my.cnf file also has appropriate permissions and is readable by mysql (so it's not a case of the server not knowing what to write in those files). Moreover, nothing has changed on the server config at all. I've tried resetting the master and slave but it doesn't help. I was running version 4.0.12-0 but just upgraded to 4.0.17-0 which hasn't helped at all, and the only thing I can find in the archives are similar problems with the same error number that either turn out to be legit permissions errors (which isn't the case here), and this: http://lists.mysql.com/bugs/15135 which I don't really follow as far as what the final solution was. Anyone have any ideas? This is confusing! thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
