Unclear on UPDATE versus INSERT, and a simple query that is not working
MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have two tables, one old, and one new. In both tables I have a column called active, which is either 0 or 1, to denote whether a user is to receive email or not. I am migrating all the data from the old table to the new. I want to make sure that the active column is identical, so I simply want to copy the fields from the old table to the new, overwriting what may exist in the new. I want to match the fields by using the member ID, called id in the old table and MEMBER_ID in the new. This is the statement I thought would work. UPDATE forum_members( active ) SELECT active FROM members WHERE forum_members.MEMBER_ID = members.id But MySQL tells me: |You have an error in your SQL syntax| The Questions: What do I need to do to correct the syntax of the statement? What I've done so far: I have looked at the online MySQL manual for the correct syntax for INSERT and UPDATE. But despite reading them, I'm still not sure which I want to be using, and how. Any assistance is much appreciated. Thank you. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unclear on UPDATE versus INSERT, and a simple query that is not working
2005/9/15, Dave [EMAIL PROTECTED]: MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have two tables, one old, and one new. In both tables I have a column called active, which is either 0 or 1, to denote whether a user is to receive email or not. I am migrating all the data from the old table to the new. I want to make sure that the active column is identical, so I simply want to copy the fields from the old table to the new, overwriting what may exist in the new. I want to match the fields by using the member ID, called id in the old table and MEMBER_ID in the new. This is the statement I thought would work. UPDATE forum_members( active ) SELECT active FROM members WHERE forum_members.MEMBER_ID = members.id I'm sure other on the list will explain in details, but I'll do short. I understood that forum_members is the new table, members the old one. So : UPDATE forums_members,members SET forums_members.active=members.active WHERE forums_members.member_id = members.id -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unclear on UPDATE versus INSERT, and a simple query that is not working
UPDATE forums_members,members SET forums_members.active=members.active WHERE forums_members.member_id = members.id Oh, I see. I now feel a little foolish as I should have grasped that. For some reason I assumed that by specifying where to get the data from, it would be assumed that's what the data was set to. But of course that's not the case. One specifies what to get, and then separately specifies what to set. Thank you for clearing that up. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding auto increment value in the dump file
I have not identified which options to use to have the current value of the AUTO_INCREMNT included in the dump file. Is this possible? -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Myisam or innodb
I have moved my data from mysql 3 to 4, across various updates, over the past few years. I use phpmyadmin generally, and it defaults to making tables myisam. I can not seem to find a really clear answer as to why I want to use one over the other. Generally, I use myisam and change it only when I need to do a rollback or something like that. Since I seem to be able to toggle from myisam to innodb without any adverse affects, why would one chose one over the other? Is there a way to prevent the use of myisam if it is old and slowly being deprecated? I am a host and do not control the table structure of clients databases. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EOFException with jdbc driver
Hi all, I have a Java webapp using a mysql database. It all used to run well, but I now frequently get the following error: 2005-09-15 11:33:32,115 [Thread-23] ERROR rotation - com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622) at com.mysql.jdbc.Connection.execSQL(Connection.java:2370) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183) I cannot really say when this started, but I did not get the impression that I changed anything in my software. It also seems that this is not restricted to certain queries, but is accidental. My configuration is Suse Linux 9.1, MySQL-Max-4.1.5-0 (the binary rpm from mysql.com), mysql-connector-java-3.1.7, Java 1.4.2. I did not find anything about how to resolve this and also not a real explanation what the error means. Any ideas? Thanks, Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Myisam or innodb
Scott Haneda [EMAIL PROTECTED] wrote on 15/09/2005 10:31:48: I have moved my data from mysql 3 to 4, across various updates, over the past few years. I use phpmyadmin generally, and it defaults to making tables myisam. I can not seem to find a really clear answer as to why I want to use one over the other. Generally, I use myisam and change it only when I need to do a rollback or something like that. Since I seem to be able to toggle from myisam to innodb without any adverse affects, why would one chose one over the other? Is there a way to prevent the use of myisam if it is old and slowly being deprecated? I am a host and do not control the table structure of clients databases. Innodb does not yet support FullText. While I believe that there are circumstances in which InnodDB is faster, I think that many applications will find MyIsam faster.\\ I don't thing InnoDB supports Load Data From Master, making adding a replication slave harder. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
places index in separate DISK.
Hi Friends, I am new starter in MYSQL, but have worked extensively in oracle. I wanted to know how we can place indexes of tables on different disk in MYSQL. Generally in oracle we have the option of specifying the tablespace on different disk. Can we do the same in mysql to disk contention. I am extermelly sorry for making comparison, please forgive me. I am using Linux and MYSQL version 4.1
Many Key_blocks_unused - why?
Hi list, I have noticed that the key buffer usage (defined in MySQL Administrator as Key_blocks_used *key_cache_block_size) always stays the same, and that I have a large amount of Key_blocks_unused, which seems to me there's a potential performance gain somewhere: show variables like 'key%'; +--+---+ | Variable_name| Value | +--+---+ | key_buffer_size | 134217728 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | +--+---+ show status like 'key%'; +++ | Variable_name | Value | +++ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 113335 | | Key_blocks_used| 114777 | | Key_read_requests | 1355363461 | | Key_reads | 27384734 | | Key_write_requests | 146025506 | | Key_writes | 128187590 | +++ The maximum number of blocks that can be allocated is: key_buffer_size / key_cache_block_size = 131072, right? Which means most blocks are unused! Also, the quotient of Key_reads over Key_read_requests is 0.02, above the recommended max. of 0.01. How can I make use of the unused key blocks, and improve performance? FWIW: using MySQL 4.1.10 on Solaris 8. Kind regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EOFException with jdbc driver
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stefan Kuhn wrote: Hi all, I have a Java webapp using a mysql database. It all used to run well, but I now frequently get the following error: 2005-09-15 11:33:32,115 [Thread-23] ERROR rotation - com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622) at com.mysql.jdbc.Connection.execSQL(Connection.java:2370) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183) I cannot really say when this started, but I did not get the impression that I changed anything in my software. It also seems that this is not restricted to certain queries, but is accidental. My configuration is Suse Linux 9.1, MySQL-Max-4.1.5-0 (the binary rpm from mysql.com), mysql-connector-java-3.1.7, Java 1.4.2. I did not find anything about how to resolve this and also not a real explanation what the error means. Any ideas? Thanks, Stefan Stefan, Something is closing the network connection abrubtly between JDBC and your MySQL server. Things to check are: 1) Connections don't sit longer than 'wait_timeout' on the server. 2) If the mysql server is on a different machine than your application: 2a) There are no network hardware issues 2b) There isn't a firewall between the server and your application that times-out connections 3) MySQL isn't crashing in between queries (see your mysql server error log) -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDKXaetvXNTca6JD8RAoojAJ9rC9QqM5WEBgASOJPagUuv3DF+hACfRv9j M/WMHIbCvVJdhmXNzHijjWM= =OPzU -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions are not enable
Hi i use a sofware to connect to mysql and it can connect with succes but wanted to share acces to mysql to many poeple and it popup [MUSQL][ODBC 3.51 Driver] Transaction are not enable Is ot a mysql server message or not ( my application ) thank's -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database link
Hi, How can i connect from MYSQL db to an oracle database. Do we need to create any database link or use some ODBC. Please help regards anandkl
Re: database link
Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:33:16 AM: Hi, How can i connect from MYSQL db to an oracle database. Do we need to create any database link or use some ODBC. Please help regards anandkl The only way to get one MySQL database server to directly manage data in a table that resides within another server is through the Federated storage engine. Federated is a new feature coming with v5.0 and is similar to MSSQL's linked table feature. Currently it only supports MySQL to MySQL table interactions. When you say connect from MYSQL db to an oracle database what exactly are you trying to accomplish? What are you trying to do? What problem are you having that you think the connection would solve? Please give us some additional details so we can answer your question more thoroughly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: database link
Hi Ananda, I am not sure what do you mean by connecting MySQL to Oracle: * Is it a permanent connection * Do you want to transfer data from one to the other * or ? Mikhail Berman -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 10:33 AM To: mysql@lists.mysql.com Subject: database link Hi, How can i connect from MYSQL db to an oracle database. Do we need to create any database link or use some ODBC. Please help regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incorrect latin1_danish_ci collation
Description: The letter 'ö' (o umlaut) and Danish 'ø' are treated as equal causing duplicate key problems. They are not ! Check also 'ä' and 'æ' which are not equal. It was present in 4.1.8 and is present in 4.1.14. How-To-Repeat: Fix: Submitter-Id: Originator:[EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Incorrect latin1_danish_ci collation Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.14-standard (MySQL Community Edition - Standard (GPL)) Server: /usr/local/mysql/bin/mysqladmin Ver 8.41 Distrib 4.1.14, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.14-standard Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 hour 18 min 15 sec Threads: 2 Questions: 3352395 Slow queries: 4 Opens: 40754 Flush tables: 1 Open tables: 18 Queries per second avg: 714.035 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux vennely.jth.net 2.6.12-1.1372_FC3 #1 Fri Jul 15 00:59:10 EDT 2005 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.4/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posi\ x --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redh\ at-linux Thread model: posix gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) Compilation info: CC='ccache gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='ccache gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-construc\ tors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 20 23:50 /lib/libc.so.6 - libc-2.3.5.so -rwxr-xr-x 1 root root 1505808 Apr 7 12:58 /lib/libc-2.3.5.so -rw-r--r-- 1 root root 2409956 Apr 7 12:03 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 7 11:29 /usr/lib/libc.so -rw-r--r-- 1 root root 204 Apr 7 11:29 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/b\ in' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--e\ nable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--wi\ th-mysqld-ldflags=-all-static' '--with-raid' '--with-readline' '--with-embedded-server' '--with-archive-storage-engine' '--with-inn\ odb' 'CC=ccache gcc' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-\ constructors' 'CXX=ccache gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Major Difference in response times when using Load Infile uti lity
At 11:31 AM 9/13/2005, you wrote: Hi all, I found that load infile should not take this much time(6 hrs) to load 5.5 million queries. Some people are saying it should not even take more than 10mins. So I think I am doing something wrong in my my.cnf file. I am Using MySQL 4.1.13 version and 2.4.20 kernel on RH9. I am including the my.cnf as well as the 'show table status' output here. Please let me know if any extra info is needed. I'm not that familiar with InnoDb, but I wonder if it could be your transaction/binary log files? Can you turn these off to see if there is a speed improvement? If there is an improvement, then you'll need to move these files to a different drive (different spindle). Mike M/c config : 2G RAM and Dual CPU 32-bit. mysql show table status; +++-++-++--- --+-+--+---++--- --+-++---+-- ++--+ | Name | Engine | Version | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +++-++-++--- --+-+--+---++--- --+-++---+-- ++--+ | stats | InnoDB | 9 | Dynamic| 4237002 | 1028 | 4357881856 |NULL |150749184 | 0 | NULL | 2005-09-12 23:16:21 | NULL| NULL | latin1_swedish_ci | NULL || InnoDB free: 16188416 kB | +++-++-++--- --+-+--+---++--- --+-++---+-- ++--+ 1 row in set (2.41 sec) I am running the load file utility from the server itself. (server config..2G RAM, Dual CPU, RH9, 2.4.20 kernel, alloted 20G of disk space, both logs and data files are on same partition). I am including the my.cnf file which I am using now. Can someone please tell me if I am setting anything seriously wrong. $$cat /etc/my.cnf [mysqld] user=mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock default-table-type=innodb init_connect='SET AUTOCOMMIT=0' transaction-isolation = READ-COMMITTED key_buffer = 250M wait_timeout = 10 max_connections = 400 connect_timeout = 50 table_cache = 1024 max_allowed_packet = 4M sort_buffer_size = 2M read_buffer_size = 2M binlog_cache_size = 1M max_heap_table_size = 64M max_user_connections = 1000 thread_concurrency = 4 query_cache_type = 0 query_cache_limit = 2M query_cache_size = 32M #thread_stack = 96k tmp_table_size = 32M log_error log_warnings = 2 log_slow_queries long_query_time = 2 log_long_format tmpdir = /tmp # *** INNODB Specific options *** #This conf uses 65%(Between 50 and 80%) of RAM, so glibc crash #should not be a problem. innodb_buffer_pool_size = 700M innodb_data_home_dir = innodb_data_file_path = /data/data1:2G;/data/data2:2G;/data/data3:2G;/data/data4:2G;/data/data5:2G;/ data/data6:2G;/data/data7:2G;/data/data8:2G;/data/data9:2G;/data/data10:2G:a utoextend innodb_fast_shutdown = 1 innodb_file_io_threads = 4 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 8M innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 90 innodb_log_file_size = 100M innodb_log_files_in_group =5 innodb_log_group_home_dir = /logs [mysql.server] user=mysql basedir=/var/lib [mysqldump] quick max_allowed_packet=16M [safe_mysqld] default-table-type=innodb init_connect='SET AUTOCOMMIT=0' err-log=/var/log/mysqld.log [mysqld_safe] default-table-type=innodb init_connect='SET AUTOCOMMIT=0' err-log=/var/log/mysqld.log open-files-limit = 4096 Thank you sujay -Original Message- From: Alan Williamson [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 13, 2005 8:48 PM To: mysql@lists.mysql.com Subject: RE: Major Difference in response times when using Load Infile utility Test 1 Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. Test2 Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Is this an InnoDB database by any chance? If it is, and it is a clean import, then disable the FOREIGN_KEY_CHECKS. SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; This is a small tip i picked up on the MySQL documentation that someone had left in the comments and has been to date one of those tips that has literally saved DAYS of my life. a ps Remember to put them back on again after you finish the import SET AUTOCOMMIT = 1; SET FOREIGN_KEY_CHECKS=1; -- Alan Williamson,
[newbie] moving mysql db to new server
hi all, my machine crashed and i'd would like to restore my mysql databases on my new system. i'm having a backup of the entire drive, but no dump. my system is mac os x.3 server. is there a way to dragdrop some folders to my new system? thanks for your help :-) julien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database link
No. You will need to use an application external to the server to perform the data collection process. This application can be one you write yourselves or something you purchase. There are many ways to perform things on a schedule: a) you application continuously runs and performs certain actions according to an internal timer b) the operating system uses one of its task scheduling services to launch your application according to a schedule you set. There are absolutely too many ways to solve your problem to answer your question any more specifically. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:56:36 AM: Hi Green, Thanks for the answer. We are planing to have reporting database on mysql. This reporting database needs to get data from oracle database on a daily basis. It would drop the table and get the fresh data from oracle database. So can this be done using database link. Regards anandkl On 9/15/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:33:16 AM: Hi, How can i connect from MYSQL db to an oracle database. Do we need to create any database link or use some ODBC. Please help regards anandkl The only way to get one MySQL database server to directly manage data in a table that resides within another server is through the Federated storage engine. Federated is a new feature coming with v5. 0 and is similar to MSSQL's linked table feature. Currently it only supports MySQL to MySQL table interactions. When you say connect from MYSQL db to an oracle database what exactly are you trying to accomplish? What are you trying to do? What problem are you having that you think the connection would solve? Please give us some additional details so we can answer your question more thoroughly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Differences Between ORACLE SQL PLus and MYSQL SQL
All, Could you please tell us what are the differences between ORACLE SQL plus and MYSQL SQL? Thank you :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EOFException with jdbc driver
Hi Mark, thanks for your answer. See comments. Stefan, Something is closing the network connection abrubtly between JDBC and your MySQL server. Things to check are: 1) Connections don't sit longer than 'wait_timeout' on the server. I am actually using connection pooling (the torque system, in case you know this). After I get this error, my app is still running fine, and also doing dtabase queries. Something interesting: When I do a query ... where x like %y% the error occurs. Doing where x regexp y works fine, even if I do the regular expression query after the like one, without restarting server or similar things. 2) If the mysql server is on a different machine than your application: Same machine 2a) There are no network hardware issues 2b) There isn't a firewall between the server and your application that times-out connections 3) MySQL isn't crashing in between queries (see your mysql server error log) Nothing in the log -Mark -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with grant
HI, I have granted all privileges to a user to connect from any host like below grant all on *.* to [EMAIL PROTECTED] identified by 'password'; flush privileges; when I tried to connect to database using ip address like below on the server where database was hosted mysql -uusername -p -h127.0.0.1 I am unable to connect to the database. Please anybody explain what was the problem Thanks Anil DBA
Re: [newbie] moving mysql db to new server
2005/9/15, Julien vander Straeten [EMAIL PROTECTED]: hi all, my machine crashed and i'd would like to restore my mysql databases on my new system. i'm having a backup of the entire drive, but no dump. my system is mac os x.3 server. is there a way to dragdrop some folders to my new system? If you use MyISAM Table format, you could do that indeed. shutdown server Look for your MySQL Folder and just drag/drop the folders (the ones containing .frm, .MYD and .MYI files) in this directory (except mysql and test) into you new destination. restart server If it's InnoDB, I have no idea... thanks for your help :-) julien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database link
Yes, you can use the same process. You will need to modify your existing code so that you get the data from Oracle and write it to MySQL. You will be able to reuse most, but not all, of your existing script. Those portions that connect to your destination server and write data into the database will need to be modified to use the proper MySQL equivalents. Also, please remember to CC: all responses to the entire list. If I had become unavailable due to the demands of my normal job (all but two or three active members of the list do NOT work for MySQL, we all have regular, full-time jobs) someone else could have responded in my place. It also makes any information you gain or provide useful for others as it will be in the public realm. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ananda Kumar [EMAIL PROTECTED] 09/15/2005 11:17 AM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] [EMAIL PROTECTED] cc Subject Re: database link Hi Green, Right now we are doing the same way. The current reporting db is on oracle, so we use a cronjob and the scripts connects to main db using database link and fetches data into the reporting database. So i was wondering if i can do the same using mysql and connect to oracle db. Regards anandkl On 9/15/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: No. You will need to use an application external to the server to perform the data collection process. This application can be one you write yourselves or something you purchase. There are many ways to perform things on a schedule: a) you application continuously runs and performs certain actions according to an internal timer b) the operating system uses one of its task scheduling services to launch your application according to a schedule you set. There are absolutely too many ways to solve your problem to answer your question any more specifically. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:56:36 AM: Hi Green, Thanks for the answer. We are planing to have reporting database on mysql. This reporting database needs to get data from oracle database on a daily basis. It would drop the table and get the fresh data from oracle database. So can this be done using database link. Regards anandkl On 9/15/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:33:16 AM: Hi, How can i connect from MYSQL db to an oracle database. Do we need to create any database link or use some ODBC. Please help regards anandkl The only way to get one MySQL database server to directly manage data in a table that resides within another server is through the Federated storage engine. Federated is a new feature coming with v5. 0 and is similar to MSSQL's linked table feature. Currently it only supports MySQL to MySQL table interactions. When you say connect from MYSQL db to an oracle database what exactly are you trying to accomplish? What are you trying to do? What problem are you having that you think the connection would solve? Please give us some additional details so we can answer your question more thoroughly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: [newbie] moving mysql db to new server
Hello. i'm having a backup of the entire drive, but no dump. In what way have you done the backup of the drive? Do you have an archive with files from the corrupted drive or a raw image (snapshot) of the file system? If you have an archive you can just copy the data directory to the new system. However, both old and new systems and versions of MySQL should be compatible. For more details see: http://dev.mysql.com/doc/mysql/en/Moving.html http://dev.mysql.com/doc/mysql/en/upgrading-to-arch.html If you have a snapshot, extract the MySQL data directory from the image and do as it was described before. Julien vander Straeten [EMAIL PROTECTED] wrote: hi all, my machine crashed and i'd would like to restore my mysql databases on my new system. i'm having a backup of the entire drive, but no dump. my system is mac os x.3 server. is there a way to dragdrop some folders to my new system? thanks for your help :-) julien -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: places index in separate DISK.
Hello. May be DATA DIRECTORY, INDEX DIRECTORY table options will be helpful for you. See: http://dev.mysql.com/doc/mysql/en/create-table.html Ananda Kumar [EMAIL PROTECTED] wrote: i Friends, I am new starter in MYSQL, but have worked extensively in oracle. I wanted to know how we can place indexes of tables on different disk in MYSQL. Generally in oracle we have the option of specifying the tablespace on different disk. Can we do the same in mysql to disk contention. I am extermelly sorry for making comparison, please forgive me. I am using Linux and MYSQL version 4.1 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
certified binaries
Does anyone know if there are certified binaries for 4.0.x ? I think I read somewhere that they will have it for 4.1 and later for 5 only. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUERY CACHE TUNING QUESTION
Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 1226682 13560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes850128 8096499 Qcache_not_cached 2725692 16361318 Qcache_free_blocks 65 12 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/(Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA
QUERY CACHE TUNING QUESTION
_ From: Anil [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 10:36 PM To: 'mysql@lists.mysql.com' Subject: QUERY CACHE TUNING QUESTION Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/(Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/(Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA
Re: confirm unsubscribe to mysql@lists.mysql.com
On Thursday 15 September 2005 10:02, [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please click on the following link: http://lists.mysql.com/u/mysql/4329a93daa95db74/jeff.nensha=gmail.com This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. We haven't checked whether your address is currently on the mailing list. To see what address you used to subscribe, look at the messages you are receiving from the mailing list. Each message has your address hidden inside its return path; for example, [EMAIL PROTECTED] receives messages with return path: mysql-return-number[EMAIL PROTECTED] --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: For help and a description of available commands, send a message to: [EMAIL PROTECTED] To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 17020 invoked by uid 509); 15 Sep 2005 17:02:53 - Received-SPF: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 64.233.162.192 as permitted sender) Received: from zproxy.gmail.com (HELO zproxy.gmail.com) (64.233.162.192) by lists.mysql.com (qpsmtpd/0.29) with ESMTP; Thu, 15 Sep 2005 19:02:52 +0200 Received: by zproxy.gmail.com with SMTP id 18so294926nzp for [EMAIL PROTECTED]; Thu, 15 Sep 2005 10:03:37 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:reply-to:organization:to:date:user-agent:mime-version:content-ty pe:content-transfer-encoding:content-disposition:message-id:from; b=HSafd/KKV2nFxEZQ+Wu4wzSAvllc5NqIFzkxv3vhbUZvTeK/ozkETa9vo0v8V9jBcRHKVgfl3 uTBlmohcK4w4hBHoUPqZYi988i35yGFZwgY7PcbE/OS1fCHz91XcAQhs8Io1TsAbQWKhYMXGjEEn 9osp/BThrmaNnyccwvQczU= Received: by 10.36.247.7 with SMTP id u7mr3488418nzh; Thu, 15 Sep 2005 10:03:37 -0700 (PDT) Return-Path: [EMAIL PROTECTED] Received: from ?192.168.1.47? ( [71.102.133.117]) by mx.gmail.com with ESMTP id j7sm287121nzd.2005.09.15.10.03.36; Thu, 15 Sep 2005 10:03:37 -0700 (PDT) Reply-To: [EMAIL PROTECTED] Organization: Nensha To: [EMAIL PROTECTED] Date: Thu, 15 Sep 2005 10:03:07 -0700 User-Agent: KMail/1.8 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: [EMAIL PROTECTED] From: Jeff Shapiro [EMAIL PROTECTED] -- Q: Why did the chicken cross the road? A: He was giving it last rites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spreading the disk load
Symlinking works fine but keep in mind an important gotcha: if you ever do a table rebuild, mysql completely ignores your symlink and overwrites it with the file (which may even fill up your disk). Have you noticed these table options (from http://dev.mysql.com/doc/mysql/en/create-table.html)? | DATA DIRECTORY = 'absolute path to directory' | INDEX DIRECTORY = 'absolute path to directory' Having the data and index files on seperate drives can certainly speed up IO. I know these options work for MyISAM, but am not sure about InnoDB. Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 14 Sep 2005, Chris Kantarjiev wrote: I'd like to spread the disk arm load across multiple drives. At the moment, we mostly use MyISAM tables, but we are also experimenting with InnoDB. What's the 'best practice' for doing this? There's no obvious configuration that lets me designate one directory for index and another for data - am I meant to do this with symlinks? How can I do anything like that with InnoDB, which appears to put everything in one massive file? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many Mysql processes at startup after upgrade to 4.1.14
Hi All, Last night I upgraded our Master server to 4.1.14 standard log from 4.0.17, I upgraded the slave a week ago and the replication was working perfectly, once I upgraded and started mysql, it started fine without any errors but the server was steadily creating mysql processes at the rate of like 20/sec until it reached the Max_connections limit set at 800, while it was doing this it was serving the web clients only randomly , also the load was very very low neither was it taking up any memory, it was just creating processes , I remember seeing this behaviour even earlier with 4.0.17 when the server just started but after a certain point the no of processes return to normal. So I had to switch back to 4.0.x, and it works perfectly fine, Also I did not dump the databases just zipped the old datadir and unzipped it into the new datadir, anybody else experience this behaviour at startup( I am aware that once the server is back online it is flooded with requests), the OS is Redhat 7.3, the server currently does 200-400 qps quiet comfortably . Any help would be appreciated, let me know if you need more info Kishore Jalleda
Re: spanned indexes
Eli wrote: Gleb Paharenko wrote: You may want to think about the MERGE storage ENGINE, however it doesn't have a real spanned index. See: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html MERGE is not the case here, since it is JOIN of tables which are different in defintion. MERGE table would be helpful if UNION on same-defined tables was used.. It seems that the solution for this (as for now) is to combine all the tables into one big table and index fields over it. -thanks, Eli How would an index on columns in two different tables help you with a join of those two tables? Perhaps if you told us what you are really trying to do, someone could suggest a solution. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user and hostname connection priveleges
James wrote: I had set up the following I have a MYSQL server running on let's say 123.xx.xx.xx User: user1 Host: 111.xx.xx.xx and gave that user the appropriate priveleges. How exactly? Did you use GRANT? Something like GRANT ALL ON test.* TO [EMAIL PROTECTED] IDENTIFIED BY 'user1_pass'; or something else? You can verify a user's privileges with SHOW GRANTS. For example, SHOW GRANTS FOR [EMAIL PROTECTED]; So. I tried to connect to the MYSQL server from 111.xx.xx.xx using user1, but it won't allow me to. How? Did you use the mysql client? What, precisely, was your command line? What does it won't allow me to mean? Did you get an error message? If so, what was it, exactly? I then had to add this entry: User: user1 Host: % One possibilty, then, is that mysql saw the connection as coming from a machine other than 111.xx.xx.xx, so the wild card worked where the specific IP did not. This should show up in the connection error message. Now I'm allowed to access the MYSQL server. Doesn't % open it up to all clients wanting to connect? I want to restrict which machine can connect to the server. How do I do that? Yes, [EMAIL PROTECTED] means user1 is allowed to connect from anywhere, so long as he/she supplies the correct password. Usually, you don't want that. If a wildcard is needed, you still try to restrict access as much as possible. For example, '[EMAIL PROTECTED]'. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with grant
Anil wrote: HI, I have granted all privileges to a user to connect from any host like below grant all on *.* to [EMAIL PROTECTED] identified by 'password'; flush privileges; Verify this worked with SHOW GRANTS FOR [EMAIL PROTECTED]; By the way, giving all privileges to a user who can connect from any machine in the world is probably not a good idea. when I tried to connect to database using ip address like below on the server where database was hosted mysql -uusername -p -h127.0.0.1 I am unable to connect to the database. Please anybody explain what was the problem We cannot explain, because you have not told us what went wrong. Please provide the exact text of the error message you received (copy/paste). Thanks Anil DBA Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with grant
Michael, Just I want to know if we grant privileges with '%' wild card character will it accept connection from 127.0.0.1. when I tried it is showing connection request is coming from [EMAIL PROTECTED] and connection is not establishing . to my knowledge '%' means it shouls accept connection from any host . please clarify Thanks Anil DBA -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 11:22 PM To: Anil Cc: mysql@lists.mysql.com Subject: Re: problem with grant Anil wrote: HI, I have granted all privileges to a user to connect from any host like below grant all on *.* to [EMAIL PROTECTED] identified by 'password'; flush privileges; Verify this worked with SHOW GRANTS FOR [EMAIL PROTECTED]; By the way, giving all privileges to a user who can connect from any machine in the world is probably not a good idea. when I tried to connect to database using ip address like below on the server where database was hosted mysql -uusername -p -h127.0.0.1 I am unable to connect to the database. Please anybody explain what was the problem We cannot explain, because you have not told us what went wrong. Please provide the exact text of the error message you received (copy/paste). Thanks Anil DBA Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY CACHE TUNING QUESTION
You tripled the amount of memory assigned to the cache, but your Qcache_lowmem_prunes value went up 10x and Qcache_not_cached went up 6x. I would say that you have enough queries that are unique, that most of your queries are getting cached then flushed before the same query comes up again. The number of queries being cached went up 13x, so you are caching a lot more queries, but it's not doing all that much good. You're getting diminishing returns as you increase memory. The query cache works best when you have the same exact queries being run over and over. You know your code, so you need to think how many unique queries you have. Perhaps you're getting close to the number of unique queries you run and increasing your cache just a bit more will suddenly send your hit rate way up, perhaps not. Test in big increments. If you suddenly get a big boost in hit ratio, work your way back down. But it may be that the query cache just can't be used optimally with your system. On Sep 15, 2005, at 1:10 PM, Anil wrote: _ From: Anil [mailto:[EMAIL PROTECTED] Sent: Thursday, September 15, 2005 10:36 PM To: 'mysql@lists.mysql.com' Subject: QUERY CACHE TUNING QUESTION Hi list, We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 gb RAM. We tried to fine tune query cache . please find below the analysis PARAMETER old modified == == query_cache_size 10 MB 32 MB Qcache_queries_in_cache 187 2444 Qcache_inserts 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ (Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ (Qcache_inserts +Qcache_not_cached+Qcache_hits)) * 100 As per the above analysis we observed that qcache hit rate came down after increasing query_cache_size from 10 MB to 32 MB and qcache_hits got increased but the remaining parameters Qcache_inserts, Qcache_not_cached, Qcache_lowmem_prunes also increased because of that hit rate came down drastically. We didn't understand the reason behind hit . Is there any recommended procedure to fine tune query cache . awaiting early reply from list. Thanks in advance Anil DBA -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: certified binaries
Hi! Mayuran Yogarajah wrote: Does anyone know if there are certified binaries for 4.0.x ? I think I read somewhere that they will have it for 4.1 and later for 5 only. I do not know where you read it, but to the best of my current knowledge this is correct. I do not expect them ever to be done for 4.0. Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: determing how loaded my mysql server is
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We have a production server that may be getting overworked, but I don't know. When I look at 'mysqladmin status' it consistently shows 107.# queries/sec on average. That seems a bit high, it should be less, I imagine, since the Id number in the processlist seems to increment by perhaps 5-10/second. Is there a way to monitor the server, without putting too much of a load on the mysql server, to see what is going on? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDKdNoikQgpVn8xrARAnLrAJ9ZxPNHUskSQVTxn/JRgm6RCkjHlgCdFGj6 d9PUim7Yo713+GbcbZZ30Z0= =AQZl -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with grant
Anil wrote: Michael, Just I want to know if we grant privileges with '%' wild card character will it accept connection from 127.0.0.1. when I tried it is showing connection request is coming from [EMAIL PROTECTED] and connection is not establishing . to my knowledge '%' means it should accept connection from any host . please clarify Thanks Anil DBA Anil, To get meaningful help, you must provide meaningful information. Descriptions of your understanding of an error message are a poor substitute for the actual error message itself. If you want a definitive answer, you need to supply the exact text of the error message you received (as I asked in my previous message). That said, I'll go out on a limb and take a guess. '%' does mean any host, but '%' is the least specific of any possible host sppecification. You need to read the description of the authentication process in the manual http://dev.mysql.com/doc/mysql/en/connection-access.html. Entries in the user table are sorted from most specific to least, with host taking precedence over user. You are attempting to connect as [EMAIL PROTECTED], but 127.0.0.1 is localhost. Here are some possible users which would match, in descending order of specificity: [EMAIL PROTECTED] ''@localhost [EMAIL PROTECTED] [EMAIL PROTECTED] My guess is you have a more specific entry than [EMAIL PROTECTED] that is a match. We would know for sure if you included the error message. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions are not enable
Hello. See: http://dev.mysql.com/doc/mysql/en/transaction.html liofr [EMAIL PROTECTED] wrote: Hi i use a sofware to connect to mysql and it can connect with succes but wanted to share acces to mysql to many poeple and it popup [MUSQL][ODBC 3.51 Driver] Transaction are not enable Is ot a mysql server message or not ( my application ) thank's -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding auto increment value in the dump file
Hello. As far as I see mysqldump doesn't support this feature. Here is the thread where people discuss this issue, may be it will be interesting for you. See: http://lists.mysql.com/mysql/79914 Jacques Brignon [EMAIL PROTECTED] wrote: I have not identified which options to use to have the current value of the AUTO_INCREMNT included in the dump file. Is this possible? -- Jacques Brignon -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user and hostname connection priveleges
Hello. If you want to have a complete understanding of how MySQL access privilege system works, read this chapter of the manual: http://dev.mysql.com/doc/mysql/en/privilege-system.html James [EMAIL PROTECTED] wrote: I had set up the following I have a MYSQL server running on let's say 123.xx.xx.xx User: user1 Host: 111.xx.xx.xx and gave that user the appropriate priveleges. So. I tried to connect to the MYSQL server from 111.xx.xx.xx using user1, but it won't allow my to. I then had to add this entry: User: user1 Host: % Now I'm allowed to access the MYSQL server. Doesn't % open it up to all clients wanting to connect? I want to restrict which machine can connect to the server. How do I do that? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
converting access (.mdb) files...
Anyone know a way to convert MS Access DB files (.mdb) into a format MySQL can import? I'm on a Mac hence I don't have Access. Best, CH ___ Craig Hoffman www.eclimb.net [EMAIL PROTECTED] iChat / AIM: m0untaind0g ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting access (.mdb) files...
There used to be a program called AccessDump that will dump access tables into .sql files. Their website seems to be unresponsive at the moment, though. Here's a link: www.AccessDump.com They are from the south, so it's possible that the site was affected by the hurricane. Jenifer - Original Message - From: Craig Hoffman [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Thursday, September 15, 2005 4:52 PM Subject: converting access (.mdb) files... Anyone know a way to convert MS Access DB files (.mdb) into a format MySQL can import? I'm on a Mac hence I don't have Access. Best, CH ___ Craig Hoffman www.eclimb.net [EMAIL PROTECTED] iChat / AIM: m0untaind0g ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Myisam or innodb
For high volume discussion board type work InnoDB is faster. Our slow query log droped 66% just by switching engine types. Earlier comments about innoDB not supporting full text are actually supposed to read InnoDB doesn't support full text indexes... This is only important if you really need a full text index. If you want transactions, ie a series of queries tied together and all executed in sequence or all rolled back if there's an issue, then you must choose InnoDB. There are some areas such as Data Warehousing that benefit from MyISAM, and there are some that benefit from InnoDB.. for the majority though there's no clear choice, and I understand that most people who have tried direct comparisons end up choosing InnoDB based on performance. If you don't have a clear reason for using one over the other, compare both with appropriate memory settings and see how you go. In a hosting environment you either need to use 4.1 or higher and innodb_file_per_table to make sure each table get's it's own file... or you need to make sure you allocate a LOT of disk space to the shared InnoDB table space to make sure you don't run out. As to a setting to change the default... the book says: If you omit the ENGINE or TYPE option, the default storage engine is used. Normally this is MyISAM, but you can change it by using the -- default-storage-engine or --default-table-type server startup option, or by setting the storage_engine or table_type system variable. which in plain language means adda line like this to your my.cnf file: default-storage-engine=innodb (or myisam or whatever you want it to be) Users could then still create a table of a different type, but unless they specify something it will be whatever you set to be the default. Also be aware that memory settings behave differently for different storage engines. InnoDB needs it's own memory... MyISAM doesn't use InnoDB's memory... and so on. If you set your default to be InnoDB but a user still sets up something in MyISAM then you need to make sure there is some memory for MyISAM. Final note.. MySQL needs the mysql database to exists and for the tables it cares about to be MyISAM... these hold your user permissions and so on... don't try converting this to InnoDB, that would be bad. Best Regards, Bruce On Sep 15, 2005, at 2:31 AM, Scott Haneda wrote: I have moved my data from mysql 3 to 4, across various updates, over the past few years. I use phpmyadmin generally, and it defaults to making tables myisam. I can not seem to find a really clear answer as to why I want to use one over the other. Generally, I use myisam and change it only when I need to do a rollback or something like that. Since I seem to be able to toggle from myisam to innodb without any adverse affects, why would one chose one over the other? Is there a way to prevent the use of myisam if it is old and slowly being deprecated? I am a host and do not control the table structure of clients databases. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting access (.mdb) files...
Craig Hoffman wrote: Anyone know a way to convert MS Access DB files (.mdb) into a format MySQL can import? I'm on a Mac hence I don't have Access. mdbtools: http://mdbtools.sourceforge.net/ -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with symbolic link
Hi all Problem description OS: Solaris 9 Mysql: 4.1.8 Periodically I get error message can't get stat of xxx.MYD for tables that are symbolic link to tables on different database on the same file system. Error disappear after executing flush tables. If someone solve this problem let me know please. Thanks for any help -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with symbolic link
Vlad Shalnev wrote: Periodically I get error message can't get stat of xxx.MYD for tables that are symbolic link to tables on different database on the same file system. Error disappear after executing flush tables. Why are you doing this? You can access tables in other databases in your SQL queries so what is the point of making symbolic links to tables in other databases? Is it even supported? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
