mysql_fix_privilege_tables doesn't fix privilege tables [50 character or so descriptive subject here (for reference)]
Description: When I start up mysqld (4.1.0-alpha), I get the message in the error log: mysql.user table is not updated to new password format: Disabling new password usage until mysql_fix_privilege_tables is run I *HAVE* run mysql_fix_privilege_tables. At least 4 times. I have a whole bunch of privilege columns including super_priv. Every time I run it it corrupts the (select-only web server) users I created since upgrading (from 3.23.56 to 4.1.0-alpha) by handing out privileges I don't want these users to have, like CREATE TEMPORARY TABLES. What's the point of SHOW PRIVILEGES? There are a lot of privilege columns in mysql.user that don't show in SHOW PRIVILEGES, yet I can grant them and display them in SHOW GRANTS FOR ... . I thought it was to keep the list of privileges in sync with what the server actually supports? How-To-Repeat: Start mysqld. Get warning message. Run mysql_fix_privilege_tables. Repeat ad nauseum. Fix: Is the mysql_fix_privilege_tables that came with 4.1.0-alpha really up to date? Submitter-Id: submitter ID Originator:Gordon Burditt Organization: MySQL support: none [none | licence | email support | extended email support ] Synopsis: mysql_fix_privilege_tables doesn't fix the privilege tables Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (FreeBSD port: mysql-server-4.1.0_1) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for portbld-freebsd4.7 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.0-alpha-log Protocol version10 Connection mysql.burditt.org via TCP/IP TCP port3306 Uptime: 2 hours 25 min 6 sec Threads: 1 Questions: 385428 Slow queries: 7 Opens: 35 Flush tables: 1 Open tables: 29 Queries per second avg: 44.272 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: FreeBSD hammy.lonestar.org 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Wed Feb 19 01:29:17 CST 2003 [EMAIL PROTECTED]:/scratch5/i386-obj/usr/src/sys/HAMMY i386 Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1218496 Dec 3 2002 /usr/lib/libc.a lrwxrwxr-x 1 root wheel 9 Dec 3 2002 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 574916 Dec 3 2002 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.1.0_1' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--with-openssl' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.7' 'CFLAGS=-O -pipe -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.7' 'CC=cc' 'CXXFLAGS=-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/PHP ?
Hi Ted, 1. Modify the query which presents all records by selecting let's say the peopleID. 2. When you build your update link you can use that ID to ask for a particular record. 3. Then you can use the same query + AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK. something like this: ALL RECORDS: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; PARTICULAR RECORD: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK; regards Salam [EMAIL PROTECTED] wrote: I'm writing here in hopes of finding someone who uses this and might be able to help me. I am dealing with a recordset who's query is thus: SELECT firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; My tables and keys are: table 1) people - primary key = peopleID table 2) make - primary key = makeID table 3) models - primary key = modelID - foreign key = make ID table 4) machines - primary key = machinesID - foreign keys = peopleID, modelID The above query (using Dreamweaver MX speak) is a repeating region behavior so that I get a master PHP page in web browser that displays all my records with the fields as I wish them (using the data from the 4 tables), that works fine --thanks all. At the end of each row (record) on that master page I am building 2 links: Update and Delete. The Update link will lead to a detail page of that particular record, identical visually to the particular record in the master page except that it will be presented in an editable/submit form -that will update the record in the database. Right now my problem is how to adjust the recordset query -above- to produce the result that will give that specific record/row that the link is next to. Right now I have the link working but I am using the same query as above and I am not getting the row that is next to the link, i.e., I'm not getting the correct record -but some other record. If anyone on this list has any knowledge and could help get over this hurdle I would be EXTREMELY grateful -been trying to figure this out for days. I apologize in advance for posting this here if that was wrong. I won't do again, I'm just looking for some help. I have several books and have been to other forums and the internet... still working on it. Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: freebsd 5.1 + mysql 4.0.13
On Sat, Jun 28, 2003 at 01:14:37PM +0200, Richard Fuchs wrote: hi! i'm using mysql 4.0.13 from the standard freebsd 5.1 ports distribution, and i keep getting repeated random table corruptions. the same thing happens when building both with the native threading lib, and with linuxthreads. i figured it would be related to threading as it's always happening when the mysql server is loaded, that is has lots of concurrent queries on the same table going on. suddenly the table would get corrupted, and i have to run a repair table on it (this always nukes a few rows from the table). it's reproducable only in the sense that it keeps happening randomly ;) are there any known issues with this combination, or with mysql 4.0.13 or freebsd 5.1 in general? what could be the cause for this, and how could it be fixed? You seem to be the first reporting a problem with FreeBSD 5.1 and MySQL. Do you *need* to use 5.1 or could you try 4.8 to see if the problem goes away? That'd help to narrow it down to eithe MySQL or something specific to FreeBSD 5.1. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 25 days, processed 813,050,547 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/INNODB speed on large databases
On Sat, Jun 28, 2003 at 01:47:28PM -0400, Wendell Dingus wrote: Hello, I've got a situation I'm hoping someone here can help me out with. We have a web server serving up data via some PHP programs pulling from MySQL (3.23.56 currently) in INNODB tables. There are 40+ (active) databases totalling a bit over 28 Gigabytes at this point. As we add more users speed is beginning to drop... Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of RAM. 4 x 15K RPM drives RAID5. It's hitting load averages 12 during peak times and database query speeds are really starting to drag... Uh oh. I've talked some with the consulting folks at MySQL and have the intention of contracting them to help analyze our database layout, structure, and queries to help optimize things. Smart move. My quandry is this though.. The existing server has 4GB but I can't get innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that route if it could be utilized, I've not figured out how though and assume that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a 64-bit address space sounds good. Yeah, if you want more stuff in memory, look seriously at the Opterons. I'm sure I can get the LVS approach with a load balancer and 3 or 4 back-end real servers going without too much trouble. Where do I spend the money most wisely though? More and more and more RAM in each backend server or is anything over 2GB going to mostly not be used (Probably P4 2.4Ghz XEON boxes). Drive speed? I'm thinking I'll test out using fast SCSI drives and maybe some SATA drives RAID-0 stiped versus non-striped to see the speed difference. My whole goal is speed, speed, and more speed out of MySQL! That's what I'd suggest. Get some SCSI disks with really low seek times (4ms) and stripe using RAID-0. The actual web applications place a very small load on the server, viewing top or mytop show that database access is where all the time is going and primarily on the 2 largest databases. A query against one of the other databases when the server is mainly idle happen in very few seconds or tenths. The same query (data layout is basically identical across all the databases) takes 2 minutes sometimes on either of the largest 2 databases. Can you post the query and the result of running it thru EXPLAIN? I have the bosses ear for hardware _now_ though and want to move forward before that opportunity passes. I'll post the database specs at the end of this. If you were hosting that much data without a ton of inserts all day long but lots of selects where would you concentrate your efforts? Speed of CPU or speed/quantity of RAM or speed of drives or what? You haven't told us how busy the CPU gets, so I'm going to assume it's *not* the bottleneck. I'd focus ot disk performance and having sufficent memory. Would you load balance a quantity of servers or lean more towards a couple of much larger 64-bit servers? Running entirely out of RAM is very appealing but somehow I suspect that will be cost prohibitive :) Just buy reasonable hardware, load-balance it, and then get the MySQL folks to help optimize things? That's the approach we use at work and it works well. Keeps the costs down and makes [most] scaling pretty easy. Mytop output after about 28 hours of uptime so this is just Friday during business hours access primarily: MySQL on localhost (3.23.56-Max-log) up 1+04:56:30 [13:29:15] Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 Threads Total: 33Active: 1 Cached: 0 Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 Ugh. Only 17 queries/sec average? Hmm. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 25 days, processed 813,104,760 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: freebsd 5.1 + mysql 4.0.13
Jeremy Zawodny wrote: On Sat, Jun 28, 2003 at 01:14:37PM +0200, Richard Fuchs wrote: are there any known issues with this combination, or with mysql 4.0.13 or freebsd 5.1 in general? what could be the cause for this, and how could it be fixed? You seem to be the first reporting a problem with FreeBSD 5.1 and MySQL. Do you *need* to use 5.1 or could you try 4.8 to see if the problem goes away? That'd help to narrow it down to eithe MySQL or something specific to FreeBSD 5.1. no i don't *need* it, but switching os's would mean more downtime while possibly not even fixing the problem. and if i were switching os, i would probably go to linux anyway... i've narrowed the problem down already: i have specified enable-locking and skip-name-resolve in my.cnf now, and i haven't had any corruptions for a whole day now. it looks like one of those two options fixes the problem. i can't really tell which one of them does it, and i don't feel like playing russian roulette trying to find that out, so this is my works-for-me case for now... cheers richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: freebsd 5.1 + mysql 4.0.13
On Sun, Jun 29, 2003 at 08:55:30AM +0200, Richard Fuchs wrote: Jeremy Zawodny wrote: On Sat, Jun 28, 2003 at 01:14:37PM +0200, Richard Fuchs wrote: are there any known issues with this combination, or with mysql 4.0.13 or freebsd 5.1 in general? what could be the cause for this, and how could it be fixed? You seem to be the first reporting a problem with FreeBSD 5.1 and MySQL. Do you *need* to use 5.1 or could you try 4.8 to see if the problem goes away? That'd help to narrow it down to eithe MySQL or something specific to FreeBSD 5.1. no i don't *need* it, but switching os's would mean more downtime while possibly not even fixing the problem. and if i were switching os, i would probably go to linux anyway... Well, we have dozens of servers running on 4.8 that haven't had such problems. That's why I suggested it. After all, 4.8 is stable while 5.1 is current. i've narrowed the problem down already: i have specified enable-locking and skip-name-resolve in my.cnf now, and i haven't had any corruptions for a whole day now. it looks like one of those two options fixes the problem. i can't really tell which one of them does it, and i don't feel like playing russian roulette trying to find that out, so this is my works-for-me case for now... Interesting. The skip-name-resolve shouldn't be an issue when using LinuxThreads. As for enable-locking, that sounds suspicious. You weren't running other proceses outside of MySQL that might work with the data files (myisamchk, for example), were you? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 813,480,202 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/PHP ?
[EMAIL PROTECTED] wrote: Thanks Salam, but a question: this would mean that I must do by hand every record ID link, no? I don't think you are building your master html page by hand, do you? No, of course no hand work. The script should take care of it e.g. in a loop while fetching the records from the 1st query to be presented (each record will have it's own peopleID) you have to add something like this to the url which will be then read by the update script. yourUpdateScript?id=$peopleID Salam On Sunday, June 29, 2003, at 02:29 AM, Salam Baker Shanawa wrote: Hi Ted, 1. Modify the query which presents all records by selecting let's say the peopleID. 2. When you build your update link you can use that ID to ask for a particular record. 3. Then you can use the same query + AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK. something like this: ALL RECORDS: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; PARTICULAR RECORD: SELECT peopleID, firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID AND peopleID=THE_ID_OF_THAT_RECORD_EXTRACTED_FROM_THE_LINK; regards Salam [EMAIL PROTECTED] wrote: I'm writing here in hopes of finding someone who uses this and might be able to help me. I am dealing with a recordset who's query is thus: SELECT firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID; My tables and keys are: table 1) people - primary key = peopleID table 2) make - primary key = makeID table 3) models - primary key = modelID - foreign key = make ID table 4) machines - primary key = machinesID - foreign keys = peopleID, modelID The above query (using Dreamweaver MX speak) is a repeating region behavior so that I get a master PHP page in web browser that displays all my records with the fields as I wish them (using the data from the 4 tables), that works fine --thanks all. At the end of each row (record) on that master page I am building 2 links: Update and Delete. The Update link will lead to a detail page of that particular record, identical visually to the particular record in the master page except that it will be presented in an editable/submit form -that will update the record in the database. Right now my problem is how to adjust the recordset query -above- to produce the result that will give that specific record/row that the link is next to. Right now I have the link working but I am using the same query as above and I am not getting the row that is next to the link, i.e., I'm not getting the correct record -but some other record. If anyone on this list has any knowledge and could help get over this hurdle I would be EXTREMELY grateful -been trying to figure this out for days. I apologize in advance for posting this here if that was wrong. I won't do again, I'm just looking for some help. I have several books and have been to other forums and the internet... still working on it. Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: freebsd 5.1 + mysql 4.0.13
Jeremy Zawodny wrote: On Sun, Jun 29, 2003 at 08:55:30AM +0200, Richard Fuchs wrote: Jeremy Zawodny wrote: On Sat, Jun 28, 2003 at 01:14:37PM +0200, Richard Fuchs wrote: are there any known issues with this combination, or with mysql 4.0.13 or freebsd 5.1 in general? what could be the cause for this, and how could it be fixed? You seem to be the first reporting a problem with FreeBSD 5.1 and MySQL. Do you *need* to use 5.1 or could you try 4.8 to see if the problem goes away? That'd help to narrow it down to eithe MySQL or something specific to FreeBSD 5.1. no i don't *need* it, but switching os's would mean more downtime while possibly not even fixing the problem. and if i were switching os, i would probably go to linux anyway... Well, we have dozens of servers running on 4.8 that haven't had such problems. That's why I suggested it. After all, 4.8 is stable while 5.1 is current. well it wasn't my choice. personally i'm a linux guy, but right now i'd rather have minimum downtime w/o too much playing around. i've narrowed the problem down already: i have specified enable-locking and skip-name-resolve in my.cnf now, and i haven't had any corruptions for a whole day now. it looks like one of those two options fixes the problem. i can't really tell which one of them does it, and i don't feel like playing russian roulette trying to find that out, so this is my works-for-me case for now... Interesting. The skip-name-resolve shouldn't be an issue when using LinuxThreads. As for enable-locking, that sounds suspicious. You weren't running other proceses outside of MySQL that might work with the data files (myisamchk, for example), were you? no of course not :) unless the default freebsd install comes with some cron jobs or something that do something nasty with the tables behind the scenes (just kidding, but i've actually checked for that). is it possible that the internal locking is broken and doesnt work right, and the use of external locking works as a kind of workaround for this? greets richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql-test-run failure Mac OS X 10.2.6 ctype_latin1_de test [assumed]
I am sorry, I forgot to mention that MySql passed or skipped all tests up to the ctype_latin1_de test the message in terminal is ERROR: /usr/local/stayathome/mysql/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting I have tried mysql-test-run --force (times out after 30 seconds) and mysql-test-run ctype_latin1_de --force (times out after 400 seconds) and the distro is http://openjaguar.online.fr/ Thank you, Bob On Saturday, June 28, 2003, at 08:32 PM, Robert D. Sharp wrote: From: root [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [mysql-test-run failure Mac OS X 10.2.6] Description: Using OpenJaguar distribution www.openjaguar.fr MySql runs very slow How-To-Repeat: sudo perl mysql-test-run Fix: unknown Submitter-Id: Robert D. Sharp [EMAIL PROTECTED] Originator:System Administrator Organization: www.learningspace.org MySQL support: [none] Synopsis: MySql fails ctype_latin1_de test [assumed] Severity: [ non-critical | serious | critical ] (one line) File Edit Options Buffers Tools Help Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.13 (Source distribution) C compiler:gcc (GCC) 3.1 20020420 (prerelease) C++ compiler: g++ (GCC) 3.1 20020420 (prerelease) Environment: Powerbook G4, OS X.2.6, Libraries ? System: Darwin localhost 6.6 Darwin Kernel Version 6.6: Thu May 1 21:48:54 PDT 2003; root:xnu/x\ nu-344.34.obj~1/RELEASE_PPC Power Macintosh powerpc Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/libexec/gcc/darwin/ppc/3.1/specs Thread model: posix Apple Computer, Inc. GCC version 1151, based on gcc version 3.1 20020420 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxr-xr-x 1 root wheel 15 Jun 25 20:12 /usr/lib/libc.dylib - libSystem.dylib Configure command: ./configure '--prefix=/usr/local/stayathome/mysql' '--with-openssl=/usr' '--w\ ith-embedded-server' '--with-vio' '--enable-assembler' '--sharedstatedir=/usr/local/stayathome/m\ ysql/share/com' '--sysconfdir=/usr/local/stayathome/mysql/etc' '--infodir=/usr/local/stayathome/\ mysql/share/info' '--mandir=/usr/local/stayathome/mysql/share/man' Sorry for the cut and paste but the mysqlbugs script put me into an environment that I am not used to. This may be useless to you but I am interested. Bob -- It has been said before but warrants repeating, If you think education is expensive, try ignorance. Bob Sharp 6/7 th Grade Math Academy Teacher Board Member, The Learning Space First Elected Middle School Representative to the NCCE Board Recipient of The First Annual Learning Space Achievement Awards for Members -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: freebsd 5.1 + mysql 4.0.13
On Sun, Jun 29, 2003 at 09:38:09AM +0200, Richard Fuchs wrote: well it wasn't my choice. personally i'm a linux guy, but right now i'd rather have minimum downtime w/o too much playing around. Ah, got it. Interesting. The skip-name-resolve shouldn't be an issue when using LinuxThreads. As for enable-locking, that sounds suspicious. You weren't running other proceses outside of MySQL that might work with the data files (myisamchk, for example), were you? no of course not :) unless the default freebsd install comes with some cron jobs or something that do something nasty with the tables behind the scenes (just kidding, but i've actually checked for that). Good. is it possible that the internal locking is broken and doesnt work right, and the use of external locking works as a kind of workaround for this? Yeah, that's what it's starting to sounds like. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 815,367,494 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joins
Hi, I think there where 2 possibilities. 1. Add to the customers_basket table the categories_id SELECT cat.* FROM customers_basket AS cb, categories AS cat WHERE products_id=7 AND cb.categories_id=cat.categories_id 2. Make a relation table CREATE TABLE relation_product_category ( products_id int NOT NULL, categories_id int NOT NULL ); SELECT cat.* FROM relation_product_category AS rel, categories AS cat WHERE rel.products_id=7 atur -- Andrew wrote: Hi MySQL I want to run a result through a routine but I need to get the result first :) The table customer_basket contains the products_id which is no good for my routine. What I need to check is the categories_id but there is no reference in the customers_basket. Presumably I need to do a join on the table that holds the cat_id? So I already have the the product_id as this is in the customer_basket so to get the cat_id I need to select only the cat_id where product_id. Am I thinking straight on this problem, if so I cant seems to get a result here are the two tables: CREATE TABLE customers_basket ( customers_basket_id int NOT NULL auto_increment, customers_id int NOT NULL, products_id tinytext NOT NULL, customers_basket_quantity int(2) NOT NULL, final_price decimal(15,4) NOT NULL, customers_basket_date_added char(8), PRIMARY KEY (customers_basket_id) ); CREATE TABLE categories ( categories_id int NOT NULL auto_increment, categories_image varchar(64), parent_id int DEFAULT '0' NOT NULL, sort_order int(3), date_added datetime, last_modified datetime, PRIMARY KEY (categories_id), KEY idx_categories_parent_id (parent_id) ); Any ideas? Thanks andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stored procedures in mysql5
ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL/INNODB speed on large databases
On Sat, Jun 28, 2003 at 01:47:28PM -0400, Wendell Dingus wrote: Hello, I've got a situation I'm hoping someone here can help me out with. We have a web server serving up data via some PHP programs pulling from MySQL (3.23.56 currently) in INNODB tables. There are 40+ (active) databases totalling a bit over 28 Gigabytes at this point. As we add more users speed is beginning to drop... Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of RAM. 4 x 15K RPM drives RAID5. It's hitting load averages 12 during peak times and database query speeds are really starting to drag... p3's are ancient architecture now , but i have found trnasactions and full text searching to be very cpu intensive I've talked some with the consulting folks at MySQL and have the intention of contracting them to help analyze our database layout, structure, and queries to help optimize things. hmm the most common thing is to send the list an explain table and maybe the table layout someone may help out, could be an indexing problem. My quandry is this though.. The existing server has 4GB but I can't get innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that route if it could be utilized, I've not figured out how though and assume that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a 64-bit address space sounds good. hey funny u mentioned that i had a read @ thoms hardware i must recommend this as a worthy read http://www17.tomshardware.com/cpu/20030422/index.html ok so basically in a unix environment the opteron blew all over the XEON especially in the mysql benchmark, so the opteron/linux is going to be the recommended cheaper alternative server to sun/solaris, although obviouslly XEON kicked ass in rendering situations on an XP desktop as its optimised for it , so its the situation really i'm currently investigating in the right XEON chip and mobo for a XP rendering workstation for audio/video stuff, but if i were to build a unix server opteron all the way I have the bosses ear for hardware _now_ though and want to move forward before that opportunity passes. I'll post the database specs at the end of this. If you were hosting that much data without a ton of inserts all day long but lots of selects where would you concentrate your efforts? Speed of CPU or speed/quantity of RAM or speed of drives or what? maybe send a grab of top or something would be good Mytop output after about 28 hours of uptime so this is just Friday during business hours access primarily: MySQL on localhost (3.23.56-Max-log) up 1+04:56:30 [13:29:15] Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 Threads Total: 33Active: 1 Cached: 0 Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 hmm mytop is a good app -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MS-Project on MYSQL?
Hi, Anyone out there ever successfully implement the Microsoft Project SQL database architecture on a MySQL server? -tv Tom VanderMeulen; mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
groups, limits and counts.
I have a tabletheres a surprise month protocol port utime 06 tcp21 12 06 tcp21 13 05 udp 43 100232 05 udp 21 100245 what I would like to do is select by month and by protocol but within protocol limit to the top 5 by count of port. In other words,I want the 5 most common instances of port per instance of protocol per instance of month. is this actually possible with a single query (mysql 4.0)? I have tried vairious combinations of group by and order by but however I do it, limit n, seems to do just that...limit the results to n rows... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
otherguy (Cameron Wilhelm ) wrote: Well, keep in mind that although SQL can do a lot in a single statement, it can't always do *everything* required for a business function in one statement. As I'm painfully aware of, but was hopeful about. Your hopes were sensible -- I believe that some SQL versions have an 'INTERSECT' analagous to the 'UNION' that would have done the job. (I've never used such an SQL, but I think they're out there somewhere!) It's a pity that MySQL doesn't have this extension. I can't thank you enough for your time and effort. I should be able to tweak this enough to make it usable. A pleasure; I enjoy this kind of thing. Besides, after pointing out that other solutions proposed weren't going to do it for you, I felt some obligation to come up with an alternative! Thanks again. Now I just have to beat up the system guys for making me do this :) You're welcome. Good luck with it. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
graphics
I cannot find how to upload graphics to a mysql database. Any suggestions? George Guebely Digital Directory USA, Inc (562)628-9677
NFS or replication?
I am interested in any thoughts that people may have for creating a scalable mysql infrastructure. I have a web application which runs on several front end web servers which hit one backend mysql server. Presently I can continue to grow by adding front end webservers -- the mysql server is not close to maxed out. Looking toward the future I will have to make a decision about how to grow the mysql serving capability and have several ideas on how to do it. Now some questions for the group: 1. I can guess that my select to insert/update ratio is probably on the order of 4:1 but is there a simple tool to use to determine the actual ratio. 2. When I want to scale up the mysql server what are the pros/cons of each of the following: a. Create an NFS server on the backend and load balance several mysql servers all accessing the same database files via NFS (is this even possible/desirable?) b. Make one big server the primary insert/update server and replicate the data out to many read-only slaves (at what ratio of read to writes in conjunction with number of slaves does this start to limit scalability). c. Buy one big monster server every year and hope to stay ahead of my needs (and have the previous years machine as a backup) 3. With a replication strategy how does one make sure that the current information is displayed to a user? For example, a frequent action in a web application is to update information in a user's profile. Typically the way this is done is for the user to enter the information into a web form submit the form and then the user gets a page with the current data displayed in a read-only format (so the user knows the update was successful). How do most people handle this to make sure that the current data is displayed? Do you just perform the select from the write server for this one case? Or is replication fast enough that performing the select from one of the slaves is ok? 4. Replication (choice b) seems to be the preferred way to go, based on what I have seen on the list; is there a reason why NFS is not an option? Also is the choice determined by the type of database (InnoDB vs. MyIsam). I am interested in any comments/experience people may have on this issue. I have many thoughts of my own regarding ease of maintenance, backup, reliability, ease of expansion, cost, performance, etc. However I have not had time or hardware to test the different possibilities and would greatly appreciate hearing what others have to say. Thank you for your comments, Steven Balthazor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replacing data in 1 table from another
# [EMAIL PROTECTED] / 2003-06-17 11:44:46 -0400: At 05:14 PM 6/16/2003, Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2003-06-16 15:15:31 -0400: I can use: SELECT sum(if(voidreason0,0,amt)) as sales FROM sdtik where acctno=1234 and datein=20020615 to gather customers' sales totals. How can I setup a single query where the newdata.sales would be replaced with the summarized data from the select. you need UPDATE or REPLACE, together with GROUP BY http://www.mysql.com/doc/en/REPLACE.html http://www.mysql.com/doc/en/UPDATE.html http://www.mysql.com/doc/en/SELECT.html UPDATE sales, sdtik SET sales.sales = SUM(IF(voidreason 0, 0, sdtik.amt)) WHERE sales.acctno = sdtik.acctno I tired both statements you suggested. The update is what I would like to use, but I got an error, ERROR 1064: You have an error in your SQL syntax near ' sdtik set sales.sales ' you use a pre-4.0.4 MySQL, right? multi-table updates are supported since 4.0.4 (as stated in UPDATE.html). -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joins
# [EMAIL PROTECTED] / 2003-06-29 08:14:28 +0100: The table customer_basket contains the products_id which is no good for my routine. What I need to check is the categories_id but there is no reference in the customers_basket. Presumably I need to do a join on the table that holds the cat_id? So I already have the the product_id as this is in the customer_basket so to get the cat_id I need to select only the cat_id where product_id. Am I thinking straight on this problem, if so I cant seems to get a result here are the two tables: CREATE TABLE customers_basket ( customers_basket_id int NOT NULL auto_increment, customers_id int NOT NULL, products_id tinytext NOT NULL, customers_basket_quantity int(2) NOT NULL, final_price decimal(15,4) NOT NULL, customers_basket_date_added char(8), PRIMARY KEY (customers_basket_id) ); CREATE TABLE categories ( categories_id int NOT NULL auto_increment, categories_image varchar(64), parent_id int DEFAULT '0' NOT NULL, sort_order int(3), date_added datetime, last_modified datetime, PRIMARY KEY (categories_id), KEY idx_categories_parent_id (parent_id) ); how is a product related to a category? CREATE TABLE products ( product_id ... category_id ... product_desc ... ) in this case you'll need something like this: SELECT p.category_id FROM products p, customers_basket cb WHERE cb.customers_id = 123 AND cb.products_id = p.product_id if a product can be in more than one category, you probably have another table instead of the products.category_id column: CRATE TABLE products_in_categories ( product_id ... category_id ... ) then you'd use this: SELECT pic.category_id FROM products_in_categories pic, customers_basket cb WHERE cb.customers_id = 123 AND cb.products_id = pic.product_id -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
two-field primary key update issue
I have a table with a two-field primary key, it's structure is like this: CREATE TABLE extraFieldOptions ( fieldID tinyint(3) unsigned NOT NULL default '0', fieldOption varchar(16) NOT NULL default '', fieldValue varchar(16) NOT NULL default '', PRIMARY KEY (fieldID,fieldOption) ) TYPE=MyISAM; I can't figure out how to update the 'fieldValue' for a given row, here is my query: update extraFieldOptions set fieldValue = 'horizontal' where fieldID = '2' and fieldOption = 'alignment' mysql update extraFieldOptions set fieldValue = 'horizontal' where fieldID = '2' and fieldOption = 'alignment'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 MySQL doesn't produce an error.. but I think maybe it's the 'and' in my where clause that isn't working. I have looked high and low for an example of such a query but cannot find anything. Any help would be greatly appreciated. Thanks. -- Greg Donald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql server wont start
OK i am having problems starting the mysql server on Redhat Linux 8.0. A little aboutmy background. I have some experience getting around in linux on the command line. Proably enough just to be dangerous :-). I have installed and used Mysql under Windows 2000 no problem. Question: I installed Mysql from an RPM on Redhat 8.0 Im fairly sure that it installed properly at least i didnt see any error messages. This is what it gave me using mysql.server start: (it gave me basically the same message when using mysqld_safe) [EMAIL PROTECTED] mysql]# ./mysql.server start [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 030629 12:21:45 mysqld ended I assume this means that the server started with no problem? When I did a PS i didnt see the server running in the background I assume it should show up as a process. Then when i issuied the following according to the post-install documentation to set intial privlidges it gave this error. [EMAIL PROTECTED] bin]# mysqladmin -u root password 'star1234' Illegal instruction [EMAIL PROTECTED] bin]# ./mysql -u root mysql Illegal instruction Im sure there is something simple i have missed but it keeps telling me that everything is an illegal instruction. Id appreicate any help if someone has encountered something similar. Any more information you need just tell me. Thanks for you time, Blake _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting images to mysql - is it possible?
I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc
RE: mysql server wont start
Hi did you run mysql_install_db ? This sets up the mysql database and initial privilages Peter -Original Message- From: Blake Howe [mailto:[EMAIL PROTECTED] Sent: 29 June 2003 19:27 To: [EMAIL PROTECTED] Subject: mysql server wont start OK i am having problems starting the mysql server on Redhat Linux 8.0. A little aboutmy background. I have some experience getting around in linux on the command line. Proably enough just to be dangerous :-). I have installed and used Mysql under Windows 2000 no problem. Question: I installed Mysql from an RPM on Redhat 8.0 Im fairly sure that it installed properly at least i didnt see any error messages. This is what it gave me using mysql.server start: (it gave me basically the same message when using mysqld_safe) [EMAIL PROTECTED] mysql]# ./mysql.server start [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 030629 12:21:45 mysqld ended I assume this means that the server started with no problem? When I did a PS i didnt see the server running in the background I assume it should show up as a process. Then when i issuied the following according to the post-install documentation to set intial privlidges it gave this error. [EMAIL PROTECTED] bin]# mysqladmin -u root password 'star1234' Illegal instruction [EMAIL PROTECTED] bin]# ./mysql -u root mysql Illegal instruction Im sure there is something simple i have missed but it keeps telling me that everything is an illegal instruction. Id appreicate any help if someone has encountered something similar. Any more information you need just tell me. Thanks for you time, Blake _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL memory problem on AIX 4.3.2
Hi all, Now I'm facing a big problem and see if anyone know how to solve it. I'm using RS6000 AIX 4.3.2 CPU*2 1G RAM with HA. And DB is MySQL 4.0.12-max. Here is the content of my.cnf. [client] #password = your_password port= 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 set-variable= max_connections=400 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 log-bin log_slow_queries log_warnings server-id = 1 default_character_set=gbk # Uncomment the following if you are using BDB tables skip-bdb # Uncomment the following if you are using InnoDB tables skip-innodb # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Now I have to restart mysqld one time in every day, because of cannot connect it. The error message in .err file is: 030627 11:50:56 Error: Can't create thread to kill server 030627 11:51:06 mysqld restarted /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 030627 11:51:15 /home/mysql/bin/mysqld: Normal shutdown 030627 11:51:16 /home/mysql/bin/mysqld: Shutdown Complete 030627 11:51:16 mysqld ended 030627 12:05:19 mysqld started /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 030628 21:09:29 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit ' to allow mysqld to use more memory or you can add more swap space 030628 21:09:29 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit ' to allow mysqld to use more memory or you can add more swap space 030628 21:09:30 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit ' to allow mysqld to use more memory or you can add more swap space 030629 22:47:33 Error: Can't create thread to kill server 030629 22:47:43 mysqld restarted /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 030629 22:47:53 /home/mysql/bin/mysqld: Normal shutdown 030629 22:47:53 /home/mysql/bin/mysqld: Shutdown Complete 030629 22:47:53 mysqld ended 030629 23:07:26 mysqld started /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 ulimit result is ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) 131072 stack(kbytes)32768 memory(kbytes) 32768 coredump(blocks) 2097151 nofiles(descriptors) 2000 show status result is mysql show status; +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 123 | | Aborted_connects | 0 | | Bytes_received | 152280| | Bytes_sent | 125496454 | | Com_admin_commands | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 184 | | Com_change_master| 0 | | Com_check| 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 52| | Com_insert_select
Re: inserting images to mysql - is it possible?
Hi, You have to insert a new blob field where you can store binary data. But it is not recommanded to store images in a database. I recommande you to store images on a server filesystem and only the reference to the images in a database. Armand Digital Directory USA wrote: I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedures in mysql5
On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote: ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; There are a lot of SP examples in the source distribution or BK tree. Have a look at mysql-test/t/sp.test. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedures in mysql5
thanks this is pretty insane cant wait for its release :D -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 6:07 AM To: electroteque Cc: Mysql Subject: Re: stored procedures in mysql5 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote: ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; There are a lot of SP examples in the source distribution or BK tree. Have a look at mysql-test/t/sp.test. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedures in mysql5
insane you mean extremely fast? or easy to use? i'd like to know what those SPs are good for... -yves -Ursprüngliche Nachricht- Von: electroteque [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Montag, 30. Juni 2003 00:09 Betreff: RE: stored procedures in mysql5 thanks this is pretty insane cant wait for its release :D -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 6:07 AM To: electroteque Cc: Mysql Subject: Re: stored procedures in mysql5 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote: ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; There are a lot of SP examples in the source distribution or BK tree. Have a look at mysql-test/t/sp.test. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems connecting to server
Description: I have just installed MySQL 4.0.4 on Linux kernel 2.4.2-2 on a Hewlett-Packard Kayak machine. I ran the mysql_install_db script with no problems, but am unable to create a root password as directed by the script. I have started MySQL with ./bin/mysqld_safe as directed by the script, but when I try to create a root user with ./bin/mysqladmin -u root password 'thepword', I get: ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! This is the first install of MySQL on this server (and my first try at installing). I'm also wondering where you copy the support-files/mysql.server in Linux? How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.4-beta (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: Linux klinux 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/mh/perl /usr/bin/mh/make /usr/bin/mh/gmake /usr/bin/mh/gcc /usr/bin/mh/cc GCC: Reading specs from /usr/bin/mh/../lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro ' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 May 26 16:12 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql ' --with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldfl ags=-all-static --with-mysqld-ldflags=-all-static 'CFLAGS=-O2 -mcpu=pentiumpro ' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-const ructors' CXX=gcc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lookup used Version of MySql MySql Foreign Keys and Referencial Integrity
# [EMAIL PROTECTED] / 2003-06-28 16:56:13 +0200: I use mySQL on HP/UX 11.i - unfortunateley I am unable to lookup the used version. What is the command to do that? (according to mysql.info it is Version 3.23.42. ) SELECT VERSION(); -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: groups, limits and counts.
At 13:18 +0200 6/29/03, Mark Rowlands wrote: I have a tabletheres a surprise month protocol port utime 06 tcp21 12 06 tcp21 13 05 udp 43 100232 05 udp 21 100245 what I would like to do is select by month and by protocol but within protocol limit to the top 5 by count of port. In other words,I want the 5 most common instances of port per instance of protocol per instance of month. is this actually possible with a single query (mysql 4.0)? I have tried vairious combinations of group by and order by but however I do it, limit n, seems to do just that...limit the results to n rows... LIMIT is applied after all the other stuff. It won't do what you want. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't compile on NetBSD
Can any one help me? Hard to tell without more information. Also, since the issues are likely to be system/CPU dependent, you may need to ask on a netBSD list, as well. -- Joel Rees, programmer, Kansai Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp PS: I just checked the netbsd packages, ftp://ftp.netbsd.org/pub/NetBSD/packages/pkgsrc/databases/mysql-server/README.html ftp://ftp.netbsd.org/pub/NetBSD/packages/pkgsrc/databases/mysql-client/README.html and it looks like the most recent there is mysql 3.23.49nb3. (I wonder what nb3 means.) So, my instinctive pointer to the packages collection would have been wrong. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql server wont start
At 19:32 +0100 6/29/03, Peter Lovatt wrote: Hi did you run mysql_install_db ? This sets up the mysql database and initial privilages The RPM install should do that automatically. Peter -Original Message- From: Blake Howe [mailto:[EMAIL PROTECTED] Sent: 29 June 2003 19:27 To: [EMAIL PROTECTED] Subject: mysql server wont start OK i am having problems starting the mysql server on Redhat Linux 8.0. A little aboutmy background. I have some experience getting around in linux on the command line. Proably enough just to be dangerous :-). I have installed and used Mysql under Windows 2000 no problem. Question: I installed Mysql from an RPM on Redhat 8.0 Im fairly sure that it installed properly at least i didnt see any error messages. This is what it gave me using mysql.server start: (it gave me basically the same message when using mysqld_safe) [EMAIL PROTECTED] mysql]# ./mysql.server start [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 030629 12:21:45 mysqld ended I assume this means that the server started with no problem? When I did a PS i didnt see the server running in the background I assume it should show up as a process. Then when i issuied the following according to the post-install documentation to set intial privlidges it gave this error. [EMAIL PROTECTED] bin]# mysqladmin -u root password 'star1234' Illegal instruction [EMAIL PROTECTED] bin]# ./mysql -u root mysql Illegal instruction Im sure there is something simple i have missed but it keeps telling me that everything is an illegal instruction. Id appreicate any help if someone has encountered something similar. Any more information you need just tell me. Thanks for you time, Blake -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql server wont start
At 18:27 + 6/29/03, Blake Howe wrote: OK i am having problems starting the mysql server on Redhat Linux 8.0. A little aboutmy background. I have some experience getting around in linux on the command line. Proably enough just to be dangerous :-). I have installed and used Mysql under Windows 2000 no problem. Question: I installed Mysql from an RPM on Redhat 8.0 Im fairly sure that it installed properly at least i didnt see any error messages. This is what it gave me using mysql.server start: (it gave me basically the same message when using mysqld_safe) [EMAIL PROTECTED] mysql]# ./mysql.server start [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 030629 12:21:45 mysqld ended I assume this means that the server started with no problem? No, that means it ended, i.e., it's *not* running. If you look in the data directory (/var/lib/mysql on your system) there should be an error log (a file with a .err suffix). Does it contain any clues. When I did a PS i didnt see the server running in the background I assume it should show up as a process. Then when i issuied the following according to the post-install documentation to set intial privlidges it gave this error. [EMAIL PROTECTED] bin]# mysqladmin -u root password 'star1234' Illegal instruction [EMAIL PROTECTED] bin]# ./mysql -u root mysql Illegal instruction That's odd. Which RPM or RPMs did you install? (Please provide the *exact* names.) Im sure there is something simple i have missed but it keeps telling me that everything is an illegal instruction. Id appreicate any help if someone has encountered something similar. Any more information you need just tell me. Thanks for you time, Blake -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: .fil
In that case they are probably fixed width files. If they are not fixed with and are in fact delimited by tab or comma, you can use LOAD DATA to bring them in. See http://www.mysql.com/doc/en/LOAD_DATA.html And if they are fixed width, you can write a script in something like Perl to unpack the records into a comma or tab delimited format, and then use LOAD DATA to load the data into MySQL. Of course, the delimiter character may appear in the data, so you'll need to escape those as you unpack them. You'll want to consult the PICs while you are building the script, of course. -- Joel Rees, programmer, Kansai Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication for Remote
We work in a largely distributed environment, with servers in the main server room being thousands of kilometers (that's about 3/4 of a mile) from clients. We are hoping to set up a replication system so that select queries go to the local server (slave) at the client and update queries go to the server (master) at the main server room. Has anyone else researched this idea that may want to discuss ? We are using bundled mysql/linux/php/apache servers. ICT Helpdesk: 8939 7116
Re: Replication for Remote
1 mi. = 1.609 Km. ;-) On Sunday, June 29, 2003, at 10:37 PM, Rohan Hawthorne wrote: We work in a largely distributed environment, with servers in the main server room being thousands of kilometers (that's about 3/4 of a mile) from clients. We are hoping to set up a replication system so that select queries go to the local server (slave) at the client and update queries go to the server (master) at the main server room. Has anyone else researched this idea that may want to discuss ? We are using bundled mysql/linux/php/apache servers. ICT Helpdesk: 8939 7116 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-field primary key update issue
# [EMAIL PROTECTED] / 2003-06-29 13:09:03 -0500: CREATE TABLE extraFieldOptions ( fieldID tinyint(3) unsigned NOT NULL default '0', fieldOption varchar(16) NOT NULL default '', fieldValue varchar(16) NOT NULL default '', PRIMARY KEY (fieldID,fieldOption) ) TYPE=MyISAM; mysql update extraFieldOptions set fieldValue = 'horizontal' where fieldID = '2' and fieldOption = 'alignment'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 MySQL doesn't produce an error.. but I think maybe it's the 'and' in my where clause that isn't working. I have looked high and low for an example of such a query but cannot find anything. Any help would be greatly appreciated. Thanks. what was the value of fieldValue prior to the update? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-field primary key update issue
# [EMAIL PROTECTED] / 2003-06-29 13:09:03 -0500: CREATE TABLE extraFieldOptions ( fieldID tinyint(3) unsigned NOT NULL default '0', fieldOption varchar(16) NOT NULL default '', fieldValue varchar(16) NOT NULL default '', PRIMARY KEY (fieldID,fieldOption) ) TYPE=MyISAM; mysql update extraFieldOptions set fieldValue = 'horizontal' where fieldID = '2' and fieldOption = 'alignment'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 MySQL doesn't produce an error.. but I think maybe it's the 'and' in my where clause that isn't working. I have looked high and low for an example of such a query but cannot find anything. Any help would be greatly appreciated. Thanks. what was the value of fieldValue prior to the update? fieldValue is vertical before the update. -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting images to mysql - is it possible?
If you want to store images in the database, use a blob columntype.. And take a look at this example alot of people have based mysql binary storage off: http://www.php4.com/forums/viewtopic.php?t=6 good luck On Sun, 29 Jun 2003, Digital Directory USA wrote: I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication for Remote
Shouldn't be a problem.. if you already have a private link between sites your set, if not drop in a vpn solution to ensure end to end security.. The only main difference betten your situation and most (all servers are feet apart at LAN speed) is the WAN thruput/etc.. Mysql should keep re-trying to download updates if it fails due to communications failure or anything like that.. On Mon, 30 Jun 2003, Rohan Hawthorne wrote: We work in a largely distributed environment, with servers in the main server room being thousands of kilometers (that's about 3/4 of a mile) from clients. We are hoping to set up a replication system so that select queries go to the local server (slave) at the client and update queries go to the server (master) at the main server room. Has anyone else researched this idea that may want to discuss ? We are using bundled mysql/linux/php/apache servers. ICT Helpdesk: 8939 7116 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-field primary key update issue
# [EMAIL PROTECTED] / 2003-06-29 23:24:58 -0500: # [EMAIL PROTECTED] / 2003-06-29 13:09:03 -0500: CREATE TABLE extraFieldOptions ( fieldID tinyint(3) unsigned NOT NULL default '0', fieldOption varchar(16) NOT NULL default '', fieldValue varchar(16) NOT NULL default '', PRIMARY KEY (fieldID,fieldOption) ) TYPE=MyISAM; mysql update extraFieldOptions set fieldValue = 'horizontal' where fieldID = '2' and fieldOption = 'alignment'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 MySQL doesn't produce an error.. but I think maybe it's the 'and' in my where clause that isn't working. I have looked high and low for an example of such a query but cannot find anything. Any help would be greatly appreciated. Thanks. what was the value of fieldValue prior to the update? fieldValue is vertical before the update. Hmm, strange. I would bet that's not the case... CREATE TABLE extraFieldOptions ( fieldID tinyint(3) unsigned NOT NULL default '0', fieldOption varchar(16) NOT NULL default '', fieldValue varchar(16) NOT NULL default '', PRIMARY KEY (fieldID,fieldOption) ) TYPE=MyISAM; Query OK, 0 rows affected (0.09 sec) INSERT extraFieldOptions VALUES (2, 'alignment', 'vertical'); Query OK, 1 row affected (0.05 sec) UPDATE extraFieldOptions SET fieldValue = 'horizontal' WHERE fieldID = 2 AND fieldOption = 'alignment'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM extraFieldOptions; +-+-++ | fieldID | fieldOption | fieldValue | +-+-++ | 2 | alignment | horizontal | +-+-++ 1 row in set (0.00 sec) UPDATE extraFieldOptions SET fieldValue = 'horizontal' WHERE fieldID = 2 AND fieldOption = 'alignment'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 As you can see, what you see is MySQL's (documented) behavior in response to a noop update. I don't know what could be causing this otherwise. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
