Re: Storing a space
Have you tried insertig non-breaking space instead #160 or 0xA0 ...i think so - this shoud be it /taken from here 3,02 KB (3 098 bytes)-All NON-Breaking/ HTH ;-) John Mistler wrote: Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing ibdata files on the fly
Hi List, Is it possible to change data and log files (ibdata and iblogfiles copied for backup purpose) on the fly? Will something like this work?? 1. Close all client connections 2. Flush and disable further client connections. 3. Copy ibdata and iblogfiles from backup dir 4. SIGHUP mysqld. What does mysqld does when it receives SIGHUP ? Thanks, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with maxdb odbc connection
Hallo !
Strange problem with maxdb odbc connections.
I installed maxdb, databasemanager and sqlstudio on my local windows xp
pc and did some testing.
My application connects via system-dsn MAXDBLOKAL
STORE
SQLSTRINGCONNECT('dsn=maxdblokal;port=7210;uid=DBA;pwd=DBA;DATABASE=BWNE
U') TO verbindungsnr
Everthing works as expected.
Then i installed maxdb on a linux server (same database name !!!)
Everthing seems to be ok. I can connect with the database manager, start
and stop ..
I can connect with sql studio and create and drop tables ..
BUT ?
When i open an connection from my pc to the server (dsn=MAXDBSERVER same
as MAXDBLOKAL but with different ip-adress)
STORE
SQLSTRINGCONNECT('dsn=maxdbserver;port=7210;uid=DBA;pwd=DBA;DATABASE=BWN
EU') TO verbindungsnr
i'm always connected to my local database
If the local database is not running there is an error message.
How can i connect to the server database ???
With mysql switching between lokal and server database by using two
different dsns works well !?
Any help welcomed
Best regards
Albert Beermann
'
'
(0 0)
+-oOO---(_)---+
| Tel: 0541/5841-868 |
| Fax: 0541/5841-869 |
| Mail: mailto:[EMAIL PROTECTED] |
| Internet: http://www.piepenbrock.de|
+---oOO---+
|__|__|
|| ||
ooO Ooo
Re: SQL SELECT HELP
Hi, Ok. This is good!! Thank you! Zoli Egor Egorov [EMAIL PROTECTED] 2004-04-30 03:30 PM To: [EMAIL PROTECTED] cc: (bcc: Zoltan Gyurasits/GYO/COMP/PHILIPS) Subject:Re: SQL SELECT HELP Classification: [EMAIL PROTECTED] wrote: Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. You need something like: SELECT DISTINCT t3.id FROM table2 t2 INNER JOIN table1 t1 ON t1.value=t2.value RIGHT JOIN table1 t3 ON t1.id=t3.id WHERE t1.id IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Probleme avec WHERE MATCH ... AGAINST
Bonjour, je ne sais pas si vous allez pouvoir me répondre mais j'ai un problème avec le FULL TEXT. Comment faire pour ke la fonction de recherche des mots ne prennent pas en compte l'appostrophe? MySQL utilise un filtre très simple pour séparer le texte en mots. Un mot est n'importe quelle chaîne de caractères constituée de lettres, chiffres, `'' et `_'. Merci.
SQL and Yahoo IM
Hi, I am using phpMyAdmin for last several years. I don't like it's framed design. Recently I read that I can query the database using my Yahoo IM. http://www.duncanlamb.com/sdba/?Projects/SQL+Admin I wonder if this software really works. Has anyone tried it before? What type of server will I need to host such software. (I apologize if it's out of topic) Shantanu Oak __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accessing DBMS remotely: MySQL? FireBird?
Hi Fred, Some of our customers have remote offices. I was wondering if it'd be safe to have a DBMS running at their central office, and have our client application running on hosts in the branches connect to it through a VPN via the Net? If the connection is reasonably stable, and you cache often used static data, you sure can. What happens if the connection goes south while a branch office was making changes? Does the DBMS just rollbacks changes automatically after a time-out? For Firebird, I am sure about this: yes. it will do a Rollback. For MySQL, I expect it to do the same. Should we set up some kind of replication instead? Also, are there compeling reasons to go for Firebird instead of MySQL? I don't know enough about the capabilities of each DBMS today to make an educated choice. That depends - Firebird has quite some features that MySQL doesn't have. On the other hand, MySQL has built in replication, which Firebird doesn't have. However, if you need to do some more server-processing, Firebird has stored procedures, triggers, views and check constraints, all of which MySQL doesn't have. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Freeing up connections
Deepak Vishwanathan [EMAIL PROTECTED] wrote: If a user 'x' has exhausted all his connections, is there a way to free up those connections. What command do I use for that? Do you mean that user exceeds max_connections value? http://dev.mysql.com/doc/mysql/en/User_resources.html If so, FLUSH USER_RESOURCES or FLUSH PRIVILEGES will help you. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL and Yahoo IM
Shantanu Oak wrote: Hi, I am using phpMyAdmin for last several years. I don't like it's framed design. Recently I read that I can query the database using my Yahoo IM. http://www.duncanlamb.com/sdba/?Projects/SQL+Admin I wonder if this software really works. Has anyone tried it before? What type of server will I need to host such software. (I apologize if it's out of topic) Love the way Yahoo's IM has converted select count(*) to select count and a pretty yellow graphical star.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Integrating phpBB with an existing MySQL database
MySQL Listers, I already asked this question on phpBB's own forums, but no one seems inclined to answer me. I'm hoping there might be more charitable souls here on this list. My question is that I want use phpBB because I am satisfied with it's overall functionality and usability. However, it sets up it's own series of tables in one's database which are a little complicated. I'm sure they are sensibly laid out, but I am a relative newcomer to MySQL, so their usage is opaque to me. What I want to do is integrate the user accounts within phpBB with the user data that I have already collected so far. I have a site that's been up for a few years and it has user accounts where they store contact and profile information. I'd like those same users to be able to log into phpBB with the same username and password that they have always been using. I thought one thing I could do is try and find the table that stores phpBB's usernames and passwords, and extend those to include the user data that I have already. But I know enough to know that it is unlikely to be as simple as that. For example, I thought I read somewhere that phpBB uses a different hashing algorithm to encrypt it's passwords. Has anyone here been through a process like this before? Can anyone offer some tips and warnings about attempting this? -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL and Yahoo IM
yeah, isn't it cute! Cute enough to gag a maggot, maybe. You can turn that off, you know..click 'login', 'preferences', and open the 'Messages' menu. You will see a checkbox under 'misc' for 'Enable Emoticons'. Uncheck it, and it'll quit turning things like :) and * into silly graphics. hth Dan - Original Message - From: Sime [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 03, 2004 3:09 AM Subject: Re: SQL and Yahoo IM Shantanu Oak wrote: Hi, I am using phpMyAdmin for last several years. I don't like it's framed design. Recently I read that I can query the database using my Yahoo IM. http://www.duncanlamb.com/sdba/?Projects/SQL+Admin I wonder if this software really works. Has anyone tried it before? What type of server will I need to host such software. (I apologize if it's out of topic) Love the way Yahoo's IM has converted select count(*) to select count and a pretty yellow graphical star.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date format problem
Matt Tucker [EMAIL PROTECTED] wrote: I'm moving a JavaServlet app over from Tomcat on Win2K with a MS SQL 7 DB to Tomcat on Red Hat Linux with mySQL. Of course, there's about a hundred queries that use dates and of course, they're all in the format mm-dd-. is there a way to format the date column in my mysql tables to accept a date in this format or do i really have to go through every sql statement and parse the date and rebuild it to be -mm-dd? thanks so much. If you want to store values in the DATE columns you shoul convert all data to the -mm-dd format. Then you can use DATE_FORMAT() function to retrieve data in various formats: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF-8 Collation
Hi, I recently tried MySQL 4.1.1-alpha in order to get proper UTF-8 support. I need to be able to order on a utf8 text field. Accented characters should (broadly) be treated as though they were not accented for ordering purposes. Many of the european charsets (eg German) seem to have special collations which follow the rules of the language. I suspect the Unicode Collation Algorithm should be a good approximation to what I need but while the manual says it's there for ucs2 (not utf8) it appears not to be. Anyway, is it be possible to create a custom collation? For now, I've ended up replicating the data with unaccented chars in order to have a column to order on. It's a solution for now, but it's hardly ideal. Thanks Gavin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrating phpBB with an existing MySQL database
Hi Dave, I thought one thing I could do is try and find the table that stores phpBB's usernames and passwords, and extend those to include the user data that I have already. But I know enough to know that it is unlikely to be as simple as that. For example, I thought I read somewhere that phpBB uses a different hashing algorithm to encrypt it's passwords. Has anyone here been through a process like this before? Can anyone offer some tips and warnings about attempting this? It isn't very hard to do this actually. Although I don't use phpBB myself, I had a quick look at it to see if it was what we needed for a particular community site (management decided to use vBulletin in the end). I know that phpBB has a few converter scripts on the web site. These may help you to learn a bit how you can fill the phpBB user information. You need to dig a bit around in those scripts to see which part(s) are about retrieving information from the original forum database and which part(s) are about inserting the data in the phpBB database. Of course you only need the parts that deal with the user data! There is also a universal converter system on the download page, but that seems a bit complicated at first sight. Maybe you can ask the developer of the UCS how you easily use it to add your own users to phpBB? I think you will manage ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem (bug?) with LOCATE(substr,str,pos)
Boris Mueller [EMAIL PROTECTED] wrote:
I need to do a LOCATE(substr,str,pos) request where pos must be taken from a
column.
for unknown reason (bug?) it does not work.
I made the following example to ilustrate the problem:
select ERW, locate('xyz', A, 3000) as loc1, locate('xyz', A, ERW) as loc2
from B;
+--+-++
| ERW | loc1| loc2 |
+--+-++
| 2873 | 10363 | 0 |
| 2677 | 18027 | 0 |
| 2459 | 13016 | 0 |
| 2539 |3462 | 0 |
+--+-++
4 rows in set (0.00 sec)
in this example loc2 should have the same result as loc1, but loc2 always
presents 0.
whats going wrong here?
anyone can help?
I have mysql 4.0.18 on redhat 9.0.
ERW is a mediumint(8) unsigned not null
Could you create a test case? I wasn't able to repeat it with my test data.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing ibdata files on the fly
At 12:39 +0530 5/3/04, Sp.Raja wrote: Hi List, Is it possible to change data and log files (ibdata and iblogfiles copied for backup purpose) on the fly? No, you must shut down the server cleanly. Will something like this work?? 1. Close all client connections 2. Flush and disable further client connections. 3. Copy ibdata and iblogfiles from backup dir 4. SIGHUP mysqld. What does mysqld does when it receives SIGHUP ? Thanks, Sp.Raja -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem
I have a problem with a character
for example : in my databas i have a word like : papier d'emballage and if
i try this request :
$requete = SELECT * FROM `produit` WHERE MATCH (nom) AGAINST ('emballage*'
IN BOOLEAN MODE);;
---it doesnt't work, because of this character : ' and there is no
answer. Indeed on your site, it's indicated : MySQL uses a very simple
parser to split text into words. A ``word'' is any sequence of characters
consisting of letters, digits, `'', or `_'.
thanks.
Re: Problem
Sorry, it's an error
- Original Message -
From: mickael
To: [EMAIL PROTECTED]
Sent: Monday, May 03, 2004 4:07 PM
Subject: Problem
I have a problem with a character
for example : in my databas i have a word like : papier d'emballage and if
i try this request :
$requete = SELECT * FROM `produit` WHERE MATCH (nom) AGAINST ('emballage*'
IN BOOLEAN MODE);;
---it doesnt't work, because of this character : ' and there is no
answer. Indeed on your site, it's indicated : MySQL uses a very simple
parser to split text into words. A ``word'' is any sequence of characters
consisting of letters, digits, `'', or `_'.
thanks.
innodb lock information
How can I go about getting information about lock information? I looked at the InnoDB status screen but it doesnt say a whole lot. Im getting alot of problems with lock wait timeouts. What I want to know is, what is obtaining the locks, what user is obtaining the locks and with what query/update/insert statement. I also had alot of problems with deadlocks but that is solved now. This all came up because I had to fork() and have many children update the database at the same time. I searched the web alot for examples of how to do DB interactions while forking but I didn't find much information/examples. Any advice is welcome. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE clause problem
This is my table: mysql desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Stats] MySQL List: April 2004
-- Searchable archives for this list are available at http://www.listsearch.com/mysql.lasso -- == MySQL List Stats April, 2004 == Note: Up/Down % as compared with March, 2004 Posts: 1816 (Down 15%) Authors: 543 (Down 14%) Threads: 627 (Down 17%) Top 20 Contributors by Number of Posts -- Paul DuBois 88 Michael Stassen 57 Egor Egorov 53 Victor Pendleton52 Victoria Reznichenko45 Dathan Vance Pattishall 29 Stormblade 29 Lou Olsten 20 Mark Susol | Ultimate Creative Media18 Jigal van Hemert17 Victor Medina 16 Ronan Lucio 16 Matt Chatterley 16 Jeremy Zawodny 15 lga215 Harald Fuchs15 Rhino 14 Donny Simonton 14 Martijn Tonies 13 beacker 12 Top 20 Threads by Number of Posts -- MySQL on Linux 18 Gripe with MySQL17 Perl Modelues 15 SELECT DISTINCT returns an incorrect result with special charac... 15 first LIMIT then ORDER 14 Datetime Default Value 13 mysqld too busy to check its grant tables? 13 MySQL and Unicode 12 MySQL Website 12 Unixware 7.1.0 compile error... mysql 4.0.1812 starting mysql daemon 11 Why can't I use an AS value in the WHERE clause. 11 stuck with simple query. Plz have a look11 Hey what, no pity for a new user? 11 Learning curve 10 Process Monitoring 10 mysql error file 9 SQL Query Question 9 Creating Users and Passwords 9 Unable to connect to mysql with phpmyadmin 8 Top 20 Search Terms by Number of Requests -- 104410 mysql 10 date 9 login8 update 7 weekdays 7 user 7 odbc 7 thread 6 database 6 ftp 5 cluster 5 Filemaker5 crystal 5 in 5 auto 5 blob 4 Permission 4 Madeleine
Re: Win Linux
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 | Another question how can see the compatibility of authentification? | I don't know. It should be written in docs... Anyway, try the next thing... Try to create a symlink for every db except for mysql. create a symlink /var/lib/mysql/db1 - /mnt/onedisc/Program\ Files/mysql/data/db1 create a symlink /var/lib/mysql/db2 - /mnt/onedisc/Program\ Files/mysql/data/db2 create a symlink /var/lib/mysql/db3 - /mnt/onedisc/Program\ Files/mysql/data/db3 this way you can control which databases will be shared between win and lin. Regards, Ivan -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAlmSuTGB93IULb3YRAmt1AJ4wyI31vgXS/OwdpkxerolyfLdpiQCfZcFW PZ26ss5LaXM9KFosh2PEd8Y= =pdaD -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE clause problem
Try using IN(Month(Now()), Month(Now()) -1) -Original Message- From: mayuran To: [EMAIL PROTECTED] Sent: 5/3/04 10:15 AM Subject: WHERE clause problem This is my table: mysql desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE clause problem
Hmm. Bit Odd. However, I suspect the problem is that your 'where' isn't explicit enough: Where ( month(date) = month(now()) ) or ( month(date) = month(now())-1 ) Bear in mind that if month(now()) = 1 you will be looking for records in month 0! A better way to do this might be: WHERE month(date) BETWEEN month(now() - interval 1 month) AND month(now) Cheers, Matt -Original Message- From: mayuran [mailto:[EMAIL PROTECTED] Sent: 03 May 2004 16:15 To: [EMAIL PROTECTED] Subject: WHERE clause problem This is my table: mysql desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE clause problem
From: mayuran [mailto:[EMAIL PROTECTED] Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) The first one, while it may look valid, isn't. `MONTH(NOW())' currently evaluates to 5. `MONTH(NOW())-1' currently evaluates to 4. As such, your first query is essentially the following: SELECT * FROM testing WHERE MONTH(date) = (5 OR 4); Which becomes: SELECT * FROM testing WHERE MONTH(date) = 1; `(MONTH(NOW()) OR MONTH(NOW())-1)' aka `(5 OR 4)' evaluates to 1, because it's just ORing two integers. Note that it's returning exactly what it's supposed to be returning, the date in January. So while it may look like it makes sense in pseudo-code, on paper it's not how MySQL evaluates things. It builds the right side of the equality and then compares. Stick with your second query and you'll be fine, even if it is more typing. ;) HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrating phpBB with an existing MySQL database
Phpbb uses an md5 hash to store the passwords. You can use the mysql md5 function or php's to encrypt them. To verify md5 the supplied password and match against the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE clause problem
mayuran wrote: This is my table: mysql desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) Thanks Shorter to type doesn't necessarily mean does what you want. (MONTH(NOW()) OR MONTH(NOW())-1) evaluates to (5 OR 4). The BOOLEAN OR operator returns 1 if either opperand is TRUE (nonzero), or 0 if both operands are FALSE (0). Hence, (5 OR 4) = 1. So, your shorter query evaluates like this: SELECT * FROM testing WHERE MONTH(date) = 1; So you are getting the correct result. Your second, longer query, or the alternatives sent by Victor and Matt, will do what you want. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
float in PROCEDURE ANALYSE() / misleading CREATE TABLE error
Hi,
I think there is
1) a problem with FLOAT recommendations in PROCEDURE ANALYSE
2) a minor problem with CREATE TABLE(f FLOAT(negative_value,...))
Please correct me if I'm wrong.
With MySQL 4.0.17, the query
SELECT * FROM my_table PROCEDURE ANALYSE();
gives me the following result for a DOUBLE NOT NULL column xxx:
-- 8
Field_name: xxx
Min_value: 0.002
Max_value: 2800
Min_length: 1
Max_length: 7
Empties_or_zeros: 45
Nulls: 0
Avg_value_or_avg_length: 63.10753644525
Std: 81.175363704985
Optimal_fieldtype: FLOAT(-25,1) NOT NULL
-- 8
The suggested field type FLOAT(-25,1) seems not to be a valid column
type. Also, 1 decimal would be a bad choice when there is a value 0.002.
mysql create table test.foo (f float(-25,1) not null);
ERROR 1074: Too big column length for column 'f' (max = 255). Use BLOB
instead
This happens for many (or all) DOUBLE columns, so PROCEDURE ANALYSE's
advice is probably broken here.
The CREATE TABLE statement gives the misleading error message Too big
while the value is actually too small. Probably -25 is read into an
'unsigned int' or similar internally and then interpreted as a very
large integer like 2^32-25. At sql/sql_yacc.yy:1099, precision is
defined as '(' NUM ',' NUM ')' and according to sql_lex.cc:345, NUM can
be a negative value. Maybe in one of these locations an error could be
reported if NUM is negative, but that's just a guess.
Hans-Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE clause problem
At 11:15 -0400 5/3/04, mayuran wrote: This is my table: mysql desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. As others have pointed out, this cannot work for January. (Not to mention that it's semantically not correct anyway.) You might want to adapt the technique shown at the bottom of this page: http://dev.mysql.com/doc/mysql/en/Date_calculations.html Note the use of the MOD operator to handle month number wraparound. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) Indeed, leaving out parts of the correct solution often will result in a shorter query. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL] Multiple instances of server starting
Thanks, Paul. The machines are running RH 9 (one instance) and Fedora Core 1 (multiple instances). It's not really a problem, so much as a curiosity about whether the one with more processes showing was using more resources. Jim On Friday 30 April 2004 07:36 pm, Paul DuBois wrote: At 18:02 -0400 4/30/04, jim wrote: Hi, I've just set up a mySQL server and, upon starting it, and running ps ax | grep mysql , it appears that there are multiple instances running: [EMAIL PROTECTED] var]# ps ax | grep mysql 7808 pts/0S 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/db02.pid 7840 pts/0S 0:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/db02.pid --skip-locking --port=3306 --socket=/tmp/mysql.sock ..and so on. Ten instances. This should be the same config file (my.cnf) and same mysql.server start script (not that it should matter (??)) as a second machine, which does the right thing, and runs a single instance of [mysqld]: [EMAIL PROTECTED] root]# ps ax | grep mysql 1519 ?S 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/emma.pid 1552 ?S778:21 [mysqld] 26985 pts/0S 0:00 grep mysql Very likely you're seeing threads being reported as processes on one machine and not the other. What operating system does each machine run? Looking at this post to this list: http://lists.mysql.com/mysql/154832 it seems that others have had this problem, but the answer there does not really explain why these two servers are behaving differently. No, but it does explain that this is not really a problem. Well, I suppose it's possible to consider it a problem. But if so, it's an operating system problem, not a MySQL problem. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Plz help quick - mysql/php/web server undefined function all of a sudden
I have a web server that uses mysql-4.1.0/apache-2.4.6/php-4.3.4 on freebsd-5.1. It has been working fine for the past few years, now all of a sudden today I get an undefined function error. This is from httpd-error.log - PHP Fatal error: Call to undefined function: mysql_connect() in /...stuff This is the connection function I am using - ? $conn=mysql_connect(localhost,user,) or die (Could not connect to the server); mysql_select_db(simradusa, $conn) or die (Could not get the database); ? If I comment out the above function the pages will load but of course none of the database stuff will load. If I leave the lines with the connect function uncommented the page fails to load altogether. As I mentioned - this just suddenly came about either today or over the weekend, I know it worked friday when I left the office. This particular machine has been up for 151 days 22 hours without a hiccup. thanks for the help, -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] There is no reason anyone would want a computer in their home. --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (Then why do I have 8? Somebody help me!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
urban myth?
My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Does the database not return it in the order that the entries are submitted? I've done some log parsing/caching in databases, and the order had always been the same whether I use an order by date or not. One thing logs to the db, the other grabs. Had no problem without an order. P Boyd E. Hemphill [EMAIL PROTECTED] 05/03/2004 01:39 PM To: [EMAIL PROTECTED] cc: Subject:urban myth? My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urban myth?
From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? From what I understand (as I've heard this explained a half dozen times by different people), the results are returned in the order they appear on disk. This can change via any number of factors (such as a disk defrag), though, so you should never rely on getting results back in the same order every time if you're not using an ORDER BY clause. For the most part, though, results /will/ appear to come back in the same order. I imagine that's what your boss is talking about. It's definitely not to be relied on, though. If this is documented online, I've yet to see it (though I haven't exactly gone looking for it). Someone else may have a more accurate description of the way it's done, though. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Yes it is a myth. The records will come back in the same order IF there have been not inserts and deletes. Depends on the database product to. My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? You are correct. Ordering takes time. Why choose a random column on which to order the results and take additional time when the user didn't ask for it. Here's the proof: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; +--+ | num | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) delete from foo where num = 3; insert into foo values (6); insert into foo values (3); delete from foo where num = 6; select * from foo; +--+ | num | +--+ |1 | |2 | |4 | |5 | |3 | +--+ 5 rows in set (0.00 sec) Garth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 3 May 2004 12:39:48 -0500 Boyd E. Hemphill [EMAIL PROTECTED] wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? I believe that the relation database model specifies that the order of rows is not important in the resulting relation produced from a relational operation (Select, Project, Union, Join, etc.). Whether or not you actually get the same order on the same SELECT query run multiple times depends on the DBMS you are using. I think different DBMS's may do things in idle time to optimize table layout and perhaps re-order the data, I'm not sure about MySQL. Conceptually as an end-user, you should never assume that the DBMS will always return your data in the same order regardless of how/where it is physically stored, that's the job of the ORDER BY clause in a SELECT query. The DBMS is supposed to abstract away the details of the physical storage implementation, so when order is important, every query should have an ORDER BY to guaruntee the sort order, especially if you want to port the application. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, May 03, 2004 at 12:39:48PM -0500, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? In reality, the order is often the same but you should never DEPEND on that being the case without an ORDER BY clause. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
As everyone has mentioned - you should always assume the data comes back randomly - even if the table is completely static and there have been no inserts or updates, but it's even more subtle than that. When you port your application to a database than allows your queries to run multi-threaded (Oracle, DB2, SQL Server) - every query process will return a chunk of the data and one process will mastermind and glue the chunks together. There is no guarantee that the chunks will always be glued in the same order - unless ORDER BY has been specified. People who get sloppy in single threaded databases and only use a GROUP BY without an ORDER BY, relying on the fact that the GROUP BY does a sort and makes the ORDER BY unnecessary - get a rude awakening when they move to a multi-threaded database where every query process returns its chunk sorted but the different chunks are glued out of sequence unless you explicitly specified the ORDER BY. So - certain errors will never become apparent in single-threaded MySQL or Microsot Access, but will sting you when upgrading to a multi-threaded database. Therefore - beware - and always specify ORDER BY if you want your data sorted. In a message dated 5/3/2004 1:39:48 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. bob At 12:55 PM 5/3/2004, Garth Webb wrote: On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? You are correct. Ordering takes time. Why choose a random column on which to order the results and take additional time when the user didn't ask for it. Here's the proof: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; +--+ | num | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) delete from foo where num = 3; insert into foo values (6); insert into foo values (3); delete from foo where num = 6; select * from foo; +--+ | num | +--+ |1 | |2 | |4 | |5 | |3 | +--+ 5 rows in set (0.00 sec) Garth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 03 May 2004 13:21:56 -0500 Bob Ramsey [EMAIL PROTECTED] wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. Conceptually, row order truely is random and meaningless in an RDBMS. In a multi-user system who cares who originally inserted the data and in what order? Just because it behaves that way now, in future versions of MySQL the developers may decide to do something completely different and change the default way rows are pulled without an ORDER BY and they certainly have that right. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
It's also not in the order it was entered ( as suggested ). Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. bob At 12:55 PM 5/3/2004, Garth Webb wrote: On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? You are correct. Ordering takes time. Why choose a random column on which to order the results and take additional time when the user didn't ask for it. Here's the proof: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; +--+ | num | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) delete from foo where num = 3; insert into foo values (6); insert into foo values (3); delete from foo where num = 6; select * from foo; +--+ | num | +--+ |1 | |2 | |4 | |5 | |3 | +--+ 5 rows in set (0.00 sec) Garth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Plz help quick - mysql/php/web server undefined function all of a sudden
John W. Holmes [EMAIL PROTECTED] wrote on 05/03/2004 10:59:15 AM: From: Chip Wiegand [EMAIL PROTECTED] I have a web server that uses mysql-4.1.0/apache-2.4.6/php-4.3.4 on freebsd-5.1. It has been working fine for the past few years, now all of a sudden today I get an undefined function error. This is from httpd-error.log - PHP Fatal error: Call to undefined function: mysql_connect() in /...stuff I know you say that nothing changed but something obviously has. It looks like someone upgraded or reinstalled PHP but did not compile in MySQL support. Take a look at a phpinfo() page and see if there is a MySQL section. ---John Holmes... John, Yep, looking at phpinfo.php shows no support for mysql. This is very strange. I know these things don't just happen by themselves. I also know there are only two people with the password to the server, myself and my boss (and he knows nothing about the server to begin with). Anyway, looks like php needs to be configured to work with mysql. Now that it is the way it is, how do I go about that? According to pkg_info the version of php is 4.3.4_3. Being a port install how do I configure it to use mysql? Thanks, Chip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 2004-05-03 at 11:21, Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. I don't think the point of the original question was Are returned results random and completely unpredictable, but Can I rely on the order of unordered results to be the same every time. The test I provided proves that no, they cannot be relied upon to be same every time. Anyway, if result order isn't the same every time, but in an arbitrary database specific order, they might as well be random. bob At 12:55 PM 5/3/2004, Garth Webb wrote: On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? You are correct. Ordering takes time. Why choose a random column on which to order the results and take additional time when the user didn't ask for it. Here's the proof: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; +--+ | num | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) delete from foo where num = 3; insert into foo values (6); insert into foo values (3); delete from foo where num = 6; select * from foo; +--+ | num | +--+ |1 | |2 | |4 | |5 | |3 | +--+ 5 rows in set (0.00 sec) Garth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get MySQL to startup at boot up for AIX 4.3.3???
Hi! I'm using AIX 4.3.3 and I'm still not able to get the MySQLDaemons to start automatically at every bootup. AIX use the inittab, not the init.d Can anyone show me the way? Thanks, FletchSOD
Re: urban myth?
Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. snip Apparently not random, but not in the order inserted either. Consider: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; delete from foo where num = 3; insert into foo values (6); insert into foo values (3); mysql select * from foo; +--+ | num | +--+ |1 | |2 | |6 | |4 | |5 | |3 | +--+ 6 rows in set (0.01 sec) (Same example as before with the delete...where num=6 removed.) Note the 6 is where the 3 was originally, because the slot where the first 3 was inserted/deleted was reused for the 6. This trivial example yields results which are ordered neither by num nor by the order inserted. The lesson is clear: The *only* way to be sure your rows are sorted in a particular way is to explicitly request it with an ORDER BY clause, as several others have pointed out. This is really a fundamental principle: It is the data in the row that matters, not how or where it is stored. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Gerald- In my experience, I have inserted and retrieved from a decent sized db (a few million records per day), and have gotten them out in the same order. There were no other operations on the db except for cronological ones, i.e. delete the first hundred rows, insert a hundred rows. The results were always returned in the order expected. I do not contest that it depends on the dbms as well as any operations done on existing data. P gerald_clark [EMAIL PROTECTED] 05/03/2004 02:35 PM To: Bob Ramsey [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: urban myth? It's also not in the order it was entered ( as suggested ). Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. bob At 12:55 PM 5/3/2004, Garth Webb wrote: On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? You are correct. Ordering takes time. Why choose a random column on which to order the results and take additional time when the user didn't ask for it. Here's the proof: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; +--+ | num | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) delete from foo where num = 3; insert into foo values (6); insert into foo values (3); delete from foo where num = 6; select * from foo; +--+ | num | +--+ |1 | |2 | |4 | |5 | |3 | +--+ 5 rows in set (0.00 sec) Garth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with search on Japanese and version 4.015
Hi, I'm implementing a search on fields some using the latin character set and others the Japanese character set; however because its virtually hosted I'm limited to using mysql 4.015 Is the documentation for this version online. I tried to find but could only get for the latest version which discusses search over different characters sets but specifically to 4.1. Thank-you David --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.662 / Virus Database: 425 - Release Date: 4/20/04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urban myth?
To all who answered thank you. This answer below is the one that I can use to convince him what he proposes is not necessarily safe. Now I just need to decide how to convince him it was his idea :-) Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 12:57 PM To: Bob Ramsey Cc: [EMAIL PROTECTED] Subject: Re: urban myth? Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. snip Apparently not random, but not in the order inserted either. Consider: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; delete from foo where num = 3; insert into foo values (6); insert into foo values (3); mysql select * from foo; +--+ | num | +--+ |1 | |2 | |6 | |4 | |5 | |3 | +--+ 6 rows in set (0.01 sec) (Same example as before with the delete...where num=6 removed.) Note the 6 is where the 3 was originally, because the slot where the first 3 was inserted/deleted was reused for the 6. This trivial example yields results which are ordered neither by num nor by the order inserted. The lesson is clear: The *only* way to be sure your rows are sorted in a particular way is to explicitly request it with an ORDER BY clause, as several others have pointed out. This is really a fundamental principle: It is the data in the row that matters, not how or where it is stored. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Plz help quick - mysql/php/web server undefined function all of a sudden
It sounds like someone upgraded your PHP libraries, and forget to include MySQL support. Do you admin this server, or does someone else? j- k- On Monday 03 May 2004 09:20 am, Chip Wiegand said something like: I have a web server that uses mysql-4.1.0/apache-2.4.6/php-4.3.4 on freebsd-5.1. It has been working fine for the past few years, now all of a sudden today I get an undefined function error. This is from httpd-error.log - PHP Fatal error: Call to undefined function: mysql_connect() in /...stuff This is the connection function I am using - ? $conn=mysql_connect(localhost,user,) or die (Could not connect to the server); mysql_select_db(simradusa, $conn) or die (Could not get the database); ? If I comment out the above function the pages will load but of course none of the database stuff will load. If I leave the lines with the connect function uncommented the page fails to load altogether. As I mentioned - this just suddenly came about either today or over the weekend, I know it worked friday when I left the office. This particular machine has been up for 151 days 22 hours without a hiccup. thanks for the help, -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] There is no reason anyone would want a computer in their home. --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (Then why do I have 8? Somebody help me!) -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBD::MySQL on RH8
I am having problems with installing the DBD::MySQL Perl module. My system is: RedHat 8 (patched current) Perl 5.8.4 (installed from RPM but upgraded manually) Apache 2.0.40 Modperl 1.99 I am heading toward being able to install Best Practicals request tracker. Currently this is a clean system. Any help is appreciated. Here is the output from my install (as far as I went) [EMAIL PROTECTED] DBD-mysql-2.9003]# perl Makefile.PL --cflags=-I/usr/include/mysql --libs='-L/usr/lib/mysql -lz' --testdb=test --testuser= --testpassword='' --testhost=server.domain.com --ssl I will use the following settings for compiling and testing: cflags(Users choice) = -I/usr/include/mysql libs (Users choice) = -L/usr/lib/mysql -lz nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ssl (Users choice) = 1 testdb(Users choice) = test testhost (Users choice) = server.domain.com testpassword (Users choice) = testuser (Users choice) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Checking if your kit is complete... Looks good Using DBI 1.42 (for perl 5.008004 on i686-linux) installed in /usr/local/lib/perl5/site_perl/5.8.4/i686- linux/auto/DBI Writing Makefile for DBD::mysql [EMAIL PROTECTED] DBD-mysql-2.9003]# make cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm cp lib/Mysql.pm blib/lib/Mysql.pm cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod cp lib/Mysql/Statement.pm blib/lib/Mysql/Statement.pm cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm cc -c -I/usr/local/lib/perl5/site_perl/5.8.4/i686-linux/auto/DBI -I/usr/include/mysql -DDBD_MYSQL_WITH_ SSL -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -DVERSION= \2.9003\ -DXS_VERSION=\2.9003\ -fpic -I/usr/local/lib/perl5/5.8.4/i686-linux/CORE dbdimp.c /usr/bin/perl -p -e s/~DRIVER~/mysql/g /usr/local/lib/perl5/site_perl/5.8.4/i686-linux/auto/DBI/Driver .xst mysql.xsi /usr/bin/perl /usr/local/lib/perl5/5.8.4/ExtUtils/xsubpp -typemap /usr/local/lib/perl5/5.8.4/ExtUtils/t ypemap mysql.xs mysql.xsc mv mysql.xsc mysql.c Warning: duplicate function definition 'do' detected in mysql.xs, line 192 Warning: duplicate function definition 'rows' detected in mysql.xs, line 290 cc -c -I/usr/local/lib/perl5/site_perl/5.8.4/i686-linux/auto/DBI -I/usr/include/mysql -DDBD_MYSQL_WITH_ SSL -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -DVERSION= \2.9003\ -DXS_VERSION=\2.9003\ -fpic -I/usr/local/lib/perl5/5.8.4/i686-linux/CORE mysql.c Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so LD_RUN_PATH=/usr/lib /usr/bin/perl myld cc -shared -L/usr/local/lib dbdimp.o mysql.o -o blib/arch/au to/DBD/mysql/mysql.so -L/usr/lib/mysql -lz chmod 755 blib/arch/auto/DBD/mysql/mysql.so cp mysql.bs blib/arch/auto/DBD/mysql/mysql.bs chmod 644 blib/arch/auto/DBD/mysql/mysql.bs Manifying blib/man3/DBD::mysql.3 Manifying blib/man3/DBD::mysql::INSTALL.3 Manifying blib/man3/Mysql.3 Manifying blib/man3/Bundle::DBD::mysql.3 [EMAIL PROTECTED] DBD-mysql-2.9003]# make test PERL_DL_NONLAZY=1 /usr/bin/perl -MExtUtils::Command::MM -e test_harness(0, 'blib/lib', 'blib/arch') t/*.t t/00base...install_driver(mysql) failed: Can't load '/root/.cpan/build/DBD-mysql-2.9003/blib/arc h/auto/DBD/mysql/mysql.so' for module DBD::mysql: /root/.cpan/build/DBD-mysql-2.9003/blib/arch/auto/DBD/ mysql/mysql.so: undefined symbol: net_buffer_length at /usr/local/lib/perl5/5.8.4/i686-linux/DynaLoader. pm line 230. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at t/00base.t line 38 t/00base...dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/10dsnlistinstall_driver(mysql) failed: Can't load '/root/.cpan/build/DBD-mysql-2.9003/blib/arc h/auto/DBD/mysql/mysql.so' for module DBD::mysql: /root/.cpan/build/DBD-mysql-2.9003/blib/arch/auto/DBD/ mysql/mysql.so: undefined symbol: net_buffer_length at /usr/local/lib/perl5/5.8.4/i686-linux/DynaLoader. pm line 230. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at t/10dsnlist.t line 45 t/10dsnlistdubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 1-3 Failed 3/3 tests, 0.00% okay t/20createdrop.install_driver(mysql) failed: Can't load '/root/.cpan/build/DBD-mysql-2.9003/blib/arc h/auto/DBD/mysql/mysql.so' for module DBD::mysql: /root/.cpan/build/DBD-mysql-2.9003/blib/arch/auto/DBD/ mysql/mysql.so: undefined symbol: net_buffer_length at /usr/local/lib/perl5/5.8.4/i686-linux/DynaLoader. pm line 230. at (eval 1) line 3
Re: Plz help quick - mysql/php/web server undefined function all of a sudden
Joshua J. Kugler [EMAIL PROTECTED] wrote on 05/03/2004 12:23:16 PM: It sounds like someone upgraded your PHP libraries, and forget to include MySQL support. Do you admin this server, or does someone else? j- k- Yep, I ran phpinfo.php and verified that it no longer shows support for mysql. Only two of have the password to the server, myself and my boss (who knows absolutely nothing about it). Now the question is - how do I configure php to support mysql when it is already installed from the ports? Do I do a whole new install again? I hope not. -- Chip On Monday 03 May 2004 09:20 am, Chip Wiegand said something like: I have a web server that uses mysql-4.1.0/apache-2.4.6/php-4.3.4 on freebsd-5.1. It has been working fine for the past few years, now all of a sudden today I get an undefined function error. This is from httpd-error.log - PHP Fatal error: Call to undefined function: mysql_connect() in /...stuff This is the connection function I am using - ? $conn=mysql_connect(localhost,user,) or die (Could not connect to the server); mysql_select_db(simradusa, $conn) or die (Could not get the database); ? If I comment out the above function the pages will load but of course none of the database stuff will load. If I leave the lines with the connect function uncommented the page fails to load altogether. As I mentioned - this just suddenly came about either today or over the weekend, I know it worked friday when I left the office. This particular machine has been up for 151 days 22 hours without a hiccup. thanks for the help, -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] There is no reason anyone would want a computer in their home. --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (Then why do I have 8? Somebody help me!) -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth,and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Also, I'd say that it depends on what you mean by row. If you mean the same physical data as was previously displayed, then they probably WILL come back in the same order, but there are no guarantees (a dataset is always unordered, unless something else is specified). But on the other hand, if by row, we mean the same unique data, well then it might change as someone else might delete a row, and then someone else again might insert the same unique data. Same data, but a different physical row. In this case, data will certainly come back in a different order, and if you only look at, say, the unique identifier to determine row position, then the row might well have changed it's position within the row, fact is, it is much more likely that it has! Finally, a quote from Chris Date, as read in Relatuional databases - Selected writings, rom the section entitled Relational Database: An overview: If the entire ORDER BY clause is omitted, the result appears in unpredictable order and A table is an unordered set of rows. And as the result of a SELECT is also considered a relation (or a table), this latter quote applies too. /Karlsson Boyd E. Hemphill wrote: To all who answered thank you. This answer below is the one that I can use to convince him what he proposes is not necessarily safe. Now I just need to decide how to convince him it was his idea :-) Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 12:57 PM To: Bob Ramsey Cc: [EMAIL PROTECTED] Subject: Re: urban myth? Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. snip Apparently not random, but not in the order inserted either. Consider: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; delete from foo where num = 3; insert into foo values (6); insert into foo values (3); mysql select * from foo; +--+ | num | +--+ |1 | |2 | |6 | |4 | |5 | |3 | +--+ 6 rows in set (0.01 sec) (Same example as before with the delete...where num=6 removed.) Note the 6 is where the 3 was originally, because the slot where the first 3 was inserted/deleted was reused for the 6. This trivial example yields results which are ordered neither by num nor by the order inserted. The lesson is clear: The *only* way to be sure your rows are sorted in a particular way is to explicitly request it with an ORDER BY clause, as several others have pointed out. This is really a fundamental principle: It is the data in the row that matters, not how or where it is stored. Michael -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: float in PROCEDURE ANALYSE() / misleading CREATE TABLE error
On Mon, May 03, 2004 at 05:56:03PM +0200, Hans-Peter Grimm wrote: Hi, I think there is 1) a problem with FLOAT recommendations in PROCEDURE ANALYSE 2) a minor problem with CREATE TABLE(f FLOAT(negative_value,...)) I think that there is maybe: 3) a bug in PROCEDURE ANALYSE that you should report in the MySQL bug tracking system. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning on FreeBSD
On Sat, May 01, 2004 at 09:50:14PM -0700, Max Clark wrote: Hi all, I have a db that is connection heavy running on FreeBSD 4.9. The server was compiled with Linux Threads enabled. I am searching for performance tuning information. Outside of enabling Linux Threads I haven't been able to find much else. I am under the impression that I should be using innodb tables instead of myisam, and I am sure there are a slew of other things that I could tune. Is there a site/document dedicated to this? My copy of high performany mysql by Jeremy Zawodny is in the mail, so any help in the mean time would be greatly appreciated. I am getting cannot create new thread errors on my system when load gets extremely heavy, are there any additional things I can do to help this? Would FreeBSD 5.1 be any better? When you say load there, what exactly do you mean? How many concurrent threads are running? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need correct 'order by' syntax where field does not contain NULL
Hi. I had a MySQL DB set up and recently added a field 'order' to allow for exceptions in a web site menu heirarchy. Fields should be ordered by 'order' field first where it does not contain 'NULL', and then by field 'title'. I had this previously: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY title ...and now, when I put in: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY order, title ...my menu shows nothing. How can I write my statement to allow for ORDER by order where 'order' does not contain 'NULL', and then title? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need correct 'order by' syntax where field does not contain NULL
select * from navigation WHERE id = '.$category.' AND active='y' AND order IS NOT null ORDER BY order, title Hi. I had a MySQL DB set up and recently added a field 'order' to allow for exceptions in a web site menu heirarchy. Fields should be ordered by 'order' field first where it does not contain 'NULL', and then by field 'title'. I had this previously: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY title ...and now, when I put in: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY order, title ...my menu shows nothing. How can I write my statement to allow for ORDER by order where 'order' does not contain 'NULL', and then title? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can one found out why a query is not cached?
At 17:02 -0700 5/3/04, Haitao Jiang wrote: I have a complex query which took 4 seconds, I set the query cache size to 512MB. BUt the query is not cached, it only returns 7 rows, so the cache size is not a problem here. Anyway I can find out why MySQL 4.1 is not caching my queries? The criteria for caching are given on this page: http://dev.mysql.com/doc/mysql/en/Query_Cache_How.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need correct 'order by' syntax where field does not contain NULL
WHERE id = id = '$category.' AND active ='y' AND order is not NULL ORDER by order,title -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 2:24 PM To: [EMAIL PROTECTED] Subject: Need correct 'order by' syntax where field does not contain NULL Hi. I had a MySQL DB set up and recently added a field 'order' to allow for exceptions in a web site menu heirarchy. Fields should be ordered by 'order' field first where it does not contain 'NULL', and then by field 'title'. I had this previously: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY title ...and now, when I put in: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY order, title ...my menu shows nothing. How can I write my statement to allow for ORDER by order where 'order' does not contain 'NULL', and then title? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can one found out why a query is not cached?
I have a complex query which took 4 seconds, I set the query cache size to 512MB. BUt the query is not cached, it only returns 7 rows, so the cache size is not a problem here. Anyway I can find out why MySQL 4.1 is not caching my queries? Thanks a lot Haitao __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Log
I'm pretty sure that the answer to this is No, you cannot but I figured I'd check anyway... As I go back through my query log, I'd like to know the user that issued the statement. If the user is still connected, I can cross reference it with the SHOW PROCESSLIST ID, but if they have signed off, is there a way to get the user then? Thanks, Lou
Reading MS Access tables from MySQL or Perl
Hello, This is partially off-topic as I am looking at reading Access tables from either Perl (preferred) or MySQL, but if anyone has any suggestions, I will be most grateful. My first thoughts were to write a Perl script which will read 2 columns from an MS Access table and load them into MySQL. It would be run at a click of a button from a web interface to display updated MySQL tables. However, after few hours of googling it appears that this is easier said than done due to ODBC and DSN quirks. Direct import into MySQL requires external utility like DBTools which defeats desired automation of the procedure. Is there a known best way to do this and has anyone done something similar? Thanks in advance for any hints. -- Nik Belajcic [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Integrating phpBB with an existing MySQL database
Jigal, I know that phpBB has a few converter scripts I think the converter scripts are for a different issue. I don't have a previous BBS system that I want to convert from. All I have is user profile information stored in a simple MySQL database that I built myself. The users are subscribed to a newsletter and also have some biographical information that appears on the web site. All I want to do is ensure that when they register for the newsletter and become a member of the site that they are automatically included in the site's phpBB forum. I think you will manage ;-) Thanks for the encouragement. Since after a day or so of my posting going out, no one has posted any Make sure you definitely don't do X warnings, I'm going to go ahead and play with it. Thanks to Joe for the password information and also especially de_RiN for offering more specific help offlist. -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading MS Access tables from MySQL or Perl
Nik Belajcic wrote: Hello, This is partially off-topic as I am looking at reading Access tables from either Perl (preferred) or MySQL, but if anyone has any suggestions, I will be most grateful. My first thoughts were to write a Perl script which will read 2 columns from an MS Access table and load them into MySQL. It would be run at a click of a button from a web interface to display updated MySQL tables. However, after few hours of googling it appears that this is easier said than done due to ODBC and DSN quirks. Direct import into MySQL requires external utility like DBTools which defeats desired automation of the procedure. Is there a known best way to do this and has anyone done something similar? Thanks in advance for any hints. I haven't done this myself, but one of my friends uses a perl thing call DBI::Proxy. You install it on the server ( ie machine with MS Access ) and client ( ie machine you'll be running the Perl scripts from ) and it *somehow* works. Sorry I'm a bit lean on the details. I've seen it working. Google for it. Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading MS Access tables from MySQL or Perl
Thanks for that tip. It helped me find a page which explains it properly. In case anyone else might be interested, here is the link. http://www.awilcox.com/geek_stuff/perl/proxy.html -- Nik Belajcic [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Documentation on character sets for version 4.0.17
Hi, I got the 4.0.17 documentation but when it discusses character sets it discusses this topic wrt 4.1 Where can I find documentation specifically in regards to the production version on how to work with, store, and search asian character sets specifically Japanese. Thank-you David Jrt; x-avg-checked=avg-ok-8ED7DEB Content-Disposition: inline --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.662 / Virus Database: 425 - Release Date: 4/20/04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
