Penalty for non-null defaults ?
Is there any storage / performance penalty for specifying '' or 0 as default values, as opposed to allowing null's in the table structure? sql, query Thanks, Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 & os x server
Hello, i'm new on unix like sytem and on mysql, and i try to install mysql 3.23.33 on MACos x server. I downloaded the archive, decompressed it into serverOSX/usr/local/ with the commend tar -zxvf mysql-3.23.33-apple-rhapsody5.5-powerpc .tar.gz i got a folder named "mysql-3.23.33-apple-rhapsody5.5-powerpc",i created a link to this folder with ln -s mysql-3.23.33-apple-rhapsody5.5-powerpc mysql, i added a group mysql, added a user mysql at this group (using the network manager, not the shell), but when i wanted to run the mysql_install_db script, it wrote in a shell window named 'Dead Terminal': my_print_defaults: not found Didn't find ./bin/mysqld You should do a 'make install' before executing this script [Process exited - exit code 1] can someone help me? thank you, charlie EISSEN
vb.net & aggregate MySQL functions
I'm having problems with vb.net and MySQL, only on reading records returned with requests for functions, i.e., such as the following SELECT max(field1) AS MaxValue FROM Table1 or SELECT concat( field1, ' ', field2) as FullName FROM Table1 The queries work fine when executed directly from mysqlfront. It is only when attempting to read them from vb using oledb datareader() that present a problem. Any queries not using functions work fine. dataReader claims no fields have been returned. I'm using MyOLEDB version 3.00 TIA, Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL stmt help request
I have a need to retrieve a subset of records as follows: Database structure: Field0IntegerAutonumber Field1IntegerUnique Field2Integernot unique where the starting record would be selected by Field1 (an indexed field of unique values). The ending record required would be the first succeeding record where the value of Field2 is equal to the value of Field2 in the 'starting' record. Field2 is NOT unique, and is not in any particular order. The result would be ordered by Field0 (the autonumber field) Is this possible? TIA, Charlie MySQL Query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Impossible SQL ???
I can't seem to come up with a solution to the following SQL query, and have a project dependent on a solution. I'd rather not load the whole database and then parse it in the code. With the following Database structure: Field0 Integer Autonumber Field1 Integer Unique Field2 Integer not unique where the starting record would be selected by Field1 (an indexed field of unique values). The ending record required would be the first succeeding record where the value of Field2 is equal to the value of Field2 in the 'starting' record. Field2 is NOT unique and not ordered. The result would be ordered by Field0 (the autonumber field) Is this possible? MySQL Query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Impossible SQL ???
Thanks for the reply, but it isn't quite what is needed. The problem is that I need all the records between the two occurances of identical values in field 2, with no records which occur before or after those two occurances. For example, the following table with 3 fields: 11 10 24 99 32 99 41 98 54 88 62 97 If the parameter for the second column is 4, I would need to retrieve records 2, 3, and 4. If the query needs, for simplicity, to return record 5, that could be handled by the program. Thanks for your thoughts!! Charlie > I can't seem to come up with a solution to the following SQL query, and have > a project dependent on a solution. I'd rather not load the whole database > and then parse it in the code. > > With the following Database structure: > Field0 Integer Autonumber > Field1 Integer Unique > Field2 Integer not unique > > where the starting record would be selected by Field1 (an indexed field of > unique values). > > The ending record required would be the first succeeding record where the > value of Field2 is equal to the value of Field2 in the 'starting' record. > > Field2 is NOT unique and not ordered. > The result would be ordered by Field0 (the autonumber field) > > Is this possible? I am not sure if I understood your mail correctly but you can try this: If you've not already done it, create an UNIQUE index for field1 and an INDEX for field2 and then try the query with a self-join on your table. SELECT foo1.* FROM yourtable foo1, yourtable foo2 WHERE foo1.field0 = foo2.field0 AND foo1.field2 = foo2.field2 AND foo1.field1 = your_starting_value ORDER BY foo1.field0 ASC /h - Before posting, please check: http://www.mysql.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/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqld won't start
From: root To: mysql@lists.mysql.com Subject: mysqld won't start >Description: In an attempt to upgrade to MySQL 5.0.24 it was necessary to use "rpm -e ..." to remove version 5.0.22 then "rpm -U ..." to install the shared,server and client modules. When I do a "rpm -qa" it shows that the new modules have been installed. When the system tries to start mysqld the following message is reported: Starting MySQLCouldn't find MySQL manager or server [FAILED] When attempting to start with "# mysqld_safe start" I got this report: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid [date] [time] mysqld ended >How-To-Repeat: >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: email >Synopsis:mysqld won't start after upgrading to 5.0.24 >Severity:critical >Priority:high >Category:mysql >Class:support<[ sw-bug | doc-bug | change-request | support ] (one line)> >Release:mysql-5.0.24-standard (MySQL Community Edition - Standard (GPL)) >C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >C++ compiler: gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >Environment: System: Linux DEVBOX 2.6.9-34.0.2.EL #1 Fri Jun 30 10:23:19 EDT 2006 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.5/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.5 20051201 (Red Hat 3.4.5-2) Compilation info: CC='gcc' CFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' CXX='gcc' CXXFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Mar 24 12:49 /lib/libc.so.6 -> libc-2.3.4.so -rwxr-xr-x 1 root root 1438668 Feb 8 19:12 /lib/libc-2.3.4.so -rw-r--r-- 1 root root 2415476 Feb 8 18:50 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Feb 8 18:02 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-server-suffix=-standard' '--without-embedded-server' '--with-innodb' '--with-archive-storage-engine' '--without-bench' '--with-zlib-dir=bundled' '--with-big-tables' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic' '--prefix=/' '--with-extra-charsets=complex' '--with-yassl' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' 'CXXFLAGS=-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' 'CXX=gcc' 'LDFLAGS=' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
find in list
Hello, I want to do a query where I only gt the results by an numeric value: select * from MyTable where 1 IN someFieldInMyTable I know this query doesn't work, but maybe anyone knows what I mean. 1 can be a value betwenn 1 and 23 someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21 br Charlie
AW: AW: find in list
Hello Michael,
thx, I know normalisation.
BUT normalisation isn't always the best (fastest) way to store, or select
the datas, this is why I don't use it most time.
Often I'm testing my projects with normalisation and without and my last
very big problem with big select statements is very fast without
normalisation and with normalisation it was very slow.
br
Charlie
> -Ursprüngliche Nachricht-
> Von: Michael Stassen [mailto:[EMAIL PROTECTED]
> Gesendet: Donnerstag, 17. August 2006 05:00
> An: mysql@lists.mysql.com
> Cc: Mike van Hoof; Charlie Schaubmair
> Betreff: Re: AW: find in list
>
> Charlie Schaubmair wrote:
> > Hello,
> >
> > I want to do a query where I only gt the results by a
> numeric value:
> >
> > select * from MyTable where 1 IN someFieldInMyTable > I
> know this query doesn't work, but maybe anyone knows what I mean.
> >
> > 1 can be a value betwenn 1 and 23
> > someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21
>
> Mike van Hoof wrote:
> > try:
> >
> > SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
> > This will also give you results like 21 > > What I
> usally do in these cases is build the values like this:
> >
> > [1][2][3][21]
> >
> > And then the query:
> >
> > SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
> >
> > Mike
>
> Charlie Schaubmair wrote:
> > Hello Mike,
> >
> > thx, but isn't there another way?
>
> Mike van Hoof wrote:
> > maybe there is, but i don't know why... because when you
> do a like query > it also finds the 21 when you do a like on the 1.
> > what you maybe can do (not tested) is:
> >
> > SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield
> FROM MyTable > WHERE wherefield='%,1,%'
> >
> > Now your someFieldInMyTable is CONCATed to
> ,1,2,3,4,5,6,9,21, and over > that value the where clause is done...
>
> Mike van Hoof wrote:
> > Sorry, query was wrong... has to be:
> >
> > SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield
> FROM MyTable > WHERE wherefield LIKE '%,1,%'
>
> You've just discovered why this is not the way to store a
> list of attributes.
> Stuffing multiple values in a single cell is a bad idea. The
> correct way to implement this is to store the attributes in a
> separate table, one per row.
> That is, instead of
>
>MyTable
>===
>MyTable_id other columns attributes
>1 ... 1,2,3,4,5,6,9,21
>2 ... 5,7,13
>
> you would do this:
>
>MyTable_attributes
>===
>MyTable_id other columns
>1 ...
>2 ...
>
>MyTable_attributes
>==
>MyTable_id attribute
>1 1
>1 2
>1 3
>1 4
>1 5
>1 6
>1 9
>1 21
>2 5
>2 7
>2 13
>
> Then finding rows in MyTable which have attribute "1" becomes trivial:
>
>SELECT m.*
>FROM MyTable m
>JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
>WHERE ma.attribute = 1;
>
> With a UNIQUE index on (attribute, MyTable_id) in
> MyTable_attributes, this will be very quick.
>
>
>
> That said, you can find what you want with your current
> schema using the
> FIND_IN_SET() function
> <http://dev.mysql.com/doc/refman/4.1/en/string-functions.html>.
>
>SELECT *
>FROM MyTable_attributes
>WHERE FIND_IN_SET(1, someFieldInMyTable);
>
> This works so long as the values in someFieldInMyTable are
> separated by commas.
> If you switch to some other separator, such as enclosing
> attributes in brackets, it won't work.
>
> Note that no index on someFieldInMyTable can be used for this
> query, however, so it requires a full-table scan.
>
> Michael
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set convert?
Hey all, got a simple question: Is there a quick way to convert all the data in my database to a specific character set? I want to convert all the text to UTF-8. -Charlie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode
Aloa, ich muß in einer Webanwendung jetzt auch noch Daten von verschiedenen Ost-Ländern wie zB Bulgarien, Polen, Tschechien usw. integrieren. Das hätte ich mir eigentlich alles recht einfach vorgestellt, aber leider ist meine DB nach einer Umstellung von latin1 auf utf8 sehr langsam und die Suche nach deutschen Umlauten funktioniert dann auch nicht mehr, außer man stellt die Spalten auf uft8_bin um, aber dann müssen alle Suchausdrücke case-sensitive sein, was mir auch nichts bringt. UND mit sehr langsam meine ich jetzt einen Faktor x ca. 10!!! Also nun meine Fragen: Wie schaffe ich es mit MySQL, damit ich verschiedene Sprachen, bzw. Zeichensätze, in einer Tabelle halten kann und diese dann auch noch richtig und schnell durchsuchen kann? Es geht um eine Tabelle mit 15 Spalten und 5 Spalten davon werden sehr oft durchsucht und diese Spalten sind: 2x tinyint, 1x Varchar, 1x Text und 1x Longtext Die Tabelle beinhaltet ca. 60.000 Datensätze. Ist da MSSQL, oder eine andere DB etwa besser geeignet? Sehr wichtig ist für mich die Performance! thx+lg Charlie
AW: Unicode
Hi, Ok, thx for your help, it's a little bit faster, but also much too slow. And what can I do that I can find such characters like 'ä' with a select like: SELECT * FROM myTable WHERE myField like '%a%' in the unicode-field? br Charlie > -Ursprüngliche Nachricht- > Von: Paul McCullagh [mailto:[EMAIL PROTECTED] > Gesendet: Montag, 11. September 2006 16:23 > An: Charlie Schaubmair > Cc: mysql@lists.mysql.com > Betreff: Re: Unicode > > Hi Charlie, > > The performance problem may be due to using UTF-8 instead of > unicode. > Try declaring your text column as: > > my_text VARCHAR(...) UNICODE > > I am guessing, but it may be that the search is slow because > MySQL has to convert UTF-8 to UNICODE before doing the comparison. > > BTW, don't use CHAR(...) UNICODE, unless the text field is > short, because there is a bug with the removal of trailing > spaces in the index, that makes the index file much larger > than necessary. > > - Paul > > On Sep 11, 2006, at 11:58 AM, Charlie Schaubmair wrote: > > > Aloa, > > > > ich muß in einer Webanwendung jetzt auch noch Daten von > verschiedenen > > Ost-Ländern wie zB Bulgarien, Polen, Tschechien usw. integrieren. > > Das hätte ich mir eigentlich alles recht einfach vorgestellt, aber > > leider ist meine DB nach einer Umstellung von latin1 auf utf8 sehr > > langsam und die Suche nach deutschen Umlauten funktioniert > dann auch > > nicht mehr, außer man stellt die Spalten auf uft8_bin um, aber dann > > müssen alle Suchausdrücke case-sensitive sein, was mir auch nichts > > bringt. > > UND mit sehr langsam meine ich jetzt einen Faktor x ca. 10!!! > > > > Also nun meine Fragen: > > Wie schaffe ich es mit MySQL, damit ich verschiedene Sprachen, bzw. > > Zeichensätze, in einer Tabelle halten kann und diese dann auch noch > > richtig und schnell durchsuchen kann? > > Es geht um eine Tabelle mit 15 Spalten und 5 Spalten davon > werden sehr > > oft durchsucht und diese Spalten sind: > > 2x tinyint, 1x Varchar, 1x Text und 1x Longtext Die Tabelle > beinhaltet > > ca. 60.000 Datensätze. > > > > Ist da MSSQL, oder eine andere DB etwa besser geeignet? > > Sehr wichtig ist für mich die Performance! > > > > thx+lg > > Charlie > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql_config giving wrong location of libraries for AMD64
First, here's where they are: [EMAIL PROTECTED] /]# find / -name libmysql* /usr/lib64/libmysqlclient_r.so.14 /usr/lib64/libmysqlclient.so.14.0.0 /usr/lib64/libmysqlclient.so.14 /usr/lib64/libmysqlclient_r.so /usr/lib64/mysql/libmysqlclient.la /usr/lib64/mysql/libmysqlclient_r.la /usr/lib64/mysql/libmysqlclient_r.a /usr/lib64/mysql/libmysqlclient.a /usr/lib64/libmysqlclient_r.so.14.0.0 /usr/lib64/libmysqlclient.so But mysql_config's output is wrong: [EMAIL PROTECTED] /]# mysql_config --libmysqld-libs -L/usr/lib -lmysqld -lpthread -lcrypt -lnsl -lm -lpthread -lrt -lcrypt -lnsl -lm -lz I used the RPM install of 4.1.8 MySQL of Server, Devel, Shared, and Client. Anyone have any ideas? -Charlie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Just a small newbie question
G'Evening, Im extremely new to MySQL. Im sitting here using a self teach book to try to guide my way through it. Anyways, the book is discussing adding users and setting priveledges. Now the book is vague on how to do so, but I believe I use the bin/mysql.exe file (Im on windows obviously). Problem is when I go to it, all it does and open and close real fast. Am I doing something wrong? Thanks, sorry for the noob question. _ Proud member of www.Dragonmount.com The Largest -Wheel of Time- Community on the Internet! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Just a small newbie question
Ok well I reinstalled again and it seems to be working ok now. Thanks anyways ;) --- "Matt W" <[EMAIL PROTECTED]> wrote: Hi Charlie, Don't worry, all questions are OK. :-) Instead of trying to double-click mysql.exe, open your Command Prompt and, assuming the mysql\bin directory isn't in your PATH, navigate to to mysql\bin directory and run mysql from there or specify the full path. e.g. C:\>cd \mysql\bin C:\mysql\bin>mysql -OR- C:\>C:\mysql\bin\mysql (Sorry if you already know how to do that. ;-)) And then when mysql quits, the window won't close but will stay there and you can see what the error message is. Tell us what that error is. Hope that helps. Matt ----- Original Message - From: "Charlie Brewer" Sent: Monday, September 22, 2003 12:05 AM Subject: Just a small newbie question > G'Evening, > > Im extremely new to MySQL. Im sitting here using a self teach book to try to guide my way through it. Anyways, the book is discussing adding users and setting priveledges. Now the book is vague on how to do so, but I believe I use the bin/mysql.exe file (Im on windows obviously). Problem is when I go to it, all it does and open and close real fast. Am I doing something wrong? Thanks, sorry for the noob question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Proud member of www.Dragonmount.com The Largest -Wheel of Time- Community on the Internet! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Noob Question: Error 1046
Evenin', Sorry to send noob questions, I know how annoying they can be, but we need help too ;) Anyways, Im using a self teach book to teach myself PHP, MySQL and Apache. The book is getting into MySQL and basic commands. Anyways, they gave an example, and I typed it in as shown in the book, but I get an error. I took a screenshot (I thought that would be easier) and posted it here: http://hostultra.org/brewer/mysql.jpg Any help would be very much so appreciated, thanks.
Re: Noob Question: Error 1046
Ah apparently this silly book skipped that step. So when I do databases I get "mysql" and "nuke". And figuring out how to make a new one wasnt very hard heh. Thanks, plenty more noob questions to come im sure :) - Original Message - From: "Parker Morse" <[EMAIL PROTECTED]> To: "Charlie Brewer" <[EMAIL PROTECTED]> Sent: Thursday, September 25, 2003 3:09 PM Subject: Re: Noob Question: Error 1046 > On Thursday, Sep 25, 2003, at 15:12 US/Eastern, Charlie Brewer wrote: > > and basic commands. Anyways, they gave an example, and I typed it in > > as shown in the book, but I get an error. I took a screenshot (I > > thought that would be easier) and posted it here: > > > > http://hostultra.org/brewer/mysql.jpg > > [The image shows "Welcome to MySQL Monitor" etc., the first command is > a valid "CREATE TABLE" instruction, followed by "ERROR 1046: No > database selected"] > > In order to create a table, MySQL needs to know which database to put > it in. Since we can't see the command line you put in to execute, it's > tough to know if you've actually got databases available, but assuming > you do and you know which they are, you'd give the command > > mysql> use [database]; > > before your CREATE, so you're "in" the relevant database. > > If you don't know which databases you have available, try > > mysql> show databases; > > and see what happens. > > Like this (munged, obviously): > > [root bluebird]# mysql -h raven.example.com -u root -p > Enter password: > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 39944 to server version: 4.0.14-standard-log > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> show databases; > +---+ > | Database | > +---+ > | mysql | > | test | > +---+ > 7 rows in set (0.04 sec) > > mysql> use test; > Database changed > > pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql@lists.mysql.com
>Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:Charlie & >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-3.23.53 (FreeBSD port: mysql-server-3.23.53_4) >Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.53, for portbld-freebsd4.7 >on i386 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.53 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 hour 30 min 19 sec Threads: 1 Questions: 37 Slow queries: 1 Opens: 13 Flush tables: 2 Open tables: 0 Queries per second avg: 0.007 >Environment: System: FreeBSD radstats.srv.poptel.org.uk 4.7-RELEASE FreeBSD 4.7-RELEASE #0: Tue Dec 3 10:53:40 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/RADSTATS i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='cc' CXXFLAGS='-O -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1218496 Oct 9 13:43 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Nov 25 08:43 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 574916 Oct 9 13:43 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --without-debug --without-readline --without-bench --with-mit-threads=no --with-libwrap --with-low-memory '--with-comment=FreeBSD port: mysql-server-3.23.53_4' --program-prefix= --with-innodb --enable-assembler --with-berkeley-db --prefix=/usr/local i386-portbld-freebsd4.7 'CFLAGS=-O -pipe ' CXX=cc host_alias=i386-portbld-freebsd4.7 build_alias=i386-portbld-freebsd4.7 target_alias=i386-portbld-freebsd4.7 CC=cc 'CXXFLAGS=-O -pipe -felide-constructors -fno-rtti -fno-exceptions' Perl: This is perl, version 5.005_03 built for i386-freebsd mysql in free(): warning: recursive call - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 connection refused or lost
Hi, I have searched the archives and unfortunately could not find a solution to my problem. I have setup a machine as a mysql server and cannont connect to it remotely. When I try "telnet localhost 3306" all seems ok. When I try "telnet domain.com 3306" I get the message "Connection closed by foreign host", same goes when trying the IP address from the local box. If I try this as well from a remote box, I get "Connection closed by foreign host". I have setup a user for the remote box and granted all rights to this user, using the IP address of the remote box. I am not sure if the software firewall here is causing some problems or if it is something I am missing with MySQL. Best Wishes, Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 connection refused or lost (Update)
> When I try "telnet localhost 3306" all seems ok. When I try > "telnet domain.com 3306" I get the message "Connection closed by foreign > host", same goes when trying the IP address from the local box. If I try > this as well from a remote box, I get "Connection closed by foreign host". > I believe I have figured out most of the problem with the connectin refused. I still have a problem with lost connections from a remote box and trying to connect via the IP address locally. mysql -u sat -h domain.com test (works) mysql -u sat -h IPAddress test ERROR 2013: Lost connection to MySQL server during query When I try to connect to this box from another box which I have granted all rights, I get the error: ERROR 2013: Lost connection to MySQL server during query Any help will be greatly appreciated. Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 connection refused or lost (Update)
> >> > > When I try to connect to this box from another box which I have > granted all > > rights, I get the error: > > ERROR 2013: Lost connection to MySQL server during query > > > https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=77467 > > "lost connection ..." > [mysqld] > set-variable=thread_stack=256k > Thanks! Life is good again :) Best Wishes, Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MAX_ROWS limits
Hi, I am using MySql 3.23.47 for nt (W2000). The number of rows of my table on an NTFS file system is estimated to become around 6,000,000,000. I am trying to increase my default MAX_ROWS=4294967295 with ALTER TABLE test AVG_ROW_LENGTH=9 MAX_ROWS=60; Following this, SHOW TABLE STATUS still gives the original number for MAX_ROWS! Could someone help me to understand, if MAX_ROWS is limited to the maximum value of the integer, or do I need to change some other settings in order to go beyond this number? Thanks for any ideas! Charlie _ Chat with friends online, try MSN Messenger: http://messenger.msn.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
MAX_ROWS limits
Hi, I am using MySql 3.23.47 for nt (W2000). The number of rows of my table on an NTFS file system is estimated to become around 6,000,000,000. I am trying to increase my default MAX_ROWS=4294967295 with ALTER TABLE test AVG_ROW_LENGTH=9 MAX_ROWS=60; Following this, SHOW TABLE STATUS still gives the original number for MAX_ROWS! Could someone help me to understand, if MAX_ROWS is limited to the maximum value of the integer, or do I need to change some other settings in order to go beyond this number? Thanks for any ideas! Charlie _ Chat with friends online, try MSN Messenger: http://messenger.msn.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
GRANT TEMPORARY CREATE ...
Hi, My database users have problems with the missing sub-query feature of MySql that I would like to solve by letting them create temporary tables since their scope is a connection thread. However I cannot let them to start creating tables that are not temporary. Unfortunately I cannot find such privilege system currently supported by MySQL. Could anyone confirm that or suggest a solution? Thanks for any response! Charlie _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SLOW MULTI COLUMN INDEXES, ESPECIALLY FOR MERGE TABLES
Hi all, I seem to have difficulties efficiently using multiple column unique indexes. I notice major performance differences in the following example. Is this a known problem, or am I not doing the right thing? The following scenario takes for me 0.76 seconds: CREATE TABLE test(d_id INT, t_id INT, value VARCHAR(255)); CREATE UNIQUE INDEX test_idx on test(d_id, t_id); SELECT * FROM test WHERE d_id < 500 AND t_id < 500; This takes nearly 1 minute if I try to use MERGE tables (the index would still remain UNIQUE) The data in the test table has the following features: Both d_id and t_id values occure 2-3000 times (it't probably not good for the b-tree) It seems like first the query evaluates (d_id < 500) and slowly comes up with 500,000 results. Finally (t_id < 500) dramatically reduces the result set to 200. If I set PACK_KEYS = 1, and even rebuild the indexes, nothing really changes. I don't know how multiple column keys are handled, are they treated as one primary key or are they looked up separately, butif I combine the d_id and t_id keys in a new table to produce a primary key e.g. as (d_id*10,000,000+t_id) everything is fast (0.01 sec), (but wierd). Could anyone recommend a better solution in this case? Thanks for any comments! Charlie _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Comparing UNSIGNED and INDEXED columns with negative numbers
Hi All, Using MySQL server version 3.23.49-nt I get strange results when running the following very simple test. I am wondering if this is a bug, feature or perhaps a standart SQL behaviour?? CREATE TABLE test(id BIGINT UNSIGNED); CREATE INDEX test_idx on test(id); INSERT INTO test VALUES (0), (1), (2), (3); SELECT * FROM test WHERE id > -1; I get an empty result. With column type INT UNSIGNED, it's a little bit better; only the 0 is not included in the result set! However if I don't put an index on the column, everything seems to behave good. I recon it has something to do with casting (-1) to an unsigned value. Is (id > -1) a valid and supported comparison on that column after all??? Thanks for any answers! Charlie _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
No Warnings after changed data
Hi All,
I'd like to find out how I could convince mysql to generate warnings
whenever the data I want to insert is modified by the server.
E.G. CREATE TABLE test (value real);
INSERT INTO test VALUES ('a1');
I am programming in JAVA using JDBC.
Thanks for any answers!
Charlie
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: No Warnings after changed data
Hello, I think that my problem boils down to the unimplemented getWarnings() method in the mm. JDBC driver. If it was implemented, I could detect if the data was e.g. truncated. Can anybody help me find an implementation for this method? Thank you again, Charlie Hi. :I'd like to find out how I could convince mysql to generate warnings :whenever the data I want to insert is modified by the server. I believe there's no built-in way of doing that. I keep a checksum of a data stored. And when i want to write to that raw next time I just check the checksum (md5 digest) _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C Libraries: Commands out of sync...help
Hi, A program I recently re-written part of to use a MySQL database is having a lot of problems. The problems aren't very pronounced on my machine unless I run it under Rational Purify, but others report more problems. When doing mysql_query(&mysql,myquerystring), I am getting back randomly the errors "Commands out of sync", or "Lost connection to MySQL server during query". This happens within seconds from within Purify, so I don't think it's a time thing. Note Purify isn't complaining about memory access or leak errors, it just appears to do something to my program to make these errors more apparent (speed?) (after about 10 queries, but problems rarely at the same point) My program is a multithreaded, cross platform, C++ server application. I'm using a singleton Database class for access to the DB. On initialization, it connects to the DB. I was hoping that I could re-use just one connection for the lifetime of my server. To deal with the threads, all MySQL DB calls are protected within a common mutex. At first, with getting "Commands out of sync", I assumed I'd done something wrong with my mutexes but after review it appears I haven't. I also reviewed to make sure that I always called mysql_free_result(myresultset) after doing a query (SELECT, but not INSERT, UPDATE or DELETE), and within the mutex. The help files for Commands out of sync suggests I'm perhaps doing things out of order, but after review I can't find anything that might cause it...I just have 20+ different methods making queries and possibily examining and reparsing the results before returning. What strategy is recommended here? I'm sufficiently exasperated that it seems my only alternative is to change each call to do a mysql_init and real_connect for each database query. But this seems like a big performance hit to me. Or I could just attempt to reconnect, and reissue the query each time this happens (which seems often), but that is ugly (code-wise) and may not solve it, I'd rather fix the problem or the symptom entirely. But a possibility. Help, recommendations, please!? Maybe something I might be missing? Using MySQL client and server libraries 3.23.33. The other interesting note is that my application uses ACE libraries (http://www.cs.wustl.edu/~schmidt/ACE.html, very cool) to deal with platform specifics, threading, mutexes and other stuff...) Namespace collisions with ACE is why I'm not using the C++ libraries. I expect to make at least one query to the database every few minutes, so timeouts aren't really an issue. The errors have 99% been tested on, and noticed on, Windows 2000 Pro SP1 on x86 arch. TIA! Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Fulltext search
Lorang Jacques wrote: >Hello, >This is the last time I will post this thread. Apparently nobody knows any >solution to this : > >The Fulltext search only matches those rows who match >teh exact word, so if I search for "bank", the query wont find banking. >(this would work with "like '%bank%'", but that is much slower). So is there >anything you can to make it also match those ?? > I don't think you can make FULLTEXT do substring matches. If LIKE is too slow, maybe try |INSTR|(str, substr) instead and see if it's any faster. > >Thankx >Lorang Jacques >sql is mysql > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Enum
Ann Myhre wrote:
>Hi group
>
>Access has a datatype (I don't remember the name) which is a kind of boleaen; it
>represents Yes/No, true or false. When I try to translate that creating a table in
>MySQL I try ENUM("N","Y") but I do not get the expected result when using If Y
>then...(ASP). Do I use ENUM right? I have tried SET also, but with no better result.
>Anyone?
>
Use type `bool'. This is an alias for tinyint(1), with values 0 or 1.
Charlie
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
To many processes, 100% CPU usage...HELP
We just upgraded a box (RH 7.2) to current Apache, PHP, and MySQL. We used the MySQL binary for 3.23.46. On the old box which was nearly identical we were running 3.23.43 (I think). The old box didn't have any problems w/ MySQL but this box is having problems. The only real thing that changed was the version of MySQL, PHP, RH, etc. Can someone offer some insight into what might be going on here? Any suggestions on how to fix the problem. The connections are PHP non-persistent connections for a very active web based message board system. Again, keep in mind that this very similar setup was working flawlessly the day before yesterday on a lesser box. Current Box: Dual PIII 1G, w/ 2G of ram. Database is a message board database that is only 250,000 rows and only 57M in size. The problem is that mysqld is taking up all the CPU. Usually there are only a dozen mysqlds running sometimes there are as many as 200+. Regardless of how many daemons are running they take all the CPU and it appears that memory used is possibly growing. Sometimes the RSS will run up to 120M+ Please HELP, this is a live box (not so live now) and my users can't access the site. I can't turn up the old box because it's in transit via UPS back from my Co-location facility.1 TIA, Charlie Here's a sample from top PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 20395 mysql 17 0 58104 56M 1696 R48.2 2.8 0:04 mysqld 20477 mysql 15 0 58108 56M 1696 R30.1 2.8 0:02 mysqld 20338 mysql 16 0 58104 56M 1696 S 4.3 2.8 0:06 mysqld 20492 mysql 20 0 58712 57M 1696 R 4.2 2.8 0:01 mysqld 20265 mysql 17 0 58104 56M 1696 R 4.1 2.8 0:10 mysqld 20353 mysql 18 0 58104 56M 1696 R 4.1 2.8 0:04 mysqld 20359 mysql 16 0 58104 56M 1696 S 4.1 2.8 0:06 mysqld 20362 mysql 16 0 58104 56M 1696 R 4.1 2.8 0:05 mysqld 20413 mysql 18 0 58108 56M 1696 R 4.0 2.8 0:04 mysqld 20349 mysql 17 0 58104 56M 1696 R 3.9 2.8 0:06 mysqld 20496 mysql 17 0 58712 57M 1696 R 3.9 2.8 0:01 mysqld 19458 mysql 14 0 57724 56M 1696 S 3.8 2.8 0:08 mysqld 20437 mysql 16 0 58108 56M 1696 R 3.8 2.8 0:02 mysqld 20444 mysql 16 0 58108 56M 1696 R 3.8 2.8 0:03 mysqld 20445 mysql 17 0 58108 56M 1696 R 3.7 2.8 0:03 mysqld 20522 mysql 18 0 58712 57M 1696 R 3.7 2.8 0:01 mysqld 20335 mysql 14 0 58104 56M 1696 S 3.6 2.8 0:07 mysqld 20388 mysql 20 0 58652 57M 1696 R 3.6 2.8 0:05 mysqld 20391 mysql 20 0 58652 57M 1696 R 3.6 2.8 0:04 mysqld 20410 mysql 14 0 58108 56M 1696 R 3.5 2.8 0:04 mysqld 20474 mysql 15 0 58108 56M 1696 R 3.5 2.8 0:03 mysqld 20489 mysql 17 0 58152 56M 1696 R 3.5 2.8 0:01 mysqld 20527 mysql 20 0 58728 57M 1696 R 3.5 2.8 0:01 mysqld - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Errors
Hi Support Team, I'm quite a novice in installing Mysql. I needed to do this because of a task i had to accomplish by installing webtts for which mysql was a pre-requisite. during the installation process of webtts, i had the following errors "install_driver(mysql) failed: Can't load '/usr/local/lib/perl5/site_perl/5.005/i 386-freebsd/auto/DBD/mysql/mysql.so' for module DBD::mysql: Shared object "libmy sqlclient.so.10" not found at /usr/libdata/perl/5.00503/DynaLoader.pm line 169. at (eval 40) line 3 Perhaps a required shared library or dll isn't installed where expected at /usr/local/mysql-3.23.51/sql-bench/server-cfg line 238 " I'm doing this task on a freebsd 4.5 box. I would appreciate any help. sorry for any inconveniences. Agbenya Adotey Network Computer Systems Accra, Ghana. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 stopping mysqld
Hello, i'm running mysqld on darwin (mac os x client)...Start is good, but then i can't stop the server...i tried ./mysqladmin shutdown, ./mysqladmin -u root shutdown, but it doesn't work..any one can help??? mysql 3.23.37 Thank you, EISSEN Chalrie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
linking ms access to mysql db
question: Can i link to a mysql database using ms access 97 or ms access 2000 using an odbc driver. i recall doing it before, but can't seem to get it work again. i would appreciate any help you could give me on this subject. thanks charlie scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
