RE: utf8 support
Any hint where and how to get mysql 4.1.1? Thanks John -Original Message- From: Jon Haugsand [mailto:[EMAIL PROTECTED] Sent: Monday, August 25, 2003 3:14 AM To: '[EMAIL PROTECTED]' Subject: Re: utf8 support * John Li MySql 4.1 should have utf8 charset support, I downloaded the alpha version but don't seem to have the utf8.xml file comes with it. Any one know how to get the needed charset support files? It looks like a bug. Try version 4.1.1. See e.g.: http://www.pantek.com/library/general/lists/lists.mysql.com/mysql/msg00565. html -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about row level locking with InnoDB tables
Hello, I am relatively new to MySql (4.0.14) but I have read through the relevent documentation and am still confused about how row level locking behaves with InnoDB tables. I created a database with a single innodb table which has 2 columns, one of which is indexed. The locking behavior I see when I test against this database is that it uses row level locks if the SELECT ... FOR UPDATE involves the indexed column, but uses table level locks if instead it involves the non-indexed column. For example, if I have 2 mysql clients that perform the following operations: client1 set autocommit=0; client1 begin; client1 SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE; client2 set autocommit=0; client2 begin: client2 SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE; The above query by client2 will block if the column in question is not indexed, implying that client1 has somehow locked the entire table, even though client1 and client2 are selecting different rows. Am I misconfiguring something, or does InnoDB simply only support row level locking when you are selecting indexed rows? Thanks in advance, Steve McWilliams Software Engineer Emprisa Networks 703-691-0433x21 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explain syntax
Hi list: I have the following partial result from an explain select: | table| type | possible_keys | key | key_len | ref | rows | Extra | +--++--+--+-+-+--+-+ | OrdenServicio| ref| XIF923OrdenServicio,XIF117OrdenServicio,XIF910OrdenServicio,XIF172OrdenServicio,IDX5979OrdenServicio,IDX5984OrdenServicio,IDX5985OrdenServicio,IDX0010OrdenServicio,IDX0111OrdenServicio | IDX5979OrdenServicio | 4 | const | 1058 | where used; Using temporary || Tienda | ref| PRIMARY | PRIMARY | 4 | OrdenServicio.idTienda |1 | | | TipoGarantia | ALL| PRIMARY | NULL |NULL | NULL |2 | where used | | Pedido | eq_ref | PRIMARY,IDX6868,XIF134Pedido,XIF135Pedido | PRIMARY | 12 | OrdenServicio.idPedido,OrdenServicio.idCanal,OrdenServicio.idTienda |1 | | .-- And I would like to remove the Using temporary label, and this is my query: Select distinct OrdenServicio.folioOrdenServicio, OrdenServicio.idCanal, OrdenServicio.idTienda, OrdenServicio.idTaller, OrdenServicio.idCentroServicio, OrdenServicio.idPedido, OrdenServicio.idProducto, OrdenServicio.fallaCliente, OrdenServicio.idEntregaControl, OrdenServicio.numSerie, OrdenServicio.estaComponenteBase, OrdenServicio.observaciones, OrdenServicio.idStatusOrden, OrdenServicio.idCanalLevantamiento, OrdenServicio.idTiendaLevantamiento, OrdenServicio.idCentroServicioLevantamiento, OrdenServicio.idTallerLevantamiento, OrdenServicio.idTipoGarantia, OrdenServicio.idLinea, OrdenServicio.esReparacionInterna, OrdenServicio.otroAccesorio, OrdenServicio.idFalla, OrdenServicio.fechaLevantamiento, Taller.nombre Taller_nombre, Tienda.nombre Tienda_nombre, Producto.nombre Producto_nombre, Marca.idMarca, Marca.nombre Marca_nombre, Pedido.fechaSurtimiento, ProductoComprado.idStatusGarantia, ProductoComprado.garantiaExtendida, ProductoComprado.numPoliza, Cliente.idCliente, Cliente.nombre Cliente_nombre,Cliente.apellidoP, Cliente.apellidoM, TipoGarantia.nombre TipoGarantia_nombre, CentroServicio.nombre CentroServicio_nombre, StatusGarantia.nombre StatusGarantia_nombre from OrdenServicio, Tienda, Producto, Marca, Pedido, Cliente, TipoGarantia, CentroServicio, Taller, ProductoComprado, StatusGarantia where OrdenServicio.idTipoGarantia = TipoGarantia.idTipoGarantia and OrdenServicio.idTienda = Tienda.idTienda and OrdenServicio.idProducto = Producto.idProducto and OrdenServicio.idPedido = Pedido.idPedido and OrdenServicio.idTienda = Pedido.idTienda and OrdenServicio.idCanal = Pedido.idCanal and OrdenServicio.idProducto = ProductoComprado.idProducto and OrdenServicio.idPedido = ProductoComprado.idPedido and OrdenServicio.idCanal = ProductoComprado.idCanal and OrdenServicio.idTienda = ProductoComprado.idTienda and ProductoComprado.idProducto = Producto.idProducto and Producto.idMarca = Marca.idMarca and ProductoComprado.idStatusGarantia = StatusGarantia.idStatusGarantia and Cliente.idCliente = Pedido.idCliente and Cliente.idTienda = Pedido.idTienda and Cliente.idCanal = Pedido.idCanal and OrdenServicio.idTaller = Taller.idTaller and OrdenServicio.idCentroServicio = CentroServicio.idCentroServicio and (OrdenServicio.idStatusOrden = 13) and OrdenServicio.idLinea = 2 group by OrdenServicio.folioOrdenServicio limit 0,10; Any suggestions to remove the Using temporary label in the explain results. Thnx in advanced Best Regards. Mikel
If the mysql crash,how can I trace the log
Hello, mysql, Yesterday,mysql process disappear sliently. After I check the err-log,no clues found. But the PID file still exising.I think the mysql terminate by some reason.But I still not found why. The error-log mysqld.log wouldn't provide any valueable msg.It just contains the shutdown start up log. Any idea? Best regards. MaFai [EMAIL PROTECTED] 2003-08-27 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Query taking WAY TO LONG...HELP PLEASE!!!
All,
I have a query that is inner joined with another table based on country codes
Select distinct Name.* from Name inner join Location on Location.key =
Name.key and Location.cc in ('list of countries here');
The problem is when I have more than 2 country codes, the query takes forever...
When I show processlist, it says converting HEAP to MyISAM ( this takes over an hour
)...
I've tried bumping up max_heap_table_size to 128M and tmp_table_size to 128M, but that
only
seems to delay the converting HEAP to MyISAM message
Name contains about 3 million records and Location contains about 1.5 million records.
Using MySQL 4.0.14-pro
Thanks,
Cory Twibell
Lockheed Martin Space Systems Company
Electronic Combat Development Systems
[EMAIL PROTECTED]
(303) 971-3184
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
* Twibell, Cory L
I have a query that is inner joined with another table based on
country codes
Select distinct Name.* from Name inner join Location on Location.key =
Name.key and Location.cc in ('list of countries here');
The problem is when I have more than 2 country codes, the query
takes forever...
[...]
Please show the output of the following commands:
SHOW CREATE TABLE Name;
SHOW CREATE TABLE Location;
EXPLAIN Select distinct Name.*
from Name inner join Location on
Location.key = Name.key and
Location.cc in ('list of countries here');
You probably need to define some indexes:
URL: http://www.mysql.com/doc/en/MySQL_indexes.html
URL: http://www.mysql.com/doc/en/CREATE_INDEX.html
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Explanation of multiple-column indexes
Hi Jesse, - Original Message - From: Jesse Sheidlower Sent: Monday, August 25, 2003 8:26 AM Subject: Explanation of multiple-column indexes After some discussion in a separate thread, I've been trying to get a better understanding of the workings of multiple-column indexes, and think I'm still missing the point. I understand indexing (last_name, first_name) in that order if you'd always be searching last names and only be searching first names in combination. Correct. What I'm trying to understand is how you would set up these indexes when you'd always be doing joins with another table. Suppose you have The Canonical CD Database, and you have a table songs with fields song_id, album_id, song_title, and song_length. Suppose you're often doing searches of song_title or (for some reason) song_length, and that any time you'd do such a search, you'd _always_ be joining it to the album table. It would seem that you'd want at least two multiple-indexes in the song table, one of them including song_title and album_id, the other including song_length and album_id. Is this correct? Do you need song_id (which would be a primary key on that table) in there too? What order should the indexes be in? You wouldn't necessarily want indexes on (song_title, album_id) -- in that order -- and/or (song_length, album_id). This reason for this is because if any other columns from the song table are involved in the query (in the select list or in the WHERE), MySQL will have to hit the data file for those columns anyway, and album_id as the second column in the index won't be used -- just the first column -- song_title or song_length -- if you're searching on them. However, if only the 2 columns in the index are involved in the query (searching on title or length and join with album_id), then having album_id in the index would be benficial because no seek to the data file is needed. This can be verified by seeing if EXPLAIN says Using index for the song table. Note: One of the exceptions where it wouldn't say Using index and would have to consult the data file is if song_title, for example, has only a prefix index on the first n characters (e.g. created with KEY (song_title(10), album_id), instead of KEY (song_title, album_id), which indexes the FULL column). And no, including song_id in one of these indexes wouldn't be of any use. If every search for song_title or song_length must be joined against the album table, it's not clear which should be the first named column in this index. The experiments I've done so far have been inconclusive, and I don't think I'm understanding the process in the first place. The indexes would be: (song_title, album_id) (song_length, album_id) If the order was reversed (e.g. album_id was first in the index), the index couldn't be used for searching. And like I said above, if other columns besides the 2 in the index are involved in the query, album_id isn't used anyway. In that case, just index title and length separately for searching: (song_title) (song_length) Of course, if you included ALL columns that will be used in queries in each index (with title or length as the first column in each), then it wouldn't have to go to the data file and EXPLAIN would say Using index. But this doesn't usually give much speed improvement and is a waste of space. Just letting you know. :-) Thanks very much. Jesse Sheidlower You're welcome. Hope that helped. If you have any more questions, ask away. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.14 install problem: mysqld crashes
Description: I've recently compiled mysql 4.0.14 on redhat 8.0. The installation fails, however, when running mysql_install_db. I've attached a transcript below. I gather from searching the 'net that this isn't an entirely unusual thing to happen, and is likely the result of some misconfiguration on my part, or perhaps a version incompatability with my libc. Unfortunately, I haven't seen any concrete suggestions on how to get past it. I've studied the installation instructions pretty closely, experimented with various compilation flags. I just keep getting the same results. This should be a pretty vanilla installation. Can someone please point me in the direction of figuring out what I'm doing wrong? How-To-Repeat: Geez ... how *not* to repeat it :) Fix: dunno Submitter-Id: [EMAIL PROTECTED] Originator:Gary Organization: personal use MySQL support: email support Synopsis: 4.0.14 install problems; mysqld crashes Severity: minor Priority: low Category: mysql Class: sw-bug Release: mysql-4.0.14 (Source distribution) C compiler:gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) C++ compiler: gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Environment: machine, os, target, libraries (multiple lines) System: Linux xeon 2.4.20-19.7 #2 Wed Aug 20 14:21:18 CDT 2003 i686 i686 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-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redha t-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O3 ' CXX='gcc' CXXFLAGS='-O3 -fno-exceptions -fno-rtti -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Aug 21 17:58 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-mysqld-ldflags=-all-static' 'CFLAGS=-O3 ' 'CXXFLAGS=-O3 -fno-exceptions -fno-rtti -felide-constructors' 'CXX=gcc' $ ./mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x83cc2d8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf5fea68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8089a26 0x82402f8 0x8251796 0x809a0e5 0x809ce2f 0x8099f04 0x8094718 0x823b74d 0x82720ba ./mysql_install_db: line 1: 31298 Segmentation fault /usr/local/mysql/libexec/mysqld --bootstrap --skip-grant-tables --basedir=/u sr/local/mysql --datadir=/usr/local/mysql/var --skip-innodb --skip-bdb Installation of grant tables failed! Examine the logs in /usr/local/mysql/var for more information. You can also try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant You can use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/mysql/bin/mysqlbug script! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
Indexing columns will help but you *may* need to take a peek at some other
factors such as Memory Management
Read Jeremy's article on MySQL Performance Tuning
http://www.linux-mag.com/2001-12/mysql_01.html
Hth,
Martin
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Twibell, Cory L [EMAIL PROTECTED]
Sent: Tuesday, August 26, 2003 7:19 PM
Subject: Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
* Twibell, Cory L
I have a query that is inner joined with another table based on
country codes
Select distinct Name.* from Name inner join Location on Location.key =
Name.key and Location.cc in ('list of countries here');
The problem is when I have more than 2 country codes, the query
takes forever...
[...]
Please show the output of the following commands:
SHOW CREATE TABLE Name;
SHOW CREATE TABLE Location;
EXPLAIN Select distinct Name.*
from Name inner join Location on
Location.key = Name.key and
Location.cc in ('list of countries here');
You probably need to define some indexes:
URL: http://www.mysql.com/doc/en/MySQL_indexes.html
URL: http://www.mysql.com/doc/en/CREATE_INDEX.html
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 64-Bit and INNODB
Ahh, so Linux on 64-bit right now with INNODB is really not much different than on 32-bit x86? XEON CPUs have AWE/PAE which lets them address a 36-bit memory address space, getting past the 4GB addressable limit. 64-bit CPUs obviously can address _much_ more memory in a single chunk. MySQL/INNODB though is still going to be limited to that same 2GB buffer size? Is that correct? Hmmm... We've talked about sponsorship of Innobase to implement PAE on XEON/x86 Linux but making it work on Opteron I think would be more appropriate. Do you want to publicly talk about costs of that implementation Heikki? How many folks here would want this and be willing to pass the hat to make it happen? A low-end 1U Opteron server including RAM and CPU are only marginally more expensive than a (good) low-end similarly-equippped XEON server. In my opinion there is no doubt that it will take off in a big way! PS. RedHat kernels definitely support PAE and that's possibly via an additional patch beyond the stock kernel? Not just in Advanced Server either, this is with the bigmem kernel on a box (standard RedHat 7.3) with 5GB RAM for instance [too bad I can only allocate a bit under 2GB for the INNODB buffer though :-) ] 10:07pm up 1 day, 1:07, 1 user, load average: 0.05, 0.07, 0.08 102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.3% user, 8.2% system, 0.0% nice, 90.4% idle CPU1 states: 1.0% user, 0.1% system, 0.0% nice, 98.3% idle Mem: 5318292K av, 4571076K used, 747216K free, 0K shrd, 261444K buff Swap: 2096220K av, 123060K used, 1973160K free 3284376K cached Kernel 2.6.0 definitely makes mention of PAE and support for large amounts of memory. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 4:08 PM To: [EMAIL PROTECTED] Subject: Re: 64-Bit and INNODB Hi! - Original Message - From: Marc Slemko [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, August 26, 2003 6:56 AM Subject: RE: 64-Bit and INNODB On Mon, 25 Aug 2003, Wendell Dingus wrote: I didn't notice a reply to this when first posted. Surely someone has stuffed a lot of memory into an Opteron or Itanium by now and knows the answer. Is a 64-bit Malloc all that is necessary or does INNODB have to specifically support more memory in some other fashion? Heikki? Thanks in advance! well, interestingly according to the innodb release notes, on windows: MySQL/InnoDB-4.1.0, April 3, 2003 * InnoDB now supports up to 64 GB of buffer pool memory in a Windows 32-bit Intel computer. This is possible because InnoDB can use the AWE extension of Windows to address memory over the 4 GB limit of a 32-bit process. A new startup variable innodb_buffer_pool_awe_mem_mb enables AWE and sets the size of the buffer pool in megabytes. not sure what it would take to make that work on linux, but if all you need is more memory, and the fairly reasonable performance hit is ok, you may be a lot better off just getting an x86 box with 8 dimm slots and loading them up with 1 or 2 gig dimms... then making AWE in mysql work on linux. The cost you pay to go the 64 bit box is pretty hefty. We are waiting to see if 64-bit Linux computers take off. Adding the 32-bit Intel AWE support into InnoDB on Linux would be rather easy if someone wants to sponsor the project. I recall AWE itself can be used with the Red Hat Linux Advanced Server, if I remember the OS name right. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- 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: InnoDB Error ib_logfile0 of different size
On Tue, 26 Aug 2003, Heikki Tuuri wrote: Asif, now take the old log files from the safe place and put them back! That will save your data. The instructions about changing the size of the log files are the following: http://www.innodb.com/ibman.html#Adding_and_removing If you want to change the number or the size of your InnoDB log files, you have to shut down MySQL and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log files from the log file directory, edit my.cnf, and start MySQL again. InnoDB will tell you at the startup that it is creating new log files. That worked, thanks a lot. Now I am wondering if their is any other modification on my system with 4x450Mhz, 4gig RAM, mysql 4.0.13 that I should look into to improve the performance Here is my cnf file look like [client] #password = your_password port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #skip-networking #log-bin server-id = 1 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates Also not sure what I should do here, keep the comments? [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout I am doing any replication. I have just one server. I am using MyISAM and InnoDB Type tables and Row formats were Dynamic Thanks again -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64-Bit and INNODB
Andi, - Original Message - From: Andi Kleen [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 1:37 AM Subject: Re: 64-Bit and INNODB Heikki Tuuri [EMAIL PROTECTED] writes: Adding the 32-bit Intel AWE support into InnoDB on Linux would be rather easy if someone wants to sponsor the project. I recall AWE itself can be used with the Red Hat Linux Advanced Server, if I remember the OS name right. It can be used with any 2.4 Linux kernel. All you need to do is to create a big file in tmpfs and mmap64() windows out of it. No fancy acronyms needed. good. Thank you for the information! The solution I was thinking of was attaching areas of shared memory to the mysqld process. -Andi Best regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Replication Problem (XP+mysql4.0.14)
nobody have answer ? For me it is very urgent to kwow when the .15 version will be avalaible . Tks - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: I.P. [EMAIL PROTECTED] Sent: Friday, August 22, 2003 1:16 PM Subject: Re: Replication Problem (XP+mysql4.0.14) I have your same problem in my network where the master i 3.23.52 and then slave are 4.0.14; before, in all 4.0.13, it was ok, without this bug. It will be solved in 4.0.15 ? Best regards Massimo - Original Message - From: I.P. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 22, 2003 11:56 AM Subject: Replication Problem (XP+mysql4.0.14) Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my configuration: ### FOR MASTER # # This will be passed to all mysql clients [client] #password=my_password port=3300 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4/ datadir = c:/mysql4/data/ port=3300 language=polish default-character-set=latin2 log-bin server-id=1 log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\master\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\master\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\master\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [mysqlhotcopy] interactive-timeout ### ###FOR SLAVE # # This will be passed to all mysql clients [client] #password=my_password port=3301 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4_slave/ datadir = c:/mysql4_slave/data/ port=3301 language=polish default-character-set=latin2 server-id=2 # log-bin # log-slave-updates master-host=127.0.0.1 master-user=irek master-password=XX master-port=3300 master-connect-retry=30 # log-update=log_updates.log log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\slave\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\slave\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\slave\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M ## END configuration So i have noticed this things. I start replication with master and slave: 1) Master is running ... --- Slave is running ... and at console can i see: 030804 22:55:36 InnoDB: Started 030804 22:55:36 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3300', r eplication started in log 'FIRST' at position 4 030804 22:55:36 Slave SQL thread initialized, starting replication in log 'FIRS T' at position 0, relay log '.\hq-relay-bin.001' position: 4
Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
On Tue, Aug 26, 2003 at 07:38:04PM -0600, Twibell, Cory L wrote:
All,
I have a query that is inner joined with another table based on
country codes Select distinct Name.* from Name inner join Location
on Location.key = Name.key and Location.cc in ('list of countries
here');
The problem is when I have more than 2 country codes, the query
takes forever... When I show processlist, it says converting HEAP
to MyISAM ( this takes over an hour )... I've tried bumping up
max_heap_table_size to 128M and tmp_table_size to 128M, but that
only seems to delay the converting HEAP to MyISAM message
Name contains about 3 million records and Location contains about 1.5 million
records.
The table is a HEAP table?
I don't think the optimizer knows how to make an IN(...) query in a
HEAP table fast. Have you tried this with a MyISAM table? It should
be pretty fast, assuming you're pulling back less than 1/4th of the
rows with that query.
Can you show us the EXPLAIN output from the query?
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.13: up 25 days, processed 1,141,718,690 queries (517/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64-Bit and INNODB
Wendell, - Original Message - From: Wendell Dingus [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, August 27, 2003 6:35 AM Subject: RE: 64-Bit and INNODB Ahh, so Linux on 64-bit right now with INNODB is really not much different than on 32-bit x86? XEON CPUs have AWE/PAE which lets them address a 36-bit memory address space, getting past the 4GB addressable limit. 64-bit CPUs obviously can address _much_ more memory in a single chunk. MySQL/INNODB though is still going to be limited to that same 2GB buffer size? Is that correct? no. For example, people are running InnoDB on a 64-bit Sparc with 4 GB buffer pools. Hmmm... We've talked about sponsorship of Innobase to implement PAE on XEON/x86 Linux but making it work on Opteron I think would be more appropriate. Do you want to publicly talk about costs of that implementation Heikki? How many folks here would want this and be willing to pass the hat to make it happen? But InnoDB already works on 64-bit systems. Nothing to implement. There is an AMD64 binary downloadable from http://www.mysql.com/downloads/mysql-4.0.html. A low-end 1U Opteron server including RAM and CPU are only marginally more expensive than a (good) low-end similarly-equippped XEON server. In my opinion there is no doubt that it will take off in a big way! PS. RedHat kernels definitely support PAE and that's possibly via an additional patch beyond the stock kernel? Not just in Advanced Server either, this is with the bigmem kernel on a box (standard RedHat 7.3) with 5GB RAM for instance [too bad I can only allocate a bit under 2GB for the INNODB buffer though :-) ] 10:07pm up 1 day, 1:07, 1 user, load average: 0.05, 0.07, 0.08 102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.3% user, 8.2% system, 0.0% nice, 90.4% idle CPU1 states: 1.0% user, 0.1% system, 0.0% nice, 98.3% idle Mem: 5318292K av, 4571076K used, 747216K free, 0K shrd, 261444K buff Swap: 2096220K av, 123060K used, 1973160K free 3284376K cached Kernel 2.6.0 definitely makes mention of PAE and support for large amounts of memory. Andi Kleen just wrote that all 2.4.xx kernels support PAE. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 4:08 PM To: [EMAIL PROTECTED] Subject: Re: 64-Bit and INNODB Hi! - Original Message - From: Marc Slemko [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, August 26, 2003 6:56 AM Subject: RE: 64-Bit and INNODB On Mon, 25 Aug 2003, Wendell Dingus wrote: I didn't notice a reply to this when first posted. Surely someone has stuffed a lot of memory into an Opteron or Itanium by now and knows the answer. Is a 64-bit Malloc all that is necessary or does INNODB have to specifically support more memory in some other fashion? Heikki? Thanks in advance! well, interestingly according to the innodb release notes, on windows: MySQL/InnoDB-4.1.0, April 3, 2003 * InnoDB now supports up to 64 GB of buffer pool memory in a Windows 32-bit Intel computer. This is possible because InnoDB can use the AWE extension of Windows to address memory over the 4 GB limit of a 32-bit process. A new startup variable innodb_buffer_pool_awe_mem_mb enables AWE and sets the size of the buffer pool in megabytes. not sure what it would take to make that work on linux, but if all you need is more memory, and the fairly reasonable performance hit is ok, you may be a lot better off just getting an x86 box with 8 dimm slots and loading them up with 1 or 2 gig dimms... then making AWE in mysql work on linux. The cost you pay to go the 64 bit box is pretty hefty. We are waiting to see if 64-bit Linux computers take off. Adding the 32-bit Intel AWE support into InnoDB on Linux would be rather easy if someone wants to sponsor the project. I recall AWE itself can be used with the Red Hat Linux Advanced Server, if I remember the OS name right. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
On Tue, 26 Aug 2003 17:00:16 -0400 Nicola Hartland [EMAIL PROTECTED] wrote: When I do the tests on frontpage, it doesn't seem to do anything. what test are you talking about? On the Mysqladmin it tells me that my odbc driver 3,.51 not found is that a problem and how do I get the driver? you don't need ODBC driver to work in PHP with MySQL. PHP has native builtin extension for MySQL. just use mysql_*(); functions and be happy =) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: own privileges
User can use SHOW GRANTS command: Thank you! But one more question: what privileges user should have to issue this command? I've got a user who has rights only to SELECT from certain db. What should i GRANT to him to allow use of SHOW GRANTS? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_install_db static compilation problem solved
Hi, When compiling MySQL with statically linking options (with glibc 2.3.1), There weren't any errors during configure and compilation stages but mysql_install_db was giving out a segmentation fault. The list archives had suggested to give up statically linking libraries, which worked fine. It seems that this was a glibc issue. When I upgraded to glibc 2.3.2, MySQL no longer crashed while running mysql_install_db even when I compiled with-all-static-flags. Doruk -- FISEK INSTITUTE - http://ww.fisek.com.tr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication (master/slave for different servers with different databases)
Hi, I've read the replication section in the manual but i'm not quite sure about the possibilities of the replication in mysql: It's possible a server could act as master for one database with slave server A and as a slave for another database which runs on master server B? If yes: as far as i understood, you need special priviliges to control replication using sql commands such as START SLAVE, but i didn't saw a way to limit a user to control only one slave or master... is that possible? Many thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication (master/slave for different servers with different databases)
Matthias Blaser wrote: It's possible a server could act as master for one database with slave server A and as a slave for another database which runs on master server B? As in A - database1 - S - database2- B AFAIK anything S logs will be red by B. Using 'replicate-do-db=database2' B will only process changes for database2. By design S should not make changes in database1, but those red from logging at A. Obviously, 'log-slave-updates' must remain OFF at S http://www.mysql.com/doc/en/Replication_Options.html HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication (master/slave for different servers with different databases)
On Wednesday 27 August 2003 10:50, Hans van Harten wrote: Matthias Blaser wrote: It's possible a server could act as master for one database with slave server A and as a slave for another database which runs on master server B? As in A - database1 - S - database2- B Well, it should be something like this (sorry, if it was confusing): Our server X: master for database1, the slave for this db is on server A Our server X: slave for database2, the master for this db is on server B Is it possible to run both configurations on the same server (X)? (he should be master for database1, but slave for database2, both databases are on different servers). Background is, that one customer want to have his database on our server running in slave mode, replicating his own intranet-database... now we have to decide if we can run this configuration on our main server or have to install a dedicated one. If these two configurations written above are not possible on the same server, we want to run a dedicated server, cause it's not that nice and flexible to be restricted to only one replication-customer. AFAIK anything S logs will be red by B. Using 'replicate-do-db=database2' B will only process changes for database2. As far as I understood, it's not possible for B to replicate any other database if his replication user has only access to database2 regardless if 'replicate-do-db=database2' is set or not and regardless if the master server writes other databases to it's binarylog too? Many thanks, Matt mysql, sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL does not release locks
Hi I have problems with mysql (4.0.6-4.0.14b) on windows 2000 SP3. MySQL locks tables when I alter table structure or execute update queries. I know that this is the exactly what database engine is supposed to do but the engine doesn't release the locks at all. This happens frequently but not allways. Approximately every third alter/update query locks table permanently. Recently I found out that by executing FLUSH TABLES release locks and I am able to keep on working with the table. I have been useing only MyISAM table types. Table locking is annoying because there might be concurrent users useing the same table. They are not able to access the table at all or SELECT queries returns wrong number of records. I use different clients to execute queries phpMyAdmin, mysqlcc, mysql, MSAccess (ODBC). Table locking occurs independently of client used. For example phpMyAdmin sometimes shows following error message when I try to alter structure of locked table: ERROR 7: Error on rename of '.\front\industry.MYI to '.\front\#sql-a64-439.MYI' (ERROR: 13) I checked error code 13: Permission denied. But there shouldn't be permission problems because I use account having all privileges and only some of my queries cause these king of errors. I am not alone with this kind of problem because there is a lot of reports in various mailing lists. I searched from web and news groups but nobody knows how to overcome the issue. It might be that the problem is win32 specific. I would be more than thankfull if someone knows reason for table locking. Sincerely, Teemu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: My details [INF2003082300000269]
Thanks for writing to [EMAIL PROTECTED] This is an auto-generated response to let you know that your message has reached our general information box on 8/23/2003 at 5:38:55 AM. Customer Service will be processing and, if necessary, responding to your inquiry shortly. We usually reply to general inquiries within one business day and appreciate your patience. There is no need to respond to this message, but if your message was concerning a technical support issue, a sales-related question, or beta product, there is a more direct and quicker way of contacting the appropriate SoftArtisans personnel--please see below. -- SoftArtisans Technical Support, Sales, and Beta Mailboxes -- Occasionally, our general information box receives messages that are more appropriate for another mailbox. For faster service, please consider resending your message directly to one of the follwing: For free technical support issues, please send your message directly to: [EMAIL PROTECTED] For a sales consultation, quote request, or other sales-related question, please send your message directly to: [EMAIL PROTECTED] For comments, feedback, or questions regarding our beta products, please send your message directly to: [EMAIL PROTECTED] If this is an urgent technical support problem and you require a guaranteed response time, SoftArtisans offers two levels of paid technical support: PRIORITY SUPPORT. Guaranteed response within two business hours of placing your call or sending your e-mail. There is no guarantee that we'll be able to provide an immediate resolution of your problem, but we will work on it until we can come to a conclusion. We will always provide you an expected date to issue you a patch if necessary. Please see instructions at: http://www.softartisans.com/softartisans/priorsup.html PER-INCIDENT SUPPORT. Guaranteed response within one business day of placing your call or sending your e-mail. There is no guarantee that we?ll be able to provide an immediate resolution of your problem, but we will work on it until we can come to a conclusion. If you would like to use a Per-incident technical support that was packaged with a product purchase (such as ASPStudio with Annual Maintenance), please call with your order number. Otherwise, please see instructions at: http://www.softartisans.com/softartisans/persup.html - Tracking Your General Information Request - Every incoming email is assigned a unique tracking number. Your tracking number is: [INF200308230269] In order to help us track the progress of request, please include [INF200308230269] in the subject line of any further mail regarding this issue. - SOFTARTISANS HOURS OF OPERATION - Monday through Friday, 9:00 a.m. to 5:00 p.m. Eastern Time (GMT-5) We look forward to reading your message and will respond as quickly as possible. Thanks. Sincerely, SoftArtisans Customer Service http://www.softartisans.com P.S. Please stay in touch! The SoftArtisans monthly newsletter is filled with helpful product information, upgrade notices and ASP tips. Please visit http://discuss.softartisans.com/cgi-bin/wa.exe?SUBED1=newsletterA=1 if you would like to receive or stop receiving the newsletter. You are currently subscribed to the newsletter as [EMAIL PROTECTED] SoftArtisans respects your privacy and will not sell or rent personally identifiable information to anyone. Our privacy policy is posted at http://www.softartisans.com/softartisans/privacypolicy.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Escaping UCS2 characters
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I asked a question along these lines yesterday, but got no response. Perhaps I can make it clearer: I have a ucs2 table in my predominantly latin1 database. (Mysql 4.1.1 from bk) I am using latin1 most of the time, it's the default charset, and the one I am using to talk to MySQL with. I want to add some UCS2 data to my UCS2 table. How do I escape UCS2 characters in a latin1 SQL statement? I know their values in hex, etc... But I can't seem to add them. I tried doing \u00a3 syntax buy MySQL didn't seem to understand it. simon -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQE/TJB3PBt+tvwCnbYRAqK2AJ9ai7kLystd9WsIiCje/4YIqWPwHACfZBIu c9l4WFPTI4sGWdcLMKzIixA= =2F5f -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warning Re: My details [INF2003082300000269]
from what I can see from the subject and this message alone, someone on this list has been infected by the sobig.f virus, could everyone just please check their computers... that is if you are running any windows machine, not you that are running linux or any other os... thanks in advance... /Christian Andersson [EMAIL PROTECTED] wrote: Thanks for writing to [EMAIL PROTECTED] This is an auto-generated response to let you know that your message has reached our general information box on 8/23/2003 at 5:38:55 AM. Customer Service will be processing and, if necessary, responding to your inquiry shortly. We usually reply to general inquiries within one business day and appreciate your patience. There is no need to respond to this message, but if your message was concerning a technical support issue, a sales-related question, or beta product, there is a more direct and quicker way of contacting the appropriate SoftArtisans personnel--please see below. -- SoftArtisans Technical Support, Sales, and Beta Mailboxes -- Occasionally, our general information box receives messages that are more appropriate for another mailbox. For faster service, please consider resending your message directly to one of the follwing: For free technical support issues, please send your message directly to: [EMAIL PROTECTED] For a sales consultation, quote request, or other sales-related question, please send your message directly to: [EMAIL PROTECTED] For comments, feedback, or questions regarding our beta products, please send your message directly to: [EMAIL PROTECTED] If this is an urgent technical support problem and you require a guaranteed response time, SoftArtisans offers two levels of paid technical support: PRIORITY SUPPORT. Guaranteed response within two business hours of placing your call or sending your e-mail. There is no guarantee that we'll be able to provide an immediate resolution of your problem, but we will work on it until we can come to a conclusion. We will always provide you an expected date to issue you a patch if necessary. Please see instructions at: http://www.softartisans.com/softartisans/priorsup.html PER-INCIDENT SUPPORT. Guaranteed response within one business day of placing your call or sending your e-mail. There is no guarantee that we?ll be able to provide an immediate resolution of your problem, but we will work on it until we can come to a conclusion. If you would like to use a Per-incident technical support that was packaged with a product purchase (such as ASPStudio with Annual Maintenance), please call with your order number. Otherwise, please see instructions at: http://www.softartisans.com/softartisans/persup.html - Tracking Your General Information Request - Every incoming email is assigned a unique tracking number. Your tracking number is: [INF200308230269] In order to help us track the progress of request, please include [INF200308230269] in the subject line of any further mail regarding this issue. - SOFTARTISANS HOURS OF OPERATION - Monday through Friday, 9:00 a.m. to 5:00 p.m. Eastern Time (GMT-5) We look forward to reading your message and will respond as quickly as possible. Thanks. Sincerely, SoftArtisans Customer Service http://www.softartisans.com P.S. Please stay in touch! The SoftArtisans monthly newsletter is filled with helpful product information, upgrade notices and ASP tips. Please visit http://discuss.softartisans.com/cgi-bin/wa.exe?SUBED1=newsletterA=1 if you would like to receive or stop receiving the newsletter. You are currently subscribed to the newsletter as [EMAIL PROTECTED] SoftArtisans respects your privacy and will not sell or rent personally identifiable information to anyone. Our privacy policy is posted at http://www.softartisans.com/softartisans/privacypolicy.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: variables in select into outfile
Thompson, Jordan [EMAIL PROTECTED] wrote: I wan to use a variable for the outfile file name in a select into clause. I am doing something like: set @fileName=/tmp/result.text SELECT col1, col2 INTO OUTFILE @fileName FIELDS TERMINATED BY ',' FROM test_table; the interpreter dies at the @fileName... if I exchange @filename with /tmp/result.text it runs fine. You can't use variables in such manner. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitive indexes
gord barq [EMAIL PROTECTED] wrote: I'm using MySQL 4.1 so how do I define case sensitive collation? Is it on the columns or on the indexes? On column. Look at the collations of your character set using SHOW COLLATION command: http://www.mysql.com/doc/en/Charset-SHOW-COLLATION.html _cs at the and of collation name means case sensitive. Here you can find examples how to set up character set and collation: http://www.mysql.com/doc/en/Charset-examples.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment fields
Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Thanks. Fernando Bernardino __ Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about row level locking with InnoDB tables
Steve, - Original Message - From: Steve McWilliams [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, August 27, 2003 12:07 AM Subject: Question about row level locking with InnoDB tables Hello, I am relatively new to MySql (4.0.14) but I have read through the relevent documentation and am still confused about how row level locking behaves with InnoDB tables. I created a database with a single innodb table which has 2 columns, one of which is indexed. The locking behavior I see when I test against this database is that it uses row level locks if the SELECT ... FOR UPDATE involves the indexed column, but uses table level locks if instead it involves the non-indexed column. For example, if I have 2 mysql clients that perform the following operations: client1 set autocommit=0; client1 begin; client1 SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE; client2 set autocommit=0; client2 begin: client2 SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE; The above query by client2 will block if the column in question is not indexed, implying that client1 has somehow locked the entire table, even though client1 and client2 are selecting different rows. a locking SELECT will set row locks (usually next-key locks) on every index record it looks at. This is necessary to prevent 'phantom rows' from appearing in your result set. If you do not have an index on my_column, MySQL needs to scan the whole table and thus it locks every row. http://www.innodb.com/ibman.html#Locks_set_by_statements http://www.innodb.com/ibman.html#Next_key_locking http://www.innodb.com/ibman.html#Cope_with_deadlocks Am I misconfiguring something, or does InnoDB simply only support row level locking when you are selecting indexed rows? Thanks in advance, Steve McWilliams Software Engineer Emprisa Networks 703-691-0433x21 [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: own privileges
[EMAIL PROTECTED] wrote: User can use SHOW GRANTS command: Thank you! But one more question: what privileges user should have to issue this command? I've got a user who has rights only to SELECT from certain db. What should i GRANT to him to allow use of SHOW GRANTS? Every user can see his own grants. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 09:05:17 -0300 bernardaum [EMAIL PROTECTED] wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? yes, use TRUNCATE TABLE table; to restart sequence. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.13/Solaris9 - 64 bit or 32 bit
Hi all, I'm upgrading a mission critical production server with Solaris 9 and mysql 4.0.13 and was wonder if I should install as 64 bit or 32 bit. I couldn't find much recent discourse on the subject, perhaps some of you have some experience, known pit falls, ideas or suggestions Thanks, I know general questions like this are tougher to answer. Mike
Var/log/mysql/mysql.log
Quick log question... We've got our 3.23 db installed with all the data on the /home partition which is ok. The *-bin.00x logs are also in that data directory. The Var/log/mysql/mysql.log however on our root drive has grown to 11gig. Our Root partion is not as large as our Home partition and I need to know if I can delete or trim down the mysql.log without hurting replication at all. If not, how can I move it to the Home partition? If it can't be done without a recompile, it's not a huge problem as I plan on upgrading the hardware and db to 4.x in the next week or so, but I just need to know. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Online Backup.
Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wednesday 27 August 2003 1:05 pm, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? When I need to do this, I just drop and re-create the table. simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 13:27:25 +0100 Simon [EMAIL PROTECTED] wrote: On Wednesday 27 August 2003 1:05 pm, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? When I need to do this, I just drop and re-create the table. http://www.mysql.com/doc/en/TRUNCATE.html Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. (c) I suppose TRUNCATE is more efficient way to get the same result =) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Newbie Question
When I do the tests on frontpage, it doesn't seem to do anything. what test are you talking about? I am reading the Book PHP and MYsql for dummies and it talks of a test you can do to see if PHP and SQL are talking with each other. On the Mysqladmin it tells me that my odbc driver 3,.51 not found is that a problem and how do I get the driver? you don't need ODBC driver to work in PHP with MySQL. PHP has native builtin extension for MySQL. just use mysql_*(); functions and be happy =) you will have to excuse my ignorance what Is a mysql_*;function as I said I am completely new to this and don't know how to set this up. thanks Nicci --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED]
Re: auto_increment fields
At 9:05 -0300 8/27/03, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Why bother? MySQL doesn't care if there are gaps in the sequence. And if you're using the ID to relate records in the table to records in another table, you'll destroy the correspondence. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax question
These are tables that I did not design (and would not have in this fashion), but I have to make do with them Table 1 structure: id_num number, descr1 varchar(30), descr2 varchar(30), descr3 varchr(30) Table 2 structure id_name varchar(15), ext_descr varchar(30) Table 2 is a child of table 1 (sort of) id_name in table 2 = id_num from table 1, preceeded by zero fill, superceeded by a three digit number (1 - 999). For example if id_num = 1234567, id_name might be 01234567001 and there might also be a 01234567002, etc. I need to produce a query (so that I can do a report) that has the following result: id_num descr1 descr2 descr3 ext_descr ext_descr ext_descr ...ETC... The bottom line here is that I need to get a select on the id_num in table 1 and all corresponding records in table 2. I know I build the first 12 characters of the id_name by using the id_num, zero filling and inquiring on substr(id_name,1,12). However, I am having a little trouble building the sql statement itself. Any thoughts would be appreciated. Thanks. Mark Roberts Sr. Systems Analyst Corporate Compliance Governance Applications
Re: auto_increment fields
On Wed, 27 Aug 2003 09:38:16 -0400 Paul DuBois [EMAIL PROTECTED] wrote: At 9:05 -0300 8/27/03, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Why bother? MySQL doesn't care if there are gaps in the sequence. And if you're using the ID to relate records in the table to records in another table, you'll destroy the correspondence. imho, he's talking about resetting auto_increment to 0, not about gaps in ID: When I -- delete all -- the records ... --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
On Wed, 27 Aug 2003 09:33:38 -0400 Nicola Hartland [EMAIL PROTECTED] wrote: I am reading the Book PHP and MYsql for dummies and it talks of a test you can do to see if PHP and SQL are talking with each other. hmm.. you will have to excuse my ignorance what Is a mysql_*;function as I said I am completely new to this and don't know how to set this up. visit http://php.net/mysql - you will see. I'm talking about mysql_connect();, mysql_query(); etc. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
At 17:56 +0400 8/27/03, Antony Dovgal wrote: On Wed, 27 Aug 2003 09:38:16 -0400 Paul DuBois [EMAIL PROTECTED] wrote: At 9:05 -0300 8/27/03, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Why bother? MySQL doesn't care if there are gaps in the sequence. And if you're using the ID to relate records in the table to records in another table, you'll destroy the correspondence. imho, he's talking about resetting auto_increment to 0, not about gaps in ID: When I -- delete all -- the records ... Second reason still applies. If it's still something deemed desireable: ALTER TABLE tbl_name AUTO_INCREMENT = 1; (or 0, but sequences don't really begin with 0, they begin with 1. :-)) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
On 26 Aug 2003 at 19:38, Twibell, Cory L wrote:
I have a query that is inner joined with another table based on
country codes Select distinct Name.* from Name inner join Location on
Location.key = Name.key and Location.cc in ('list of countries
here');
From the message you're getting it seems you're using a heap table.
Why is that? In the documentation it says that for heap tables
Indexes will only be used with = and = (but are VERY fast).
The indexes for heap tables are hash-based, which means they can't be
used for range queries (since the hash values for consecutive keys
won't be consecutive). But if you're using IN in you're query (with
more than one value in the list) then you need to get a range from
the index. Since that's not possible with a heap table, the whole
table must be scanned.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Explanation of multiple-column indexes
On Tue, Aug 26, 2003 at 09:26:55PM -0500, Matt W wrote: Hi Jesse, - Original Message - From: Jesse Sheidlower What I'm trying to understand is how you would set up these indexes when you'd always be doing joins with another table. Suppose you have The Canonical CD Database, and you have a table songs with fields song_id, album_id, song_title, and song_length. Suppose you're often doing searches of song_title or (for some reason) song_length, and that any time you'd do such a search, you'd _always_ be joining it to the album table. It would seem that you'd want at least two multiple-indexes in the song table, one of them including song_title and album_id, the other including song_length and album_id. Is this correct? Do you need song_id (which would be a primary key on that table) in there too? What order should the indexes be in? You wouldn't necessarily want indexes on (song_title, album_id) -- in that order -- and/or (song_length, album_id). This reason for this is because if any other columns from the song table are involved in the query (in the select list or in the WHERE), MySQL will have to hit the data file for those columns anyway, and album_id as the second column in the index won't be used -- just the first column -- song_title or song_length -- if you're searching on them. However, if only the 2 columns in the index are involved in the query (searching on title or length and join with album_id), then having album_id in the index would be benficial because no seek to the data file is needed. This can be verified by seeing if EXPLAIN says Using index for the song table. [...] If every search for song_title or song_length must be joined against the album table, it's not clear which should be the first named column in this index. The experiments I've done so far have been inconclusive, and I don't think I'm understanding the process in the first place. The indexes would be: (song_title, album_id) (song_length, album_id) If the order was reversed (e.g. album_id was first in the index), the index couldn't be used for searching. And like I said above, if other columns besides the 2 in the index are involved in the query, album_id isn't used anyway. In that case, just index title and length separately for searching: (song_title) (song_length) Of course, if you included ALL columns that will be used in queries in each index (with title or length as the first column in each), then it wouldn't have to go to the data file and EXPLAIN would say Using index. But this doesn't usually give much speed improvement and is a waste of space. Just letting you know. :-) Well, if speed rather than space is my main concern, _and_ I can't predict what the searches will be--i.e. it's quite possible that some searches will be only song_title, some will be only song_length, and some will be both, and some will involve other combinations of fields not shown in this made-up example--then what? Do I have several multiple-column indexes, each with (song_title, album_id) or whatever for each field, along with single-column indexes for everything (even though every query would be joined on album_id)? Or do I need to have three- or more-column indexes for all the potential groups that might be searched? (song_title, song_length, album_id), (song_title, song_something_else, album_id), etc.? Thanks very much. Best, Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaping UCS2 characters
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 27 August 2003 12:05 pm, I wrote: How do I escape UCS2 characters in a latin1 SQL statement? Okay, so the answer's in the manual. I just didn't look hard enough... http://www.mysql.com/doc/en/Charset-literal.html So I do: 'INSERT INTO blahblah (ucs2col,etc) VALUES (_ucs2 x'0024003100300030', 123)' ta-daa. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQE/TLzDPBt+tvwCnbYRAke2AJ9dMWEt4ggMApR0Nsln85ZlkQylTgCfa/sk +Mm5jDSavr2PTyi/UavJcAI= =OxZs -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 10:04:02 -0400 Paul DuBois [EMAIL PROTECTED] wrote: Second reason still applies. yes, but I suppose he knows what he's trying to do =) If it's still something deemed desireable: ALTER TABLE tbl_name AUTO_INCREMENT = 1; yes, this is another one possible solution, but I think that DELETE FROM table; ALTER TABLE table AUTO_INCREMENT = 1; is not so pretty, as TRUNCATE TABLE table; and is not so efficient. am I wrong? (or 0, but sequences don't really begin with 0, they begin with 1. :-)) yep =) but we both know what he's talking about... it's a some kind of telepathy =)) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Local daynames?
Dear Mysql Users, Could you tell me what should I do to get local (polish) daynames from the following function: SELECT DAYNAME(1998-02-05); thanks, regards, Lukasz
Speeding query
Hi list: I have the following partial result from an explain select: | table| type | possible_keys | key | key_len | ref | rows | Extra | +--++--+--+-+-+--+-+ | OrdenServicio| ref| XIF923OrdenServicio,XIF117OrdenServicio,XIF910OrdenServicio,XIF172OrdenServicio,IDX5979OrdenServicio,IDX5984OrdenServicio,IDX5985OrdenServicio,IDX0010OrdenServicio,IDX0111OrdenServicio | IDX5979OrdenServicio | 4 | const | 1058 | where used; Using temporary || Tienda | ref| PRIMARY | PRIMARY | 4 | OrdenServicio.idTienda |1 | | | TipoGarantia | ALL| PRIMARY | NULL |NULL | NULL |2 | where used | | Pedido | eq_ref | PRIMARY,IDX6868,XIF134Pedido,XIF135Pedido | PRIMARY | 12 | OrdenServicio.idPedido,OrdenServicio.idCanal,OrdenServicio.idTienda |1 | | .-- And I would like to remove the Using temporary label, and this is my query: Select distinct OrdenServicio.folioOrdenServicio, OrdenServicio.idCanal, OrdenServicio.idTienda, OrdenServicio.idTaller, OrdenServicio.idCentroServicio, OrdenServicio.idPedido, OrdenServicio.idProducto, OrdenServicio.fallaCliente, OrdenServicio.idEntregaControl, OrdenServicio.numSerie, OrdenServicio.estaComponenteBase, OrdenServicio.observaciones, OrdenServicio.idStatusOrden, OrdenServicio.idCanalLevantamiento, OrdenServicio.idTiendaLevantamiento, OrdenServicio.idCentroServicioLevantamiento, OrdenServicio.idTallerLevantamiento, OrdenServicio.idTipoGarantia, OrdenServicio.idLinea, OrdenServicio.esReparacionInterna, OrdenServicio.otroAccesorio, OrdenServicio.idFalla, OrdenServicio.fechaLevantamiento, Taller.nombre Taller_nombre, Tienda.nombre Tienda_nombre, Producto.nombre Producto_nombre, Marca.idMarca, Marca.nombre Marca_nombre, Pedido.fechaSurtimiento, ProductoComprado.idStatusGarantia, ProductoComprado.garantiaExtendida, ProductoComprado.numPoliza, Cliente.idCliente, Cliente.nombre Cliente_nombre,Cliente.apellidoP, Cliente.apellidoM, TipoGarantia.nombre TipoGarantia_nombre, CentroServicio.nombre CentroServicio_nombre, StatusGarantia.nombre StatusGarantia_nombre from OrdenServicio, Tienda, Producto, Marca, Pedido, Cliente, TipoGarantia, CentroServicio, Taller, ProductoComprado, StatusGarantia where OrdenServicio.idTipoGarantia = TipoGarantia.idTipoGarantia and OrdenServicio.idTienda = Tienda.idTienda and OrdenServicio.idProducto = Producto.idProducto and OrdenServicio.idPedido = Pedido.idPedido and OrdenServicio.idTienda = Pedido.idTienda and OrdenServicio.idCanal = Pedido.idCanal and OrdenServicio.idProducto = ProductoComprado.idProducto and OrdenServicio.idPedido = ProductoComprado.idPedido and OrdenServicio.idCanal = ProductoComprado.idCanal and OrdenServicio.idTienda = ProductoComprado.idTienda and ProductoComprado.idProducto = Producto.idProducto and Producto.idMarca = Marca.idMarca and ProductoComprado.idStatusGarantia = StatusGarantia.idStatusGarantia and Cliente.idCliente = Pedido.idCliente and Cliente.idTienda = Pedido.idTienda and Cliente.idCanal = Pedido.idCanal and OrdenServicio.idTaller = Taller.idTaller and OrdenServicio.idCentroServicio = CentroServicio.idCentroServicio and (OrdenServicio.idStatusOrden = 13) and OrdenServicio.idLinea = 2 group by OrdenServicio.folioOrdenServicio limit 0,10; Any suggestions to remove the Using temporary label in the explain results. Thnx in advanced Best Regards. Mikel
Re: Replication with multiple servers in Mysql ?
Hi Jeremy Zawodny (and all) I am using your mytop and this is what I am seeing MySQL on localhost (4.0.13-standard-log) up 0+10:48:22 [11:14:37] Queries: 85.6k qps:2 Slow: 207.0 Se/In/Up/De(%):86/05/05/02 qps now:2 Slow qps: 0.0 Threads: 11 ( 2/ 4) 00/00/00/00 Cache Hits: 37.6k Hits/s: 1.0 Hits now: 0.0 Ratio: 51.0% Ratio now: 0.0% Key Efficiency: 99.2% Bps in/out: 727.8/ 1.5k Now in/out: 40.8/ 4.2k It seems my mysql is little slow. I have 4 450Mhz with 4 gig RAM and I am using my-huge.cnf. I don't have any secondary server. Wonder if you or anyone else could suggest any place for improvement. Thanks a lot -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Var/log/mysql/mysql.log
Jeff McKeon [EMAIL PROTECTED] wrote: We've got our 3.23 db installed with all the data on the /home partition which is ok. The *-bin.00x logs are also in that data directory. The Var/log/mysql/mysql.log however on our root drive has grown to 11gig. Our Root partion is not as large as our Home partition and I need to know if I can delete or trim down the mysql.log without hurting replication at all. If not, how can I move it to the Home partition? If it can't be done without a recompile, it's not a huge problem as I plan on upgrading the hardware and db to 4.x in the next week or so, but I just need to know. It's a file of general query logs. It isn't related to the replication. If you want to turn off logging just stop MySQL server and start it without --log option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table Query taking WAY TO LONG...HELP PLEASE!!!
All, the tables are defined as MyISAM. In fact, I left last night and
the Query was still running the convert HEAP to MyISAM Somethings
definately wrong.
I have indices on Name.key, Location.key and Location.cc
the results of the explain :
id| select_type| table | type | possible keys| key
|key_len| ref |rows | Extra
---
1 | SIMPLE | LOCATION|range |PRIMARY,LOC_CC_IDX|LOC_CC_IDX |2
|NULL |55248|
1 | SIMPLE | NAME|ref |NAME_KEY_IDX |NAME_KEY_IDX|15
|LOC_IDX|2|
It looks like it's using the correct indicesI don't know what else
to do...
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 27, 2003 12:23 AM
To: Twibell, Cory L
Cc: [EMAIL PROTECTED]
Subject: Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
On Tue, Aug 26, 2003 at 07:38:04PM -0600, Twibell, Cory L wrote:
All,
I have a query that is inner joined with another table based on
country codes Select distinct Name.* from Name inner join Location
on Location.key = Name.key and Location.cc in ('list of countries
here');
The problem is when I have more than 2 country codes, the query
takes forever... When I show processlist, it says converting HEAP
to MyISAM ( this takes over an hour )... I've tried bumping up
max_heap_table_size to 128M and tmp_table_size to 128M, but that
only seems to delay the converting HEAP to MyISAM message
Name contains about 3 million records and Location contains about 1.5
million records.
The table is a HEAP table?
I don't think the optimizer knows how to make an IN(...) query in a
HEAP table fast. Have you tried this with a MyISAM table? It should
be pretty fast, assuming you're pulling back less than 1/4th of the
rows with that query.
Can you show us the EXPLAIN output from the query?
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.13: up 25 days, processed 1,141,718,690 queries (517/sec. avg)
--
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: utf8 support
Li, John [EMAIL PROTECTED] wrote: Any hint where and how to get mysql 4.1.1? Official binaries will be available in September at: http://www.mysql.com/downloads/index.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
A very good question! One which I will attempt to provide a half-decent answer for. Trying to execute a transaction that involves grabbing massive amounts of data from various tables while still allowing updates to be committed can be tricky. At best, this means performance problems. Please don't get me wrong - MySQL is the fastest database on the planet (and it looks to only get faster, with additional query caches, better designed caches and a helper thread architecture in the works) but there are certain things that are just hard to do quickly, even if you are the demigods that work at Innobase Oy and MySQL AB. The advantage of other methods is that you avoid all sorts of nasty locking on various parts of your database. The advantage of the Hot Copy product that Innobase Oy sell is that it doesn't place any locks on your InnoDB table space when it runs. In summary, it's not a question as to whether the current methods work, it's a question as to how well suited they are to your needs. For mine they work well, but if I was processing 600 queries / second, I think I'd want ibcopy to help me out. Regards, Chris quote who=P Srinivasulu If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ingres vs MySQL
Hello MySQL Team, I'm working currently on the project where we need to make a decision what engine would be our main production database engine. Could you please help me and shed a light on what pros and cons MySQL vs Ingres: features, performance, support, portability among Wintel and Unix based machines (FreeBSD, Linux, NetBSD). I like very much MySQL, but could not find any good resource or information about Ingres, even from maker. Any help and suggestions highly appreciated. With best regards, Igor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
Hi All, my $0.0.2... anybody correct me please if you know it better...;-) 2003 8 27 23:22P Srinivasulu : If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. mysqldump --single-transaction is backing up all tables in a single transaction, see the remark MUTUALLY exclusive with lock tables, meaning that NO other client can modify the tables/ data at that time (which may take some time for big databases ). mysqlhotcopy is only working on MyISAM table formats yet and again locking the whole table. But as its a read lock other clients can still read the data. However, this perl script runs only local (must be run on the server). As for the question about the online backup facility, I believe what meant is that you can run an online backup from a tool like phpmyadmin, xoops, basically a normal webpage, etc. The difference of all these tools is a) how they accesss and lock the tables/data you want to backup b) the format of the output file (txt,sql format, binary etc. c) the supported features I hope this answers some of your questions. Let me know if you need more details. Best regards Nils Valentin Tokyo/Japan Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
Hi Chris, 2003 8 28 00:54Chris Nolan : A very good question! One which I will attempt to provide a half-decent answer for. Trying to execute a transaction that involves grabbing massive amounts of data from various tables while still allowing updates to be committed can be tricky. At best, this means performance problems. Please don't get me wrong - MySQL is the fastest database on the planet (and it looks to only get faster, with additional query caches, better designed caches and a helper thread architecture in the works) but there are certain things that are just hard to do quickly, even if you are the demigods that work at Innobase Oy and MySQL AB. The advantage of other methods is that you avoid all sorts of nasty locking on various parts of your database. The advantage of the Hot Copy product that Innobase Oy sell is that it doesn't place any locks on your InnoDB table space when it runs. Wouldn't that break the ACID ? The backup data would not be fully consistent, integrated anymore. Anyway thats my understanding. I understand that the InnoDB Hot Copy product is providing this feature by using the actual row locks to make sure that ACID is taken care of while creating a consistent backup. Best regards Nils Valentin In summary, it's not a question as to whether the current methods work, it's a question as to how well suited they are to your needs. For mine they work well, but if I was processing 600 queries / second, I think I'd want ibcopy to help me out. Regards, Chris quote who=P Srinivasulu If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
Hi Chris, 2003 8 28 00:54Chris Nolan : A very good question! One which I will attempt to provide a half-decent answer for. Trying to execute a transaction that involves grabbing massive amounts of data from various tables while still allowing updates to be committed can be tricky. At best, this means performance problems. Please don't get me wrong - MySQL is the fastest database on the planet (and it looks to only get faster, with additional query caches, better designed caches and a helper thread architecture in the works) but there are certain things that are just hard to do quickly, even if you are the demigods that work at Innobase Oy and MySQL AB. The advantage of other methods is that you avoid all sorts of nasty locking on various parts of your database. The advantage of the Hot Copy product that Innobase Oy sell is that it doesn't place any locks on your InnoDB table space when it runs. You made an interesting point there with the locking mechanism. I double checked the innodb homepage. http://www.innodb.com/hotbackup.html While it says at the top as you stated no locks are written it also says under 3) how it locks and unlocks the tables. I guess thats best explained by Heikki ;-) - I copied her. Best regards Nils Valentin Tokyo/Japan In summary, it's not a question as to whether the current methods work, it's a question as to how well suited they are to your needs. For mine they work well, but if I was processing 600 queries / second, I think I'd want ibcopy to help me out. Regards, Chris quote who=P Srinivasulu If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
2003 8 28 01:43Nils Valentin : Hi Chris, 2003 8 28 00:54Chris Nolan : A very good question! One which I will attempt to provide a half-decent answer for. Trying to execute a transaction that involves grabbing massive amounts of data from various tables while still allowing updates to be committed can be tricky. At best, this means performance problems. Please don't get me wrong - MySQL is the fastest database on the planet (and it looks to only get faster, with additional query caches, better designed caches and a helper thread architecture in the works) but there are certain things that are just hard to do quickly, even if you are the demigods that work at Innobase Oy and MySQL AB. The advantage of other methods is that you avoid all sorts of nasty locking on various parts of your database. The advantage of the Hot Copy product that Innobase Oy sell is that it doesn't place any locks on your InnoDB table space when it runs. You made an interesting point there with the locking mechanism. I double checked the innodb homepage. http://www.innodb.com/hotbackup.html While it says at the top as you stated no locks are written it also says under 3) how it locks and unlocks the tables. I guess thats best explained by Heikki ;-) - I copied her. Best regards Nils Valentin Tokyo/Japan In summary, it's not a question as to whether the current methods work, it's a question as to how well suited they are to your needs. For mine they work well, but if I was processing 600 queries / second, I think I'd want ibcopy to help me out. Regards, Chris quote who=P Srinivasulu If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup functionality will be provided in Mysql-5.1. I think its already there in the form of mysqlhotcopy. Is Mysql going to extend the functionality of mysqlhotcopy so that it works for Innodb tables also? or they are going to provide independent online backup functionality. Please clarify me. http://www.mysql.com/doc/en/TODO_MySQL_5.1.html Thanks, Srinivasulu. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance
Sorry for reposting , wrong subject in the prebious one -- Forwarded message -- Date: Wed, 27 Aug 2003 11:17:36 -0400 (EDT) From: Asif Iqbal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Replication with multiple servers in Mysql ? Hi Jeremy Zawodny (and all) I am using your mytop and this is what I am seeing MySQL on localhost (4.0.13-standard-log) up 0+10:48:22 [11:14:37] Queries: 85.6k qps:2 Slow: 207.0 Se/In/Up/De(%):86/05/05/02 qps now:2 Slow qps: 0.0 Threads: 11 ( 2/ 4) 00/00/00/00 Cache Hits: 37.6k Hits/s: 1.0 Hits now: 0.0 Ratio: 51.0% Ratio now: 0.0% Key Efficiency: 99.2% Bps in/out: 727.8/ 1.5k Now in/out: 40.8/ 4.2k It seems my mysql is little slow. I have 4 450Mhz with 4 gig RAM and I am using my-huge.cnf. I don't have any secondary server. Wonder if you or anyone else could suggest any place for improvement. Thanks a lot -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql Online Backup.
The advantage of other methods is that you avoid all sorts of nasty locking on various parts of your database. The advantage of the Hot Copy product that Innobase Oy sell is that it doesn't place any locks on your InnoDB table space when it runs. Wouldn't that break the ACID ? The backup data would not be fully consistent, integrated anymore. Anyway thats my understanding. I understand that the InnoDB Hot Copy product is providing this feature by using the actual row locks to make sure that ACID is taken care of while creating a consistent backup. The algorithm as I understand it is something like this: 1) Tell InnoDB engine to flush the transaction journal to the data pool. 1) Tell InnoDB engine to NOT flush the transaction journal to data pool until further notice. 2) Make a flat copy of the data pool (analogous to just cp'ing the files). 3) Make a copy of pending transactions in the transaction journal. 4) Tell InnoDB engine to resume normal behavior. Transactions can still continue to write to the DB because everything goes through the transaction journal anyway. The journal simply stores a list of changed DB pages associated with a particular transaction, and is flushed to the data pool asynchronously. The data pool always represents a consistent-state snapshot of the DB thanks to the double-write buffer. The only risk is that the backup procedure may take such a long time that the transaction journal becomes full. I don't know how InnoDB handles this -- I suspect transactions simply start failing at that point although I suppose it's possible that they simply block until space becomes available. You can avoid this by ensuring that your transaction journals are sufficiently large to accommodate the copy process in the face of the heaviest possible DB write load. Estimating with any precision is difficult but it's usually fairly straightforward to come up with a definitely safe guess. (Our production DB runs 3 transaction journals of 20MB each for example -- plenty of space for us, even assuming very substantial growth in write traffic) -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explain select
Hi list: I have the following partial result from an explain select: | table| type | possible_keys | key | key_len | ref | rows | Extra | +--++--+--+-+-+--+-+ | OrdenServicio| ref| XIF923OrdenServicio,XIF117OrdenServicio,XIF910OrdenServicio,XIF172OrdenServicio,IDX5979OrdenServicio,IDX5984OrdenServicio,IDX5985OrdenServicio,IDX0010OrdenServicio,IDX0111OrdenServicio | IDX5979OrdenServicio | 4 | const | 1058 | where used; Using temporary || Tienda | ref| PRIMARY | PRIMARY | 4 | OrdenServicio.idTienda |1 | | | TipoGarantia | ALL| PRIMARY | NULL |NULL | NULL |2 | where used | | Pedido | eq_ref | PRIMARY,IDX6868,XIF134Pedido,XIF135Pedido | PRIMARY | 12 | OrdenServicio.idPedido,OrdenServicio.idCanal,OrdenServicio.idTienda |1 | | .-- And I would like to remove the Using temporary label, and this is my query: Select distinct OrdenServicio.folioOrdenServicio, OrdenServicio.idCanal, OrdenServicio.idTienda, OrdenServicio.idTaller, OrdenServicio.idCentroServicio, OrdenServicio.idPedido, OrdenServicio.idProducto, OrdenServicio.fallaCliente, OrdenServicio.idEntregaControl, OrdenServicio.numSerie, OrdenServicio.estaComponenteBase, OrdenServicio.observaciones, OrdenServicio.idStatusOrden, OrdenServicio.idCanalLevantamiento, OrdenServicio.idTiendaLevantamiento, OrdenServicio.idCentroServicioLevantamiento, OrdenServicio.idTallerLevantamiento, OrdenServicio.idTipoGarantia, OrdenServicio.idLinea, OrdenServicio.esReparacionInterna, OrdenServicio.otroAccesorio, OrdenServicio.idFalla, OrdenServicio.fechaLevantamiento, Taller.nombre Taller_nombre, Tienda.nombre Tienda_nombre, Producto.nombre Producto_nombre, Marca.idMarca, Marca.nombre Marca_nombre, Pedido.fechaSurtimiento, ProductoComprado.idStatusGarantia, ProductoComprado.garantiaExtendida, ProductoComprado.numPoliza, Cliente.idCliente, Cliente.nombre Cliente_nombre,Cliente.apellidoP, Cliente.apellidoM, TipoGarantia.nombre TipoGarantia_nombre, CentroServicio.nombre CentroServicio_nombre, StatusGarantia.nombre StatusGarantia_nombre from OrdenServicio, Tienda, Producto, Marca, Pedido, Cliente, TipoGarantia, CentroServicio, Taller, ProductoComprado, StatusGarantia where OrdenServicio.idTipoGarantia = TipoGarantia.idTipoGarantia and OrdenServicio.idTienda = Tienda.idTienda and OrdenServicio.idProducto = Producto.idProducto and OrdenServicio.idPedido = Pedido.idPedido and OrdenServicio.idTienda = Pedido.idTienda and OrdenServicio.idCanal = Pedido.idCanal and OrdenServicio.idProducto = ProductoComprado.idProducto and OrdenServicio.idPedido = ProductoComprado.idPedido and OrdenServicio.idCanal = ProductoComprado.idCanal and OrdenServicio.idTienda = ProductoComprado.idTienda and ProductoComprado.idProducto = Producto.idProducto and Producto.idMarca = Marca.idMarca and ProductoComprado.idStatusGarantia = StatusGarantia.idStatusGarantia and Cliente.idCliente = Pedido.idCliente and Cliente.idTienda = Pedido.idTienda and Cliente.idCanal = Pedido.idCanal and OrdenServicio.idTaller = Taller.idTaller and OrdenServicio.idCentroServicio = CentroServicio.idCentroServicio and (OrdenServicio.idStatusOrden = 13) and OrdenServicio.idLinea = 2 group by OrdenServicio.folioOrdenServicio limit 0,10; Any suggestions to remove the Using temporary label in the explain results. Thnx in advanced Best Regards. Mikel
Re: Telemobile auto-reply
Scott Haneda wrote: Anyone else get one of these for every post to this list? Return-Path: [EMAIL PROTECTED] That offender have now been removed from the list and prevented from re-subscribing. Best Wishes Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Var/log/mysql/mysql.log
We've got our 3.23 db installed with all the data on the /home partition which is ok. The *-bin.00x logs are also in that data directory. The Var/log/mysql/mysql.log however on our root drive has grown to 11gig. Our Root partion is not as large as our Home partition and I need to know if I can delete or trim down the mysql.log without hurting replication at all. If not, how can I move it to the Home partition? If it can't be done without a recompile, it's not a huge problem as I plan on upgrading the hardware and db to 4.x in the next week or so, but I just need to know. It's a file of general query logs. It isn't related to the replication. If you want to turn off logging just stop MySQL server and start it without --log option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Egor, Thanks for the reply. That is what I thought but wanted to make sure. Can logrotate be used with this log file or will there be problems? Also, if I just wanted to kill the current mysql.log file without restarting mysql by simply deleting it(it's in production and stopping the db means our entire software system must be shutdown which is a pain)? Will MySql simply create a new file and go about it's merry way or will it error out? The current file is 12gig. If we could kill the current file in this manner it would buy us the time we need until we do the rebuild/upgrade later next week. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Script for Database[MySQL]::Web interfacing
Hello, Could someone recommend a decent php script that could be used for database[MySQL]::Web interfacing and management. Preferably open source, freeware or otherwise [I'll just have to grin bare it] Thanks -- Joe -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1044 on database restore
Carl B. Constantine [EMAIL PROTECTED] wrote: * Victoria Reznichenko ([EMAIL PROTECTED]) wrote: It means that user doesn't have ALTER privilege. BUT, I've tried granting the user all privs in mysqlcc and it still doesn't work. Looking at my other setup, the users look like they are set up the same. What version do you use? Did you try to do it with mysql client program? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible: Update query within another query's loop?
I am still quite new to MySQL and have a basic question. I am using PHP, so
forgive me if this is more a PHP issue.
I want to perform an update to a specific record based on a condition. In
the outer loop, I have Query1, which returns set Result1. Inside this loop,
I run an UPDATE query which returns Result2.
// executed first query.
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row[id];
// construct a new query
$q2 =UPDATE `table` SET `review` = 1 where id =
'.$recid.';;
$result2 = mysql_query($q2)
}
Is this a valid use of MySQL? I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.
Mark Richards
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Online Backup.
Hi! A hot backup (= online backup) means backing up your database without locking anything for more than a few milliseconds. If you back up a massive, fast changing, database with mysqldump --single-transaction, then -- at least in some theoretical settings -- InnoDB can run out of space in the tablespace, because it cannot purge history in the tablespace before that big mysqldump ends. If you use InnoDB Hot Backup http://www.innodb.com/hotbackup.html, the only limit is the size of the disk(s) where you take the backup. The size of InnoDB's transaction logs, ib_logfiles, does NOT limit the size of transactions you can run during the backup procedure. InnoDB Hot Backup (ibbackup) works like similar tools for Oracle. It copies the tablespace, page by page, and at the same time archives the log that InnoDB writes during the backup process to its log files. The archived log becomes the file ibbackup_logfile in your backup. ibbackup does not communicate with mysqld in any way during the backup process. It does not set any locks. It just reads the data and log files. When we want to take the backup into use, ibbackup applies the archived log to the data files in the backup and in that way rolls the backup forward to a consistent single point in time. When Nils talks about setting table locks, he probably means a new Perl script innobackup http://www.innodb.com/hotbackup.html#innobackup_perl_script. innobackup calls ibbackup and takes a backup of both InnoDB and MyISAM type tables, as well as the .frm files. To back up MyISAM tables it has to call FLUSH TABLES WITH READ LOCK at the end of the backup run, because there is no hot backup procedure available for MyISAM tables. If the MyISAM tables are small, like the 'user' and 'host' system tables in the 'mysql' database, then the locked phase only lasts a couple of seconds, and we can call also innobackup essentially a 'hot backup' tool. Note that mysqldump takes a 'logical' backup of your database, because it dumps the rows into a file in a human-readable form. ibbackup, on the other hand, takes a binary backup of your database. Even if you use ibbackup, you should sometimes make logical dumps because you can easier check that data in a human-readable file is not corrupt. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED]; P Srinivasulu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 7:47 PM Subject: Re: Mysql Online Backup. 2003 8 28 01:43Nils Valentin : Hi Chris, 2003 8 28 00:54Chris Nolan : A very good question! One which I will attempt to provide a half-decent answer for. Trying to execute a transaction that involves grabbing massive amounts of data from various tables while still allowing updates to be committed can be tricky. At best, this means performance problems. Please don't get me wrong - MySQL is the fastest database on the planet (and it looks to only get faster, with additional query caches, better designed caches and a helper thread architecture in the works) but there are certain things that are just hard to do quickly, even if you are the demigods that work at Innobase Oy and MySQL AB. The advantage of other methods is that you avoid all sorts of nasty locking on various parts of your database. The advantage of the Hot Copy product that Innobase Oy sell is that it doesn't place any locks on your InnoDB table space when it runs. You made an interesting point there with the locking mechanism. I double checked the innodb homepage. http://www.innodb.com/hotbackup.html While it says at the top as you stated no locks are written it also says under 3) how it locks and unlocks the tables. I guess thats best explained by Heikki ;-) - I copied her. Best regards Nils Valentin Tokyo/Japan In summary, it's not a question as to whether the current methods work, it's a question as to how well suited they are to your needs. For mine they work well, but if I was processing 600 queries / second, I think I'd want ibcopy to help me out. Regards, Chris quote who=P Srinivasulu If the backup solutions are there already, why is that a future requirement for Mysql? Thanks, Srinivasulu. Chris Nolan [EMAIL PROTECTED] 8/27/2003 6:41:27 PM Hi! You can currently do this with InnoDB with mysqldump (look at the --single-transaction switch). I use it for backing up a 12 GB mail database without problems. Regards, Chris P.S. Keep up the great work over there at Novell! Tell us where to send the expensive bottles of alcohol for when you help put SCO out of their misery! P Srinivasulu wrote: Hi All, The link below says that online backup
database from win to lin
Hey, I have a question about copying a database that was developed on a windows os and moving it to a Linux server os. Will this work ? Has it been done before ? Thanks, -Dan
RE: database from win to lin
Yes it will work, and it has been done before. Copy the *.MY* files and the *.frm files to the linux server. ---Original Message- --From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] --Sent: Wednesday, August 27, 2003 11:31 AM --To: [EMAIL PROTECTED] --Subject: database from win to lin -- --Hey, -- --I have a question about copying a database that was developed on a --windows os and moving it to a Linux server os. Will this work ? Has it --been done before ? -- --Thanks, -- ---Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database from win to lin
Thanks so much for the reply... - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Dan J. Rychlik' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 2:11 PM Subject: RE: database from win to lin Yes it will work, and it has been done before. Copy the *.MY* files and the *.frm files to the linux server. ---Original Message- --From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] --Sent: Wednesday, August 27, 2003 11:31 AM --To: [EMAIL PROTECTED] --Subject: database from win to lin -- --Hey, -- --I have a question about copying a database that was developed on a --windows os and moving it to a Linux server os. Will this work ? Has it --been done before ? -- --Thanks, -- ---Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database from win to lin
Dan, Dan J. Rychlik said: Hey, I have a question about copying a database that was developed on a windows os and moving it to a Linux server os. Will this work ? Has it been done before ? Thanks, -Dan Yes, I have done it just recently. Just use the mysqldump-command. -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lots of FULLTEXT stuff (suggestions)
Hi! First: thanks for ideas - I'm adding them to my todo :) About dates - it's very difficult to say when a particular feature will be implemented. Anyway, first I'm going to finish with this 2-level index structure - to implement optimizations that rely on it. Any speed/optimization improvements are welcome for gigs of data, especially with IN BOOLEAN MODE (e.g. automagically sorted by relevance like a natural language query, although this is probably difficult if a wildcard* is used?). It's not possible - at least I don't know to do it. In natural language mode the fulltext search is done in in Fulltext initialization stage - as you noticed. So an engine can sort documents on relevance. In boolean mode each found document is returned at once - that's why this search mode is faster, it need not support/keep the list of all matched documents. And the FULLTEXT index shouldn't always be chosen for non-const join types when another index would find less rows first. e.g. ... WHERE MATCH ... AND primary_key IN (1, 2); should use the PRIMARY key, not the FULLTEXT. :-) But maybe that's not possible, since I guess it's a problem auto sorting by relevance if it's not using the FULLTEXT index. Hmm. The logic in making FULLTEXT index always the preferred one is that even if it's not the index as reported by EXPLAIN, it is still used in Fulltext initialization. So, using it in join to retrieve rows adds no extra costs. But now I think that there is still the cost of reading row data from disk, so using PRIMARY/UNIQUE index can be faster in some cases. I am not sure, though, optimizer can take this into account properly - to know the number of matched rows before choosing an index would mean doing fulltext search for EXPLAIN too - I doubt it will be appreciated :) Still, with 2-level index some estimations can be made... Great - thanks for the idea! Anyway, in boolean mode there's no initialization so there's no reasons (besides historical) for it to be preferred - it'll be fixed. To the developers: any word on if and when any of these things would be implemented? I know from the TODO and other list messages that some will. Any *estimates* (in months or MySQL version) on when would be great. Just any info on new full-text features, even ones that I didn't mention, would be awesome to hear. :-) And like how they would be implemented and used by us (syntax, etc.). As I told - it's very difficult to predict this :( Anyway, I doubt anything that requires changing .frm file structure will get into 4.1 How about changing the default min/max (or just min if you want) word length? I think everyone *really* wishes ft_min_word_len was 3. Seems like that and indexing numbers shorter than min_word_len could be easily done. Please? :-) Yes, it's safe enough for 4.1 There Sergei is talking about a new .frm format (plain text) that will allow more of these features. Will it allow us to somehow define how to parse things or something?? Could you elaborate more on what this will bring? In November 2001, he said the new .frm format would be here this year. It's been almost 2 years since then, so when is it do? It's now planned for 5.1 - plain text .frm comes together with complete redesign of internal table structure handling, table structure cache, etc. But even without it .frm format was extended in 4.1 so I don't need it for adding per-index options anymore. Also, are the current MySQL versions using the 2 level full-text index format yet? I'm thinking not? 4.1.0 is using it. This index structure was done to make possible new powerful optimizations. It is these optimizations what is not implemented yet :( It's in my highest-priority todo. Finally, in the full-text TODO, it says Generic user-suppliable UDF preparser. Could you also elaborate on this? The generic part almost makes it sound like some sort of script to define how to parse the text. But UDF makes it sound like a separate thing that has to be loaded with CREATE FUNCTION. But UDFs won't work with your MySQL binaries, will they, since they're complied statically? mysql-max binary is compiled dynamically - so it works with UDF's. And UDF in the todo item does not mean it will MySQL User-Definable Function yet - it could be a Stored Procedure, e.g. The idea is to be able to supply a function (whatever it is) that takes a column's data and returns a list of words that this data contain. It could be used e.g. to fulltext-index pdf's or xml's or MS Word files, or whatever. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB warning in error log
I'm testing my application using MySQL 4.1.0. Before I was using 4.0.14 version. Today I've looked into error file of the 4.1.0 where there was hundreds of new lines with information like this: InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 3686351459 page number 5, 0'th page in dblwr buf. What does it mean ?? Best regards, ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB warning in error log
Marek, do not worry: http://www.innodb.com/ibman.html#InnoDB_history MySQL/InnoDB-4.0.14, July 21, 2003: ... * Fixed the checksum calculation of data pages. Previously most OS file system corruption went unnoticed. Note that if you downgrade from version = 4.0.14 to an earlier version 4.0.14 then in the first startup(s) InnoDB will print warnings: InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 2552202359 page number 8245, 127'th page in dblwr buf. but that is not dangerous and can be ignored. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ Subject: InnoDB warning in error log From: Marek Lewczyk Date: Wed, 27 Aug 2003 22:48:46 +0100 I'm testing my application using MySQL 4.1.0. Before I was using 4.0.14 version. Today I've looked into error file of the 4.1.0 where there was hundreds of new lines with information like this: InnoDB: Warning: an inconsistent page in the doublewrite buffer InnoDB: space id 3686351459 page number 5, 0'th page in dblwr buf. What does it mean ?? Best regards, ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
