SET Conversion Questions
A bit long, but consulting docs DuBois aren't yielding solutions apparant to me... I'm converting a database where valuelists were controlled with a custom routine that read values from a text file. Selections of a specific valuelist were saved to a single field in the database, and each selection is delimited within the field with a \r. Hoping to use that same routine with MySQL, I imported this data into MySQL 3.23.46 and imported the valuelist selections as is into a VARCHAR field large enough to hold all selections. I'm fine storing selection values to the MySQL field. However, I now find myself in a pickle when it comes to searching that field for combinations of values. The previous database compared each value in my search string with each value as an independent contains. So, if I had red, blue, green, black as selection choices, and a field had red \r green \r black, (spaces are for readability only) I could search for red \r black and get a list of found records. MySQL doesn't like to do this. I can search with a single selection red and specify a 'contains' operator with my middleware (Lasso Pro 5) andget a list of records. However, I cannot include more than one option in my search string. It's obvious (and understandable) that MySQL searching with a literal red \r black single string, and finds no matches. Questions: 1 - is there a way with some SQL expression to search the existing field and \r delimited data with a string such as red \r black to reproduce the same search I had before? (I know this will not be as fast as SET). 2 - if I convert this column to a SET and define my choices, will MySQL automatically recognize the existing \r delimited values in the fields and properly convert the field data? 2a - do I first have to manually convert all \r instances to commas or something else? 2b - do I have to export the data, redefine the column as SET, then reimport it into this column? 3 - what happens to field data when the SET selections are redefined? The reason behind the previous setup was to allow easy changes to the selection list and isolate the definition of the list from the database. I know SET is faster, but these are really small databases. Thanks. -- Greg Willits -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: -fno-implicit-templates, -fno-exceptions,-fno-rtti problems
Hi! You may want to try out Intel's C++ compiler for Linux if you are using an ix86 CPU. We used it here at work and it resulted in code that ran more than twice as fast, though we have never tried it with MySQL. I wonder whether Intel's C++ compiler accept those flags. Are those flags included in some standards? My problem is that I don't want the mysqld crashed randomly when I do benchmarking on it. I compiled MySQL with the Intel compiler a year ago. There was no noticeable difference in speed compared to GCC. And greetings to a fellow UofA student (though I've now graduated). thank you. I am honored. -- Regards Peng -- Peng Zhao [EMAIL PROTECTED] http://www.cs.ualberta.ca/~pengzhao TEL (Lab): (780)492-3725 Lab: CSC251 Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql performance question
Hi Ken, Here are the values: kern.maxfiles: 24616 kern.maxfilesperproc: 22154 They are slightly less than 25716, is there any way to check if this upper limit becoming reached sometimes? Now there are question about innodb tables and 4.0.1. 1. What can be gained by switching to innodb inside 3.23 version, will the effect be significant? 2. Going to 4.0.1 with innodb - will it bring additional effect, what is more important - innodb or 4.0.1 or both? 3. How can I run both 3.23 and 4.0.1 on a single server, so I can play with 4.0.1 before transfering all databases to it? Starting both versions of daemons seems to be simple enough, but it seems the problem will start with the client part - how perl libraries (DBD) can deside to which mysql version they should connect? So can the mysql 3.23 clients libraries be used with 4.0.1, or what are the ways of solving it? Luck Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 15:14:39 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Your welcome Alexander! OK the value for table_cache is 8572 * 3 = 25716 file descriptors, how big is kern.maxfiles and kern.maxfilesproc? These need to be set above 26000 to allow all those tables to be opened! I know I have missed this sometime, or not reset them after a kernel compile. Also you don't need 4.0.1 for innodb tables, they work fine on 3.23.49 and LINUX_THREADS option from the ports package I think is only available for 3.23.xx versions. The only problems I have had with 4.0.1 have been self inflicted. There are some bugs in some of the newer features, 4.0.2 should be out soon. I have been kicking around the idea of using 4.0.1 in production as none of the bugs so far really affect my application. You can see the online change log for a list of changes and fixes since 4.0.1 at http://www.mysql.com/doc/N/e/News-4.0.2.html Good Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Ken Menzel [EMAIL PROTECTED] Cc: Simon Green [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 11:31 AM Subject: Re: Mysql performance question Thanks a lot for the advices. The value for table_cache is 8572, and I'm indeed accessing a lot of tables. So as I gather it the main effect should be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS and using innodb tables, true? Now what can you say about reliability issue, aren't these products currently in the development stage and can they be used for the production server? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 10:07:22 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green
MySql vs. WinCard
hi everybody, I have an OpenBSD 3.0 box running MySQL here, in my opinion setup correctly and everything runs smooth. now I have the challenge to migrate an m$Access based application called WinCard to play correctly with MySQL. the problem is, that it is not possible to enter new records from the (Access) GUI. as soon as I enter a record it pops up a message box (Record is deleted) - what it actually is. editing as well as deleting works fine. Oh, yes, I do use the latest MyODBC/Jet/MDAC drivers. probably there is some black magic in the ODBC settings, so here is what I have ACTIVATED: - return matching rows - recommended by MySQL - simulate ODBC 1.0 - recommended by MySQL - use compressed protocol - change bigint columns into int - safety neither the publisher nor the developers can/want to help me, because we only support microsoft products while OpenSource is no standard. I really would like to prove them wrong... so if there is somebody out there who knows anything about WinCard or has any other clue what I could do, please please help me... Claude Fankhauser - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems compiling PHP with mysql support as a apache DSO on a HPUX11.x box
Hi, mmh, maybe it's not a mysql-problem... Yes, I read through the numerous threads about this topic but I can't solve my problem After compiling php-4.1.1 with mysql support (mysql-3.23.47) as a DSO (./configure --enable-libgcc --with-mysql=/opt/mysql-3.23.47 --with-apxs=/opt/apache-1.3.23/bin/apxs) apache complains with the following error: bin/apachectl start /usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code) from /opt/mysql-3.23.47/lib/mysql/libmysqlclient.sl.10 Syntax error on line 236 of /opt/apache-1.3.23/conf/httpd.conf: Cannot load /opt/apache-1.3.23/libexec/libphp4.so into server: No such file or directory bin/apachectl start: httpd could not be started I compiled all the stuff with gcc version 2.95.2 19991024 (release) Here are the options I used to compile MySQL CXX=gcc CFLAGS=-fomit-frame-pointer -O6 -fpic CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti -O6 ./configure --prefix=/opt/mysql-3.23.47 --with-low-memory --with-innodb chatr Any idea?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to know MySQL instance(all daemons) really startup?
Hi, all mysql team As we know, use safe_mysqld or mysql.server we can startup Mysql instance. But how can i know when all the mysqld daemons really startup except use command ps? Thanks in advance. Robert Li Computer Associates - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Hoe to install client
Hello how to install mysql client only to connect server in windowsNt and Linux right now i'am working in server please mail to [EMAIL PROTECTED] _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to know MySQL instance(all daemons) really startup?
Robert, `mysqladmin ping` will print mysqld is alive if the server is running, or error message in any other case. Peter On Wed, 20 Feb 2002, Li, Robert wrote: Hi, all mysql team As we know, use safe_mysqld or mysql.server we can startup Mysql instance. But how can i know when all the mysqld daemons really startup except use command ps? Thanks in advance. Robert Li Computer Associates -- GPG ID D40940EC FPR 89CC E331 FFD0 3138 9CB2 FE0D 122E 9EC9 D409 40EC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table file extensions
Hi there, I am using MySQL v3.23.40 on Windows NT. When creating various MyISAM tables the files saved are of the form *.frm *.MYD and *.MYI. So far so good. When I copy these files over to a Linux box though some of the *.MYD and *.MYI extensions appear as lowercase *.myd and *.myi This causes MySQL on Linux to complain as it looking for the uppercase versions of the files. Does anyone know how I can get around this, or even better, why the case appears to change? Any help would be greatly appreciated. Neil Email: [EMAIL PROTECTED] [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fake interval data type
On Wed, Feb 20, 2002 at 11:38:31AM +0100, [EMAIL PROTECTED] wrote: sql,query The above is for antispam filter. I have a need for an interval data type. It would contain start and end of a certain time event. Since it is not possible to have two different events at the same place and at the same time, I want to ensure that there cannot exist two entries for which time interval would overlap with another entry's interval. I've done a simple routine in PHP which cheks if there is an overlaping entry in database and it inserts new entry only if it's 'safe'. Obviously, there is a problem if there are two entries, let's call them A and B, and A entry is made after B has done the checking but before B has made a new record. Any sugestions? Even sugestions for another free open source db would do. Sorry for my broken english. Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
install and upgrade problems with MySQL 3.23.49 via rpms
I maintain 3 machines running MySQL. Machine 1: Redhat 6.2 (Pentium 133, 40 megs, 1.5 gig) An attempt to upgrade the server from version 3.23.46 to 3.23.49 via rpm upgrade generated no errors or warnings but the resulting mysqld server crashed. Reinstallation of an earlier version was required to get an executable mysqld. Machine 2: Redhat 7.2 (Pentium 100, 40 megs RAM, 3 Gig HD -level5 RAID): An attempt to install version 3.23.49 rpm from scratch on a newly installed system generated no errors or warnings but the resulting mysqld crashed on startup (multiple tries). Machine 3: Redhat 7.2 (Pentium III 733, 768 ram, 20 gig) Installation of server 3.23.49 via rpm was flawless. Is this a glibc version issue? I noticed that the server RPM for version 3.23.49 is much bigger than earlier versions. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: **Backing Up A Database**
You may simply copy or move logrotate from /etc/cron.daily in /etc/cron.hourly. Logrotate will then be executed hourly, have a look into /etc/logrotate.conf, /etc/logrotate.d and determine which files are due to be rotated. But as you cannot set other delays but daily,weekly,monthly (see man logrotate), I suggest some tricking: add size 10k in the /etc/logrotate.d/-file you created, so that logrotate will find it due every day or when its size grew bigger than 10k (i.e. very soon, more often than daily...). For testing purposes, you may also just call /etc/cron.daily/logrotate by hand from the commandline. Felix -Ursprüngliche Nachricht- Von: Alexander Shaw [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 20. Februar 2002 01:15 An: MySQL List; Felix Richter Betreff: RE: **Backing Up A Database** Hi Felix (or anyone), I'm giving this a go but as a Linux newbie running Red hat 7.2 I have a question to ask Do I need to alter anything in Cron for it to run the logrotate script? What would I need to put into the hourly cron folder to make things happen more often to check they are working correctly? Alex - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: While executing a query, Got error 22275 from table handler
root writes: Description: When i execute the following query on my database: SELECT retaillastdone_.* FROM retaillastdone_ WHERE (retaillastdone_.source_=mercury AND retaillastdone_.timestamp_ BETWEEN 97864920 AND 978735599099) ORDER BY retaillastdone_.owner_,retaillastdone_.timestamp_ ASC ; I get the error ERROR 1030: Got error 22275 from table handler however, I am sure my tables arent corrupted as I ran myisamchk on it. The interesting thing is that the following query executes without any problems SELECT retaillastdone_.* FROM retaillastdone_ WHERE (retaillastdone_.source_=mercury AND retaillastdone_.timestamp_ BETWEEN 97864920 AND 978735599099) ORDER BY retaillastdone_.owner_,retaillastdone_.timestamp_ ASC limit 10; I used the email-support and got a reply from Sergei that its bug. Thus, Iam sending this testcase. Hi! Table has not arrived with your mail. It is possible that it got stripped away. Please upload your table to ftp://support.mysql.com:/pub/mysql/secret and let us know it's filename. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Installation Problem mysql-3.23.49
[EMAIL PROTECTED] writes: Description: When I run a ./configure --prefix=/usr/local/mysql --with-libwrap the configure completes but the make fails. Making all in sql make[2]: Entering directory `/usr/local/src/mysql-3.23.49/sql' Making all in share make[3]: Entering directory `/usr/local/src/mysql-3.23.49/sql/share' make[3]: Nothing to be done for `all'. make[3]: Leaving directory `/usr/local/src/mysql-3.23.49/sql/share' make[3]: Entering directory `/usr/local/src/mysql-3.23.49/sql' c++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\-DHAVE_CONFIG_H -I./../include -I./../regex-I. -I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -c mysqld.cc mysqld.cc: In function `void *handle_connections_sockets (void *)': /usr/include/tcpd.h:131: too many arguments to function `void sock_host ()' mysqld.cc:2395: at this point in file /usr/include/tcpd.h:69: too many arguments to function `int hosts_access ()' mysqld.cc:2396: at this point in file /usr/include/tcpd.h:124: too many arguments to function `char *eval_client ()' mysqld.cc:2401: at this point in file make[3]: *** [mysqld.o] Error 1 make[3]: Leaving directory `/usr/local/src/mysql-3.23.49/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/usr/local/src/mysql-3.23.49/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/src/mysql-3.23.49' make: *** [all-recursive-am] Error 2 This definitely requires that we take a look at it. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
new ppc64 Linux port! But a bug in status standard test :(
Description: A standard test fails. This is on PowerPC 64-bit Linux which is very new and we have only just finished with glibc as 64-bit. Thus, the syscalls, glibc and the compiler itself are all suspect -- so I don't expect anyone can really track this down except myself. I am looking for advice of where to start and maybe some speculation as to what might be failing. I do have the log output from the --debug if anyone cares for a peek. The status.test in mysql-test fails on this new platform with the following output: TEST USER SYSTEM ELAPSEDRESULT status [ fail ] Error at line 25: Result content mismatch Maximum memory usage: 196608 bytes (192k) Below are the diffs between actual and expected results: --- *** r/status.result Thu Dec 27 15:37:53 2001 --- r/status.reject Wed Feb 20 01:13:58 2002 *** *** 2,6 Table_locks_immediate 0 Table_locks_waited0 Variable_name Value ! Table_locks_immediate 3 ! Table_locks_waited1 --- 2,6 Table_locks_immediate 0 Table_locks_waited0 Variable_name Value ! Table_locks_immediate 0 ! Table_locks_waited0 --- How-To-Repeat: sudo ./mysql-test-run --debug --local status Fix: Submitter-Id: submitter ID Originator:Todd Inglett Organization: MySQL support: none Synopsis: status.test failure on PowerPC 64-bit Linux (new platform) Severity: non-critical Priority: low Category: mysql Class: Release: mysql-3.23.47 (Source distribution) Environment: RS/6000 44p/270 2-way 1GB ppc64 Linux (new) gcc 3.0.4 (new port) glibc 2.2.4 (very new port) System: Linux tt 2.4.13 #2 SMP Thu Feb 14 09:07:49 CST 2002 ppc64 unknown Architecture: ppc64 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /opt/ppc64/powerpc64-linux/bin/gcc /usr/bin/cc GCC: Reading specs from /opt/ppc64-20020218/lib/gcc-lib/powerpc64-linux/3.0.4/specs Configured with: /work/toolchain/gcc/gcc-20020218/configure --build=powerpc-linux --host=powerpc-linux --target=powerpc64-linux --enable-languages=c,c++ --disable-nls --prefix=/opt/ppc64-20020218 Thread model: single gcc version 3.0.4 20020218 (prerelease) Compilation info: CC='gcc' CFLAGS='-mminimal-toc' CXX='c++' CXXFLAGS='-mminimal-toc' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1524461 Dec 19 09:51 /lib/libc.so.6 -rw-r--r--1 root root 25765912 Dec 19 09:25 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 19 09:25 /usr/lib/libc.so -rw-r--r--1 root root 956946 Dec 20 12:41 /usr/lib/libc-client.a lrwxrwxrwx1 root root 20 Feb 7 08:36 /usr/lib/libc-client.so - libc-client.so.2000c -rwxr-xr-x1 root root 753040 Dec 20 12:41 /usr/lib/libc-client.so.2000c Configure command: ./configure --prefix=/usr/local64/mysql --with-named-curses-libs=/usr/local64/lib/libncurses.a --with-debug=full - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Copying tables problem
Hi all, I have a thorny problem and I hope this is the right place, I've been everywhere else I could think of. I have limited skills in this area. I have a site with content management run by mysql/php (just to be original), using two databases on the same machine. db1 is the working copy for users to edit the site, db2 is the live db that runs the site. I need to be able to copy db1.table_n to db2.table_n on demand as and when the data is checked for integrity. The following statements are what I think I know. Please correct me if I'm horrendously wrong here: REPLACE INTO db2.table_n SELECT * FROM db1.table_n won't work as this won't delete empty rows from db2 (if a row gets deleted from db1 the change won't be reflected in db2). DELETE * from db2.table_n, then INSERT INTO db2.table_n SELECT * FROM db1.table_n looks like a very bad thing, although this *is* doing what I want to happen I just can't believe that's the correct way of doing things. BACKUP TABLE db1.table_n TO '/path/to/db2/directory' wouldn't recreate indexes and is probably a stupid idea anyway. Replication is no good as the data is synch'ed automatically without user intervention. If I could trigger replication this would be an option for me. mysqldump --opt -t -u myuser -pmypass db1 | mysql --host=localhost db2 looks good to me, but is way above my skill level and frankly I'm scared to run it. So my question boils down to... am I misusing REPLACE when in fact it would do the job, or will the mysqldump command work? Thanks, and if I'm in the wrong place please steer me in the right direction, cheers, Jaz ** Jaz-Michael King Online Services Manager IPRO http://www.ipro.org ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT and UPDATE in one query?
Hi! I need to SELECT some rows using a lengthy WHERE-clause and then to UPDATE these rows just to set a flag. What is the best way to do this (performance-wise)? Being able to do that in one single query would be great because otherwise I had to use transactions to be sure to match _exactly_ the selected rows. Felix - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql performance question
Good Morning Alexander! Looks like you have tuned your system pretty well. They are slightly less than 25716, is there any way to check if this upper limit becoming reached sometimes? sysctl kern.openfiles will tell you how many are open now but there is no way to know if the upper limit is being reached except inside of mysql you can look at 'show status' at see if the ratio of 'opened tables' to 'open tables' is high. If you have a much larger number of opened tables something is not right. But I suspect all is well with your setup. Also note the 'open files' will tell you how many files mysql has open. Now there are question about innodb tables and 4.0.1. 1. What can be gained by switching to innodb inside 3.23 version, will the effect be significant? I think the biggest advantage of the innodb tables (besides transaction capable) is the row level locking, which mainly benefits you if you are getting many 'table_locks_waited'. Innodb is very good at parallel operations. You can set the transaction level to a low value and avoid the speed hit from transactions as well. Check out Heikki's web page at www.innodb.com and http://www.innodb.com/bench.html Great stuff! Also this is the easiest thing to try, if you have innodb table space enabled on your server just type 'ALTER TABLE mytable TYPE=INNODB' do this for all you table (with that many tables I would do it with a script) and if you don't like it 'TABLE mytable TYPE=MYISAM' to change it back. 2. Going to 4.0.1 with innodb - will it bring additional effect, what is more important - innodb or 4.0.1 or both? This depends on your application, for instance the 'query cache' feature of 4.0.1 is amazing. If there are many reads of data that may be repeated the query cache can signifigantly increase performance. I would check out the online manual for a full list of features in 4.0.1. The main two for me have been the query cache and the ability for an index to be used with ORDER BY DESC. 3. How can I run both 3.23 and 4.0.1 on a single server, so I can play with 4.0.1 before transfering all databases to it? Starting both versions of daemons seems to be simple enough, but it seems the problem will start with the client part - how perl libraries (DBD) can deside to which mysql version they should connect? So can the mysql 3.23 clients libraries be used with 4.0.1, or what are the ways of solving it? Yes you can run two versions (but not on windows). You must assign the second version to another port, which will affect you apps if you want to use the second version the app must be told which one to connect to. I have not done this, but I think there is a recipe somewhere, maybe you could repost this question or take a look in the manual at mysql-multi (or in the local/bin directory). Best of luck to you also, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Help
Hello I have a query that goes through and gets the data out of three tables without a problems. Now the task at hand is to compair that out put against three identiacal tables not displaying any duplicate date. Tables: a a.aocode , a.CaseNo , a.CaseName b. b.ao_code , b.initials c c.CaseNo, c.Name Then the new three tables have the same structure except they are named a_new, b_new, c_new. The query that I am using to pull the data out of the first table is as follows: Select distinct a.CaseNo, a.CaseName, b.initials, c.Name, from a left join b on a.aocode=b.ao_code left join c on a.CaseNo=c.CaseNo where b.initials ='XXX' For this example lets say that I have a where clause in there also that would get passed as a variable from another script. Now how in the world to I take run this query against both sets of tables and then combine the data and bring back a result? Thanks--- = Want to know when a band is playing or want to see what bands are playing at your favorite bar go to www.buffalogigs.com __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compilation Error
Hi there, I was following the instructions to compile the latest version of mysql for Mac OS X, when I ran into a few errors. I downloaded the source file as per the instructions from Apple (http://developer.apple.com/internet/macosx/osdb.html), and then followed the instructions on mysql (http://www.mysql.com/doc/Q/u/Quick_install.html). I had already added the group and user with an OS X utility called Net Info. Furthermore, the source file had already been unpacked on my desktop automatically, by a utility called Stuffit. I placed the source file in the directory I wanted to use. I went to that directory via command line and initiated the configuration. I added a few options based on the suggestions from Apple (http://developer.apple.com/internet/macosx/osdb.html). These were the results: [cpe0030654b7478:/library/mysql/mysql-3.23.49] dport% ./configure --prefix=/Library/mysql --with-unix-socket- path=/Library/mysql/run/mysql_socket --with-mysqld-user=mysql --with-comment --with-debug loading cache ./config.cache checking host system type... powerpc-apple-darwin5.2 checking target system type... powerpc-apple-darwin5.2 checking build system type... powerpc-apple-darwin5.2 checking for a BSD compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... no checking for working aclocal... missing checking for working autoconf... missing checking for working automake... missing checking for working autoheader... missing checking for working makeinfo... missing checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) no checking for gawk... no checking for mawk... no checking for nawk... no checking for awk... awk checking for gcc... no checking for cc... no configure: error: no acceptable cc found in $PATH I have very little experience compiling anything, but it looks like I'm missing something important on my system. Could it be that I simply don't have a compiler installed? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sum function question
Hi, i have a table like this Key Field-1 A string1 A string2 A string3 B string1 B string 2 I want to create a query to get the following result (only one line by key field): A - string1,string2,string3 B - string1,string2 I try to use the Sum function but it only works on numbers, ( the number of occurrences of key field, is unknown) Thanks in advance for any advice that you might have. Javier - Scanned for all known viruses by Messagelabs -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sum function question
- Original Message - From: Javier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 2:47 PM Subject: Sum function question Hi, i have a table like this Key Field-1 A string1 A string2 A string3 B string1 B string 2 I want to create a query to get the following result (only one line by key field): A - string1,string2,string3 B - string1,string2 I try to use the Sum function but it only works on numbers, ( the number of occurrences of key field, is unknown) Thanks in advance for any advice that you might have. Javier - Scanned for all known viruses by Messagelabs -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Help
On Wednesday 20 February 2002 09:20, Web boy wrote: You want to use UNION. If I remember correctly its a fairly new feature for MySQL, but its a standard SQL thing. From the manual: SELECT ... UNION [ALL] SELECT ... [UNION SELECT ...] UNION is implemented in MySQL 4.0.0. UNION is used to combine the result from many SELECT statements into one result set. The SELECT commands are normal select commands, but with the following restrictions: * Only the last SELECT command can have INTO OUTFILE. * Only the last SELECT command can have ORDER BY. If you don't use the keyword ALL for the UNION, all returned rows will be unique, like if you had done a DISTINCT for the total result set. If you specify ALL, then you will get all matching rows from all the used SELECT statements. Now how in the world to I take run this query against both sets of tables and then combine the data and bring back a result? Thanks--- = Want to know when a band is playing or want to see what bands are playing at your favorite bar go to www.buffalogigs.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C API example code.
Hi list, Where can I find an online tutorial / example on how to use the C API for mysql? TIA George (mysql, query, sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Inventory management system based on mysql
Hi all, I am trying to put together an inventory management system for my company an I was thinking to do it using mysql for the database, Red Hat Linux with apache for the server. I opted for a web like environment for this project because there are so many different computers that need to access the server. I have windows pc, linux pc, Macintosh. If I were to do somenting in C++ or Java or Python I would have to make a version of the interface for every system, sometimes even for every version of the same OS. So I decided that I would want to write this application using only web technologies: mysql, php, javascript, java; and all the users would access the server through a secure connection with their browser. this way the server can be accessed from any kind of computer. And everything will be on a LAN, so the speed is no problem. Does anyone know any reason why this would not be a good idea? Thanks, --Cip-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Inventory management system based on mysql
No reason, you have the right idea especially if you are dealing with multiple platforms -Original Message- From: Cip [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 8:54 AM To: [EMAIL PROTECTED] Subject: Inventory management system based on mysql Hi all, I am trying to put together an inventory management system for my company an I was thinking to do it using mysql for the database, Red Hat Linux with apache for the server. I opted for a web like environment for this project because there are so many different computers that need to access the server. I have windows pc, linux pc, Macintosh. If I were to do somenting in C++ or Java or Python I would have to make a version of the interface for every system, sometimes even for every version of the same OS. So I decided that I would want to write this application using only web technologies: mysql, php, javascript, java; and all the users would access the server through a secure connection with their browser. this way the server can be accessed from any kind of computer. And everything will be on a LAN, so the speed is no problem. Does anyone know any reason why this would not be a good idea? Thanks, --Cip-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems compiling PHP with mysql support as a apache DSO on aHPUX11.x box
Do you have also recompiled apache after compiling php ? Am Mit, 2002-02-20 um 10.04 schrieb Andre Konopka: Hi, mmh, maybe it's not a mysql-problem... Yes, I read through the numerous threads about this topic but I can't solve my problem After compiling php-4.1.1 with mysql support (mysql-3.23.47) as a DSO (./configure --enable-libgcc --with-mysql=/opt/mysql-3.23.47 --with-apxs=/opt/apache-1.3.23/bin/apxs) apache complains with the following error: bin/apachectl start /usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code) from /opt/mysql-3.23.47/lib/mysql/libmysqlclient.sl.10 Syntax error on line 236 of /opt/apache-1.3.23/conf/httpd.conf: Cannot load /opt/apache-1.3.23/libexec/libphp4.so into server: No such file or directory bin/apachectl start: httpd could not be started I compiled all the stuff with gcc version 2.95.2 19991024 (release) Here are the options I used to compile MySQL CXX=gcc CFLAGS=-fomit-frame-pointer -O6 -fpic CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti -O6 ./configure --prefix=/opt/mysql-3.23.47 --with-low-memory --with-innodb chatr Any idea?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connect question
Below is two scripts. The first one connects fine. The second gives the following error. Parse error: parse error in /usr/www/users/abc/connect.php on line 1 What is the problem with the second script? Thanks in advance - Craig #1 - Works --- HTML HEAD TITLE Our List of Jokes /TITLE HEAD BODY ?php // Connect to the database server $dbcnx = @mysql_connect(localhost, root, DWZ9bBXe); if (!$dbcnx) { echo( PUnable to connect to the . database server at this time./P ); exit(); } // Select the jokes database if (! @mysql_select_db(jokes) ) { echo( PUnable to locate the joke . database at this time./P ); exit(); } ? P Here are all the jokes in our database: /P BLOCKQUOTE ?php // Request the text of all the jokes $result = mysql_query(SELECT JokeText FROM jokes); if (!$result) { echo(PError performing query: . mysql_error() . /P); exit(); } // Display the text of each joke in a paragraph while ( $row = mysql_fetch_array($result) ) { echo(P . $row[JokeText] . /P); } ? /BLOCKQUOTE /BODY /HTML --- --- #2 Errors --- html head titleConnect to a MySQL Server/title /head body ?php $dbcnx = @mysql_connect(localhost, root, DwZ9bBXe); if ($dbcnx != FALSE) { print The connection to the server was made successfully.; } else { print The connection to the server failed.; } mysql_close($dbcnx); ? /body /html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connect question
The two passwords in the connections are different perhaps??? - Original Message - From: Craig Westerman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 3:24 PM Subject: Connect question Below is two scripts. The first one connects fine. The second gives the following error. Parse error: parse error in /usr/www/users/abc/connect.php on line 1 What is the problem with the second script? Thanks in advance - Craig #1 - Works --- HTML HEAD TITLE Our List of Jokes /TITLE HEAD BODY ?php // Connect to the database server $dbcnx = @mysql_connect(localhost, root, DWZ9bBXe); if (!$dbcnx) { echo( PUnable to connect to the . database server at this time./P ); exit(); } // Select the jokes database if (! @mysql_select_db(jokes) ) { echo( PUnable to locate the joke . database at this time./P ); exit(); } ? P Here are all the jokes in our database: /P BLOCKQUOTE ?php // Request the text of all the jokes $result = mysql_query(SELECT JokeText FROM jokes); if (!$result) { echo(PError performing query: . mysql_error() . /P); exit(); } // Display the text of each joke in a paragraph while ( $row = mysql_fetch_array($result) ) { echo(P . $row[JokeText] . /P); } ? /BLOCKQUOTE /BODY /HTML --- --- #2 Errors --- html head titleConnect to a MySQL Server/title /head body ?php $dbcnx = @mysql_connect(localhost, root, DwZ9bBXe); if ($dbcnx != FALSE) { print The connection to the server was made successfully.; } else { print The connection to the server failed.; } mysql_close($dbcnx); ? /body /html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connect question
Why bother with the line : if ($dbcnx != FALSE) When you could use : if (!$dbcnz) . ? - Original Message - From: Craig Westerman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 3:24 PM Subject: Connect question Below is two scripts. The first one connects fine. The second gives the following error. Parse error: parse error in /usr/www/users/abc/connect.php on line 1 What is the problem with the second script? Thanks in advance - Craig #1 - Works --- HTML HEAD TITLE Our List of Jokes /TITLE HEAD BODY ?php // Connect to the database server $dbcnx = @mysql_connect(localhost, root, DWZ9bBXe); if (!$dbcnx) { echo( PUnable to connect to the . database server at this time./P ); exit(); } // Select the jokes database if (! @mysql_select_db(jokes) ) { echo( PUnable to locate the joke . database at this time./P ); exit(); } ? P Here are all the jokes in our database: /P BLOCKQUOTE ?php // Request the text of all the jokes $result = mysql_query(SELECT JokeText FROM jokes); if (!$result) { echo(PError performing query: . mysql_error() . /P); exit(); } // Display the text of each joke in a paragraph while ( $row = mysql_fetch_array($result) ) { echo(P . $row[JokeText] . /P); } ? /BLOCKQUOTE /BODY /HTML --- --- #2 Errors --- html head titleConnect to a MySQL Server/title /head body ?php $dbcnx = @mysql_connect(localhost, root, DwZ9bBXe); if ($dbcnx != FALSE) { print The connection to the server was made successfully.; } else { print The connection to the server failed.; } mysql_close($dbcnx); ? /body /html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP! How to run multiple versions of MySQL on Windows
I am trying to run multiple versions of MySQL (not simultaneously) on my Windows box. One is NuSphere's 3.23.44 with Gemini tables and the other is Max 3.23.49. Do I only need to put the base files into two separate directories and then modify the my.ini to point to the version I want to use at that point in time? Aside from my.ini, are there any other external (outside the base directory) files that I need to worry about. Any other alternative solutions or am I missing the boat here. Thanks in advance, Craig sql,query,tables - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What is error code 1044 ?
Now I can not open my all databases. The error following: [root@linuxserver /root] # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with;or \g. Your MySQL connection id is 1 to server version: 3.22.32 Type 'help' for help. mysql use mysql; ERROR 1044: Access denied for user:'@localhost' to database 'mysql' mysql select * from user; ERROR 1046: No Database Selected mysql Please tell me what wrong my Mysql is? Thanks. -- ¹úÄÚÊÕ·ÑÓÊÏäË×îÇ¿£¬Ê×´ÎȨÍþÆÀ²â¼û·ÖÏþ http://news.21cn.com/domestic/21CN/2001-11-19/550568.html ÂòÆóÒµÓÊÏ䣬»ñÔùÓòÃû http://mail.21cn.com/corporation/010.html 21cn´øÄú×ß½øÄ¿±êÖ®Âã¬ÏíÊÜÉÌÂÃÀÖȤ http://travel.21cn.com/agency/aimtrip/aimtrip.html ÊÕ·ÑÓÊÏäÕæÄÜ´óÐÐÆäµÀ£¬¸øÎÒÒ»¸öÀíÓÉ http://mail.21cn.com/bijiao/index2-7.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What is error code 1044 ?
Exactly what the error message says. In other words you do not have the correct permissions to access the mysql database. - Original Message - From: bc203 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 3:31 PM Subject: What is error code 1044 ? Now I can not open my all databases. The error following: [root@linuxserver /root] # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with;or \g. Your MySQL connection id is 1 to server version: 3.22.32 Type 'help' for help. mysql use mysql; ERROR 1044: Access denied for user:'@localhost' to database 'mysql' mysql select * from user; ERROR 1046: No Database Selected mysql Please tell me what wrong my Mysql is? Thanks. -- ¹úÄÚÊÕ·ÑÓÊÏäË×îÇ¿£¬Ê×´ÎȨÍþÆÀ²â¼û·ÖÏþ http://news.21cn.com/domestic/21CN/2001-11-19/550568.html ÂòÆóÒµÓÊÏ䣬»ñÔùÓòÃû http://mail.21cn.com/corporation/010.html 21cn´øÄú×ß½øÄ¿±êÖ®Âã¬ÏíÊÜÉÌÂÃÀÖȤ http://travel.21cn.com/agency/aimtrip/aimtrip.html ÊÕ·ÑÓÊÏäÕæÄÜ´óÐÐÆäµÀ£¬¸øÎÒÒ»¸öÀíÓÉ http://mail.21cn.com/bijiao/index2-7.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Hoe to install client
shashidhara, Wednesday, February 20, 2002, 11:51:59 AM, you wrote: sn Hello sn how to install mysql client only to connect server sn in windowsNt and Linux sn right now i'am working in server sn please mail to sn [EMAIL PROTECTED] You can use a standart client program for Linux MySQL-client-VERSION.i386.rpm, see at: http://www.mysql.com/doc/L/i/Linux-RPM.html You can find info about MyCC at: http://www.mysql.com/downloads/gui-mycc.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C API example code.
George, Wednesday, February 20, 2002, 4:51:32 PM, you wrote: GL Hi list, GL Where can I find an online tutorial / example on how to use the C API GL for mysql? Look in MySQL online documentation about C API: http://www.mysql.com/doc/C/C/C.html GL George GL (mysql, query, sql) -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 2013: Lost connection to MySQL server during query
Tim, Tuesday, February 19, 2002, 9:57:07 PM, you wrote: TW Hopefully someone out there can help me as I haven't been able to find any TW documentation on this that applies. TW I am running MySQL version 3.22.32 on a FreeBSD v4.4 machine. I am getting TW Error 2013: Lost connection to MySQL server during query when I run the TW following query: TW select distinct Product.MFG_ID, TW MFG.Name TW fromProduct, TW Cat_link, TW MFG TW where MFG.ID=Product.MFG_ID TW and Product.ID=Cat_link.PROD_ID TW and Cat_link.CAT_ID=3 TW and Product.Status=A TW order by MFG.Name; TW Does anyone have any ideas? Thanks! Connection lost error occurs when communication bug or bug in the client take place. Look at: http://www.mysql.com/doc/G/o/Gone_away.html http://www.mysql.com/doc/P/a/Packet_too_large.html TW TW -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
using MySQL
Bob, Wednesday, February 20, 2002, 2:01:12 AM, you wrote: BR I seem to have forgotten how to get into MySQL and work. BR (It's been a while but I'm starting up again. I get these BR errors: bob@sfpetard:~ mysqladmin create library BR mysqladmin: CREATE DATABASE failed; error: 'Access denied BR for user: '@localhost' to database 'library'' BR and: bob@sfpetard:~ mysql BR Welcome to the MySQL monitor. Commands end with ; or \g. BR Your MySQL connection id is 13 to server version: BR 3.23.44-Max-log BR Type 'help;' or '\h' for help. Type '\c' to clear the BR buffer. BR mysql show databases BR - ; BR +---+ BR | Database | BR +---+ BR | addresses | BR | inventory | BR | libes | BR | mysql | BR | sample| BR | test | BR +---+ BR 6 rows in set (0.01 sec) BR mysql use libes BR ERROR 1044: Access denied for user: '@localhost' to BR database 'libes' BR I am doing this on SuSE 7.3. I know there is a user mysql BR already set up but I don't knwo the password. You should set a permission for the user. The recomended way is to use GRANT statement. Look at: http://www.mysql.com/doc/G/R/GRANT.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 16 char limit on Grant username
Why? Victoria Reznichenko wrote: Vincent, Sunday, February 17, 2002, 9:49:55 PM, you wrote: VS Hello, VS according to the docs: VSFor the moment, GRANT only supports host, table, database, and column names up to 60 VS characters long. VS A user name can be up to 16 characters. VS Is there a way to increase this limitation? VS I want 32-64 character usernames. VS Thanks It's impossible for tables 'user', 'db' in mysql db. -- Vincent Stoessel [EMAIL PROTECTED] Linux and Java Application Developer (301) 362-1750 AIM, MSN: xaymaca2020 , Yahoo Messenger: vks_jamaica - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: C API example code.
Thanks, I will have a look at the official site. Is anybody aware of a non-oficial site that contains an example with a real db as backend. (With real db I mean tables with a few thousand+ records per table and advanced queries with multiple joins) and then how one can optimise the c code to run optimaly with several clients connecting via the c client code to the server. Egor Egorov [EMAIL PROTECTED] 02/20 5:50 PM George, Wednesday, February 20, 2002, 4:51:32 PM, you wrote: GL Hi list, GL Where can I find an online tutorial / example on how to use the C API GL for mysql? Look in MySQL online documentation about C API: http://www.mysql.com/doc/C/C/C.html GL George GL (mysql, query, sql) -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please, need help urgently
Had a similar problem a few years back. System worked fine for a number of months then started crashing whenever I recompiled the kernel. Turned out to be a defective secondary cache (yes it was a Pentium and yes it was a number of years ago). Showed up only under heavy cpu load. Try stressing the box. Good luck On Wed, 20 Feb 2002, Sundara Pandian wrote: Date: Wed, 20 Feb 2002 10:33:02 +0530 From: Sundara Pandian [EMAIL PROTECTED] To: SplitSlim [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Please, need help urgently Hi, It would be time consuming to understand why the server crashed and then repair the server, as u say it is a mission crtical one , the quickest way to get it up and running would be to back up the database (copy the ur database directory completely under the data directory) reinstall MySQL cleanly and get the DB back in action again.Also make sure u've got a 'release' build of MySQL, I got the following info from a site hope that helps.. Signal 11 Description : Signal 11, or officially know as segmentation fault, means that the program accessed a memory location that was not assigned. That's usually a bug in the program. So if you're writing your own program, that's the most likely cause. However, this FAQ will concentrate on the possibilities besides that. The cause can be software or (most likely) hardware for a Signal 11 crash. Read on to find out more. (*) Of course nothing is Linux specific. If your hardware is flaky, Linux, Windows 3.1, FreeBSD, Windows NT and NextStep will all crash. If you are not reading this at http://www.BitWizard.nl/sig11/, that's where you can find the most recent version. cheers, Sundara Pandian, Megasoft Ltd, India. - Original Message - From: SplitSlim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 10:14 AM Subject: Please, need help urgently I need some help, this is a mission critical server and it has CRASHED! Details:FreeBSD on INTEL, MySQL v3.23.36 (FreeBSD Port) The server had to be rebooted, and when it came back up, my MySQL server died. It's been working flawlessly for months (5 or 6), but all the sudden mysqld will not start. Here's whats in my log... __ 020219 22:52:00 mysqld restarted mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 020219 22:52:00 mysqld restarted mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 020219 22:52:01 mysqld restarted mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 020219 22:52:01 mysqld restarted mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. _--_ This keeps repeating and repeating. Since it's a port I don't have debugging compiled into MySQL, so I can't stack trace. I've used isamchk, myisamchk, and mysqld --flush to try to clean everything up, but no luck. Please help, I need to get this thing back up tonight! If you need anymore info, I'll be on this group all night until I get this thing back up. Thanks in advance! NR - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
print out the datastructure of tables
Hello, I am new with MySQL and have it running on Windws 2000 server and programming with VC++ 6.0 Now converting from SQL 7.0 to MySQL I wonder if there is a way to print out the data structure of the table in MySQL? Regards Theo van Stratum Albanet Ltd (Technical Manager, Senior Programmer) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP! How to run multiple versions of MySQL on Windows
Scalper, Wednesday, February 20, 2002, 5:28:41 PM, you wrote: S I am trying to run multiple versions of MySQL (not simultaneously) on my S Windows box. S One is NuSphere's 3.23.44 with Gemini tables and the other is Max 3.23.49. S Do I only need to put the base files into two separate directories and then S modify the my.ini to point to the version I want to use at that point in time? S Aside from my.ini, are there any other external (outside the base S directory) files that I need to worry about. S Any other alternative solutions or am I missing the boat here. Don't forget to read the manual: http://www.mysql.com/doc/I/n/Installing_many_servers.html http://www.mysql.com/doc/M/u/Multiple_servers.html http://www.mysql.com/doc/m/y/mysqld_multi.html S Thanks in advance, S Craig -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What is error code 1044 ?
bc203, Wednesday, February 20, 2002, 5:31:03 PM, you wrote: b Now I can not open my all databases. The error following: b [root@linuxserver /root] # mysql -u root -p b Enter password: b Welcome to the MySQL monitor. Commands end with;or \g. b Your MySQL connection id is 1 to server version: 3.22.32 b Type 'help' for help. b mysql use mysql; b ERROR 1044: Access denied for user:'@localhost' to database 'mysql' b mysql select * from user; b ERROR 1046: No Database Selected b mysql b Please tell me what wrong my Mysql is? Thanks. You have no permissions on mysql database. Could anybody set other (reset) permitions? At the worst this will help you: http://www.mysql.com/doc/R/e/Resetting_permissions.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: column1 like column2%
mysql,query Hi , is there any way of performing something like the below statement? select * from table1,table2 where table1.column1 like table2.column2 thanks in advance Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Extracting Blob fields in C API
Hi, I am trying to extract blob data from a query using the C API functions (actually I am using Delphi). I understand the structure of the result set and need to know the length of the blob field to extract it. As far as I can tell the only way to find the length of the fields is by using the mysql_fetch_lengths function. However I can't use this function reliably as I need random access to my results. ie - I use mysql_store_result followed by mysql_data_seek and mysql_row_seek to move around the results. mysql_fetch_lengths only seems to work if the table is accessed sequentially using the mysql_fetch_field function first. Am I missing something here? Surely there must be a simple answer. At the moment the only solution I have is to scan the whole results set and cache the lengths before allowing random access. This is terrible waste of resource as the client software must already have this information stored. I hope someone can help quickly before I tear all my hair out. Mike Brickman Radical Solutions Limited - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with connecting JSP and mysql
Hello sir, I am finding Problem with connecting JSP and mysql, it gives no error as well as exception,no output is coming out. please help me in this problem anticipating quick response Thanking you! subodh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: basic query question
to create a second instance of the same table and this will probably show the relationship twice SELECT p1.lname, p1.fname, p2.lname, p2.fname FROM members AS 'p1' LEFT JOIN members AS 'p2' ON p1.engagedto = p2.id not sure if you can do the LEFT JOIN table AS 'table2' or not, can't get to mysql.com to check the documentation :( it will be something similar however your table being in this structure of course +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 18| | 273 | Simmons | Maria | 78| | 221 | Papa | Sharla | 123 | | 18 | Biehl | Ruth| 131 | | 302 | Vance | Alicia | 204 | | 123 | Goettl| Christopher | 221 | | 78 | Ellsworth | Morgan | 273 | | 204 | Millet| David | 302 | +-+---+-+---+ -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 2:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: Roger Karnouk [EMAIL PROTECTED] To: Dean Householder [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble
Getting Data From One Table Based On Another - Agh!
Looking through the MySQL stuff online, I still can't come up with a statement to pull data from one table based on data from another. Table1 Contains: orderid Table2 Contains: orderid otherfield1 otherfield2 I want to get the data from table 2 based on a matching orderid first pulled from table1. Anyone have any ideas? Either this isn't able to be done, or I am (and I'm not) not advanced enough to understand the SQL statement needed. So far, I have tried a number of them that haven't worked. Any ideas? Thanks! -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Master/Slave reversal in Replication
Hello I'm new to MySQL, Unix and Replication. I would like to know if in a scenario where I have one master and several slaves I can convert one of the slaves to master in the event my master goes down. Once I can bring the master back up, how to I reverse the operation? Does any one know how to make the switch or where I can read up on it? I'm using FreeBSD with MySQL v4.0.1 alpha. Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Getting Data From One Table Based On Another - Agh!
SELECT Table1.orderid, Table2.otherfield1, Table2.otherfield2 FROM Table1, Table2 WHERE Table1.orderid = Table2.orderid; --Greg Johnson -Original Message- From: SpyProductions Support Team [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 12:31 PM To: [EMAIL PROTECTED] Subject: Getting Data From One Table Based On Another - Agh! Looking through the MySQL stuff online, I still can't come up with a statement to pull data from one table based on data from another. Table1 Contains: orderid Table2 Contains: orderid otherfield1 otherfield2 I want to get the data from table 2 based on a matching orderid first pulled from table1. Anyone have any ideas? Either this isn't able to be done, or I am (and I'm not) not advanced enough to understand the SQL statement needed. So far, I have tried a number of them that haven't worked. Any ideas? Thanks! -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error in test rpl000015
Description: problem with test rpl15, FreeBSD 4.0, gcc version 2.95.2 19991024 How-To-Repeat: Fix: Submitter-Id: [EMAIL PROTECTED] Originator:Gestore minimo Organization: MySQL support: [none ] Synopsis: Don't know how much this affect the general reliability Severity: Priority: Category: mysql Class: Release: mysql-3.23.48 (Source distribution) Environment: System: FreeBSD servizi.interazioni.it 4.0-RELEASE FreeBSD 4.0-RELEASE #0: Fri Jul 7 19:41:11 GMT 2000 [EMAIL PROTECTED]:/usr/src/sys/compile/WSSP40 i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1128450 Mar 20 2000 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Apr 2 2001 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 531444 Mar 20 2000 /usr/lib/libc.so.4 Configure command: ./configure --prefix=/mysql_root --with-debug Perl: This is perl, version 5.005_03 built for i386-freebsd Error listing follows... Installing Test Databases Removing Stale Files Installing Master Databases Installing Slave Databases Starting MySQL daemon Loading Standard Test Databases Starting Tests TEST USER SYSTEM ELAPSEDRESULT rpl15 [ fail ] /disco_db/mysql-3.23.48_root/bin/mysqltest: At line 35: Result length mismatch real 0.48 user 0.18 sys 0.20 Below are the diffs between actual and expected results: --- *** r/rpl15.result Wed Feb 20 20:56:02 2002 --- r/rpl15.reject Wed Feb 20 21:00:47 2002 *** *** 1,7 File PositionBinlog_do_dbBinlog_ignore_db master-bin.00173 Master_Host Master_User Master_Port Connect_retry Log_FilePos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter ! 0 0 0 No 0 0 Master_Host Master_User Master_Port Connect_retry Log_FilePos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter 127.0.0.1 test999860 4 No 0 0 Master_Host Master_User Master_Port Connect_retry Log_FilePos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter --- 1,7 File PositionBinlog_do_dbBinlog_ignore_db master-bin.00173 Master_Host Master_User Master_Port Connect_retry Log_FilePos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter ! 127.0.0.1 root93061 4 No 0 0 Master_Host Master_User Master_Port Connect_retry Log_FilePos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter 127.0.0.1 test999860 4 No 0 0 Master_Host Master_User Master_Port Connect_retry Log_FilePos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter --- Please follow the instructions outlined at http://www.mysql.com/doc/R/e/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Aborting. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL HELP
Richard Shade Systems Administrator (936)559-2280 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 11:39 AM To: Shade, Richard Subject: Re: MYSQL HELP Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Richard Shade Systems Administrator (936)559-2280 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential, proprietary, and/or privileged material. Any review, retransmission, dissemination, or other use of, or taking of, any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all computers. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential, proprietary, and/or privileged material. Any review, retransmission, dissemination, or other use of, or taking of, any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all computers. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Good, Free Mac GUI to mySQL?
On Mon, 18 Feb 2002, David Steinbrunner wrote: [EMAIL PROTECTED] wrote: Good, Free Mac GUI to mySQL? The only free GUI client for mySQL for the Mac that I know of would be CocoaSQL: http://homepage.mac.com/mxcantor/ which is only for Mac OS X. GNUe Forms and Designer have recently started working on Mac OS X. They will not do administrative tasks but can be used to access data from quite a few different databases. www.gnuenterprise.org Take Care, james -- James Thompson138 Cardwell Hall Manhattan, Ks 66506785-532-0561 Kansas State University Department of Mathematics -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
syntax error at line 1 in my_print_defaults
Description: When I try to run, I get the following output: bin/safe_mysqld --user=mysql 410 # ./bin/my_print_defaults: syntax error at line 1: `(' unexpected How-To-Repeat: I used gtar to untar the binary Solaris 8 version. I get the error eny time I try and start mysql. Fix: Submitter-Id: Gary Lon Brown Originator:Super-User Organization: Hartford Life MySQL support: none Synopsis: syntax error at line 1 in my_print_defaults Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.39 (Official MySQL binary) Environment: System: SunOS godzilla 5.8 Generic_108528-12 sun4u sparc SUNW,Sun-Blade-100 Architecture: sun4 Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.5.1/egcs-2.91.60/specs gcc version egcs-2.91.60 19981201 (egcs-1.1.1 release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1763908 Dec 7 18:06 /lib/libc.a lrwxrwxrwx 1 root root 11 Jun 24 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136748 Dec 7 18:06 /lib/libc.so.1 -rw-r--r-- 1 root bin 1763908 Dec 7 18:06 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Jun 24 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136748 Dec 7 18:06 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --disable-shared Perl: This is perl, version 5.005_03 built for sun4-solaris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL HELP
Richard, Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Sub-selects not (yet) permitted by MySQL (RTFM: 1.7.4.1 Sub-SELECTs) Looks like a job for two SELECTs and a temporary table... Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
insert select in ONE statement?
Hi, I wonder if anyone can offer me some advice with this one: I have a table called 'iq_data' holding just a primary key and a field called 'iq_data'. +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | iqid | int(11)| | PRI | NULL| auto_increment | | iqdata | mediumtext | | | NULL|| +++--+-+-++ 'iq_data' contains the contents of a plain text file in the following format: .010 .1083649E+03 .2186916E+02 .0013470 .1993729E+03 .2738670E+02 == + approx. 100-1000 more lines. After I insert data into the table, I need to get the iqid that was generated. One solution is to do something like: SELECT iqid from iq_data where iqdata=the contents of the file; // ^ this will give me the correct answer, but it may be a // little inefficient as there may be thousands of records, and the // search string is very large or SELECT iqid from iq_id order by iqid (and get the last iqid generated) // ^ querying like this immediately after inserting data to the table, // but assumes that another record has not yet been added A problem will arise if say, data is inserted by user 1, and then by user 2, and then the iqid for user1 is requested, but the iqid for user2 will be returned. So... Is there any way I can e.g. insert the data into the table and return the value of the PK that was generated in one statement? If not, of the 2 solutions above what is more efficient? Is there are more elegant solution? Thank you very much for your time; hope someone can help ;-) - Best regards, Lee Reilly /My SQL query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: insert select in ONE statement?
use LAST_INSERT_ID() to get the last inserted id http://www.mysql.com/doc/G/e/Getting_unique_ID.html and 'The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). ' Regards, Dan -Original Message- From: Lee P Reilly [mailto:[EMAIL PROTECTED]] Sent: Thursday, 21 February 2002 8:50 a.m. To: MySQL Subject: insert select in ONE statement? Hi, I wonder if anyone can offer me some advice with this one: I have a table called 'iq_data' holding just a primary key and a field called 'iq_data'. +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | iqid | int(11)| | PRI | NULL| auto_increment | | iqdata | mediumtext | | | NULL|| +++--+-+-++ 'iq_data' contains the contents of a plain text file in the following format: .010 .1083649E+03 .2186916E+02 .0013470 .1993729E+03 .2738670E+02 == + approx. 100-1000 more lines. After I insert data into the table, I need to get the iqid that was generated. One solution is to do something like: SELECT iqid from iq_data where iqdata=the contents of the file; // ^ this will give me the correct answer, but it may be a // little inefficient as there may be thousands of records, and the // search string is very large or SELECT iqid from iq_id order by iqid (and get the last iqid generated) // ^ querying like this immediately after inserting data to the table, // but assumes that another record has not yet been added A problem will arise if say, data is inserted by user 1, and then by user 2, and then the iqid for user1 is requested, but the iqid for user2 will be returned. So... Is there any way I can e.g. insert the data into the table and return the value of the PK that was generated in one statement? If not, of the 2 solutions above what is more efficient? Is there are more elegant solution? Thank you very much for your time; hope someone can help ;-) - Best regards, Lee Reilly /My SQL query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: insert select in ONE statement?
SELECT LAST_INSERT_ID() will return the last auto_increment key written. This value is connection-specific, so that inserts performed on other connections will not affect it (in other words, you'll get the last key generated by user 1, regardless of what user 2 does in the meantime). --Greg Johnson -Original Message- From: Lee P Reilly [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 2:50 PM To: MySQL Subject: insert select in ONE statement? Hi, I wonder if anyone can offer me some advice with this one: I have a table called 'iq_data' holding just a primary key and a field called 'iq_data'. +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | iqid | int(11)| | PRI | NULL| auto_increment | | iqdata | mediumtext | | | NULL|| +++--+-+-++ 'iq_data' contains the contents of a plain text file in the following format: .010 .1083649E+03 .2186916E+02 .0013470 .1993729E+03 .2738670E+02 == + approx. 100-1000 more lines. After I insert data into the table, I need to get the iqid that was generated. One solution is to do something like: SELECT iqid from iq_data where iqdata=the contents of the file; // ^ this will give me the correct answer, but it may be a // little inefficient as there may be thousands of records, and the // search string is very large or SELECT iqid from iq_id order by iqid (and get the last iqid generated) // ^ querying like this immediately after inserting data to the table, // but assumes that another record has not yet been added A problem will arise if say, data is inserted by user 1, and then by user 2, and then the iqid for user1 is requested, but the iqid for user2 will be returned. So... Is there any way I can e.g. insert the data into the table and return the value of the PK that was generated in one statement? If not, of the 2 solutions above what is more efficient? Is there are more elegant solution? Thank you very much for your time; hope someone can help ;-) - Best regards, Lee Reilly /My SQL query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT and UPDATE in one query?
Hi, I think you can try to use REPLACE INTO table (a,b,c,d) SELECT e,f,g,h FROM table2 WHERE field=what_you_want. Best Regards, Jocelyn Fournier Presence-PC - Original Message - From: Felix Richter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 3:01 PM Subject: SELECT and UPDATE in one query? Hi! I need to SELECT some rows using a lengthy WHERE-clause and then to UPDATE these rows just to set a flag. What is the best way to do this (performance-wise)? Being able to do that in one single query would be great because otherwise I had to use transactions to be sure to match _exactly_ the selected rows. Felix - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL HELP
How would you use a temporary table? Is that the most efficient way? David -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 11:03 AM To: Shade, Richard; [EMAIL PROTECTED] Subject: Re: MYSQL HELP Richard, Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Sub-selects not (yet) permitted by MySQL (RTFM: 1.7.4.1 Sub-SELECTs) Looks like a job for two SELECTs and a temporary table... Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Problem
Hello, I am having a difficult time figuring out how to get a certain specific result from the database . Example tables: table1 ID|name --- 1 |15 2 |25 3 |50 table2 ID|code --- 1 |1 2 |2 3 |2 My query select * from table1,table2 WHERE table1.ID = table2.code The current query returns all records in table2 that match the ID in table1 but what I want it to do is return only one record from table2 for each match in table1 Query results 1 2 2 Desired results 1 2 Thanks for any help. Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Problem
From: [EMAIL PROTECTED] My query select * from table1,table2 WHERE table1.ID = table2.code Query results 1 2 2 Desired results 1 2 Try: SELECT * FROM table1,table2 WHERE table1.ID = table2.code GROUP BY table2.code You'll find this someplace in: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html But it's a rather thick document. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrade
MySQL(mysql-server-3.23.36-1) is working find now, but I need upgrade to MySQL-4.0.0-2.i386.rpm and this error appears. [root@switch newrad]# rpm -Uvh MySQL-4.0.0-2.i386.rpm error: failed dependencies : MySQL-server conflicts with mysql-server-3.23.36-1 libmysqlclient.so.10 is needed by mysql-server-3.23.36-1 [root@switch newrad]# Please helpme. Thanks Johann S. Giese V. CTO LatLink.Net [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrade
MySQL(mysql-server-3.23.36-1) is working find now, but I need upgrade to MySQL-4.0.0-2.i386.rpm and this error appears. [root@switch newrad]# rpm -Uvh MySQL-4.0.0-2.i386.rpm error: failed dependencies : MySQL-server conflicts with mysql-server-3.23.36-1 libmysqlclient.so.10 is needed by mysql-server-3.23.36-1 [root@switch newrad]# Please helpme. Thanks Johann S. Giese V. CTO LatLink.Net [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question on maximum record input frequency and table lengths.
On Wednesday 20 February 2002 02:06, Anvar Hussain K.M. wrote: Hello, I don't feel the the table sizet will be a problem. But how would the database function with such a huge number of queries per second. If I read right, you will be taking a maximum (presently) of 42 channals each taking taking data at the rate of 360/sec. That would be 42x360 = 15120 queries to the database per second. Can MySql withstand this? My concern wouldn't be so much can MySQL handle 15120 queries per second but can your disk drives handle it... Even with some pretty high-end hardware thats a tall order. Consider, a top line SCSI drive is 10k RPM, that means that at the very best the heads get over each sector on the disk 10k times per second, so that is the absolute theoretical maximum rate at which reads and writes can be issued to the drive (since drives have to spin around once per I/O operation no matter what size it is). Given that many operations span multiple cylinders that is just the optimum rate of the hardware, and doesn't account for seek time delays, which are orders of magnitude higher than that. Of course the controller and the OS, and MySQL can all help by coagulating various operations into lesser numbers of larger operations, but fundamentally the limit is still there. Disk stripping and to a somewhat lesser extent, other forms of disk load balancing will also alleviate the bottleneck, but you are still talking about a very high level of write frequency. I'd be loathe to claim that any particular system would handle that load without testing that hypothesis. Its certainly doable, but might take a bit more hardware than one would think at first blush. One solution is to insert multiple rows in a single query. Possibly inserting only after a monitor has taken 100 samples (an optimal value can be found by trial). Now the number of queries would be reduced to 3.6 x 42 = 151. Something manageable for the database engine. It would do good to keep number of indeces to the minimum required and simple. Anvar. At 07:29 PM 19/02/2002 -0800, you wrote: On the largest plants we would expect to monitor, we would have about 42 separate analog channels (tables) taking data at the 360 or more times per second. Each of these tables would have two fields - a record number (index) autoincrementing, and a numeric (probably a smallint in most cases) which are change, and a couple of static identifier fields, so each record would be on the order of 100 bytes or less. Michael Mitchell, Principal, DGRI Systems - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fake interval data type
On Wednesday 20 February 2002 05:51, Sasa Babic wrote: On Wed, Feb 20, 2002 at 11:38:31AM +0100, [EMAIL PROTECTED] wrote: sql,query The above is for antispam filter. I have a need for an interval data type. It would contain start and end of a certain time event. Since it is not possible to have two different events at the same place and at the same time, I want to ensure that there cannot exist two entries for which time interval would overlap with another entry's interval. I've done a simple routine in PHP which cheks if there is an overlaping entry in database and it inserts new entry only if it's 'safe'. Obviously, there is a problem if there are two entries, let's call them A and B, and A entry is made after B has done the checking but before B has made a new record. Any sugestions? Even sugestions for another free open source db would do. Sorry for my broken english. Thanks. Transactions would be the answer. Use an innodb table and wrap your check/insert code in a transaction. The select which does the check will lock the table in such a way that any other attempt to insert within the range you tested for should end up being locked. Its kind of a subtle point, but innodb's locking not only locks rows, it locks the ABSENCE of rows. If I understand correctly this means that something like: BEGIN WORK SELECT COUNT(*) FROM table WHERE starttime? AND endtime? (check the count) INSERT INTO table (if count is zero) COMMIT SHOULD do the trick. The transaction should block anything else that tries to do an insert into the same range before you do your commit. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best solution for a very busy MySQL server
Hello, Have a look at : http://www.compagnie.com/stef/solution1.jpg http://www.compagnie.com/stef/solution2.jpg I'm searching for a solution to change a high traffic mysql server with a distributed system. 1)Is the solution 1 is good for a writing + reading access ? Are there problems with locking processus ? 2)Is the solution 2 is good for : -direct writing to mysql server (insert, update, replace ...) -using MyODBC to read tables (select) 3) I found another MySQL cluster solution http://prometheus.zerodivide.net/mysql_cluster/about/ This system uses MySQL replication. Thanks for advices. Stéphane HENRY - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please, need help urgently
Hi, On Wed, 2002-02-20 at 14:44, SplitSlim wrote: I need some help, this is a mission critical server and it has CRASHED! Details:FreeBSD on INTEL, MySQL v3.23.36 (FreeBSD Port) 020219 22:52:01 mysqld restarted mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. _--_ This keeps repeating and repeating. Since it's a port I don't have debugging compiled into MySQL, so I can't stack trace. I've used isamchk, myisamchk, and mysqld --flush to try to clean everything up, but no luck. Please help, I need to get this thing back up tonight! If you need anymore info, I'll be on this group all night until I get this thing back up. You provide very little information... lots missing: which FreeBSD version, did you build MySQL Server yourself and if so with which options, etc It's like phoning your garage and saying my car doesn't start, what's wrong? - they can make some basic suggestions such as put some petrol in it and try again, but otherwise they too will need more info! A reinstall of MySQL Server might do the trick, IF you've got a corrupted binary through the crash. But as someone else noted, it is also possible you've got a hardware problem. Did you try to run other stuff, and what was the result there? Something else I have to mention because it is terribly important: MySQL support (http://www.mysql.com/support/). Like you said, it all ran fine for months, but it's a mission critical system and you can't have long (if any) downtime MySQL support is a type of insurance; most of the time you won't need it, but when you do, it's URGENT. MySQL AB's support people have ample experience with this kind of stuff. Depending on what type of support contract you'd have, you'd work together with them to track down the problem, clean up the system, and bring it back online. Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CHECK constraints
Hello, I searched the manual but it seems like MySQL does not support ANSI SQL CHECK constraints. These are often used to check that rows inserted into tables meet specified criteria. For example table T created as follows CREATE TABLE T ( X INT, CHECK (X = 10) ); has a check constraint so that if the user tried to insert a row with the following statement: INSERT INTO T VALUES (11); then the DBMS reports an error but if on the other hand the user enters INSERT INTO T VALUES (6); then 6 = 10 so the insertion works fine. It would be nice if mysql supported this feature, but thanks for mysql. Have a nice day, Neil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems compiling PHP with mysql support as a apache DSO on aHPUX11.x box
Hi Andre, On Wed, 2002-02-20 at 19:04, Andre Konopka wrote: mmh, maybe it's not a mysql-problem... Indeed, doesn't look like it. Yes, I read through the numerous threads about this topic but I can't solve my problem After compiling php-4.1.1 with mysql support (mysql-3.23.47) as a DSO (./configure --enable-libgcc --with-mysql=/opt/mysql-3.23.47 --with-apxs=/opt/apache-1.3.23/bin/apxs) apache complains with the following error: bin/apachectl start /usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code) from /opt/mysql-3.23.47/lib/mysql/libmysqlclient.sl.10 Syntax error on line 236 of /opt/apache-1.3.23/conf/httpd.conf: Cannot load /opt/apache-1.3.23/libexec/libphp4.so into server: No such file or directory bin/apachectl start: httpd could not be started Ok, so /opt/apache-1.3.23/libexec/libphp4.so does not exist. Look at httpd.conf, where are the other Apache modules loaded from? Check where the make install of PHP puts the libphp4.so (also scan your disk for all occurrances of 'libphp4.so') Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL HELP
David, How would you use a temporary table? Is that the most efficient way? Use SELECT INTO temp_tbl to replace the sub-select clause. Rewrite the existing SELECT into a join with the temp_tbl. Is there another way? =dn Richard, Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Sub-selects not (yet) permitted by MySQL (RTFM: 1.7.4.1 Sub-SELECTs) Looks like a job for two SELECTs and a temporary table... Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Support contracts question
Just a trivial question. How does dual locations work? I am based in our Brisbane Australia office and am using MySQL as a backend for a company Intranet. Our parent company is based in Hayward, California and has recently converted an old DOS based database over to MySQL. We are both in the process of purchasing support contracts, but should we get one each? Or can we share even though there are 2 separate installations doing separate things on different platforms? Don't get me wrong, the cost of support doesn't bother me, I mean for what you get it is well worth the price, but this is just an issue of logistics (and accounting freaking out for duplicating items). Hope this question makes sense. Neil Silvester Webmaster Heat and Control Ph: +61 7 3877 6429 [EMAIL PROTECTED] http://www.heatandcontrol.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table file extensions
Hi Neil, On Wed, 2002-02-20 at 20:51, Neil Freeman wrote: I am using MySQL v3.23.40 on Windows NT. When creating various MyISAM tables the files saved are of the form *.frm *.MYD and *.MYI. So far so good. When I copy these files over to a Linux box though some of the *.MYD and *.MYI extensions appear as lowercase *.myd and *.myi This causes MySQL on Linux to complain as it looking for the uppercase versions of the files. Does anyone know how I can get around this, or even better, why the case appears to change? Depending on your version, you will find mysql_fix_extensions.sh in the scripts/ directory. I'll post it below just in case, it's pretty short. It's just useful because it's a pain to rename this stuff by hand ;-) [snip] #!@PERL@ # This is a utility for MySQL. It is not needed by any standard part # of MySQL. # Usage: mysql_fix_extentions datadir # does not work with RAID, with InnoDB or BDB tables # makes .frm lowercase and .MYI/MYD/ISM/ISD uppercase # useful when datafiles are copied from windows die Usage: $0 datadir\n unless -d $ARGV[0]; for $a ($ARGV[0]/*/*.*) { $_=$a; s/\.frm$/.frm/i; s/\.(is[md]|my[id])$/\U$/i; rename ($a, $_) || warn Cannot rename $a = $_ : $!; } [end snip] Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA LOCAL INFILE (Only loads 20,000 or 41,000 Records)
his is my first post here so please excuse any irrelevancies. I am trying to load a .csv file that contains 41943 Records of Data. I am only able to load 20972 Records. (it seems to skip almost every-other record). The file, located @: http://thisLinux.kicks-Ass.net/projects/databases/tbl_mapZipCodes.csv and contains 41943 lines that match the following comma-seperated structure: 'Pleasantville','NY','00401' 'Holtsville','NY','00501' 'Holtsville','NY','00544' Here is my query mysql LOAD DATA LOCAL INFILE '/usr/local/httpd/htdocs/projects/databases/tbl_ma - INTO TABLE RecruiterConsole.tbl_mapZipCodes - FIELDS TERMINATED BY ',' - OPTIONALLY ENCLOSED BY '\'' - LINES TERMINATED BY '\n' - (mapCity,mapState,mapZip); Here are the Results Query OK, 20972 rows affected (1.56 sec) Records: 20972 Deleted: 0 Skipped: 0 Warnings: 41943 I have tried a few different trouble-shooting scenarios and none seem to work for me. Anybody have any suggestions or similar experiences? Thanks in advance. Sean O'Donnell Monrovia, CA. [EMAIL PROTECTED] http://dynamicwebdevelopers.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Server configuration denies access to data source
Hi, I am a new user of MYSQL, when I use JDBC to connect MYSQL, I got exception: SQLException: Server configuration denies access to data source. Could some one kindly help me ? Thanks a lot. Jeff Zhao Here is my program, it is very simple import java.sql.*; public class LoadDriver { public static void main(String[] Args) { try { Class.forName(org.gjt.mm.mysql.Driver).newInstance(); } catch (Exception E) { System.err.println(Unable to load driver.); E.printStackTrace(); } try { Connection Conn = DriverManager.getConnection( jdbc:mysql://localhost/jzhaodb?user=jzhao); } catch (SQLException E) { System.out.println(SQLException: + E.getMessage()); System.out.println(SQLState: + E.getSQLState()); System.out.println(VendorError: + E.getErrorCode()); } } } __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Trouble with MySQL, C, gdb, pthreads
Hi there, I'm having quite a bit of trouble with an application I'm writing on Linux, using MySQL. The database connectivity stuff works fine, all my queries return sane results, etc, but I can't run the application under gdb. When I try to connect to the database when under gdb, I get: Access denied for user: 'dparrish@localhost' (Using password: YES) Without the debugger it runs fine. I've printed out the host, username, password, database and port beforehand, so I know they are correct. I'm now using MySQL-Max 4.0.1, but I had the same problem with 3.23.28. I am linking with libmysqlclient_r, and I'm calling my_init() at the start of the program, mysql_thread_init() at the start of the thread and mysql_thread_end() at the end of the thread. Any thoughts or solutions? - THREAD CODE SNIPPET mysql_thread_init(); if (!(dbhandle = mysql_init(NULL))) { log(LOG_CRITICAL, mysql_init returned an error: %s, mysql_error(dbhandle)); goto connection_end; } log(LOG_NOTICE, Connecting to mysql host %s, %s/%s, database %s, port %d, mysql_host, mysql_username, mysql_password, mysql_db, mysql_port); if (!(mysql_real_connect(dbhandle, mysql_host, mysql_username, mysql_password, mysql_db, mysql_port, NULL, 0))) { log(LOG_CRITICAL, Can't connect to MYSQL database: %s, mysql_error(dbhandle)); goto connection_end; } [...] connection_end: mysql_thread_end(); return false; --- Regards, David Parrish IT Director, Excido Pty Ltd http://www.excido.com/ Mobile: 0410 586 121 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.323 / Virus Database: 180 - Release Date: 8/02/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Support contracts question
Hi Neil, On Thu, 2002-02-21 at 08:26, Neil Silvester wrote: Just a trivial question. How does dual locations work? I am based in our Brisbane Australia office and am using MySQL as a backend for a company Intranet. Our parent company is based in Hayward, California and has recently converted an old DOS based database over to MySQL. We are both in the process of purchasing support contracts, but should we get one each? Or can we share even though there are 2 separate installations doing separate things on different platforms? Don't get me wrong, the cost of support doesn't bother me, I mean for what you get it is well worth the price, but this is just an issue of logistics (and accounting freaking out for duplicating items). I think this type of question would be best answered by the people at [EMAIL PROTECTED], so I've forwarded your message there. Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL HELP
You can also try: Select SUBSTRING( MAX( CONCAT(LPAD(calltime,6,'0'),finishcode) ), 7) AS finishcode, 0+LEFT( MAX( CONCAT(LPAD(calltime,6,'0'),finishcode )), 6) AS calltime From callhistory As H,calllist As L where H.rowid = L.rowid group by H.rowid This is from: http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html Although it's regarded as 'inefficient', but you can do it in one statement. Regards, Dan -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, 21 February 2002 11:18 a.m. To: David McInnis; 'Shade, Richard'; [EMAIL PROTECTED] Subject: Re: MYSQL HELP David, How would you use a temporary table? Is that the most efficient way? Use SELECT INTO temp_tbl to replace the sub-select clause. Rewrite the existing SELECT into a join with the temp_tbl. Is there another way? =dn Richard, Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Sub-selects not (yet) permitted by MySQL (RTFM: 1.7.4.1 Sub-SELECTs) Looks like a job for two SELECTs and a temporary table... Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with connecting JSP and mysql
Hi, On Thu, 2002-02-21 at 03:11, subodh kumar wrote: I am finding Problem with connecting JSP and mysql, it gives no error as well as exception,no output is coming out. please help me in this problem anticipating quick response I don't mean to be negative, but considering you're basically giving very little information yourself, I'd anticipate the response would be minimal, if not completely non-existant. The people on this list may be highly experienced, but they're not mind-readers or magicians! You may want to post the relevant part of your Java code, and explain exactly what you are doing. THEN people might be able to help. Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
urgent problem with mysql and phpmyadmin
I have created a table in the mysql db, using phpmyadmin with the following statement: create table books ( isbn char(13) not null,author char(30),title(60),price float(4,2),primary key(isbn)); which works fine and creates the table i need the problem comes when i try to insert some values into the price column using the phpmyadmin form, if for example i try to enter the price 34.99 it will only allow me to enter 34.9, and then when i go to the browse feature it shows the value as 34.90 any ideas on what's going wrong??? any help would be much apprecited. thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Deadlock
Everyone, I experienced table deadlock on my MySQL server...my client program does a Table Write lock before writing to it...but suddenly the computer hangs-up...do, other client got stuck and waiting for the table to be free for writing... But as I know...only the thread who does locking can do the unlockingis there a hard way to unlock the table?...an admin way...what I did...is just shut the server down and resume...can mysqladmin do it a simple way? R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: urgent problem with mysql and phpmyadmin
My guess. . . I have seen this problem with other databases . . . don't know if it is true for MySQL as well. Try to change to your field definition to float(5,2) as it may be counting the (.) as part of your field size. So if you number is xx.xx you will need 5,2. 4,2 would give you xx.x David McInnis -Original Message- From: Andrew Bunkell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 5:31 PM To: [EMAIL PROTECTED] Subject: urgent problem with mysql and phpmyadmin I have created a table in the mysql db, using phpmyadmin with the following statement: create table books ( isbn char(13) not null,author char(30),title(60),price float(4,2),primary key(isbn)); which works fine and creates the table i need the problem comes when i try to insert some values into the price column using the phpmyadmin form, if for example i try to enter the price 34.99 it will only allow me to enter 34.9, and then when i go to the browse feature it shows the value as 34.90 any ideas on what's going wrong??? any help would be much apprecited. thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: row level privilages - thoughts on how to do
Thanks Arjen, seems like a bit of a fiddle but not a bad solution considering some of the other options. Unfortunately I also need the row level locking of innodb so it's not going to work in my case. This lead me to ponder, if this sort of row level protection was added would it be at the mySQL level or the table handler level - ie do I need it in innodb table types or lower than that for it to work? I know this is sort of hypothetical at the moment but if it did get added then at what level it gets added would be of interest. Thanks again for the thoughts, cheers, noel On Wednesday, February 20, 2002 8:47 AM, Arjen Lentz [SMTP:[EMAIL PROTECTED]] wrote: Hi Noel, On Tue, 2002-02-19 at 13:40, [EMAIL PROTECTED] wrote: I'm wondering about row level privilages - being able to restrict what people see and can update etc on a row level as well as a column/table level. As others mentioned, having VIEWs wouldn't necessarily be of any help, since VIEWs are quite restrictive about INSERT/UPDATE type operations on the view. Anyway, after a little pondering, I think I've come up with a fairly nice solution for this problem. It is MySQL specific though: Use MyISAM MERGE tables (http://www.mysql.com/doc/M/E/MERGE.html). You can define a MERGE table, merging multiple *identical* MyISAM type tables. It is a bit like a view, in a way. You can do a query on the merge table, which will internally gather data from all the tables. How does this help you? You set up your permissions so that admin tools have full access to the merge table and all its parts. For individual users, you only grant access to their own table, to the extent that you want. Some shuffling between tables might be required when a row is for instance 'transfered' to another user, but I would figure that that is fairly easy to handle. Anyway, this way the plain end-user application can be bound by the user permissions, there's nothing specific the application needs to do, and no way for the user to circumvent that system. This may not be suitable for every situation, but it might be useful here Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INNODB conversion
Dear sir, I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? so any one can please tell me, if i missed any steps __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
hung read() after SIG ALARM + libmysqlclient + Race Condition?
Hope this makes sense I'm seeing random hung reads() after a mysql insert that appears to be related to a sig alarm race condition. The basic workflow of this program is while (1) recvfrom() /* few hundred packets a sec */ parse packet insert into sql However, once per second a sig alarm is generated which does house keeping and what-not. This will work for minutes or hours, but eventually the read(3, ...) blocks , which is the mysql socket. When this occurs you just see the repeated sig alarms being catch, but immediately returning to that read() which never returns. Note there is still hundreds of udp packets coming in but never able to be read because the never-returing read(3,) on the mysql sock. Normal syscalls resemble: write(3, \1\1\0\0\3INSERT INTO call_logging.de..., 261) = 261 read(3, \3\0\0\1, 4) = 4 read(3, \0\1\0, 3)= 3 recvfrom(5, ...) = 398 Here is (hopefully enough) machine information: *** Precompiled binaries from mysql.com. *** Linux mmlog2 2.4.9-21smp #1 SMP Thu Jan 17 14:01:48 EST 2002 i686 unknown *** My Program complied as such (tried both shared and static linked,) *** Note, this program isn't making use of threads, and I've seen the same *** results with -D_REENTRANT -lpthread and without, as well as linking *** the non _r libmysqlclient gcc -o clconsumer -O -D_REENTRANT -g -lpthread -lm -lz -I/usr/include/mysql -I/usr/local/mysql/include clconsumer.o /usr/local/mysql/lib/libmysqlclient_r.a mysql Ver 11.17 Distrib 3.23.49, for pc-linux-gnu (i686) Connection id: 13 Current database: Current user: root@localhost Current pager: stdout Using outfile: '' Server version: 3.23.49 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 4 hours 55 min 3 sec Threads: 4 Questions: 3261389 Slow queries: 0 Opens: 195 Flush tables: 1 Open tables: 7 Queries per second avg: 184.228 -- Here is the infinite syscalls when mysql isn't returning (expected?) data on the socket? read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted) --- SIGALRM (Alarm clock) --- time(NULL) = 1014267832 rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0 alarm(1)= 0 rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0 sigreturn() = ? (mask now []) read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted) --- SIGALRM (Alarm clock) --- time(NULL) = 1014267833 rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0 alarm(1)= 0 rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0 sigreturn() = ? (mask now []) read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted) --- SIGALRM (Alarm clock) --- Cliff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INNODB conversion
Hi Nilesh, On Thu, 2002-02-21 at 15:14, Nilesh Deshpande wrote: I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Ok, by the looks of this you're running on Windows (I see c:\ etc), right? After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections Here's where things start getting curious I see Unix-style pathnames here, with a /home/heikki/ etc. I don't see that happening on a Windows system, so the question is: where did you start the server? Like by chance on a *nix system you were connected to from the Win box? See, the printed info does not even match up with the config info you posted above you configured 2x 2GB data files, but the first one is initialised to something like 134 MB and the second one to 262 MB. I think you're looking at a completely different server there! After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? This is typical of a server that does not have InnoDB support built-in or enabled. I think you have to sort out what you're doing where, make sure you're configuring, starting, and connecting to the same server, and I'm confident that once that is the case, the above problem will disappear. Do let us know on the list how you fare with this Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INNODB conversion
At 21:14 20/02/2002 -0800, Nilesh Deshpande wrote: Hi! Very strange the printed messages you sent. What MySQL release version are you using ? I did the test with your start InnoDB set with 3.23.48 and got: c:\mysql\binmysqld-max --standalone --console InnoDB: The first specified data file c:\ibdata\ibdata1 did not exist: InnoDB: a new database to be created! 020221 2:42:46 InnoDB: Setting file c:\ibdata\ibdata1 size to 200 MB InnoDB: Database physically writes the file full: wait... 020221 2:42:59 InnoDB: Data file c:\ibdata\ibdata2 did not exist: new to be cr eated 020221 2:42:59 InnoDB: Setting file c:\ibdata\ibdata2 size to 200 MB InnoDB: Database physically writes the file full: wait... 020221 2:43:13 InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 30 MB InnoDB: Database physically writes the file full: wait... 020221 2:43:15 InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 30 MB InnoDB: Database physically writes the file full: wait... 020221 2:43:18 InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 30 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 020221 2:43:30 InnoDB: Started mysqld-max: ready for connections Notice the paths of the files. You showed: /home/heikki/data/ibdata1 did not exist: The above is should be the Heikki's Unix machine !. Please send for us the release version and the complete configuration data file (my.ini/my.cnf). Regards, Miguel Dear sir, I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? so any one can please tell me, if i missed any steps __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Re: INNODB conversion
At 21:14 20/02/2002 -0800, Nilesh Deshpande wrote: Hi, Now I assume that the text you sent is from our documentation. For to build an InnoDB table: mysql create table table_name (id int)type=innodb; Query OK, 0 rows affected (0.14 sec) Regards, Miguel Dear sir, I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? so any one can please tell me, if i missed any steps __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Help
Is there another way as I do not have that version of MYSQL installed on my server? THanks- KCK --- Tod Harter [EMAIL PROTECTED] wrote: On Wednesday 20 February 2002 09:20, Web boy wrote: You want to use UNION. If I remember correctly its a fairly new feature for MySQL, but its a standard SQL thing. From the manual: SELECT ... UNION [ALL] SELECT ... [UNION SELECT ...] UNION is implemented in MySQL 4.0.0. UNION is used to combine the result from many SELECT statements into one result set. The SELECT commands are normal select commands, but with the following restrictions: * Only the last SELECT command can have INTO OUTFILE. * Only the last SELECT command can have ORDER BY. If you don't use the keyword ALL for the UNION, all returned rows will be unique, like if you had done a DISTINCT for the total result set. If you specify ALL, then you will get all matching rows from all the used SELECT statements. Now how in the world to I take run this query against both sets of tables and then combine the data and bring back a result? Thanks--- = Want to know when a band is playing or want to see what bands are playing at your favorite bar go to www.buffalogigs.com = Want to know when a band is playing or want to see what bands are playing at your favorite bar go to www.buffalogigs.com __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: column1 like column2%
Hi, Try this: select * from table1,table2 where table1.column1 like concat(table2.column2,'%'); Anvar. At 04:57 PM 20/02/2002 +, you wrote: mysql,query Hi , is there any way of performing something like the below statement? select * from table1,table2 where table1.column1 like table2.column2 thanks in advance Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replicate-ignore-table
On Wed, Feb 06, 2002 at 10:02:02AM -0600, Todd Newbold wrote: Hi everyone, When using replicate-ignore-table in mysql, does filtering happen before or after transmission from the master to the slave? Is the directive on the slave? If so, that's where it happens. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 13 days, processed 441,175,616 queries (372/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fake interval data type
Hi! Its kind of a subtle point, but innodb's locking not only locks rows, it locks the ABSENCE of rows. If I understand correctly this means that something like: BEGIN WORK SELECT COUNT(*) FROM table WHERE starttime? AND endtime? (check the count) INSERT INTO table (if count is zero) COMMIT SHOULD do the trick. The transaction should block anything else that tries to do an insert into the same range before you do your commit. I must add you have to use a LOCKING read in the SELECT. Either SELECT ... FROM ... WHERE .. FOR UPDATE; which sets exclusive locks, or SELECT ... FROM ... WHERE .. FOR LOCK IN SHARE MODE; which sets shared locks on the rows (to be precise, index records) it encounters. Without these additional clauses the default SELECT mode of InnoDB is the consistent non-locking read of Oracle. Locking reads in InnoDB also lock the absence of rows, as Tod states. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyODBC Blocking Table for DROP request ?
Hello everybody, here is a strange thing that creates me nightmares ... How-To-Repeat: Stage 1 . Using a my SQL GUI : Create a simple Table ... create table FAKE (bidon integer) Everything works fine ... Stage 2. Using a C compiler and MyODBC3.51 Driver (actually I use Borland as C Compiler) h_hndl hconn,hstmt,henv ; SQLRETURN res = SQL_ERROR ; res = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,henv) ; if (res != SQL_SUCCESS) return FALSE ; res = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INT EGER) ; if (res != SQL_SUCCESS) return FALSE ; res = SQLAllocHandle(SQL_HANDLE_DBC, henv,hconn) ; if (res != SQL_SUCCESS) return FALSE ; res = SQLConnect(hconn, DSNName,...,'UserName', Password...', ..) ; if (res != SQL_SUCCESS)return FALSE ; res = SQLAllocHandle(SQL_HANDLE_STMT,hconn,hstmt); if (res != SQL_SUCCESS) return FALSE ; res= SQLColumns(hstmt, NULL, 0, // All catalogs NULL, 0, // All schemas (unsigned char*) FAKE, SQL_NTS, // CUSTOMERS table NULL, 0); // All columns res = SQLFreeHandle (SQL_HANDLE_STMT,hstmt); if (res != SQL_SUCCESS)return FALSE ; res = SQLDisconnect (hconn); if (res != SQL_SUCCESS)return FALSE ; res = SQLFreeHandle (SQL_HANDLE_DBC,hconn); if (res != SQL_SUCCESS)return FALSE ; res = SQLFreeHandle (SQL_HANDLE_ENV,henv); if (res != SQL_SUCCESS)return FALSE ; Everything successes in this code , always SQL Success is returned ( note: I removed the lines that use the result set of SQLColumns.. because of no interest ..) Stage 3. Using the GUY , try to erase the table ... DROP table FAKE Message : Error on delete of '.\LemoineDB\fake.MYI' (Errcode: 13) (I think this is an permission problem) I am almost sure I habe all the permissionss : I cannot succeed in deleting the table until I stop the server and restart it . Once the server is restarted I can drop the table using the command above with no problem . Other test I did : If I comment out the SQLColumns API , everything works fines from stage 1 to 3 . Fix: I have to restart the server to drop the tables ..(that not a fix ) :--))) See also information below and joined the MyODBC.log I created running the code . Please , give me a quick answer even if no quick solution . Thank you . Best Regards, Fabrice Schleifer .. Synopsis: synopsis of the problem (one line) Submitter-Id: submitter ID Originator: your name Organization: your organization MySQL support: [none | licence | email support | extended email support ] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld, mysql client, C, PHP, PERL, ...] Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-3.23.38 Exectutable: [mysqld-nt] Environment: Pentium 4 1.5 Ghz 512 Mo System: Win2000 Compiler: VC++ 6.0 Architecture: i - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems compiling PHP with mysql support as a apache DSO on a HPUX11.x box
Wolfgang Pichler wrote: Do you have also recompiled apache after compiling php ? YES Am Mit, 2002-02-20 um 10.04 schrieb Andre Konopka: Hi, mmh, maybe it's not a mysql-problem... Yes, I read through the numerous threads about this topic but I can't solve my problem After compiling php-4.1.1 with mysql support (mysql-3.23.47) as a DSO (./configure --enable-libgcc --with-mysql=/opt/mysql-3.23.47 --with-apxs=/opt/apache-1.3.23/bin/apxs) apache complains with the following error: bin/apachectl start /usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code) from /opt/mysql-3.23.47/lib/mysql/libmysqlclient.sl.10 Syntax error on line 236 of /opt/apache-1.3.23/conf/httpd.conf: Cannot load /opt/apache-1.3.23/libexec/libphp4.so into server: No such file or directory bin/apachectl start: httpd could not be started I compiled all the stuff with gcc version 2.95.2 19991024 (release) Here are the options I used to compile MySQL CXX=gcc CFLAGS=-fomit-frame-pointer -O6 -fpic CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti -O6 ./configure --prefix=/opt/mysql-3.23.47 --with-low-memory --with-innodb chatr Any idea?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems compiling PHP with mysql support as a apache DSO on a HPUX11.x box
Yes, I read through the numerous threads about this topic but I can't solve my problem After compiling php-4.1.1 with mysql support (mysql-3.23.47) as a DSO (./configure --enable-libgcc --with-mysql=/opt/mysql-3.23.47 --with-apxs=/opt/apache-1.3.23/bin/apxs) apache complains with the following error: bin/apachectl start /usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code) from /opt/mysql-3.23.47/lib/mysql/libmysqlclient.sl.10 Syntax error on line 236 of /opt/apache-1.3.23/conf/httpd.conf: Cannot load /opt/apache-1.3.23/libexec/libphp4.so into server: No such file or directory bin/apachectl start: httpd could not be started Ok, so /opt/apache-1.3.23/libexec/libphp4.so does not exist. Look at httpd.conf, where are the other Apache modules loaded from? Sorry, didn't mention it, of course /opt/apache-1.3.23/libexec/libphp4.so exist. Apache complains with a systeax error if apache could not load module... Andre - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Segmentation Fault at run-all-tests¹ script..
Hey all, Just did a compile and install of v4.01 for multiprocessor Sparc. Server has started, runs OK, have fiddled with privileges successfully... Naturally, next step: I¹d like to test everything. But now, I get a Segmentation Fault immediately at run-all-tests¹ script in sql-bench. Any ideas? Lou - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fake interval data type
Oops, the syntax is SELECT ... FROM ... WHERE .. LOCK IN SHARE MODE; Heikki -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 21, 2002 9:27 AM Subject: Re: Fake interval data type Hi! Its kind of a subtle point, but innodb's locking not only locks rows, it locks the ABSENCE of rows. If I understand correctly this means that something like: BEGIN WORK SELECT COUNT(*) FROM table WHERE starttime? AND endtime? (check the count) INSERT INTO table (if count is zero) COMMIT SHOULD do the trick. The transaction should block anything else that tries to do an insert into the same range before you do your commit. I must add you have to use a LOCKING read in the SELECT. Either SELECT ... FROM ... WHERE .. FOR UPDATE; which sets exclusive locks, or SELECT ... FROM ... WHERE .. FOR LOCK IN SHARE MODE; which sets shared locks on the rows (to be precise, index records) it encounters. Without these additional clauses the default SELECT mode of InnoDB is the consistent non-locking read of Oracle. Locking reads in InnoDB also lock the absence of rows, as Tod states. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MyODBC Blocking Table for DROP request ?
Hi, -Original Message- From: Fabrice Schleifer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 11:31 PM To: [EMAIL PROTECTED] Subject: MyODBC Blocking Table for DROP request ? Hello everybody, here is a strange thing that creates me nightmares ... How-To-Repeat: Stage 1 . Using a my SQL GUI : Create a simple Table ... create table FAKE (bidon integer) Everything works fine ... Stage 2. Using a C compiler and MyODBC3.51 Driver (actually I use Borland as C Compiler) cut Everything successes in this code , always SQL Success is returned ( note: I removed the lines that use the result set of SQLColumns.. because of no interest ..) Stage 3. Using the GUY , try to erase the table ... DROP table FAKE Message : Error on delete of '.\LemoineDB\fake.MYI' (Errcode: 13) (I think this is an permission problem) I am almost sure I habe all the permissionss : I cannot succeed in deleting the table until I stop the server and restart it . Once the server is restarted I can drop the table using the command above with no problem . Other test I did : If I comment out the SQLColumns API , everything works fines from stage 1 to 3 . Fix: I have to restart the server to drop the tables ..(that not a fix ) :--))) See also information below and joined the MyODBC.log I created running the code . Please , give me a quick answer even if no quick solution . I could able to reproduce the bug, and will fix it for the next release versions by finding the exact cause (possibly some where we are not closing the opened session while using the mysql_list_fields()). The immediate work around is to execute FLUSH TABLES before you try to drop it, that way you can eliminate the server shutdown. And, thanks for the good functionality bug. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php