4.1.4-gamma cannot compile
got thie error and broken when making mysql 4.1.4-gamma automake-1.7: not found WARNING: `automake-1.7' is needed, and you do not seem to have it handy on your system. You might have modified some files without having the proper tools for further handling them. Check the `README' file, it often tells you about the needed prerequirements for installing this package. You may also peek at any GNU archive site, in case some other package would contain this missing `automake-1.7' program. But I already have automake-1.9 autoconf-2.59_2 automake-1.9 bison-1.875_3 gmake-3.80_2 libtool-1.5.6_1 m4-1.4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4-gamma cannot compile
Run aclocal; autoheader; autoconf; automake I got many warning and one error: aclocal general some warning: configure.in:277: warning: underquoted definition of AC_SYS_COMPILER_FLAG run info '(automake)Extending aclocal' or see http://sources.redhat.com/automake/automake.html#Extending%20aclocal configure.in:300: warning: underquoted definition of AC_SYS_CPU_COMPILER_FLAG configure.in:308: warning: underquoted definition of AC_SYS_OS_COMPILER_FLAG acinclude.m4:4: warning: underquoted definition of MYSQL_CHECK_READLINE_DECLARES_HIST_ENTRY acinclude.m4:24: warning: underquoted definition of MYSQL_CHECK_LIBEDIT_INTERFACE acinclude.m4:45: warning: underquoted definition of MYSQL_CHECK_NEW_RL_INTERFACE acinclude.m4:68: warning: underquoted definition of MYSQL_CHECK_SIZEOF acinclude.m4:97: warning: underquoted definition of MYSQL_TYPE_ACCEPT acinclude.m4:138: warning: underquoted definition of MYSQL_TYPE_QSORT acinclude.m4:156: warning: underquoted definition of MYSQL_TIMESPEC_TS acinclude.m4:175: warning: underquoted definition of MYSQL_TZNAME acinclude.m4:302: warning: underquoted definition of MYSQL_CHECK_ULONG acinclude.m4:320: warning: underquoted definition of MYSQL_CHECK_UCHAR acinclude.m4:338: warning: underquoted definition of MYSQL_CHECK_UINT acinclude.m4:357: warning: underquoted definition of MYSQL_CHECK_IN_ADDR_T acinclude.m4:379: warning: underquoted definition of MYSQL_PTHREAD_YIELD acinclude.m4:418: warning: underquoted definition of MYSQL_CHECK_FP_EXCEPT acinclude.m4:459: warning: underquoted definition of AM_PROG_CC_STDC acinclude.m4:503: warning: underquoted definition of AM_SANITY_CHECK acinclude.m4:522: warning: underquoted definition of MYSQL_CHECK_LIB_TERMCAP acinclude.m4:543: warning: underquoted definition of MYSQL_SIGNAL_CHECK acinclude.m4:583: warning: underquoted definition of MYSQL_CHECK_GETPW_FUNCS acinclude.m4:596: warning: underquoted definition of MYSQL_HAVE_TIOCGWINSZ acinclude.m4:609: warning: underquoted definition of MYSQL_HAVE_FIONREAD acinclude.m4:621: warning: underquoted definition of MYSQL_HAVE_TIOCSTAT acinclude.m4:634: warning: underquoted definition of MYSQL_STRUCT_DIRENT_D_INO acinclude.m4:668: warning: underquoted definition of MYSQL_TYPE_SIGHANDLER acinclude.m4:687: warning: underquoted definition of MYSQL_CXX_BOOL acinclude.m4:706: warning: underquoted definition of MYSQL_STACK_DIRECTION acinclude.m4:729: warning: underquoted definition of MYSQL_FUNC_ALLOCA acinclude.m4:798: warning: underquoted definition of MYSQL_CHECK_LONGLONG_TO_FLOAT acinclude.m4:823: warning: underquoted definition of MYSQL_CHECK_CPU acinclude.m4:870: warning: underquoted definition of MYSQL_CHECK_VIO acinclude.m4:889: warning: underquoted definition of MYSQL_FIND_OPENSSL acinclude.m4:944: warning: underquoted definition of MYSQL_CHECK_OPENSSL acinclude.m4:1008: warning: underquoted definition of MYSQL_CHECK_MYSQLFS acinclude.m4:1036: warning: underquoted definition of MYSQL_CHECK_ORBIT acinclude.m4:1641: warning: underquoted definition of AC_SYS_LARGEFILE_FLAGS acinclude.m4:1680: warning: underquoted definition of AC_SYS_LARGEFILE_SPACE_APPEND acinclude.m4:1692: warning: underquoted definition of AC_SYS_LARGEFILE_MACRO_VALUE acinclude.m4:1711: warning: underquoted definition of MYSQL_SYS_LARGEFILE autoconf general an error configure.in:218: error: possibly undefined macro: AC_PROG_LIBTOOL If this token and others are legitimate, please use m4_pattern_allow. See the Autoconf documentation. shell (cd innobase; aclocal; autoheader; autoconf; automake) shell (cd bdb/dist; sh s_all) reconfigure in mysql src directory,then make,still fail On Wed, 1 Sep 2004 16:14:51 +0800, Unreal HSHH [EMAIL PROTECTED] wrote: got thie error and broken when making mysql 4.1.4-gamma automake-1.7: not found WARNING: `automake-1.7' is needed, and you do not seem to have it handy on your system. You might have modified some files without having the proper tools for further handling them. Check the `README' file, it often tells you about the needed prerequirements for installing this package. You may also peek at any GNU archive site, in case some other package would contain this missing `automake-1.7' program. But I already have automake-1.9 autoconf-2.59_2 automake-1.9 bison-1.875_3 gmake-3.80_2 libtool-1.5.6_1 m4-1.4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is their still any reason why Stored Procedure calls are not supported in MySql?
Please Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and Don't quote an entire digest to write a new message. With regards, Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is their still any reason why Stored Procedure calls are not supported in MySql?
Please Stored procedures are in the works for MySQL 5.0: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html The manual is your friend. Don't quote an entire message/digest to add 3 lines. With regards, Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and errno 13 on RedHat
HI again, This multiple mysql proccesses are nothing to worry about - these are the threads that mysql opens to accept incoming connections. You can configure the maximum number of connections and the mysql memory usage in the /etc/my.cnf file. On Wednesday 01 September 2004 02:14, Yannick Warnier wrote: Le mar 31/08/2004 à 16:21, Dobromir Velev a écrit : Hi, Make sure that the mysql user have permissions to write into the folder /var/lib/mysql (or whatever your data folder is) . Thanks Eric and Dobromir. This was a problem of permissions on /var/lib/mysql indeed. Although it did not work immediatly after a change, I saw (how awful) that the mysql version was a 3.23 or something and replaced by the recommended version 4. Now it all works perfectly (although mysqld is now divided in ten different processes, but I don't know why and I don't mind as long as it works). Thanks, Yannick -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] How to Select multiples tables of different database in one query
Not difficult : database db1 database db2 database db3 select db1.table3.column2, db2.table5.column1, db3.table1.column7 FROM db1.table3, db2.table5, db3.table1; You can use alias in FROM on table like this : select alias1.column2, alias2.column1, alias3.column7 FROM db1.table3 AS alias1, db2.table5 AS alias2, db3.table1 AS alias3; Warning : the mysql user MUST have select (or insert, update, delete) right access on db1, db2 and db3. Fred. Tariq Murtaza wrote: *Hi All,* Can someone elaborate on How to Select multiples tables of different database in one query, perticularly when using php as scripting language. Regards *TM* -- === Frederic HARDYEmail: [EMAIL PROTECTED] HEXANET SARL URL: http://www.hexanet.fr/ ZAC Les CharmillesTel: +33 (0)3 26 79 30 05 3, allée Thierry Sabine Direct: +33 (0)3 26 61 77 84 BP 202 - 51686 REIMS CEDEX 2 FRANCE === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] How to Select multiples tables of different database in one query
Warning 2 : it is a mysql example. Whith other DBM, i don't know how to do that, but i think that its exactly the same thing. PHP version : mysql_query($query). Fred. Tariq Murtaza wrote: *Hi All,* Can someone elaborate on How to Select multiples tables of different database in one query, perticularly when using php as scripting language. Regards *TM* -- === Frederic HARDYEmail: [EMAIL PROTECTED] HEXANET SARL URL: http://www.hexanet.fr/ ZAC Les CharmillesTel: +33 (0)3 26 79 30 05 3, allée Thierry Sabine Direct: +33 (0)3 26 61 77 84 BP 202 - 51686 REIMS CEDEX 2 FRANCE === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Crash on Windows XP SP2 when not run as an administrator
On 31 Aug 2004 at 17:33, Todd O'Bryan wrote: I've tested mysql in my high school lab running from an administrator and from a student account, both from within the Cygwin terminal, and at the Windows command prompt. From the administrator account, it runs fine for hours. From the student account, it promptly (within 15 seconds or so) crashes with the same error each time. I managed to write down the error: Hi, First of all: why are you running the server from a students account? The server should be running as a system service. Open up the services control panel and check whether MySQL is listed. If it is not you need to install it by running the command c:\mysql\bin\mysqld.exe -install from a command prompt whilst logged in as an adiminstrator. You can then set the service to run automatically at boot in the services control panel ( I think this the default anyway ). Is there something that non-administrators need access to that could be causing the problem? If you REALLY need to run the server under an unprivelaged account then make sure the student has full permissions on the c:\mysql folder ( or wherever its installed) and ALL subdirectories. You may need to do more - I have never run MySQL this way (check the manual : http://dev.mysql.com) . Could it be an SP2 issue? You should also set the permissions in the new SP2 firewall so that MySQL can open TCP port 3306. Or switch it off and install a real firewall ;) Hope this helps Ian --
Re: Per Thread CPU usage
Frank [EMAIL PROTECTED] wrote: is it somehow possible to log how much CPU time a thread (or query) consumed? (this is only for debugging purposes) man mpstat This may help. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4-gamma cannot compile
fix by configure with --without-libedit --without-readline On Wed, 1 Sep 2004 16:23:36 +0800, Unreal HSHH [EMAIL PROTECTED] wrote: Run aclocal; autoheader; autoconf; automake I got many warning and one error: aclocal general some warning: configure.in:277: warning: underquoted definition of AC_SYS_COMPILER_FLAG run info '(automake)Extending aclocal' or see http://sources.redhat.com/automake/automake.html#Extending%20aclocal configure.in:300: warning: underquoted definition of AC_SYS_CPU_COMPILER_FLAG configure.in:308: warning: underquoted definition of AC_SYS_OS_COMPILER_FLAG acinclude.m4:4: warning: underquoted definition of MYSQL_CHECK_READLINE_DECLARES_HIST_ENTRY acinclude.m4:24: warning: underquoted definition of MYSQL_CHECK_LIBEDIT_INTERFACE acinclude.m4:45: warning: underquoted definition of MYSQL_CHECK_NEW_RL_INTERFACE acinclude.m4:68: warning: underquoted definition of MYSQL_CHECK_SIZEOF acinclude.m4:97: warning: underquoted definition of MYSQL_TYPE_ACCEPT acinclude.m4:138: warning: underquoted definition of MYSQL_TYPE_QSORT acinclude.m4:156: warning: underquoted definition of MYSQL_TIMESPEC_TS acinclude.m4:175: warning: underquoted definition of MYSQL_TZNAME acinclude.m4:302: warning: underquoted definition of MYSQL_CHECK_ULONG acinclude.m4:320: warning: underquoted definition of MYSQL_CHECK_UCHAR acinclude.m4:338: warning: underquoted definition of MYSQL_CHECK_UINT acinclude.m4:357: warning: underquoted definition of MYSQL_CHECK_IN_ADDR_T acinclude.m4:379: warning: underquoted definition of MYSQL_PTHREAD_YIELD acinclude.m4:418: warning: underquoted definition of MYSQL_CHECK_FP_EXCEPT acinclude.m4:459: warning: underquoted definition of AM_PROG_CC_STDC acinclude.m4:503: warning: underquoted definition of AM_SANITY_CHECK acinclude.m4:522: warning: underquoted definition of MYSQL_CHECK_LIB_TERMCAP acinclude.m4:543: warning: underquoted definition of MYSQL_SIGNAL_CHECK acinclude.m4:583: warning: underquoted definition of MYSQL_CHECK_GETPW_FUNCS acinclude.m4:596: warning: underquoted definition of MYSQL_HAVE_TIOCGWINSZ acinclude.m4:609: warning: underquoted definition of MYSQL_HAVE_FIONREAD acinclude.m4:621: warning: underquoted definition of MYSQL_HAVE_TIOCSTAT acinclude.m4:634: warning: underquoted definition of MYSQL_STRUCT_DIRENT_D_INO acinclude.m4:668: warning: underquoted definition of MYSQL_TYPE_SIGHANDLER acinclude.m4:687: warning: underquoted definition of MYSQL_CXX_BOOL acinclude.m4:706: warning: underquoted definition of MYSQL_STACK_DIRECTION acinclude.m4:729: warning: underquoted definition of MYSQL_FUNC_ALLOCA acinclude.m4:798: warning: underquoted definition of MYSQL_CHECK_LONGLONG_TO_FLOAT acinclude.m4:823: warning: underquoted definition of MYSQL_CHECK_CPU acinclude.m4:870: warning: underquoted definition of MYSQL_CHECK_VIO acinclude.m4:889: warning: underquoted definition of MYSQL_FIND_OPENSSL acinclude.m4:944: warning: underquoted definition of MYSQL_CHECK_OPENSSL acinclude.m4:1008: warning: underquoted definition of MYSQL_CHECK_MYSQLFS acinclude.m4:1036: warning: underquoted definition of MYSQL_CHECK_ORBIT acinclude.m4:1641: warning: underquoted definition of AC_SYS_LARGEFILE_FLAGS acinclude.m4:1680: warning: underquoted definition of AC_SYS_LARGEFILE_SPACE_APPEND acinclude.m4:1692: warning: underquoted definition of AC_SYS_LARGEFILE_MACRO_VALUE acinclude.m4:1711: warning: underquoted definition of MYSQL_SYS_LARGEFILE autoconf general an error configure.in:218: error: possibly undefined macro: AC_PROG_LIBTOOL If this token and others are legitimate, please use m4_pattern_allow. See the Autoconf documentation. shell (cd innobase; aclocal; autoheader; autoconf; automake) shell (cd bdb/dist; sh s_all) reconfigure in mysql src directory,then make,still fail On Wed, 1 Sep 2004 16:14:51 +0800, Unreal HSHH [EMAIL PROTECTED] wrote: got thie error and broken when making mysql 4.1.4-gamma automake-1.7: not found WARNING: `automake-1.7' is needed, and you do not seem to have it handy on your system. You might have modified some files without having the proper tools for further handling them. Check the `README' file, it often tells you about the needed prerequirements for installing this package. You may also peek at any GNU archive site, in case some other package would contain this missing `automake-1.7' program. But I already have automake-1.9 autoconf-2.59_2 automake-1.9 bison-1.875_3 gmake-3.80_2 libtool-1.5.6_1 m4-1.4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize table time estimation...
Christopher M Bergeron [EMAIL PROTECTED] wrote: Does anyone know of any guidelines or references that I can refer to with regard to how long it takes to Optimize tables? I'm running on a 3GHz x86 [single] processor box with SCSI RAID and lots of ram. The Mysql db files are approx. 35G (uncompressed). The table in question is described like this: +---+---+--+-+-+---+ | siteEvent_oid | bigint(255) | | PRI | 0 | | | vendorHandle | varchar(255) | YES | | NULL | | | eventCode | int(11) | | | 0 | | | visitorId | varchar(255) | | MUL | | | | sessionId | varchar(255) | YES | | | | | timestamp | timestamp(14) | YES | MUL | NULL | | +---+---+--+-+-+---+ Can anyone give me an estimate on how long it would take to optimize this? I don't want to start the process and not know how long it will take to complete. A very rough estimate - it will take as much time as needed to physically copy the whole table files (MYD, MYI, frm) twice. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to admin a server that currently has too many connections?
Justin Swanhart [EMAIL PROTECTED] wrote: The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? You only have to wait for some connections to free. Next time enlarge max_connections variable. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHECK TABLE looks up all databases.
Fredrik Carlsson [EMAIL PROTECTED] wrote: I have some questions regarding CHECK TABLE, OPTIMIZE TABLE. When i run these commands on table1 mysql seems to lock up all other databases, is it possible to run these things in the background so that other databases can be accessible. No. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question of negative numbers..
Hi, I have a query that returns a list of numbers ranging from -10 to +10 I would like to be able to have a 2nd column where a result of 5 is 5 but -5 is also 5, so in effect all the negative (and only the negative) results are made positive to find the deviation from zero. so 5, 4, -3, 4, -1, 0 would become 5, 4, 3, 4, 1, 0 I have been searching for if then else in google so I could do if a 0 then a = 0-a but no joy. is there a function to make negative numbers positive?
Re: A question of negative numbers..
abs() - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 12:59 PM Subject: A question of negative numbers.. Hi, I have a query that returns a list of numbers ranging from -10 to +10 I would like to be able to have a 2nd column where a result of 5 is 5 but -5 is also 5, so in effect all the negative (and only the negative) results are made positive to find the deviation from zero. so 5, 4, -3, 4, -1, 0 would become 5, 4, 3, 4, 1, 0 I have been searching for if then else in google so I could do if a 0 then a = 0-a but no joy. is there a function to make negative numbers positive? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A question of negative numbers..
Found answer to my own question: Returns the absolute value of X: mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. MySQL Reference Manual (C) 2002 MySQL AB
Re: A question of negative numbers..
[EMAIL PROTECTED] wrote on 01/09/2004 11:59:58: is there a function to make negative numbers positive? ABS(x) See http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator 1.0.12 and MySQL Query Browser 1.0.7-beta Released
MySQL Administrator 1.0.12 and MySQL Query Browser 1.0.7-beta have been released. MySQL Administrator is a GUI management console for MySQL, with support for tasks such as managing users, configuring MySQL, performing backups, editing table definitions etc. More information at: http://www.mysql.com/products/administrator/ This release contains a few bug fixed relating to backup. MySQL Query Browser is a GUI query shell, intended to allow execution of SQL queries from a Web browser alike easy to use interface. It has such handy features as query history and bookmarking, tabular visualization of results, side-by-side resultset comparison and more. You can download sources and binaries for Windows and Linux from: http://dev.mysql.com/downloads/administrator/ and http://dev.mysql.com/downloads/query-browser/ This is the first beta release of MySQL Query Browser containing the complete functionality that will be found in the final release of the tool. This includes improved query composing, a GUI command line with multiline editing and full UTF8 support, support for loading / saving / viewing BLOB fields and many more. -- Michael Zinner, GUI Developer MySQL AB, www.mysql.com Office: +43 676 753 26 30 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question of negative numbers..
ABS(column) ? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 6:59 AM Subject: A question of negative numbers.. Hi, I have a query that returns a list of numbers ranging from -10 to +10 I would like to be able to have a 2nd column where a result of 5 is 5 but -5 is also 5, so in effect all the negative (and only the negative) results are made positive to find the deviation from zero. so 5, 4, -3, 4, -1, 0 would become 5, 4, 3, 4, 1, 0 I have been searching for if then else in google so I could do if a 0 then a = 0-a but no joy. is there a function to make negative numbers positive? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem on InnoDB - Tablespace enough but engine said table full
I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:1500M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ibd ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/dat a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx - Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
Cannot compile mysql 4.1.x in FreeBSD5.3-BETA2
Compile 4.1.4, gcc -O3 -DDBUG_OFF -pipe -march=pentium4 -fno-omit-frame-pointer -O3 -D_THREAD_SAFE -o myisamchk myisamchk.o -static -D_THREAD_SAFE -D_THREAD_SAFE libmyisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lz -lkse -lcrypt -lm -lkse /usr/lib/libc.a(isnan.o)(.text+0x5e): In function `__isnanl': : multiple definition of `__isnanl' /usr/lib/libm.a(s_isnan.o)(.text+0x27): first defined here *** Error code 1 Stop in /usr/home/hshh/src/mysql-4.1.4-gamma/myisam. *** Error code 1 Compile 4.1.3 if g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I../include -O3 -DDBUG_OFF -pipe -march=pentium4 -fno-omit-frame-pointer -O3 -felide-constructors -fno-rtti -fno-exceptions -D_THREAD_SAFE -fno-implicit-templates -fno-exceptions -fno-rtti -DMYSQLD_NET_RETRY_COUNT=100 -MT mysql.o -MD -MP -MF .deps/mysql.Tpo -c -o mysql.o `test -f 'mysql.cc' || echo './'`mysql.cc; then mv -f .deps/mysql.Tpo .deps/mysql.Po; else rm -f .deps/mysql.Tpo; exit 1; fi mysql.cc:307: error: expected constructor, destructor, or type conversion before '*' token mysql.cc:307: error: expected `,' or `;' before '*' token mysql.cc: In function `int not_in_history(const char*)': mysql.cc:1269: error: `HIST_ENTRY' undeclared (first use this function) mysql.cc:1269: error: (Each undeclared identifier is reported only once for each function it appears in.) mysql.cc:1269: error: `oldhist' undeclared (first use this function) mysql.cc:1269: error: `history_get' undeclared (first use this function) *** Error code 1 Stop in /usr/home/hshh/src/mysql-4.1.3-beta/client. *** Error code 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
Rhino wrote: Okay, you've convinced me that you need some Left Joins ;-) I think you'll have to use a mix of Left Joins and Inner Joins. In the cases where you need a row even if something doesn't exist, like a subject who has an address but no phone number, use a Left Join. In any case where a row shouldn't appear unless there is a match between the keys, use an Inner Join. I wish I could be more precise than that but I don't know your particular business as well as you do. To help clarify the difference between Inner and Left Joins, I'm going to paste in part of an email I wrote last week helping someone with this point. You probably know a lot of the things in there already but I think this should help you understand the difference between an Inner Join and a Left Join. Please post again if you anything here isn't clear or you still need help despite my instant tutorial. Mix of joins solved my problem, i use left joins for addresses and phones and inner joins for field of work. Thanx for tutorial, is's been wery helpful and now i can say that i Understand ;) -- Regards Marko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
I would use the --tables option of mysqldump. It accepts more than one table name, so all you need to do is make a list of the tables you want dumped. If you are combining --tables with other options (like -B/--databases or -u or -p) make sure the --tables option is the LAST parameter in the list as everything that occurs after it will be considered a table name. details here - http://dev.mysql.com/doc/mysql/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 08/31/2004 05:57:50 PM: - Original Message - From: Emi Lu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:13 PM Subject: mysqldump all tables except 2 in a database Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. You haven't said which version of MySQL you are using or what operating system you are running so this may not be much use to you. I am running MySQL 4.0.15 on Linux Mandrake 8.2. A bash script I posted earlier today could probably be adapted to do what you want. It acquires the names of all of the databases and takes backups of them; all you'd have to do is add an 'if' statement that excluded the two databases that you didn't want to dump. I have attached the script again to this note since it is rather hard to read when it is wrapped in the email editor. Let me know if you have any questions about the script. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sort_buffer_size error, or 1034?, I think it's erroring wrong
Greetings- I have the binary dist of 4.0.20 running on a fairly adequate box (dualxeon,6gbRAM). I try to alter a table which has 733k rows and get the following: mysql ALTER TABLE `CPULoad` CHANGE `TIME` `TIME` VARCHAR( 24 ) NOT NULL; ERROR 1034: sort_buffer_size is to small 'Huh?' I say mysql select @@sort_buffer_size; ++ | @@sort_buffer_size | ++ | 1073741816 | ++ 1 row in set (0.00 sec) Any clue why this happens? It only shows up on tables where there is data. Empty tables can be modified without problem. Thanks- P - Peter J. Milanese The New York Public Library System Administrator mailto:[EMAIL PROTECTED] Tel.: +1 (212) 621-0203Fax: +1 (212) 247-5848 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary tables and replication
Hi: I'm using MySQL 4.0, and have setup replication with one server and one slave, both running LM 9.1. My application creates a temporary table on the master, which is supposed to be deleted when the connection is closed. When that happens, replication stops with the following message: ERROR: 1051 Unknown table 'tmpclisal' 040831 20:16:38 Slave: error 'Unknown table 'tmpclisal'' on query 'DROP /*!40005 TEMPORARY */ TABLE truepos.tmpclisal', error_code=1051 040831 20:16:38 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'central-bin.001' position 12475966 Is there a problem in using temporary tables and replication? I have not found any clues in the manual. If there is a problem, perhaps someone can point to a url where I can find a solution? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question of negative numbers..
[EMAIL PROTECTED] wrote: Hi, I have a query that returns a list of numbers ranging from -10 to +10 I would like to be able to have a 2nd column where a result of 5 is 5 but -5 is also 5, so in effect all the negative (and only the negative) results are made positive to find the deviation from zero. so 5, 4, -3, 4, -1, 0 would become 5, 4, 3, 4, 1, 0 I have been searching for if then else in google so I could do if a 0 then a = 0-a but no joy. is there a function to make negative numbers positive? You've already discovered ABS(), the absolute value function, which is the correct solution. I just wanted to point out that mysql does have an IF() function, as well, since you didn't find it. For example, you could accomplish the same thing with IF(a = 0, a, -a) IF() is documented in the manual http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html. I'd suggest trying the manual first, rather than Google, when looking for mysql functions. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables and replication
I believe some versions of 4.0 have problem with auto dropping temporary tables in replication. The easy fix is to drop temporary tables when you are finished with them instead of relying on mysql to do it for you. -- Eric Bergen [EMAIL PROTECTED] On Wed, 1 Sep 2004 08:16:43 -0600, Alfredo Cole [EMAIL PROTECTED] wrote: Hi: I'm using MySQL 4.0, and have setup replication with one server and one slave, both running LM 9.1. My application creates a temporary table on the master, which is supposed to be deleted when the connection is closed. When that happens, replication stops with the following message: ERROR: 1051 Unknown table 'tmpclisal' 040831 20:16:38 Slave: error 'Unknown table 'tmpclisal'' on query 'DROP /*!40005 TEMPORARY */ TABLE truepos.tmpclisal', error_code=1051 040831 20:16:38 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'central-bin.001' position 12475966 Is there a problem in using temporary tables and replication? I have not found any clues in the manual. If there is a problem, perhaps someone can point to a url where I can find a solution? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling MySQL with OpenSSL
All, I am trying to compile MySQL with OpenSSL in an alternate location ( /data/OpenSSL ). When I do that either configure will say that no installation of OpenSSH is there (even with the --with-openssl-includes= and --with-openssl-libs= ), or it will compile without OpenSSL support ( show valiables like ... ). My configure is as follows: ./configure --prefix=/data/MySQL --exec-prefix=/data/MySQL \ --enable-thread-safe-client --enable-asembler \ --enable-local-infile --with-extra-charsets=complex \ --disable-shared --with-openssl=/data/OpenSSL \ --without-docs --with-vio --with-openssl-includes=/data/OpenSSL/include/openssl \ --with-openssl-libs=/data/OpenSSL/lib Has anybody suceessfuly compiled MySQL with OpenSSL support in an alternate location? The MySQL version is 4.0.20. Any help would be appreciated. Best regards, ES __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Mikhail Entaltsev wrote: You _could_ try adding an identical primary key to the stat_in table as you have on the 321st_stat table. However, since we need all of the rows from stat_in in the results, I am not sure that it will help speed up the join (because it's a left join). Even though I think the index would be ignored, it is worth a try to see if it would make a difference with a new EXPLAIN. Tried this, setup a matching index on the temp table, it took 17 hours to load the input file into the temp stat_in table, so it's definitly not going to save me any time I've used enable/disable keys before, but 2 problems, one it was only 5 % faster, and two, I will have primary key violations when I enable the primary key, it wont enable it, at least that's my understanding of it. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tuning suggestion for large query
Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling MySQL with OpenSSL on alternate location
All, I am trying to compile MySQL with OpenSSL in an alternate location ( /data/OpenSSL ). When I do that either configure will say that no installation of OpenSSH is there (even with the --with-openssl-includes= and --with-openssl-libs= ), or it will compile without OpenSSL support ( show variables like ... ). My configure is as follows: ./configure --prefix=/data/MySQL --exec-prefix=/data/MySQL \ --enable-thread-safe-client --enable-assembler \ --enable-local-infile --with-extra-charsets=complex \ --disable-shared --with-openssl=/data/OpenSSL \ --without-docs --with-vio --with-openssl-includes=/data/OpenSSL/include/openssl \ --with-openssl-libs=/data/OpenSSL/lib Has anybody successfully compiled MySQL with OpenSSL support in an alternate location? The MySQL version is 4.0.20. Any help would be appreciated. Best regards, ES __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad too many connections error (os x)
Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bad too many connections error (os x)
Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Best of luck, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.4 is released
Hi! InnoDB is a MySQL table type that provides foreign key constraints, ACID transactions, row-level locking, consistent (MVCC) non-locking reads, transaction savepoints, and a commercial InnoDB Hot Backup tool to MySQL. InnoDB is included in all MySQL releases, except the commercial 'MySQL Classic' binaries. For InnoDB, MySQL-4.1.4 is mainly a bugfix release. You can download the binaries from http://dev.mysql.com/downloads/mysql/4.1.html NOTE that an upgrade from 4.0 to 4.1.3 or later requires a rebuild of some tables if you have stored control characters (ASCII value 32) to non-latin1 non-BINARY indexed columns in an InnoDB table. If you have stored control characters in an indexed non-BINARY column in a MyISAM table, you have to REPAIR or rebuild that table. There is also a rebuild need if you have used a TIMESTAMP column in an InnoDB table in 4.1.0 - 4.1.3. Functionality added or changed: * Important: Made internal representation of TIMESTAMP values in InnoDB in 4.1 to be the same as in 4.0. This difference resulted in wrong datetime values in TIMESTAMP columns in InnoDB tables after upgrade from 4.0 to 4.1. (Bug #4492) Warning: extra steps during upgrade required! This means that if you are upgrading from 4.1.x, where x = 3, to 4.1.4 you should use mysqldump for saving and then restoring your InnoDB tables with TIMESTAMP columns. No conversion is needed if you upgrade from 3.23 or 4.0 to 4.1.4 or later. * Added a new startup option innodb_locks_unsafe_for_binlog. This option forces InnoDB not to use next-key locking in searches and index scans. * Added innodb_status_file system variable to mysqld to control whether output from SHOW INNODB STATUS is written to a `innodb_status.pid' file in the data directory. By default, the file is not created. To create it, start mysqld with the --innodb_status_file=1 option. * Changes for NetWare to exit InnoDB gracefully on NetWare even in a case of an assertion failure, instead of intentionally crashing the `mysqld' server process. Also keep in mind: * Important: Starting from MySQL 4.1.3, InnoDB uses the same character set comparison functions as MySQL for non-latin1_swedish_ci character strings that are not BINARY. This changes the sorting order of space and characters ASCII(32) in those character sets. For latin1_swedish_ci character strings and BINARY strings, InnoDB uses its own pad-spaces-at-end comparison method, which stays unchanged. If you have an InnoDB table created with MySQL 4.1.2 or earlier, with an index on a non-latin1 character set (in the case of 4.1.0 and 4.1.1 with any character set) CHAR/VARCHAR/or TEXT column that is not BINARY but may contain characters ASCII(32), then you should do ALTER TABLE or OPTIMIZE table on it to regenerate the index, after upgrading to MySQL 4.1.3 or later. Bugs fixed: * Fixed a bug in ON DELETE CASCADE and ON UPDATE CASCADE foreign key constraints: long chains of cascaded operations would cause a stack overflow and crash the server. Cascaded operations are now limited to 15 levels. (Bug #4446) * Increment the InnoDB watchdog timeout during CHECK TABLE. (Bug #2694) * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like below in the `.err' log. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex * Fixed a glitch introduced in 4.0.18 and 4.1.2: in SHOW TABLE STATUS InnoDB systematically overestimated the row count by 1 if the table fit on a single 16 kB data page. * InnoDB created temporary files with the C library function tmpfile(). On Windows, the files would be created in the root directory of the current file system. To correct this behavior, the invocations of tmpfile() were replaced with code that uses the function create_temp_file() in the MySQL portability layer. (Bug #3998) * If you RENAMEd a table, InnoDB forgot to load the foreign key constraints that reference the new table name, and forgot to check that they are compatible with the table. * If there was little file I/O in InnoDB, but the insert buffer was used, it could happen that 'Pending normal aio reads' was bigger than 0, but the I/O handler thread did not get waken up in 600 seconds. This resulted in a hang, and an intentional crashing of `mysqld'. An outstanding bug: * If you have specified innodb_file_per_table in my.cnf, and try to create a TEMPORARY InnoDB type table, InnoDB will complain that it cannot find a path ./tmp/tablename. Workaround: create a MySQL database whose name is tmp (on Windows, temp). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
I think that there is an error in your code. I have had this problem with a small application I wrote: The problem arises when you don't send the quit command to mysql before dropping tcp. You must send a quit command in every case (if you opened a socket). I can not be more precise because i don't know what labguage you use (C, php, ...). There is a command to reenable connection; if my memory is good: mysqladmin -flush-hosts or something (see mysqladmin reference or help). Santino At 9:02 -0700 1-09-2004, Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert/select for multiple tables...
hi... trying to figure out how to structure an insert/select for a multiple table situation... sort of... insert table1, table2 (table1.item1, table1.item2, table2.item1,...) select a.q1, b.q2 from a1 left join a2 on a2.t=a1.t where a2.r='4'; i can't seem to figure out the syntax for the multiple table insert... searching through google/mysql hasn't shed any light on this.. thanks for any pointers/comments... -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database connectivity
Hi, I wanted to setup connectivity between mysql database and oracle database.I have one database in mysql from which all tables data I want to insert real time data into oracle database.How to connect mysql database with oracle database? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages!
Table check and tips
While I currently have about a dozen tables, I'd like to throw this one up for any opinions, suggestion, warnings, things I may not be saying. It's not a started entity - schema diagram but should be clear for the purposes of the email. Table: Posts (innodb) -- PostID (auto inc, int) set to PK -- Entered (timestamp) -- StartP (timestamp) (user decision) -- PLen (Int) (user decision) -- VenID (int) (Not FK'd to the VenTable though, don't see the need) Session variables and joins keeping them related --- Ind (varchar) based on user choice from menu provides IndID from static table - City - (varchar) based on user choice from menu provides CitID from static table --- State - (varchar) based on user choice from menu provides StatID from static table - Term - (varchar) based on user choice from menu provides TermID from static table -- Rate - (varchar) User text input -- Contact - (varchar) right now they are pulled dynamic fields from the Ven Table, defaulted into form for Posts and put into this table Contact Email - (varchar) same as above - I'm starting to see an issue here, meaning, possible bad design), My strategy was that one company may have an account with various users sharing the one contact name and password. Not sure. One more insignificant field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database connectivity
What will the connection be used for? Replication? Query between datasources? There are some third party applications such as Golden Gate that have replication products. You could also write the connection yourself in a language such as Java or C. -Original Message- From: Seena Blace To: Seena Blace; [EMAIL PROTECTED] Sent: 9/1/04 11:30 AM Subject: Re: Database connectivity Hi, I wanted to setup connectivity between mysql database and oracle database.I have one database in mysql from which all tables data I want to insert real time data into oracle database.How to connect mysql database with oracle database? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad quot;too many connectionsquot; error (os x)
Michael Winston said: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael Actually php may be using connection pooling which will NOT close off the connections. It can be configured either way in the apache php configuration file. I use squirrelmail, which is a php based webmail system with MySQL holdin configuration information and those connections never close, they just go inactive. There is one connection for each apache child. Since apache doesn't use the same child the answer the query each time, on a busy server you may end up with quite a few connecitons. Just a thougth. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 Installation Fault
Hi, I've used MySQL 3.23 on Redhat 9 for a long time. Recently, I'm thinking of using MySQL 4 because some applications need it. I download a rpm from mysql.com and have it rpm. It can't start and comes up with problem as -== terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffd2b8, stack_bottom=0x5f656c62, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x61742d2d is invalid pointer thd-thread_id=1966159154 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040901 23:38:39 mysqld ended === website provides a detailed information on it. However, I guess it's some minor and simple thing that makes me stucked here. I tried to install it on another machine, the same thing comes up. But when I try to use mysqld_safe, it's okay. But not this machine I want to run it. I used the same procedure for installation, both systems are Redhat 9 without big changes. The major difference is on the hardware. 1) This one is okay by mysqld_safe: Celeron 2.0, 256Mb DDR, 2) This one is not okay by any means: Pentium II, 128MB RAM Will you have any experience to share? Thanks a lot. Regards, Yw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston wrote: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael You'll also find this problem if you have some badly-optimised queries, or writes that take a long time to run on a frequently-accessed table. For example, if you have a table that frequently accessed and run a slow update on it, any thread trying to read from that table will block. If you get more selects happening to that table coming in while it's still locked, your number of connections in use will shoot upwards rapidly until the slow update finishes and the table is unlocked. Have a look in your slow query log (or turn it on if it's not enabled) to look for any queries like this. Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bad too many connections error (os x)
Not if the host that is blocked is `localhost`. -Original Message- From: Michael Winston To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 9/1/04 11:24 AM Subject: Re: bad too many connections error (os x) This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database connectivity
AFAIK, MySQL servers can only talk to other MySQL servers and that is only through the replication protocol/connections. Beyond that you will need to make your server-to-server connections through some kind of external client. Can Oracle act as a client to foreign databases? If it cannot, I believe you will need to write some code to develop a custom solution. It may be possible to use the MySQL source code to enable your custom client to behave as a MySQL replication slave then transform the binlog entries you receive into valid Oracle statements. Other than that, I think that you would need to periodically poll the MySQL server for changes and duplicate them into your Oracle server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace [EMAIL PROTECTED] wrote on 09/01/2004 12:30:12 PM: Hi, I wanted to setup connectivity between mysql database and oracle database.I have one database in mysql from which all tables data I want to insert real time data into oracle database.How to connect mysql database with oracle database? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages!
Re: bad too many connections error (os x)
This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert/select for multiple tables...
bruce wrote: hi... trying to figure out how to structure an insert/select for a multiple table situation... sort of... insert table1, table2 (table1.item1, table1.item2, table2.item1,...) select a.q1, b.q2 from a1 left join a2 on a2.t=a1.t where a2.r='4'; i can't seem to figure out the syntax for the multiple table insert... searching through google/mysql hasn't shed any light on this.. thanks for any pointers/comments... -bruce That's because there is no multiple-table INSERT syntax. See the manual for correct INSERT syntax http://dev.mysql.com/doc/mysql/en/INSERT.html. I can't tell from your example what you are trying to do. You only select 2 columns, but you seem to be trying to insert the result into at least 4 columns. Perhaps if you carefully described what you have and what you want, someone will be able to suggest a way to accomplish your goals. Michael P.S. You don't need a LEFT JOIN in that example. A simple JOIN would do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.4 still reports itself as 4.1.3b-beta
I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert/select for multiple tables...
michael... it was meant as an example, to convey what i want to do, which is do a simltaneaous insert into multiple tables at the same time. the syntax concerning the left join/elements to be inserted was not intended to be syntacticly (sp?) correct!!! and as i stated, searching through mysql docs/google didn't shed light on this issue. could be because mysql doesn't permit this kind of interaction, although it is permitted with the update... does this make more sense to you now... -regards -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 10:06 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: insert/select for multiple tables... bruce wrote: hi... trying to figure out how to structure an insert/select for a multiple table situation... sort of... insert table1, table2 (table1.item1, table1.item2, table2.item1,...) select a.q1, b.q2 from a1 left join a2 on a2.t=a1.t where a2.r='4'; i can't seem to figure out the syntax for the multiple table insert... searching through google/mysql hasn't shed any light on this.. thanks for any pointers/comments... -bruce That's because there is no multiple-table INSERT syntax. See the manual for correct INSERT syntax http://dev.mysql.com/doc/mysql/en/INSERT.html. I can't tell from your example what you are trying to do. You only select 2 columns, but you seem to be trying to insert the result into at least 4 columns. Perhaps if you carefully described what you have and what you want, someone will be able to suggest a way to accomplish your goals. Michael P.S. You don't need a LEFT JOIN in that example. A simple JOIN would do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4-gamma cannot compile
At 16:14 +0800 9/1/04, Unreal HSHH wrote: got thie error and broken when making mysql 4.1.4-gamma automake-1.7: not found WARNING: `automake-1.7' is needed, and you do not seem to have it handy on your system. You might have modified some files without having the proper tools for further handling them. Check the `README' file, it often tells you about the needed prerequirements for installing this package. You may also peek at any GNU archive site, in case some other package would contain this missing `automake-1.7' program. But I already have automake-1.9 autoconf-2.59_2 automake-1.9 bison-1.875_3 gmake-3.80_2 libtool-1.5.6_1 m4-1.4.1 I see something similar. If I do this first: (cd innobase; aclocal;autoheader;autoconf;automake) aclocal;autoheader;autoconf;automake And then run configure, the build succeeds. Does that work for you? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert/select for multiple tables...
At 10:52 -0700 9/1/04, bruce wrote: michael... it was meant as an example, to convey what i want to do, which is do a simltaneaous insert into multiple tables at the same time. the syntax concerning the left join/elements to be inserted was not intended to be syntacticly (sp?) correct!!! and as i stated, searching through mysql docs/google didn't shed light on this issue. could be because mysql doesn't permit this kind of interaction, although it is permitted with the update... You can't do it. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table dump into binary
how to take binary/ascii dump of table? [EMAIL PROTECTED] wrote:AFAIK, MySQL servers can only talk to other MySQL servers and that is only through the replication protocol/connections. Beyond that you will need to make your server-to-server connections through some kind of external client. Can Oracle act as a client to foreign databases? If it cannot, I believe you will need to write some code to develop a custom solution. It may be possible to use the MySQL source code to enable your custom client to behave as a MySQL replication slave then transform the binlog entries you receive into valid Oracle statements. Other than that, I think that you would need to periodically poll the MySQL server for changes and duplicate them into your Oracle server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace wrote on 09/01/2004 12:30:12 PM: Hi, I wanted to setup connectivity between mysql database and oracle database.I have one database in mysql from which all tables data I want to insert real time data into oracle database.How to connect mysql database with oracle database? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages!
Server hanging
Hello, I had a problem with our MySQL server-4.0.18 where it suddenly stoped working. Even a KILL command didn´t killed the mysqld process. I looked for any error message in the file /usr/local/mysql/hostname.err, but I couldn´t see any error message about such time. Does anyone knows what could make MySQL hangs? And where can I find error messages that could tell me what happend? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert/select for multiple tables...
Bruce, Sorry, I guess I wasn't clear. I understood what you were asking, and I thought I answered it. Your search of the mysql docs and google found nothing about multiple-table inserts because you can't do that. I think the mysql manual page I referenced is clear: INSERT Syntax INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] See? Multiple columns, but only one table. My intent in my second paragraph was to offer help. Since there is no multi-table insert, you need to solve your problem a different way. You'll probably need a separate insert for each table, and possibly a temporary table. I didn't want to guess what you need, however, based on your example, because I wasn't sure what you really wanted to accomplish. I suppose it might have been more helpful to offer a couple of general suggestions. Here goes: INSERT INTO table1 SELECT id, a.q1, b.q1 FROM a JOIN b ON a2.t=a1.t WHERE a2.r='4'; INSERT INTO table2 SELECT id, a.q2, b.q2 FROM a JOIN b ON a2.t=a1.t WHERE a2.r='4'; Of course, that leaves a possibility that table a or b gets changed in between, and running the JOIN twice may not be all that efficient. This might be better: CREATE TEMPORARY TABLE holding SELECT id, a.q1 aq1, a.q2 aq2, b.q1 bq1, b.q2 bq2 FROM a JOIN b ON a2.t=a1.t WHERE a2.r='4'; INSERT INTO table1 SELECT id, aq1, bq1 FROM holding; INSERT INTO table2 SELECT id, aq2, bq2 FROM holding; DROP TABLE holding; If that doesn't help you with what you need, reply with a description of the problem to be solved, and I'm sure someone will have a solution. Finally, about my P.S. There are many, many questions on this list that come down to the poster not knowing/understanding JOIN vs. LEFT JOIN. They either need a LEFT JOIN, or they always use LEFT JOIN even when they don't need it. Perhaps you do not fall into that category, and it is perfectly obvious to you that WHERE a2.r='4' throws away any extra (null) rows created by the LEFT JOIN, making the LEFT JOIN equivalent to, but slower than, a simple JOIN. Michael bruce wrote: michael... it was meant as an example, to convey what i want to do, which is do a simltaneous insert into multiple tables at the same time. the syntax concerning the left join/elements to be inserted was not intended to be syntacticly (sp?) correct!!! and as i stated, searching through mysql docs/google didn't shed light on this issue. could be because mysql doesn't permit this kind of interaction, although it is permitted with the update... does this make more sense to you now... -regards -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] bruce wrote: hi... trying to figure out how to structure an insert/select for a multiple table situation... sort of... insert table1, table2 (table1.item1, table1.item2, table2.item1,...) select a.q1, b.q2 from a1 left join a2 on a2.t=a1.t where a2.r='4'; i can't seem to figure out the syntax for the multiple table insert... searching through google/mysql hasn't shed any light on this.. thanks for any pointers/comments... -bruce That's because there is no multiple-table INSERT syntax. See the manual for correct INSERT syntax http://dev.mysql.com/doc/mysql/en/INSERT.html. I can't tell from your example what you are trying to do. You only select 2 columns, but you seem to be trying to insert the result into at least 4 columns. Perhaps if you carefully described what you have and what you want, someone will be able to suggest a way to accomplish your goals. Michael P.S. You don't need a LEFT JOIN in that example. A simple JOIN would do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table check and tips
My comments intermixed below :-) Stuart Felenstein [EMAIL PROTECTED] wrote on 09/01/2004 12:30:19 PM: While I currently have about a dozen tables, I'd like to throw this one up for any opinions, suggestion, warnings, things I may not be saying. It's not a started entity - schema diagram but should be clear for the purposes of the email. Table: Posts (innodb) -- PostID (auto inc, int) set to PK -- Entered (timestamp) -- StartP (timestamp) (user decision) -- PLen (Int) (user decision) -- VenID (int) (Not FK'd to the VenTable though, don't see the need) Session variables and joins keeping them related --- Ind (varchar) based on user choice from menu provides IndID from static table so call this IndID. As a matter of personal preference (very personal) I name my FK fields (even if I don't actually create a FK to enforce the relationship) like tablename_columnname. So I would call it Ind_ID (or whatever your other table is called) - City - (varchar) based on user choice from menu provides CitID from static table and this CitID or City_ID --- State - (varchar) based on user choice from menu provides StatID from static table ditto: StatID (State_ID?) - Term - (varchar) based on user choice from menu provides TermID from static table ditto: TermID (Term_ID?). However, wouldn't you need to support multiple terms per posting? This would be a situation where normalization requires you to split this value into its own table like: CREATE TABLE Posts_Terms ( Posts_PostID int, Term_ID int, Primary Key(Posts_PostID, Term_ID), KEY(Term_Id) ) TYPE=InnoDB, COMMENT 'This table holds a list of terms used to identify a post for searches' The multi-column primary key will prevent any single term from being applied more than once to any one post. The second index would speed up searches to find which posts are associated with specific terms. -- Rate - (varchar) User text input -- Contact - (varchar) right now they are pulled dynamic fields from the Ven Table, defaulted into form for Posts and put into this table ??? - I have no clue what the Ven table has in it or how you defined fields that change their data types and defaults (dynamic fields ?) so your comment makes less than no sense to me. Don't confuse your web/UI design with your data design. They may be symbiotic but they are definitely NOT the same. Contact Email - (varchar) same as above - I'm starting to see an issue here, meaning, possible bad design), My strategy was that one company may have an account with various users sharing the one contact name and password. Not sure. ??? - again I can't understand the context of your comment. I know from previous messages to the list that you are working on a job seekers web site but you don't seem to have a well defined idea of who needs to know what about whom. One more insignificant field. so why mention that you have it? If it is truly insignificant, don't add it to the table. ... May I suggest that you temporarily stop development and go through a bit of basic object modelling? Start by defining the different users (also known by the UML term actors) of your system and what you want them to be able to do. I think I can name 3 but you may have more: JobSeekers, EmployeeSeekers, and Administrators (please create your OWN names that better fit YOUR business model). A JobSeeker is someone who wants to be found by a EmployeeSeeker. They will want their profile/resume/CV to be accurate, online, and searchable. EmployeeSeekers are those people who want to find people (JobSeekers) they can hire to fill certain positions. They need to be able to search all of the profile/resume/CV(s) of the JobSeekers on your site and initiate contact with them if they meet their hiring criteria. Administrators are superusers (like you) who need to be able to change anything about everything associated with the site. Most of what an Administrator needs to do will probably be accomplished through off-site (not-web-based) methods like direct editing of your data tables using MySQL tools and scripts. However, you may want to develop certain web-based tools to help with list management (terms, cities, states, etc) and other related processes (like creating and disabling JobSeeker logins). The trade-off to consider is will a
Re: Table dump into binary
Have you looked at the manual? The program mysqldump jumps to mind: http://dev.mysql.com/doc/mysql/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace [EMAIL PROTECTED] 09/01/2004 02:04 PM To [EMAIL PROTECTED] cc [EMAIL PROTECTED], Seena Blace [EMAIL PROTECTED] Subject Table dump into binary how to take binary/ascii dump of table? [EMAIL PROTECTED] wrote: AFAIK, MySQL servers can only talk to other MySQL servers and that is only through the replication protocol/connections. Beyond that you will need to make your server-to-server connections through some kind of external client. Can Oracle act as a client to foreign databases? If it cannot, I believe you will need to write some code to develop a custom solution. It may be possible to use the MySQL source code to enable your custom client to behave as a MySQL replication slave then transform the binlog entries you receive into valid Oracle statements. Other than that, I think that you would need to periodically poll the MySQL server for changes and duplicate them into your Oracle server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace wrote on 09/01/2004 12:30:12 PM: Hi, I wanted to setup connectivity between mysql database and oracle database.I have one database in mysql from which all tables data I want to insert real time data into oracle database.How to connect mysql database with oracle database? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages!
another insert/select for multiple tables... (or, determining previous auto-increment ID)
The question: Is there anything in MySQL that will allow me to determine, accurately, the last auto-incremented field from a particular database, so I can then insert based upon this into another table? What if 2 users input at the same time? Please see the 'long explanation' for further details. Long explanation: I've been reading over the previous thread for this. I am faced with a similar situation and would appreciate some syntax assistance. I am only partially finished. I have 2 tables to insert into: candidate resume When a new entry is entered via an INSERT statement I have set up, an auto-increment integer is assigned (Candidate_ID) in the 'candidate' table. This insert is structured in PHP: $query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial, Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability, Interview_Contact, US_Experience, Location_Country) values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '', '$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working', '$Available', '', '$location', '', '$relocation', '$Tech_Ranking', '$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate', '$Def_Rate_Unit', '', '', '$interview_availability', '$interview_contact', '$US_Experience', ''); $result = mysql_query($query) or die(Error: . mysql_error()); -- Now, having entered that data, I need to insert different fields from my form into 'resume'; syntax is probably the same, but where I'm stuck is how to determine what auto-incremented number was assigned to the entry in candidate, so I can then insert columns based on this. resume is structured as so: Field TypeNullDefault Candidate_IDint(10) Yes NULL Section_ID int(10) Yes NULL Section_Value longtextYes NULL Scenario is as follows: New entry: James Brown, is assigned 10700 for Candidate_ID and is inserted into table 'candidate'. Following this insert, insert is required for 6 rows of data into table 'resume': Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form data from textbox Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form data from a different textbox etc. until Section_ID 6. How would I go about setting this up for resume, now that it's set up for candidate? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DESPERATE: Disconnect from Database Question
Hi folks, heres a real stupid question, but Im baffled. I am running MySQL v . 4.1.3b-beta-nt , JVM 1.4.2_05-b04 by SUN, Tomcat 5.0.27,Windows 2000 Server Im attempting to run SERVLETS for my application. I have a HTML form to kick off my application in Tomcat App Manager It has 2 options on it,Company Login and User Login. Company Login opens a new window to accept the Login-id and password, calls a Servlet to Open MySql, update it, close it then closes the window returning to the HTML form User Login opens a new window to accept the Login-id and password, calls a Servlet to Open MySql, update it, close it then continues on into the application My problem is this, I log into the company, it accepts all the data, opens, updates, closes Myql all with a Sqlstate = 0 (perfect) I then log attemp to log into the USER and when it attempts to open the database, it returns SQLSTATE = 08002 (connection already in use)... Any other calls to database result in SQLSTATE = 08000 (connection exception) if I back out to Tomcat App Manger, Stop the Application, then Start the application, go back into the HTML screen and choose LOGIN USER, I accept the variables, open the database, update it, and close it, all with SQLSTATE = 0. It seems, that the connection is not being released after I do updates, I get a ONE TIME connect, update... My code for the disconnect is pretty simple commit disconnect all So I thought, maybe I need a QUIT statement, I changed to.. Commit Quit Disconnect all The QUIT command throws an SQLSTATE = 42000 (invalid syntax) Is QUIT not supported under MYSQL can anyone ofer me CLUES as to why it may not be Disconnected successfuly Im desperate here Mike
Re: another insert/select for multiple tables... (or, determining previous auto-increment ID)
Since you should still have the same information that you used to create your entry in the candidate table you can do something like: SELECT @candidateID := Candidate_ID FROM candidate WHERE Last_Name = '$lastname' AND First_Name = '$firstname' AND Middle_Initial = '$middle' INSERT INTO resume (Candidate_ID, Section_ID, Section_Value) VALUES (@candidateID, 1, '$field1'), (@candidateID, 2, '$field2'), (@candidateID, 3, '$field3'), (@candidateID, 4, '$field4'), (@candidateID, 5, '$field5'), (@candidateID, 6, '$field6'); If you use this format, you have to worry about your field data (field1 through field6) messing with your single quotes. This is especially true as I think those are user-entered fields, right? I can't think of it off the top of my head but I believe that PHP already has a function that will safely escape text strings so that they are INSERTable into MySQL using single quotes (sorry for the brain freeze). Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eve Atley [EMAIL PROTECTED] wrote on 09/01/2004 03:02:21 PM: The question: Is there anything in MySQL that will allow me to determine, accurately, the last auto-incremented field from a particular database, so I can then insert based upon this into another table? What if 2 users input at the same time? Please see the 'long explanation' for further details. Long explanation: I've been reading over the previous thread for this. I am faced with a similar situation and would appreciate some syntax assistance. I am only partially finished. I have 2 tables to insert into: candidate resume When a new entry is entered via an INSERT statement I have set up, an auto-increment integer is assigned (Candidate_ID) in the 'candidate' table. This insert is structured in PHP: $query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial, Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability, Interview_Contact, US_Experience, Location_Country) values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '', '$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working', '$Available', '', '$location', '', '$relocation', '$Tech_Ranking', '$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate', '$Def_Rate_Unit', '', '', '$interview_availability', '$interview_contact', '$US_Experience', ''); $result = mysql_query($query) or die(Error: . mysql_error()); -- Now, having entered that data, I need to insert different fields from my form into 'resume'; syntax is probably the same, but where I'm stuck is how to determine what auto-incremented number was assigned to the entry in candidate, so I can then insert columns based on this. resume is structured as so: Field Type Null Default Candidate_ID int(10)Yes NULL Section_ID int(10)Yes NULL Section_Value longtextYes NULL Scenario is as follows: New entry: James Brown, is assigned 10700 for Candidate_ID and is inserted into table 'candidate'. Following this insert, insert is required for 6 rows of data into table 'resume': Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form data from textbox Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form data from a different textbox etc. until Section_ID 6. How would I go about setting this up for resume, now that it's set up for candidate? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another insert/select for multiple tables... (or, determining previous auto-increment ID)
Yes, LAST_INSERt_ID(). It's connection-specific, not db-specific. Because it is connection-specific, your 2-users issue is avoided. See the manual for the details http://dev.mysql.com/doc/mysql/en/Getting_unique_ID.html. Michael Eve Atley wrote: The question: Is there anything in MySQL that will allow me to determine, accurately, the last auto-incremented field from a particular database, so I can then insert based upon this into another table? What if 2 users input at the same time? Please see the 'long explanation' for further details. Long explanation: I've been reading over the previous thread for this. I am faced with a similar situation and would appreciate some syntax assistance. I am only partially finished. I have 2 tables to insert into: candidate resume When a new entry is entered via an INSERT statement I have set up, an auto-increment integer is assigned (Candidate_ID) in the 'candidate' table. This insert is structured in PHP: $query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial, Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability, Interview_Contact, US_Experience, Location_Country) values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '', '$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working', '$Available', '', '$location', '', '$relocation', '$Tech_Ranking', '$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate', '$Def_Rate_Unit', '', '', '$interview_availability', '$interview_contact', '$US_Experience', ''); $result = mysql_query($query) or die(Error: . mysql_error()); -- Now, having entered that data, I need to insert different fields from my form into 'resume'; syntax is probably the same, but where I'm stuck is how to determine what auto-incremented number was assigned to the entry in candidate, so I can then insert columns based on this. resume is structured as so: Field TypeNullDefault Candidate_IDint(10) Yes NULL Section_ID int(10) Yes NULL Section_Value longtextYes NULL Scenario is as follows: New entry: James Brown, is assigned 10700 for Candidate_ID and is inserted into table 'candidate'. Following this insert, insert is required for 6 rows of data into table 'resume': Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form data from textbox Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form data from a different textbox etc. until Section_ID 6. How would I go about setting this up for resume, now that it's set up for candidate? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import DBF data into MySQL table
Ave, Is there a way to directly import DBF data into MySQL table or do I HAVE to convert it into CSV (Using EXCEL) or some other method? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
This also may caused by the TTL of the http socket and the launched zombie forks by the http/php server ( the FIN_WAIT_2 problem!!). Thats what it happend to me . I dunno who waited for who, and became zombie, was it MySQL waiting for timeout or was socket wating to timeout. You can test this easily. Simultaneously issue same instance of the service in question from same network and watch the netstat and top -t. -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook Alex Greg wrote: Michael Winston wrote: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael You'll also find this problem if you have some badly-optimised queries, or writes that take a long time to run on a frequently-accessed table. For example, if you have a table that frequently accessed and run a slow update on it, any thread trying to read from that table will block. If you get more selects happening to that table coming in while it's still locked, your number of connections in use will shoot upwards rapidly until the slow update finishes and the table is unlocked. Have a look in your slow query log (or turn it on if it's not enabled) to look for any queries like this. Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tuning suggestion for large query
At 10:40 AM 9/1/2004, you wrote: Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. Thanks. - Try the Handler command with an Index if you need the results sorted. It may do the trick. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tuning suggestion for large query
Thanks Mike. Seems like even with handler, the big query process is still consuming all my RAM and swap and being killed with error 'VM: killing process mysql __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)' I would like to find a startup parameter either for client or server to limit per thread memory usage. -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 4:39 PM To: [EMAIL PROTECTED] Subject: Re: tuning suggestion for large query At 10:40 AM 9/1/2004, you wrote: Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. Thanks. - Try the Handler command with an Index if you need the results sorted. It may do the trick. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication failed
Hi, I've set up a master-slave MySQL configuration. When I do: LOAD DATA FROM MASTER on the slave I get the following error: ERROR 1189: Net error reading from master . Errorlog displays: create_table_from_dump: failed in handler::net_read_dump() The master logs displays nothing. A few databases have been transferred. The size of all database combined which must be transferred is ca 150mb (pretty little I guess ;) I'm pretty clueless what it causes. Does anyone has ideas? Thank in Advanced, Frank de Bot! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication bug?
Hi Folks, We are trying to put a monitoring solution in place at a client and have come up against something during testing. If the replication user disappears off the master and the slave cannot log in, the Slave_IO_Thread still shows running and no error in the last error number field. Does anybody know if this is intentional? I can't find any references to this in the doco. This is MySQL 4.0.20 and RH Advanced Server 2.1 After deleting the user on the master, the following message appears in the log but the slave status shows a healthy relationship. 40902 10:54:13 Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3307': Error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' errno: 1045 mysql show slave status \G *** 1. row ** Master_Host: hpim202-98.aus.hp.com Master_User: repl Master_Port: 3307 Connect_retry: 60 Master_Log_File: hpim202-98-bin.001 Read_Master_Log_Pos: 913879 Relay_Log_File: MAU023W-relay-bin.010 Relay_Log_Pos: 305 Relay_Master_Log_File: hpim202-98-bin.001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 913879 Relay_log_space: 301 1 row in set (0.00 sec) Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax
UPDATE string segment?
I have a column that holds hard disk file location info such as: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf etc. . . (many files located in the same folder) Can anyone suggest a single statement (or multiple) that would update every file location located in this same folder to another location, say: /Volumes/External HD/aFolder/aChildFolder/aFile.pdf /Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf etc . . . ? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication bug?
David, I haven't ever attempted to delete the slave user on the master, and since I only run replication on 4.1 boxes and not 4.0 boxes, I won't be able to help much. But I would probably submit it to http://bugs.mysql.com and they can verify that it is a bug. But they will probably not recommend deleting the slave user again. :) Donny -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 8:53 PM To: MySQL List Subject: Replication bug? Hi Folks, We are trying to put a monitoring solution in place at a client and have come up against something during testing. If the replication user disappears off the master and the slave cannot log in, the Slave_IO_Thread still shows running and no error in the last error number field. Does anybody know if this is intentional? I can't find any references to this in the doco. This is MySQL 4.0.20 and RH Advanced Server 2.1 After deleting the user on the master, the following message appears in the log but the slave status shows a healthy relationship. 40902 10:54:13 Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3307': Error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' errno: 1045 mysql show slave status \G *** 1. row ** Master_Host: hpim202-98.aus.hp.com Master_User: repl Master_Port: 3307 Connect_retry: 60 Master_Log_File: hpim202-98-bin.001 Read_Master_Log_Pos: 913879 Relay_Log_File: MAU023W-relay-bin.010 Relay_Log_Pos: 305 Relay_Master_Log_File: hpim202-98-bin.001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 913879 Relay_log_space: 301 1 row in set (0.00 sec) Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging
Back in 3.23.x we used to see MySQL hang on a corrupt table (typically caused by an improper shutdown). A kill -9 was needed to stop the process, upon restart we'd run a mysqlcheck (MyISAM tables) and repair the table. We didn't see anything obvious in the logs except the last query, which was attempting to use the corrupt table. Ronan Lucio wrote: Hello, I had a problem with our MySQL server-4.0.18 where it suddenly stoped working. Even a KILL command didn´t killed the mysqld process. I looked for any error message in the file /usr/local/mysql/hostname.err, but I couldn´t see any error message about such time. Does anyone knows what could make MySQL hangs? And where can I find error messages that could tell me what happend? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE string segment?
You can use the replace string function: update table set file_path=replace(file_path,'aFolder','aFolder/aChildFolder'); In each update aFolder will be replaced by the new path. John Mistler wrote: I have a column that holds hard disk file location info such as: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf etc. . . (many files located in the same folder) Can anyone suggest a single statement (or multiple) that would update every file location located in this same folder to another location, say: /Volumes/External HD/aFolder/aChildFolder/aFile.pdf /Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf etc . . . ? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql Architecture
Hi All, I would like to know some information about the MySql architecture. All the other databases have some info about their architecture in the documentation. But I havent found any documentation on the mysql architecture. Please refer me some websites or download liks where I can get some info on mysql architecture. I would appreciate, if you could send me some documentation on the mysql architecture. thanks and regards, Rajashik Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE string segment?
Wow! That works. Thanks a bunch! While we're at it, I have one more hang-up: Is there a (DISTINCT?) statement that will select the distinct folder path(s) of every entry in the table? For instance, if I have three files: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf /Volumes/External HD/aFolder/aChildFolder/aThirdFile.pdf the query would return: /Volumes/External HD/aFolder/ /Volumes/External HD/aFolder/aChildFolder/ Any ideas? Thanks again, John on 9/1/04 8:03 PM, Michael Kruckenberg at [EMAIL PROTECTED] wrote: You can use the replace string function: update table set file_path=replace(file_path,'aFolder','aFolder/aChildFolder'); In each update aFolder will be replaced by the new path. John Mistler wrote: I have a column that holds hard disk file location info such as: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf etc. . . (many files located in the same folder) Can anyone suggest a single statement (or multiple) that would update every file location located in this same folder to another location, say: /Volumes/External HD/aFolder/aChildFolder/aFile.pdf /Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf etc . . . ? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hosted application data design
Hi all, I'm designing a hosted application that many clients will access. I've got all sites running off one PHP code base quite nicely, and now I'm hoping to reduce the multiple databases down to just one shared DB. (Note: at this stage, all accounts are fictional, so there's no legacy data to worry about). So instead of each account have it's own database and autonomous tables, I'd like all accounts to share one database. This will: - make updates easy - allow each account to be group aware - allow a parent website easy access to all account data I've added client_id columns to all tables, and things are working fine, with the following exception... I'm worried about the primary keys (IDs) for each table growing to an unmanageable size pretty quick... 50 accounts * 500 forum posts = 25000 IDs pretty quickly. 50 sites * 5000 members = 250,000 members. I'm sure this isn't much of a technical/performance issue (with proper indexing), but it's just strikes me as a little strange that the first news post for an account could have an ID of '502846', rather than '1'. So, I was hoping I might be missing something in regards to primary keys... Can a primary key (or unique key) be established on two columns (eg primary key = client_id + post id)? Can an auto-increment column only increment in relation to the client id? Or am I expecting *way* too much out of MySQL and relational data design? --- Justin French http://indent.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to create a user with limited privileges
Hi all, I would like to know the mysql command to do the following: Create a user (who will access mysql database), with privilaeges so that, he can only retrieve the information in the data base (values in the tables), he should not be able to alter, update, delete any table values. Kindly help. thanks, -rajashik Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to create a user with limited privileges
Hi Rajashik, This is documented very well in the manual at http://dev.mysql.com/doc/mysql/en/GRANT.html#IDX1697 You would want the GRANT statement and only allow them SELECT privileges on the tables you wish them to view. eg. GRANT SELECT on test.* to user@somehost.% IDENTIFIED BY password; Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Rajashik Kar [mailto:[EMAIL PROTECTED] Sent: Thursday, 2 September 2004 2:21 PM To: '[EMAIL PROTECTED] ' Subject: how to create a user with limited privileges Hi all, I would like to know the mysql command to do the following: Create a user (who will access mysql database), with privilaeges so that, he can only retrieve the information in the data base (values in the tables), he should not be able to alter, update, delete any table values. Kindly help. thanks, -rajashik Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP MySQL (Intel Compiler) Problem
Dear Friends, I am trying to compile PHP with MySQL support in an HP DL380 server (Xeon Processors). The MySQL is build with Intel Compiler. I am getting the following problem / error : --- /usr/local/mysql/lib/libmysqlclient.a(libmysql.o)(.text+0x1ade): In function `store_param_time': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(libmysql.o)(.text+0x1ba2): In function `net_store_datetime': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(libmysql.o)(.text+0x1beb): In function `store_param_str': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(libmysql.o)(.text+0x2428): In function `mysql_stmt_bind_param': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(libmysql.o)(.text+0x2a01): In function `fetch_result_bin': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(libmysql.o)(.text+0x2a36): more undefined references to `_intel_fast_memcpy' follow /usr/local/mysql/lib/libmysqlclient.a(my_vsnprintf.o)(.text+0x22a): In function `my_vsnprintf.': : undefined reference to `_intel_fast_memset' /usr/local/mysql/lib/libmysqlclient.a(ctype.o)(.text+0x1b3): In function `cs_value': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype.o)(.text+0x218): In function `cs_value': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype.o)(.text+0x714): In function `cs_value': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype.o)(.text+0x740): In function `cs_value': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-simple.o)(.text+0xf2b): In function `my_long10_to_str_8bit': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-simple.o)(.text+0x1053): more undefined references to `_intel_fast_memcpy' follow /usr/local/mysql/lib/libmysqlclient.a(ctype-simple.o)(.text+0x1495): In function `my_fill_8bit': : undefined reference to `_intel_fast_memset' /usr/local/mysql/lib/libmysqlclient.a(ctype-bin.o)(.text+0x51b): In function `my_strnxfrm_bin': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-ucs2.o)(.text+0x1eb5): In function `my_strnxfrm_ucs2_bin': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-tis620.o)(.text+0x58): In function `my_strnncoll_tis620': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-tis620.o)(.text+0x6e): In function `my_strnncoll_tis620': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-tis620.o)(.text+0x12e): In function `my_strnncoll_tis620': : undefined reference to `_intel_fast_memcpy' /usr/local/mysql/lib/libmysqlclient.a(ctype-tis620.o)(.text+0x239): more undefined references to `_intel_fast_memcpy' follow collect2: ld returned 1 exit status make: *** [sapi/cli/php] Error 1 I have installed the Intel compiler and added the /opt/intel_cc_80/lib to the LD_LIBRARY_PATH environment variable. Please kindly help me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]