Re: bash powered MySQL Queries
On Sun, 30 Jan 2005, Andy wrote: Hi all I just wanted to know what would be the easiest way to retrieve simple data from a MySQL database from a bash script. I do this a lot - just construct the query and dump it into a file from within the script, eg: echo select * from widgets where colour = 'red'; /tmp/query Then pipe the query into the mysql command line client and the result is echoed to stdin: $RESULT=`mysql -u user -ppassword widget_sales /tmp/query` and the variable $RESULT contains the result of your query. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spaces in table/column name
On Thu, 9 Dec 2004, sharif islam wrote: How mysql deals with spaces in table / column name? I am also using mysqlcc. If I try the following in the doesn't work. Creating table name with spaces from mysqlcc didn't give any error. But the following does: INSERT INTO 'tbl name with spaces' (col1, 'col name with spaces') VALUES(15,16); It really is a bad idea to use spaces and most non-alphanumeric characters in database, table and column names. Spaces are used as separators in most operating systems (recent versions of Windows and MacOS excepted) and although you may find you can create databases, tables and columns containing spaces if you enclose them in '' or quotes, you will sooner or later run into problems if you access these outside of MySQL or using MySQL running on a different system. If you must put in a space, why not use the '_' underscore character? This is legal in all operating systems I know of (MS-DOS doen't like the plain '-' hyphen, for example). Hope this helps, Andy # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data loss problem with mysql
On Wed, 24 Nov 2004 [EMAIL PROTECTED] wrote: Dear all, We are running mysql 4.0.17 on linux environment. Our database resides on external disk connected via FC cables. We recently noticed a loss of data in the following scenario. Inserted a row in a table in a separate transaction by a java application, queried a row in the table in a separate transaction by a java application and was successful. Then the FC cable connecting to external db disks was pulled and after sometime put it back Now the inserted row is missing in the database. From our logs, we have a query log that shows the inserted statement prior to FC cable disconnection. After cable pull, we have taken database dump that reveals the missing row that was inserted prior to FC cable disconnection. If somebody would have accidentally deleted, then we can expect the delete statement in the query log. But there is no delete statement in the query log. Can anybody help. What operating system(s) are you using for the system you are making the query from and also for the external database server? mysqld makes as much use of database server system memory as possible and a lot the live database will be cached in memory. If you insert a row and then read it back, it will be in the table but the table is in memory and hasn't necessarily been written to physical disk. Also, UNIX and Unix-like systems normally work with disk buffers so that when a file is written to, it is the disk buffer that is written to, not the physical disk itself. The disk buffers are then flushed out to disk every 30 seconds. It could be that the FC cable was unplugged during the buffer flush, causing the operating system to abort the flush and not update the file on the physical disk. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Low-end SATA vs. SCSI
On Fri, 12 Nov 2004, Fagyal Csongor wrote: Hi List, I am putting in a separate disk for our MySQL (4.1.7) server. I have some MyISAM, some InnoDB tables. Lots of reads, lots of writes (mostly atomic ones, insert/update one row), a few million rows per table, approx. 100-400 queries per second. What would you say is better (with respect to performance): a small SCSI disk (say 18G, 10kRPM) or a bigger SATA (say 120G, 7200RPM)? How about a 15kRPM SCSI disk? That's what I use and you can get them as large as 73GB. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with dbf and dbt
On Mon, 8 Nov 2004, José Antonio Viadas O. wrote: Can someone help me, i have two files one dbf and one dbt thant i need to import it in mysql, can someone help me telling me how can i do this. Have a look at dbf2mysql (http://dbf2mysql.soourceforge.net) - this will import .dbf files. I'm not sure if the current version of dbf2mysql will handle memo (.dbt) files though. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and partitions
On Wed, 3 Nov 2004, Yves Arsenault wrote: Hello all, I have MySQL installed on a Mandrake Linux system, it is installed on the /usr partition. I was wondering, is it possible to store some databases on the /var partition while MySQL is installed on the /usr partition? Yes. In fact, somewhere under the /var tree is the usual place for mysql databases to reside in many installations. And, if it is possible, can you store DB_A on /usr and DB_B on /var (or at least 2 different partitions). You can although current thinking in the UNIX/Linux world is that the /usr filesystem should be read-only, which means files directories that change all the time shouldn't be under /usr. But older UNIXes and Linuxes commonly use /usr/var, /usr/tmp and /usr/var/tmp for holding changing data so it's really up to you. If the databases are big and/or heavily used, I tend to put them on their own /mysql partition or even on their own fast disk. /home/mysql or /export/home/mysql is another possibility. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Two more Gmail invites
On Thu, 23 Sep 2004, John Meyer wrote: Just to let people know. And BTW, you have to say that you want the account. Reply off list. Onlist, I'd like to know how most people back up their Mysql dbs? XML or direct SQL file? I prefer the latter, although I'd like to hear from proponents of the former. I use mysqldump to dump entire databases to simple ASCII text files that can then be compressed. Then I can simply pipe the file into the mysql client to restore create them, eg: mysqladmin -u root -pxx create sound_sources cat sound_sources.dump | mysql -u root -pxx sound_sources The nice thing about working with mysqldump fiels is they're editable with any text editor, so you can massage tables, delete tables, etc or you can simply restore a single table instead of all of them. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question of mysql start up
On Wed, 15 Sep 2004, [GB2312] ÀîÈñ wrote: Hi I have installed some software on aix5.2 . the list of software: apache-1.3.29-1.aix4.3.ppc.rpm php-4.0.6-5.aix4.3.ppc.rpm MySQL-3.23.47-3.aix4.3.ppc.rpm MySQL-client-3.23.47-3.aix4.3.ppc.rpm All of them was installed by using the command : rpm -i *.* then Apache and php can work but mysql can't run when I used the command safe_mysqld start the system told me Starting mysqld daemon with databases from /var/lib/mysql 040913 19:08:45 mysqld ended when I used the command mysql the system told me ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) That message is coming from the mysql client as it can't connect to the mysqld server as that isn't running. Have a look in /var/lib/mysql for a file ending in '.err' - this will give you an idea of what is wrong. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql start up again
On Wed, 15 Sep 2004, [GB2312] ÀîÈñ wrote: Hi I have installed some software on aix5.2 . the list of software: apache-1.3.29-1.aix4.3.ppc.rpm php-4.0.6-5.aix4.3.ppc.rpm MySQL-3.23.47-3.aix4.3.ppc.rpm MySQL-client-3.23.47-3.aix4.3.ppc.rpm All of them was installed by using the command : rpm -i *.* then Apache and php can work but mysql can't run when I used the command safe_mysqld start the system told me Starting mysqld daemon with databases from /var/lib/mysql 040913 19:08:45 mysqld ended when I used the command mysql the system told me ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Many friends tell me to see the err log, the below is the err log(locaohost.err),but I can't find any useful things ,can anyone help me? 040915 19:14:06 mysqld started /opt/freeware/libexec/mysqld: unrecognized option `--key_buffer=16M' Why not try removing the option '--key_buffer=16M' from the safe_mysqld (or mysqld_safe) start up scripts? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and PHP
On Wed, 15 Sep 2004, nestor(earth) wrote: People, This more of a php mysql question. I have installed PHP ( 5.01) with Apache(1.31) and it runs. I have install Mysql (the latest as of last night) and it runs. My problem is that PHP does not see Mysql. Now I have done this installation 4 or 5 times but th elast time was over a year ago. Did you build both php and apache with MySQL support? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and PHP
On Wed, 15 Sep 2004, Greg Donald wrote: On Wed, 15 Sep 2004 16:02:45 +0100 (BST), andy thomas [EMAIL PROTECTED] wrote: Did you build both php and apache with MySQL support? I wasn't aware you could build Apache with MySQL support. How is that done? Apologies - I should have said just php. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql and PHP
On Wed, 15 Sep 2004, Chris Blackwell wrote: slightly off topic, but you can sort of build apache with mysql support. mod_auth_mysql allows you to authenticate users and groups against a DB http://sourceforge.net/projects/modauthmysql/ That's actually the reason I originally said I compiled my apache with MySQL support! I tend to build my apache httpd with modules like php and mod_auth_mysql statically compiled into the binary (for speed and performance reasons) rather than loaded at run-time as a dynamic module. Andy -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED] Sent: 15 September 2004 16:22 To: Greg Donald Cc: [EMAIL PROTECTED] Subject: Re: Mysql and PHP On Wed, 15 Sep 2004, Greg Donald wrote: On Wed, 15 Sep 2004 16:02:45 +0100 (BST), andy thomas [EMAIL PROTECTED] wrote: Did you build both php and apache with MySQL support? I wasn't aware you could build Apache with MySQL support. How is that done? Apologies - I should have said just php. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18
On Fri, 27 Aug 2004, Lehman, Jason (Registrar's Office) wrote: I am switching from a Windows computer to a Linux computer and when I dump the data from Windows to Linux I have no problem except for the fact that some of my characters have been converted to strange characters and when the data is displayed on a web page they show up as ?. I am sure that it has to do with character sets but I am not sure what to do about it. Any help would be appreciated. I suspect you are using MySQL version 4 or later? I'm not sure how/what you are using to extract the data and display it on a web page but we had a similar problem after we upgraded from MySQL 3.23.18 to 4.0.18 and our experiences may be of some help to you. We use Macromedia Cold Fusion MX 6.1 with apache on Linux - as Cold Fusion is an ODBC-oriented environment, they supply the Merant ODBC driver for MySQL to connect the two. After the upgrade, things like the UK pound symbol and apostrophes in text fields were being displayed as black squares or '?' on a web page even though they appeared correctly if viewed with the mysql command-line client. After a lot of investigation I eventually found that I had to add a parameter like: useUnicode=truecharacterEncoding=Windows-1252 to the ODBC/MySQL driver which solved the problem. It sounds to me as if you have a similar problem in your environment although the fix in your case will be different. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Response from Server
On Tue, 13 Jul 2004, s.ahmad wrote: thankyou for your kind attention. there are 50+ Reservation stations in the country. Where multiple operators are sitting and reserving seats. Indexes are being used in the DB. as i am related to hosting company, i havent seen if they are taking advantage of indexes. It could be a PHP problem rather than a MySQL problem. A PHP script error can cause the web server (apache, IIS, or whatever) to go to 99-100% CPU utilisation on a server that isn't running MySQL. Typical of this is where a PHP script tries to access or serve up a zero length plain text ASCII file - it will find it OK but as it has no bytes in it and, most importantly, no EOF character the script will hang and take the httpd server to 99% or 100% CPU unless it has been written to check for conditions like these. This will happen with just 1 single access to the page. Hope this helps, Andy regards, s.ahmad On Mon, 12 Jul 2004 22:36:10 -0800, Joshua J. Kugler [EMAIL PROTECTED] wrote: Can you give us more insight into your database layout? Are you using indexes? How many clients are accessing it? What kind of queries? Are those queries written to take advantage of the indexes? j- k- On Monday 12 July 2004 10:28 pm, s.ahmad said something like: Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- 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] -- s.ahmad [EMAIL PROTECTED] www.shakeelahmad.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restore from mysqldump file
On Fri, 2 Jul 2004, Chuck Barnett wrote: Hello, I have a huge problem that you guys may be able to help me with. I did a mysqldump all databases into a sql71.sql file. when I try and I get the following error when trying to restore ERROR 1050 at line 204528: Table 'columns_priv' already exists I'm sure that this is for the mysql database. Im doing this to restore mysql --user=root -p sql71.sql please give me some help on thisthe .sql file is 170+megs in size. It's best to backup databases individually and then you can restore all of them except for the mysql control database, which is clearly present in your case. About the only thing I can think of is to open your sql71.sql dump and edit out the mysql database. This will fix the problem but you will need to use a system with a lot of free memory to do the edit. Hope this helps. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Federated servers
On Tue, 29 Jun 2004 [EMAIL PROTECTED] wrote: Hello! We are currently running with one big ms sql-server. Is it possible to do federated servers on mysql? What I want to do is to purchase one more server and split the work load on the two servers. Yes, you can use master/slave replication in MySQL to share the load across multiple serevrs. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RES: ORDER BY problem
On Tue, 8 Jun 2004, Renato Cramer wrote: Hello Andy, I don't known if this is possible without handle string, what I don't guess recommended, because of performance and legibility of code. One suggestion will be store in column 'surname' (or other) the data already in format of sort. In other words, will be two columns in table, and, depending on approach, the second column will can be disabled for the users. Example: Name: Marco van Basten Archive: Basten, Marco van I hope that helps. Well, this was fixed in the end by this query: select substring_index(surname,' ',-1) as r from advisers order by r which produced the desired result. But we have since had complaints from individuals wanting their surnames sorted differently! People from Germany with surnames such as 'von Neumann' like to have this sorted with the V's and not the N's while people from the Netherlands with 'van den Berg' want it to be with the B's and not the V's. We are now redesigning the table to allow records to be displayed in a specific order chosen by the administrator, rather than trying to do this automatically by a SELECT statement. Thanks for your suggestion anyway. Andy -Mensagem original- De: andy thomas [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 8 de junho de 2004 08:51 Para: [EMAIL PROTECTED] Assunto: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: ORDER BY problem
On Tue, 8 Jun 2004, Franz, Fa. PostDirekt MA wrote: Hi, it is not possible to handle all cases proper. You can just handle all cases you know with the REPLACE-function, so you simply delete the prefixes in the WHERE-clause. But that only works for all prefixes you know. If you do like ORDER BY REPLACE(REPLACE(surname,'du',''),'de','') you get all 'du Sautoy' and 'de Contes' exactly like you wanted it but 'de la Tour' will still apear at the wrong place. I don't think you can be sure to remove all prefixes like this, because you can't be sure to know all of them. A different trick would be to say allways take the last 'word' in the surname, which is much more efficent, but will unfortunally not work with double names like 'Schwarzenegger Schriver' (Maybe they are written with a '-' in it, it's just to show the principle). This is what I did in the end, to alwyas use the last word that's separated by a space. So, there is not lot the world can learn from germany, but we treat all these prefixes like they belong ti the name, which means 'von Hohenzollern' is correctly ordered among the the v's and not the h's. Well, we have run into this problem already since I 'fixed' the ordering! As there are people from all over the world using this database, we are now about to redesign the table to allow individual people to decide where they want their surname to appear in the listing. Thanks for your suggestions, Andy -Ursprüngliche Nachricht- Von: andy thomas [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 8. Juni 2004 13:51 An: [EMAIL PROTECTED] Betreff: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE - Order By Problem
On Tue, 8 Jun 2004, Andy Eastham wrote: Andy, Just: select substring_index(surname,' ',-1) as r from advisers order by r; Yes, that did the trick! Thanks, Andy -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 15:57 To: Andy Eastham Cc: Mysql List Subject: RE: RE - Order By Problem On Tue, 8 Jun 2004, Andy Eastham wrote: Look at using the Reverse() function, then take the substring up to the first space, then reverse the result. Well, 'select substring_index(surname,' ',-1) from advisers' does the trick as far as extracting the wanted parts of surnames at the end of the surname filed but I'm not sure how to use this as an argument to ORDER BY? Shouldn't something like: select substring_index(surname,' ',-1) as r from advisers, select * from advisers order by r work? Thanks for your help, Andy -Original Message- From: Paul McNeil [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 14:04 To: [EMAIL PROTECTED] Subject: RE - Order By Problem I have never done anything like this but after looking at the spec's I have a possible direction for you In String functions there is LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. I think that if you create a function that uses this to strip the string to the left of the last found space and that returns the string to the right you could call this in your query and use it in the order by statement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE - Order By Problem
On Tue, 8 Jun 2004, Michael Stassen wrote:
The proposed solution to sort on a portion of the surname field will work,
but it has a drawback. If you sort on the result of a function applied to a
column, you prevent the use of any index on that column. If your data set
and user base are both small, this may be a problem you can ignore, but it
won't scale well. Also, I expect you will want 'de la Tour' to come before
'du Tour', so you'll have to do a secondary sort on surname.
The table is quite small with only 33 records at present although it gets
accessed maybe 10K times a day.
I'd like to suggest an alternate solution. In your current scheme, you
would put 'de la Tour' in your surname column, but you are saying that
'Tour' is the part to sort by, while 'de la' is not. To my mind, that means
'de la' and 'Tour' are different kinds of data, which means they belong in
different columns -- surname_prefix and surname, perhaps. Then you can
concatenate surname_prefix and surname for display purposes, but sort on
just surname (or surname, surname_prefix, first_name), and an index on
surname (or surname, surname_prefix, first_name) could be used.
For example:
SELECT * FROM advisers;
++++--+
| id | first_name | surname_prefix | surname |
++++--+
| 1 | Michael| NULL | Stassen |
| 2 | Max| van den| Berg |
| 3 | Sylvia | du | Sautoy |
| 4 | Alicia | NULL | Davidson |
| 5 | Marco | van| Basten |
| 6 | Andy | NULL | Thomas |
| 7 | Michelle | de | Contes |
| 8 | Gabrielle | de la | Tour |
| 9 | Joe| NULL | McNeil |
| 10 | Chris | NULL | Brown|
++++--+
10 rows in set (0.30 sec)
SELECT first_name, CONCAT_WS(' ', surname_prefix, surname) AS last_name
FROM advisers
ORDER BY surname;
++--+
| first_name | last_name|
++--+
| Marco | van Basten |
| Max| van den Berg |
| Chris | Brown|
| Michelle | de Contes|
| Alicia | Davidson |
| Joe| McNeil |
| Sylvia | du Sautoy|
| Michael| Stassen |
| Andy | Thomas |
| Gabrielle | de la Tour |
++--+
SELECT CONCAT_WS(' ', first_name, surname_prefix, surname) AS name
FROM advisers
ORDER BY surname, surname_prefix, first_name;
+--+
| name |
+--+
| Marco van Basten |
| Max van den Berg |
| Chris Brown |
| Michelle de Contes |
| Alicia Davidson |
| Joe McNeil |
| Sylvia du Sautoy |
| Michael Stassen |
| Andy Thomas |
| Gabrielle de la Tour |
+--+
Yes, this is one way of doing this. But having adopted an alternative
solution based on a suggestion from Andy Eastham, it now turns out that
the users of the database from different countries have different ideas
of how we should be ordering surnames! So to keep everyone happy, the
table is being redesigned to allow entries to be ordered as the users
want them ordered, rather than the way *we* think they should be ordered.
Complicated but that's life...
cheers,
Andy
andy thomas wrote:
On Tue, 8 Jun 2004, Andy Eastham wrote:
Look at using the Reverse() function, then take the substring up to the
first space, then reverse the result.
Well, 'select substring_index(surname,' ',-1) from advisers' does the
trick as far as extracting the wanted parts of surnames at the end of
the surname filed but I'm not sure how to use this as an argument to
ORDER BY? Shouldn't something like:
select substring_index(surname,' ',-1) as r from advisers, select * from
advisers order by r
work?
Thanks for your help,
Andy
-Original Message-
From: Paul McNeil [mailto:[EMAIL PROTECTED]
Sent: 08 June 2004 14:04
To: [EMAIL PROTECTED]
Subject: RE - Order By Problem
I have never done anything like this but after looking at the spec's I
have a possible direction for you
In String functions there is LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of substring
substr in string str. The second syntax returns the position of the first
occurrence of substring substr in string str, starting at position pos.
Returns 0 if substr is not in str.
I think that if you create a function that uses this to strip the string
to the left of the last found space and that returns the string to the right
you could call this in your query and use it in the order by statement.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL
ORDER BY problem
In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE - Order By Problem
On Tue, 8 Jun 2004, Paul McNeil wrote:
I have never done anything like this but after looking at the spec's I have
a possible direction for you
In String functions there is
LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of substring
substr in string str. The second syntax returns the position of the first
occurrence of substring substr in string str, starting at position pos.
Returns 0 if substr is not in str.
Yes, this is the approach I was thinking of using but:
select locate(' ','surname',1) from advisers
just returns 0 for all records, whether or not they contain the ' ' space
substring.
I think that if you create a function that uses this to strip the string to
the left of the last found space and that returns the string to the right
you could call this in your query and use it in the order by statement.
Well, this would probably work if I could get the above statement to work.
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE - Order By Problem
On Tue, 8 Jun 2004, Vadim P. wrote:
If surname is a field, then use it without the single quotes ('),
otherwise it is treated as a literal string and 0 is the correct result:
select locate(' ',surname,1) from advisers
Thanks a lot, this is working. I now need to figure out how to use the IF
syntax, etc (not done this before ;-) so that the result from thsi query
can be used as an argument for the next.
cheers,
Andy
andy thomas wrote:
Yes, this is the approach I was thinking of using but:
select locate(' ','surname',1) from advisers
just returns 0 for all records, whether or not they contain the ' ' space
substring.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE - Order By Problem
On Tue, 8 Jun 2004, Andy Eastham wrote: Look at using the Reverse() function, then take the substring up to the first space, then reverse the result. Well, 'select substring_index(surname,' ',-1) from advisers' does the trick as far as extracting the wanted parts of surnames at the end of the surname filed but I'm not sure how to use this as an argument to ORDER BY? Shouldn't something like: select substring_index(surname,' ',-1) as r from advisers, select * from advisers order by r work? Thanks for your help, Andy -Original Message- From: Paul McNeil [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 14:04 To: [EMAIL PROTECTED] Subject: RE - Order By Problem I have never done anything like this but after looking at the spec's I have a possible direction for you In String functions there is LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. I think that if you create a function that uses this to strip the string to the left of the last found space and that returns the string to the right you could call this in your query and use it in the order by statement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generating an automatic e-mail via MySQL
On Wed, 12 Feb 2003, Dan Tappin wrote: Does any one have a suggestion on running a daily / weekly e-mail notification based on results from a MySQL query? I have a table with date sensitive rows. The idea that as rows become stale (they were created / updated more than a week or month ago) the owner of the row is sent an e-mail with a summary of the stale items or even just a link back to a web page for updating. The e-mail addresses would come from a related 'user' table. The stale data table would have the 'user' id in a column for a JOIN statement. I do something similar using a shell script run by cron. This pipes a query into mysql like this: cat mysql_command.list | mysql -u user -ppassword database where the file mysql_command.list is a plain text file containing the query which uses SELECT INTO OUTFILE to dump fields containing membership IDs, names and email addresses of all entries between two dates of a membership database into a CSV file. Then the script continues using sed and cut to create a mail message body and header from the data in this CSV file which can be piped into mail (/bin/mail, /bin/mailx or whatever you have on your system) or even directly into sendmail. It works fine. Andy - 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: Serwer Hardware p4 or pIII ?
On Wed, 4 Dec 2002, Helmut Apfelholz wrote: Hi, I am assembling mysql only server. I am planning 2Gb RAM, 4 x 15k SCSI disks. However I cannot decide if I should get 2 p4 processors or 2 tuallatin pIII. I could not find any mysql specif?c benchmarks, that would show which processors I should use. I will be running linux on the server. Could anyone share his/hers experience with me ? I have often wondered about that myself so I would be interested in other people's views. I currently run a number of servers with dual 1GHz P3's. Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PHP bias
On Sat, 9 Nov 2002, R. Hannes Niedner wrote: On 11/10/02 5:12 AM, Robert Macwange [EMAIL PROTECTED] wrote: I am bothers me. It bothers me that that the MySQL people have a bias towards PHP. PHP is an inferior language. Deal with perl instead. Robert Ouch.Death to all fanatics!!! Do you have any question or are you just boiling? It's just that MySQL and PHP integrate together so well. The authors of PHP have gone to great lengths to create extensive interfaces to a wide range of databases, not just MySQL. perl doesn't have anything like as much database support, which is why PHP has become popular in the web community. Andy - 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 4.x for debian?
On Wed, 6 Nov 2002, von Boehn, Gunnar wrote: Hello, what is the status of MySQL 4.x for debian? Does MySQL AB plan to release deb paketes or will MySQL AB support or encourage the debian pakete maintainers? I think MySQL policy is to support generic packaging formats applicable to all platforms. Proprietary packaging formats such as those used by Debian, Red Hat, etc are not directly supported but contributed by others. regards, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT COUNT
On Fri, 27 Sep 2002, Michael J. Fuhrman wrote: Hello All, I seem to be running into a very strange problem. In the mySQL command line interface I issue SELECT COUNT (*) FROM The correct syntax is SELCT COUNT(*) ie, there's no space between SELECT and the (*). Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug ?
On Mon, 2 Sep 2002, Marian wrote: Why mysql corrupt tables if filesystem if full ... ? On good database servers (eg. PROGRESS) process shutdown server (protetcting data). sytem: linux-2.2.19 mysql-3.23-38 Good sys admins don't let filesystems become full Andy - 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: Moving a DB from one Server to another
On Mon, 8 Jul 2002 [EMAIL PROTECTED] wrote: Todd Cary writes: Is there a quick and simple way to move a MySQL DB from one server to another MySQL server? Can I just copy the tables? for as far i know, you can just copy the directory ( DB ) from one server to the other. i've done it a couple times. and it stil works :) I use mysqldump to create dumps of all the databases/tables, ftp or sftp them to the other server, create new databases there using mysqladmin and then pipe the dump into mysql, eg: cat database.dump | mysql -u root -p database Andy - 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.com site down
On Tue, 2 Jul 2002, Darley, Terry wrote: I'm in the UK and I can get to it okay !!! Perhaps something has changed on your PC/Network ??? I've had problems reaching the MySQL web site too. I think it's to do with the fact I'm connecting through a UK academic site which relies in ebone for European connectivity. ebone have gone bust I think ... Andy -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED]] Sent: 02 July 2002 10:45 To: [EMAIL PROTECTED] Subject: RE: fulltext searching Hi If anybody from mysql is listening / watching, www.mysql.com is down, and has been for a while. Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Stored procedures
Does anyone know if stored procedures have been implemented in MySQL? Getting into the MySQL web site from the UK is almost impossible these days so I thought I'd ask here. Thanks in advance for any information, Andy - 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: Stored procedures
On Thu, 27 Jun 2002, Gerald Jensen wrote: Yes ... we all know that stored procedures have not been implemented in MySQL. I thought as much but just wanted rapid confirmation. Sorry to be so terse, but this is about the umpteenth posting of this same question this week ... doesn't anybody read the list or search the archive before they post stuff? I suspect the e-bone problems over in Continental Europe are the reason we in the UK are getting an intermittent mailing list feed and almost no access at all to the MySQL site. Sorry if I upset anyone, Andy - Original Message - From: andy thomas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 7:01 AM Subject: Stored procedures Does anyone know if stored procedures have been implemented in MySQL? Getting into the MySQL web site from the UK is almost impossible these days so I thought I'd ask here. Thanks in advance for any information, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL for Digital UNIX V4.0F (Rev. 1229) binary?
On Thu, 18 Apr 2002, Atila Hajnal wrote: We have Digital UNIX V4.0F (Rev. 1229) and we will to run MySQL but only binary is for OSF 5.1 version. This isn't adecvate. Can anybody help us in compiling source or have anybody binary version (just compiled)? I have a version 3.23.18 binary built under DU 4.0D if that's of any help to you. Should run OK under 4.0F. If you can wait a week or so, I'm about to build 3.23.49 on this platform and you can have that binary also. Andy - 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: Do any of your applications work?
On Sat, 6 Apr 2002 [EMAIL PROTECTED] wrote: I sense this could be a troll, but... /* Hugh O'Loughlin [[EMAIL PROTECTED]] writes: */ . . Frankly, the agreement your client should look at cancelling is the one with your company. Your inability to install MySQL notwithstanding, jumping into a public forum whining and crying that things don't work the way you think they should, providing little to no information on what doesn't work means in your case, and finally threatening that a CLIENT may cancel a licensing agreement... Sir, other words come to mind, Pompous twit? ;-) Andy - 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 Power ?
On Fri, 5 Apr 2002, Steve Rapaport wrote: On Friday 05 April 2002 06:37 pm, andy thomas wrote: On Fri, 5 Apr 2002, Steve Rapaport wrote: With InnoDB, I'm sure this problem goes away, but as soon as we go to InnoDB, we have to pay for backups and support, which means we start looking around at 'pay' solutions. Why do you suddenly have to pay for backups and support? We have to pay for backups because backing up an Innodb table and restoring it reliably is not a simple matter of locking the table and copying the files, like in a MyISAM table. To do it reliably it looks to me like you'll need Heikki's InnoDBHotCopy module, which costs money. mysqldump works fine with Innodb although this may not be the complete answer in your case if you have a busy database. We have to pay for support for a similar reason: We're using replication for failover, and I've already had cases where replication fails for one reason or another. In these cases, recovering replication was tedious but possible. Reading through the manual for Innodb it looks (actually looked, it's changed for the better lately) like this would become hellish and unlikely to succeed. So I'd be paying for support to keep replication working, I think. I've not used replication yet so I can't really comment on this aspect. OK, point taken. cheers, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table statistics
On Thu, 4 Apr 2002, Doug Bishop wrote:
Try:
?
mysql_connect(localhost, user, password);
$query = SHOW TABLES;;
$result = mysql_db_query(databasename, $query);
$i = 0;
while ($row = mysql_fetch_array($result))
{
$tableNames[$i] = $row[0];
$i++;
}
for ($i = 0; $i = count($tableNames); $i++)
{
$query = SELECT COUNT(*) FROM . $tableNames[$i] . ;;
$result = mysql_db_query(databasename, $query);
$row = mysql_fetch_array($result);
echo Table . $tableNames[$i] . contains . $row[0] . rows.\nbr;
}
?
Yes but this is PHP code that can only be run through a suitable web
server, browser, etc - I want a command line solution like mysqlshow ...
or from within the mysql client itself.
Andy
-Original Message-
From: andy thomas [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 03, 2002 11:23 PM
To: [EMAIL PROTECTED]
Subject: Table statistics
Is there a command I can give in the mysql client to find the number of
rows in a table or, better still, the number of rows in all the tables in
a database?
Andy
-
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: Table statistics
On Wed, 3 Apr 2002, Nick Arnett wrote: -Original Message- From: Doug Bishop [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 10:13 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Table statistics Try: ? mysql_connect(localhost, user, password); ... Might be helpful to the original poster to mention that this is Perl. He didn't specify a language, so he may not comprehend what you've offered. This is actually PHP which I use a lot myself. But I wanted to know if there was a simple command for this within mysql, or some option to mysqlshow. It's far quicker to type somethinmg on the command line than fire up a web browser, etc. And there must be a lot of MySQL installations on servers which aren't also web servers, or don't have PHP installed, etc. Actually, it would be very nice if MySQL had a shell API - you can do this to some extent by piping a command file into it but it involves a fair amount of work to get the same functionality you get with, say, the C or PHP interfaces. I don't mean this as a criticism, but it's interesting that we seem to often assume that Perl is THE language for scripting MySQL. Not true, of course. In fact, I'm finding Python easier, except for the blasted printf type formatting, whose syntax was driving me batty yesterday until... well, I could go on, but I won't. Try PHP? ;-) cheers, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
On Thu, 4 Apr 2002, denonymous wrote: From: andy thomas [EMAIL PROTECTED] Is there a command I can give in the mysql client to find the number of rows in a table or, better still, the number of rows in all the tables in a database? To return the # of rows in a table: SELECT COUNT(*) FROM table_name; Thanks, this works fine! I keep apache access logs for web servers in MySQL databases and was wondering why the database for March's logs on a particular server was about a third of the size of February's even though the access stats looked about the same for those months. Hence the need to compare the table sizes. cheers, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
On Thu, 4 Apr 2002, Georg Richter wrote: On Thursday, 4. April 2002 10:42, andy thomas wrote: Yes but this is PHP code that can only be run through a suitable web server, browser, etc - I want a command line solution like mysqlshow ... or from within the mysql client itself. You can also run PHP from the commandline without any webbrowser. I did ask about this recently over in the php-general mailing list and someone said it was possible to do this in the Windows implementation and building a stand-alone PHP for Unix was documented in the INSTALL file. But I couldn't find it. Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table statistics
Is there a command I can give in the mysql client to find the number of rows in a table or, better still, the number of rows in all the tables in a database? Andy - 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: Coldfusion,Mysql,Myodbc
On Tue, 26 Mar 2002, [EMAIL PROTECTED] wrote: Hi there . I am new to Mysql.I have to access mysql from coldfusion..and we dont get myODBC as binary for solaris 6 ot 8. so we have to compile them...and i am not able to compile the myODBC bit...its giving compilation problem..Any suggetions from you guys. ColdFusion 4.5.1 and later ship with the Merant ODBC drivers for MySQL and you should be using these. Note that the cfodbc45.so driver shipped with ColdFusion Enterprise Server 5.0 (which lives in ../coldfusion/lib) is broken and you can download a fixed version from Macromedia's web site. Andy - 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: Coldfusion,Mysql,Myodbc
On Tue, 26 Mar 2002, Xavier Prelat wrote: Even if you are using the CFServer 5.0 you will not be able to edit any MySQL DSN with the ColdFusion DSN web admin tool. First install the MySQL ODBC on your server, then create any DSN you need using the ODBC Sources tool (administrator tool on Win 2000). Once you created the DSN you want, the CF let you manage the DSN through the web admin interface! Hmmm, that is odd. We have had no problem editing MySQL DSN's with the Merant drivers, although where you are asked for the MySQL server's hostname (and the MySQL server is on the same system as Cold Fusion), you must give the full hostname, not just localhost. If you use 'localhost' it will work initially but if you need to restart cfserver for any reason, then it will not be able to verify the DSN again, unless you have used the full Internet address of teh MySQL server. Andy -Message d'origine- De : andy thomas [mailto:[EMAIL PROTECTED]] Envoye : mardi 26 mars 2002 13:14 A : [EMAIL PROTECTED] Cc : [EMAIL PROTECTED]; [EMAIL PROTECTED] Objet : Re: Coldfusion,Mysql,Myodbc On Tue, 26 Mar 2002, [EMAIL PROTECTED] wrote: Hi there . I am new to Mysql.I have to access mysql from coldfusion..and we dont get myODBC as binary for solaris 6 ot 8. so we have to compile them...and i am not able to compile the myODBC bit...its giving compilation problem..Any suggetions from you guys. ColdFusion 4.5.1 and later ship with the Merant ODBC drivers for MySQL and you should be using these. Note that the cfodbc45.so driver shipped with ColdFusion Enterprise Server 5.0 (which lives in ../coldfusion/lib) is broken and you can download a fixed version from Macromedia's web site. Andy - 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: ERROR 2002: Can't connect to local MySQL server through socket'/tmp/mysql.sock' (2)
On Tue, 26 Mar 2002, colin o wrote: Same thing happens to me, i just use bin/safe_mysqld --user=root to start to server so that i can use mysql. I don't know exactly why it happens though. Possibly permissions?? It is better to run mysqld as a non-root user, such as mysql. --- Mike Yrabedra [EMAIL PROTECTED] wrote: What would cause this error to occur all of a sudden? ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Is the socket? It should show up in a directory listing of /tmp - if it isn't there then mysqld isn't running. Andy - 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
MS Access to MySQL conversion
We have a Unix server which in addition to running MySQL is also a fileserver for a group of MS Windows PCs (using samba) and it would be useful to be able to convert the Microsoft Access .mdb files put there by users directly to MySQL, or at least to .csv format. Does anyone know of such a utility? Andy - 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: High Availability questions
On Tue, 19 Mar 2002, Young Sul wrote: Hi, I've got a website that uses a mysql backend database. Due to the way in which the database and development has been architected, I'm unable to load-balance the database between multiple DB servers, taking advantage of replication inherent in mysql. (this is due largely to developmental constraints, and heavy use of write only session-management) I *need* to somehow mirror and create a decent failover environment for my database. Currently, I mirror the master DB on another server, and can switch over ...manually... if the master dies. I'm wondering if others on this list have encountered a similar situation -- how did you finally architect your DB environment? I run a number of web servers all of which are mirrored by a second identical failover server. During normal office hours when the sites are busiest, the main server dumps all databases every hour to an NFS share and the failover server first drops all the backup databases and then imports them from the share. (The two servers have a private dedicated netwotk conection between them for this, separate from the main public Internet connections). The sites themselves, httpd configuration files, clinet FTP account info, etc are mirrored every night by simply updating all the files that have changed during the previous 24 hours. The failover server simply pings the main server every minute - if it fails to get a response for 2 minutes, then it stops Apache, Cold Fusion servers, etc, areconfigures its network interfaces to use the IP addresses of the failed server, replaces the Apache httpd.conf with the last one from the main server and restarts Apache. Outside of normal office hours and at weekends, the MySQL databases are synchronised every 3 hours and all changes made to the databases are logged using the NySQL logging feature. This can be 'played' back from the last update to reflect any changes made since the previous update. Simple but effective. Now that version 4.0.x is here, we might look at using the master/slave replication features this offers but there's no great rush and we need to do it in such a way that downtime on existing servers is minimised. Andy - 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
