rand()
Hi,
I am trying the below sql statment:
mysql> select c.city, t.periodo, t.vitofferta from travel t, city c where
t.cityid=c.id and t.periodo>"2001-07-20" and
month(t.periodo)=month("2001-07-20") and
YEAR(t.periodo)=YEAR("2001-07-20") and rand() limit 7;
The result:
+---+++
| city | periodo| vitofferta |
+---+++
| Mauritius | 2001-07-21 | 2199 |
| Napoli| 2001-07-28 | 645|
| London| 2001-07-31 | 345|
| London| 2001-07-31 | 375|
| London| 2001-07-31 | 375|
| London| 2001-07-31 | 385|
| London| 2001-07-31 | 385|
+---+++
Perfect. But when I try again I want it to display another sent of 7
records but it just keeps displaying the same.
+---+
| version() |
+---+
| 3.23.36 |
+---+
How do I do this.
Adrian
-
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.org
John Birrell wrote: > On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: > > MySQL AB and NuSphere had a meeting over the phone, we exchanged > > information and opinions and NuSphere will propose times for the > > next meeting. > Since these issues are between two private companies, please take > your discussions off this list. It is none of our business. By > all means, tell us the result. With all due respect, John, I for one absolutely disagree with you on this. I want to be kept as informed as MySQL AB and NuSphere are willing to keep me. I'd be willing to bet there are a sufficient number of participants here who agree with me. Tom Keller mysql? query? database? huh? - 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: About safe_mysqld - another problem
Hi All, This problem is predominant and is being experienced by all who are instaling mysql. please could someone be of help and help us newbies. Thanks Ajay --- peter_tkchoi <[EMAIL PROTECTED]> wrote: > > Hi, > >I'm a beginner of MySql. I've just installed the > lastest ver. MySql under RH7.1 Linux. First, I do > have runned the "mysql_install_db" script. When I > tried to up the MySql server by running safe_mysqld > script, it also failed and now I've got an error > message logged as follows: > > 010720 22:39:06 mysqld started > 010720 22:39:06 /usr/libexec/mysqld: Can't find > file: './mysql/host.frm' (errno: 13) > 010720 22:39:06 mysqld ended > > I want to ask if anybody help me: > > 1. What should I do in order to resolve this > problem??? > >Pls advise. Thank you for your attention. > > Regards. > Peter Choi > > > == > ·s®ö§K¶O¹q¤l«H½c http://sinamail.sina.com.hk > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.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
Which type of join do I need to use?
Maybe I'm trying to use the wrong kind of join ... I was trying to do an INNER JOIN but it wasn't working and I'm starting to think that maybe the problem was my understanding of what an INNER JOIN does rather than how I coded the INNER JOIN (although the code worked in Access but who says Access uses the right syntax!) I have 2 tables: Table1: AttractionID (Primary Key) MemberID AttractionName Desc Contact Address etc. Table2: AttTypeID (Primary Key) AttractionID (Indexed but not unique) Type Table 2 keeps a list of the categories that each attraction in table 1 falls under. For example, Restaurant, Resort, and Gift Shop. If Attraction 1 is a Resort with a Restaurant then there would be 2 entries in Table 2 for the attractionID of Attraction 1 one that had the Type of Resort and one that had the Type of Restaurant. I want to do a select that will bring up 1 row for each combination of Attraction 1 and it's associated category. I thought it should be something like this: SELECT ... FROM table1 INNER JOIN table2 ON table1.AttractionID = table2.AttractionID WHERE ... my condition But I'm starting to wonder if maybe it shouldn't be an INNER JOIN at all. Any help would be appreciated. Thanks. Bob Horton PneumaSoft Services Inc. Custom Software & Web Development Phone: (306) 545-1068 [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: Another Newbie Question
You may want to understand what distinct and avg means. The combination doesn't make sense. Bob Rea schrieb am Samstag, 21. Juli 2001, 02:36:11: > The SQL book I am using has this: mysql>> select avg(distinct prod_price) as avg_price > -> from Products > -> where vend_id = 'dll01' > -> ; > and responds thus: > ERROR 1064: You have an error in your SQL syntax near 'distinct > prod_price) as avg_price > from Products > where vend_id = 'dll01'' at line 1 > How do I do this, if I can? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: Fw: tricky RAND() function...
>From the German php FAQ:
version >= 3.23
SELECT * FROM tabelle ORDER BY RAND() LIMIT 1
version < 3.23
//see how many rows you have
$result = @mysql_query("SELECT COUNT(*) FROM $table");
$row = mysql_fetch_row($result);
//produce random number out of that
mt_srand((double)microtime()*100);
$number = mt_rand(0,$row[0]-1);
//fetch one record randomly
$result = @mysql_query("SELECT * FROM $table LIMIT $number,1");
Of course, you could encapsulate that stuff nicely into a
function.
Derick Dorner schrieb am Samstag, 21. Juli 2001, 05:33:13:
> - Original Message -
> From: Derick Dorner
> To: [EMAIL PROTECTED]
> Sent: Tuesday, July 17, 2001 3:11 PM
> Subject: tricky RAND() function...
> I am using MySQL 3.22, and need to know how to randomly select a record, therefore I
>can't just use the ORDER BY RAND() clause, because of my version. so i do this:
> SELECT field1,field2*0+RAND() as rand_col FROM TableName WHERE field3=5 ORDER BY
>rand_col;
> --according to paul dubois' book I have to do that field2*0+rand to override mysql's
>query optimizer...?
> This works great randomly selecting records, but it obviously also returns the
>rand_col column, which I can't have (I have to output the data (a picture) straight
>into a web browser). How can I
> either re-write this statement or "hide" that rand_col column from showing up?
> all i want to do is randomly select one column using mysql 3.22, is this that hard?
> thanks in advance!
> -derick
--
Herzlich
Werner Stuerenburg
_
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
-
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: Fw: tricky RAND() function...
>From the German php FAQ:
version >= 3.23
SELECT * FROM tabelle ORDER BY RAND() LIMIT 1
version < 3.23
//see how many rows you have
$result = @mysql_query("SELECT COUNT(*) FROM $table");
$row = mysql_fetch_row($result);
//produce random number out of that
mt_srand((double)microtime()*100);
$number = mt_rand(0,$row[0]-1);
//fetch one record randomly
$result = @mysql_query("SELECT * FROM $table LIMIT $number,1");
Derick Dorner schrieb am Samstag, 21. Juli 2001, 05:33:13:
> - Original Message -
> From: Derick Dorner
> To: [EMAIL PROTECTED]
> Sent: Tuesday, July 17, 2001 3:11 PM
> Subject: tricky RAND() function...
> I am using MySQL 3.22, and need to know how to randomly select a record, therefore I
>can't just use the ORDER BY RAND() clause, because of my version. so i do this:
> SELECT field1,field2*0+RAND() as rand_col FROM TableName WHERE field3=5 ORDER BY
>rand_col;
> --according to paul dubois' book I have to do that field2*0+rand to override mysql's
>query optimizer...?
> This works great randomly selecting records, but it obviously also returns the
>rand_col column, which I can't have (I have to output the data (a picture) straight
>into a web browser). How can I
> either re-write this statement or "hide" that rand_col column from showing up?
> all i want to do is randomly select one column using mysql 3.22, is this that hard?
> thanks in advance!
> -derick
--
Herzlich
Werner Stuerenburg
_
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
-
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 syntax
- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, July 21, 2001 12:33 PM Subject: Mysql syntax > If I wanted to asssign John Doe to system1, how would I write the statement in sql to pull the sysid from the > system table via the sysname, and then to update the user table with the sysid and John Doe? INSERT INTO users (sysid, username) SELECT sysid, 'John Doe' FROM system WHERE sysname = 'system1'; You might want to add a primary key to the users table (will come in handy when you start querying this table), and an index to system.sysname. -- Allen Grace Dark Blue Sea Pty Ltd ph +61 7 3007 fax +61 7 3007 0001 ***The opinions expressed in this email are my own and are not representative of DBS Pty Ltd.*** - 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: Questions about extremely large database support
On Thu, Jul 19, 2001 at 03:15:02PM -0500, Tom Wheeler wrote: > > Our two most important requirements for the database engine are > speed and scalability. We will be inserting probably 5,000,000 > records per day into our database Sounds doable--on reasonable hardware. > and will maintain around 2TB of data. Sounds doable--on an OS which handles large files, disks, etc. > We would like to be able to scale easily, potentially by splitting > databases up across multiple machines. Makes good sense. > My questions are: Is mySQL up to such a task? I have been using > mySQL for four years now, but have never used it in a project of > this magnitude. We'll be handling financial data in the database, > so integrity is important. There's nothing theoretically wrong with it. It's just a matter of having the right OS and enough raw CPU/IO power to do the work. > Where can I find information about very large databases with mySQL? Don't know... Some of the folks here have experience. But some of the biggest MySQL installations are probably known only to the folks at MySQL AB who do corporate support. At lunch last year, Monty mentioned someone at a good sized company (Texas Instruments, maybe?) who was putting a few billion records in a MyISAM table and not having any trouble. > Is there a really efficient way to implement a function like > indexseek() in FoxPro? This function will simply check an index to > tell you if a record with that key exists. Sort of like "select > count(id) from Foo where id=1" except that it doesn't actually fetch > the field value and it just checks the index file, not the data > file. That's been covered separately, I think. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW MySQL 3.23.29: up 34 days, processed 268,958,054 queries (89/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: MySQL Timing out
On Wed, Jul 18, 2001 at 10:12:03PM -0400, Chris Anderson wrote: > > I have an include file that includes my mysql connect > information. Its sole purpose is to login to mysql. Strangely on my > Win9x development system with apache, sometimes, almost randomly, > the connection will time out. Then if I press refresh it works > prefectly. I am only connecting once per page and am closing my > connections when done. Does this sound like a MySQL or Apache > onfiguration problem? Or is there something I'm missing. Thanks What happens if you try it outside of Apache? -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW MySQL 3.23.29: up 34 days, processed 268,953,746 queries (89/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: Mysqlhotcopy regexp problem
On Fri, Jul 20, 2001 at 01:25:22AM -0700, Jeremy Zawodny wrote:
> >
> > If I specify just one database, I still get the "deprecated"
> > warning, but the hotcopy works...
>
> There are two bugs.
>
> (1) The "depricated" message. I'm submitting a patch for that.
>
> (2) The fact that if you specify no databases but do specify a
> regexp, it doesn't do what you expect. It is unclear to me,
> based on the docs for mysqlhotcopy, what it is supposed to do in
> that case. But I'm leaning toward thinking that your
> expectations are right. I'll put that in the patch as well.
The patch against scripts/mysqlhotcopy.sh from the latest bitkeeper
source tree is below my signature. I tested it using your example and
it seemed to solve the problems for me.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW
MySQL 3.23.29: up 34 days, processed 268,910,584 queries (89/sec. avg)
--- mysqlhotcopy.sh.origFri Jul 20 00:51:17 2001
+++ mysqlhotcopy.sh Fri Jul 20 02:11:06 2001
@@ -223,18 +223,27 @@
my $db = $rdb->{src};
eval { $dbh->do( "use $db" ); };
die "Database '$db' not accessible: $@" if ( $@ );
-my @dbh_tables = $dbh->func( '_ListTables' );
+my @dbh_tables = $dbh->tables();
## generate regex for tables/files
-my $t_regex = $rdb->{t_regex};## assign temporary regex
-my $negated = $t_regex =~ tr/~//d;## remove and count negation operator: we
don't allow ~ in table names
-$t_regex = qr/$t_regex/; ## make regex string from user regex
-
-## filter (out) tables specified in t_regex
-print "Filtering tables with '$t_regex'\n" if $opt{debug};
-@dbh_tables = ( $negated
- ? grep { $_ !~ $t_regex } @dbh_tables
- : grep { $_ =~ $t_regex } @dbh_tables );
+my $t_regex;
+my $negated;
+if ($rdb->{t_regex}) {
+$t_regex = $rdb->{t_regex};## assign temporary regex
+$negated = $t_regex =~ tr/~//d;## remove and count
+ ## negation operator: we
+ ## don't allow ~ in table
+ ## names
+
+$t_regex = qr/$t_regex/; ## make regex string from
+ ## user regex
+
+## filter (out) tables specified in t_regex
+print "Filtering tables with '$t_regex'\n" if $opt{debug};
+@dbh_tables = ( $negated
+? grep { $_ !~ $t_regex } @dbh_tables
+: grep { $_ =~ $t_regex } @dbh_tables );
+}
## get list of files to copy
my $db_dir = "$datadir/$db";
@@ -249,10 +258,18 @@
closedir( DBDIR );
## filter (out) files specified in t_regex
-my @db_files = ( $negated
- ? grep { $db_files{$_} !~ $t_regex } keys %db_files
- : grep { $db_files{$_} =~ $t_regex } keys %db_files );
+my @db_files;
+if ($rdb->{t_regex}) {
+@db_files = ($negated
+ ? grep { $db_files{$_} !~ $t_regex } keys %db_files
+ : grep { $db_files{$_} =~ $t_regex } keys %db_files );
+}
+else {
+@db_files = keys %db_files;
+}
+
@db_files = sort @db_files;
+
my @index_files=();
## remove indices unless we're told to keep them
@@ -808,4 +825,8 @@
Ask Bjoern Hansen - Cleanup code to fix a few bugs and enable -w again.
Emil S. Hansen - Added resetslave and resetmaster.
+
+Jeremy D. Zawodny - Removed depricated DBI calls. Fixed bug which
+resulted in nothing being copied when a regexp was specified but no
+database name(s).
-
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
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can mysql.users table props be modified?
On Mon, Jul 16, 2001 at 03:59:50PM -0700, Jose de Leon wrote: > Hello All, > > I have need to modify users table in mysql database. I wish to > expand the user column from 16 chars to 128 chars. Will this cause > problems with MySQL server engine and will it recognize and use my > changes? What is maximum size allowed and also can I safely expand > width of database name field? > > Can I make these changes just to the databse tables or must I > recompile MySQL for new sizes? Good question... While I haven't *tried* it, I glanced thru the code a bit and didn't see an red flags. So it'll probably work. If not, I'm sure that someone can point us at what needs to change. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW MySQL 3.23.29: up 34 days, processed 268,899,972 queries (89/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
Problem installing MYSQL
Hi. I need to install mysql on Solrais (5.5.1 Generic sun4m sparc SUNW,SPARCstation-20), but config procedure stop with this message: >checking for restartable system calls... configure: error: can not run test program while cross compiling Looking in config.log i see some fatal errors: ... ld: fatal: library -ldir: not found ... ld: fatal: library -lnsl_r: not found ... ld: fatal: library -lbind: not found ... ld: fatal: library -lcompat: not found ... Can anybody help me? Fabrizio - 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
Fw: tricky RAND() function...
- Original Message - From: Derick Dorner To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 3:11 PM Subject: tricky RAND() function... I am using MySQL 3.22, and need to know how to randomly select a record, therefore I can't just use the ORDER BY RAND() clause, because of my version. so i do this: SELECT field1,field2*0+RAND() as rand_col FROM TableName WHERE field3=5 ORDER BY rand_col; --according to paul dubois' book I have to do that field2*0+rand to override mysql's query optimizer...? This works great randomly selecting records, but it obviously also returns the rand_col column, which I can't have (I have to output the data (a picture) straight into a web browser). How can I either re-write this statement or "hide" that rand_col column from showing up? all i want to do is randomly select one column using mysql 3.22, is this that hard? thanks in advance! -derick
Re: installing source code on corel linux
What directory are you in when you 'unpack' the source distribution ? What directory are you in when you issue the 'make' command ? What directory are you in when you issue the 'scripts/mysql_install_db' command ? (I usually go to the 'installed' directory "/usr/local/mysql" and type "./bin/mysql_install_db", AFTER creating the "var" subdirectory) I would suspect that if the system is complaining that no "Makefile" exists, that you either have a bad distribution (your should be using 3.23.39 anyway), you have unpacked it somewhere other than where you are now, or your Linux installation is still missing a 'development' type package - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 8:25 AM Subject: installing source code on corel linux > I am a newbie to linux and mysql and would like some advice re > installing mysql source code on corel linux. > > I have successfully installed a binary version of Mysql (now > deleted) but need to compile and install a source version, as I want > to next install PHP.which I understand needs the source code > distribuion of Mysql. > > The mysql version is 3-23-38 > I have followed the instructions from the Mysql documentation as > follows- > shell> groupadd mysql > shell> useradd -g mysql mysql > shell> gunzip < mysql-VERSION.tar.gz | tar -xvf - > shell> cd mysql-VERSION > shell> ./configure --prefix=/usr/local/mysql > shell> make > shell> make install > shell> scripts/mysql_install_db > shell> chown -R mysql /usr/local/mysql > shell> chgrp -R mysql /usr/local/mysql > shell> /usr/local/mysql/bin/safe_mysqld --ser=mysql & > > Everything is OK up to and including line 5, the config command. > > The make command results in an error message which is because > the makefile file does not exist. > > I have installed autoconf and automake and rerun the config file OK > which recognises autoconf and automake > > I then run automake ( instead of the make command) and > processing takes place for a few seconds without any messages. > > However no mysql/bin folders or files have been created that I > would expect from the compile/install process. > > Can anyone tell me what I have missed pls. > > Cheers Barrie Lees > > > > - > 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: Left Join very sloooowwww..
Hi. On Fri, Jul 20, 2001 at 11:22:56PM +0200, [EMAIL PROTECTED] wrote: > > > SELECTbrand.brandname, > > SUM((productorders.quantity)*(productorders.price)) AS turnover > > FROM orders > > LEFT JOIN productorders ON productorders.orderid = orders.id > > LEFT JOIN articles ON productorders.ordernr = articles.ordernr > > LEFT JOIN products ON products.id = articles.id > > LEFT JOIN brand ON products.brand = brand.id > > WHERE productorders.date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), >'%Y-%m-%d') > > AND productorders.date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 0 DAY), >'%Y-%m-%d') > > AND orders.status != 2 > > GROUP BY brand.brandname > > ORDER BY turnover asc > > > > Some comments: > > > > - Where is the table "articles" coming from? Did you mean > > "perfect_articles"? If not, you have no restriction on this table. > > Yes, sorry, missed that one.. All the tables I originally made the > query for have the prefix perfect_ in front.. (perfect_articles, > perfect_brand, perfect_productsorders.. I removed this to the make > it shorter and not make the query look more complicated than it > is.. (To answer your question: I missed the one perfect_ you saw, > this had to be removed) > > > - Where is the table "orders"? I assume you meant "productorders"? > No, I do a select from orders... Oh. Sorry. A shot to quick after the issue with perfect_articles. ;-) [...]> > > - Do you really need LEFT JOINs instead of normal JOINs? At least the > > effect of the LEFT JOIN with "productorders" is lost by using > > "orders.status != 2" in the WHERE clause > > > > - You know that LEFT JOINs are usually slower than normal joins and > > therefore should only be used when needed? (Additonally, the result > > will differ in many cases) > > Sorry, tell me I am dumb... No need to be sorry. Everyone once had to begin with. [...] > Then I got my hands on examples which used a left join examples.. I > have never tried a normal join (sigh..), but thinking of it, what is > the difference between an left join and a normal join..?? With a "normal join" (I don't know the technical term), I mean something like (both variants are semantically equivalent, i.e. do the same) SELECT * FROM orders, productorders WHERE productorders.orderid = orders.id SELECT * FROM orders JOIN productorders WHERE productorders.orderid = orders.id Additionally to what a normal join returns, a LEFT JOIN also returns each row of the left table, even if there is no match in the right table, with NULL filled in as value. Or in other words: while a normal join only returns matches, a LEFT JOIN returns all rows of the left table either where the rows are either paired with the matches, or with NULL values, if no matches can be found. Therefore a LEFT JOIN allways has to inspect all rows, whereas a normal join can optimize which rows to look at. [...] > I use PHP to format my output, so maybe a left join has something to > do with the order the columns are displayed which is no use for > me..?? No. The order is determined by the ORDER BY clause. > > It doesn't matter if you use a ON clause instead of a WHERE clause > > with normal JOINS. The type of the JOIN does matter! And you cannot > > write LEFT JOINs with the constraint in the WHERE clause. I have to correct me a bit. As far as I could see in the manual, there is no variant of the normal join which supports an ON clause, so my statement should read: "It wouldn't matter ..." > I understand what you say, only the last sentance I think I should > go read the manual to understand it.. (you cannot write left joins > with the contraints?? in the where clause..) With contraints I simply meant the linking condition, like "productorders.orderid = orders.id". What I meant was, that if one really wants the result from a LEFT JOIN (i.e. the NULL values, too), it doesn't make sense to write the constraint in the WHERE clause, because it would eliminate all NULL values (because '=' is never true for a NULL value). [...] > These command (explain select...) are new to mee, but it looks like > some useful information and that I can optimize a lot by adding some > extra indexes... Yes, but with LEFT JOINs, there is not much room for MySQL to use the indexes (as the whole left table has to be scanned). So, as I said, get rid of the LEFT JOINs, if you don't really need them (if you are not sure, I bet you don't need them). > Original Query: > > SELECT perfect_brand.brandname, > >Sum((perfect_customer_productorders.quantity)*(perfect_customer_productorders.price)) >AS omzet > FROM perfect_customer_orders > LEFT JOIN perfect_customer_productorders on >perfect_customer_productorders.orderid = perfect_customer_orders.id > LEFT JOIN perfect_articles on perfect_customer_productorders.ordernr = >perfect_articles.ordernr > LEFT JOIN perfect_products on
About safe_mysqld - another problem
Hi, I'm a beginner of MySql. I've just installed the lastest ver. MySql under RH7.1 Linux. First, I do have runned the "mysql_install_db" script. When I tried to up the MySql server by running safe_mysqld script, it also failed and now I've got an error message logged as follows: 010720 22:39:06 mysqld started 010720 22:39:06 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010720 22:39:06 mysqld ended I want to ask if anybody help me: 1. What should I do in order to resolve this problem??? Pls advise. Thank you for your attention. Regards. Peter Choi == ·s®ö§K¶O¹q¤l«H½c http://sinamail.sina.com.hk - 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: FreeBSD issues
Michael, MySQL worked beatifully for us on a vanilla FreeBSD system -- I simply installed the binary (precompiled) package from the FreeBSD ports collection. I recommend you get the binary rather than playing with the configuration yourself ... - Tom Haapanen -- Software Metrics/Equitrac Corp. Advanced Printing Solutions -- http://www.metrics.com/ -Original Message- From: Institute For Social Ecology [mailto:[EMAIL PROTECTED]] Sent: 18 July 2001 18:33 To: Ken Menzel; [EMAIL PROTECTED] Subject: RE: FreeBSD issues Ken, Thank you for your very helpful notes. We took your advice and updated to FreeBSD 4.3 and the last version of MYSQL. We are still experiencing problems with MYSQL, though. We installed it fresh, with no freeBSD tweaks. I was not able to locate the email you referred to regarding FreeBSD installs. If you have any pointers on tweaking a freeBSD install, it would be very helpful. Regards, Michael Caplan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql syntax
I have been trying to figure out how to do this, and I am sure that I am just overlooking somthing small. Suppose I create two tables as follows: CREATE TABLE system ( sysid INT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY, sysname VARCHAR(12), os VARCHAR(10), cpumhz INT ); CREATE TABLE user ( sysid INT REFERENCES system, username VARCHAR(30) ); Also suppose that I populated the system table with the following information: +---+---+++ | sysid | sysname | os | cpumhz | +---+---+---+-+ | 1 | system1 | Linux | 1000 | | 2 | system2 | Unix |300| +---+---+-++ If I wanted to asssign John Doe to system1, how would I write the statement in sql to pull the sysid from the system table via the sysname, and then to update the user table with the sysid and John Doe? Thanks, Phil
Re: mysql.sock
Hi All, I am a new user of mysql. I too installed mysql on my linux box. but i get the same message. what is this message and how do we solve it. I had the client and server RPM and installed them. I once managed to start the server with mysqld. But now the server also doesnt start saying host.frm error. Please do let me know the details of linux installation. I need mysql very badly and I am seeking help from all of you. Thanks Ajay PS - Micheal , If you get to know how this problem can be solved do let me know. I shall do the same if I get the solution --- Michael Johnson <[EMAIL PROTECTED]> wrote: > I have been trying to install MySQL on my Cobalt > RAQ3 and I seem to have > lost my mysql.sock file. I get the following error > message when I tray to > carryout any mysql activity. > > ERROR 2002: Can't connect to local MySQL server > through socket > '/var/lib/mysql/mysql.sock' (111) > > Pls advise > > > > Michael Johnson > Director > BPEnet > Humphrey Consulting Limited > 13 Austin Frais > London EC2N 2JX > Tel +44(0)1323 438975 > Fax +44(0)1323 738355 > email [EMAIL PROTECTED] > > Also @ Carmichael House > 60 Lower Baggott Street > Dublin 2 > Tel +353(0)1 602 4739 > > Also @ 26 Boulevard Royal > L-2449 > Luxembourg > Tel +352 22 99 99 55 07 > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.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
A Plroblem Connecting
Hello I have mysqlgui-1.7.5-1-linux-static and semi-static, mysql-3.23.39, linux slackware 7.2. When I run the mysqlgui executable(in static & semi-static), I enter password and the gui loads but says "Can't connect to local MySQL server through socket" (111). The password is correct and I can connect through mysql or mysqladmin, and mysqlgui works on ms windows. I will like to get it to work under Linux. Thanks.
Re: Is last_insert_id reliable?
This is precisely what I did, only I left out the 'as max_hit_id' part, and jumped right to pixels. Works quickly and neatly. Thanks very much. Walter On 7/20/01 6:35 PM, "Jonothan Farr" <[EMAIL PROTECTED]> wrote: > LAST_INSERT_ID is the value of the auto increment field in the table that was > last inserted into. You probably want: > > $sql4 = "SELECT MAX(hits.hit_id) as max_hit_id > as pixels > from hits;"; > > --jfarr > > > - > 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: Is last_insert_id reliable?
Yes, you are correct in assuming that, but ONLY if you use MyISAM tables.
BDB and ISAM tables reuse number, kind of.
Ex:
1
2
3
4
5
If you delete 5, the next record you insert will have an ID of 5. If you
delete 4 instead of 5, the next one inserted will have an ID of 6. So, BDB
and ISAM tables do kind of a MAX(id)+1, whereas MyISAM tables actually keep
track of the last used auto increment number.
Hope that helps!
j- k-
On Friday 20 July 2001 17:11, you wrote:
> That I'm trying to do here is get the VALUE of hit_id from the last row of
> hits. It is an auto-increment number, but it is much higher than the count
> of the rows, because people go through and delete their hits from time to
> time. Am I correct in assuming that auto-numbers are never re-used?
>
> Walter
>
> On 7/20/01 7:06 PM, "Joshua J. Kugler" <[EMAIL PROTECTED]> wrote:
> > Read the manual again on last_insert_id(). That function only gives you
> > the unique id of the last insert *for that thread* If you connect, don't
> > do any inserts, and select last_insert_id, there is no guarentee what you
> > will get. If you are trying to find the number of rows, it would be much
> > easier to do this:
> >
> > SELECT COUNT(*) AS pixels FROM hits
> >
> > Try that. From the looks of what you are doing, that will be accurate
> > every time. COUNT(*) knows how many rows there are in a table.
> > last_insert_id() has nothing to do with how many rows are in a table.
> >
> > If you are going to delete old rows one day, then you might want to do
> > this:
> >
> > SELECT MAX(id) as pixels FROM hits
> >
> > Hope that helps.
> >
> > On Friday 20 July 2001 13:31, Walter Lee Davis wrote:
> >> Currently, I have been counting the rows in PHP in order to get the
> >> value of the last hit_id in the database like this:
> >>
> >> $sql4 = "SELECT hits.hit_id from hits";
> >>
> >> $result = mysql_query ($sql4, $connection)
> >> or die("error #" . mysql_errno() . ": " . mysql_error());
> >> $last = mysql_num_rows($result) - 1;
> >> $go = mysql_data_seek($result, $last);
> >> $row = mysql_fetch_object($result);
> >> $pixels = number_format($row->hit_id);
> >>
> >> Which is really expensive on my very tall hits table. I hit upon doing
> >> it in MySQL, which is much, much faster:
> >>
> >> $sql4 = "SELECT hits.hit_id
> >> as pixels
> >> from hits
> >> WHERE hit_id=LAST_INSERT_ID();";
> >>
> >> $result = mysql_query ($sql4, $connection)
> >> or die("error #" . mysql_errno() . ": " . mysql_error());
> >> $row = mysql_fetch_object($result);
> >> $pixels = number_format($row->pixels);
> >>
> >> It's staggering the difference in speed. But if I sit there and hit
> >> refresh on the browser, I get wildly different values for $pixels. It
> >> jumps around the actual number by +-10 or 12. The first method is
> >> precisely the same every time (unless a hit is recorded in the interim).
> >> Am I doing something wrong here? Can I not count on MySQL to know how
> >> many rows it has recorded in a table? Any idea why that select statement
> >> would select a different row each time?
--
Joshua Kugler, Information Services Director
Associated Students of the University of Alaska Fairbanks
[EMAIL PROTECTED], 907-474-7601
-
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: Is last_insert_id reliable?
That I'm trying to do here is get the VALUE of hit_id from the last row of
hits. It is an auto-increment number, but it is much higher than the count
of the rows, because people go through and delete their hits from time to
time. Am I correct in assuming that auto-numbers are never re-used?
Walter
On 7/20/01 7:06 PM, "Joshua J. Kugler" <[EMAIL PROTECTED]> wrote:
> Read the manual again on last_insert_id(). That function only gives you the
> unique id of the last insert *for that thread* If you connect, don't do any
> inserts, and select last_insert_id, there is no guarentee what you will get.
> If you are trying to find the number of rows, it would be much easier to do
> this:
>
> SELECT COUNT(*) AS pixels FROM hits
>
> Try that. From the looks of what you are doing, that will be accurate every
> time. COUNT(*) knows how many rows there are in a table. last_insert_id()
> has nothing to do with how many rows are in a table.
>
> If you are going to delete old rows one day, then you might want to do this:
>
> SELECT MAX(id) as pixels FROM hits
>
> Hope that helps.
>
> On Friday 20 July 2001 13:31, Walter Lee Davis wrote:
>> Currently, I have been counting the rows in PHP in order to get the value
>> of the last hit_id in the database like this:
>>
>> $sql4 = "SELECT hits.hit_id from hits";
>>
>> $result = mysql_query ($sql4, $connection)
>> or die("error #" . mysql_errno() . ": " . mysql_error());
>> $last = mysql_num_rows($result) - 1;
>> $go = mysql_data_seek($result, $last);
>> $row = mysql_fetch_object($result);
>> $pixels = number_format($row->hit_id);
>>
>> Which is really expensive on my very tall hits table. I hit upon doing it
>> in MySQL, which is much, much faster:
>>
>> $sql4 = "SELECT hits.hit_id
>> as pixels
>> from hits
>> WHERE hit_id=LAST_INSERT_ID();";
>>
>> $result = mysql_query ($sql4, $connection)
>> or die("error #" . mysql_errno() . ": " . mysql_error());
>> $row = mysql_fetch_object($result);
>> $pixels = number_format($row->pixels);
>>
>> It's staggering the difference in speed. But if I sit there and hit refresh
>> on the browser, I get wildly different values for $pixels. It jumps around
>> the actual number by +-10 or 12. The first method is precisely the same
>> every time (unless a hit is recorded in the interim). Am I doing something
>> wrong here? Can I not count on MySQL to know how many rows it has recorded
>> in a table? Any idea why that select statement would select a different row
>> each time?
-
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 Installing source code on linux
Hi I am a newbie to linux and mysql and would like some advice re installing mysql source code on corel linux. I have successfully installed a binary version of Mysql (now deleted) but need to compile and install a source version, as I want to next install PHP.which I understand needs the source code distribuion of Mysql. The mysql version is 3-23-38 I have followed the instructions from the Mysql documentation as follows- shell> groupadd mysql shell> useradd -g mysql mysql shell> gunzip < mysql-VERSION.tar.gz | tar -xvf - shell> cd mysql-VERSION shell> ./configure --prefix=/usr/local/mysql shell> make shell> make install shell> scripts/mysql_install_db shell> chown -R mysql /usr/local/mysql shell> chgrp -R mysql /usr/local/mysql shell> /usr/local/mysql/bin/safe_mysqld --ser=mysql & Everything is OK up to and including line 5, the config command. The make command results in an error message which is because the makefile file does not exist. I have installed autoconf and automake and rerun the config file OK which recognises autoconf and automake I then run automake ( instead of the make command) and processing takes place for a few seconds without any messages. However no mysql/bin folders or files have been created that I would expect from the compile/install process. Can anyone tell me what I have missed pls. Cheers Barrie Lees - 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
Another Newbie Question
The SQL book I am using has this: mysql> select avg(distinct prod_price) as avg_price -> from Products -> where vend_id = 'dll01' -> ; and responds thus: ERROR 1064: You have an error in your SQL syntax near 'distinct prod_price) as avg_price from Products where vend_id = 'dll01'' at line 1 How do I do this, if I can? -- Bob Rea Fear of Hell is pernicious; So is fear of Heaven. [EMAIL PROTECTED] http://www.sirius.com/~rear - 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: Is last_insert_id reliable?
Adding, For that thread. selecting LAST_INSERT_ID() for a pseudo-foreign keys system is reliable. The only case I can possibly think of, Of this being a problem is when you're dealing with a persistent connection to the database which is shared among multiple applications. Jonothan Farr wrote: > > LAST_INSERT_ID is the value of the auto increment field in the table that was last >inserted into. You probably want: > > $sql4 = "SELECT MAX(hits.hit_id) as max_hit_id > as pixels > from hits;"; > > --jfarr > > - > 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: Randomize column order
There are only 8,000 rows. Unfortunately, I was really hoping for some sort of function within MySQL to do this. I'm not using PHP, but rather Perl. I could pretty much do the same thing in Perl, I guess. Is there any elegant solution or alternative within MySQL? On Friday, July 20, 2001, at 04:43 PM, Werner Stuerenburg wrote: > How many rows do you have? I'd do it in php along those lines: > read the rows from the old table in 3 arrays. randomize one or > all of them (built in function in version 4, else do it > yourself), then populate new table from those arrays. > > Sie schrieben am Freitag, 20. Juli 2001, 23:56:38: > >> I'm sure I'm just missing something basic, but here goes... > >> I need to create a table, populated with data, from an existing table. >> Easy enough: >> "create table TEST select * from OLD_DATA" > >> Most cool. Now, let's say OLD_DATA has three columns: A, B, & C. I >> want to create new table TEST, with all 3 columns from OLD_DATA, >> but.I want to totally randomize the order of column B. For >> example, >> I want to go from: > >> FIRSTNAME LASTNAMEPID >> bob jones 1 >> marysmith 2 >> maddog brown 3 > >> To this: > >> FIRSTNAME LASTNAMEPID >> bob smith 1 >> marybrown 2 >> maddog jones 3 > >> I guess what I'm looking for is something similar to this imaginary >> command: > >> CREATE TABLE TEST SELECT FIRSTNAME, PID, (LASTNAME ORDER BY RAND()) >> FROM >> OLD_DATA; > >> Any help is appreciated. > > >> - >> 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 > > > > -- > Herzlich > Werner Stuerenburg > > _ > ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen > Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 > http://pferdezeitung.de > > - 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 Statement with AS Keyword
select table1.colOne as one, table2.colOne as two from table1, table2 where some condition... Later fetch the values with names one and two. Sie schrieben am Freitag, 20. Juli 2001, 23:55:45: > I have two tables and both tables have a field name thats the same. I > need to display > both those field names back to the browser using PHP. I have used php to > display all > the other fields but I can't display these fields, because of the > identical name. I know you can us the Select statement with the keyword > AS to rename the fields to something else, but I can't get the syntax > right. I hope someone can send me an example, my email is > [EMAIL PROTECTED] > -- > # > Kory Wheatley > Academic Computing Analyst Sr. > Phone 282-3874 > # > Everything must point to him. > - > 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 > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Randomize column order
How many rows do you have? I'd do it in php along those lines: read the rows from the old table in 3 arrays. randomize one or all of them (built in function in version 4, else do it yourself), then populate new table from those arrays. Sie schrieben am Freitag, 20. Juli 2001, 23:56:38: > I'm sure I'm just missing something basic, but here goes... > I need to create a table, populated with data, from an existing table. > Easy enough: > "create table TEST select * from OLD_DATA" > Most cool. Now, let's say OLD_DATA has three columns: A, B, & C. I > want to create new table TEST, with all 3 columns from OLD_DATA, > but.I want to totally randomize the order of column B. For example, > I want to go from: > FIRSTNAME LASTNAMEPID > bob jones 1 > marysmith 2 > maddog brown 3 > To this: > FIRSTNAME LASTNAMEPID > bob smith 1 > marybrown 2 > maddog jones 3 > I guess what I'm looking for is something similar to this imaginary > command: > CREATE TABLE TEST SELECT FIRSTNAME, PID, (LASTNAME ORDER BY RAND()) FROM > OLD_DATA; > Any help is appreciated. > - > 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 -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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 InnoDb table type - Urgent help required
Hi, I am using mySQL on Windows 2000. I need to use the InnoDb table type to get transaction support. I had initially installed mysql and was using the ISAM table type and everything was working fine. My NT service was configured to run the mysqld-nt.exe. For using the InnoDb type I un-installed the service and re-installed it with mysqld-max-nt.exe. I also modified my.ini to include the innodb variables as follows: innodb_data_file_path=ibdata1:200M innodb_data_home_dir=f:/mysql/innodb/ibdata innodb_log_group_home_dir=f:/mysql/innodb/iblogs The directories specified in the above variables exist. However when I try to run the service I get the error "Could not start the MySQL service on Local Computer... Error 1067: The process terminated unexpectedly". No error is logged in the mysql.err file in the data directory. When I remove the above innodb specific variables the service runs fine. I also tried un-installing mysql completely and re-installing it but no luck. Can somebody please help me? Thanks Rashmi - 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: Is last_insert_id reliable?
> $sql4 = "SELECT hits.hit_id as pixels from hits
> WHERE hit_id=LAST_INSERT_ID();";
>
> $result = mysql_query ($sql4, $connection)
> or die("error #" . mysql_errno() . ": " . mysql_error());
> $row = mysql_fetch_object($result);
> $pixels = number_format($row->pixels);
>
> It's staggering the difference in speed. But if I sit there and
> hit refresh
> on the browser, I get wildly different values for $pixels. It jumps around
> the actual number by +-10 or 12. The first method is precisely the same
> every time (unless a hit is recorded in the interim). Am I doing something
> wrong here? Can I not count on MySQL to know how many rows it has recorded
> in a table? Any idea why that select statement would select a
> different row each time?
>From http://www.mysql.com/doc/M/i/Miscellaneous_functions.html:
The last ID that was generated is maintained in the server on a
per-connection basis.
Since LAST_INSERT_ID() works on a per-connection basis, and since you are
most likely using persistent connections, each reload of your web browser
returns a different instance of the http server and therefore a different
persistent connection. The best way to do it would probably be to update the
last id in the table right after inserting the row (update othertable set
row = last_insert_id() where yadda yadda yadda).
-
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: Rare crash
On Friday 20 July 2001 14:00, Steven Roussey wrote: > > Locked table should not crash a server !! > > Oh.. I only meant in the cascade sense: Locked Table -> Queries waiting -> > Threads fill up -> Run out of memory -> Crash One thing to remember about row vs table level locking is that any query that locks the table for long enough to cause trouble with MyISAM is also likely to lock enough rows to cause trouble with a row level locking handler. In other words, row level locking will not fix the problem of poorly optimized query. A lot of times MySQL gets unfair blame for failing to perform due to table locking simply because it is an easy escape route to explain to the boss/co-workers why the site went down. The truth of the matter in many cases ( not in all, of course) is that the same query mix would have caused a similar combination of problems with page or row locks. If you guys are running into problems with lock contention or poor performance for some other reason, I would recommmend that you spend some time investigating the matter and then, if you cannot understand/fix the problem, submit a test case/benchmark that will demonstrate it. What happens is that our hacker's pride gets hurt when somebody says MySQL is not good enough for their system and presents some code to prove it. Either or both of the two things are going to happen: * We will tell you how you can optimize your system * We will fix MySQL so that your system will do better Do not underestimate the power of this. It does miracles - from what I have observed in the last year and half that I worked with MySQL AB, this has been one of the most significant drivers of development. Hard repeatable evidence of performance problems really gets under our skin and we cannot sleep until we get it out, especially Monty. We get a kick out of having our users tell us that after our suggested optimization, the system runs 10-100 times faster and now can handle the load. We just ike to make things better. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - 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: Is last_insert_id reliable?
> Currently, I have been counting the rows in PHP in order to get the value of > the last hit_id in the database like this: You have mysql_insert_id() in php to get the last autoincrement value. This is _not_necessarily_ the same as the number of rows. > It's staggering the difference in speed. But if I sit there and hit refresh > on the browser, I get wildly different values for $pixels. It jumps around > the actual number by +-10 or 12. The first method is precisely the same > every time (unless a hit is recorded in the interim). Am I doing something > wrong here? Well, I imagine that the last inserted id (not number of rows) will be given after you have actually inserted a row. I never tried to ask twice. Did you ever find that a variable in php shows a totally obscure value? If so, you may find that the value was not defined at all and point to some random memory block which contains some value from another process which you see. Maybe this is the case with mysql, too. So you better be sure to look for what you refer to. I made a search on LAST_INSERT_ID but it returned 0 results in mysql.com. Funny. > Can I not count on MySQL to know how many rows it has recorded > in a table? Any idea why that select statement would select a different row > each time? This is a different question. You should ask SELECT count(*) as num FROM hits and then look at the value of num to get an answer to that question. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: Is last_insert_id reliable?
Read the manual again on last_insert_id(). That function only gives you the
unique id of the last insert *for that thread* If you connect, don't do any
inserts, and select last_insert_id, there is no guarentee what you will get.
If you are trying to find the number of rows, it would be much easier to do
this:
SELECT COUNT(*) AS pixels FROM hits
Try that. From the looks of what you are doing, that will be accurate every
time. COUNT(*) knows how many rows there are in a table. last_insert_id()
has nothing to do with how many rows are in a table.
If you are going to delete old rows one day, then you might want to do this:
SELECT MAX(id) as pixels FROM hits
Hope that helps.
On Friday 20 July 2001 13:31, Walter Lee Davis wrote:
> Currently, I have been counting the rows in PHP in order to get the value
> of the last hit_id in the database like this:
>
> $sql4 = "SELECT hits.hit_id from hits";
>
> $result = mysql_query ($sql4, $connection)
> or die("error #" . mysql_errno() . ": " . mysql_error());
> $last = mysql_num_rows($result) - 1;
> $go = mysql_data_seek($result, $last);
> $row = mysql_fetch_object($result);
> $pixels = number_format($row->hit_id);
>
> Which is really expensive on my very tall hits table. I hit upon doing it
> in MySQL, which is much, much faster:
>
> $sql4 = "SELECT hits.hit_id
> as pixels
> from hits
> WHERE hit_id=LAST_INSERT_ID();";
>
> $result = mysql_query ($sql4, $connection)
> or die("error #" . mysql_errno() . ": " . mysql_error());
> $row = mysql_fetch_object($result);
> $pixels = number_format($row->pixels);
>
> It's staggering the difference in speed. But if I sit there and hit refresh
> on the browser, I get wildly different values for $pixels. It jumps around
> the actual number by +-10 or 12. The first method is precisely the same
> every time (unless a hit is recorded in the interim). Am I doing something
> wrong here? Can I not count on MySQL to know how many rows it has recorded
> in a table? Any idea why that select statement would select a different row
> each time?
--
Joshua Kugler, Information Services Director
Associated Students of the University of Alaska Fairbanks
[EMAIL PROTECTED], 907-474-7601
-
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: Is last_insert_id reliable?
LAST_INSERT_ID is the value of the auto increment field in the table that was last inserted into. You probably want: $sql4 = "SELECT MAX(hits.hit_id) as max_hit_id as pixels from hits;"; --jfarr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql.sock
I have been trying to install MySQL on my Cobalt RAQ3 and I seem to have lost my mysql.sock file. I get the following error message when I tray to carryout any mysql activity. ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) Pls advise Michael Johnson Director BPEnet Humphrey Consulting Limited 13 Austin Frais London EC2N 2JX Tel +44(0)1323 438975 Fax +44(0)1323 738355 email [EMAIL PROTECTED] Also @ Carmichael House 60 Lower Baggott Street Dublin 2 Tel +353(0)1 602 4739 Also @ 26 Boulevard Royal L-2449 Luxembourg Tel +352 22 99 99 55 07 - 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.org
On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: > MySQL AB and NuSphere had a meeting over the phone, we exchanged > information and opinions and NuSphere will propose times for the > next meeting. Since these issues are between two private companies, please take your discussions off this list. It is none of our business. By all means, tell us the result. -- John Birrell - [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: auto_increment, is this a bug?? Where to report it?
OK, I found some... >From the manual at "MyISAM tables"... "Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster (at least 10 %) and old numbers will not be reused as with the old ISAM. Note that when a AUTO_INCREMENT is defined on the end of a multi-part-key the old behavior is still present." Well...and what about the last phrase "Note that..."? I didn't understand it. Maybe I'm having a bad design problem since I'm using the auto_increment feature to define the number of the editions of my bulletins. I'm studying other ways to do it. William N. Zanatta - 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: auto_increment, is this a bug?? Where to report it?
> The problem is...if I delete the third entry (Edicao = 3), the next > entry inserted should be 3, right?! Well it is not! It is 4. Well, this may be debated. I suffered a lot from the behaviour you want to have. I considered it bad design ... An autoincrement field should not serve for counting or numbering but for making sure that you have a unique key - no more and no less - without having to think about it. If you count on this - that every record will have its own number no matter what happens - then the number of a deleted record _must_ be discarded. Of course, you can live with both implementations if you know what happens and what you're doing. Lastly, the trouble I had was nothing else than bad design. Relying on the unique key, populated other tables with this foreign key. When the row was deleted, I didn't care about deleting the dependent rows in the other tables and files connected with them (pictures). As a result, there was a good chance that somebody had the wrong picture in their classifieds: big embarrassment! Took me some time to find out about this scenario. Of course, the bad design was that I didn't care about deleting all dependencies including the files. But otherwise, I wouldn't have run into problems at all and only wasted disk space which isn't that much of a value these days. If you care to look at the manual, you will find explanations about this change in design. I don't remember the details now, but I know that I have seen them. Use the search box and you will find it fast. This explains why the behavior changes with the versions. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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 Statement with AS Keyword
I have two tables and both tables have a field name thats the same. I need to display both those field names back to the browser using PHP. I have used php to display all the other fields but I can't display these fields, because of the identical name. I know you can us the Select statement with the keyword AS to rename the fields to something else, but I can't get the syntax right. I hope someone can send me an example, my email is [EMAIL PROTECTED] -- # Kory Wheatley Academic Computing Analyst Sr. Phone 282-3874 # Everything must point to him. - 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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Randomize column order
I'm sure I'm just missing something basic, but here goes... I need to create a table, populated with data, from an existing table. Easy enough: "create table TEST select * from OLD_DATA" Most cool. Now, let's say OLD_DATA has three columns: A, B, & C. I want to create new table TEST, with all 3 columns from OLD_DATA, but.I want to totally randomize the order of column B. For example, I want to go from: FIRSTNAME LASTNAMEPID bob jones 1 marysmith 2 maddog brown 3 To this: FIRSTNAME LASTNAMEPID bob smith 1 marybrown 2 maddog jones 3 I guess what I'm looking for is something similar to this imaginary command: CREATE TABLE TEST SELECT FIRSTNAME, PID, (LASTNAME ORDER BY RAND()) FROM OLD_DATA; Any help is appreciated. - 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: auto_increment, is this a bug?? Where to report it?
OK guys, Thank you all... That's it... the problem is that I'm using MyISAM at this time and the other tables I have are of ISAM type. Sorry and thank you again! William N. Zanatta Ravi Raman wrote: > > hi. > > from the manual: > > If you delete the row containing the maximum value for an AUTO_INCREMENT > column, the value will be reused with an ISAM, or BDB table but not with a > MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM > table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over > for all table types. > > what type is the table you're using? > > hth. > -ravi. > > -Original Message- > From: William N. Zanatta [mailto:[EMAIL PROTECTED]] > Sent: Friday, July 20, 2001 3:54 PM > To: mysql > Subject: auto_increment, is this a bug?? Where to report it? > > Hello guys... > > I'm having problem with the auto_increment feature. > I have a table like this... > > ++-+--+-+++ > | Field | Type| Null | Key | Default| Extra > | > > ++-+--+-+++ > | Data | date| | | -00-00 | > | > | Edicao | int(3) unsigned | | PRI | NULL | auto_increment > | > | Status | varchar(6) | | || > | > > ++-+--+-+++ > > When I make entries to this table, the column Edition goes... > ++ > | Edicao | > ++ > | 1 | > | 2 | > | 3 | > ++ > as expected. > The problem is...if I delete the third entry (Edicao = 3), the next > entry inserted should be 3, right?! Well it is not! It is 4. So I get a > table like this... > ++ > | Edicao | > ++ > | 1 | > | 2 | > | 4 | > ++ > > Well, I tested it on 2.33.37, 2.33.38 and 2.33.39. In 2.33.37 it was > right...the entry was 3. But in the newer versions the number was 4. > It's like the auto_increment pointer didn't do the decrement when I > deleted the last entry. > Is this a bug?! Where should it be reported to? > Thank you all... > > William N. Zanatta > > - > 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: EXPLAIN question
In the last episode (Jul 20), Ravi Raman said: > ok, first actual question to the list, hope someone can help: > the following query - > > select pn.number, c.ID, c.cost, c.days_valid, c.description from > pinnumbers pn, cardtype c where pn.cardtypeID = c.ID and pn.tmaster = 0 > order by c.ID, pn.number > > returns: 55033 rows in set (4 min 10.11 sec) Are pn.cardtypeID and c.ID declared the same? That can sometimes cause problems. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment, is this a bug?? Where to report it?
It is not a bug, it is a bug fix. The auto_increment is based on the last number used. It is not supposed to decrement. j- k- On Friday 20 July 2001 11:53, William N. Zanatta wrote: > Hello guys... > > I'm having problem with the auto_increment feature. > I have a table like this... > > ++-+--+-+++ > > | Field | Type| Null | Key | Default| Extra > > ++-+--+-+++ > > | Data | date| | | -00-00 | > | > | Edicao | int(3) unsigned | | PRI | NULL | auto_increment > | > | Status | varchar(6) | | || > > ++-+--+-+++ > > When I make entries to this table, the column Edition goes... > ++ > > | Edicao | > > ++ > > | 1 | > | 2 | > | 3 | > > ++ > as expected. > The problem is...if I delete the third entry (Edicao = 3), the next > entry inserted should be 3, right?! Well it is not! It is 4. So I get a > table like this... > ++ > > | Edicao | > > ++ > > | 1 | > | 2 | > | 4 | > > ++ > > Well, I tested it on 2.33.37, 2.33.38 and 2.33.39. In 2.33.37 it was > right...the entry was 3. But in the newer versions the number was 4. > It's like the auto_increment pointer didn't do the decrement when I > deleted the last entry. > Is this a bug?! Where should it be reported to? > Thank you all... > > William N. Zanatta > > - > 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 -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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: Left Join very sloooowwww..
Hi Benjamin, Thanks for reading my message an taking some time to give some help/sugggestion.. First you are right that I should have written the query in a readable form.. You gave some comments: > SELECTbrand.brandname, > SUM((productorders.quantity)*(productorders.price)) AS turnover > FROM orders > LEFT JOIN productorders ON productorders.orderid = orders.id > LEFT JOIN perfect_articles ON productorders.ordernr = articles.ordernr > LEFT JOIN products ON products.id = articles.id > LEFT JOIN brand ON products.brand = brand.id > WHERE productorders.date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), >'%Y-%m-%d') > AND productorders.date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 0 DAY), >'%Y-%m-%d') > AND orders.status != 2 > GROUP BY brand.brandname > ORDER BY turnover asc > > Some comments: > > - Where is the table "articles" coming from? Did you mean > "perfect_articles"? If not, you have no restriction on this table. Yes, sorry, missed that one.. All the tables I originally made the query for have the prefix perfect_ in front.. (perfect_articles, perfect_brand, perfect_productsorders.. I removed this to the make it shorter and not make the query look more complicated than it is.. (To answer your question: I missed the one perfect_ you saw, this had to be removed) > - Where is the table "orders"? I assume you meant "productorders"? No, I do a select from orders... Further in the query productorders is linked to orders.. Productorders is also then linked to the database which contains all articles -> articles is linked to the products and products linked to brand... (Eg: the example: Microsoft (brand) -> Operating systems (products) -> Windows 95 (articles) The person orders a windows 95 package.. The subtotals, way of payment etc if stored in orders and all the articles he has ordered is stored in productorders.. The reason why I needed to link productorders to orders is to have the option of ignoring all records in which the order.status = 2.. (This is an number which tells me that the order has been cancelled) (I hope you understand, this is very hard to explain I would be better of drawing a small chart..) > - Do you really need LEFT JOINs instead of normal JOINs? At least the > effect of the LEFT JOIN with "productorders" is lost by using > "orders.status != 2" in the WHERE clause > > - You know that LEFT JOINs are usually slower than normal joins and > therefore should only be used when needed? (Additonally, the result > will differ in many cases) Sorry, tell me I am dumb... I think that I understand SQL a bit, but I am by no ways an expert.. I do try to get my hands on books from which I can learn something, but joins and indexes are still an area from which I can learn a lot of stuff.. In the past I always used a 'where' clause when joining stuff (eg: select apples.description, pears.description from apples, pears where pears.id = apples.id order by apples.id etc etc Then I got my hands on examples which used a left join examples.. I have never tried a normal join (sigh..), but thinking of it, what is the difference between an left join and a normal join..?? (I will look at the chapter join in the mysql manual just now, so if I ask something dumb which is explained in the manual then skip the answer..) I use PHP to format my output, so maybe a left join has something to do with the order the columns are displayed which is no use for me..?? > It doesn't matter if you use a ON clause instead of a WHERE clause > with normal JOINS. The type of the JOIN does matter! And you cannot > write LEFT JOINs with the constraint in the WHERE clause. I understand what you say, only the last sentance I think I should go read the manual to understand it.. (you cannot write left joins with the contraints?? in the where clause..) I did an explain on the query like you suggested/asked: (Please bear in mind that you should think the 'perfect_' and perfect_customer_ away for the above example... These command (explain select...) are new to mee, but it looks like some useful information and that I can optimize a lot by adding some extra indexes... Original Query: SELECT perfect_brand.brandname, Sum((perfect_customer_productorders.quantity)*(perfect_customer_productorders.price)) AS omzet FROM perfect_customer_orders LEFT JOIN perfect_customer_productorders on perfect_customer_productorders.orderid = perfect_customer_orders.id LEFT JOIN perfect_articles on perfect_customer_productorders.ordernr = perfect_articles.ordernr LEFT JOIN perfect_products on perfect_products.id = perfect_articles.id LEFT JOIN perfect_brand on perfect_products.brand = perfect_brand.id WHERE (perfect_customer_productorders.date >= DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' ) AND perfect_customer_productorders.date <=
Is last_insert_id reliable?
I have a hit counter script that logs all its hits in one table, one row per
hit.
Currently, I have been counting the rows in PHP in order to get the value of
the last hit_id in the database like this:
$sql4 = "SELECT hits.hit_id from hits";
$result = mysql_query ($sql4, $connection)
or die("error #" . mysql_errno() . ": " . mysql_error());
$last = mysql_num_rows($result) - 1;
$go = mysql_data_seek($result, $last);
$row = mysql_fetch_object($result);
$pixels = number_format($row->hit_id);
Which is really expensive on my very tall hits table. I hit upon doing it in
MySQL, which is much, much faster:
$sql4 = "SELECT hits.hit_id
as pixels
from hits
WHERE hit_id=LAST_INSERT_ID();";
$result = mysql_query ($sql4, $connection)
or die("error #" . mysql_errno() . ": " . mysql_error());
$row = mysql_fetch_object($result);
$pixels = number_format($row->pixels);
It's staggering the difference in speed. But if I sit there and hit refresh
on the browser, I get wildly different values for $pixels. It jumps around
the actual number by +-10 or 12. The first method is precisely the same
every time (unless a hit is recorded in the interim). Am I doing something
wrong here? Can I not count on MySQL to know how many rows it has recorded
in a table? Any idea why that select statement would select a different row
each time?
Walter
-
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: auto_increment, is this a bug?? Where to report it?
hi. from the manual: If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM, or BDB table but not with a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types. what type is the table you're using? hth. -ravi. -Original Message- From: William N. Zanatta [mailto:[EMAIL PROTECTED]] Sent: Friday, July 20, 2001 3:54 PM To: mysql Subject: auto_increment, is this a bug?? Where to report it? Hello guys... I'm having problem with the auto_increment feature. I have a table like this... ++-+--+-+++ | Field | Type| Null | Key | Default| Extra | ++-+--+-+++ | Data | date| | | -00-00 | | | Edicao | int(3) unsigned | | PRI | NULL | auto_increment | | Status | varchar(6) | | || | ++-+--+-+++ When I make entries to this table, the column Edition goes... ++ | Edicao | ++ | 1 | | 2 | | 3 | ++ as expected. The problem is...if I delete the third entry (Edicao = 3), the next entry inserted should be 3, right?! Well it is not! It is 4. So I get a table like this... ++ | Edicao | ++ | 1 | | 2 | | 4 | ++ Well, I tested it on 2.33.37, 2.33.38 and 2.33.39. In 2.33.37 it was right...the entry was 3. But in the newer versions the number was 4. It's like the auto_increment pointer didn't do the decrement when I deleted the last entry. Is this a bug?! Where should it be reported to? Thank you all... William N. Zanatta - 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: auto_increment, is this a bug?? Where to report it?
Well, it's a feature not a bug, and you are right too. auto_increment remembers the last value and keeps increasing it, no matter how many "holes" are created. So when use it, better to create a status column to change flag as deleted other than real delete it. "William N. Zanatta" wrote: > > Hello guys... > > I'm having problem with the auto_increment feature. > I have a table like this... > > ++-+--+-+++ > | Field | Type| Null | Key | Default| Extra > | > > ++-+--+-+++ > | Data | date| | | -00-00 | > | > | Edicao | int(3) unsigned | | PRI | NULL | auto_increment > | > | Status | varchar(6) | | || > | > > ++-+--+-+++ > > When I make entries to this table, the column Edition goes... > ++ > | Edicao | > ++ > | 1 | > | 2 | > | 3 | > ++ > as expected. > The problem is...if I delete the third entry (Edicao = 3), the next > entry inserted should be 3, right?! Well it is not! It is 4. So I get a > table like this... > ++ > | Edicao | > ++ > | 1 | > | 2 | > | 4 | > ++ > > Well, I tested it on 2.33.37, 2.33.38 and 2.33.39. In 2.33.37 it was > right...the entry was 3. But in the newer versions the number was 4. > It's like the auto_increment pointer didn't do the decrement when I > deleted the last entry. > Is this a bug?! Where should it be reported to? > Thank you all... > > William N. Zanatta > > - > 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: Left Join very sloooowwww..
> I added comments and a recipe to my debug function collection. > You can download at the address > http://pferdezeitung.de/php3/toosDebug.zip Sorry - in adding comments I inadvertently dropped several lines in function debugMsg. So if you downloaded version 0.4, please fetch version 0.4a. And then ... I missed to add table, sql etc. to this message! Sigh -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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
auto_increment, is this a bug?? Where to report it?
Hello guys... I'm having problem with the auto_increment feature. I have a table like this... ++-+--+-+++ | Field | Type| Null | Key | Default| Extra | ++-+--+-+++ | Data | date| | | -00-00 | | | Edicao | int(3) unsigned | | PRI | NULL | auto_increment | | Status | varchar(6) | | || | ++-+--+-+++ When I make entries to this table, the column Edition goes... ++ | Edicao | ++ | 1 | | 2 | | 3 | ++ as expected. The problem is...if I delete the third entry (Edicao = 3), the next entry inserted should be 3, right?! Well it is not! It is 4. So I get a table like this... ++ | Edicao | ++ | 1 | | 2 | | 4 | ++ Well, I tested it on 2.33.37, 2.33.38 and 2.33.39. In 2.33.37 it was right...the entry was 3. But in the newer versions the number was 4. It's like the auto_increment pointer didn't do the decrement when I deleted the last entry. Is this a bug?! Where should it be reported to? Thank you all... William N. Zanatta - 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: not selecting last row
That should work just fine. :) Thanks! - Original Message - From: "John Meyer" <[EMAIL PROTECTED]> To: "Tyler Longren" <[EMAIL PROTECTED]>; "MySQL List" <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 3:21 PM Subject: Re: not selecting last row > At 12:25 PM 7/20/01 -0500, Tyler Longren wrote: > >Hi everyone, > > > >Is there a way to NOT select the last row that was inserted into a table? I > >want everything before it. > > > >I know how I could do this using 2+ queries, but can it be done by issuing > >just 1 query? > > > >Thanks everyone, > >Tyler > > Assuming you had a unique ID, couldn't you do it this way: > > "SELECT * FROM TABLE WHERE ID=!LAST_INSERT_ID();" > > > John Meyer > [EMAIL PROTECTED] > Programmer > > > If we didn't have Microsoft, we'd have to blame ourselves for all of our > programs crashing - 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: not selecting last row
At 12:25 PM 7/20/01 -0500, Tyler Longren wrote: >Hi everyone, > >Is there a way to NOT select the last row that was inserted into a table? I >want everything before it. > >I know how I could do this using 2+ queries, but can it be done by issuing >just 1 query? > >Thanks everyone, >Tyler Assuming you had a unique ID, couldn't you do it this way: "SELECT * FROM TABLE WHERE ID=!LAST_INSERT_ID();" John Meyer [EMAIL PROTECTED] Programmer If we didn't have Microsoft, we'd have to blame ourselves for all of our programs crashing - 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: string errors compiling mysql+gemini-3.23.39 on Athlon running Linux 2.4.3-12
On Fri, Jul 20, 2001 at 06:44:29AM -0400, Jonathan Buhacoff wrote: > -- > >Description: > I get errors when I try to compile the distribution. The first one happens > in the gemini tree and is caused by a prototype mismatch between the bzero() > defined in and the one defined in the mysql source. Apparently, > the one on my system takes a void* for the first argument and the mysql > source expects char*. I got around this by lying in config.h and saying I > don't have string_h or strings_h and that I don't have bzero. Now gemini > compiles fine. > But, the problems continue. The next problem occurs in the client tree. I > guess since I said previously that I don' t have the strings.h file, I'm > leaving strlen undeclared and client/sql_string.cc balks at this. Hi, I encountered the same thing but my fix was different. Try going into gemini/incl/pscsys.h and look for this: +#ifndef bzero #define bzero(s,n) memset((s),(int)0,(n)) #endif Just pop that bit out so it doesn't bother defining it. Assuming you have bzero which you appear to. Put config.h back the way it was and all should work fine. Shane -- Shane Wegner: [EMAIL PROTECTED] http://www.cm.nu/~shane/ PGP: 1024D/FFE3035D A0ED DAC4 77EC D674 5487 5B5C 4F89 9A4E FFE3 035D - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help needed for replication
Dear Frederic, >localhost | jkasas | 1896f443280395b3 > And what I do and get : >mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; >ERROR 1133: Can't find any matching row in the user table Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out quotes)! :) Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: "BALU Frédéric" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 5:54 PM Subject: help needed for replication > Hi everybody, > > I use Replication on a windows NT4 Server. > There are 1 master and 2 slaves, all on the same machine. > One slave is started with the option skip-name-resolve, not the other. > For the 2 slaves : master-host=localhost > When adding a user (MYSQL.User), the replication is OK but, > when I use GRANT, I get a 1133 error. > Let's have a look on a slave : > > MYSQL.User table : >localhost | jkasas | 1896f443280395b3 > And what I do and get : >mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; >ERROR 1133: Can't find any matching row in the user table > > Personnally, I don't understand why. > Does anyone understand ? > > Thx for the answers. > > -- > Frederic BALU > > - > 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: Storing survey(s) data?
On 20-Jul-2001 Nelson Goforth wrote:
> Thanks to Steve and Jeff for your replies.
>
>
> The new system will store the data in MySQL table(s) for retrieval
> not only of specific survey results, but (as I'm collecting industry
> type and demographic data as well) in more complex ways as well. I
> may actually STILL store the individual survey results in text files
> for simplicity and 'bullet-proofness', but I need the added
> flexibility of being able to retrieve the data in more than one way.
> I AM a little uncertain of how best to get the data into a text file
> to then feed into Excel (pretty reports and graphs), which was the
> issue in my second question. Probably it's simply 'SELECT...INTO
> OUTFILE...', but I'm trying to think of ways to avoid my client
> having to ftp.
>
--- directly into Excel ---
print 'Content-type: application/ms-excel', "\n";
print 'Content-Disposition: inline filename="foo.csv"', "\n\n";
-- or save as file ---
print 'Content-type: octet/stream', "\n";
print 'Content-Disposition: attachment; filename="foo.csv"', "\n\n";
$res=SQLQuery("SELECT ...");
while ( @row = $res->fetchrow()) {
print join(',', @row), "\r\n";
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table 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
not selecting last row
Hi everyone, Is there a way to NOT select the last row that was inserted into a table? I want everything before it. I know how I could do this using 2+ queries, but can it be done by issuing just 1 query? Thanks everyone, Tyler - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help!
Dear Don, congrats for changing from Access to a real DBMS! I am not informed how complex your tables are (relations etc.). As for the data concerned, simply export the Access tables into CSV format. MySQL can import CSV very easily. Try PhpMyAdmin, a browser based PHP tool with a graphical interface. Problems may / will arise with date fields. Access has strange date formats, MySQL uses the standard SQL date format. Maybe someone else from the list has a solution for this. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: "Don Moor" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 4:50 PM Subject: Help! > My website, which included a Microsoft Access database, was on a server that > went bye-bye. I signed up with a new host that doesn't support MS > Access...and they suggested that I contact you to convert my database to > Mysql. > > What do I do next? > > Please respond ASAP! > > Don Moor > [EMAIL PROTECTED] > http://www.dmoor.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: Left Join very sloooowwww..
Hello. [...] > Here is the query > [query reformatted... btw, it would have been nice if you had done this at first] SELECTbrand.brandname, SUM((productorders.quantity)*(productorders.price)) AS turnover FROM orders LEFT JOIN productorders ON productorders.orderid = orders.id LEFT JOIN perfect_articles ON productorders.ordernr = articles.ordernr LEFT JOIN products ON products.id = articles.id LEFT JOIN brand ON products.brand = brand.id WHERE productorders.date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d') AND productorders.date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 0 DAY), '%Y-%m-%d') AND orders.status != 2 GROUP BY brand.brandname ORDER BY turnover asc Some comments: - Where is the table "articles" coming from? Did you mean "perfect_articles"? If not, you have no restriction on this table. - Where is the table "orders"? I assume you meant "productorders"? - Do you really need LEFT JOINs instead of normal JOINs? At least the effect of the LEFT JOIN with "productorders" is lost by using "orders.status != 2" in the WHERE clause - You know that LEFT JOINs are usually slower than normal joins and therefore should only be used when needed? (Additonally, the result will differ in many cases) [...] > I need to do the above query about 4 times for different intervals and in this case >it then takes about 15 minutes to complete... > (Pentium III 800 machine with 128 MB) [...] We need to see the output of EXPLAIN SELECT ... SHOW INDEX FROM brand SHOW INDEX FROM productorders SHOW INDEX FROM perfect_articles SHOW INDEX FROM products SHOW INDEX FROM orders > It looks that as soon as if I start to link a table with more than > 1000 records the machine is having a hard time.. Is it better to use > a where clause to link the tables..?? It doesn't matter if you use a ON clause instead of a WHERE clause with normal JOINS. The type of the JOIN does matter! And you cannot write LEFT JOINs with the constraint in the WHERE clause. Bye, Benjamin. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help!
> What do I do next? Check in to mysql.com, look for AccessToMySQL tools, fget a distribution, set it up and convert your data. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: [PHP] PHP & MySQL
At 12:30 PM 7/20/01 -0400, you wrote: >on 7/20/01 12:32 PM, Erich Kolb at [EMAIL PROTECTED] wrote: > > > When you query a MySQL Database, how do you set the order alphabetically? > > >If it's a text field just say DESC > >Susan > isn't that reverse alphabetical ASC is (default) alphabetical John Meyer [EMAIL PROTECTED] Programmer If we didn't have Microsoft, we'd have to blame ourselves for all of our programs crashing - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help!
At 08:50 AM 7/20/01 -0600, you wrote: >My website, which included a Microsoft Access database, was on a server that >went bye-bye. I signed up with a new host that doesn't support MS >Access...and they suggested that I contact you to convert my database to >Mysql. First off, download mysql from mysql.com, also download dbtools from the mysql web site. Install both, then navigate to the mysql directory, enter the bin directory, and click on WinMySqlAdmin. Once that is up, click on the dbtools icon and select Data->Import Data Wizard. Select Access database, navigate to your access database, and run from there. Then, click on Data->Export Database, and export the data that you just uploaded to your mysql server. John Meyer [EMAIL PROTECTED] Programmer If we didn't have Microsoft, we'd have to blame ourselves for all of our programs crashing - 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
Borland CBuilder5 and mysql C api
I have been trying for some months to use the C api with Borland builder, so far with only limited success. I can write a console application in the Builder environment, compile it, but not link it. The error says the library routines are not found. If I export the make file, edit it to include the required libraries and run it from the command line, all is well (for a while). If I actually compile and run a programme using the VCL in the same directory, then the original program will no longer link. If I move the code to a new directory, it will now link again. I have discovered no way of linking a Builder programme using the VCL to the mysql library, either in the environment, or by hand. Always, the library routines cannot be found. If anyone has any experience with this environment, I would be very grateful for any information or help you could provide. --- Layton Morris [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: database server upgrade
On Fri, Jul 20, 2001 at 06:20:00AM -0700, Mike Wexler wrote: > > I already increased table_cache from 128 to 2048. Which helped. And > last night I increase key_buffer from 16MB to 64MB. Maybe it should > be even larger? Oh, you can easily make it quite a bit higher. On my 1GB systems, I have it at 384MB at a minimum. > If I use replication, I guess I should have all the updates go to a > master server and distribute the queries to the slaves? Ideally, yes. -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW MySQL 3.23.29: up 34 days, processed 266,444,747 queries (89/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: mysql.org
MySQL AB and NuSphere had a meeting over the phone, we exchanged information and opinions and NuSphere will propose times for the next meeting. Britt... -- D. Britton Johnston 603-578-6707 Nashua Chief Technology Officer 781-280-4954 Bedford NuSphere Corporation 781-280-4600 Main 14 Oak Park 781-280-4646 Fax Bedford, MA 01730 www.nusphere.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: delete & sub-selects
"Andrius Armonas" <[EMAIL PROTECTED]> wrote: > I'm running mysql 3.23.39. Could anybody explain me how to do this in mysql > (without sub-selects): > > delete from IP where id in ( select id from USERS where MK='0032' ) One solution is to build a list of id values from the users table in your application. If using PHP an easy way to do is loop through each result, adding to an array then implode the array on "," and save as a $var. Then run the delete with "...WHERE id IN ( $var )". -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.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
problem with mysql-3.23.39 server
Hello there. When I install Mysql-3.23.39 at my OpenBSD 2.9 server, it works perfectly.. until i restart the server.. does anyone have had the same problems as I ? plz write back.. I just get this command, when i try to start the mysqld server. with safe_mysqld # safe_mysqld /usr/libexec/ld.so: my_print_defaults: libpthread.so.14.20: No such file or dire ctory Starting mysqld daemon with databases from /var/mysql 010720 19:56:33 mysqld ended i have write /usr/local/lib/pth in the ld path. in rc.conf and the file does exist at the computer under /usr/local/lib/pth help me plz.. What to do.. by the way.. this is the content of my w3.err file.. (logfile) 010720 19:56:53 mysqld started /usr/libexec/ld.so: mysqld: libpthread.so.14.20: No such file or directory 010720 19:56:53 mysqld ended >From Tomas.. - 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
EXPLAIN question
hi. ok, first actual question to the list, hope someone can help: the following query - select pn.number, c.ID, c.cost, c.days_valid, c.description from pinnumbers pn, cardtype c where pn.cardtypeID = c.ID and pn.tmaster = 0 order by c.ID, pn.number returns: 55033 rows in set (4 min 10.11 sec) pinnumbers has only about 8 rows, and cardtype has 2 rows. an explain shows this: +---+--+---++-+---+---+- + | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+---++-+---+---+- + | pn| ref | tmster| tmster | 5 | const | 67580 | Using temporary; Using filesort | | c | ALL | PRIMARY | NULL |NULL | NULL | 2 | where used | +---+--+---++-+---+---+- + if i replace the first part of the query with "select count(pn.number) from...", i get: +--+ | count(pn.number) | +--+ |55033 | +--+ 1 row in set (0.81 sec) pretty fast. what's making it take so long? the join? as stated before, there are only 2 rows in cardtype. output from show index as follows: mysql> show index from pinnumbers; +++--+--+-+- --+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+- --+-+--++-+ | pinnumbers | 1 | tmster |1 | tmaster | A |NULL | NULL | NULL | NULL| | pinnumbers | 0 | PRIMARY |1 | number | A | 8 | NULL | NULL | NULL| +++--+--+-+- --+-+--++-+ 2 rows in set (0.00 sec) mysql> show index from cardtype; +--++--+--+-+--- +-+--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--++--+--+-+--- +-+--++-+ | cardtype | 0 | PRIMARY |1 | ID | A | 2 | NULL | NULL | NULL| +--++--+--+-+--- +-+--++-+ 1 row in set (0.00 sec) any input would be gratefully appreciated, if the table descriptions would help, i'll post those. this seems like an easy join, but i'm confused as to what to do to speed it up. -ravi. - 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: database server upgrade
Quick question about the SHOW TABLE STATUS command and the Data_free info in particular. You say: "If it is high, then it's time to run OPTIMIZE TABLE..." What is considered high? As I'm looking at my output, I see that most of my tables show a value of 0, however, some have a value around 300 and some go WAY high. Is 300 considered high? Sorry if this is a stupid question...just not sure. Thanks. -- Jason On 7/20/01 10:01 AM, Basil Hussain was heard saying: > Hi, > >> Thank you very much for the detailed analysis! One question: >> where did he get all this data from? > > You can show all of MySQL's status and configuration parameters by issuing > these statements: > > SHOW STATUS; > SHOW VARIABLES; > > Also, the following can come in handy if you want to see info about your > tables: > > SHOW TABLE STATUS; > > The one piece of data that is particularly of relevance to performance > tuning with the output from this is Data_free. This shows how much space has > been allocated in the table but not used. If it is high, then it's time to > run OPTIMIZE TABLE on that particular table to consolidate empty gaps. > > Regards, > > Basil Hussain > --- > Internet Developer, Kodak Weddings > E-Mail: [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 > - 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 needed for replication
Hi everybody, I use Replication on a windows NT4 Server. There are 1 master and 2 slaves, all on the same machine. One slave is started with the option skip-name-resolve, not the other. For the 2 slaves : master-host=localhost When adding a user (MYSQL.User), the replication is OK but, when I use GRANT, I get a 1133 error. Let's have a look on a slave : MYSQL.User table : localhost | jkasas | 1896f443280395b3 And what I do and get : mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Personnally, I don't understand why. Does anyone understand ? Thx for the answers. -- Frederic BALU - 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
installing source code on corel linux
I am a newbie to linux and mysql and would like some advice re installing mysql source code on corel linux. I have successfully installed a binary version of Mysql (now deleted) but need to compile and install a source version, as I want to next install PHP.which I understand needs the source code distribuion of Mysql. The mysql version is 3-23-38 I have followed the instructions from the Mysql documentation as follows- shell> groupadd mysql shell> useradd -g mysql mysql shell> gunzip < mysql-VERSION.tar.gz | tar -xvf - shell> cd mysql-VERSION shell> ./configure --prefix=/usr/local/mysql shell> make shell> make install shell> scripts/mysql_install_db shell> chown -R mysql /usr/local/mysql shell> chgrp -R mysql /usr/local/mysql shell> /usr/local/mysql/bin/safe_mysqld --ser=mysql & Everything is OK up to and including line 5, the config command. The make command results in an error message which is because the makefile file does not exist. I have installed autoconf and automake and rerun the config file OK which recognises autoconf and automake I then run automake ( instead of the make command) and processing takes place for a few seconds without any messages. However no mysql/bin folders or files have been created that I would expect from the compile/install process. Can anyone tell me what I have missed pls. Cheers Barrie Lees - 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: Storing survey(s) data?
Thanks to Steve and Jeff for your replies. A spreadsheet is how I've handled this for several years now. Responses came in via a Scantron card reader (or manually) and the resulting text file was fed into an Excel spreadsheet for graphic output. I was in mind of the spreadsheet when devising a data storage structure, but didn't want to be caught up in an inefficient paradigm if there was a better way - hence I sought help from the list. Then I put in an interim system that allowed Web-based response, wrote the responses to a text file (one file per questionnaire per client) and then the text went into the same Excel spreadsheets. The new system will store the data in MySQL table(s) for retrieval not only of specific survey results, but (as I'm collecting industry type and demographic data as well) in more complex ways as well. I may actually STILL store the individual survey results in text files for simplicity and 'bullet-proofness', but I need the added flexibility of being able to retrieve the data in more than one way. I AM a little uncertain of how best to get the data into a text file to then feed into Excel (pretty reports and graphs), which was the issue in my second question. Probably it's simply 'SELECT...INTO OUTFILE...', but I'm trying to think of ways to avoid my client having to ftp. The actual questions ARE stored in a separate table (table 1 in Jeff's example), with one questionnaire (regardless of number of questions) per record. The questions are simply in one text field and my Perl program parses them out into a table. All questionnaires in this group are scored on a 1-5 scale - so I just use radio buttons in an HTML form. Works great. Storing them this way also allows me the potential for storing each questionnaire in multiple language versions (one field in each record holds a language code, like 'en' or 'es', and another holds the CHARSET code). I've also got a table (Jeff's Table 2) that holds the information about the survey as a whole (client name, industry type, etc). Another table holds demographic data. Since I'm wanting anonymity for the survey-takers, and since I want to make sure that no one is stuffing the ballot box (which was possible up to now), I also have the administrators create a set of 'tickets' (another table) for the survey. They enter the e-mail addresses for the people they want to answer the survey, then the system creates a ticket number (an MD5 digest involving a random number) and that ticket number, attached to a URL, is mailed to the survey taker. Once they use that ticket to take the survey the ticket is 'punched' and can't be used again. The client is not allowed to see the relationship between ticket number and e-mail address and therefore anonymity is increased. I suppose I could disassociate them even further with another step and remove the association of the ticket number with the data, but the anonymity isn't THAT critical and the ticket number is how I'll associate the questionnaire responses with the demographic data. So I'll go with keeping all the data together, and hope they don't come up with any '100 questions' questionnaires. 90 is too many anyway - takes hours! Thanks again, Nelson > >> Seems like the simple way would be to create a table with 100 fields >>> and store the answers (plus metadata - survey #, timestamp, etc) there, >>> each record using up as many fields as necessary. In this case I'd >>> probably store the metadata in fields 1-10 (as needed) and begin the >>> real data in field 11. >> >> If you need to add/edit/delete a question you'll have to modify the >> table structure and you'll likely have to modify all of your queries. > >No, not if he uses generic column names e.g. q1, q2, q3 and a helper >table to match up columns with descriptive question names. This helper >table can include a column that relates to a Survey ID (since he has >multiple surveys of differing data schemas). > >>> Or could use one table per survey type - so that only data from the >>> same list of questions goes into each table. > >Mrf, save me, Helper Table! > <---snip> >NO, this is a bad idea for the sake of queries. You most likely want to >keep all your answers for each survey together in one record so you can >do complex queries over all responses to a given survey. Picture in your >head how each scenario would look in a spreadsheet. Think of how you >would apply different queries to the data using each of the two suggested >data organization methods. > > > I recommend 3 tables. Table 1 stores the questions - question_id and >> question_name. Table 2 stores the surveys - survey_id, question_id. >> Table 3 stores the survey results - user_id (or simple sequential id), >> survey_id, question_id, response. If each question has set choices >> you'll need a 4th table which will have question_id, choice_id, >> choice_description and instead of response in T
Re: SMP+mysql problem
Under solaris 8 you can use pmap -x 22889 which will give address space map. -Igor On Fri, 20 Jul 2001, Jeremy Zawodny wrote: > On Wed, Jul 18, 2001 at 12:45:57PM +0200, Werner Stuerenburg wrote: > > What I am stumbling about is this: > > > > > PID USERNAME THR PRI NICE SIZE RES STATE TIMECPU COMMAND > > >> 22889 root 30 330 801M 11M cpu0 23.6H 26.75% mysqld > > > > Is this really true: size 801 M How can that be > > There are two answers: > > (1) He's given MySQL a lot of RAM. Maybe a big key_buffer. > > (2) Solaris is a bit funny about the way it reports memory usage. > All dynamic libraries are sometimes included in the memory > stats. Certainly that doesn't account for 800M, but it's > probably in there. > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW > > MySQL 3.23.29: up 33 days, processed 263,736,279 queries (89/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: Anyone knows what's wrong with this INSERT sentence?
I don't investigate the error -- why don't you put a unique index on email? That's how you get what you want. > I have this two tables : table1,table2 . I would like to include all emails from >table2 into table1 removing duplicates. > INSERT INTO table1 (email) SELECT email from table2 where table1.email <> >table2.email; > MYSQL says: ERROR 1109: Unknown table 'table1' in where clause > Both tables exist, what's wrong? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: database server upgrade
Hi, > Strange. My understanding was that RAID 5 was good for read bandwidth > but that keeping the parity disk uptodate slowed it down for write > bandwidth. Well, what you say is almost true to a certain extent. Firstly, with RAID 5 parity is striped across the disks too, so there is no bottleneck with the parity writing, just a slight overhead. So, RAID 5 isn't the fastest RAID schema (that's RAID 0) but it's still faster than RAID 1, as the data still only has to be written once overall (as opposed to one copy on each disk). I've just thought of something else that might also warrant looking into. A few of the guys at MySQL advocate using RAID 0+1 for the greatest speed. As far as I remember, RAID 0+1 is where you have the data striped across two disks, and those disks are mirrored on another pair. > > Your Opened_tables figure is quite large, which means you are incurring > > extra I/O penalties as tables have to be constantly opened and > closed. You > > should try increasing your table_cache size. > > Its already 2024 (I've upped it from 128). What is the maximum > reasonable value on RedHat Linux 6.2 and a 2.2.X kernel. Would upgrading > to RedHat 7.1 and 2.4.x help? Hmm. I'm not sure exactly, but with RH 6.2 I don't think you'll be able to increase this much more as I seem to remember the file descriptor limit is 2000-something. I may be wrong though... (It may be only 6.0 that has that limit.) > > Slow_launch_threads should never be more than zero. And, seeing as your > > configured slow launch yardstick time is 2 seconds, this > indicates you may > > be starting to have a bottleneck here. You should trying setting a > > thread_cache_size of something like 32 - maybe higher. > > Ok. Although 1 out of 346,157 doesn't seem to be significant. After the > server has been up a week or two I can tell if this is significant. What > is the downside of a thread_cache? Why isn't it on by default? Having a thread cache is useful for environments with high frequencies of MySQL connections. When a client connects, a new thread is created (you may notice from the status variables that your Connections and Threads_created are the same figures). To quote from the manual: "When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created." So, having a thread cache reduces latency on new connections to MySQL from client apps - which can only be a good thing, yes? Granted, 1 out of 346,157 isn't significant in itself, but it's a good idea to pre-empt a potential load problem. As far as I know, there is no downside of having a thread cache (aside from the inevitable memory usage, etc.). As for why it isn't turned on by default, I have no idea. > We are running everything through a 100 Mbps switch. > I can certainly take 3 or 4 very query instensive clients and set them > up to use read only clients. Does it make sense to do it that way? Also, > all the queries go through a perl module of mine, so I could distribute > everything, but does it make sense to distribute updates? It really depends on what replication topology you employ. If you go for a '1-way' or 'one master, many slaves' topology then having your read-only clients use the slave servers would make sense. All your read/write clients could also read in a distributed fashion but would write only to the master. If, however, you employed a '2-way' or 'many-masters' topology then the issue of distributing updates occurs. However, '2-way' replication has whole load of issues that you need to tip-toe around carefully (such as auto-increment fields clashing values). Unless your environment has a high update load too, then you probably don't need to worry about distributing updates. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help!
My website, which included a Microsoft Access database, was on a server that went bye-bye. I signed up with a new host that doesn't support MS Access...and they suggested that I contact you to convert my database to Mysql. What do I do next? Please respond ASAP! Don Moor [EMAIL PROTECTED] http://www.dmoor.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help! can't load libmysqlclient.so.10
If you are running Linux, man ldconfig. add the path to /etc/ld.so.conf and run ldconfig. Michael Taney wrote: > I've installed mysql and PHP4 according to instructions at mysql.com, > but I'm getting the following error when I try to start httpd: > > Cannot load /etc/httpd/modules/libphp4.so into server: > libmysqlclient.so.10: cannot open shared object file: No such file or > directory [FAILED] > > So, it's locating libphp4.so ok, but can't find libmysqlclient.so.10, > which actually located at: > > /usr/local/mysql/lib/mysql/libmysqlclient.so.10 > > > > > === > Michael Taney > [EMAIL PROTECTED] > 802 748-6311 voc > 802 748-6322 fax > > > > > - > 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 -- Gerald L. Clark [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
delete & sub-selects
hello, I'm running mysql 3.23.39. Could anybody explain me how to do this in mysql (without sub-selects): delete from IP where id in ( select id from USERS where MK='0032' ) Thanks. .:: Andrius Armonas ::. .:: [EMAIL PROTECTED] ::. .:: http://baubas.andrius.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
Compiling Mysql++-1.7.9 on Linux with GCC 3.0
Hi boys, I have a problem when I make mysql++-1.7.9 API with gcc 3.0. Can somebody help me, where is the problem ? Is mysql++-1.7.9 compatible with the last ANSI/ISO C++ rules ? Matteo Limonta - 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.org
Absolutely great! You are so keen and brave - I wouldn't dare to and some others obviously neither! We need people who calm things down to make negotiations and an agreement possible, that's absolutely true, but we also need people to emphasize the frontiers to make a correct agreement possible in the first place. If it is not possible to distinguish between right or wrong, how will you hope to reach a basis that will last? It is not a question of politics, but of ethics and morals. I know that these are not counted upon in business, but ultimately this is what drives the world. And nobody will ever change the basic principles. You may have short term success violating those principles, but they will not last. Yesterday, I read an interesting German article about Bill Gates. A nice parallel was drawn to Napoleon and the like. All of these had huge success on the short run, but failed very soon. When I started in my computer business, Nixdorf was overwhelmingly successful. Five years later, they didn't exist anymore. They, like all big players at the time, held their customers hostage. Nobody liked that, so as soon as they had a chance they took their choice. The same will happen with Microsoft and any other endeavor which tries to force people into something which they don't really want. On the other hand, the open source movement is driven by huge energies which cannot be bought by money, no matter how much you want to throw at it. As they are negotiating right now, as far as I remember, this beautiful analysis may come a little late. On the other hand, they plan to take breaks and communicate, so I hope both parties will get to know your arguments. >>You are, of course, welcome to your opinion, and to the expression of said >>opinion. You are also liable to be judged on the basis of that expression, and >> frankly, your expression leaves me wondering about your wisdom and your >>analytical capabilities, not to mention your social skills. > What - you work for NuShpere. My analysis of this affair is correct. > It comes down to trust >I TRUST MONTY. > I do not trust Brit, and man who has abused the GPL, abused the MYSQL > trademark, and broke the trust between the two partners. > What do I base that trust on > 6 years of contant communication and dependency of the MYSQL > staff and my PERSONAL relationship with Monty and his Fella's > as he called them. > Furthmore, the assumption that under any condition NuSphere BROUGHT the right > to open up shop directly under the MYSQL name and conduct independent sales, > promotion and business, is damn off the wall, far fetched, and ridicules to > asume, that you'd have to be a complete utter moron to beleive this report, > and in addition, the report should be playing tomorrow afternoon on the > Opra show, and be reported right next to the alien abduction story in the > National Enquirer. > Furthermore, their behavior secondary to this, and the proposition that they > opened the mysql.org site as a "Community" site flies right in the face > of the proposition forwarded by NuShpere that they opened the site with full > rights to do so under previous agreements because they purchased control > over the MYSQL trademark because they did in secret, then protested they had > they right ot, after saying it was a communitee site, and then finally, they > release they're Gemini code on it, only after 100's of people complained that > they were violating the GPL. > This is NOT the actions of an honest person...period... > No > We need NuSphere to admit their wrongs, make a blanket apology, and > everyone can call a no harm no foul, and forget it happened. > Ruben > - End of forwarded message from Brooklyn Linux Solutions CEO - > - > 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 -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help! can't load libmysqlclient.so.10
Michael Taney <[EMAIL PROTECTED]> writes: > I've installed mysql and PHP4 according to instructions at mysql.com, > but I'm getting the following error when I try to start httpd: > > Cannot load /etc/httpd/modules/libphp4.so into server: > libmysqlclient.so.10: cannot open shared object file: No such file or > directory [FAILED] > > So, it's locating libphp4.so ok, but can't find libmysqlclient.so.10, > which actually located at: > > /usr/local/mysql/lib/mysql/libmysqlclient.so.10 This directory needs to be in /etc/ld.so.conf -- Trond Eivind Glomsrød Red Hat, Inc. - 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 keeps corrupting
I experience something similar for a long time and try to find out what's happening. I cannot see any rule, so far. Any ideas what I could do to find out? For some days now, I didn't have problems now except for a duplicate entry error which shouldn't occur at all. Before, this indicated a corrupted index and/or table. But now the table is ok. Puzzling. BTW: I studied the sections in the manual about what to do if tables keep crashing. Sie schrieben am Freitag, 20. Juli 2001, 14:32:18: > This is the feedback from repair: mysql>> repair table hits > -> ; > +--++--+ > -+ > | Table| Op | Msg_type | Msg_text > | > +--++--+ > -+ > | counter.hits | repair | info | Wrong bytesec: 17-0-197 at 1097180; > Skipped | > | counter.hits | repair | warning | Number of rows changed from 8628 to > 8627| > | counter.hits | repair | status | OK > | > +--++--+ > -+ > 3 rows in set (0.71 sec) > This has happened at least once a day for the past three days (ever since > the hit counter got popular. > This is the version: > mysql Ver 11.15 Distrib 3.23.38, for apple-darwin1.3.3 (powerpc) > And some other stats: > Threads: 6 Questions: 2007 Slow queries: 0 Opens: 14 Flush tables: 1 > Open tables: 4 Queries per second avg: 0.055 > As you can see, it's barely ticking over compared to some installs I've > seen. Any suggestions where to look for trouble here? > Walter > - > 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 -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: database server upgrade
Hi, > Thank you very much for the detailed analysis! One question: > where did he get all this data from? You can show all of MySQL's status and configuration parameters by issuing these statements: SHOW STATUS; SHOW VARIABLES; Also, the following can come in handy if you want to see info about your tables: SHOW TABLE STATUS; The one piece of data that is particularly of relevance to performance tuning with the output from this is Data_free. This shows how much space has been allocated in the table but not used. If it is high, then it's time to run OPTIMIZE TABLE on that particular table to consolidate empty gaps. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [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
string errors compiling mysql+gemini-3.23.39 on Athlon running Linux 2.4.3-12
-- >Description: I get errors when I try to compile the distribution. The first one happens in the gemini tree and is caused by a prototype mismatch between the bzero() defined in and the one defined in the mysql source. Apparently, the one on my system takes a void* for the first argument and the mysql source expects char*. I got around this by lying in config.h and saying I don't have string_h or strings_h and that I don't have bzero. Now gemini compiles fine. But, the problems continue. The next problem occurs in the client tree. I guess since I said previously that I don' t have the strings.h file, I'm leaving strlen undeclared and client/sql_string.cc balks at this. >How-To-Repeat: I'm not sure what to put here because this happened even the first time I tried to compile. I'm running RedHat and it came with mysql rpm's but I want to set the localstatedir to be something else and it didn't seem possible with a binary distribution unless I use symlinks. So actually if you know how to do this please enlighten me and I won't need to compile. >Fix: BTW I used mysqlbug but I can't send mail from the machine where it's installed so I have to paste things here. Sorry if the format isn't exactly right. I'm not on the mysql list so if you happen to know how to solve my problem please cc to me at [EMAIL PROTECTED] >Submitter-Id: >Originator: Jonathan [EMAIL PROTECTED] >Organization: >MySQL support: none >Synopsis: multiple errors involving string functions when compiling mysql+gemini 3.23.39 on Athlon running Linux 2.4.3-12 >Severity: critical (for me) >Priority: medium (I'd really rather not settle for the default location of localstatedir) >Category: mysql >Class: sw-bug or configuration problem >Release: mysql-3.23.39 (Source distribution) (according to mysqlbug) >Environment: Ok, I'm pasting here the output of mysqlbug but I have some beef with this: #1, in "Compilation info" it does NOT show the vars that I set when I compiled. #2, in "Configure command" it does NOT show the settings that I specified I know that what I specified was used because I could see it in make's output. I don't know why mysqlbug didn't detect the right stuff. So, after mysqlbug's output I put a little line of === and then pasted what I used. System: Linux beer 2.4.3-12 #7 Thu Jul 5 15:07:25 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-85) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jun 23 2000 /lib/libc.so.6 -> libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 17:58 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 15:27 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 15:27 /usr/lib/libc.so Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock --with-low-memory\ --with-mit-threads=yes --without-perl --enable-thread-safe-client --with-be rkeley-db --with-\ innodb Compilation info: CC=gcc CFLAGS="-O3 -DDBUG_OFF" CXX=gcc CXXFLAGS="-O3 -DDBUG_OFF -fno-implicit-templates -felide-constructors -fno-e xceptions -fno-rtti" Configure command: ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --localstated ir=/system4/database/mysql/data --with-gemini=gemini --enable-assembler --wi th-mysqld-ldflags=-all-static --enable-thread-safe-client >Additional info: In file included from mysql.cc:49: sql_string.h: In method `String::String (const char *)': sql_string.h:39: `strlen' undeclared (first use this function) sql_string.h:39: (Each undeclared identifier is reported only once for each function it appears in.) In file included from ../readline/chardefs.h:37, from ../readline/keymaps.h:31, from ../readline/readline.h:36, from mysql.cc:83: /usr/include/string.h: At top level: /usr/include/string.h:218: `size_t strlen (const char *)' used prior to declaration /usr/include/string.h:242: parse error before `)' /usr/include/string.h:242: parse error before `)' /usr/include/string.h:242: parse error before `)' /usr/include/string.h:242: warning: `memcpy' initialized and declared `extern' /usr/include/string.h:242: variable or field `memcpy' declared void /usr/include/string.h:242: `int memcpy' redeclared as different kind of symbol /usr/include/string.h:38: previous declaration of `void *memcpy (void *, const void *, unsigned int)' /usr/include/string.h:242: initializer list being treated as compound expression make: *** [mysql.o] Error 1 -- By the way if I have done something wrong in reporting this and it's about to be ignored, please let me know so I can fix whatever and get some help. I've been using the binaries on another machine for ov
Re: database server upgrade
> > > Yes, you should definitely look at option #9 first. Here's a few pointers to > some things that immediately spring off the screen at me: > > | Open_tables | 1296 | > | Open_files | 2180712| > | Open_streams | 0 | > | Opened_tables| 1277057| > | table_cache | 2024 | > > Your Opened_tables figure is quite large, which means you are incurring > extra I/O penalties as tables have to be constantly opened and closed. You > should try increasing your table_cache size. Its already 2024 (I've upped it from 128). What is the maximum reasonable value on RedHat Linux 6.2 and a 2.2.X kernel. Would upgrading to RedHat 7.1 and 2.4.x help? > > | Slow_launch_threads | 1 | > | Threads_cached | 0 | > | Threads_created | 346157 | > | thread_cache_size| 0 | > > Slow_launch_threads should never be more than zero. And, seeing as your > configured slow launch yardstick time is 2 seconds, this indicates you may > be starting to have a bottleneck here. You should trying setting a > thread_cache_size of something like 32 - maybe higher. Ok. Although 1 out of 346,157 doesn't seem to be significant. After the server has been up a week or two I can tell if this is significant. What is the downside of a thread_cache? Why isn't it on by default? > > | Table_locks_immediate| 27157119 | > | Table_locks_waited | 58498 | > | Key_read_requests| 1535872968 | > | Key_reads| 5560163| > > This is good. Table locks that had to be waited for are less than 1% of > total locks. You don't seem to have too much of a problem with lock > contention. Also, your ratio of key reads/requests is way less than 0.01, so > no general problems with index usage on your queries. This doesn't cover a period of max usage. I need to get some more information now that I've upgraded the server. > > | Created_tmp_disk_tables | 415975 | > | tmp_table_size | 2097144| > > Created_tmp_disk_tables could probably be a little lower. Try increasing > your tmp_table_size memory figure to lessen the number of temp tables > written to disk - 2Mb is probably quite small if you're shuffling large > amounts of data. Ok. > > As for replication, there could be a couple of sticking points with this > strategy that you may need to overcome. The first is whether your client > applications (be they web scripts, custom apps, whatever) can easily be > re-programmed to support distributing their SQL query load amongst several > servers. Secondly, if you are chucking large amounts of data around and your > servers are replicating it all, your networking may not be up to scratch. If > you go for replication you should make sure you're running at least 100Mbps > between your MySQL servers. (BTW, if in a closed environment, running > 100Mbps to the clients might help also.) We are running everything through a 100 Mbps switch. I can certainly take 3 or 4 very query instensive clients and set them up to use read only clients. Does it make sense to do it that way? Also, all the queries go through a perl module of mine, so I could distribute everything, but does it make sense to distribute updates? > > Looking at option #8, you may see quite a large performance boost if you go > for InnoDB tables and your query load consists of large quantities of small > queries, as this is where row-level locking, etc. will help most. There are some tables that are like this. > > Regards, > > Basil Hussain > --- > Internet Developer, Kodak Weddings > E-Mail: [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 - 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: SMP+mysql problem
> It's normal that the mysqld is 795M big? If is not normal which could be the > cause of this size? Certainly not. But Jeremy gave some comments; I don't know anything about this, I wonder myself. My processes are between 8 and 25 MB. > PID USERNAME THR PRI NICE SIZE RES STATE TIMECPU COMMAND > 14237 root 50 580 795M 33M sleep 187:33 0.15% mysqld This process is sleeping and consumes that much memory. This looks funny to me. Why should the sleeping process keep that much memory hostage? But most probably, I don't understand anything about what's happening here. Anybody out there? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The Best Platform
The Best: By Dave Carter SELECT * FROM tbl_experience WHERE knowledge like '%SQL%' ORDER BY least_expense GROUP BY by no_equal; Result: MySQL Ok so it's a little hokey, but it was the best I could after the conversion project I just got out of, thank god for MySQL. Dave Carter Chief Web Architect ABT, Inc. http://www.abti.cc - 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.org
The web site is a off shot of the magazine, Ziff-Davis publishes most of the "tech" magazines on the new racks. They Been doing it for a long time. The class that this one is in I will call the "Movers and Shakers". "Interactive week" they claim is a 200$ a year subscription, but I doubt anyone ever paid it. It is sent to you after filling out a questionnaire that you buy/approval/recommend Internet/computer stuff. They want the CTO to the lead programmer(people in the purchase order loop) They are using it so the advertiser reach the group they want. Overall they are not bad and give you something to read in the restroom. MJM - Original Message - From: "Van" <[EMAIL PROTECTED]> To: "Michael Meltzer" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 1:32 AM Subject: Re: mysql.org > Michael Meltzer wrote: > > > > thought the list might want to know, this has been picked up by a trade > > magazine, I got a copy of "interactive week" in sail mail today. (In my best > > sarcastic voice)As they say in Hollywood "Any Publicity is good as long as > > your spell the names right". Found a web version if any one wants a look. > > > > http://www.zdnet.com/intweek/stories/news/0,4164,2787146,00.html > > > > MJM > > > > database to make the filter happy > > Michael: > > I'd have never caught that but for the list. Thanks. Wonder what the audience > for that site is. Also, was particularly intrigued by the "Portal out of the > Box" note. I thought Progress' only integration with MySQL was Gemini. Clearly > MySQL + Gemini != Portal. Hmmm! > > I'm (probably?) not going anywhere with this, but, perhaps someone should order > the mysql.org product and check for PHP integration under interesting licensing > (not GPL). Not the same licensing as Apache, which could be integrated in > almost anything non-GPL, but makes the ears perk up. > > My vote's for Monty and MySQL AB. That's the server I use and will continue to > do so. > > Best Regards, > Van > -- > = > Linux rocks!!! http://www.dedserius.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: database server upgrade
Basil Hussain wrote: > > Hi, > > > > We currently have a dedicate server for MySQL. The server is a dual > > > pentium III (1Ghz) with 2GB of RAM in it. It also has 2 18GB 10,000 > > > RPM drives in it arranged in a Raid 1 configuration (mirror). > > > Sometime in the next 3-6 months we will be maxing out its > > > capacity. (We were maxed out a few days ago, but we added 1GB of RAM > > > and cached some query results). The system is currently running > > > RedHat Linux 6.2. > > > > > > While there are some non-optimal queries and maybe some variable tuning > > > that we can and should do, we will need to upgrade at some point and its > > > not obvious to me what the upgrade path is. > > > > Until we have more of an idea where your system is stressed, it's hard > > to say. Are the CPUs maxed? I/O channels? RAM? > > If your system is getting stressed with disk I/O, then a good first step > could be to move to a different RAID configuration. As you're running RAID > 1, when writing data, each bit of data has to be written to both drives. If > your environment involves a lot of INSERT queries, then it may be worth > adding another disk and moving up to RAID 5. Strange. My understanding was that RAID 5 was good for read bandwidth but that keeping the parity disk uptodate slowed it down for write bandwidth. > Alternatively, you might > consider foresaking redundancy and going down to RAID 0. Also, are you > running hardware or software RAID? AMI Megaraid hardware. > If software, getting a dedicated RAID > card will lessen the load on your CPUs. > > > > The axes of expansion I see are: > > > 1) CPU speed (2 GHz processors?) > > > 2) # of CPUs (quad processor, 8 processors?) > > > 3) Multiple machines (replication) > > > 4) More memory (current system maxes out at 4GB) > > > 5) Different CPUs (SPARC, Alpha, IBM Power, HP-PA, Itanium) > > > 6) Faster disks (15,000 RPM) > > > 7) More disks (striping, different databases/tables on > > different disks, > > > MySQL striping) > > > 8) Switch some high contention tables to InnoDB, BDB or > > Gemini to avoid > > > lock contention > > > 9) Optimize server variables > > > > > > Which approach or combination of approaches is likely to double > > > (quadruple?) our throughput at the best price performance? I have > > > attached some info to help characterize our usage. > > > > Replication. You can do it with less expensive hardware. You'll get > > good performance and probably be able to scale farther wit it. Of > > course, you'll want to look at #9 before spending any money. And try > > to get an idea of where your contention for resources is today. > > Yes, you should definitely look at option #9 first. Here's a few pointers to > some things that immediately spring off the screen at me: > > | Open_tables | 1296 | > | Open_files | 2180712| > | Open_streams | 0 | > | Opened_tables| 1277057| > | table_cache | 2024 | > > Your Opened_tables figure is quite large, which means you are incurring > extra I/O penalties as tables have to be constantly opened and closed. You > should try increasing your table_cache size. > > | Slow_launch_threads | 1 | > | Threads_cached | 0 | > | Threads_created | 346157 | > | thread_cache_size| 0 | > > Slow_launch_threads should never be more than zero. And, seeing as your > configured slow launch yardstick time is 2 seconds, this indicates you may > be starting to have a bottleneck here. You should trying setting a > thread_cache_size of something like 32 - maybe higher. > > | Table_locks_immediate| 27157119 | > | Table_locks_waited | 58498 | > | Key_read_requests| 1535872968 | > | Key_reads| 5560163| > > This is good. Table locks that had to be waited for are less than 1% of > total locks. You don't seem to have too much of a problem with lock > contention. Also, your ratio of key reads/requests is way less than 0.01, so > no general problems with index usage on your queries. > > | Created_tmp_disk_tables | 415975 | > | tmp_table_size | 2097144| > > Created_tmp_disk_tables could probably be a little lower. Try increasing > your tmp_table_size memory figure to lessen the number of temp tables > written to disk - 2Mb is probably quite small if you're shuffling large > amounts of data. > > As for replication, there could be a couple of sticking points with this > strategy that you may need to overcome. The first is whether your client > applications (be they web scripts, custom apps, whatever) can easily be > re-programmed to support distributing their SQL query load amongst several > servers. Secondly, if you are chucking large amounts of data around and your > servers are replicating it all, your networking may not be up to scratch. If > you go for replication you sho
Re: REPLACE single value
Sorry, I didn't use replace yet, I always work with update which
is what I want. Are you sure you want to use replace? See the
differences in the manual. The syntax for update is
UPDATE table_name
SET col1 = '$val1',
col2 = '$val2',
col3 = '$val3'
WHERE primKey = '$id'
or something -- you get the idea.
> REPLACE contacts (access) VALUE ('P') WHERE id=x
--
Herzlich
Werner Stuerenburg
_
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
-
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: concept of users connecting from different host
> i don't understand what does connecting from different host mean? Where do you have this problem? > 1. does it mean connecting from a different pc to the pc hosting the mysql > server through telnet? Why would you like to do that? looks like a masochistic approach to me, under normal circumstances. > 2. connecting from the same pc hosting the mysql server and specifying the > host when connecting? Which would be localhost then, right? > 3. running mysql client on a local computer and connecting to the pc hosting > the mysql server. how do you connect in thisway then? Well, i do it through the browser as I use mysql on my server. in this approach, the connection is done through a cgi program running on the server, in my case php. in other words, I use php functions to connect to mysql, which means that the program connects to localhost. If I would like to connect, say, from your program on one machine to a database on another machine, then you will have to address that machine directly, for example with the IP number of that machine. You will have to insert a new user in the mysql database. Example: I manage my server databases through phpMyAdmin. This is a server based program, so it connects like all other server stuff through localhost. No problem. Recently, I downloaded mysqlfront (http://www.mysqlfront.de/). I liked that program very much, but as it runs as a standalone program on my client machine, it can connect to the database on that machine as localhost, but not to the web server. In order to do that, I would have to set up a new user and such. Another example. In addition to my own project, I have several customer projects. When I wanted to integrate data from a customer into my own project, I couldn't connect to the database of the customer directly because the ISP wouldn't allow it. When we moved to our own dedicated server, I moved the site of this customer to this machine, too. As I was master of this machine now, I could have realized my original plan to tap into the live data of this customer. In this case, both databases would reside on localhost. But it turned out that we had some problems. Now we moved our project to a separate dedicated server. As I am master of both machines, I will have to introduce the machine address of our project into the user table of the machine with the customer's data to realize my original plan. -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: database server upgrade
Jeremy Zawodny wrote: > > On Thu, Jul 19, 2001 at 04:21:05PM -0700, Mike Wexler wrote: > > > > We currently have a dedicate server for MySQL. The server is a dual > > pentium III (1Ghz) with 2GB of RAM in it. It also has 2 18GB 10,000 > > RPM drives in it arranged in a Raid 1 configuration (mirror). > > Sometime in the next 3-6 months we will be maxing out its > > capacity. (We were maxed out a few days ago, but we added 1GB of RAM > > and cached some query results). The system is currently running > > RedHat Linux 6.2. > > > > While there are some non-optimal queries and maybe some variable tuning > > that we can and should do, we will need to upgrade at some point and its > > not obvious to me what the upgrade path is. > > Until we have more of an idea where your system is stressed, it's hard > to say. Are the CPUs maxed? I/O channels? RAM? At 1GB the RAM was stressed. Paged like crazy. I don't have enough experience under load since the upgrade to tell where the current stress point is, but it does appear to be much better balanced now. > > > The axes of expansion I see are: > > 1) CPU speed (2 GHz processors?) > > 2) # of CPUs (quad processor, 8 processors?) > > 3) Multiple machines (replication) > > 4) More memory (current system maxes out at 4GB) > > 5) Different CPUs (SPARC, Alpha, IBM Power, HP-PA, Itanium) > > 6) Faster disks (15,000 RPM) > > 7) More disks (striping, different databases/tables on different disks, > > MySQL striping) > > 8) Switch some high contention tables to InnoDB, BDB or Gemini to avoid > > lock contention > > 9) Optimize server variables > > > > Which approach or combination of approaches is likely to double > > (quadruple?) our throughput at the best price performance? I have > > attached some info to help characterize our usage. > > Replication. You can do it with less expensive hardware. You'll get > good performance and probably be able to scale farther wit it. Of > course, you'll want to look at #9 before spending any money. And try > to get an idea of where your contention for resources is today. I already increased table_cache from 128 to 2048. Which helped. And last night I increase key_buffer from 16MB to 64MB. Maybe it should be even larger? If I use replication, I guess I should have all the updates go to a master server and distribute the queries to the slaves? > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW > > MySQL 3.23.29: up 33 days, processed 263,569,017 queries (89/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: Bug report: FULLTEXT index corrupts the index with too many TEXT fields
Take a look at : http://www.mysql.com/doc/I/n/Installing_source_tree.html - Original Message - From: "Simon Green" <[EMAIL PROTECTED]> To: "'Fournier Jocelyn [Presence-PC]'" <[EMAIL PROTECTED]>; "Carsten Gehling" <[EMAIL PROTECTED]>; "Sergei Golubchik" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 2:55 PM Subject: RE: Bug report: FULLTEXT index corrupts the index with too many TEXT fields > Hi > Where can we get V4.0? > > Thanks Simon > > -Original Message- > From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > Sent: 20 July 2001 12:30 > To: Carsten Gehling; Sergei Golubchik > Cc: [EMAIL PROTECTED] > Subject: Re: Bug report: FULLTEXT index corrupts the index with too many > TEXT fields > > > Hi, > > I've just tested with MySQL 4.0, no error, but strange result : > > mysql> insert into visitkort (kategori_id) values (108); > Query OK, 1 row affected (0.00 sec) > > mysql> update visitkort set navn = 'test5' where id = last_insert_id(); > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > mysql> update visitkort set tekst1 = 'bla bla' where id = last_insert_id(); > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > The latest update seems to have been successful, but if you look at the > table you can see the tekst1 field has not been updated : > > ++-+---+---+-++-+-+---+- > -+-+-+---+--+--+-+ -- > --++++ > | id | kategori_id | aktiv | navn | adresse | postnr | tlf | fax | email | > password | url | beskrivelse | visitkort | skabelon | logo | billede | > tekst1 | tekst2 | tekst3 | tekst4 | > ++-+---+---+-++-+-+---+- > -+-+-+---+--+--+-+ -- > --++++ > | 1 | 108 | 0 | test5 | || | | | > | | | 0 |0 |0 | 0 || > ||| > ++-+---+---+-++-+-+---+- > -+-+-+---+--+--+-+ -- > --++++ > > Regards, > > Jocelyn Fournier > Presence-PC > > - Original Message - > From: "Carsten Gehling" <[EMAIL PROTECTED]> > To: "Sergei Golubchik" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Friday, July 20, 2001 12:06 PM > Subject: Re: Bug report: FULLTEXT index corrupts the index with too many > TEXT fields > > > > I'm going to Spain today and cannot respond to any questions in the next > > week. I was going to wait with this until I got home again, but what the > > heck ;-) > > > > Run the following script through your MySQL on an empty database with > > > > mysql -uusername -ppassword dbname > > > and the last command should produce the following error: > > > > ERROR 1034 at line 31: Incorrect key file for table: 'visitkort'. Try to > > repair it > > > > I've let 3 people besides myself test it, and they all get the same error. > > It has now been tested on: > > > > Win2k server SP2, MySQL 3.23.32 > > > > Win2k server SP2, MySQL 3.23.39 > > > > Win2k Pro (Danish), MySQL 3.23.33 (normal version) > > > > Win2k Pro SP2 (English), MySQL 3.23.38-MAX > > > > Debian GNU/Linux, MySQL 3.23.39 (bniary .deb package) > > > > Some other Linux (didn't get the distro name), MySQL 3.23.39 > > > > All of the above produces the error. So something must be wrong. > > > > Sincerily, > > - Carsten > > > > Here's the script: > > > > ## > > CREATE TABLE `visitkort` ( > > `id` int(10) unsigned NOT NULL auto_increment, > > `kategori_id` int(10) unsigned NOT NULL default '0', > > `aktiv` tinyint(3) unsigned NOT NULL default '0', > > `navn` varchar(60) NOT NULL default '', > > `adresse` varchar(150) NOT NULL default '', > > `postnr` varchar(5) NOT NULL default '', > > `tlf` varchar(20) NOT NULL default '', > > `fax` varchar(20) NOT NULL default '', > > `email` varchar(60) NOT NULL default '', > > `password` varchar(20) NOT NULL default '', > > `url` varchar(150) NOT NULL default '', > > `beskrivelse` varchar(200) NOT NULL default '', > > `visitkort` tinyint(3) unsigned NOT NULL default '0', > > `skabelon` tinyint(3) unsigned NOT NULL default '0', > > `logo` tinyint(3) unsigned NOT NULL default '0', > > `billede` tinyint(3) unsigned NOT NULL default '0', > > `tekst1` text NOT NULL, > > `tekst2` text NOT NULL, > > `tekst3` text NOT NULL, > > `tekst4` text NOT NULL, > > PRIMARY KEY (`id`), > > FULLTEXT KEY `ft` > > (`navn`,`beskrivelse`,`tekst1`,`tekst2`,`tekst3`,`tekst4`) > > ) TYPE=MyISAM; > > > > insert into visitkort (kategori_id) values (108); > > > > update visitkort set navn = 'test5' where id = last_insert_id(); > > > > update visitkort set
Re: Left Join very sloooowwww..
I added comments and a recipe to my debug function collection. You can download at the address http://pferdezeitung.de/php3/toosDebug.zip > Well, I don't know about David, but I'd be very interested in your > stopwatch program table, sql -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: Bug report: FULLTEXT index corrupts the index with too many TEXT fields
Hi Where can we get V4.0? Thanks Simon -Original Message- From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] Sent: 20 July 2001 12:30 To: Carsten Gehling; Sergei Golubchik Cc: [EMAIL PROTECTED] Subject: Re: Bug report: FULLTEXT index corrupts the index with too many TEXT fields Hi, I've just tested with MySQL 4.0, no error, but strange result : mysql> insert into visitkort (kategori_id) values (108); Query OK, 1 row affected (0.00 sec) mysql> update visitkort set navn = 'test5' where id = last_insert_id(); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update visitkort set tekst1 = 'bla bla' where id = last_insert_id(); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 The latest update seems to have been successful, but if you look at the table you can see the tekst1 field has not been updated : ++-+---+---+-++-+-+---+- -+-+-+---+--+--+-+-- --++++ | id | kategori_id | aktiv | navn | adresse | postnr | tlf | fax | email | password | url | beskrivelse | visitkort | skabelon | logo | billede | tekst1 | tekst2 | tekst3 | tekst4 | ++-+---+---+-++-+-+---+- -+-+-+---+--+--+-+-- --++++ | 1 | 108 | 0 | test5 | || | | | | | | 0 |0 |0 | 0 || ||| ++-+---+---+-++-+-+---+- -+-+-+---+--+--+-+-- --++++ Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Carsten Gehling" <[EMAIL PROTECTED]> To: "Sergei Golubchik" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 12:06 PM Subject: Re: Bug report: FULLTEXT index corrupts the index with too many TEXT fields > I'm going to Spain today and cannot respond to any questions in the next > week. I was going to wait with this until I got home again, but what the > heck ;-) > > Run the following script through your MySQL on an empty database with > > mysql -uusername -ppassword dbname > and the last command should produce the following error: > > ERROR 1034 at line 31: Incorrect key file for table: 'visitkort'. Try to > repair it > > I've let 3 people besides myself test it, and they all get the same error. > It has now been tested on: > > Win2k server SP2, MySQL 3.23.32 > > Win2k server SP2, MySQL 3.23.39 > > Win2k Pro (Danish), MySQL 3.23.33 (normal version) > > Win2k Pro SP2 (English), MySQL 3.23.38-MAX > > Debian GNU/Linux, MySQL 3.23.39 (bniary .deb package) > > Some other Linux (didn't get the distro name), MySQL 3.23.39 > > All of the above produces the error. So something must be wrong. > > Sincerily, > - Carsten > > Here's the script: > > ## > CREATE TABLE `visitkort` ( > `id` int(10) unsigned NOT NULL auto_increment, > `kategori_id` int(10) unsigned NOT NULL default '0', > `aktiv` tinyint(3) unsigned NOT NULL default '0', > `navn` varchar(60) NOT NULL default '', > `adresse` varchar(150) NOT NULL default '', > `postnr` varchar(5) NOT NULL default '', > `tlf` varchar(20) NOT NULL default '', > `fax` varchar(20) NOT NULL default '', > `email` varchar(60) NOT NULL default '', > `password` varchar(20) NOT NULL default '', > `url` varchar(150) NOT NULL default '', > `beskrivelse` varchar(200) NOT NULL default '', > `visitkort` tinyint(3) unsigned NOT NULL default '0', > `skabelon` tinyint(3) unsigned NOT NULL default '0', > `logo` tinyint(3) unsigned NOT NULL default '0', > `billede` tinyint(3) unsigned NOT NULL default '0', > `tekst1` text NOT NULL, > `tekst2` text NOT NULL, > `tekst3` text NOT NULL, > `tekst4` text NOT NULL, > PRIMARY KEY (`id`), > FULLTEXT KEY `ft` > (`navn`,`beskrivelse`,`tekst1`,`tekst2`,`tekst3`,`tekst4`) > ) TYPE=MyISAM; > > insert into visitkort (kategori_id) values (108); > > update visitkort set navn = 'test5' where id = last_insert_id(); > > update visitkort set tekst1 = 'bla bla' where id = last_insert_id(); > ## > > > > > > - > 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 > 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/
RE: mysql manager
You can use Windows explorer to create new database (Not tables). Just create new subfolder of mysql\data on your system. -Original Message- From: Donald Dahlman [mailto:[EMAIL PROTECTED]] Sent: petak, 20. jul 2001 22:03 To: [EMAIL PROTECTED] Subject: mysql manager does anyone have a program to create and manage a database with out being connected to a server Martin Jeremic [EMAIL PROTECTED] http://solair.eunet.yu/~martinj/ http://jsoft.webjump.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
problem starting mysqld
Hi all When I start mysqld using /etc/rc.d/init.d/mysqld start it starts ok alhtough when I say mysqld status I get the following error : mysqld dead substatus locked i cannot connect to the server at all please help thanks Barry
help! can't load libmysqlclient.so.10
I've installed mysql and PHP4 according to instructions at mysql.com, but I'm getting the following error when I try to start httpd: Cannot load /etc/httpd/modules/libphp4.so into server: libmysqlclient.so.10: cannot open shared object file: No such file or directory [FAILED] So, it's locating libphp4.so ok, but can't find libmysqlclient.so.10, which actually located at: /usr/local/mysql/lib/mysql/libmysqlclient.so.10 === Michael Taney [EMAIL PROTECTED] 802 748-6311 voc 802 748-6322 fax - 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 keeps corrupting
This is the feedback from repair: mysql> repair table hits -> ; +--++--+ -+ | Table| Op | Msg_type | Msg_text | +--++--+ -+ | counter.hits | repair | info | Wrong bytesec: 17-0-197 at 1097180; Skipped | | counter.hits | repair | warning | Number of rows changed from 8628 to 8627| | counter.hits | repair | status | OK | +--++--+ -+ 3 rows in set (0.71 sec) This has happened at least once a day for the past three days (ever since the hit counter got popular. This is the version: mysql Ver 11.15 Distrib 3.23.38, for apple-darwin1.3.3 (powerpc) And some other stats: Threads: 6 Questions: 2007 Slow queries: 0 Opens: 14 Flush tables: 1 Open tables: 4 Queries per second avg: 0.055 As you can see, it's barely ticking over compared to some installs I've seen. Any suggestions where to look for trouble here? Walter - 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
SV: Anyone knows what's wrong with this INSERT sentence?
INSERT INTO table1 (email) SELECT email FROM table1, table2 WHERE table1.email <> table2.email; the select is a "new" query, you have to join table1 and table2... Regards, Johan Nilsson Software Developer BeCon Mobile Internet AB, Sweden Web: http://www.beconmobile.com E-Mail: [EMAIL PROTECTED] Office: +46 457 44184 Cellular: +46 709 798897 > -Ursprungligt meddelande- > Fran: Martin Cabrera Diaubalick [mailto:[EMAIL PROTECTED]] > Skickat: den 20 juli 2001 13:14 > Till: [EMAIL PROTECTED] > Kopia: [EMAIL PROTECTED] > Amne: Anyone knows what's wrong with this INSERT sentence? > > > Hello all, > > I have this two tables : table1,table2 . I would like to include > all emails from table2 into table1 removing duplicates. > > INSERT INTO table1 (email) SELECT email from table2 where > table1.email <> table2.email; > > MYSQL says: ERROR 1109: Unknown table 'table1' in where clause > > Both tables exist, what's wrong? > > TIA > > Regards > > > > - > 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
