ODBC.NET
Hi, I finally after 2 weeks battle with asp.net - mysql - myodbc got a connection. my only problem now is that I got the connection because I downloaded microsofts ODBC.NET component and I'm using the Microsoft.Data.Odbc namespace. but I can't find any documentation for that namespace?!? can anyone help me? Peter Marino - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL DATABASE BACKUP
Yes and no If you are taking info from a live database you would have it in a steady state to copy it. This means locking the tables. But once you have a copy of the table all you would need to do is update the changes from the logs. Using replication for this I is the most simple way but would mean a second copy of MySQL but you would only need a low spec system.. I hope this helps... Simon -Original Message- From: Kathy Reyes [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 21:37 To: mysql Subject: MYSQL DATABASE BACKUP I am trying to backup a table in mysql without locking my tables it this posible - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: No support for multiple statements via JDBC?
Hi Allon, Only one query can be sent to server at a time. But your particular case can be handled with the single query Insert Into orders (Number) select Max(Number) FROM Orders; Anvar. At 04:45 PM 19/03/2002 -0800, you wrote: Hi All- We are using the mm driver for MySQL and multiple statements in one connection do not seem to be supported: I.e. SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1) The driver throws a syntax error. You can do either one of these statements on their own, and you can have a semicolon in the statement, but you cannot combine the statements. Are we missing something? How would you do a transaction or table lock without multi statement support? -Allon Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mystery files
Hi, I my mysql server database directory (that is the directory that holds the .err and .pid files) so strange files have apeared. They look line machine-name-bin.001 machine-name-bin.002 etc machine-name-bin.index Some of them are huge! They seem like binary files, but are full readable SQL commands that could be from my applications. What is going on can I delete them? Cheers Howard Miller - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mystery files
Have a look for binary log files in the manual these are binary log files, needed for replication setup eg. Lars mysql mailing list user wrote: Hi, I my mysql server database directory (that is the directory that holds the .err and .pid files) so strange files have apeared. They look line machine-name-bin.001 machine-name-bin.002 etc machine-name-bin.index Some of them are huge! They seem like binary files, but are full readable SQL commands that could be from my applications. What is going on can I delete them? Cheers Howard Miller - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow inserts and selects for fulltext indexes (mysql4)
Hi, Is there a way to speed up inserts on a table with three fulltext indexes? I'm using the multiple value insert format like: insert into blah (field1, field2) values ('val1', 'val2'), ('val2', 'val3'), etc.. Perhaps this is a bug in the current mysql4 bk snapshot, but inserts and selects on a table with three fulltext indexes are taking up to 23 seconds! On average it's still taking up to 2.5 seconds to insert 50 records into a table with 15000 records already (Which I think is kinda slow). But every now and then it'll really slow down. I just logged 9 seconds for a fulltext select on a table with 15000 records, and 23 seconds for an insert of 106 rows on the same table. The machine has 400 megs of RAM and is a PIII 750 with IDE drives. (as opposed to SCSI). Running MySQL version 4 (the latest bk snapshot). Here are the entries from the 'slow.log': ## SET timestamp=1016613725; select SQL_CACHE id,search_category,search_jobtype,joblink,title,d_date_posted,n_date_posted,jobsite,jobsite_home,jobsite_con /usr/local/mysql/libexec/mysqld, Version: 4.0.2-alpha-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument # Time: 020320 9:01:40 # User@Host: root[root] @ localhost [] # Query_time: 9 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 use workzoo; SET timestamp=1016614900; insert into search_cache (ctime, search_category,search_jobtype,joblink,title,d_date_posted,n_date_posted,jobsite,jobsite_home # Time: 020320 9:02:04 # User@Host: root[root] @ localhost [] # Query_time: 23 Lock_time: 0 Rows_sent: 106 Rows_examined: 13081 # My my.cnf is included below. Based on the config above, particularly the amount of RAM, is this the ideal config for my machine? (considering that I do large inserts, updates and select on all three fulltext indexes on the same table) btw. I'm getting great performance on multiple sequential updates (over 200 records takes under .1 seconds) by locking the table. [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking memlock set-variable= key_buffer=100M set-variable= max_allowed_packet=5M set-variable= table_cache=256 set-variable= record_buffer=1M set-variable= sort_buffer=20M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=20M set-variable= long_query_time=1 set-variable= ft_min_word_len=1 #Query cache configured for on demand only set-variable= query_cache_limit=2M set-variable= query_cache_size=2M set-variable= query_cache_startup_type=2 bind-address=10.1.1.1 log-bin server-id = 1 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname log = /var/log/mysql/general.log log-slow-queries = /var/log/mysql/slow.log Thanks, Mark. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mystery files
i believe you've the replication option turned on. see http://www.mysql.com/doc/R/e/Replication_Options.html for more details. mysql mailing list user wrote: Hi, I my mysql server database directory (that is the directory that holds the .err and .pid files) so strange files have apeared. They look line machine-name-bin.001 machine-name-bin.002 etc machine-name-bin.index Some of them are huge! They seem like binary files, but are full readable SQL commands that could be from my applications. What is going on can I delete them? Cheers Howard Miller - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Sammy Lau mailto: [EMAIL PROTECTED] - Tell me what you want and I'll tell you how you can live without it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: No support for multiple statements via JDBC?
sql,Mysql Hi Allon, Sorry. It wont work. I didn't notice that you were inserting to the same table as the select. Anvar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: still no umlauts in mysql --experts help needed
Am Mittwoch, 20. März 2002 11:24 schrieben Sie: I just installed mysql fresh out of the box on a fresh SuSE linux 7.1. mysql does not let me enter german umlauts, though. I tried to start it with mysql --default-character-set=latin_de, but it complained about the character sets not being installed. Then I installed the mysql server, which in turn installs the character sets. latin_de is not included, though. I tried it with mysql --default-character-set=german1 instead. No luck. Was meinst Du mit does not let me enter? Blockieren die entsprechenden Tasten? Erscheint nichts auf dem Bildschirm? Gibt es irgendeine Fehlermeldung? Liefert MySQL bei einem SELECT das Falsche zurück? keystrokes with umlauts do not have any echo on the screen. batch input (text files with sql statements with umlauts redirected to mysql) do not insert umlauts into the database (but the rest of the text, so that the words are mis-spelt). there is no error message. select statements contain any umlauts in the database, though, and I can enter umlauts into the database using other clients than mysql. s.m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
default value
Hello, s possible to create table with default value returned by built-in function ?? like: create table x (a int not null default UNIX_TIMESTAMP()); When inserting new column in table field a will contains timestamp? sql,query Regards, Michal Dvoracek [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: JDBC and applet
I just took a look at all of that, especially the jar file. It contains another jar file! (with the mm.mysql driver classes) maybe it appears that some runtimes don't get this working !?! it worked fine on my system : browsers netscape 6 (winnt4 and linux) and opera 5 (winnt4). Andreas * sql,select * -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 13, 2002 6:29 PM To: [EMAIL PROTECTED] Subject: JDBC and applet Hello friends, I am having a problem using the MySQL JDBC driver from an applet. In my local machine's browser and on very few remote machine's browsers the applet works correctly, however in most remote machine's browsers it cannot find a suitable driver. What is wrong with my Java code? Or my applet tag? The applet is located at http://gharms.dhs.org/3.html. The Java code that calls JDBC is in http://gharms.dhs.org/Examples/FifthApplet/JDBCDataSource.java. Thanks in advance. Best regards, Gunnar Harms - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C API Question
Hi, I have a text file , with several MySQL instructions (CREATE TABLE, INSERT, SET @var, etc.). I want to execute all these instructions from a C program , using the API. It is possible to use the function mysql_query(), to execute all the instructions contained in the file in a single call to this function? , Can I execute several MySQL instructions in one only call to mysql_query() function? If it is possible, which is the best way to do this? Thanks in advance Javier Diaz IT Developer - Scanned for all known viruses by Messagelabs -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JBuilder6 and mm.mysql-2.0.11
Hi, Lonnie Wormley wrote: Is this the correct file to have in your class path? addpath /home/launi/JBuilder6/lib/mm.mysql-2.0.11/mm.mysql-2.0.11-bin.jar Yes, please add it into jdk.config. :) -- TAKAHASHI,"KEN",Tomohiro Borland -- Enabling a new digital world where our customers have the freedom of choice to develop, deploy, and integrate applications across the enterprise and the Internet. http://www.borland.com Don\'t miss the 13th Annual Borland$B%g(B Conference, May 18-22 in Anaheim, California. Hundreds of sessions are being offered to help you enable the new digital world with best-in-class solutions for Web Services, Java$BQ(Btechnologies, mobile and enterprise computing, cross-platform development, .NET$BQ(Band J2EE$BQ(B platform technologies, and more! Register today! http://www.borland.com/conf2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
different row count in explain select on different systems
I run mysql on a SuSe 7.3 and a FreeBSD 4.5 server. The FreeBSD mysql version is 3.23.49. The linux mysql version is 3.23.33. I just copied a large table using mysqldump | mysql from the linux server to the FreeBSD server. An explain statement on a select on this table shows different row counts on both systems: #SUSE: mysql explain select id from boardmessages where board=4301 and parent=0 order by id desc limit 0,20; +---+--+---+---+-+---+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+---+---++ | boardmessages | ref | board,parent | board | 4 | const | 16671 | where used; Using filesort | +---+--+---+---+-+---+---++ #FREEBSD: mysql explain select id from boardmessages where board=4301 and parent=0 order by id desc limit 0,20; +---+--+---+---+-+---+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+---+---++ | boardmessages | ref | board,parent | board | 4 | const | 13629 | where used; Using filesort | +---+--+---+---+-+---+---++ Can anyone explain this strange deviation? s.m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication question
Jeremy You can set it up so that the slave only gets updates to certain databases. Have you looked at the replication docs in the manual yet? Yes I did, but what put me off slightly was the concept of a master and slave. My requirement is per sql database not per sql server. Am I being stoopid or is this configuration possible:- Web Server Local Server DB1 copy of DB1 copy of DB2 DB2 Cheers Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Server Installation Problem
Hi All, I am getting following error msg when I try to install MySQL using rpm -i My* error: MySQL-3.23.49a-1.i386.rpm cannot be installed error: failed dependencies: data-showtable is needed by Msql-Mysql-DBI-perl-bin-1.1823-1 DBI-perl-bin is needed by Msql-Mysql-DBI-perl-bin-1.1823-1 MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.49a-1 Thankx in advance. Suresh R. Soni. __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
List users?
How would I list all users via the command line? sql,query -- Mike Yrabedra President 323 Enterprises Home of The MacDock and The MacSurfshop [http://macdock.com] : [http://macsurfshop.com] VOICE: 770.382.1195 ___ in all your ways acknowledge Him and He will direct your paths. Proverbs 3:6 NIV {{{ -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: High Availability questions
Young Sul wrote: Hi, I've got a website that uses a mysql backend database. Due to the way in which the database and development has been architected, I'm unable to load-balance the database between multiple DB servers, taking advantage of replication inherent in mysql. (this is due largely to developmental constraints, and heavy use of write only session-management) I *need* to somehow mirror and create a decent failover environment for my database. Currently, I mirror the master DB on another server, and can switch over ...manually... if the master dies. I'm wondering if others on this list have encountered a similar situation -- how did you finally architect your DB environment? One thing I have done with a PHP/MySQL web site that is heavily DB driven for fail over is the following. I mirror all the PHP code to a second server. The second server is a replication slave for the database. If the database fails or has issues then a redirect is given, sending the user to the backup server. Some tracking is lost, but for me that is okay right now. Two way replication would solve this by sending any changes back to the master when it comes up. This now makes the problem simpiler by only have to detect when the webserver is down. Jim -- /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - [EMAIL PROTECTED] http://www.FreeBSD.org The Power to Serve [EMAIL PROTECTED] http://www.TheHousleys.net [EMAIL PROTECTED] http://www.SimTel.Net - Your mouse has moved. Windows NT must be restarted for the change to take effect! Reboot now? [OK] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating an unique multipartkey with first part often NULL in InnoDB takes forever ...
Hi, reading the table-dump with MyISAM takes about 30 seconds. With InnoDB it ran 3 hours before I killed it. I removed the Unique-key definitions and it took 22 seconds with InnoDB. Now I'm creating one unique key and it's running more than 20 hours already. Perhaps someone with a support-contract may get or have the same problem and would ask the devs for a fix :) Table-specs (MyISAM): Version: 3.23.49a about 200k rows (on 100k rows localid is NULL) about 8M data about 8M index dump-size: 6.5M (tar.gz: 1.5M) CREATE TABLE `stock_reg` ( `woid` int(10) unsigned NOT NULL default '0', `regid` tinyint(3) unsigned NOT NULL default '0', `localid` char(10) default NULL, `symbol` char(20) default NULL, `kupdt` tinyint(1) NOT NULL default '0', PRIMARY KEY (`woid`,`regid`), UNIQUE KEY `locregid` (`localid`,`regid`), UNIQUE KEY `symregid` (`symbol`,`regid`) ) TYPE=MyISAM; /* 200k rows of inserts or 7 rows with --opt dump here*/ Table-spec (InnoDB) CREATE TABLE `stock_reg_new` ( `woid` int(10) unsigned NOT NULL default '0', `regid` tinyint(3) unsigned NOT NULL default '0', `localid` char(10) default NULL, `symbol` char(20) default NULL, `kupdt` tinyint(1) NOT NULL default '0', PRIMARY KEY (`woid`,`regid`) ) TYPE=Innodb; INSERT INTO stock_reg_new SELECT * FROM stock_reg; /* 22 secs */ CREATE UNIQUE INDEX `locregid` ON stock_reg_new (`localid`,`regid`); /* is still running after more than 20 hours */ CREATE UNIQUE INDEX `symregid` ON stock_reg_new (`symbol`,`regid`); /* dont know yet */ Regards, Christan Rabe mysql,query,table,... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication question
Hi Rob Read the manual, you can do exactly what you need to and it is explained in the manual. I am doing it myself. Regards Warren You can set it up so that the slave only gets updates to certain databases. Have you looked at the replication docs in the manual yet? Yes I did, but what put me off slightly was the concept of a master and slave. My requirement is per sql database not per sql server. Am I being stoopid or is this configuration possible:- Web Server Local Server DB1 copy of DB1 copy of DB2 DB2 Cheers Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Server Installation Problem
Sounds as though you haven't got Perl installed, or more particularly the DBI modules for MySql. If you on Linux, almost certainly to be found on your Linux CDs. HM Suresh R. Soni writes: Hi All, I am getting following error msg when I try to install MySQL using rpm -i My* error: MySQL-3.23.49a-1.i386.rpm cannot be installed error: failed dependencies: data-showtable is needed by Msql-Mysql-DBI-perl-bin-1.1823-1 DBI-perl-bin is needed by Msql-Mysql-DBI-perl-bin-1.1823-1 MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.49a-1 Thankx in advance. Suresh R. Soni. __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: List users?
Try: mysql -e SELECT user FROM mysql.user You may need to add the following as well mysql -h hostname -uyouruserid -pyourpassword -e SELECT user FROM mysql.user -Original Message- From: Mike Yrabedra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 6:55 AM To: [EMAIL PROTECTED] Subject: List users? How would I list all users via the command line? sql,query -- Mike Yrabedra President 323 Enterprises Home of The MacDock and The MacSurfshop [http://macdock.com] : [http://macsurfshop.com] VOICE: 770.382.1195 ___ in all your ways acknowledge Him and He will direct your paths. Proverbs 3:6 NIV {{{ -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: High Availability questions
On Tue, 19 Mar 2002, Young Sul wrote: Hi, I've got a website that uses a mysql backend database. Due to the way in which the database and development has been architected, I'm unable to load-balance the database between multiple DB servers, taking advantage of replication inherent in mysql. (this is due largely to developmental constraints, and heavy use of write only session-management) I *need* to somehow mirror and create a decent failover environment for my database. Currently, I mirror the master DB on another server, and can switch over ...manually... if the master dies. I'm wondering if others on this list have encountered a similar situation -- how did you finally architect your DB environment? I run a number of web servers all of which are mirrored by a second identical failover server. During normal office hours when the sites are busiest, the main server dumps all databases every hour to an NFS share and the failover server first drops all the backup databases and then imports them from the share. (The two servers have a private dedicated netwotk conection between them for this, separate from the main public Internet connections). The sites themselves, httpd configuration files, clinet FTP account info, etc are mirrored every night by simply updating all the files that have changed during the previous 24 hours. The failover server simply pings the main server every minute - if it fails to get a response for 2 minutes, then it stops Apache, Cold Fusion servers, etc, areconfigures its network interfaces to use the IP addresses of the failed server, replaces the Apache httpd.conf with the last one from the main server and restarts Apache. Outside of normal office hours and at weekends, the MySQL databases are synchronised every 3 hours and all changes made to the databases are logged using the NySQL logging feature. This can be 'played' back from the last update to reflect any changes made since the previous update. Simple but effective. Now that version 4.0.x is here, we might look at using the master/slave replication features this offers but there's no great rush and we need to do it in such a way that downtime on existing servers is minimised. Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mystery files
mysql mailing list user wrote: Hi, I my mysql server database directory (that is the directory that holds the .err and .pid files) so strange files have apeared. They look line machine-name-bin.001 machine-name-bin.002 etc machine-name-bin.index Some of them are huge! They seem like binary files, but are full readable SQL commands that could be from my applications. While we're on the subject of files that MySQL is generating, anyone out there got an idea what log.01, log.02, etc in the data directory are? Better still how-can/can-I stop them being produced? I know they're not the binary log files since they're being sent elsewhere. (extract from my.cnf #log= /home/mysql/tay-log #log-update = /var/mysql/updates log-bin = /var/mysql/binary/bin ) Sorry if this is another RTFM . . . . Bob Cross. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This message is confidential. It may also be privileged or protected by other legal rules. It is not an offer or acceptance of an offer, nor shall it form any part of a legally binding contract. If you have received this communication in error, please let us know by reply then destroy it. You should not use, print, copy the message or disclose its contents to anyone. E-mail is subject to possible data corruption, is not secure, and its content does not necessarily represent the opinion of this Company. No representation or warranty is made as to the accuracy or completeness of the information and no liability is accepted for any loss arising from its use. This e-mail and any attachments are not guaranteed to be free from so-called computer viruses. You should check for viruses before down-loading it to your computer equipment. This Company has no control over other websites to which there may be hypertext links and no liability is accepted in relation to those sites. This Company randomly monitors its e-mail system (including incoming e-mails) for operational purposes. Scottish Newcastle plc Registered in Scotland, Registered Number 16288 Registered Office: 33, Ellersly Road, Edinburgh, EH12 6HX * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Urgent: Please look at this error
Hi, I am using Redhat 6.2 kernel 2.2.20 and am getting this error below. I know that these paths are incorrect , should be /jive/usr/biosym/235 instead of /usr/biosym... where do I change this? Which file? Sorry if this is obvious, but I am new to mysql. Thanks, Amrita unknown user id: mysql Starting mysqld daemon with databases from /oscar2/mysql/mysql-3.23.47-sgi-irix6.5-mips/data 4673:/oscar2/mysql/mysql-3.23.47-sgi-irix6.5-mips/bin/mysqld: rld: Fatal Error: Cannot Successfully map soname 'libpthread.so' under any of the filenames /usr/biosym/235/irix51r4/biosymso/libpthread.so:/usr/lib32/libpthread.so:/usr/lib32/internal/libpthread.so:/lib32/libpthread.so:/opt/lib32/libpthread.so:/usr/biosym/235/irix51r4/biosymso/libpthread.so.1:/usr/lib32/libpthread.so.1:/usr/lib32/internal/libpthread.so.1:/lib32/libpthread.so.1:/opt/lib32/libpthread.so.1: 020319 18:46:10 mysqld ended __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: URGENT: Update Error with MySQL++
Thi Cao writes: All, I can't seem to perform an update on my database with MySQL++. The execution of the query always throws an exception. Here's a snippet of what I did. Query query(conn); query update employee set children = 3 where id = 9; query.execute(); The above code throws an exception everytime. What am I doing wrong? Thanks. Thi Don't use streams for commands that do not return result sets. Use exec() method instead. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: default value
Michal Dvoracek wrote: Hello, s possible to create table with default value returned by built-in function ?? like: No. create table x (a int not null default UNIX_TIMESTAMP()); What is wrong with the timestamp field type? When inserting new column in table field a will contains timestamp? sql,query Regards, Michal Dvoracek [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
Joel, I have read the manual related to windows several times. But the problem that I was not having is not mentioned in the manual. I am not too sure if I am the one who needs root canal like someone earlier claimed. Or the manual needs one. I dont think the manual is helpful for beginners. Of course, I can say that the manual is not good. Of course, everyone can say it is free. The manual blends UNIX with Windows when they should be separate. And does not explain at all when mysql -u root -p does not work. It seems to work for everyone for the first time. Not me! Before getting into nitty and gritty details with you on my problem, please dont bring up hardware limits. I have an excellent computer DELL 4100 Dimension with well over 256 megs ram..etc..etc.. on windows xp just installed few days ago. My processor is near 1 giga hertz! Who has 64 megs and 100 HRZ? Perhaps for those who work in the museum. Your information on how to grant access to databases for specific users is very good, but I was not able to put it to work. I could not done it as a root user. It just wont grant access to me as a root user.I cannot type mysql -u root -p OR mysql -u root at all. It will display ACCESS denied. Same problems with first and second installation. The second installation was not necessary; you said you said waste of time. I am not going to disagree on that, either. But the NT server from the first installation must have still been running in the background without my knowing. I cannot explain how things went wrong with the first installation. But it seemed that the first installation transmitted disease to the second. Everything was impossible with both of these installations. Until then, I learned how to use WINMYSQLADMIN for adding my username and password. MYSQLADMIN did not do any good at that time. Still I was not granted access until that traffic light icon thing popped up in the right corner. There were 7 of them there. I never saw them before! But I think it was the turning point. After deleting one by one. To make story short...I learned how to stop and start server with that traffic light icon. When I stopped the server, I then started it with NET START MYSQL.. Then I was able to type in my user name and password (not root user this time), but think root user will be granted access. It appears that WINMYSQLADMIN is finally put to work. I was granted access on my user name/password. Everything appears to be working fine. I think removing those 7 traffic lites and STOP Server and then START server somehow fixed everthing for me. I have been granted access already without doing any configuration. I did not have that privilege in the beginning. The manual does not explain what I should do if mysql -u root -p does not work. And on how to use WINMYSQLADMIN on adding users. And also traffic lights. There is a program called MYSQLADMIN for DOS does not cover examples on how to using many different commands. I am a very new mySQL newbie. I barely start. I feel very sorry for the new comers to mySQL. But hopefully, these new comers are able to locate newsgroups like this. I think the documentation does not need 300 pages. And they should be separate with UNIX and WINDOWS. And explain what we should do if mysql -u root -p does not work in some pages. Right now, it is still not clear what went wrong in the very beginning. Anyway, thanks for instructing me to cc the list...Now I see how MYSQL list mailing works. - Original Message - From: Joel Rees [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 11:47 PM Subject: Re: Access denied on use mysql; alan4100: If you reply to this, please CC the list. Keep reading the manual while you try things. From your questions, I am wondering if you are not reading the manual, or are just too impatient and are trying things before you read the whole story. If your processor is faster than 100MHz and has more than 64M RAM, I recommend downloading the manual.pdf (Adobe Acrobat), so you can browse it off-line. You'll need Acrobat, of course, but Acrobat is useful for reading many things, well worth the download time. You can find links to the documentation pages at mysql's main web page. To repeat what others have said, you need to understand how to set passwords, how to supply passwords when running the client, and how to GRANT access to databases to specific users. Yes, GRANT is a MySQL command, and you do need to understand it. It would be easier if you had someone handy to configure a database for you. Then you wouldn't need to understand the GRANT command, just how to log in with a user name and password. Actually, your initial installation should be without a root password, so you should be okay. Deleting the whole installation was a drastic step, and probably unnecessary. See below for a few comments: Your mentioning about mysql -h host -u user
Re: default value
From: Michal Dvoracek [EMAIL PROTECTED] s possible to create table with default value returned by built-in function ?? like: create table x (a int not null default UNIX_TIMESTAMP()); When inserting new column in table field a will contains timestamp? In general, this cannot be done. Default values cannot be the return of a MySQL function (as much as I'd love to use NOW() for default values!). However, there's one loophole. When inserting, not specifying a value for the first timestamp field in a table will generate the current timestamp. For example: -- CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50), entered TIMESTAMP ); INSERT INTO foo SET email='[EMAIL PROTECTED]'; SELECT * FROM foo; DROP TABLE foo; -- This will return: ++--++ | id | email| entered| ++--++ | 1 | [EMAIL PROTECTED] | 20020320092330 | ++--++ 1 row in set (0.00 sec) This won't give you UNIX_TIMESTAMP(), but it gives you the MMDDhhmmss timestamp. Does that help? -- denonymous www.coldcircuit.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ssl question part 2
See section 4.3.1 Grant and Revoke syntax and 4.3.8 Using secure connections. There is a very good version of the docs in frame format at http://www.bitbybit.dk/mysqlfaq . I highly recommend it. -Original Message- From: Chris Hanes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 12:17 PM To: [EMAIL PROTECTED] Subject: ssl question part 2 I asked this the other day but didn't get any responses. Is there anyone out there with experience running mysql 4 with ssl connections?. I would like to get this setup but can't find any good documentation on how to do it. In particular, it seems that one has to specify a key and certificate to the server. I have yet to find details on this in the mysql docs or in the list archives. I would be extremely grateful for any help. Thanks... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: C API Question
multiple commands Not as far as I know, you need to open your file, read the commands and process them one at a time. The C API is not magic and has no more capability than you do setting down and typing in the commands yourself. What you can do is build one HUGE insert command from lots of individual row inserts, but you need to do that programatically, too. If you have loads of inserts to perform, it may be better to bulk load the data from a file made of the inserts' data than a record at a time if speed is an issue. I'd sure try the simple way first, though. Ken -Original Message- From: Javier [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 5:35 AM To: [EMAIL PROTECTED] Subject: C API Question Hi, I have a text file , with several MySQL instructions (CREATE TABLE, INSERT, SET @var, etc.). I want to execute all these instructions from a C program , using the API. It is possible to use the function mysql_query(), to execute all the instructions contained in the file in a single call to this function? , Can I execute several MySQL instructions in one only call to mysql_query() function? If it is possible, which is the best way to do this? Thanks in advance Javier Diaz IT Developer - Scanned for all known viruses by Messagelabs -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: C API Question
At 11:35 + 3/20/02, Javier wrote: Hi, I have a text file , with several MySQL instructions (CREATE TABLE, INSERT, SET @var, etc.). I want to execute all these instructions from a C program , using the API. It is possible to use the function mysql_query(), to execute all the instructions contained in the file in a single call to this function? , Can I execute several MySQL instructions in one only call to mysql_query() function? No. Not for the C API, or for any API. The client-server protocol requires that you issue a single query at at time. But there's nothing to stop you from writing a utility function that takes all the statements and issues them. Then you just call *that* function once. And remember that you don't include a terminating semicolon on the statements when you use an API. If it is possible, which is the best way to do this? Thanks in advance Javier Diaz IT Developer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: still no umlauts in mysql --experts help needed
Regarding your issue with accents, I understand more now about your problem, you are using shell tools in free BSD and you have not set the LANG shell environment variable. Add the folling line to your shell environments (usually .profile). LANG=en_US.ISO_8859-1; export LANG or for german language shell messaging: LANG=de_DE.ISO_8859-1; export LANG This is a FreeBSD issue, by default LINUX sets the language environment for the shell to an ISO setting, FreeBSD does not. Ken - Original Message - From: sascha mantscheff [EMAIL PROTECTED] To: Harald Fuchs [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 5:52 AM Subject: Re: still no umlauts in mysql --experts help needed Am Mittwoch, 20. März 2002 11:24 schrieben Sie: I just installed mysql fresh out of the box on a fresh SuSE linux 7.1. mysql does not let me enter german umlauts, though. I tried to start it with mysql --default-character-set=latin_de, but it complained about the character sets not being installed. Then I installed the mysql server, which in turn installs the character sets. latin_de is not included, though. I tried it with mysql --default-character-set=german1 instead. No luck. Was meinst Du mit does not let me enter? Blockieren die entsprechenden Tasten? Erscheint nichts auf dem Bildschirm? Gibt es irgendeine Fehlermeldung? Liefert MySQL bei einem SELECT das Falsche zurück? keystrokes with umlauts do not have any echo on the screen. batch input (text files with sql statements with umlauts redirected to mysql) do not insert umlauts into the database (but the rest of the text, so that the words are mis-spelt). there is no error message. select statements contain any umlauts in the database, though, and I can enter umlauts into the database using other clients than mysql. s.m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: different row count in explain select on different systems
Hi Again Sasha, You will find that explain is not always accurate. It makes guesses about the date based on statistics stored with the table files. It does not actually run the query, however if you dumped the table and reimported the table table on Linux you would probably get the same result. Ken - Original Message - From: sascha mantscheff [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 6:40 AM Subject: different row count in explain select on different systems I run mysql on a SuSe 7.3 and a FreeBSD 4.5 server. The FreeBSD mysql version is 3.23.49. The linux mysql version is 3.23.33. I just copied a large table using mysqldump | mysql from the linux server to the FreeBSD server. An explain statement on a select on this table shows different row counts on both systems: #SUSE: mysql explain select id from boardmessages where board=4301 and parent=0 order by id desc limit 0,20; +---+--+---+---+-+---+ ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+---+ ---++ | boardmessages | ref | board,parent | board | 4 | const | 16671 | where used; Using filesort | +---+--+---+---+-+---+ ---++ #FREEBSD: mysql explain select id from boardmessages where board=4301 and parent=0 order by id desc limit 0,20; +---+--+---+---+-+---+ ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+---+ ---++ | boardmessages | ref | board,parent | board | 4 | const | 13629 | where used; Using filesort | +---+--+---+---+-+---+ ---++ Can anyone explain this strange deviation? s.m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
EXPLAIN feature request
Hi Guys, I would like to request an additional feature with EXPLAIN. EXPLAIN does not run the query guesses on how the query would be run. This is not always accurate. The slow-log however logs the actual information the query was run with (like explain only the truth!). Could we have an option with EXPLAIN to actually run the query and provide the true results? Maybe REXPLAIN, or EXPLAIN RUN query? Thanks Ken - Ken Menzel ICQ# 9325188 www.icarz.com [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
unable to start mysql daemon - bug?
Hello, I hope someone here can help me out with this. I can't find anything in the documentation that does help. Everything points to the port being in use, however it isn't. I know it for a fact (netstat/telnet), no the mysqld also is not running, I know that for a fact (process list). I installed mysql-3.23.49-win on Windows 2000 to drive D: (the installer lets one choose the location). After reading the docs, I can see that I need to start the daemon like this: mysqld --basedir d:\mysql However, when I do it I get this: D:\mysql\binmysqld --basedir d:\mysql Can't start server: Bind on TCP/IP port: No error 020318 14:54:30 Do you already have another mysqld server running on port: 3306 ? 020318 14:54:31 Aborting 020318 14:54:31 mysqld: Shutdown Complete Netstat -a reveals that nothing is using port 3306. The port is free. There is no local firewall on the computer blocking the port either. I have also tried the --port option to point to another port. Nope. Interesting how mysql says all ports are in use, even when they are not. Running mysqld without any arguments results in a 30 sec delay, then the command prompt reapears and the deamon is not running. I added a my.cnf to the C:\ and this also has no effect. I then removed this and tried the winmysqladmin.exe and this also does not help. However, it also does not report any errors, with the exception of trying to generate an error report with it, which returns the wonderfull error message that it can't generate a report because it can't connect to the server, hmm, why offer to generate an error report if it can only do it when it can connect? Not much help when you can get the server started. Can someone please explain to me how in the world to get the mysql server running on Windows 2000? I have administrator rights to the computer and have restarted after the installation. Thanks - Synopsis: Impossible to start or use mysql (or the daemon) based on the documentation. Organization: None MySQL support: None Severity: Fatal Priority: medium Category: mysqld Class: sw-bug Release: mysql-3.23.49 Exectutable: mysqld Environment: PIII800, 256MB RAM, 14GB free hdd space System:Win200 Compiler: NA Architecture: i - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Writing an SQL Parser and Optimizer
On 19 Mar 2002, at 18:14, Michael Halcrow wrote: Any suggestions as to where to start? There's a Perl module called SQL::Statement that does SQL parsing: http://search.cpan.org/search?dist=SQL-Statement -- Keith C. Ivey [EMAIL PROTECTED] Washington, DC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HEAP TABLES SUDDENLY DISAPPEARS****VERY URGENT**
Hi, Application uses Jboss server with mysql backend. IMDB tables (Heap tables) are created by the app. Things work on fine. Suddenly we miss Heap tables. We are not able to reproduce this at any particular event. It happens randomly. We could not make out the reason for this. We set max_heap_table_size=32M Data in heap tables is comparatively very less. Thanks, Rama Raju - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
I've tried everything thats in this email and nothing seems to work for me. I think my situation is a little different because I had access to my database originally but now I cant connect I can sign on as kreyes but I cant sign on as root. I can even sign in as ODBC. I dont know what to do. I have lost complete access to 'mysql' datebase - Original Message - From: alan4100 [EMAIL PROTECTED] To: Joel Rees [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 9:30 AM Subject: Re: Access denied on use mysql; Joel, I have read the manual related to windows several times. But the problem that I was not having is not mentioned in the manual. I am not too sure if I am the one who needs root canal like someone earlier claimed. Or the manual needs one. I dont think the manual is helpful for beginners. Of course, I can say that the manual is not good. Of course, everyone can say it is free. The manual blends UNIX with Windows when they should be separate. And does not explain at all when mysql -u root -p does not work. It seems to work for everyone for the first time. Not me! Before getting into nitty and gritty details with you on my problem, please dont bring up hardware limits. I have an excellent computer DELL 4100 Dimension with well over 256 megs ram..etc..etc.. on windows xp just installed few days ago. My processor is near 1 giga hertz! Who has 64 megs and 100 HRZ? Perhaps for those who work in the museum. Your information on how to grant access to databases for specific users is very good, but I was not able to put it to work. I could not done it as a root user. It just wont grant access to me as a root user.I cannot type mysql -u root -p OR mysql -u root at all. It will display ACCESS denied. Same problems with first and second installation. The second installation was not necessary; you said you said waste of time. I am not going to disagree on that, either. But the NT server from the first installation must have still been running in the background without my knowing. I cannot explain how things went wrong with the first installation. But it seemed that the first installation transmitted disease to the second. Everything was impossible with both of these installations. Until then, I learned how to use WINMYSQLADMIN for adding my username and password. MYSQLADMIN did not do any good at that time. Still I was not granted access until that traffic light icon thing popped up in the right corner. There were 7 of them there. I never saw them before! But I think it was the turning point. After deleting one by one. To make story short...I learned how to stop and start server with that traffic light icon. When I stopped the server, I then started it with NET START MYSQL.. Then I was able to type in my user name and password (not root user this time), but think root user will be granted access. It appears that WINMYSQLADMIN is finally put to work. I was granted access on my user name/password. Everything appears to be working fine. I think removing those 7 traffic lites and STOP Server and then START server somehow fixed everthing for me. I have been granted access already without doing any configuration. I did not have that privilege in the beginning. The manual does not explain what I should do if mysql -u root -p does not work. And on how to use WINMYSQLADMIN on adding users. And also traffic lights. There is a program called MYSQLADMIN for DOS does not cover examples on how to using many different commands. I am a very new mySQL newbie. I barely start. I feel very sorry for the new comers to mySQL. But hopefully, these new comers are able to locate newsgroups like this. I think the documentation does not need 300 pages. And they should be separate with UNIX and WINDOWS. And explain what we should do if mysql -u root -p does not work in some pages. Right now, it is still not clear what went wrong in the very beginning. Anyway, thanks for instructing me to cc the list...Now I see how MYSQL list mailing works. - Original Message - From: Joel Rees [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 11:47 PM Subject: Re: Access denied on use mysql; alan4100: If you reply to this, please CC the list. Keep reading the manual while you try things. From your questions, I am wondering if you are not reading the manual, or are just too impatient and are trying things before you read the whole story. If your processor is faster than 100MHz and has more than 64M RAM, I recommend downloading the manual.pdf (Adobe Acrobat), so you can browse it off-line. You'll need Acrobat, of course, but Acrobat is useful for reading many things, well worth the download time. You can find links to the documentation pages at mysql's main web page. To repeat what others have said, you need to understand how to set passwords, how
Loop Problem
Can someone look at this at see what I am doing wrong: The following SQL returns to records: 50 333 select SC_USER_ID as parmSecurename from CP_USER_COMMENT where SC_ID = 107793; If I include it in the following loop the result is 50 333 333 CREATE OR REPLACE Procedure SP_USER_COMMENT_2 as --Set the User SECURE ID parmSecurename CP_USER_COMMENT.SC_USER_ID%TYPE; Cursor SC_SECURENAME is select SC_USER_ID as parmSecurename from CP_USER_COMMENT where SC_ID = 107793; Begin Open SC_SECURENAME; --While SC_SECURE%rowcount 20 Loop /* Add next three lines */ fetch SC_SECURENAME into parmSecurename; dbms_output.put_line ('USER ID: '||parmSecurename); exit when SC_SECURENAME%NOTFOUND; End LOOP; Close SC_SECURENAME; Commit; End; / PLease help Thank you in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
Hi Liyju, Before answering your note, I need to explain about the using the syntax for mysql -u user -p. If I use the format above for mysql -u root -p, I can press ENTER after typing mysql -u root -p and wait for the password inquiry. I dont need to enter the password (I dont have one, anyway) and then click ENTER again to bypass the password inquiry. I think it is the right way to do this if I am a root user. I am not sure if the root user can have a password. Does it? It does not sound possible. Perhaps someone has already mentioned it. I had learned the hard way to skip that inquiry. I doubt it is mentioned in the manual. Nevertheless, I have other problems with the installation, anyway. I think typing in mysql is the very same with mysql -u root -p. The grants are the very same. I hope it does. Does it? Your explanation about using the grant commmand for those who dont have password. mysqlgrant all on mysql.* to root@localhost identified by ''; Your correct! But I was not able to do that earlier because of something that went wrong with the installation. And I had 7 traffic lights in right corner without my knowing. After eliminating them and NET CLOSE MYSQL and then NET START MYSQL. I had done that alone several times before to no avail. It seems the combination of elimination of 7 traffic lights and NET CLOSE MYSQL gave me a good fix. I still dont know what the real problem was. I thought all I could do was install. By now, I know how to use mysql -u root -p, but I could not see how it could affect the installation in any way. I will be watching Mysql for several days. It looks like the second installation gave more problems. The NT server must have still running from the first installation. I thought I had deleted all my Mysql directores and subdirectories and also the NT server; but it may had never deleted the NT server that was still running. I do really appreciate many people who tried to help me out. - Original Message - From: Liyju Janardhan [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 12:30 AM Subject: Re: Access denied on use mysql; Hi Alan, I am using mysql on linux, so I suggest you to send cc to the list, let others help you. How do you get into mysql? Hope ur doing the following. $mysql -u root -p Since you dont have the password you can use $mysql (remember $ means linux prompt) If the above command is successful you get an mysql prompt. mysqlshow grant for root@localhost; since you don't have a password you set the grants this way (I am not sure, try it) mysqlgrant all on mysql.* to root@localhost identified by ''; Then run mysqladmin outside mysql prompt i.e. Linux promptmysqladmin reload mysqlgrant all on mysql.* to root@loaclhost --- alan4100 [EMAIL PROTECTED] wrote: Hi, These errors are produced after using GRANT commmand. Remember I have no password. = mysql grant all on test.* to user@localhost identified by password; ERROR 1044: Access denied for user: '@localhost' to database 'test' mysql $mysqladmin reload - ; ERROR 1064: You have an error in your SQL syntax near '$mysqladmin reloa ne 1 mysql show databases; +-+ | Database| +-+ | Meet_a_Geek | | database05 | | db001 | | db002 | | mrbs| | mysql | | sample_db | | shopcart| | test| +-+ 9 rows in set (0.00 sec) mysql - Original Message - From: Liyju Janardhan [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 1:37 AM Subject: Re: Access denied on use mysql; --- alan4100 [EMAIL PROTECTED] wrote: The problem begins when I type in use mysql; Please look at the very left edge with three asterisks..*** where I marked. ACCESS denied Hope it will be fixed with your help so I can mind my own chores. == C:\mysql\binnet start mysql The MySql service is starting. The MySql service was started successfully. C:\mysql\binmysqladmin ping mysqld is alive C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.23.44-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysqld - \q Bye C:\mysql\binmysqld C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.23.44-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; +-+ | Database| +-+ | Meet_a_Geek | | database05 | | db001 | | db002 | | mrbs
RE: URGENT: Update Error with MySQL++
thank you very much -Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 8:25 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: URGENT: Update Error with MySQL++ Thi Cao writes: All, I can't seem to perform an update on my database with MySQL++. The execution of the query always throws an exception. Here's a snippet of what I did. Query query(conn); query update employee set children = 3 where id = 9; query.execute(); The above code throws an exception everytime. What am I doing wrong? Thanks. Thi Don't use streams for commands that do not return result sets. Use exec() method instead. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql -u root DENIED
- Original Message - From: Chuck Roberts [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 9:36 AM Subject: RE: mysql -u root DENIED -Original Message- From: alan4100 [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 11:50 AM To: [EMAIL PROTECTED] Subject: mysql -u root DENIED I just rmdir/s the old mysql and downloaded from Wisconsin mirror site... Then I tried C:\mysql\binmysql -u root ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) That's because you must specify a password like: mysql -u root -p * Is that true about the need to specify the password? I understand that mysql -u root -p will prompt password inquiry. But I dont have to reply the password at all. I can skip it by entering the ENTER key. So I dont need to specify the password. Hope I am correct. I can just as well use mysql -u root. Both are equilvalent. I may have to check on that again. Your information on the rest is informative, but I am using Windows XP. Your mentioning of mysql_install_db script is for UNIX exclusive. Windows XP dont have it. * will ask you for the password. mysql -u root -ppassword will supply the password on the command line. mysql -u root -ppassword db will use the database 'db' when you log in. Plus the user 'root' must have been put in mysql by successfully running the bin/mysql_install_db script. Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question...
Hi.. my name is Marco Leiva and i have just downloaded your software... i want to connect Mysql to Visual Basic, I downloaded the ODBC drivers but the following error message keeps on popping: An error Occurred while copying c:\windows\system\mfc30.dll restart windows and try installing again (before running any application using ODBC and then the next message pops: Some of the files were not installed. You will need to reinstall the drivers before they can be used. If you can help me fix this problem i'll be very thankful... THX... and keep the good work on!!! Marco. _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
fast,efficient query for counting
I'm setting up some tracking stats for (opt-in) e-mail campaigns, and one of the things I'm tracking is click-thrus. I'm going to re-direct traffic thru a script that will just count the number of clicks, and store that number in a mysql table. It needs to be fast and efficient, and I'm somewhat of a mysql newbie, so I was wonderinf if there is any more efficient way to simply add a number to the existing number in that table with mysql and perl. My way seems like a lot of code to do a little task. Any help is appreciated! # set up the db connection and SQL $dbh = DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpassword,{'RaiseError'=1}); $select_number = select total_clicked from $table; # actually execute the checking query $sth = $dbh-prepare($select_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } # loop thru the query and set the result variables while (my $ref = $sth-fetchrow_arrayref) { $total_clicked = $$ref[0]; } # add 1 to total_clicked $total_clicked=$total_clicked+1; # update the number $update_number = UPDATE $table set total_clicked = $total_clicked; # actually execute the update query $sth = $dbh-prepare($update_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
Anis, My problem has been solved without configurating my.cnf. It looks like I dont need it at all because WINADMINSQL has created its own my.ini file. You may find my email to others about the fix. I still dont know what the problem is. But it appears to be fixed ; fine and okay now. After closing and starting SERVER several times, it at last accepts my mysql -u root or mysql -u root -p with no ACCESS DENIED messages anymore. - Original Message - From: Ahmed S K Anis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:00 AM Subject: Re: Access denied on use mysql; Hi alan, quite a long one.but i figure u have problems with configuration ..rather than installation. on Win NT Kindly place my.cnf attached wiht his mail in C:\ Start Mysql again.. at mysql\bin type mysql if that does not work type mysql -u root and press enter please let me know.if that resulted in mysql promt. Cheers Anis alan4100 [EMAIL PROTECTED] 03/20/02 08:00PM Joel, I have read the manual related to windows several times. But the problem that I was not having is not mentioned in the manual. I am not too sure if I am the one who needs root canal like someone earlier claimed. Or the manual needs one. I dont think the manual is helpful for beginners. Of course, I can say that the manual is not good. Of course, everyone can say it is free. The manual blends UNIX with Windows when they should be separate. And does not explain at all when mysql -u root -p does not work. It seems to work for everyone for the first time. Not me! Before getting into nitty and gritty details with you on my problem, please dont bring up hardware limits. I have an excellent computer DELL 4100 Dimension with well over 256 megs ram..etc..etc.. on windows xp just installed few days ago. My processor is near 1 giga hertz! Who has 64 megs and 100 HRZ? Perhaps for those who work in the museum. Your information on how to grant access to databases for specific users is very good, but I was not able to put it to work. I could not done it as a root user. It just wont grant access to me as a root user.I cannot type mysql -u root -p OR mysql -u root at all. It will display ACCESS denied. Same problems with first and second installation. The second installation was not necessary; you said you said waste of time. I am not going to disagree on that, either. But the NT server from the first installation must have still been running in the background without my knowing. I cannot explain how things went wrong with the first installation. But it seemed that the first installation transmitted disease to the second. Everything was impossible with both of these installations. Until then, I learned how to use WINMYSQLADMIN for adding my username and password. MYSQLADMIN did not do any good at that time. Still I was not granted access until that traffic light icon thing popped up in the right corner. There were 7 of them there. I never saw them before! But I think it was the turning point. After deleting one by one. To make story short...I learned how to stop and start server with that traffic light icon. When I stopped the server, I then started it with NET START MYSQL.. Then I was able to type in my user name and password (not root user this time), but think root user will be granted access. It appears that WINMYSQLADMIN is finally put to work. I was granted access on my user name/password. Everything appears to be working fine. I think removing those 7 traffic lites and STOP Server and then START server somehow fixed everthing for me. I have been granted access already without doing any configuration. I did not have that privilege in the beginning. The manual does not explain what I should do if mysql -u root -p does not work. And on how to use WINMYSQLADMIN on adding users. And also traffic lights. There is a program called MYSQLADMIN for DOS does not cover examples on how to using many different commands. I am a very new mySQL newbie. I barely start. I feel very sorry for the new comers to mySQL. But hopefully, these new comers are able to locate newsgroups like this. I think the documentation does not need 300 pages. And they should be separate with UNIX and WINDOWS. And explain what we should do if mysql -u root -p does not work in some pages. Right now, it is still not clear what went wrong in the very beginning. Anyway, thanks for instructing me to cc the list...Now I see how MYSQL list mailing works. - Original Message - From: Joel Rees [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 11:47 PM Subject: Re: Access denied on use mysql; alan4100: If you reply to this, please CC the list. Keep reading the manual while you try things. From your questions, I am wondering if you are not
Re: Speed question.
At 01:17 AM 3/20/2002, you wrote: An Oracle DB programmer reviewed a query that I wrote and told me that putting constants at the beginning of the query would make it slower. I thought I'd go to the experts on MySQL and be told the truth one way of the other. Example query: SELECT TABLE1.COL, TABLE2.COL FROM TABLE1, TABLE2 WHERE TABLE1.COL='CONSTANT' AND TABLE1.COL=TABLE2.COL [ OR ] SELECT TABLE1.COL, TABLE2.COL FROM TABLE1, TABLE2 WHERE TABLE1.COL=TABLE2.COL AND TABLE1.COL='CONSTANT' I know that indexes are important and such, the above is just an example to give some visual. Thanks for any help you can offer. Rob. Rob, The only thing to watch out for is that the constant should be the same data type as the column. Fox example, don't compare a Char to an Integer (or vice-versa) otherwise MySQL will have to convert the column on a row by row basis thus eliminating the use of the index.. Also if you perform a function on a column name, then it also can't use the index. So if you need to use functions it is best that you move the function to the constant side of the comparison if possible. Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: EXPLAIN feature request
Ken Menzel writes: Hi Guys, I would like to request an additional feature with EXPLAIN. EXPLAIN does not run the query guesses on how the query would be run. This is not always accurate. The slow-log however logs the actual information the query was run with (like explain only the truth!). Could we have an option with EXPLAIN to actually run the query and provide the true results? Maybe REXPLAIN, or EXPLAIN RUN query? Thanks Ken - Ken Menzel ICQ# 9325188 www.icarz.com [EMAIL PROTECTED] Hi! This is of course doable, but EXPLAIN will be more accurate if all tables involved were ANALYZE'd ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dual master/slave config
Hi, I'd like to setup two database servers in a dual master/slave config. So both machines will be each others master and slave. Only one server will be queried (selects, inserts/updates and deletes). In case of failure of one master a network switch will handle failover. The failed machine should then (manually) reboot and the database should start to catch up with be new master. - Are the any of you that have experience with this kind of setup? How did you do it? Any info on this would be great. Cheers, Danny Kruitbosch sql, mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
Odd, I think it does a very good job of helping troubleshoot: http://www.mysql.com/doc/A/c/Access_denied.html On Wed, 20 Mar 2002 09:30:48 -0500, alan4100 wrote: And does not explain at all when mysql -u root -p does not work. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Inserting files from command prompt
Can a file be inserted to a mysql table from a unix(linux) command prompt? Also, can I query the same table and output the data to a filename.ext in a specified directory? Not sure if this is an appropriate question for the list, but all clues/examples would be greatly appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
log files
I have: mysql Ver 11.12 Distrib 3.23.32, for redhat-linux-gnu (i386) and wonder about the log file /var/log/mysqld.log shouldn't that include failed connections?? mvh: Pål Wester høgskoleingenør/programmerer never.no as, stortingsgt 30, 0161 Oslo direkte: 22 01 66 34, 906 900 62 tlf: 22 01 66 34, fax: 22 01 66 21 http://never.no - icq:103476059 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
Kathy, Sounds like mine, but it is fixed. I still dont know what the fix was. May I ask you how you signed in as Kreyes? Did you add username and password yourself? Since you are denied access to mysql (default) database, it surely sounded like mine. Before I may give you some help, may I ask you again how you signed in as Kreyes. Did you use mysqladmin? How long have you been using MySQL? Did you install recently? It sounds you may need to NET CLOSE MYSQL at DOS PROMPT. And then NET START MYSQL again. I am afraid there are still related programs running in the background. But five it a try. Two times at most. I did that several times over a few days period, before it worked correctly. And let me know. - Original Message - From: Kathy Reyes [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:11 AM Subject: Re: Access denied on use mysql; I've tried everything thats in this email and nothing seems to work for me. I think my situation is a little different because I had access to my database originally but now I cant connect I can sign on as kreyes but I cant sign on as root. I can even sign in as ODBC. I dont know what to do. I have lost complete access to 'mysql' datebase - Original Message - From: alan4100 [EMAIL PROTECTED] To: Joel Rees [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 9:30 AM Subject: Re: Access denied on use mysql; Joel, I have read the manual related to windows several times. But the problem that I was not having is not mentioned in the manual. I am not too sure if I am the one who needs root canal like someone earlier claimed. Or the manual needs one. I dont think the manual is helpful for beginners. Of course, I can say that the manual is not good. Of course, everyone can say it is free. The manual blends UNIX with Windows when they should be separate. And does not explain at all when mysql -u root -p does not work. It seems to work for everyone for the first time. Not me! Before getting into nitty and gritty details with you on my problem, please dont bring up hardware limits. I have an excellent computer DELL 4100 Dimension with well over 256 megs ram..etc..etc.. on windows xp just installed few days ago. My processor is near 1 giga hertz! Who has 64 megs and 100 HRZ? Perhaps for those who work in the museum. Your information on how to grant access to databases for specific users is very good, but I was not able to put it to work. I could not done it as a root user. It just wont grant access to me as a root user.I cannot type mysql -u root -p OR mysql -u root at all. It will display ACCESS denied. Same problems with first and second installation. The second installation was not necessary; you said you said waste of time. I am not going to disagree on that, either. But the NT server from the first installation must have still been running in the background without my knowing. I cannot explain how things went wrong with the first installation. But it seemed that the first installation transmitted disease to the second. Everything was impossible with both of these installations. Until then, I learned how to use WINMYSQLADMIN for adding my username and password. MYSQLADMIN did not do any good at that time. Still I was not granted access until that traffic light icon thing popped up in the right corner. There were 7 of them there. I never saw them before! But I think it was the turning point. After deleting one by one. To make story short...I learned how to stop and start server with that traffic light icon. When I stopped the server, I then started it with NET START MYSQL.. Then I was able to type in my user name and password (not root user this time), but think root user will be granted access. It appears that WINMYSQLADMIN is finally put to work. I was granted access on my user name/password. Everything appears to be working fine. I think removing those 7 traffic lites and STOP Server and then START server somehow fixed everthing for me. I have been granted access already without doing any configuration. I did not have that privilege in the beginning. The manual does not explain what I should do if mysql -u root -p does not work. And on how to use WINMYSQLADMIN on adding users. And also traffic lights. There is a program called MYSQLADMIN for DOS does not cover examples on how to using many different commands. I am a very new mySQL newbie. I barely start. I feel very sorry for the new comers to mySQL. But hopefully, these new comers are able to locate newsgroups like this. I think the documentation does not need 300 pages. And they should be separate with UNIX and WINDOWS. And explain what we should do if mysql -u root -p does not work in some pages. Right now, it is still not clear what went wrong in the very
server startup error
Hello all I have a slackware 8.0 distro that includes mysql 3.39.x (I'm not at that computer now, so that could be wrong slighly) install pkg I have installed it, and I can get it to run with the --skip-grant-tables option. When I try to run safe_mysqld without the skip option I get the following error: Table 'mysql.host' doesn't exist Now, I ran the mysql_install_db script and it made a bunch of databases including two called host.xxx Question, what is the proper syntax for a creation of the mysql.host table so that I can create it and get past this error. If anyone else has any experience with this, could this be a bug in the install_db script? Thanks for all your help Rance _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Writing an SQL Parser and Optimizer
Let us know your results ... Adib. Keith C. Ivey wrote: On 19 Mar 2002, at 18:14, Michael Halcrow wrote: Any suggestions as to where to start? There's a Perl module called SQL::Statement that does SQL parsing: http://search.cpan.org/search?dist=SQL-Statement - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: No support for multiple statements via JDBC?
Thanks Anvar- But I guess you didn't really follow this thread. We started with the statement you are suggesting because we are porting from another datasource. As it turns out, you cannot do a Insert ... Select from the same table in MySQL. It's supported in other databases, so I was surprised. The work around is to lock the table, do a select, then an insert then unlock the table. And, thanks to everyone, I got it working under JDBC. -Allon On 3/19/02 6:56 PM, Anvar Hussain K.M. [EMAIL PROTECTED] wrote: Hi Allon, Only one query can be sent to server at a time. But your particular case can be handled with the single query Insert Into orders (Number) select Max(Number) FROM Orders; Anvar. At 04:45 PM 19/03/2002 -0800, you wrote: Hi All- We are using the mm driver for MySQL and multiple statements in one connection do not seem to be supported: I.e. SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1) The driver throws a syntax error. You can do either one of these statements on their own, and you can have a semicolon in the statement, but you cannot combine the statements. Are we missing something? How would you do a transaction or table lock without multi statement support? -Allon Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
I've tried everything thats in this email and nothing seems to work for me. I think my situation is a little different because I had access to my database originally but now I cant connect I can sign on as kreyes but I cant sign on as root. I can even sign in as ODBC. I dont know what to do. I have lost complete access to 'mysql' datebase - Original Message - From: alan4100 [EMAIL PROTECTED] To: Liyju Janardhan [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:26 AM Subject: Re: Access denied on use mysql; Hi Liyju, Before answering your note, I need to explain about the using the syntax for mysql -u user -p. If I use the format above for mysql -u root -p, I can press ENTER after typing mysql -u root -p and wait for the password inquiry. I dont need to enter the password (I dont have one, anyway) and then click ENTER again to bypass the password inquiry. I think it is the right way to do this if I am a root user. I am not sure if the root user can have a password. Does it? It does not sound possible. Perhaps someone has already mentioned it. I had learned the hard way to skip that inquiry. I doubt it is mentioned in the manual. Nevertheless, I have other problems with the installation, anyway. I think typing in mysql is the very same with mysql -u root -p. The grants are the very same. I hope it does. Does it? Your explanation about using the grant commmand for those who dont have password. mysqlgrant all on mysql.* to root@localhost identified by ''; Your correct! But I was not able to do that earlier because of something that went wrong with the installation. And I had 7 traffic lights in right corner without my knowing. After eliminating them and NET CLOSE MYSQL and then NET START MYSQL. I had done that alone several times before to no avail. It seems the combination of elimination of 7 traffic lights and NET CLOSE MYSQL gave me a good fix. I still dont know what the real problem was. I thought all I could do was install. By now, I know how to use mysql -u root -p, but I could not see how it could affect the installation in any way. I will be watching Mysql for several days. It looks like the second installation gave more problems. The NT server must have still running from the first installation. I thought I had deleted all my Mysql directores and subdirectories and also the NT server; but it may had never deleted the NT server that was still running. I do really appreciate many people who tried to help me out. - Original Message - From: Liyju Janardhan [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 12:30 AM Subject: Re: Access denied on use mysql; Hi Alan, I am using mysql on linux, so I suggest you to send cc to the list, let others help you. How do you get into mysql? Hope ur doing the following. $mysql -u root -p Since you dont have the password you can use $mysql (remember $ means linux prompt) If the above command is successful you get an mysql prompt. mysqlshow grant for root@localhost; since you don't have a password you set the grants this way (I am not sure, try it) mysqlgrant all on mysql.* to root@localhost identified by ''; Then run mysqladmin outside mysql prompt i.e. Linux promptmysqladmin reload mysqlgrant all on mysql.* to root@loaclhost --- alan4100 [EMAIL PROTECTED] wrote: Hi, These errors are produced after using GRANT commmand. Remember I have no password. = mysql grant all on test.* to user@localhost identified by password; ERROR 1044: Access denied for user: '@localhost' to database 'test' mysql $mysqladmin reload - ; ERROR 1064: You have an error in your SQL syntax near '$mysqladmin reloa ne 1 mysql show databases; +-+ | Database| +-+ | Meet_a_Geek | | database05 | | db001 | | db002 | | mrbs| | mysql | | sample_db | | shopcart| | test| +-+ 9 rows in set (0.00 sec) mysql - Original Message - From: Liyju Janardhan [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 1:37 AM Subject: Re: Access denied on use mysql; --- alan4100 [EMAIL PROTECTED] wrote: The problem begins when I type in use mysql; Please look at the very left edge with three asterisks..*** where I marked. ACCESS denied Hope it will be fixed with your help so I can mind my own chores. == C:\mysql\binnet start mysql The MySql service is starting. The MySql service was started successfully. C:\mysql\binmysqladmin ping mysqld is alive C:\mysql\binmysql Welcome to the MySQL
Index only sometimes used
Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. The select I do is a complex one with multiple tables, and when it uses the index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50 seconds. I have simplified it down and founed the place the problem issee explains bellow: mysql explain select * from mailstat where domain_id in(1,3,4,7,50,20,11); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(1,2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955648 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used mysql explain select * from mailstat where domain_id in(1); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. Initialy I thought that maybe the index was corupted, so I built a copy of the table, and inserted a handfull of rows, but the I got the same results, except for my new table 1 and 3 didnt work, but 2 did!. Out of interest I tried other indexes and found results like: mysql explain select * from mailstat where id558693; +--+---+---+-+-+--++ + | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+---+---+-+-+--++ + | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 | where used | +--+---+---+-+-+--++ + 1 row in set (0.00 sec) *primary key is used mysql explain select * from mailstat where id558694; +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | PRIMARY | NULL |NULL | NULL | 2956363 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *primary key is NOT used If anyone could shed some light on this I would b most greatful :) Cheers, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
x-ref insert from within an insert. . .select
Hi, I am loading table A from values in table B with an insert . . . select statement. I also need to populate a x-ref table with the new insert ids from the insert . . . select. I know in Oracle I would put a trigger in to do the x-ref insert after each row of the insert. . .select is processed. Is there anyway to do this in mysql/php? Spamfilter: mysql Andrew Hazen Database Developer Jatech Solutions Inc. 160 Bartley Drive Toronto, ON M4A 1E1 www.jatech.ca 416-288-9833 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HEAP TABLES SUDDENLY DISAPPEARS****VERY URGENT**
At 10:15 -0500 3/20/02, Ramaraju.R.V wrote: Hi, Application uses Jboss server with mysql backend. IMDB tables (Heap tables) are created by the app. Things work on fine. Suddenly we miss Heap tables. We are not able to reproduce this at any particular event. Is someone restarting the server? That will make HEAP tables disappear... It happens randomly. We could not make out the reason for this. We set max_heap_table_size=32M Data in heap tables is comparatively very less. Thanks, Rama Raju - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Updating MySQL using MS ACCESS
Hello, Does anybody know how MySQL works with MS Access especially when it comes to preventing concurrent updates from more than two users? I use InnoDB tables and link them through ODBC driver to MS Access for viewing and updates. Will there be any issues if two or more people have this type of ODBC link updating the same record? If yes, is there any way around it to prevent this from happening? How do I set the row locking from MS-Access or MySQL does this automatically? I'd appreciate any help on this. Martin **Disclaimer** This Memo and any attachments, may be confidential and legally privileged. If you are not the intended recipient and have received this in error, kindly destroy this message and notify the sender. Thank you for your assistance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Fulltext Retrieval in big!??? Databases
Andreas, I gained some experience putting 15'000 documents into TEXT columns and indexing them. It's not quite 15gigs, but your amount of data will be reduced when you go from *.doc to pure ASCII. My table size is: -rw-rw1 mysqldaemon 187621564 Jan 8 20:02 plaintext.MYD -rw-rw1 mysqldaemon92874752 Jan 8 20:04 plaintext.MYI -rw-rw1 mysqldaemon8648 Jan 8 20:02 plaintext.frm Insert texts into the table and then later create the fulltext index with the ALTER TABLE statement. Having lots of RAM available speeds up index creation. It may be a lengthy process otherwise. And you have to increase some buffers. My model consists of the decisions from the Swiss Federal Court since 1954. Search runs are quite fast and I am happy with the results. BOOLEAN MODE still appears buggy but this situation may change shortly. Mail me off-list if you like. Thomas On Wed, 20 Mar 2002, Andreas Dau wrote: I have to plan a content retrieval system and am thinking of using mysql cause it's always been very reliable and convenient for my needs. Now, I do not have any experiences with databases of this size. The situation is as follows: We have round about 15gig of user documents (mainly MS Office documents such as doc or ppt). If you're storing those in native format, then they're not text documents and FULLTEXT searches are unlikely to benefit you. At least, I wouldn't guess so. Oh no, sorry I was not being clear. Of course I am thinking of a script that stores the _real_ text information into the field, which in addition will lower down the amount of data enormously since MS Office documents have a very poor snr *g* In that case, I'd say the best way to find out how well it'll work is to try it. Go for it! Well you're perfectly right that would be the best way. Unfortunately that is, as usual impossible since I have no comparable testing environment. Even If I'd take the effort and put all the documents into my local server , it's nowhere near to a high performance server and furthermore I do not have 50 people sitting here in my development LAN accessing all the different imaginable services. So I'd be really happy to get some _experience_ report. Is that understandable? My question is: can this be done with MySQL? What hardware (server) would be needed to get the results in a time anywhere near resonable? And, do the clients need to be tweaked or would it be acceptable to use a Browser for it (php on an Apache)? The client shouldn't matter all that much. The real work is being done on the server side -- unless you're planning to send several megabytes of search result to the client each time. :-) lol, naw *g* - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied on use mysql;
Kathy, This is 2nd copy because I forgot to cc to [EMAIL PROTECTED] myself. Try to cc to [EMAIL PROTECTED] . There are others who want to read it. But you said earlier about emails that did not do much of help. I was lucky. Perhaps you have to wait for your turn. First, I feel I am not ready to solve this problem for you because your telneting into the linux machine from Windows 2000 machine. I have never tried this. I can tell you what I know as a complete beginner. I need to correct my saying NET CLOSE SERVER. When it should be NET STOP SERVER. You need to use WINMYSQLADMIN for adding users. There may be other ways, but I only know this way. But in your case about telneting, I am not sure what your doing because I never tried it. I am not clear about telneting, are your accessing data remotely for editing,etc, from your local Mysql server. Or vice versa. your transmitting data from your local machine to the remote Mysql server. I think your just accessing remote data. Your reply may help. I can only describe about my present settings on my pc with mysql. The present settings right after the installation of Windows XP, I did not go anything beyond granting privileges to other users like you do. But it is ready to accept my typing input: USE MYSQL before the database changed in computer's reply. At that point, I assume I am granted access to this MYSQL database. Did you type in USE MYSQL command? I dont know why you cannot. I believe you should cc your emails to [EMAIL PROTECTED] Others will help you. I think you have mentioned emails, but got no help. Sometimes, patience pays off. Good luck. And then enlightment follows. - Original Message - From: Kathy Reyes [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:54 AM Subject: Re: Access denied on use mysql; I signed in as follows mysql -h localhost -u kreyes. mysql is installed on a linux machine and I am connecting remotely from a Windows 2000 machine. When I had access as root I was able to add users and grants. I didnt give myself access to the 'msql' database because I didnt really need it. But up until yesterday I was able to go into mysql by telneting into the linux machine and then typing mysql but now I keep getting an error. I'm not sure what you mean by NET CLOSE MYSQL. I am kinda new to mysql. Please enlighten me - Original Message - From: alan4100 [EMAIL PROTECTED] To: Kathy Reyes [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:55 AM Subject: Re: Access denied on use mysql; Kathy, Sounds like mine, but it is fixed. I still dont know what the fix was. May I ask you how you signed in as Kreyes? Did you add username and password yourself? Since you are denied access to mysql (default) database, it surely sounded like mine. Before I may give you some help, may I ask you again how you signed in as Kreyes. Did you use mysqladmin? How long have you been using MySQL? Did you install recently? It sounds you may need to NET CLOSE MYSQL at DOS PROMPT. And then NET START MYSQL again. I am afraid there are still related programs running in the background. But five it a try. Two times at most. I did that several times over a few days period, before it worked correctly. And let me know. - Original Message - From: Kathy Reyes [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:11 AM Subject: Re: Access denied on use mysql; I've tried everything thats in this email and nothing seems to work for me. I think my situation is a little different because I had access to my database originally but now I cant connect I can sign on as kreyes but I cant sign on as root. I can even sign in as ODBC. I dont know what to do. I have lost complete access to 'mysql' datebase - Original Message - From: alan4100 [EMAIL PROTECTED] To: Joel Rees [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 9:30 AM Subject: Re: Access denied on use mysql; Joel, I have read the manual related to windows several times. But the problem that I was not having is not mentioned in the manual. I am not too sure if I am the one who needs root canal like someone earlier claimed. Or the manual needs one. I dont think the manual is helpful for beginners. Of course, I can say that the manual is not good. Of course, everyone can say it is free. The manual blends UNIX with Windows when they should be separate. And does not explain at all when mysql -u root -p does not work. It seems to work for everyone for the first time. Not me! Before getting into nitty and gritty details with you on my problem, please dont bring up hardware limits. I have an excellent computer DELL 4100 Dimension with well over 256 megs
will this alter table lock selects?
I have a table with over 60,000 records, I have a text field that is a full text index. I need to change it to medium text. My question is can it do it without taking the site offline. will the table still be able to serve select queries while the alter is running or will they be locked until it is done, I know that it will lock inserts and updates while the alter is running, that's fine with me. query sql -- Jeff Bearer, RHCE Webmaster PittsburghLIVE.com 2002 EPpy Award, Best Online U.S. Newspaper - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Explanation of error message
Hi, In my mysql .err file I get a lot of the following error... Aborted connection 121031 to db: ..connection details... (Got an error reading communication packets) What does this mean, and is it bad, and what do I do about it? Any help appreciated. Howard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fast,efficient query for counting
Hi, You can just do an UPDATE: UPDATE $table set total_clicked = total_clicked+1 Regards rory oconnor wrote : I'm setting up some tracking stats for (opt-in) e-mail campaigns, and one of the things I'm tracking is click-thrus. I'm going to re-direct traffic thru a script that will just count the number of clicks, and store that number in a mysql table. It needs to be fast and efficient, and I'm somewhat of a mysql newbie, so I was wonderinf if there is any more efficient way to simply add a number to the existing number in that table with mysql and perl. My way seems like a lot of code to do a little task. Any help is appreciated! # set up the db connection and SQL $dbh = DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpassword,{'RaiseError'=1}); $select_number = select total_clicked from $table; # actually execute the checking query $sth = $dbh-prepare($select_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } # loop thru the query and set the result variables while (my $ref = $sth-fetchrow_arrayref) { $total_clicked = $$ref[0]; } # add 1 to total_clicked $total_clicked=$total_clicked+1; # update the number $update_number = UPDATE $table set total_clicked = $total_clicked; # actually execute the update query $sth = $dbh-prepare($update_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Joseph Bueno NetClub/Trader.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fast,efficient query for counting
From: rory oconnor [EMAIL PROTECTED] I'm setting up some tracking stats for (opt-in) e-mail campaigns, and one of the things I'm tracking is click-thrus. I'm going to re-direct traffic thru a script that will just count the number of clicks, and store that number in a mysql table. It needs to be fast and efficient, and I'm somewhat of a mysql newbie, so I was wonderinf if there is any more efficient way to simply add a number to the existing number in that table with mysql and perl. My way seems like a lot of code to do a little task. Any help is appreciated! Instead of getting the current # out of the db, incrementing it, and then updating the field, try this: CREATE TABLE foo (clicks INT); INSERT INTO foo SET clicks=0; SELECT * FROM foo; UPDATE foo SET clicks=clicks+1; SELECT * FROM foo; UPDATE foo SET clicks=clicks+1; SELECT * FROM foo; UPDATE foo SET clicks=clicks+1; SELECT * FROM foo; DROP TABLE foo; You can increment 'columnname' with 'columnname+1'; that way, you don't need to get the current value first. -- denonymous www.coldcircuit.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ADO API support?
I have had very much success with passing sql commands via ado over the myodbc layer. I wouldn't trust addnew and update batch methods nor have I tested how or if they work, it's better to keep your connections to the database stateless for performance purposes. Meaning to execute dml statements use the ado connection object to establish the connection and pass your sql command to the db, then close your connection. Get in, get out. Don't hold anything open as your looping thru your rs... imho Same with retrieving recordsets, I recommend using disconnected recordsets which work terrific. Make your connection, pass your select, get your rs back, close your connection and manipulate your new disconn rs object using the full scope of adodb.rs methods props. hth chris www.mydba2000.com From: Kelvin Lam [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: ADO API support? Date: Tue, 19 Mar 2002 17:43:56 -0800 Hi, I tried to read the documentation, but there are only examples instead of specific/explicit documentation My question is-which ADO objects/methods/properties does myODBC support? I can easily found the entire ADO programming model, all the objects, and their methods/properties in MSDN. But, I haven't been able to found the same quality work on myODBC documentation (or am I blind?) For example, not until I try it out, I realized, as posted in newsgroup, that the UpdateBatch is not support in myODBC. Please advise. I am using the stable release 2.50. If people had better experience and performance with newer version, please let me know. Any pointer would be greatly appreciated! Kelvin Lam Software Engineer Orbit Technology Corp 1528 Brookhollow Dr, Suite 66 Santa Ana, CA 92705 Phone: (714)427-0555 Fax: (714)427-0556 - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting files from command prompt
At 10:48 -0500 3/20/02, Stembridge, Michael wrote: Can a file be inserted to a mysql table from a unix(linux) command prompt? mysqlimport? Also, can I query the same table and output the data to a filename.ext in a specified directory? SELECT ... INTO OUTFILE Not sure if this is an appropriate question for the list, but all clues/examples would be greatly appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date and SQL statement
I need to do a search where I am looking at the table that has dates and I need to look only things that are 30 days or younger. Can so one show me a some SQL statements where you use , ? And the key for equal to or less? Chuck Payne Magi Design - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Access denied on use mysql;
I thought I was sending my email to the [EMAIL PROTECTED] but I guess I wasnt. Thank you so much for your help Alan, I cant figure out how I have lost access to 'mysql' (default) database. When I attempt to use mysql on my Linux machine I get the error message ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) How ever I can login using kreyes and ODBC. Can anyone please tell me where I can find that my.cnf file I've looked every where on my linux machine and cant find it. Thanks all!!! - Original Message - From: alan4100 [EMAIL PROTECTED] To: Kathy Reyes [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 11:59 AM Subject: Re: Access denied on use mysql; Kathy, First please cc to [EMAIL PROTECTED] . There are others who want to read it. But you said earlier about emails that did not do much of help. I was lucky. Perhaps you have to wait for your turn. First, I feel I am not ready to solve this problem for you because your telneting into the linux machine from Windows 2000 machine. I have never tried this. I can tell you what I know as a complete beginner. I need to correct my saying NET CLOSE SERVER. When it should be NET STOP SERVER. You need to use WINMYSQLADMIN for adding users. There may be other ways, but I only know this way. But in your case about telneting, I am not sure what your doing because I never tried it. I am not clear about telneting, are your accessing data remotely for editing,etc, from your local Mysql server. Or vice versa. your transmitting data from your local machine to the remote Mysql server. I think your just accessing remote data. Your reply may help. I can only describe about my present settings on my pc with mysql. The present settings right after the installation of Windows XP, I did not go anything beyond granting privileges to other users like you do. But it is ready to accept my typing input: USE MYSQL before the database changed in computer's reply. At that point, I assume I am granted access to this MYSQL database. Did you type in USE MYSQL command? I dont know why you cannot. I believe you should cc your emails to [EMAIL PROTECTED] Others will help you. I think you have mentioned emails, but got no help. Sometimes, patience pays off. Good luck. And then enlightment follows. - Original Message - From: Kathy Reyes [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:54 AM Subject: Re: Access denied on use mysql; I signed in as follows mysql -h localhost -u kreyes. mysql is installed on a linux machine and I am connecting remotely from a Windows 2000 machine. When I had access as root I was able to add users and grants. I didnt give myself access to the 'msql' database because I didnt really need it. But up until yesterday I was able to go into mysql by telneting into the linux machine and then typing mysql but now I keep getting an error. I'm not sure what you mean by NET CLOSE MYSQL. I am kinda new to mysql. Please enlighten me - Original Message - From: alan4100 [EMAIL PROTECTED] To: Kathy Reyes [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:55 AM Subject: Re: Access denied on use mysql; Kathy, Sounds like mine, but it is fixed. I still dont know what the fix was. May I ask you how you signed in as Kreyes? Did you add username and password yourself? Since you are denied access to mysql (default) database, it surely sounded like mine. Before I may give you some help, may I ask you again how you signed in as Kreyes. Did you use mysqladmin? How long have you been using MySQL? Did you install recently? It sounds you may need to NET CLOSE MYSQL at DOS PROMPT. And then NET START MYSQL again. I am afraid there are still related programs running in the background. But five it a try. Two times at most. I did that several times over a few days period, before it worked correctly. And let me know. - Original Message - From: Kathy Reyes [EMAIL PROTECTED] To: alan4100 [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:11 AM Subject: Re: Access denied on use mysql; I've tried everything thats in this email and nothing seems to work for me. I think my situation is a little different because I had access to my database originally but now I cant connect I can sign on as kreyes but I cant sign on as root. I can even sign in as ODBC. I dont know what to do. I have lost complete access to 'mysql' datebase - Original Message - From: alan4100 [EMAIL PROTECTED] To: Joel Rees [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 9:30 AM Subject: Re: Access denied on use mysql; Joel, I have read the manual related to windows several times. But the problem
Important question
Does anyone know of any good PL SQL Oracle Lists? thank you in advance Lance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: rpms for redhat 6.2
Hi, can any one suggest where I can get latest MySQL rpms for redhat 6.2 with glibc 2.1x... the new rpms .49+ are all for glibc 2.2+ _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: fast,efficient query for counting
$dbh = DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpassword,{'RaiseError'=1}); $update_number = UPDATE $table set total_clicked = total_clicked + 1; $sth = $dbh-prepare($update_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } --Greg Johnson -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 10:44 AM To: [EMAIL PROTECTED] Subject: fast,efficient query for counting I'm setting up some tracking stats for (opt-in) e-mail campaigns, and one of the things I'm tracking is click-thrus. I'm going to re-direct traffic thru a script that will just count the number of clicks, and store that number in a mysql table. It needs to be fast and efficient, and I'm somewhat of a mysql newbie, so I was wonderinf if there is any more efficient way to simply add a number to the existing number in that table with mysql and perl. My way seems like a lot of code to do a little task. Any help is appreciated! # set up the db connection and SQL $dbh = DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpassword,{'RaiseError'=1}); $select_number = select total_clicked from $table; # actually execute the checking query $sth = $dbh-prepare($select_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } # loop thru the query and set the result variables while (my $ref = $sth-fetchrow_arrayref) { $total_clicked = $$ref[0]; } # add 1 to total_clicked $total_clicked=$total_clicked+1; # update the number $update_number = UPDATE $table set total_clicked = $total_clicked; # actually execute the update query $sth = $dbh-prepare($update_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql/php on linux
On Tuesday 19 March 2002 11:35, you wrote: Hi all, I too new to php and mysql, trying to get things works. I installed Redhat 7.2, Apache, mysql and php. I copied a sample php script from the internet and save it as phptest.php in my cgi-bin. When calling it from my browser, I got Format error. Did I not do it right? Should I put it in my cgi-bin? Could some one send me a working php script and instruct me how to test it? Thanks FWIW I too am very wet behind the ears in Linux, but I installed Mandrake 8.1 and have MySql, PHP4, and Apache server all talking to each other without any configuring by me. Other than what I had to select during installation of course. If you choose to go that way make sure you select the 'expert' install option. You do not have to be very expert to use it. And BTW I also am able to use Postgres too as I want to learn about both of these excellent products. Good luck Bob big snip - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sqlbindcol
Hi, I am trying to get the following to work but it returns invalid values. SQLINTEGER size; SQLExecDirect (hstmt, Select 8, SQL_NTS); SQLBindCol(hstmt,1, SQL_C_ULONG, size,0,NULL); I tried various datatypes for size but I always get some invalid value. Is the above correct or do I need to make any modification ? Thanks Chetan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index only sometimes used
At 16:31 + 3/20/02, Steve Gardner wrote: Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. The select I do is a complex one with multiple tables, and when it uses the index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50 seconds. I have simplified it down and founed the place the problem issee explains bellow: For all the cases where the index is not being used, the rows value is pretty high. The optimizer has probably decided that the number of rows relative to the total number of rows in the table is such that it'll be just as fast to do a table scan as it would be to use the index. mysql explain select * from mailstat where domain_id in(1,3,4,7,50,20,11); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(1,2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955648 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used mysql explain select * from mailstat where domain_id in(1); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. Initialy I thought that maybe the index was corupted, so I built a copy of the table, and inserted a handfull of rows, but the I got the same results, except for my new table 1 and 3 didnt work, but 2 did!. Out of interest I tried other indexes and found results like: mysql explain select * from mailstat where id558693; +--+---+---+-+-+--++ + | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+---+---+-+-+--++ + | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 | where used | +--+---+---+-+-+--++ + 1 row in set (0.00 sec) *primary key is used mysql explain select * from mailstat where id558694; +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | PRIMARY | NULL |NULL | NULL | 2956363 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *primary key is NOT used If anyone could shed some light on this I would b most greatful :) Cheers, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: fast,efficient query for counting
You can avoid the step of extracting the row data by doing: UPDATE mytable SET numclicks = numclicks + 1 -Kevin -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 8:44 AM To: [EMAIL PROTECTED] Subject: fast,efficient query for counting I'm setting up some tracking stats for (opt-in) e-mail campaigns, and one of the things I'm tracking is click-thrus. I'm going to re-direct traffic thru a script that will just count the number of clicks, and store that number in a mysql table. It needs to be fast and efficient, and I'm somewhat of a mysql newbie, so I was wonderinf if there is any more efficient way to simply add a number to the existing number in that table with mysql and perl. My way seems like a lot of code to do a little task. Any help is appreciated! # set up the db connection and SQL $dbh = DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpa ssword,{'RaiseError'=1}); $select_number = select total_clicked from $table; # actually execute the checking query $sth = $dbh-prepare($select_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } # loop thru the query and set the result variables while (my $ref = $sth-fetchrow_arrayref) { $total_clicked = $$ref[0]; } # add 1 to total_clicked $total_clicked=$total_clicked+1; # update the number $update_number = UPDATE $table set total_clicked = $total_clicked; # actually execute the update query $sth = $dbh-prepare($update_number); if (!$sth) { die Error: . $dbh-errstr . \n; } if (!$sth-execute) { die Error: . $sth-errstr . \n; } - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: server startup error
Does mysql own the mysql database directory, and all its files? You should have run it as mysql_install_db --user=mysql Rance Hall wrote: Hello all I have a slackware 8.0 distro that includes mysql 3.39.x (I'm not at that computer now, so that could be wrong slighly) install pkg I have installed it, and I can get it to run with the --skip-grant-tables option. When I try to run safe_mysqld without the skip option I get the following error: Table 'mysql.host' doesn't exist Now, I ran the mysql_install_db script and it made a bunch of databases including two called host.xxx Question, what is the proper syntax for a creation of the mysql.host table so that I can create it and get past this error. If anyone else has any experience with this, could this be a bug in the install_db script? Thanks for all your help Rance _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index only sometimes used
On Wed, 20 Mar 2002, Steve Gardner wrote: Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used snip Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. snip If anyone could shed some light on this I would b most greatful :) Cheers, Steve Check the manual section on the query optimiser,.I seem to remember that if MySQL has to check more that some percentage of a table (30% ?) the optimiser guesses it can search the entire table just as quickly. Someone please correct my if I'm wrong. Try 'select domain_id, count(*) from mailstat group by domain_id' to check the distribution of your data. Hope this helps, Nigel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index only sometimes used
If more than a third of the rows would be selected, it is faster not to use the index. Over 2 million records match the '2'. How many are there total? Steve Gardner wrote: Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. The select I do is a complex one with multiple tables, and when it uses the index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50 seconds. I have simplified it down and founed the place the problem issee explains bellow: mysql explain select * from mailstat where domain_id in(1,3,4,7,50,20,11); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(1,2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955648 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used mysql explain select * from mailstat where domain_id in(1); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. Initialy I thought that maybe the index was corupted, so I built a copy of the table, and inserted a handfull of rows, but the I got the same results, except for my new table 1 and 3 didnt work, but 2 did!. Out of interest I tried other indexes and found results like: mysql explain select * from mailstat where id558693; +--+---+---+-+-+--++ + | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+---+---+-+-+--++ + | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 | where used | +--+---+---+-+-+--++ + 1 row in set (0.00 sec) *primary key is used mysql explain select * from mailstat where id558694; +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | PRIMARY | NULL |NULL | NULL | 2956363 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *primary key is NOT used If anyone could shed some light on this I would b most greatful :) Cheers, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: x-ref insert from within an insert. . .select
Yeah, I've abandoned the nice neat insert . . . select. Instead I am selecting the table B dataset, inserting each row into table A, capturing the insert id, populating the x-ref, and going on to the next row. Twice as much code, but it works. Andrew Hazen -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 1:12 PM To: Andrew Hazen; [EMAIL PROTECTED] Subject: Re: x-ref insert from within an insert. . .select At 11:42 -0500 3/20/02, Andrew Hazen wrote: Hi, I am loading table A from values in table B with an insert . . . select statement. I also need to populate a x-ref table with the new insert ids from the insert . . . select. I know in Oracle I would put a trigger in to do the x-ref insert after each row of the insert. . .select is processed. Is there anyway to do this in mysql/php? Yes, you do it yourself. :-) You'll probably find the PHP mysql_insert_id() function helpful in your situation. Spamfilter: mysql Andrew Hazen Database Developer Jatech Solutions Inc. 160 Bartley Drive Toronto, ON M4A 1E1 www.jatech.ca 416-288-9833 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unknown database
MySQL Error: 1049 (Unknown database 'databasename') I having a very weird unpredictable problem. I have a webserver which makes database connections via Perl and DBI. For the most part it works pretty much all the time but sometimes (2 or 4 times per month) it gives an error: Unknown database 'databasename'. Now, nothing has changed. I'm notified of the unknown database message, then when I check things out, I'm able to connect to the database. This very strange. While searching the archives, I noticed at least 2 other people with the same problem and we are all running some 4.x version of FreeBSD. Does anyone have any ideas? Any help is very much appreciated. Thanks, Ozette - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: still no umlauts in mysql --experts help needed
Thank you very much for the hint. The problem occurs on SuSE linux, though, not on FreeBSD, and lang=de_DE. Following your hint, I set LANG=de_DE.ISO_8859-1 The problem still remains. Then I set LANG=de since there is a directory /usr/share/locale/de on the system, but not /usr/share/locale/de_DE Still, no luck. Any more ideas how to solve this problem are warmly welcome. s.m. Am Mittwoch, 20. März 2002 15:57 schrieb Ken Menzel: Regarding your issue with accents, I understand more now about your problem, you are using shell tools in free BSD and you have not set the LANG shell environment variable. Add the folling line to your shell environments (usually .profile). LANG=en_US.ISO_8859-1; export LANG or for german language shell messaging: LANG=de_DE.ISO_8859-1; export LANG This is a FreeBSD issue, by default LINUX sets the language environment for the shell to an ISO setting, FreeBSD does not. Ken - Original Message - From: sascha mantscheff [EMAIL PROTECTED] To: Harald Fuchs [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 5:52 AM Subject: Re: still no umlauts in mysql --experts help needed Am Mittwoch, 20. März 2002 11:24 schrieben Sie: I just installed mysql fresh out of the box on a fresh SuSE linux 7.1. mysql does not let me enter german umlauts, though. I tried to start it with mysql --default-character-set=latin_de, but it complained about the character sets not being installed. Then I installed the mysql server, which in turn installs the character sets. latin_de is not included, though. I tried it with mysql --default-character-set=german1 instead. No luck. Was meinst Du mit does not let me enter? Blockieren die entsprechenden Tasten? Erscheint nichts auf dem Bildschirm? Gibt es irgendeine Fehlermeldung? Liefert MySQL bei einem SELECT das Falsche zurück? keystrokes with umlauts do not have any echo on the screen. batch input (text files with sql statements with umlauts redirected to mysql) do not insert umlauts into the database (but the rest of the text, so that the words are mis-spelt). there is no error message. select statements contain any umlauts in the database, though, and I can enter umlauts into the database using other clients than mysql. s.m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BLOB in MYSQ
Ibrahim, Wednesday, March 20, 2002, 9:52:25 AM, you wrote: IKC I want to knowledge about blob. IKC how can write or read blob to/from MYSQL database by using Visual C++. IKC Can any body help me. You can look at example in MySQL++ (C++ API for MySQL) documentation: http://www.mysql.com/documentation/mysql++/4_Tutorial.html#SECTION03161000 IKC Ibrahim Kivanc Cihan IKC Anadolu University - Turkey -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie needs help with install
Ron, Wednesday, March 20, 2002, 2:30:56 AM, you wrote: RS I'm installing MySQLto a Corel /Debian platform, following RS directions in the book Beginning PHP4. Successfully RS did ./configure, make, make install, and mysql_install_db RS as described on page 387. The book says I'll now need to RS start and stop the Mysql server using two sample RS scripts, to wit: RS [sample startmysqld script] RS #! /bin/bash RS /usr/bin/safe_mysqld RS[sample stopmysqld script] RS #!/bin/bash RS kill 'cat /usr/var/$HOSTNAME.pid' Use mysqladmin shutdown instead of kill 'cat /usr/var/$HOSTNAME.pid'. RS Looks easy enough except that I don't have a clue where RS these scripts are supposed to reside on my system. And/or RS whether I'm supposed to execute these manually from RS a console prompt or have them run automatically at RS boot and shutdown. By default safe_mysqld is located in /usr/bin/ or /usr/local/bin. You can find location of safe_mysqld by using command whereis safe_mysqld To start and stop mysql server at boot and shutdown edit your initscripts (it depends on how the Corel/Debian Linux is handling the boot process). RS Thanks in advance for your help. RS Ron Swanson -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Cannot access the Database on same Machine
colin, Tuesday, March 19, 2002, 11:04:31 PM, you wrote: co When i try to access the database which is on my Linux co machine i get the following error: co ERROR 2002; Can't connect to local MySQL server co through socket '/tmp/mysql.sock' (111) co There is nothing in this file, although i dont know co how to edit it. co I have gone through the Manual pages and the lists, co all the searches for this type of ERROR, but to no co avail. Is your MySQL server running? Check your privileges on the socket file and on the dir that contains socket file. You can find the description of this error at: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html co I have used the database before and have tables in co there but for some reason i cannot get in. co I don't want to reinstall it as i have tables that i co need, I am really stuck so any help will be very much co appreciated, co Thanks co Colin -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql 3.22.32 table checking
Shon, Tuesday, March 19, 2002, 7:48:30 PM, you wrote: SS i am running mysql version 3.22.32. yes i am planning an upgrade. in the SS meantime, i need to check (and possibly) repair my tables without bringing SS down the mysql daemon. For version 3.22.32 you can use only isamchk/myisamchk for check and recover of tables but you should stop MySQL server first. SS thanks, SS Shon Stephens -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
List users?
Mike, Wednesday, March 20, 2002, 2:55:18 PM, you wrote: MY How would I list all users via the command line? If you mean users in mysql privilege tables you can use the following command: mysql -e 'SELECT user FROM mysql.user' If you mean active user that use database you can get list of active threads by using command mysqladmin processlist Look at: http://www.mysql.com/doc/m/y/mysqladmin.html Other way is a usage of SHOW PROCESSLIST statement in the client, look at: http://www.mysql.com/doc/S/H/SHOW_PROCESSLIST.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Index only sometimes used
Steve, Wednesday, March 20, 2002, 6:31:09 PM, you wrote: SG Could someone explain to me why an index in a select I am doing is only SG sometimes used. SG The select I do is a complex one with multiple tables, and when it uses the SG index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50 SG seconds. SG I have simplified it down and founed the place the problem issee SG explains bellow: SG mysql explain select * from mailstat where domain_id in(1,3,4,7,50,20,11); SG +--+---+---+--+-+--++--- SG -+ SG | table| type | possible_keys | key | key_len | ref | rows | Extra SG | SG +--+---+---+--+-+--++--- SG -+ SG | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where SG used | SG +--+---+---+--+-+--++--- SG -+ SG 1 row in set (0.00 sec) SG *notice ind4 is used SG mysql explain select * from mailstat where domain_id in(1,2); SG +--+--+---+--+-+--+-+--- SG -+ SG | table| type | possible_keys | key | key_len | ref | rows| Extra SG | SG +--+--+---+--+-+--+-+--- SG -+ SG | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955648 | where SG used | SG +--+--+---+--+-+--+-+--- SG -+ SG 1 row in set (0.00 sec) SG *notice ind4 is NOT used SG mysql explain select * from mailstat where domain_id in(1); SG +--+---+---+--+-+--++--- SG -+ SG | table| type | possible_keys | key | key_len | ref | rows | Extra SG | SG +--+---+---+--+-+--++--- SG -+ SG | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where SG used | SG +--+---+---+--+-+--++--- SG -+ SG 1 row in set (0.00 sec) SG *notice ind4 is used SG mysql explain select * from mailstat where domain_id in(2); SG +--+--+---+--+-+--+-+--- SG -+ SG | table| type | possible_keys | key | key_len | ref | rows| Extra SG | SG +--+--+---+--+-+--+-+--- SG -+ SG | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where SG used | SG +--+--+---+--+-+--+-+--- SG -+ SG 1 row in set (0.00 sec) SG *notice ind4 is NOT used SG Basicaly, when I use '2' in the in() statement (and one or two other values SG from hundreds that do work), the index is not used. SG Initialy I thought that maybe the index was corupted, so I built a copy of SG the table, and inserted a handfull of rows, but the I got the same results, SG except for my new table 1 and 3 didnt work, but 2 did!. SG Out of interest I tried other indexes and found results like: SG mysql explain select * from mailstat where id558693; SG +--+---+---+-+-+--++ SG + SG | table| type | possible_keys | key | key_len | ref | rows | SG Extra| SG +--+---+---+-+-+--++ SG + SG | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 | SG where used | SG +--+---+---+-+-+--++ SG + SG 1 row in set (0.00 sec) SG *primary key is used SG mysql explain select * from mailstat where id558694; SG +--+--+---+--+-+--+-+--- SG -+ SG | table| type | possible_keys | key | key_len | ref | rows| Extra SG | SG +--+--+---+--+-+--+-+--- SG -+ SG | mailstat | ALL | PRIMARY | NULL |NULL | NULL | 2956363 | where SG used | SG +--+--+---+--+-+--+-+--- SG -+ SG 1 row in set (0.00 sec) SG *primary key is NOT used SG If anyone could shed some light on this I would b most greatful :) It will be useful for you to read how MySQL uses indexes. In some cases MySQL doesn't use indexes. If the result of query is more than 30% rows in a table, MySQL won't use indexes. It works much faster without index usage. You can read about it at: http://www.mysql.com/doc/M/y/MySQL_indexes.html SG Cheers, Steve -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com
important question
Does anyone know of any good PL SQL Oracle Lists? thank you in advance Lance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Dual master/slave config
On Wed, Mar 20, 2002 at 04:48:11PM +0100, Danny Kruitbosch wrote: Hi, I'd like to setup two database servers in a dual master/slave config. So both machines will be each others master and slave. Only one server will be queried (selects, inserts/updates and deletes). In case of failure of one master a network switch will handle failover. The failed machine should then (manually) reboot and the database should start to catch up with be new master. Makes sense. - Are the any of you that have experience with this kind of setup? Yes. How did you do it? Simply configured both servers to slave off of each other. All the fun comes in making absolutely sure that updates can't go to the non-master when the real master is running. If you have auto_increment ids, that can be a real problem. How are you planning to do it at the switch to avoid race conditions? There is a hosting company which offers redundant MySQL service via a configuration like this (thanks to a hardware load-balancer). It works pretty well once we got it all working right. I've yet to find a hardware-based load balancer that will use the algorithm I'd REALLY like in this situation. I need to bug the guys at Foundry Networks about this. What I want is a load-balancer where you can say: (1) Send all connections to host A. It is master. (2) If host A is down, send all connections to host B. (3) Even if host A comes back up, keep sending connections to B. (4) Only when B dies should A become the master again. The key realization is that database connections (unlike most HTTP) are persistent, so if you're not careful you can end up with applications that have connections to BOTH servers and they believe they're connected to the master. Most load-balancers have a master/slave configuration, but it always gives preference to a particular host. IF that host comes back up, it becomes master again. That's where you can get bitten hard. This can probably be solved with LVS, but I really want a hardware solution. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 41 days, processed 1,231,810,451 queries (345/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MIN function returns different results with an index.
Hi, I'm trying to sort out an inconsistency where adding an index changes the results of a query. Seems like this is incorrect. I'm running 3.23.36-debug, under NT. I have yet to try this on other versions... The following may be a bug, but it's at least inconsistent. I wonder if this is fixed in a later version? I've found that when a query uses only the INDEX to get the answer to a query, the result of a MIN function may be different. Note that this seems to only happen when MYSQL takes the shortcut of looking only at the INDEX to get the answer. My guess is that the INDEX object contains a node for the record with NULL. And when querying, mysql doesn't bother to check whether the value it has looked up is real. I believe this is inconsistent with ANSI standards. Run the following scenario script to see the difference. Comments ? Thanks! Here's a scenario script. create table sometable ( col1 int,col2 int,col3 int,col4 int); insert into sometable values ( 1,2,3,null); insert into sometable values ( 1,2,3,4); insert into sometable values ( 1,2,1,5); explain SELECT min(col4), max(col4) from sometable; SELECT min(col4), max(col4) from sometable; create index sometable_col3_idx on sometable (col3); create index sometable_col4_idx on sometable (col4); explain SELECT min(col4), max(col4) from sometable; SELECT min(col4), max(col4) from sometable; - Results: mysql create table sometable ( col1 int,col2 int,col3 int,col4 int); Query OK, 0 rows affected (0.01 sec) mysql insert into sometable values ( 1,2,3,null); Query OK, 1 row affected (0.00 sec) mysql insert into sometable values ( 1,2,3,4); Query OK, 1 row affected (0.00 sec) mysql insert into sometable values ( 1,2,1,5); Query OK, 1 row affected (0.00 sec) mysql mysql explain SELECT min(col4), max(col4) from sometable; +---+--+---+--+-+--+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+---+ | sometable | ALL | NULL | NULL |NULL | NULL |3 | | +---+--+---+--+-+--+--+---+ 1 row in set (0.00 sec) mysql SELECT min(col4), max(col4) from sometable; +---+---+ | min(col4) | max(col4) | +---+---+ | 4 | 5 | +---+---+ 1 row in set (0.00 sec) mysql mysql create index sometable_col3_idx on sometable (col3); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql create index sometable_col4_idx on sometable (col4); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql mysql explain SELECT min(col4), max(col4) from sometable; +--+ | Comment | +--+ | Select tables optimized away | +--+ 1 row in set (0.00 sec) mysql SELECT min(col4), max(col4) from sometable; +---+---+ | min(col4) | max(col4) | +---+---+ | NULL | 5 | +---+---+ 1 row in set (0.00 sec) -- Kevin Fries - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting files from command prompt
I do this from the command line sometimes: mysql -h HOST -pPASSWORD DATABASE statements.sql The statements.sql file should contain full sql statements. This will read in the file and execute the sql 1 line at a time. The opposite would be to use mysqldump like so: mysqldump -h HOST -pPASSWORD DATABASE TABLE statements.sql This would dump the contents of the given table in the given database into the statements.sql file. This create full insert statements with schema. You can pass it many options; see the web page docs for more. --Mark Paul DuBois wrote: At 10:48 -0500 3/20/02, Stembridge, Michael wrote: Can a file be inserted to a mysql table from a unix(linux) command prompt? mysqlimport? Also, can I query the same table and output the data to a filename.ext in a specified directory? SELECT ... INTO OUTFILE Not sure if this is an appropriate question for the list, but all clues/examples would be greatly appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Important question
Oracle-L has a number of PL/SQL gurus on it. Good list. --Walt Weaver Bozeman, Montana -Original Message- From: Lance Prais [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 12:42 PM To: [EMAIL PROTECTED] Subject: Important question Does anyone know of any good PL SQL Oracle Lists? thank you in advance Lance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date and SQL statement
On Wednesday, 20. March 2002 18:31, Chuck PUP Payne wrote: Hi! I need to do a search where I am looking at the table that has dates and I need to look only things that are 30 days or younger. Can so one show me a some SQL statements where you use , ? And the key for equal to or less? SELECT columns FROM table WHERE date DATESUB(CURDATE(), INTERVAL 30 DAY); For a detailed description see: http://www.mysql.com/doc/D/a/Date_and_time_functions.html Regards Georg (mysql, query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Java to MySQL connection
I'm trying to make a connection from a java app to a MySQL database I've created on my local PC. How can I tell the app where to look for the existing database? Code I'm working with: Class.forName(sun.jdbc.odbc.JdbcOdbcDriver).newInstance(); Connection con = DriverManager.getConnection(jdbc:odbc:Testing@localhost:3306, my username , my pass ); System.out.println(Connection Made.); As you can tell from this, my database is named Testing. All I'm trying to do right now is get a connection. Right now when I run this code I get Error: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. I'm not sure I have the odbc driver setup correctly either. I downloaded mm.mysql-2.0.11 but I'm not really sure how to bring it into the mix as far as the driver goes. Thanks for any direction. Chris Stewart [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cannot access the Database on same Machine
Hi, The problem was due to permissions in the end, i used the following commands to get into the database: shell chown -R root . shell chown -R root ./bin shell bin/safe_mysqld --user=root bin/mysqladmin version . . bin/mysql I can now use the database, but it still doesn't explain why i suddenly wasn't able to use the database, even though i am the only one using it. Anyway thanks for the reply. Colin --- sascha mantscheff [EMAIL PROTECTED] wrote: probably something wrong with the permissions in the mysql database. as a workaround, try to connect not via the local socket, but via a tcp/ip-connection: # mysql -h 127.0.0.1 -u my-user -p my-database or insert the real ip of the machine instead of the loopback address. check if there is an entry in the mysql.user table with host=localhost and user=. this would preclude any local user to log into the database. s.m. Am Dienstag, 19. März 2002 22:04 schrieben Sie: Hi, When i try to access the database which is on my Linux machine i get the following error: ERROR 2002; Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) There is nothing in this file, although i dont know how to edit it. I have gone through the Manual pages and the lists, all the searches for this type of ERROR, but to no avail. I have used the database before and have tables in there but for some reason i cannot get in. I don't want to reinstall it as i have tables that i need, I am really stuck so any help will be very much appreciated, Thanks Colin __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date and SQL statement
select * from accounts where duedate=convert(float,getdate()) - Original Message - From: Chuck PUP Payne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 12:31 PM Subject: Date and SQL statement I need to do a search where I am looking at the table that has dates and I need to look only things that are 30 days or younger. Can so one show me a some SQL statements where you use , ? And the key for equal to or less? Chuck Payne Magi Design - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can i use rsync instead of Replication
On Tue, Mar 19, 2002 at 02:08:17PM +0530, Charitha wrote: Hello all, Can i use rsync instead of replicating two servers for mysql( particular database db). If yes Then just clear off my doubt whether it will lead to any data loss as while doing rsync. If the data on the master is in a consistent state (consider using FLUSH TABLES WITH READ LOCK), yes. rsync works great. I use it fix problems once in a while. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 41 days, processed 1,232,149,584 queries (345/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php