MySQL
Hola de nuevo, ahi va otra consulta de MySQL Quisiera que una de mis columnas no tuviera valores repetidos, con lo cual creo que deberia utilizar UNIQUE, y a la vez esta columna no pueda ser nula en función del valor de otra columna. ¿Existe alguna posibilidad? Me explico, tengo dos columnas, una 'tipo de usuario' y otra 'numero de habitacion'. Si el tipo de usuario es '1', el numero de habirtacion puede estar vacio, o mejor dicho, deberia estarlo. Si el tipo de usuario es '2', el numero de habitacion debe estar cumplimentado. Gracias ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL y claves foraneas
At 08:40 13/01/2003 +0100, Vicente Valero wrote: Hola, Hola, Tengo la versión 3.23.54 instalada sobre Windows. He esta leyendo algunos documentos acerca de el soporte de claves foraneas y he visto que para ello dicha versión necesita un patch llamado MySQL-MAX. En la sección de downloads he visto que existe la versión 3.23.54-MAX, ¿es este el patch necesario? No. Por favor leya las explicaciones que expongo abajo. En tal caso he visto que no es ningun archivo instalable, sino que en su interior hay carpetas llamadas como lib, include, bin... ¿Qué debo hacer con ellas, copiarlas sobre la carpeta de MySQL y rearrancar el servidor?. En nuestra sección de download, existen dos tipos: el que ya está listo para usarlo directamente y el llamado source que necesita ser compilado para usarlo, este último generalmente es usado por programadores que tienen el compilador VC++. Tu tienes que usar la versión que es denominada: mysql-3.23.54-win.zip nota que la versión source es denominada: mysql-3.23.54-win-src.zip. Descompacta el archivo en un directorio temporario y rueda el archivo setup.exe. Recomiendo que lo instales en el directorio patrón c:\mysql. Abre una tela DOS y vá al directorio c:\mysql. Alli tu encuentras unos archivos con una extensión .cnf. Esos archivos sirven para usarlo como ejemplo para tú hacer el archivo de configuración my.ini. Entonces puedes hacer una cópia de la siguiente manera: copy my-medium.cnf c:\windows\my.ini ó de acuerdo con tu sistema operativo: copy my-medium.cnf c:\winnt\my.ini luego, en el mismo lugar: edit c:\windows\my.ini Remueve los # de las siguientes claves bajo la sección [mysqld] basedir=c:/mysql datadir=c:/mysql/data innodb_data_file_path = ibdata1:400M innodb_data_home_dir = c:\ibdata innodb_log_group_home_dir = c:\iblogs innodb_log_arch_dir = c:\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 ---final del archivo my.ini--- Ahora crea 2 directorios: mkdir c:\ibdata mkdir c:\iblogs Como tu puedes notar para tener las claves extranjeras tu necesitas usar el tipo de tabla llamada InnoDB. También nota que arriba existen valores de memoria y tamaño de archivos que tu puedes adaptar a los recursos que tu computadora tiene o a tus necesidades. Estando lo de arriba listo, tu necesitas ahora arrancar un servidor MySQL del tipo MAX. Cambia para el directorio llamado c:\mysql\bin y verifica que existan los servidores abajo relacionado: mysqld-max.exe (para usarlo en Windows 9x o ME) mysqld-max-nt.exe (para usarlo en NT/Win2000/WIn XP) Ahora arranca el servidor con el comando: c:\mysql\binmysqld-max-nt --standalone --console y tu verás que InnoDB muestra lo abajo: InnoDB: The first specified data file C:\ibdata\ibdata did not exist: InnoDB: a new database to be created! 030113 6:29:14 InnoDB: Setting file C:\ibdata\ibdata size to 51 MB InnoDB: Database physically writes the file full: wait... 030113 6:29:19 InnoDB: Log file C:\iblogs\ib_logfile0 did not exist: new to be created InnoDB: Setting log file C:\iblogs\ib_logfile0 size to 16 MB InnoDB: Database physically writes the file full: wait... 030113 6:29:21 InnoDB: Log file C:\iblogs\ib_logfile1 did not exist: new to be created InnoDB: Setting log file C:\iblogs\ib_logfile1 size to 16 MB InnoDB: Database physically writes the file full: wait... 030113 6:29:23 InnoDB: Log file C:\iblogs\ib_logfile2 did not exist: new to be created InnoDB: Setting log file C:\iblogs\ib_logfile2 size to 16 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 030113 6:29:34 InnoDB: Started mysqld-max-nt: ready for connections Si tu quieres parar el servidor tu haces el comando en otra tela DOS: c:\mysql\binmysqladmin shutdown si por acaso tu sistema operativo es Win2000/NT/XP, tu puedes arrancar y parar el servidor usando el servicio, para eso primero lo instalas así: c:\mysql\binmysqld-max-nt --install Service successfully installed. si tu quieres que no sea arrancado automacticamente en el arranque de la computadora: c:\mysql\binmysqld-max-nt --install-manual Service successfully installed. para remover el servicio tu ejecutas: c:\mysql\binmysqld-max-nt --remove Service successfully removed. y para arrancar el servicio: c:\mysql\binnet start mysql O serviço de MySql está sendo iniciado. O serviço de MySql foi iniciado com êxito. y para pararlo: c:\mysql\binnet stop mysql O serviço de MySql está sendo finalizado . O serviço de MySql foi
Re: Replication bug?
On Mon, Jan 13, 2003 at 09:41:12AM +1000, Jason Brooke wrote: No, I've been ignored on this problem for 18 months now, for some reason. Quite peculiar. The limitations of replicate-do-db are documented in: http://www.mysql.com/doc/en/Replication_Options.html I found out about this limitation the hard way, just like you seem to have done. Since we don't control the scripts that use the database, we couldn't use replicate-do-db. It just takes too much time to fix the problems when someone forgets about this limitation. Regards, Fred. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: 'Jason Brooke' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 9:16 AM Subject: RE: Replication bug? Did you ever get any confirmation that it will be added to the official bug list? -Original Message- From: Jason Brooke [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 12, 2003 3:14 AM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Replication bug? Yes this is the same issue I've reported previously. Unless literally 'select' the database, the query is never written to the binary log. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 12:38 PM Subject: Replication bug? I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Book recomendations
MySQL from New Riders, author Paul DuBois HTH, Ganbold At 08:34 AM 1/8/2003 -0500, you wrote: I got a gift certificate for book store. I am looking for good MySql books with reference, examples, etc? Anyone have some ideas on good books to look at Chuck = Incoming rounds always have the right of way - Rules of War - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL on large server
This is what I have in /etc/my.cnf on my current MySQL server (dual AthlonMP,2GB RAM) [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking log-slow-queries = slowqueries.log set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=1024 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=24 set-variable= max_connections=510 set-variable = thread_concurrency=16 set-variable = myisam_sort_buffer_size=64M set-variable= tmp_table_size=128M set-variable = wait_timeout=90 Whilst monitoring the server using 'mytop' is can see the server using between 250 400 threads, never dropping below 210 threads ('top' reports about 15-20MB RAM free) 'extended-status' reports: Max_used_connections = 480 And these numbers are going to grow as the server gets busier, hence the Dual Xeon server with 6GB RAM. Instead of added more h/w in the future though, I'd like to be able to squeeze more out of Linux/MySQL I used a C program (thread-limit.c) which tests the amount the threads Linux can create (am I correct in assuming these are the same thing?) (I can send the 4k file if required) On my server, the program reports 255 threads max which I believe is down to glibc... The MySQL binaries are reported to handle 1000-1500 connections. I was wondering if anybody has achieved this building with source? If so, what was involved? (version of libs, etc were used?) Possibly of relevance are the results of running 'thread-limit' against various platforms (glibc versions were taken from 'rpm -q'; 'qpkg' in the case of Gentoo) Distro | kernel | glibc | threads created before failure Mandrake 8 | 2.4.19-16mdk | 2.2.5-16mdk| 255 Gentoo | 2.4.19-gentoo-r9 | 2.2.5-r7 | 1021 RedHat 7.2 | 2.4.19-2 (custom)| 2.2.5-42 | 255 Unfortunately, I'm not ready to deploy Gentoo on a production server, but they're doing something differently... Many thanks From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql list [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: MySQL on large server Date: Thu, 9 Jan 2003 14:36:38 -0800 On Thu, Jan 09, 2003 at 08:55:36AM +, mysql list wrote: Hi, I was wondering if anybody has built MySQL 3.23 from source that can handle a high number of connections threads. I've have tried MySQL binaries in the past (not RPMs), but these have had stability/load problems. When building from source I don't have those issues, but I am limited by the inbuilt limits (of glibc,etc...) How many connections do you need? If memory serves, the master db behind Slashdot was handling around 700 connections on a 4 processor P3 Xeon with 4GB RAM. But it's been a while since I've talked with Brian about it. I need to build MySQL 3.23 on a production server running RedHat 7.2, patched glibc (2.2.5-42) and a custom kernel (2.4.19-2 SMP). Hardware contains Dual Xeon 2.4GHz (hyperthreading disabled) and 6GB RAM. With that kind of RAM and horespower you should be able to go well above 500 connections. I'd say more on this topic, but I've not really needed to push MySQL in that direction. We tend to use more boxes that cost less and replicate data. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 25 days, processed 863,460,485 queries (388/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
Hi. On Sun 2003-01-12 at 22:01:37 -0500, [EMAIL PROTECTED] wrote: On Sun, Jan 12, 2003 at 08:12:35PM -0700, Rodney Broom wrote: I'm trying to delete 5 million rows... [...] If I was deleting things regularly, I'd have to delete maybe a couple hundred thousand rows every day. [...] When I say blocked, I mean e.g. another process tries to SELECT from another table in the database, but it takes way too long. If you are absolutely sure that the other queries don't related to the deleting query at all, it means they are slow, because your disks are too stressed. Any suggestions on how I can delete those rows without causing a lot of downtime? As http://www.mysql.com/doc/en/DELETE.html suggests, you can use LIMIT with DELETE in order to restrict the time a DELETE needs by running it in batches. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to insert entire text file in a table column
Haisam, I want to insert into content an entire text document. How do I do that without puting the entire content into a variable, let's say in perl, and then writing the INSERT statement. In MySQL, you have the LOAD_FILE() function to do this: http://www.mysql.com/doc/en/String_functions.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Haisam K. Ido [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 11:41 PM Subject: How to insert entire text file in a table column I have the following table, CREATE TABLE cp ( id INT(11) NOT NULL auto_increment, fileVARCHAR(128) NOT NULL, content LONGTEXT, UNIQUE KEY keyword(id,file), PRIMARY KEY (id) ) TYPE=INNODB; I want to insert into content an entire text document. How do I do that without puting the entire content into a variable, let's say in perl, and then writing the INSERT statement. Is there a way to give the path to a file and for mysql to use that path to insert the content? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems while compiling mysql on HP-UX 11.11
Hello, please help me. I try to compile mysql 3.23.53 on HP-UX 11.11 and get the following errer: mysql-3.23.54/readline# gmake source='rltty.c' object='rltty.o' libtool=no \ depfile='.deps/rltty.Po' tmpdepfile='.deps/rltty.TPo' \ depmode=gcc3 /bin/sh ../depcomp \ gcc -DUNDEF_THREADS_HACK -DHAVE_CONFIG_H -DNO_KILL_INTR -I. -I. -I.. -I./../include -I./.. -I.. -D__STDC_EXT__ -O3 -DDBUG_OFF -DHAVE_BROKEN_PREAD -DDONT_USE_FINITE -DHAVE_BROKEN_GETPASS -DNO_FCNTL_NONBLOCK -DDO_NOT_REMOVE_THREAD_WRAPPERS -DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT -c `test -f rltty.c || echo './'`rltty.c command line:1:1: warning: __STDC_EXT__ redefined command line:1:1: warning: this is the location of the previous definition In file included from /usr/include/sys/user.h:52, from /usr/include/sys/stream.h:53, from rlwinsize.h:45, from rltty.h:45, from rltty.c:44: /usr/include/machine/sys/setjmp.h:45: redefinition of `struct label_t' gmake: *** [rltty.o] Error 1 Mit freundlichen Grüßen Nico Grothe ___ Siemens Business Services GmbH Co OHG SBS ITS ORS AHS 512, Mch P System Support UNIX Otto-Hahn-Ring 6 / Raum 48/430 D - 81739 München / Germany Tel: +49 89 636-45586 Fax:+49 89 636-43997 MailTo:[EMAIL PROTECTED] ___ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL y claves foraneas
Vicente, the language on this list is English. I will try to answer your question, anyway. The patch you're speaking of is no patch, but another table handler in MySQL. Native tables are MyISAM, and MySQL has a number of alternative tables it can handle. One of them is InnoDB. In MySQL, you can use MyISAM and InnoDB together. If you need features like foreign keys, the MyISAM table handler cannot handle this (this is planned for the future, anyway). Momentarily, you will have to use InnoDB for features like this. I am not informed which operating system you use, but if it's Windows, you will find a number of MySQL servers in the bin/ directory (e.g. c:\mysql\bin). If you start mysqld, you will have no InnoDB tables activated, and thus no foreign key support. To enable InnoDB, you will have to start mysql-max (or, on NT systems, mysqld-max-nt). Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 8:40 AM Subject: MySQL y claves foraneas Hola, Tengo la versión 3.23.54 instalada sobre Windows. He esta leyendo algunos documentos acerca de el soporte de claves foraneas y he visto que para ello dicha versión necesita un patch llamado MySQL-MAX. En la sección de downloads he visto que existe la versión 3.23.54-MAX, ¿es este el patch necesario? En tal caso he visto que no es ningun archivo instalable, sino que en su interior hay carpetas llamadas como lib, include, bin... ¿Qué debo hacer con ellas, copiarlas sobre la carpeta de MySQL y rearrancar el servidor?. Gracias ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Problem with query cache and comments
I may add, that it is always the same comment, so the query is exactly the same and SHOULD be cached. -Ursprungliche Nachricht- Von: Christian Rabe [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 12. Januar 2003 01:41 An: [EMAIL PROTECTED] Betreff: Problem with query cache and comments Hi, I've been testing the query cache with mysql 4.0.8 and ran into a problem. the query select * from tablename where x=1; is cached correctly and generates nice hits when run more than once but if I add a comment like /* any comment */ select * from tablename where x=1; the query is cached, but there are no more hits I dont think this is intended ... Please reply or CC me directly, because I'm not on the list atm. Thanx and Regards, Christian Rabe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Running out of memory - memory leakage?
Jeremy Zawodny wrote: On Fri, Jan 10, 2003 at 01:59:07PM +0100, Csongor Fagyal wrote: Hi, I have a RedHat 8 box /w MySQL 3.23.54, /w 640M RAM. It looks like there are some sort of a memory leakage somewhere in the system, because slowly (in a day or two) I run out of memory. Even SWAP space gets used up at the end (slowly but steadily, fluctuating). If I restart MySQL, nothing happens. If I restart the services using MySQL (e.g. Apache), nothing happens. But if restart BOTH of them (MySQL AND Apache) suddenly my memory is back again and the SWAP is reclaimed also. How can I find out where my memory goes? Any ideas? Without seeing your my.cnf file, it is dificult to speculate on the cause. Jeremy Hi, I have two MySQL daemons running, one on port 3306 (mysql), one on port 3307 (mysql-user). Here are the configs: my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb set-variable= table_cache=256 set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=16M set-variable= thread_cache=4 log=/var/log/mysqld-query.log log-slow-queries=/var/log/mysqld-slow.log [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid my-user.cnf: [client] port=3307 socket=/var/lib/mysql-user/mysql-user.sock [mysqld] datadir=/var/lib/mysql-user socket=/var/lib/mysql-user/mysql-user.sock pid-file=/var/run/mysqld-user/mysqld-user.pid port=3307 skip-innodb #max_connections=300 set-variable= table_cache=256 set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=16M set-variable= thread_cache=4 log=/var/log/mysqld_user-query.log log-slow-queries=/var/log/mysqld_user-slow.log [mysql.server] user=mysql-user basedir=/var/lib [safe_mysqld] user=mysql-user err-log=/var/log/mysqld-user.log pid-file=/var/run/mysqld-user/mysqld-user.pid port=3307 socket=/var/lib/mysql-user/mysql-user.sock [mysql_install_db] err-log=/var/log/mysqld-user.log pid-file=/var/run/mysqld-user/mysqld-user.pid port=3307 socket=/var/lib/mysql-user/mysql-user.sock - Cs. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql / innodb foreign keys
# - PLEASE HELP -- # hi to all, is it still true that mysql/innodb needs explicit index creation on foreign keys? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) how can i create the database without creating explicitly an index on each foreign keys of my database? any suggestions are appreciated. thanks to all. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem to access MySQL via MySQLFront
The problem is with the new glibc-2.2.5-40 package released on October 3, 2002. Apparently its a known issue, and people are aware of it. The best solution I've seen so far is to role back to a previous version of this package, and its dependencies. If you are a redhat member, you have access to the errata information, as well as, the previous versions of updated binary rpms. (so you don't have to recompile anything) I downloaded glibc-2.2.5-39, and associated files. To role back the rpm, use the following command: (This was the command I used) rpm -U --oldpackage --force filename (I think you can simplify the command to:) rpm -Uvh --oldpackage filename (but I haven't tried it) MySQL-Front now seems to work fine for me. Hope this helps, Dean Householder http://mysqlfront.venturemedia.de/index.php?act=STf=8t=434 - Original Message - From: David Rayroud [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 7:14 AM Subject: Problem to access MySQL via MySQLFront Hi, Here is my configuration : Linux SME Server 5.6 bêta 7 Mysql 3.23.49 I try to access to Mysql via a client programm nammed MysqlFront. I have defined an user like this : Username : blank Host : .domain_name.ch Password : blank When I try to connect to MySQL, this error message come : MySQL - Error Connection failed: 2013 - Lost connection to MySQL server during query But I try to put into /etc/hosts IP mypc.domain_name.ch mypc and I retry to connect to MySQL via MySQLFront, and all is OK. I will not declare all of the PC into /etc/hosts. Is there a solution ? I browse the MySQL Documentation but I didn't find something. Thank you for your help and excuse for my poor English. David Rayroud - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
More: What's returned from $sth-execute(); ?
Hi, I see your point re: Some code to illustrate: printf Query: %s\n, $query; my $sth = $dbh-prepare ($query); $sth-execute(); # metadata information becomes available at this point ... printf NUM_OF_FIELDS: %d\n, $sth-{NUM_OF_FIELDS}; print Note: query has no result set\n if $sth-{NUM_OF_FIELDS} == 0; but what I really want to do is determine at an early stage if I have any results at all in my result set. Actual reason for this is that I'm doing a SELECT WHERE query and scanning my entrire DB for a session ID that matches one read from a cookie. I then want to determine if this is a valid session id and if not re-direct the user to a page saying unrecongnised session id or something. Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem with last_id
I have a problem with a query: I use an application where users can post messages. The messages are stored in two tables that are related by the id of the new created message. I get the last id with the following query: select max(id) as maxid from tasker_app The forum has many users and sometimes it happens that there are multiple inserts before I can receive the last id. Is it possible to stop inserts until I have finished both inserts? Thank you, Richard Brenner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: unexpected: 260 rows in set on a TINYINT
I would suggest appending an ORDER BY atomtype first of all. That's what I did, see original select statement: mysql select distinct atomtype from `gentradedb`.`gpatomdb` order by atomtype; You could also experiment with SELECT DISTINCT to remove any dupes from your output. Experiment how? Also, don't confuse the RANGE of values that the atomtype can be (0-255) if set to be UNSIGNED with the NUMBER OF ROWS in your output. The TINYINT is working. You have no values greater than 255 in there. You just have duplicates which gives you more rows than you were expecting I think. ;-) I don't think I do, the database has several millions rows. Thanks for pointing out the dups, isn't what select distinct is meant to do? Terry - I would suggest appending an ORDER BY atomtype first of all. I say this because if you scan your results, you have duplicates... | 165 | | 166 | | 167 | --- | 168 | | 169 | | 170 | | 171 | | 172 | | 173 | | 174 | | 175 | | 167 | --- | 175 | So you prolly have other in there too. You could also experiment with SELECT DISTINCT to remove any dupes from your output. DÆVID. -Original Message- From: Horizon [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 11, 2003 7:46 PM To: Mysql List Subject: unexpected: 260 rows in set on a TINYINT mysql select distinct atomtype from `gentradedb`.`gpatomdb` order by atomtype; +--+ | atomtype | +--+ |0 | |1 | |2 | |3 | |4 | |5 | |6 | |7 | |8 | |9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | | 31 | | 32 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | | 51 | | 52 | | 53 | | 54 | | 55 | | 56 | | 57 | | 58 | | 59 | | 60 | | 61 | | 62 | | 63 | | 64 | | 65 | | 66 | | 67 | | 68 | | 69 | | 70 | | 71 | | 72 | | 73 | | 74 | | 75 | | 76 | | 77 | | 78 | | 79 | | 80 | | 81 | | 82 | | 83 | | 84 | | 85 | | 86 | | 87 | | 88 | | 89 | | 90 | | 91 | | 92 | | 93 | | 94 | | 95 | | 96 | | 97 | | 98 | | 99 | | 100 | | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | | 107 | | 108 | | 109 | | 110 | | 111 | | 112 | | 113 | | 114 | | 115 | | 116 | | 117 | | 118 | | 119 | | 120 | | 121 | | 122 | | 123 | | 124 | | 125 | | 126 | | 127 | | 128 | | 129 | | 130 | | 131 | | 132 | | 133 | | 134 | | 135 | | 136 | | 137 | | 138 | | 139 | | 140 | | 141 | | 142 | | 143 | | 144 | | 145 | | 146 | | 147 | | 148 | | 149 | | 150 | | 151 | | 152 | | 153 | | 154 | | 155 | | 156 | | 157 | | 158 | | 159 | | 160 | | 161 | | 162 | | 163 | | 164 | | 165 | | 166 | | 167 | | 168 | | 169 | | 170 | | 171 | | 172 | | 173 | | 174 | | 175 | | 167 | | 175 | | 176 | | 177 | | 178 | | 179 | | 180 | | 181 | | 182 | | 183 | | 184 | | 185 | | 186 | | 187 | | 188 | | 189 | | 190 | | 191 | | 192 | | 193 | | 194 | | 195 | | 196 | | 197 | | 198 | | 199 | | 200 | | 201 | | 202 | | 203 | | 204 | | 205 | | 206 | | 207 | | 208 | | 209 | | 210 | | 211 | | 212 | | 213 | | 214 | | 215 | | 216 | | 217 | | 218 | | 219 | | 220 | | 221 | | 222 | | 214 | | 222 | | 223 | | 224 | | 225 | |
Can't connect to MySQL server Access denied for root
Hi, I removed an old mysql version and I installed a new one on a machine running Suse 8.1 Linux. shellrpm -i MySQL-client-3.23.54a-1.i386.rpm shellrpm -i MySQL-shared-3.23.54a-1.i386.rpm shellrpm -i MySQL-devel-3.23.54a-1.i386.rpm shellrpm -qa | grep -i mysql shellreboot shellsafe_mysqld --user=mysql shellps -ax ... 961 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --user=mysql 977 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql 979 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql 980 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql ... shellmysqladmin -u root password 'new-password' shellmysqladmin -u root -h hostname password 'new-password' mysqladmin: connect to server at 'hostname' failed error: 'Can't connect to MySQL server on 'hostname' (110)' Check that mysqld is running on hostname and that the port is 3306. You can check this by doing 'telnet hostname 3306' shelltelnet hostname 3306 Trying IP_address... telnet: connect to address IP_address: Connection timed out shellmysqladmin -u root password 'new-password' mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' I can only use mysql with mysql user: shellmysql -u mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 3.23.54 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; +--+ | Database | +--+ | mysql | | test | +--+ 2 rows in set (0.00 sec) mysql quit Bye What's the problem ? Help me please. Thanks in advance. Adi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Thanks and SQL Syntax help
I don't know if i understood you very well, but here's a try.. mysql select * from Classes; ++-+ | ID | Name| ++-+ | 1 | XO-312 | | 2 | PA-211a | | 3 | XUL-001 | ++-+ 3 rows in set (0.00 sec) mysql select * from Workshops order by ClassID,Date; ++-++ | ID | ClassID | Date | ++-++ | 1 | 1 | 2002-05-15 | | 8 | 1 | 2002-09-22 | | 7 | 1 | 2002-10-29 | | 2 | 1 | 2003-02-20 | | 3 | 2 | 2002-05-15 | | 9 | 2 | 2003-01-01 | | 4 | 2 | 2003-02-17 | | 5 | 3 | 2002-05-15 | | 10 | 3 | 2002-12-16 | | 6 | 3 | 2003-01-01 | ++-++ 10 rows in set (0.00 sec) mysql select ClassID, MIN(Date) min, MAX(Date) max, Classes.Name - FROM Workshops LEFT JOIN Classes ON (ClassID=Classes.ID) - GROUP BY ClassID HAVING now() BETWEEN min and max; +-+++-+ | ClassID | min| max| Name| +-+++-+ | 1 | 2002-05-15 | 2003-02-20 | XO-312 | | 2 | 2002-05-15 | 2003-02-17 | PA-211a | +-+++-+ 2 rows in set (0.00 sec) Hope this helps... On Sat, 2003-01-11 at 16:25, Steve Lefevre wrote: First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: install problem... (permissions?)
[root@localhost ~]# perror 13 Error code 13: Permission denied Also check the permissions and ownership of /usr/local/var . See the Post installation setup chapter from the manual. http://www.mysql.com/doc/en/Post-installation.html and http://www.mysql.com/doc/en/File_permissions.html On Sat, 2003-01-11 at 22:12, Stefan Hinz, iConnect (Berlin) wrote: Christopher, 030111 13:41:11 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) Did you run the install_db script? If not, MySQL can't find the grant tables. See http://www.mysql.com/doc/en/Post-installation.html for details. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Christopher Bergeron [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 7:29 PM Subject: install problem... (permissions?) Hey guys, I'm having a little problem with my new mysql installation. I built from source, and everything built find and installed okay (I think). Here's the problem: when I start safe_mysqld it just ends. Here's the snippet from my error log: /usr/local/libexec/mysqld: File './proto2-bin.1' not found (Errcode: 13) 030111 13:41:11 Could not use proto2-bin for logging (error 13) 030111 13:41:11 /usr/local/libexec/mysqld: Can't create/write to file '/usr/local/var/proto2.pid' (Errcode: 13) 030111 13:41:11 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 030111 13:41:11 /usr/local/libexec/mysqld: Error on delete of '/usr/local/var/proto2.pid' (Errcode: 13) 030111 13:41:11 mysqld ended Is there an ownership or permission problem here? Can anyone help me out here? Much thanks in advance, CB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Archive and retrieval in MySQL
HI, I have a few questions related to DB archving / restore capability of MySQL. Most of them are design issues but have a lot to do with what MySQL provides. PLease share your view on them. 1. How can i trigger a database archive when a certain data base limit is reached in a table. Ex: when 1,00,000 records is reached, archive to file and clean table. 2. How can i restore multiple files into database ? 3.When i try to view data through my application, how can i trigger the restore the data from Archive file on demand ? Regards Anis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: let users create as much DB as they want ?
--- Amer Neely [EMAIL PROTECTED] wrote: Greetings all. Hope someone could give me some pointers on this. I am working at a small ISP in Montreal. Some of our corpo users. have access to a MySQL database, that we created for them (usually, this DB is named after there username). The way we configured our mysql installation, which seams a pretty standard procedure, they can create as many tables as they want in there DB, but of course, they can't another DB. Well, we would like to do just that. Is there a way to configure mysql so that the user can create as much DB as he wants, by making sure every DB created by this user can only be accessed by him, some kind of 'root' access if you follow my logic. Furthermore, since we are billing our clients for the disk space they are using, will the structure of this will ressemble to this ; MAIN_USER - DB1 - table1, table2, ... - DB2 - table1, table2, ... Thanks for any help. I asked the same question a little while ago and the answer given to me was this: mysql GRANT ALTER,CREATE,DELETE,DROP,INDEX,SELECT,UPDATE ON `user%`.* TO 'user'@'%' IDENTIFIED BY password; Of course you GRANT the user whatever privs you wantt hem to have, I think the only one they NEED to have is CREATE and then user will get to create any DB as long as it starts with their username, like userDB2. And please if anyone sees that I have messed up any thing in this please correct me. HTH, Ryan __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Solaris Performance
Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
max-3.23.54a : ERROR 1135 on IRIX 6.5.16
Description: I'm unable to start more than 1 thread, here are the processes launched, as far as I know, one mysqld daemon is launch per client/connection, but not in this case : root 191563 1443 0 Jan 10 pts/2 0:00 /bin/sh /usr/mysql/bin/safe_mysqld --user=mysql mysql 191591 191563 0 Jan 10 pts/2 3:40 /usr/mysql/bin/mysqld --defaults-extra-file=/usr/mysql/data/my.cnf --basedir=/u How-To-Repeat: launching safe_mysqld or /usr/mysql/bin/mysqld --defaults-extra-file=/usr/mysql/data/my.cnf from whatever config file Fix: not found Submitter-Id: submitter ID Originator:Denis Pugnere Organization: -- Denis Pugnère| IGH/CNRS UPR 1142, 141 Rue de la Cardonille Tel : +33 (0)4 9961.9909 | 34396 Montpellier Cedex 5, France Fax : +33 (0)4 9961.9901 | http://www.igh.cnrs.fr MySQL support:none Synopsis: ERROR 1135 on IRIX 6.5.16 Severity: serious Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.54-max (Official MySQL-max binary) Environment: System: IRIX64 atlas 6.5 04101930 IP27 Some paths: /usr/sbin/perl /sbin/make /home/data/people/denis/bin/gmake /usr/freeware/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/freeware/lib/gcc-lib/mips-sgi-irix6.5/3.0.4/specs Configured with: ../configure --prefix=/usr/freeware --enable-version-specific-runtime-libs --disable-shared --enable-threads --enable-haifa Thread model: single gcc version 3.0.4 Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='g++' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -r-xr-xr-t1 root sys 2335588 Jun 14 2002 /lib/libc.so.1 lrwxr-xr-x1 root sys 19 May 17 2002 /usr/lib/libc.so - ../../lib/libc.so.1 lrwxr-xr-x1 root sys 19 May 17 2002 /usr/lib/libc.so.1 - ../../lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex' '--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile' '--disable-shared' '--with-berkeley-db' '--with-innodb' 'CC=gcc' 'CFLAGS=-O3 -fno-omit-frame-pointer' 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' Perl: This is perl, version 5.004_04 built for irix-n32 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The diference about raw devices and disk partitions
I'm confused , what is the difference and raw device and a disk partition ? ex: on innodb I'm using /dev/sda1 for raw device, is it correct ? tnks. sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: MySQL 4.0.7-gamma: Bugs affecting the privilege system
On Wednesday 08 January 2003 22:32, Stefan Hinz iConnect \(Berlin\) wrote: Have you tried to reproduce them? Bug #1: GRANT USAGE doesn't seem to create a user: mysql GRANT USAGE ON *.* TO 'foo'@'foohost'; Query OK, 0 rows affected (0.26 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; Empty set (0.03 sec) mysql SELECT Host, User, Password FROM mysql.user WHERE User = 'foo'; +-+--+--+ | Host| User | Password | +-+--+--+ | foohost | foo | | +-+--+--+ 1 row in set (0.04 sec) Bug #2: SHOW GRANTS shows wrong GRANT statement: mysql GRANT USAGE ON `footable`.* TO 'foo'@'foohost' IDENTIFIED BY 'foobar' - WITH GRANT OPTION; Query OK, 0 rows affected (0.03 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; +--- | Grants for foo@foohost +--- | GRANT USAGE ON *.* TO 'foo'@'foohost' IDENTIFIED BY PASSWORD '4655c05b05f1 | GRANT ON `footable`.* TO 'foo'@'foohost' WITH GRANT OPTION +--- 2 rows in set (0.00 sec) Yes, I can confirm both bugs you encountered. My settings: Server version: 4.0.7-gamma-max-nt-log OS: Win2K SP2 Maybe worth mentioning, a database 'footable' doesn't exist on my machine. Double-checking this, I found out it doesn't matter if a database exists or not. Bug #2 happens with database 'test', too. Regards, Thank you for bug reports, both bugs are fixed. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OLEDBSQL connection
Hi, I wonder if someone can paste a OLEDBSQL provider connection string to look at . Many thanks.. Sam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Select SUM Function
I understand the SQL statement in MySQL to be SELECT sum(column name) FROM table; but I would like to get a SUM of multiple columns. Is this possible?? Thanks, Ryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL v4.0.8 bug report
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.8-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux testserver.suk.net 2.4.20-grsec #2 Mon Jan 13 14:27:39 CET 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-share d --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexi t Thread model: posix SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: I've installed MySQL v4.0.8 chrooted like described in detail http://www.linuxquestions.org/questions/showthread.php?threadid=34338 Operating System is RedHat Linux v8.0 including ALL current available patches! As example I used the my-medium.cnf example configuration file ... and changed the path variables concluding to the variables ... the mysql root user did not have a passwort currently. The error occured right after I ran FLUSH PRIVILEGES (from the console) ... Kernel is v2.4.20 with the grsecurity patchset. How-To-Repeat: mysql -p use mysql; useradd ... flush privileges; (then the crash occurs right after flush privileges) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.8-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux testserver.suk.net 2.4.20-grsec #2 Mon Jan 13 14:27:39 CET 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-redhat-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='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Jan 10 15:50 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50
Re: problem with last_id
Hi, IMHO: I think you should try : 1) SET @a:=0; LOCK TABLE your_table_name WRITE; INSERT what_client_must_to_insert SELECT @a:=MAX(ID) FROM tableetc UNLOCK TABLE; After unlock the table you can find the value of variable @a by doing: SELECT @a; 2) INSERT what_client_must_to_insert SELECT LAST_INSERT_ID(); Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Richard Brenner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 1:50 PM Subject: problem with last_id I have a problem with a query: I use an application where users can post messages. The messages are stored in two tables that are related by the id of the new created message. I get the last id with the following query: select max(id) as maxid from tasker_app The forum has many users and sometimes it happens that there are multiple inserts before I can receive the last id. Is it possible to stop inserts until I have finished both inserts? Thank you, Richard Brenner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: PHP4 Secure connection parameters
Thank you for your responses Paul, To get MySQL4 to use OpenSSL I had to install OpenSSL 0.9.7 and modify the configure script to move my new include and lib directories to the end of the loop where it's searches for the OpenSSL files so it would see the 0.9.7 version last and therefore use it. When I tried to specify my 0.9.7 library using different variations of --with-openssl=/usr/local/ssl etc. I got many errors. As I look through the configure script I don't see how it can correctly handle an OpenSSL directory specified with --with-openssl because you really need to specify 2 dirs, one for include and one for lib. I have no idea why OpenSSL 0.9.6 didn't work for me - the manual says it should. Now I just need to understand how to connect to MySQL securely from my website using PHP from an ISP. Thanks, David Rock -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 11, 2003 6:26 PM To: David Rock; [EMAIL PROTECTED] Subject: RE: PHP4 Secure connection parameters At 16:52 -0700 1/11/03, David Rock wrote: Show variables says have_openssl = NO for the binary version. I compiled and installed the source version using --with-vio --with-openssl=/usr/bin Try --with-openssl without any argument and see if configure finds your OpenSSL installation location by itself. If not, use the pathname to the directory where the libraries, header files, etc. are located. The directory you supplied is the one where the openssl program is located, which isn't what configure wants. To see what kinds of things configure is looking for, pull it into an editor and sure for openssl. You'll see which directories it looks at trying to determine your OpenSSL installation location. and it still says have_openssl = NO. I'm not sure what path -with-openssl is expecting but other than that I'm not sure where to look next. FreeBSD 4.6 says OpenSSL is installed as part of the base system and indeed it does seem to be installed, or at least something called the OpenSSL Toolkit is installed and I have the /usr/bin/openssl command available. I'm not getting any errors in the mysqld log when I start the server, just no SSL support. I'm hoping someone can throw some thoughts out here. Thanks, David -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 11, 2003 12:07 PM To: David Rock; [EMAIL PROTECTED] Subject: Re: PHP4 Secure connection parameters At 9:09 -0700 1/11/03, David Rock wrote: I'm trying to use SSL connections with MySQL 4.0.8-MAX and FreeBSD 4.6 using the online manual and have a question about section 4.3.9.2 Requirements Configure MySQL with --with-vio --with-openssl. Since I installed the MAX version isn't it already SSL ready? Where do I add the --with-vio --with-openssl parameters and does --with-openssl need a path also? What should it point to? Any help appreciated. --with-vio and --with-openssl are for configuring from source. If you installed a -max server from a binary version, those options don't apply. To see whether your server is SSL capable, use this query: mysql SHOW VARIABLES LIKE 'have_openssl'; +---+---+ | Variable_name | Value | +---+---+ | have_openssl | YES | +---+---+ If it is, you'll see YES. If not, you'll see NO or no output at all. Thanks, David Rock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: problem with last_id
On Monday 13 January 2003 13:50, Richard Brenner wrote: I use an application where users can post messages. The messages are stored in two tables that are related by the id of the new created message. I get the last id with the following query: select max(id) as maxid from tasker_app The forum has many users and sometimes it happens that there are multiple inserts before I can receive the last id. Is it possible to stop inserts until I have finished both inserts? 1. You can lock table with LOCK TABLES statement: http://www.mysql.com/doc/en/LOCK_TABLES.html 2. You can use auto_increment column and LAST_INSERT_ID() function to retrieve last inserted value from the thread: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Initiating secure connection to MySQL4 from PHP
Could someone please post the basics on connecting to a MySQL 4 server from PHP at my website using a secure connection? The server is ready to accept a connection, I'm just not sure what to do next to initiate it. It seems like I would need to set something up at our ISP to talk securely to the remote MySQL server but not sure what. Thanks, David Rock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
Set up a loop where you delete with a LIMIT, pause a few milliseconds to give others a chance to get in, and loop until no records are deleted. Philip Mak wrote: I have a table in a database. I'm trying to delete 5 million rows from it. But whenever I try to do this, while the delete command is executing access to the other tables in the database are blocked (which freezes up a website that runs off that database, even though that website doesn't use the table I'm deleting from)! Any suggestions on how I can delete those rows without causing a lot of downtime? I'm using 3.23.47. Would it help if I upgraded to 4.x? sql (stupid filter...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.users read only?
Does the user 'mysql' have write access to the mysql database and files? Loren McDonald wrote: Im not sure what has gone wrong but the mysql.users table is set as read only. I am trying to grant user privileges but cant because of this. How can I change the status so that I can add to it? I am logging in (localhost) using the (unchanged default) u root account, so I know thats not the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Archive and retrieval in MySQL
Ahmed, 1. How can i trigger a database archive when a certain data base limit is reached in a table. Ex: when 1,00,000 records is reached, archive to file and clean table. You will have to wait for MySQL 5.0. This version will have SQL triggers. 2. How can i restore multiple files into database ? I am not sure if I get you right. If you mean how to restore backups, it's easy with MySQL: 1. Make the backups using mysqldump, e.g. mysqldump --all-databases backupfile 2. Restore what's in backupfile: mysql backupfile Have a look at http://www.mysql.com/doc/en/mysqldump.html for details. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Ahmed S K Anis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 2:00 PM Subject: Archive and retrieval in MySQL HI, I have a few questions related to DB archving / restore capability of MySQL. Most of them are design issues but have a lot to do with what MySQL provides. PLease share your view on them. 1. How can i trigger a database archive when a certain data base limit is reached in a table. Ex: when 1,00,000 records is reached, archive to file and clean table. 2. How can i restore multiple files into database ? 3.When i try to view data through my application, how can i trigger the restore the data from Archive file on demand ? Regards Anis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
outer join syntax
I would like to return rows, with specific columns, for each individual in my NAMES table, with address and e-mail if these exist. Thought I had the syntax down but I'm getting NULL results where I know there is data. I'm used to using SQL Server shortcut * for outer joins, guess that's catching up with me now :( I would have thought the syntax would be: SELECT A.LAST_NAME, A.FIRST_NAME, B.CITY, B.STATE, C.EMAIL FROMNAMES_TBL A LEFT JOIN ADDRESS_TBL B ON A.DBID = B.DBID LEFT JOIN EMAIL_TBL C ON A.DBID = C.DBID WHERE A.LAST_NAME = 'Smith' Am I doing it wrong? chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql v4.0.8 crashes after flush privileges
Description: I've installed MySQL v4.0.8 chrooted like described in detail at http://www.linuxquestions.org/questions/showthread.php?threadid=34338 Operating System is RedHat Linux v8.0 including ALL current available patches ... As example I used the my-medium.cnf example configuration file ... and changed the path variables concluding to the variables ... the mysql root user did not have a passwort currently. The error occured right after I ran FLUSH PRIVILEGES (from the console) ... Kernel is v2.4.20 with the grsecurity patchset. How-To-Repeat: mysql -p use mysql useradd ... flush privileges; # crash Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.8-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux testserver.suk.net 2.4.20-grsec #2 Mon Jan 13 14:27:39 CET 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-redhat-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='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Jan 10 15:50 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: MySQL 4.0.7-gamma: Bugs affecting the privilege system
Victoria, Thank you for bug reports, both bugs are fixed. That's fast :-)) What about the LOCAL bug (or feature)? I.e. you can start mysqld --local-infile=1, or you can start MySQL Monitor like mysql --local-infile=1, or even both, and it won't help. You cannot use load data LOCAL. Furthermore, client tools report that the option local-infile is unrecognized, but the manual says it's a valid option: For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option -- local-infile[=1], or disabled with --local-infile=0. Well, here's the output: c:\mysql\binmysql --local-infile=1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.7-gamma-max-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql USE test; Database changed mysql LOAD DATA LOCAL INFILE 'f:/import.txt' INTO TABLE t; ERROR 1148: Das used Kommando ist mit dieser MySQL Version nicht erlaubt Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 2:55 PM Subject: re: Re: MySQL 4.0.7-gamma: Bugs affecting the privilege system On Wednesday 08 January 2003 22:32, Stefan Hinz iConnect \(Berlin\) wrote: Have you tried to reproduce them? Bug #1: GRANT USAGE doesn't seem to create a user: mysql GRANT USAGE ON *.* TO 'foo'@'foohost'; Query OK, 0 rows affected (0.26 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; Empty set (0.03 sec) mysql SELECT Host, User, Password FROM mysql.user WHERE User = 'foo'; +-+--+--+ | Host| User | Password | +-+--+--+ | foohost | foo | | +-+--+--+ 1 row in set (0.04 sec) Bug #2: SHOW GRANTS shows wrong GRANT statement: mysql GRANT USAGE ON `footable`.* TO 'foo'@'foohost' IDENTIFIED BY 'foobar' - WITH GRANT OPTION; Query OK, 0 rows affected (0.03 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; +--- | Grants for foo@foohost +--- | GRANT USAGE ON *.* TO 'foo'@'foohost' IDENTIFIED BY PASSWORD '4655c05b05f1 | GRANT ON `footable`.* TO 'foo'@'foohost' WITH GRANT OPTION +--- 2 rows in set (0.00 sec) Yes, I can confirm both bugs you encountered. My settings: Server version: 4.0.7-gamma-max-nt-log OS: Win2K SP2 Maybe worth mentioning, a database 'footable' doesn't exist on my machine. Double-checking this, I found out it doesn't matter if a database exists or not. Bug #2 happens with database 'test', too. Regards, Thank you for bug reports, both bugs are fixed. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Select SUM Function
* Stevens, Ryan I understand the SQL statement in MySQL to be SELECT sum(column name) FROM table; but I would like to get a SUM of multiple columns. Is this possible?? Yes. You can add multiple columns inside the SUM() function: SELECT SUM(col1+col2+col3) FROM table; -- Roger sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The diference about raw devices and disk partitions
Hi, Since you don't specify your OS, I assume you are using Linux; if you are using another UNIX like system (BSD, Solaris,...), my explanations below may be useless :( A disk partition (as /dev/sda1) is a bloc device,this is not the same as a raw device. You can get more info in Linux HOWTO: http://www.linux.org/docs/ldp/howto/SCSI-2.4-HOWTO/rawdev.html The main difference is that a bloc device is accessed through the filesystem cache while accesses to a raw device bypass the cache. However, I have not yet tried raw devices with MySQL/InnoDB, so I can't give you more MySQL specific advice. Hope this helps, Joseph Bueno NetClub Dyego Souza do Carmo wrote: I'm confused , what is the difference and raw device and a disk partition ? ex: on innodb I'm using /dev/sda1 for raw device, is it correct ? tnks. sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Solaris Performance
Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL4 SSL certificate required?
On Thu, 2003-01-09 at 17:40, David Rock wrote: Hello, Can someone give me a some overview information regarding using SSL with MySQL version 4? I want to setup a 128bit SSL connection between our website hosted at an ISP and our MySQL (on FreeBSD) server which is hosted in our office. Basically I want the same level of security that we currently have between a web browser and our website. Does this require us to purchase another certificate from a CA similar to the one we already have installed at our ISP or can MySQL use the same one? Is a certificate even needed to do this? Do we need to install a certificate on the MySQL server? Any guidance is greatly appreciated. Do not purchase anything. You don't have to. Look http://www.mysqldeveloper.com/4.x-bk_tree/SSL/NOTES for use by example. You can use as long keys as you want and openssl supports. You need openssl to be installed (usually it is on every Linux, do not know about FreeBSD). I wrote original SSL part of MySQL and not sure if something have changed from this time. Shown file contains cutpaste from my console when I set MySQL up and running with SSL. Tõnu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Select SUM Function
SELECT SUM(colA+colB) FROM TABLE ? Stevens, Ryan wrote: I understand the SQL statement in MySQL to be SELECT sum(column name) FROM table; but I would like to get a SUM of multiple columns. Is this possible?? Thanks, Ryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
java.sql.SQLException: Communication link failure: java.io.IOException
Hi, I compiled MySQL 3.23.54, try MySQL CLI. Everything looks fine. But when I tried to connection MySQL through mm.mysql JDBC Driver, I got: 08:25:09SQLDataManager: Connecting... jdbc:mysql://localhost:3306/mysql 08:25:09SQLDataManager: EXCEPTION: java.sql.SQLException: Communication link failure: java.io.IOException 08:25:09SQLDataManager: LEAVING: openRepository(boolean) What this mean? MySQL Java code and JDBC Driver works fine when I download old MySQL binary to my HPUX system. Thanks a lot! Zengfa __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication bug?
I don't think I have anything that should cause this. Here is my my.ini from the the slave. The tables that are being excluded are not listed. [mysqld] basedir=C:/mysql datadir=C:/mysql/data set-variable=max_allowed_packet=16M log-slave-updates log-bin # Replication variables master-host=x.x.x.x master-user=sasassas master-password=x master-port=3306 server-id=2 # Exclude some tables that we don't want here! replicate-wild-ignore-table=ra_scanner.system replicate-wild-ignore-table=ra_scanner.local_scan_log - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
java.sql.SQLException: Communication link failure: java.io.IOException
Hi, I compiled MySQL 3.23.54, try MySQL CLI. Everything looks fine. But when I tried to connection MySQL through mm.mysql JDBC Driver, I got: 08:25:09SQLDataManager: Connecting... jdbc:mysql://localhost:3306/mysql 08:25:09SQLDataManager: EXCEPTION: java.sql.SQLException: Communication link failure: java.io.IOException 08:25:09SQLDataManager: LEAVING: openRepository(boolean) What this mean? MySQL Java code and JDBC Driver works fine when I download old MySQL binary to my HPUX system. Thanks a lot! Zengfa __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Select SUM Function
On Mon, 2003-01-13 at 14:11, Stevens, Ryan wrote: I understand the SQL statement in MySQL to be SELECT sum(column name) FROM table; but I would like to get a SUM of multiple columns. Is this possible?? Yes, you may use: SELECT sum(c1+c2) FROM table; or even: SELECT sum(table1.v + table2.v) FROM table1,table2 [WHERE ] Don't know if this was what you're looking for... ds - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Problem to access MySQL via MySQLFront
Stupid filter fodder: sql,query,queries,smallint -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] The problem is with the new glibc-2.2.5-40 package released on October 3, 2002. Apparently its a known issue, and people are aware of it. The best solution I've seen so far is to role back to a previous version of this package, and its dependencies. There was a subsequent glibc update that fixed this issue, if I remember right. I upgraded again, to the very latest one RedHat has released, and it solved the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using tranzactions in Perl
Hi all, I've seen the following code in MySQL cookbook: eval { # move some money from one person to the other $dbh-do (UPDATE money SET amt = amt - 6 WHERE name = 'Eve'); $dbh-do (UPDATE money SET amt = amt + 6 WHERE name = 'Ida'); # all statements succeeded; commit transaction $dbh-commit ( ); }; if ($@) # an error occurred { print Transaction failed, rolling back. Error was:\n$@\n; # roll back within eval to prevent rollback # failure from terminating the script eval { $dbh-rollback ( ); }; } --- Well, I don't understand why I need to check if $@ is true or false. If there wasn't any error, and the program should not rollback, in fact it could give the rollback command because it won't produce any effect after the commit command was given. Am I wrong? Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
My ISP has mySQL:how do I link to it?
My isp is Omnis.com. They have mySQL on my host server, and I have entered some contact info for a password login system. The system they already have doesn't let me use email addresses for logins. This is my first time using mySQL, how do I get it to link? Will it let me use email for login? christina - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Removing users
I've read the manual concerning REVOKE commands but how would I completely remove a user inside MySQL? I can't imagine that it's as easy as removing them from the user table. TIA, Ed mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Solaris Performance
Hi Andy Is MySQL loading the tables in to memory? MySQL AB say running MySQL on 64bit system should be faster. Sorry I can not be more help. Simon -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 15:34 To: [EMAIL PROTECTED] Subject: Solaris Performance Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
myisamchk warnings -- open tables
Since the http://lists.mysql.com/ archives site is down, I'll have to ask this question without the benifit of the archives... I have a (very large) Perl script that has a bad tendency to cause the following warning from myisamchk: myisamchk: MyISAM file /var/lib/mysql//table_name.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/var/lib/mysql//table_name.MYI' is usable but should be fixed It does this for table after table (I'm running multiple instances of the same database for diffrerent sites). It's fine to go back and run myisamchk --recover -- quick, but I'd rather stop the bleeding than continue applying bandages. I have started through the code making sure there is a $sth-finish(); after every table query/insert/update, but I seem to have made the problem worse! Is there something I should be paying special attention to? Is there something that I might just plain be missing? Many thanks in advance! Jody Harris -- http://www.realizationsystems.com/ - changing the way people communicate http://www.galacticslacker.com/ - read it and weep - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrading to version 4.
In the MySQL manual, under http://www.mysql.com/doc/en/Upgrading-from-3.23.html it says: --- The old C API functions mysql_drop_db, mysql_create_db, and mysql_connect are not supported anymore, unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. Instead of doing this, it is preferable to change the client to use the new 4.0 API. - My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. Thanks, Maximo. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The bug in 4.0.8 ?
I´m upgrated the mysql to 4.0.8 version ( from 4.0.6 ) after this my server is going down of one hour for another one. the log message is: /usr/local/mysql4//libexec/mysqld: ready for connections 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=67108864 read_buffer_size=1044480 sort_buffer_size=2097144 max_used_connections=13 max_connections=100 threads_connected=13 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 372335 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x86bd508 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=0xbcfff5f8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d7c99 0x40034f54 0x80e103d 0x80e144e 0x400320ba 0x401bad6a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=34 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 34 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. - cut Is a bug or a problem ? My System is MySQL 4.0.8 and Linux 2.4.18 with reiserfs. ps: The development tree is down ? sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Initiating secure connection to MySQL4 from PHP
would like to help you but I can't. This is PHP issue, not MySQL-s. SSL connections are supported by MySQL, they are also available in C API but I am not sure if and how PHP uses them. Looking up into PHP manual http://www.php.net/manual/en/ref.mysql.php won't show up needed functionality for this. You have three options: 1. Use third party tunnel (CIPE, stunnel, ssh for example) to encrypt communication. [...] I second that. If you use a local connection, IMHO there is no need to use SSL. If you use a remote connection, you would probably better succeed using stunnel (or sslwrap or some other SSL tunneling). - Cs. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using tranzactions in Perl
At 12:59 +0200 1/13/03, Octavian Rasnita wrote: Hi all, I've seen the following code in MySQL cookbook: eval { # move some money from one person to the other $dbh-do (UPDATE money SET amt = amt - 6 WHERE name = 'Eve'); $dbh-do (UPDATE money SET amt = amt + 6 WHERE name = 'Ida'); # all statements succeeded; commit transaction $dbh-commit ( ); }; if ($@) # an error occurred { print Transaction failed, rolling back. Error was:\n$@\n; # roll back within eval to prevent rollback # failure from terminating the script eval { $dbh-rollback ( ); }; } --- Well, I don't understand why I need to check if $@ is true or false. If there wasn't any error, and the program should not rollback, in fact it could give the rollback command because it won't produce any effect after the commit command was given. Am I wrong? That's true, but don't you think it's a little confusing to write code that issues a rollback back after a successful transaction? It's also an additional statement that in that case is unnecessary to issue, so what you suggest would be less efficient. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Removing users
[EMAIL PROTECTED] wrote: I've read the manual concerning REVOKE commands but how would I completely remove a user inside MySQL? I can't imagine that it's as easy as removing them from the user table. If you remove them (or blank their password) in the user table, they'll have no way of logging in, since MySQL won't be able to authenticate them. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl vs. PHP with MySQL - performance?
Angel Flow wrote: Would like to ask people's thoughts on whether Perl or PHP has higher performance with MySQL. I've heard rumours that DBI is slower than the PHP MySQL driver. I would say that comparing DBI to ADODB is more appropriate; PHP more or less directly calls the mysql C library functions whereas PERL actually does some additional work in there. In PERL's case, its probably a similar code distance to just do a single executed query and return an array of results as it is in PHP though. I'd benchmark it to be sure though. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
Stephen Brownlow wrote: You can make backup of the database and then drop the database.. Why do you want to do it? When maintenance is necessary, it would be far better to be able to: 1. turn off one database, 2. use myisamchk, Unix or other applications to adjust it in any way, 3. turn it back on. I must second that request, although someone else pointed out that using a LOCK FOR UPDATE would work as well since no processes would be able to touch the database during the lock. As long as the MySQL team thinks thats a safe way to handle things, and is willing to make sure it keeps working that way safely, I'd say we already have a solution though. Oh yeah, without the words SQL or QUERY, this message would be spam. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: converting text to hypertext
Rodney Broom wrote: If so, then the answer is that there isn't such a data type. If you want a link, then you'll have to make it yourself. I suggest making your column a varchar(). Then, you ~might~ get the data with a query like this: SELECT CONCAT(CONCAT('a href=', link, ''), link, '/a') FROM stuff; But please remember, as was said earlier, to use the appropriate escaping functions for your language to make sure link contains no special HTML characters in the second instance and no URL characters in the first. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL backend for mailing list
Mike wrote: I'm making a library website where users can store preferences about the types of books/cds/etc... they like (e.g. romance, rock, cooking, etc). Once a month the list of 'New Releases' is updated. Currently there is a New Release box which shows releases based on the users preferences. This type of functionality is the responsibility of your program, not the MySQL backend. MS SQL Server may offer this option, but MySQL does not and there are thousands of programs and libraries you can use to do this very simply. Please consider going to freshmeat.net or whatever your favorite search for programs I want search engine is and search for Send E-mail. If you were using PERL, there are several libraries available. In PHP, its built-in. With C or C++ I'm sure you could find one but I wrote my own, and in a *nix shell script, you can do something like: #!/bin/sh cat $message | sendmail [EMAIL PROTECTED] Look up how E-mail messages are formatted (its text; do a view source on this message, for an example) and you're probably home-free. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Removing users
That would work but is there no way to completely remove their record? Ed On Mon, 13 Jan 2003, Michael T. Babcock wrote: [EMAIL PROTECTED] wrote: I've read the manual concerning REVOKE commands but how would I completely remove a user inside MySQL? I can't imagine that it's as easy as removing them from the user table. If you remove them (or blank their password) in the user table, they'll have no way of logging in, since MySQL won't be able to authenticate them. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
general query log
MySQL gurus: I'm a big fan of the general query log for taking the occasional audit of activity against our database server, as well as a tool for checking on the efficiency of new (mostly PHP) projects that interact with MySQL. What I don't like is that I have to stop and restart the server process twice (once to turn the log on and once to turn it back off) in order to get the output. I've read through the documentation at mysql.com and would have searched through the list archives if I wasn't getting document contains no data messages from lists.mysql.com at the moment. We use mytop (http://jeremy.zawodny.com/mysql/mytop/) to keep some track of the queries that are being run, but the output pales in comparison (for this use) to that from the general query log. (1) Is there a way to turn logging on for brief periods (no more than 15 minutes) without having to stop and restart? (2) Is there some way to get similar output with a different tool? [mysql query] TIA, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: java.sql.SQLException: Communication link failure: java.io.IOException
I also got error from terminal: sendsig: useracc failed. 0x8003bf6dbe00 0x005020 Pid 3093 was killed due to failure in writing the signal context - possible static overflow. I tried to logon on as mysql user. I didn't set mysql user password. I looked at hostname-bin.err: MySQL is restarted. Thanks for your help! Zengfa --- Zengfa Gao [EMAIL PROTECTED] wrote: Hi, I compiled MySQL 3.23.54, try MySQL CLI. Everything looks fine. But when I tried to connection MySQL through mm.mysql JDBC Driver, I got: 08:25:09SQLDataManager: Connecting... jdbc:mysql://localhost:3306/mysql 08:25:09SQLDataManager: EXCEPTION: java.sql.SQLException: Communication link failure: java.io.IOException 08:25:09SQLDataManager: LEAVING: openRepository(boolean) What this mean? MySQL Java code and JDBC Driver works fine when I download old MySQL binary to my HPUX system. Thanks a lot! Zengfa __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Solaris Performance
Simon, Thanks, for the reply. I guess I better try the 64 bit version and see if it makes a difference. I'll report back what I find. Cheers, Andy -Original Message- From: Simon Green [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 16:58 To: 'Andy Eastham'; [EMAIL PROTECTED] Subject: RE: Solaris Performance Hi Andy Is MySQL loading the tables in to memory? MySQL AB say running MySQL on 64bit system should be faster. Sorry I can not be more help. Simon -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 15:34 To: [EMAIL PROTECTED] Subject: Solaris Performance Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Initiating secure connection to MySQL4 from PHP
On 13 Jan 2003, at 18:49, Tonu Samuel wrote: I would like to help you but I can't. This is PHP issue, not MySQL-s. SSL connections are supported by MySQL, they are also available in C API but I am not sure if and how PHP uses them. Looking up into PHP manual http://www.php.net/manual/en/ref.mysql.php won't show up needed functionality for this. For some reason the MYSQL_CLIENT_SSL constant isn't mentioned on that page, but it is on this one: http://www.php.net/manual/en/function.mysql-connect.php So theoretically it should be possible to make the SSL connection as long as you have PHP 4.3.0. [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: logrotate problem with mysql
On Fri, 10 Jan 2003, Michael T. Babcock wrote: if test -n `ps acx|grep mysqld`; then /usr/bin/mysqladmin flush-logs fi You're probably running it as root with the password loading from the /root/.my.cnf or something. Try adding the -uroot -p command-line options, or a [mysqladmin] section to your ~root/.my.cnf file. My /root/.my.cnf has 600 permissions and looks like: [mysqladmin] password=rootpassword user=root Do I need to add this section to the /etc/my.cnf? Is the user unknown to mysqladmin when it's run through logrotate? Thanks again. -- Jiann-Ming Su [EMAIL PROTECTED] 404-712-2603 Development Team Systems Administrator General Libraries Systems Division - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Embedding images stored in database
In the last episode (Jan 13), Toomas said: Hello List, Is there a simplier way to embed an image stored in BLOB field into HTML than using 2 calls to Perl scripts - one to generate HTML and another call from that HTML via IMG tag to retrieve and print image? I mean, something like you do it in a multipart e-mail message? You can actually do this with HTML as well, by using the little-used data: URI. Try copying this into a file and loading it up in a broswer: h1Demonstration of embedding GIF image directly in document/h1 This is embedded image: img src=data:image/gif;base64,R0lGODlhDwAPAJEBAL+/v/// ACH5BAEAAAEALAAPAA8AAAIujA2Zx5EC4WIgWnnq vQBJLTyhE4khaG5Wqn4tp4ErFnMY+Sll9naUfGpkFL5DAQA7 / It's not all that efficient, though, since the image is sent in base-64 and the server sends it on every page load (instead of letting the browser fetch it once and then cache it). If you're worried about the impact of launching a CGI every time you need to pull an image out of the database, see if your webserver has an SQL filesystem module, or something that will let you pull data straight out of the database without involving scripts at all. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication - queries out of order or dropped?
Hi All, Trying my best to maintain some production servers running mysql-max-3.23.53a-pc-linux-gnu-i686 with one master and one slave. We keep having issues where we create a new table on the master, and a short while later the slave will crash because it starts trying to perform queries which insert into the new table, but it looks as if the query to create the table hasn't been executed yet (ie the table doesn't yet exist on the slave). I've got three pairs of master/slave servers, and this is happenning on all of them. My experience is that I can brute-force something such as setting the skip count to some positive integer, etc. etc., but that's only a hack, and doesn't get around the big problem here which is that somehow the queries are seemingly getting either skipped or out-of-order. Any help here? My management is getting quite upset about this. We're not using the slaves for anything at present, but they are meant to be a disaster recovery option. Right now they're worthless... Thanks in advance, David Nedved here is the output of show slave status on a currently broken slave: (sure enough the table exists on the master, but not on the slave) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 47773 to server version: 3.23.53a-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show slave status; +-+-+-+---+--+---+---+-+-+++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | |Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | | | | | | Skip_counter | +-+-+-+---+--+---+---+-+-+++--+ | 66.83.39.9 | repl| 3306| 60| customer.024 | 146873601 | |No| | mysql | 1 | error 'Can't |create/write to file './www_heliumandballoons_com_wa/ACCESS.frm' (Errcode: 2)' on |query 'CREATE TABLE ACCESS ( ID int(1) NOT NULL default '0', CATALOG tinyint(1) default NULL, DISPLAY_CATALOG tinyint(1) default NULL, ECOMMERCE tinyint(1) default NULL, DISPLAY_ECOMMERCE tinyint(1) default NULL, RESIDENTIAL tinyint(1) default NULL, PRIMARY KEY (ID) ) TYPE=MyISAM' | 0| +-+-+-+---+--+---+---+-+-+++--+ 1 row in set (0.00 sec) mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Disable log messages for Embedded Server?
Is there any way to disable log messages being written to the standard output using Embedded Server? Using mysqld, they are automatically redirected to mysql.err (under Windows, at least), but there seems to be no way to do this in Embedded. We would like to be able to display our own output to the user, but not MySQL's. Ideally we would be able to turn off MySQL output, but at least we would like to be able to redirect it. -- Matt Solnit [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help with query
Hi all! I would like to build a query that I can use to search hierichally from an char(4) id composed as followed 2positions for the class of plant (for example) and 2 positions for the type of flower (for example) if someone give me the id that means red rose tree, and I know there is another clasifications of tree and another colors for the rose, does anyone knows how to retrieve them. 0100 - Rose tree 0101 - pink rose tree 0102 - red rose tree 0103 - rococo rose tree 0200 - generic tree - Original Message - From: Michael T. Babcock [EMAIL PROTECTED] To: Rodney Broom [EMAIL PROTECTED] Cc: Sent: Thursday, January 09, 2003 2:41 AM Subject: Re: converting text to hypertext Rodney Broom wrote: If so, then the answer is that there isn't such a data type. If you want a link, then you'll have to make it yourself. I suggest making your column a varchar(). Then, you ~might~ get the data with a query like this: SELECT CONCAT(CONCAT('a href=', link, ''), link, '/a') FROM stuff; But please remember, as was said earlier, to use the appropriate escaping functions for your language to make sure link contains no special HTML characters in the second instance and no URL characters in the first. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help with query
Hi all! I would like to build a query that I can use to search hierichally from an char(4) id composed as followed 2positions for the class of plant (for example) and 2 positions for the type of flower (for example) if someone give me the id that means red rose tree, and I know there is another clasifications of tree and another colors for the rose, does anyone knows how to retrieve them. 0100 - Rose tree 0101 - pink rose tree 0102 - red rose tree 0103 - rococo rose tree 0200 - generic tree Thanks in advance Leonardo J. Beln. query mysql sql blah! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help with query
Hi all! I would like to build a query that I can use to search hierichally from an char(4) id composed as followed 2positions for the class of plant (for example) and 2 positions for the type of flower (for example) if someone give me the id that means red rose tree, and I know there is another clasifications of tree and another colors for the rose, does anyone knows how to retrieve them. 0100 - Rose tree 0101 - pink rose tree 0102 - red rose tree 0103 - rococo rose tree 0200 - generic tree Thanks in advance Leonardo J. Beln. query mysql sql blah! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help with query
Hi all! I would like to build a query that I can use to search hierichally from an char(4) id composed as followed 2positions for the class of plant (for example) and 2 positions for the type of flower (for example) if someone give me the id that means red rose tree, and I know there is another clasifications of tree and another colors for the rose, does anyone knows how to retrieve them. 0100 - Rose tree 0101 - pink rose tree 0102 - red rose tree 0103 - rococo rose tree 0200 - generic tree Thanks in advance Leonardo J. Beln. query mysql sql blah! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Initiating secure connection to MySQL4 from PHP
On Monday 13 January 2003 17:49, Tonu Samuel wrote: Hi, Currently PHP's mysql extension doesn't support SSL. This will be available in PHP 5. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Book recomendations
Matthew K. Gold wrote: I agre with the DuBois recommendation. I have a couple of quibbles, but overall, I think it's a great book (and I did a pretty exhaustive check when I was first learning mysql. Of course, if you want a generic how-to on designing databases or SQL itself, there are an even larger list of books to consider. The ones I found most helpful initially were the Oracle certification course books. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB Foreign Key Questions
Hello I have two tables of InnoDB type. CREATE TABLE `ledger` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`id`), KEY `id` (`name`) ) TYPE=InnoDB; CREATE TABLE `voucher` ( `id` int(11) NOT NULL default '0', `vdate` date NOT NULL default '-00-00', `amount` decimal(10,0) default '100', `name` varchar(50) default 'Karam', PRIMARY KEY (`id`,`vdate`) ) TYPE=InnoDB; Now I add a Foreign Key reference to ledger.id for voucher id by using the following command - alter table voucher add constraint fk_key_1 foreign key (id) references ledger (id) now when i do a query - show table status like 'voucher' I am getting the following value in the comment field. InnoDB free: 23552 kB; (id) REFER rohit/ledger(id) It shows that foreign key has been made but i had specified it to be fk_key_1. Even if I make the Foreign Key without the keyword constraint the same thing happens ? So, what is the purpose of CONSTRAINT keyword. How can I give a name to a relationship. Also after reading the MySQL and InnoDB docs I am not able to guess how to drop a FOREIGN KEY ? How can I do that ? I am using mysql-mx-nt 3.23.54 running as a service in WinXP. Any help will be appreciated. Thanks in advance. Karam sql, query ( filteraide ) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help with query
Hi, SELECT LEFT(YOUR_COLUMN,2) FROM YOUR_TABLE.for position SELECT RIGHT(YOUR_COLUMN,2) FROM YOUR_TABLE for type Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Leonardo Javier Beln [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 10:09 PM Subject: help with query Hi all! I would like to build a query that I can use to search hierichally from an char(4) id composed as followed 2positions for the class of plant (for example) and 2 positions for the type of flower (for example) if someone give me the id that means red rose tree, and I know there is another clasifications of tree and another colors for the rose, does anyone knows how to retrieve them. 0100 - Rose tree 0101 - pink rose tree 0102 - red rose tree 0103 - rococo rose tree 0200 - generic tree Thanks in advance Leonardo J. Beln. query mysql sql blah! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB table, NOT NULL question
Hello, I'm in the process of moving from Postgres to MySQL and I have a question. +How do you prevent a field from being left empty? I have an InnoDB table that looks like the following. CREATE TABLE `stuff` ( `stuff_id` int(11) NOT NULL auto_increment, `somevalue1` varchar(35) NOT NULL, `somevalue2` varchar(35) NOT NULL, PRIMARY KEY (`stuff_id`) ) TYPE=InnoDB COMMENT='stuff table'; I run the following insert statement. INSERT INTO stuff ( somevalue2 ) VALUES ('blah') Now the field somevalue1 is empty. Doesn't this violate the NOT NULL constraint on the field? Thanks, Gabe Geisendorfer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Disable log messages for Embedded Server?
Matt, the variable mysql_embedded makes InnoDB more silent: heikki@hundin:~/mysql-4.0/sql grep -n mysql_embedded *.cc ha_innodb.cc:659: if (mysql_embedded) { ha_innodb.cc:768: srv_print_verbose_log = mysql_embedded ? 0 : 1; mysqld.cc:390:bool mysql_embedded=0; mysqld.cc:392:bool mysql_embedded=1; Have you tried redirecting stderr to some file? MySQL writes most of its messages to stderr. I am not sure if any messages come to stdout if mysql_embedded =1. If they come, it is easy to change 4.0.x so that they are either suppressed or directed to stderr. Your own program then has stdout solely for its own use. Regards, Heikki Innobase Oy - Original Message - From: Matt Solnit [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, January 13, 2003 9:26 PM Subject: Disable log messages for Embedded Server? Is there any way to disable log messages being written to the standard output using Embedded Server? Using mysqld, they are automatically redirected to mysql.err (under Windows, at least), but there seems to be no way to do this in Embedded. We would like to be able to display our own output to the user, but not MySQL's. Ideally we would be able to turn off MySQL output, but at least we would like to be able to redirect it. -- Matt Solnit [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help with query
From you example, not quite sure what you are trying to do (I could if, for example generic tree was 0199, and 01 was tree and last two characters were color or something like that) At any rate, you can use string functions to pull data from portions of fields to build whatever you want to search on. refer to http://www.mysql.com/doc/en/String_functions.html Ken Hylton Programmer Analyst IV LEC Systems Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -Original Message- From: Leonardo Javier Belen [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 2:10 PM To: [EMAIL PROTECTED] Subject: help with query Hi all! I would like to build a query that I can use to search hierichally from an char(4) id composed as followed 2positions for the class of plant (for example) and 2 positions for the type of flower (for example) if someone give me the id that means red rose tree, and I know there is another clasifications of tree and another colors for the rose, does anyone knows how to retrieve them. 0100 - Rose tree 0101 - pink rose tree 0102 - red rose tree 0103 - rococo rose tree 0200 - generic tree Thanks in advance Leonardo J. Bel?n. query mysql sql blah! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Html entities and MySql, this is a confusing one to me.
Was working on a database job last night, and ran into a little stumbling block, hope all this comes though in email, if you see a ® it is a registered mark just in case the email clients out there change it around. So I have a simple database, one table, and say one field, varchar(14) and in it I put hello®, I use html input form elements and a middleware called WebSiphon, I have also done this in php with phpmyadmin as well, the character shows up on display in the Browser as a registered mark, as well as in phpmyadmin, all good so far. Now, in my shell in the mysql prompt, or on a dump of the data, the registered mark is not what it should be, here is a example. mysql select name from custom_bikes; ++ | name | ++ | 2002 FAT BOYÆ, FLSTF - | | 2002 V-RODÆ, VRSCA - | | 2002 ROAD KINGÆ FLHR - | | 2002 FAT BOYÆ, FLSTF - | | 2002 FAT BOYÆ, FLSTFI -| | 2002 DYNA WIDE GLIDEÆ, FXDWG - | | 2002 SOFTAILÆ DEUCE, FXSTDI - | | 2002 V-RODÆ, VRSCA | | 2002 FAT BOYÆ FLSTFI | | 2002 SOFTAILÆ STANDARD, FXST | ++ Where the Æ should be a ® So in my middleware, I have made a function called encodeHTML(string) And of course it does not work to convert the ® to reg; for propper html display in a browser. Granted, most browsers are forgiving, but I consider this muddy data in MySql and would like the correct data in there. Thanks for any help. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Initiating secure connection to MySQL4 from PHP
Hello, This is discouraging after I spent most of the weekend getting OpenSSL working with our MySQL 4 server. Unfortunately I can't use Stunnel to secure the connection between our website and our MySQL server because our ISP will not allow me to run the Stunnel daemon on their servers. They have a web server farm and claim that they would need to configure Stunnel for me on each of their servers in the farm. How do people typically handle setting up Stunnel to secure their connections from a public ISP to their MySQL server? Is it possible that I only need to run the Stunnel daemon on my MySQL box? Securing the connection between a public ISP and a privately-hosted MySQL server seems like something that many people would have overcome by now. Am I really at the mercy of my ISP on this? Thanks for your help, David Rock -Original Message- From: Georg Richter [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 12:21 PM To: Tonu Samuel; David Rock Cc: '[EMAIL PROTECTED]' Subject: Re: Initiating secure connection to MySQL4 from PHP On Monday 13 January 2003 17:49, Tonu Samuel wrote: Hi, Currently PHP's mysql extension doesn't support SSL. This will be available in PHP 5. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Html entities and MySql, this is a confusing one to me.
on 01/13/2003 1:58 PM, Paul DuBois at [EMAIL PROTECTED] wrote: How do you know this isn't simply a failure of your terminal window to display the character properly? That's the most likely problem here, not that your data are stored incorrectly. Well, I don¹t for certain, I just did a dump to a text file and here were the results... As you can see the pesky Æ is in place of the ®, which really makes no sense to me, as in a browser is displays as a ®, but if I do a string replace on the ® it does not see it as one, so it wont replace it with the html entity I desire, this also happens on a few other characters as well. -- -- Dumping data for table 'custom_bikes' -- INSERT INTO custom_bikes VALUES (5,'2002 FAT BOYÆ, FLSTF -','Custom Black Powder','$23,152 plus','Custom Black Powder Coating on Accessories\r\nCustom Wheels\r\nExhaust/Hi-Flow\r\nCustom Pegs and Footboards',1,'1','qmKjwFN4eobgxQyCiQZ5u7mm.jpg',20030112182536,200301121538 31); INSERT INTO custom_bikes VALUES (3,'2002 V-RODÆ, VRSCA -','Skull paint','$23,397 plus','Custom Skull Paint\r\nHi-Flow/Pipes\r\nCustom Pegs and Grips',7,'1','5cFX4x85HAe2Fao2pGQUSAyp.jpg',20030112183701,20030112153742); INSERT INTO custom_bikes VALUES (4,'2002 ROAD KINGÆ FLHR -','Flame paint','$23,809 plus','Custom Luxury Rich Red with Red Flames paint\r\nPerformance Exhaust/Hi-Flow\r\nSecurity System\r\nCustom handgrips, footboards, and other accessories',6,'1','Y5Es935New44GzuUnTxJFA3i.jpg',20030112175803,20030112153 750); INSERT INTO custom_bikes VALUES (6,'2002 FAT BOYÆ, FLSTF -','Silver with blue flames','$22,497 plus','Custom Silver and Blue Flame Paint\r\nPipes/Hi-Flow\r\nCustom Grips, Pegs, and Footboards',4,'1','RXofzWGeVbc14G4EDbAwrQw3.jpg',20030112175757,200301121538 41); INSERT INTO custom_bikes VALUES (8,'2002 FAT BOYÆ, FLSTFI -','White pearl with white flames','$31,274 plus','Custom White Paint With White Flames\r\nFull Chrome Accessory Package\r\nCustom Wheels\r\nPerformance Exhaust/Hi-Flow',5,'1','X5m5YtfFuGoLDuT5MJ9YYjuh.jpg',20030112175800,2003011 2153901); INSERT INTO custom_bikes VALUES (9,'2002 DYNA WIDE GLIDEÆ, FXDWG -','Skull paint','$26,952 plus','Custom Skull Paint\r\nCustom Wheels\r\nTriple Trees\r\nLots of Chrome\r\nPerformance Exhaust/Hi-Flow',3,'1','7uG5VXUfgiwfgwN4Xg4jpR6P.jpg',20030112182532,2003011 2153907); INSERT INTO custom_bikes VALUES (10,'2002 SOFTAILÆ DEUCE, FXSTDI -','Flame paint','$34,619 plus','Custom Flame Paint\r\nCustom Wheels\r\nChrome Accessory Package\r\n95î Big Bore\r\nExhaust/Hi-Flow',2,'1','GrjGCz33woRHwe9JH9xyWZ7j.jpg',20030113050601 ,20030112153914); INSERT INTO custom_bikes VALUES (14,'2002 V-RODÆ, VRSCA','Flame Paint','$22,817 plus ttl','Custom Flame Paint\r\nHi-Flow/Pipes\r\n',8,'1','HpcfDW5BmkoozLVWDYQA63bX.jpg',20030113065 241,20030113065241); INSERT INTO custom_bikes VALUES (15,'2002 FAT BOYÆ FLSTFI','Snakeskin Paint','SOLD','Custom Snakeskin Paint\r\nScreaminí Eagle Exhaust/Hiflow\r\n',9,'1','GuCeu6qbwzysXg2QxmFrWvoW.jpg',20030113065405,2003 0113065405); INSERT INTO custom_bikes VALUES (16,'2002 SOFTAILÆ STANDARD, FXST','Flamepaint','SOLD','Custom Flame Paint\r\nLots of Chrome\r\nCustom Accessories\r\nSecurity System\r\nVance and Hines Exhaust',10,'1','6grjaxN7LKcUtNneMAsoMvB4.jpg',20030113065514,20030113065514 ); sql,query,queries,smallint - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Html entities and MySql, this is a confusing one to me.
At 13:51 -0800 1/13/03, Scott Haneda wrote: Was working on a database job last night, and ran into a little stumbling block, hope all this comes though in email, if you see a ® it is a registered mark just in case the email clients out there change it around. So I have a simple database, one table, and say one field, varchar(14) and in it I put hello®, I use html input form elements and a middleware called WebSiphon, I have also done this in php with phpmyadmin as well, the character shows up on display in the Browser as a registered mark, as well as in phpmyadmin, all good so far. Now, in my shell in the mysql prompt, or on a dump of the data, the registered mark is not what it should be, here is a example. mysql select name from custom_bikes; ++ | name | ++ | 2002 FAT BOYÆ, FLSTF - | | 2002 V-RODÆ, VRSCA - | | 2002 ROAD KINGÆ FLHR - | | 2002 FAT BOYÆ, FLSTF - | | 2002 FAT BOYÆ, FLSTFI -| | 2002 DYNA WIDE GLIDEÆ, FXDWG - | | 2002 SOFTAILÆ DEUCE, FXSTDI - | | 2002 V-RODÆ, VRSCA | | 2002 FAT BOYÆ FLSTFI | | 2002 SOFTAILÆ STANDARD, FXST | ++ Where the Æ should be a ® How do you know this isn't simply a failure of your terminal window to display the character properly? That's the most likely problem here, not that your data are stored incorrectly. So in my middleware, I have made a function called encodeHTML(string) And of course it does not work to convert the ® to reg; for propper html display in a browser. Granted, most browsers are forgiving, but I consider this muddy data in MySql and would like the correct data in there. Thanks for any help. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.0.9 is released
Hi, MySQL 4.0.9, a new version of the world's most popular Open Source Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that all mirrors may not be up to date; If you can't find this version on some mirror, please retry later or try another mirror. This is a new gamma release, fixing some recently discovered bugs. From the ChangeLog: --- Functionality added or changed: * `OPTIMIZE TABLE' will for MyISAM tables treat all `NULL' values as different when calculating cardinality. This helps in optimising joins between tables where one of the tables has a lot of `NULL' values in a indexed column: SELECT * from t1,t2 where t1.a=t2.key_with_a_lot_of_null; * Added join operator `FORCE INDEX (key_list)'. This acts likes `USE INDEX (key_list)' but with the addition that a table scan is assumed to be VERY expensive. One bad thing with this is that it makes `FORCE' a reserved word. * Reset internal row buffer in MyISAM after each query. This will reduce memory in the case you have a lot of big blobs in a table. Bugs fixed: * A security patch in 4.0.8 causes the mysqld server to die if the remote hostname can't be resolved. This is now fixed. * Fixed crash when replication big `LOAD DATA INFILE' statement that caused log rotation. Regards, - Jani For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Jani Tolonen [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading to version 4.
Maximo, My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. PHP's mysql_connect() is just a wrapper around the C API mysql_connect. As the manual says: This function is deprecated. It is preferable to use mysql_real_connect() instead. I assume that PHP 4.2.3 is already using mysql_real_connect(). If this is not the case, I'm quite sure (if no one here disagrees ;) that PHP 5 will. And I'm really sure that it will always be called mysql_connect() in PHP, just to keep things easy. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Maximo Migliari [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 7:45 PM Subject: Upgrading to version 4. In the MySQL manual, under http://www.mysql.com/doc/en/Upgrading-from-3.23.html it says: --- The old C API functions mysql_drop_db, mysql_create_db, and mysql_connect are not supported anymore, unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. Instead of doing this, it is preferable to change the client to use the new 4.0 API. - My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. Thanks, Maximo. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Removing users
Ed, That would work but is there no way to completely remove their record? With REVOKE, you can partially or totally revoke privileges from users. What you can't do with REVOKE (in MySQL) is to erase a user completely. For this, you will have to DELETE FROM mysql.user WHERE User = 'user2bremoved'. You can even use this as an alternative way to remove users. You need two statements for this, just in case ... DELETE FROM mysql.user WHERE User = 'user2bremoved'; DELETE FROM mysql.db WHERE User = 'user2bremoved'; Opposed to GRANT and REVOKE DELETE will not cause the server to notice the privilege changes. You need another statement for this: FLUSH PRIVILEGES; This will force the server to reload the grant tables, and thus, the privileges. Details: http://www.mysql.com/doc/en/User_Account_Management.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: [EMAIL PROTECTED] To: Michael T. Babcock [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 7:10 PM Subject: Re: Removing users That would work but is there no way to completely remove their record? Ed On Mon, 13 Jan 2003, Michael T. Babcock wrote: [EMAIL PROTECTED] wrote: I've read the manual concerning REVOKE commands but how would I completely remove a user inside MySQL? I can't imagine that it's as easy as removing them from the user table. If you remove them (or blank their password) in the user table, they'll have no way of logging in, since MySQL won't be able to authenticate them. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading to version 4.
At 23:45 +0100 1/13/03, Stefan Hinz, iConnect (Berlin) wrote: Maximo, My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. PHP's mysql_connect() is just a wrapper around the C API mysql_connect. Huh? PHP's mysql_connect() has been using mysql_real_connect() for several years now. As the manual says: This function is deprecated. It is preferable to use mysql_real_connect() instead. I assume that PHP 4.2.3 is already using mysql_real_connect(). If this is not the case, I'm quite sure (if no one here disagrees ;) that PHP 5 will. And I'm really sure that it will always be called mysql_connect() in PHP, just to keep things easy. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Removing users
At 23:40 +0100 1/13/03, Stefan Hinz, iConnect (Berlin) wrote: Ed, That would work but is there no way to completely remove their record? With REVOKE, you can partially or totally revoke privileges from users. What you can't do with REVOKE (in MySQL) is to erase a user completely. For this, you will have to DELETE FROM mysql.user WHERE User = 'user2bremoved'. Better specify the Host value as well, in case you have multiple accounts with the same User value. :-) You can even use this as an alternative way to remove users. You need two statements for this, just in case ... DELETE FROM mysql.user WHERE User = 'user2bremoved'; DELETE FROM mysql.db WHERE User = 'user2bremoved'; Opposed to GRANT and REVOKE DELETE will not cause the server to notice the privilege changes. You need another statement for this: FLUSH PRIVILEGES; This will force the server to reload the grant tables, and thus, the privileges. Details: http://www.mysql.com/doc/en/User_Account_Management.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Html entities and MySql, this is a confusing one to me.
on 01/13/2003 2:10 PM, Scott Haneda at [EMAIL PROTECTED] wrote: How do you know this isn't simply a failure of your terminal window to display the character properly? That's the most likely problem here, not that your data are stored incorrectly. Correct you are, the data in MySql was clean, I needed to set my terminal to the proper charset, but I still for the life of me can not figure out why I can not string replace on it in my middleware. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication halts with sql syntax error
Description: There seems to be a problem with mysql replication. When the slave server encounters sql that uses backticks instead of single quotes. The +replication stops until you manually advance the position number in the master.info +file. How-To-Repeat: 1. Set up a replication server 2. enter in sql on the master server using backticks instead of singlequotes eg. DROP TABLE `ee_camera`, `ee_camera_type`, `ee_category`, `ee_counter`, `ee_country`, `ee_exhibition`, `ee_exhibition_feedback`, +`ee_lens`, `ee_light`, `ee_location`, `ee_message`, `ee_microthumb_path`, `ee_news`, +`ee_owner`, `ee_photo`, `ee_photo_size`, +`ee_photo_to_category`, `ee_photo_to_exhibition`, `ee_size`, `ee_thumb_path`, +`ee_thumbs`, `ee_workflow` 3. Watch the error log on the slave, it will stop replication at this point. Fix: Submitter-Id: submitter ID Originator:markb Organization: Server101.com MySQL support: none Synopsis: Replication halts with sql syntax error Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.54-Max Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 hours 45 min 44 sec Threads: 1 Questions: 50694 Slow queries: 0 Opens: 4433 Flush tables: 1 Open tables: 64 Queries per second avg: 1.814 Environment: Intel, Redhat 7.2 System: Linux launch.server101.com 2.4.16-xfs #2 SMP Tue Jan 15 05:26:12 EST 2002 i686 unknown 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/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-112.7.1) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 8 02:13 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1285884 Oct 11 03:19 /lib/libc-2.2.4.so -rw-r--r--1 root root 27338282 Oct 11 02:48 /usr/lib/libc.a -rw-r--r--1 root root 178 Oct 11 02:48 /usr/lib/libc.so lrwxrwxrwx1 root root 10 May 28 2002 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem getting the last insert id in my application - connectiontimed out
Hi First off I would like to say I appreciate all your help very much. Now, here's what I have. I installed, and confirmed that it runs properly, the Connector/J v3 drivers. my resin.conf looks like this: resource-ref res-ref-namejdbc/FormsAreUs/res-ref-name res-typejavax.sql.DataSource/res-type !-- res-typecom.mysql.jdbc.jdbc2.optional.MysqlDataSource/res-type -- init-param driver-name=com.mysql.jdbc.Driver/ init-param url=jdbc:mysql://localhost:3306/FormsAreUs/ init-param user=foo/ init-param password=bar/ init-param max-connections=20/ init-param max-idle-time=30/ /resource-ref And when I 'use' this connection (in this case) I try with of the following example. (For brevity I have removes all 'error' checking etc, suffice it ot say the exception thrown is below). Now I CAN get it to work with last_insert_id() but I would rather not have to use another round trip to the DB. Here is my code. Example 1: String sqlCommand = INSERT INTO Questions (question, questionTypeID) VALUES ( + pQuestion.getQuestion() + , + pQuestion.getQuestionTypeID() + ); Statement stmt2 = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE ); stmt2.executeUpdate( sqlCommand, Statement.RETURN_GENERATED_KEYS ); ResultSet rs2 = stmt2.getGeneratedKeys(); int lastQuestionID = rs2.getInt( 1 ); THROWS: java.lang.AbstractMethodError: com.mysql.jdbc.jdbc2.Statement.executeUpdate(Ljava/lang/String;I) Any help would be much appreciated. Oh, the interesting this is that this 'stmt2.executeUpdate() works on other pages as I only use prepared statements if/when I can. Thanks. Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John Bateman wrote: Thanks for the tip. Ive upgraded to the Connector/J driver, but I am having problem configuring it. Do you have an y experience with Resin you could point how what should go where? I can post my resin.conf file here if you like. Thanks. Have you tried something like: resource-ref res-ref-namejdbc/test/res-ref-name res-typejavax.sql.DataSource/res-type init-param driver-name=com.mysql.jdbc.Driver/ init-param url=jdbc:mysql://localhost:3306/test/ init-param user=/ init-param password=/ init-param max-connections=20/ init-param max-idle-time=30/ /resource-ref -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+IsJ+tvXNTca6JD8RAuFuAJ0eNi9yVCakCjACiKFZ+jM8Zo07/QCdFrq0 YTrR3TQWMcOc4sD9STgEWVo= =hbKd -END PGP SIGNATURE- . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
preventing duplicate records when using WHERE foo IN syntax
Hello, I need to get a single result from a SELECT from a table with the following sturcture: component - varchar(25) primary key catNum - varchar(25) primary key price - decimal(10,2) my component list may contain duplicate catNum ie: primary drive -WD1GJB - WD800JB -none secondary drive - WD800JB -none monitor -SONLCD19 -none any one catNum will have the same price so when i go looking for parts, knowing the catNum, i do this: SELECT price FROM syscomp WHERE catNum = $catNum no problem - i only look at the first return and am happy! However when putting together a system - and i have the following: $system = qq('WD800JB', 'SONLCD19', 'none', 'WD1GJB'); and insert that into my SQL as: SELECT price FROM syscomp WHERE catNum IN ($system); if 'WD800JB' is in TWO components, i get two results - thus, and incorrect number of return results. (price is doubled) However when i use: SELECT DISTINCTROW price FROM syscomp WHERE catNum IN ($system); and my list looks like this: $system = qq('WD800JB', 'WD800JB', 'SONLCD19', 'none'); i only get 4 results, the second 'WD800JB' is ignored. The only solution i can think of is: $system = ('partA', 'partB', 'partC', 'partD', 'partD'); foreach ($system){ SELECT price FROM syscompt WHERE catNum = '$_' # incrememnt $total with the first resutl. } BUT this seems painfully slow - as this would result in up to 20 calls to the sql server. I could ensure that each $system had the matching component, to match on the primary key, but i don't know how to set up the IN statement for a double part: WHERE concat (component, '-', catNum) IN ($system) can anyone help me with a solution. thanks. ken easson justken.net [EMAIL PROTECTED] justken web programming and technical support. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem getting the last insert id in my application - connectiontimed out
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John Bateman wrote: Hi First off I would like to say I appreciate all your help very much. Now, here's what I have. I installed, and confirmed that it runs properly, the Connector/J v3 drivers. my resin.conf looks like this: resource-ref res-ref-namejdbc/FormsAreUs/res-ref-name res-typejavax.sql.DataSource/res-type !-- res-typecom.mysql.jdbc.jdbc2.optional.MysqlDataSource/res-type -- init-param driver-name=com.mysql.jdbc.Driver/ init-param url=jdbc:mysql://localhost:3306/FormsAreUs/ init-param user=foo/ init-param password=bar/ init-param max-connections=20/ init-param max-idle-time=30/ /resource-ref And when I 'use' this connection (in this case) I try with of the following example. (For brevity I have removes all 'error' checking etc, suffice it ot say the exception thrown is below). Now I CAN get it to work with last_insert_id() but I would rather not have to use another round trip to the DB. Here is my code. Example 1: String sqlCommand = INSERT INTO Questions (question, questionTypeID) VALUES ( + pQuestion.getQuestion() + , + pQuestion.getQuestionTypeID() + ); Statement stmt2 = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE ); stmt2.executeUpdate( sqlCommand, Statement.RETURN_GENERATED_KEYS ); ResultSet rs2 = stmt2.getGeneratedKeys(); int lastQuestionID = rs2.getInt( 1 ); THROWS: java.lang.AbstractMethodError: com.mysql.jdbc.jdbc2.Statement.executeUpdate(Ljava/lang/String;I) Notice You have an older version of Connector/J (2.0.14 something or other) laying around in your classpath :( Make sure you're using the driver from: http://www.mysql.com/downloads/api-jdbc-dev.html -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+I08ttvXNTca6JD8RArCPAJ93E9ckNb9syrGQvc+n4fe+SSPgJgCff/vT CNWI/vr5tcB6LYLtyj13W3w= =9ZQU -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql.users read only?
Just tried. Nope... mysql grant all privileges on mysql.* to ext identified by 'ext1tex2' with grant option; ERROR 1036: Table 'user' is read only -- Loren McDonald AKA MisterrMac Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com Does the user 'mysql' have write access to the mysql database and files? Loren McDonald wrote: I'm not sure what has gone wrong but the mysql.users table is set as read only. I am trying to grant user privileges but can't because of this. How can I change the status so that I can add to it? I am logging in (localhost) using the (unchanged default) -u root account, so I know that's not the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading to version 4.
On Mon, Jan 13, 2003 at 03:45:59PM -0300, Maximo Migliari wrote: In the MySQL manual, under http://www.mysql.com/doc/en/Upgrading-from-3.23.html it says: --- The old C API functions mysql_drop_db, mysql_create_db, and mysql_connect are not supported anymore, unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. Instead of doing this, it is preferable to change the client to use the new 4.0 API. - My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. The PHP MySQL API will still work for you. Additionally, there is a new API that is currently in development that is intended to support the new features of MySQL like SSL connections, prepared statements, etc. Cheers! -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com MySQL Tip: Find the highest score for a given name mysql SELECT name, max(score) FROM high_score GROUP BY name; Gosh, Batman. The nobility of the almost-human porpoise. --Robin, the Boy Wonder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can't connect to MySQL server Access denied for root
Sounds like the same problem I'm having (for which I'm still stumped and don't know what has caused it): table mysql.user is read only. Test this by logging into mysql and using the GRANT or REVOKE statements. -- Loren McDonald AKA MisterrMac Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com ... shellmysqladmin -u root password 'new-password' shellmysqladmin -u root -h hostname password 'new-password' mysqladmin: connect to server at 'hostname' failed error: 'Can't connect to MySQL server on 'hostname' (110)' Check that mysqld is running on hostname and that the port is 3306. You can check this by doing 'telnet hostname 3306' shelltelnet hostname 3306 Trying IP_address... telnet: connect to address IP_address: Connection timed out shellmysqladmin -u root password 'new-password' mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' I can only use mysql with mysql user: shellmysql -u mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 3.23.54 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to insert entire text file in a table column
Excellent! It works. H M Kunzmann wrote: Yes there is ! Use LOAD_FILE(path_name) as the value component of the insert/update statement On Mon, 2003-01-13 at 00:41, Haisam K. Ido wrote: I have the following table, CREATE TABLE cp ( id INT(11) NOT NULL auto_increment, fileVARCHAR(128) NOT NULL, content LONGTEXT, UNIQUE KEY keyword(id,file), PRIMARY KEY (id) ) TYPE=INNODB; I want to insert into content an entire text document. How do I do that without puting the entire content into a variable, let's say in perl, and then writing the INSERT statement. Is there a way to give the path to a file and for mysql to use that path to insert the content? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php