load strips first character
Hello, I am loading data from a text file where filed3 is a path to a document in a format \\path\to\some\directory but after loading into MySQL it appears as \path\to\some\directory with the first backslash stripped away. Why is this happening and how can I avoid it? LOAD DATA INFILE '/local/datadocument.txt' INTO TABLE tdocument FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3); Thanks, -- 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: SUM same field twice in one SELECT?
With the query: SELECT SUM(IF(cur = 'EUR',amount,0)) as Amount, euro, SUM(IF(cur = 'FIM',amount / 5.94573,0)) as Amount, fim - euro, SUM(IF(cur = 'SEK',amount / 9.294,0)),0)) as Amount, sek - euro FROM table1; I get the total amount spent -- converted into one currency, euro -- for the respective currencies. But it would also be nice to have a grand total of money spent. That is, now that I know how to convert the sums by exchange rate, is there a way for me to sum it all up; i.e. Amount, ,euro + Amount, fim - euro + Amount, sek - euro = grand total spent, converted into euro? (That's what I was trying to do with the nested sum. :) ) Kristian Yoed Anis wrote: Hmm, I'm not exactly sure that I understand what you are trying to do now. But why do a nested sum? Wouldn't one sum be enough? After all you are already taking into consideration the currency change by the division factor. Yoed -Original Message- From: Kristian Niemi [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 1:23 AM To: Yoed anis Subject: Re: SUM same field twice in one SELECT? Thanks! That enabled me to convert the different currencies into one specific currency. Now, if I could bother you a bit more, and ask how I would do to get a grand total of, say, SEK and EUR now that they're converted into the same currency? How to sum the sums, so to speak. I tried a nested sum: SELECT SUM(IF(cur = 'EUR',amount + SUM(IF(cur = 'FIM',amount / 5.94573,0)) + SUM(IF(cur = 'SEK',amount / 9.294,0)),0)) FROM table1; But that only got me a Invalid use of group function error-message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext index -- word needs not found
Hi Joyce,
needs is a stopword, that's why it's not indexed or found. You can
use your own ft_stopword_file to define the list without needs. The
default, built-in stopword list is defined in, I think, the
myisam/ft_static.c file of the source distribution, for reference.
Hope that helps.
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent: Wednesday, April 28, 2004 12:01 AM
Subject: fulltext index -- word needs not found
Description:
We have three different unrelated tables, each with one field that
has a fulltext index. In each table, in the field with the fulltext
index, there are records with the word needs in them, and in each
case no
records are found when doing this type of query (using a fulltext
index):
select * from testdb where match(highergeog) against('needs' in
boolean mode);
However, records are found when doing substring searches:
select * from testdb where highergeog like '%needs%';
snip
Also I know someone running 4.0.15 on linux, and needs can't be
found
in fulltext queries on his data also, even though the word exists in
the data.
Perhaps this is all solved in 4.0.18?
Fix:
Don't use fulltext index--use substring search.
But substring is slower than fulltext.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
column names in select
'lo is it possible to suppress (i.e. not display) the column names from the resultset of a select statement? if so, how? tks tom Disclaimer http://www.shoprite.co.za/disclaimer.html
Re: Mysql for Family History (genealogy)
On Tue, 27 Apr 2004 15:47:19 -0400 (EDT), wrote: Hi, Anyone designed a MySql database for family history? Any ideas,recommendations, problems ? You can try asking the GRAMPS team (http://www.gnu.org/directory/gramps.html). I seem to remember that they are playing around with using a database (right now, only in CVS). Don't know if it's specifically MySQL or not. The current release of GRAMPS stores its information in an XML file. I'm looking for something a little simpler. I just thought somebody might have designed a mysql datastructure. zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXISTS : in MySQL 4.0.* ?
Does EXISTS exist in 4.0.15 ? or is it introduced in 4.1.*. If it does exist, what is incorrect in the syntax above ? (it matches the MySQL manual example from what I can see). Subqueries (http://dev.mysql.com/doc/mysql/en/Subqueries.html) were only introduced in version 4.1, so I don't think that your query is going to work in any version before 4.1... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EXISTS : in MySQL 4.0.* ?
Hi, I'm using MySQL 4.0.15 and have been trying to use EXISTS/NOT EXISTS ... with no success - getting You have an error in your SQL syntax. I've got 2 tables A and B and am trying something like this SELECT THIS.A_ID FROM A THIS WHERE NOT EXISTS (SELECT THIS_ITEMS.B_ID FROM B THIS_ITEMS WHERE THIS.A_ID=B.A_ID) Does EXISTS exist in 4.0.15 ? or is it introduced in 4.1.*. If it does exist, what is incorrect in the syntax above ? (it matches the MySQL manual example from what I can see). TIA -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql for Family History (genealogy)
zzapper [EMAIL PROTECTED] schreef in bericht news:[EMAIL PROTECTED] On Tue, 27 Apr 2004 15:47:19 -0400 (EDT), wrote: Anyone designed a MySql database for family history? This http://members.shaw.ca/pythiangenealogy/ site has a MySQL based genealogy program. The program is shareware, but the downloadable file contains all the .sql files needed to setup the db ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXISTS : in MySQL 4.0.* ?
Andy Jefferson [EMAIL PROTECTED] wrote: Hi, I'm using MySQL 4.0.15 and have been trying to use EXISTS/NOT EXISTS ... with no success - getting You have an error in your SQL syntax. I've got 2 tables A and B and am trying something like this SELECT THIS.A_ID FROM A THIS WHERE NOT EXISTS (SELECT THIS_ITEMS.B_ID FROM B THIS_ITEMS WHERE THIS.A_ID=B.A_ID) Does EXISTS exist in 4.0.15 ? or is it introduced in 4.1.*. If it does exist, what is incorrect in the syntax above ? (it matches the MySQL manual example from what I can see). EXISTS is supported from 4.1. In 4.0.x you can rewrite your query using LEFT JOIN: http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.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]
Re: column names in select
Tom Roos [EMAIL PROTECTED] wrote: is it possible to suppress (i.e. not display) the column names from the resultset of a select statement? if so, how? For command-line mysql client use --skip-column-names (-N) option: http://dev.mysql.com/doc/mysql/en/mysql.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]
mysqlimport and \ as data
Hi Group, Does anybody have any expierence with mysqlimport and a comma separated file with data wich contains field data with a: \. For example I have the data in de column path: C:\temp. When I import this I got something like : c:||emp ... Does anybody know how to solve this problem? This \ occurs to much to do the job manually ;-) Thanks a lot. Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column names in select
is it possible to suppress (i.e. not display) the column names from the resultset of a select statement? if so, how? What about only selecting the columns you need? 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]
MySQLd crash within a UDF on Linux
Description:
I'm experiencing a Linux-related only problem with a simple UDF which call
mysql_real_connect()
How-To-Repeat:
A very simple function:
#include stdio.h
#include stdlib.h
#include string.h
#include my_global.h
#include my_sys.h
#include mysql.h
#include m_ctype.h
#include m_string.h
longlong myfunc(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error )
{
MYSQL mysql;
mysql_init(mysql);
if (!(mysql_real_connect(mysql,localhost,root,,test, 0 , NULL , 0 )))
{
*error=1;
return (longlong)(-1);
}
mysql_close(mysql);
return (longlong)1;
}
compiled with
gcc -I /usr/include/mysql -shared -o libmyfunc.so myfunc.c -lmysqlclient_r
The thread crashes with:
thd=0x847bc78
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x43be4d0c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x810cdbc
0xe420
0x1
0x43bf47c1
0x80d0042
0x80d0082
0x80c5061
0x8100ce7
0x813c64b
0x813b5d0
0x811b08b
0x811f60e
0x811a066
0x8119a57
0x8119025
0x401b39b4
0x847c89c
New value of fp=0x43be6bb0 failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions
on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x84761b0 = select myfunc(prova)
thd-thread_id=1
I'm available to provide more details, anyway it seems crashing within a syscall.
If I run the program with --one-thread it does not crash but returns NULL.
I could provide more details on request.
Fix:
None known.
Submitter-Id: frankie
Originator:Francesco Paolo Lovergine
Organization: Debian
MySQL support: none
Synopsis: udf problem on linux
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.18 (Source distribution)
C compiler:gcc (GCC) 3.3.3 (Debian 20040401)
C++ compiler: g++ (GCC) 3.3.3 (Debian 20040401)
Environment: Debian GNU/Linux, arch i386, libc and gcc below.
System: Linux klecker 2.6.5-1-686 #1 Sat Apr 24 08:47:10 EST 2004 i686 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.3/specs
Configured with: ../src/configure -v
--enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr
--mandir=/usr/share/man --infodir=/usr/share/info
--with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib
--enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu
--enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc
i486-linux
Thread model: posix
gcc version 3.3.3 (Debian 20040401)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 2004-04-24 22:22 /lib/libc.so.6 -
libc-2.3.2.so
-rw-r--r--1 root root 1243792 2004-04-21 20:58 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2640378 2004-04-21 20:59 /usr/lib/libc.a
-rw-r--r--1 root root 204 2004-04-21 20:34 /usr/lib/libc.so
lrwxrwxrwx1 root root 28 2004-03-24 10:01
/usr/lib/libc-client.so.2002edebian - libc-client.so.2002edebian.1
-rw-r--r--1 root root 767184 2004-03-22 23:31
/usr/lib/libc-client.so.2002edebian.1
Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql'
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include'
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared'
'--enable-static' '--enable-thread-safe-client' '--enable-assembler'
'--enable-local-infile' '--with-raid'
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql'
'--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server'
'--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline'
'--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb' '--with-debug'
--
MySQL General Mailing List
For list archives:
Problems compiling MySQL 3.23.52 on Slackware 9.1
Hi there,
I'm trying to compile the MySQL 3.23.52 source distribution on
Slackware 9.1. I'm using g++ (GCC) 3.2.3 and, I think, I've got all
the required shared libraries installed. `./configure' exits OK, but
`make' exits with an error while building the mysql client:
|mysql.o(.text+0x1b29): In function `com_source(String*, char*)':
|: undefined reference to `errno'
|collect2: ld returned 1 exit status
Inspired by Paul DuBois
(http://www.jsw4.net/info/list-archives/mysql/03-wk23/msg00660.html),
I've added the line '#include errno.h' to client/mysql.cc.
Afterwards, `make' spit out an error ('undefined reference to
`errno'') concerning libmysqlclient.so (the shared library built just
before the mysql client). I've tried a lot of crazy actions, like
using gcc instead of g++ (CXX=gcc etc.), copying /usr/include/errno.h
to the mysql source tree and many, many other, but these experiments
were in vain. I've also googled the whole world and found nothing.
Does anybody has a clue what should I do? Perhaps some package is
missing on my system? Oh, by the way, the same source tarball compiles
fine on RedHat 7.2 (no errors are shown while compiling the mysql
client).
Thanks in advance for your help,
Michal Kepien
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Type=InnoDB
the following raises error:150. Does this mean we cannot do this in INNODB format? create table t1 (a1 integer not null, b1 varchar(36), primary key (a1), foreign key (b1) references t2(b1) on delete cascade) TYPE=INNODB regards Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type=InnoDB
A Z [EMAIL PROTECTED] wrote: the following raises error:150. Does this mean we cannot do this in INNODB format? create table t1 (a1 integer not null, b1 varchar(36), primary key (a1), foreign key (b1) references t2(b1) on delete cascade) TYPE=INNODB Column b1 must be indexed. -- 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]
SQL SELECT HELP
Hi, I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( Thank you in advanced, Zoli
Failed reports for installing MySQL on Linux(AMD64)
Hi, Deeply appreciated your helps. (See attached file: failed_mysql_report) S.C. Lin Taiwan Semiconductor Manufacturing Company, Ltd. No. 6, Li-Hsin Rd.6, Science-Based Industrial Park Hsin-Chu, Taiwan 300-77, R.O.C. Tel: 03-666 Ext: 4770 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
[EMAIL PROTECTED] wrote: I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.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]
Repairing InnoDB table
Hello, I have a database using a few InnoDB tables. The database has not been in use for a while and now I noticed that all InnoDB tables in the database cannot be opened and used at all. Whatever I try to do, I got Can't open asiakkaat.InnoDB (errno: 1) error. I'd like to know, how could I repair the tables and get even the structure copied. I took a look to the data files and there are .frm files for the tables available. Thank you for help, Ville Mattila -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] 2004-04-28 12:10 PM To: [EMAIL PROTECTED] cc: (bcc: Zoltan Gyurasits/GYO/COMP/PHILIPS) Subject:Re: SQL SELECT HELP Classification: [EMAIL PROTECTED] wrote: I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.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]
The MySQL Replication...
Hello Guys !!! I'm want to solve this problem with the replication : MASTER - SLAVE OF MASTERSLAVE1 - SLAVE OF SLAVE 1SLAVE2 The replication on MySQL supports this schema ? Exists a possibility to use Slave of Slave on Replication ? The Insert is STORED on MASTER and REPLICATED to SLAVE1 and REPLICATED to SLAVE 2 AFTER stored on SLAVE1... This is possible ? THANKS My System is Debian Linux Box , Kernel 2.6 with MySQL 4.0.18-PRO with 512MB of ram, using InnoDB and Multi-MySQL-Servers... InnoDB,SQL,MySQL,Help - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 296 -6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load strips first character
The backslash, `\` is an escape character. Four backslashes, path will be recognized as \\path in MySQL. -Original Message- From: Nik Belajcic To: [EMAIL PROTECTED] Sent: 4/28/04 1:38 AM Subject: load strips first character Hello, I am loading data from a text file where filed3 is a path to a document in a format \\path\to\some\directory but after loading into MySQL it appears as \path\to\some\directory with the first backslash stripped away. Why is this happening and how can I avoid it? LOAD DATA INFILE '/local/datadocument.txt' INTO TABLE tdocument FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3); Thanks, -- Nik Belajcic [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: Repairing InnoDB table
Have the ib* files been removed or damaged? Depending on your configuration, these coulde exists in your mysql data directory. -Original Message- From: Ville Mattila To: [EMAIL PROTECTED] Sent: 4/28/04 5:28 AM Subject: Repairing InnoDB table Hello, I have a database using a few InnoDB tables. The database has not been in use for a while and now I noticed that all InnoDB tables in the database cannot be opened and used at all. Whatever I try to do, I got Can't open asiakkaat.InnoDB (errno: 1) error. I'd like to know, how could I repair the tables and get even the structure copied. I took a look to the data files and there are .frm files for the tables available. Thank you for help, Ville Mattila -- 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: The MySQL Replication...
This daisy-chained master scenario is possible. -Original Message- From: Dyego Souza Dantas Leal To: [EMAIL PROTECTED] Sent: 4/28/04 6:47 AM Subject: The MySQL Replication... Importance: High Hello Guys !!! I'm want to solve this problem with the replication : MASTER - SLAVE OF MASTERSLAVE1 - SLAVE OF SLAVE 1SLAVE2 The replication on MySQL supports this schema ? Exists a possibility to use Slave of Slave on Replication ? The Insert is STORED on MASTER and REPLICATED to SLAVE1 and REPLICATED to SLAVE 2 AFTER stored on SLAVE1... This is possible ? THANKS My System is Debian Linux Box , Kernel 2.6 with MySQL 4.0.18-PRO with 512MB of ram, using InnoDB and Multi-MySQL-Servers... InnoDB,SQL,MySQL,Help - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 296 -6640 - Reply: [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: mysqlimport and \ as data
Can you run a find and replace to double up the backslashes? \ -- \\ -Original Message- From: Hans van Dalen To: [EMAIL PROTECTED] Sent: 4/28/04 3:46 AM Subject: mysqlimport and \ as data Hi Group, Does anybody have any expierence with mysqlimport and a comma separated file with data wich contains field data with a: \. For example I have the data in de column path: C:\temp. When I import this I got something like : c:||emp ... Does anybody know how to solve this problem? This \ occurs to much to do the job manually ;-) Thanks a lot. Hans -- 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: The MySQL Replication...
Dyego Souza Dantas Leal [EMAIL PROTECTED] wrote: Hello Guys !!! I'm want to solve this problem with the replication : MASTER - SLAVE OF MASTERSLAVE1 - SLAVE OF SLAVE 1SLAVE2 The replication on MySQL supports this schema ? Exists a possibility to use Slave of Slave on Replication ? The Insert is STORED on MASTER and REPLICATED to SLAVE1 and REPLICATED to SLAVE 2 AFTER stored on SLAVE1... This is possible ? Yes. In this case you should start SLAVE1 with --log-slave-updates replication option. -- 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]
table locking using perl DBI
I have a perl cgi script that needs to lock the tables. Unfortunately,
as HTTP is a one shot protocol, once the cgi script completes execution,
the tables are unlocked.
I need it to stay locked until another cgi script unlocks them!
Is there any other way of achieving this!?
Thanks
Andy
--
perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^'
in:control developer, Telindus, RG27 9HY
DDI: +44 1256 709211, GSM: +44 7810 636652
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table locking using perl DBI
Andy Ford wrote: I have a perl cgi script that needs to lock the tables. Unfortunately, as HTTP is a one shot protocol, once the cgi script completes execution, the tables are unlocked. I need it to stay locked until another cgi script unlocks them! Is there any other way of achieving this!? You cannot unlock tables that have been locked by another cgi script. If you could, you could easily steal the locks that were set by someone else (not a good idea). If your scripts access the tables exclusively, that is, no scripts from someone else or other clients have access to the tables you want to lock, you could use the GET_LOCK(str,timeout) and RELEASE_LOCK(str) functions to perform cooperative advisory locking on a global/server (not table) level. This will protect you from yourself, but requires consistent use by all your scripts and it doesn't protect you from other clients/scripts accessing the tables, something which you can easily forget sooner or later. Hans-Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table locking using perl DBI
Hi Andy,
The best way to accomplish this is at an application level, setup a
lock table and put your locks in there. There are many methods for
handling application locks. Choose one that work for you. With a
database level lock, when the connection terminates the lock is
released.
Persistant connections might be an option, but you can't guarantee
your CGI will get the same connection and be able to release the lock!
Hope this helps,
Ken
- Original Message -
From: Andy Ford [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, April 26, 2004 10:01 PM
Subject: table locking using perl DBI
I have a perl cgi script that needs to lock the tables.
Unfortunately,
as HTTP is a one shot protocol, once the cgi script completes
execution,
the tables are unlocked.
I need it to stay locked until another cgi script unlocks them!
Is there any other way of achieving this!?
Thanks
Andy
--
perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^'
in:control developer, Telindus, RG27 9HY
DDI: +44 1256 709211, GSM: +44 7810 636652
--
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]
SELECT DISTINCT returns an incorrect result with special characters
I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | fá | | Fá | | fa | | Fa | | fâ | | Fâ | | fã | | Fã | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | fá | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. Thanks. __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary -Original Message- From: Ricardo To: [EMAIL PROTECTED] Sent: 4/28/04 9:02 AM Subject: SELECT DISTINCT returns an incorrect result with special characters I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | fá | | Fá | | fa | | Fa | | fâ | | Fâ | | fã | | Fã | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | fá | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. Thanks. __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- 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: SELECT DISTINCT returns an incorrect result with special char acters
Thanks Mr. Pendleton. But I cannot change the behaviour of the LIKE operator and the ORDER BY clause, which are going to be affected by both of your suggestions. --- Victor Pendleton [EMAIL PROTECTED] escreveu: Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special characters
Ricardo [EMAIL PROTECTED] wrote: I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | f? | | F? | | fa | | Fa | | f? | | F? | | f? | | F? | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | f? | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. What is the character set of the data? What is the character set of MySQL server? -- 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: SELECT DISTINCT returns an incorrect result with special char acters
Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? -Original Message- From: Ricardo To: Victor Pendleton; [EMAIL PROTECTED] Sent: 4/28/04 9:33 AM Subject: RE: SELECT DISTINCT returns an incorrect result with special char acters Thanks Mr. Pendleton. But I cannot change the behaviour of the LIKE operator and the ORDER BY clause, which are going to be affected by both of your suggestions. --- Victor Pendleton [EMAIL PROTECTED] escreveu: Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2-alpha not creating .so -files?
I'm trying to build RPMs from Mysql Cluster, yes the alpha version, but I run into this problem... no .so -files. What I'm missing here? I'm using redhat 9 source rpm as my starting point. Harri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
- The sort order gets incorrect: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE MyField LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fa | | Fâ | | fá | | Fa | | fã | | Fá | | fâ | | Fã | +-+ - The like operator gets case-sensitive: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE BINARY(MyField) LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fâ | | fá | | fã | | fa | +-+ --- Victor Pendleton [EMAIL PROTECTED] escreveu: Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special characters
latin1 --- Egor Egorov [EMAIL PROTECTED] escreveu: What is the character set of the data? What is the character set of MySQL server? -- 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 __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Win2k Server open to connections from all IPs
Hi, I have installed mysql 3.23 on our Windows 2000 Server machine. How can I configure the database such that anyone on the internet can connect to this database? I know this sounds dangerous but once this has been configured we will install VPNs on this machine and any machine that we permit to connect to it. I did consider adding individual IP addresses but some people are allocated new IP addresses everytime they connect to the internet - i.e. dial up accounts... Thanks for your help _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
It does not appear to respect the ascii values. Should the order be? fa fá fâ fã fa fá fâ fã -Original Message- From: Ricardo To: [EMAIL PROTECTED] Sent: 4/28/04 10:05 AM Subject: RE: SELECT DISTINCT returns an incorrect result with special char acters - The sort order gets incorrect: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE MyField LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fa | | Fâ | | fá | | Fa | | fã | | Fá | | fâ | | Fã | +-+ - The like operator gets case-sensitive: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE BINARY(MyField) LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fâ | | fá | | fã | | fa | +-+ --- Victor Pendleton [EMAIL PROTECTED] escreveu: Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- 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: SELECT DISTINCT returns an incorrect result with special char acters
The order should be: fa Fa fá Fá fâ Fâ fã Fã As I get in MS-SQL Server. --- Victor Pendleton [EMAIL PROTECTED] escreveu: It does not appear to respect the ascii values. Should the order be? fa fá fâ fã fa fá fâ fã __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM same field twice in one SELECT?
Thanks! Now I can get it to work just the way I want it. ... and learned how to use IF and CASE statements with MySQL at the same time! :) Oh, and it's actually precisely because FIM doesn't exist anymore that I want to convert it to euro. ;) (And SEK just comes along for the ride.) Harald Fuchs wrote: You've got it almost right: SELECT SUM(CASE cur WHEN 'EUR' THEN amount WHEN 'FIM' THEN amount / 5.94573 WHEN 'SEK' THEN amount / 9.294 END) AS Amount, total FROM table1; By the way: 'FIM' doesn't exist anymore ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create table results in (errno: 121)
Tom, More specifically, find out what the error messages means by using the `SHOW INNODB STATUS` command. This will give a description of the last error message generated by InnoDb. In your case, its definitely the foreign key that is causing the problem. As far as what it is specifically, I'd reference the message, and if that doesn't help bring the message to the group. Regards, Adam On Apr 22, 2004, at 9:29 AM, Tom Brown wrote: Hi, I'm trying to create a table with the following syntax CREATE TABLE TNSession ( SessionID int(11) NOT NULL auto_increment, IPAddress varchar(50) default NULL, Created datetime default NULL, Expired tinyint(4) default '0', AccountID int(11) default '0', PRIMARY KEY (SessionID), KEY FK_AccountID (AccountID), CONSTRAINT `0_20` FOREIGN KEY (`AccountID`) REFERENCES `TNAccount` (`AccountID`) ) TYPE=InnoDB ROW_FORMAT=DYNAMIC; it results in a ERROR 1005: Can't create table './TNMailServer/TNSession.frm' (errno: 121) This is on version mysql-standard-4.0.18-pc-linux-i686 Can anyone shed any light on this as it has me stumped - There is a TNAccount table with a AccountID column? thanks for any assistance Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This electronic mail transmission contains confidential information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
Ricardo wrote: The order should be: fa Fa fá Fá fâ Fâ fã Fã As I get in MS-SQL Server. Which collation are you using in MS SQL Server? And in MySQL? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MySQL] Using the MAX() value in WHERE clause
Hi, The task is to find users whose accounts have not been validated for the last 10 days. This SELECT accomplishes that: SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(CURDATE(), INTERVAL 10 DAY) But, using curdate() is dangerous. What if the system time is messed up? Instead, would be better to use MAX(dateCreated), but that does not work. This does not work: SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(MAX(dateCreated), INTERVAL 10 DAY) Error is 'Invalid use of group function even if a GROUP BY is added. Is there a way to do this, so that the SELECT can get only values for users who have validated LIKE 'N' and their dateCreated is 10 days before the latest dateCreated? Thanks for your help. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Table Locking Issue
On Tue, 27 Apr 2004, Scott Switzer wrote: Hi, I am having a difficult time with a query. My environment is MySQL v4.0.16 (InnoDB tables) running on Linux (latest 2.4 kernel). Basically, I am running a query of the form: INSERT INTO temp_tbl SELECT c1,c2... FROM t1,t2,t3,t4 WHERE ... It is a very complex query, which looks at millions of rows of data to produce results. The issue is this: When running from the MySQL command line: Normally, when the query is run, it takes about 5 minutes to complete. When I LOCK t1,t2,t3,t4 before running, it takes 8 seconds to run. Are you sure it is actually _working_ when it takes 8 seconds to run? You say it is a very complex query that looks at millions of rows ... unless those are all cached (and they could be, depending on your setup), 8 seconds would probably be too fast for it to run no matter how mysql optimized it. Triple check that if you start with an empty temp_tbl it actually inserts everything it should into temp_tbl. If you aren't locking temp_tbl, I wouldn't expect the query to actually work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I backup mysql db file directly ?
Hi, I would like to backup a mysql database called foodb Supposed that I have backuped just the file /var/mysql/foodb and that I have completly lost my hard drive. Can I recover data from the foodb file ? If yes, how ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Table Locking Issue
Thanks for the message. I should rephrase - the data set is millions of rows, but the tables are indexed, and an EXPLAIN looks like it is using indexes effectively. The query produces the exact same results both times (with and without LOCKing). Is there a reason that by calling the query via PHP (v4.3) it would either not LOCK the tables effectively, or optimise the query differently than when running in the MySQL client? If you are duly motivated, I could send you the exact query and a test DB dump... Thanks, Scott Marc Slemko wrote: On Tue, 27 Apr 2004, Scott Switzer wrote: Hi, I am having a difficult time with a query. My environment is MySQL v4.0.16 (InnoDB tables) running on Linux (latest 2.4 kernel). Basically, I am running a query of the form: INSERT INTO temp_tbl SELECT c1,c2... FROM t1,t2,t3,t4 WHERE ... It is a very complex query, which looks at millions of rows of data to produce results. The issue is this: When running from the MySQL command line: Normally, when the query is run, it takes about 5 minutes to complete. When I LOCK t1,t2,t3,t4 before running, it takes 8 seconds to run. Are you sure it is actually _working_ when it takes 8 seconds to run? You say it is a very complex query that looks at millions of rows ... unless those are all cached (and they could be, depending on your setup), 8 seconds would probably be too fast for it to run no matter how mysql optimized it. Triple check that if you start with an empty temp_tbl it actually inserts everything it should into temp_tbl. If you aren't locking temp_tbl, I wouldn't expect the query to actually work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on Win2k Server open to connections from all IPs
Open the port in the firewall ... GRANT SELECT ON database_name.table_name TO user_name@'%' IDENTIFIED BY 'some_password'; ... If you are going to do this, may I suggest starting MySQL with a non standard port number? -Original Message- From: shaun thornburgh To: [EMAIL PROTECTED] Sent: 4/28/04 10:12 AM Subject: MySQL on Win2k Server open to connections from all IPs Hi, I have installed mysql 3.23 on our Windows 2000 Server machine. How can I configure the database such that anyone on the internet can connect to this database? I know this sounds dangerous but once this has been configured we will install VPNs on this machine and any machine that we permit to connect to it. I did consider adding individual IP addresses but some people are allocated new IP addresses everytime they connect to the internet - i.e. dial up accounts... Thanks for your help _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- 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: Can I backup mysql db file directly ?
I would suggest using mysqldump to ensure that you get all the data. Install a new version of MySQL then run mysql dumpFile.sql -Original Message- From: Thomas Carrié To: [EMAIL PROTECTED] Sent: 4/28/04 11:09 AM Subject: Can I backup mysql db file directly ? Hi, I would like to backup a mysql database called foodb Supposed that I have backuped just the file /var/mysql/foodb and that I have completly lost my hard drive. Can I recover data from the foodb file ? If yes, how ? -- 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: [MySQL] Using the MAX() value in WHERE clause
jim wrote: Hi, The task is to find users whose accounts have not been validated for the last 10 days. This SELECT accomplishes that: SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(CURDATE(), INTERVAL 10 DAY) But, using curdate() is dangerous. What if the system time is messed up? Instead, would be better to use MAX(dateCreated), but that does not work. This does not work: SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(MAX(dateCreated), INTERVAL 10 DAY) Error is 'Invalid use of group function even if a GROUP BY is added. Is there a way to do this, so that the SELECT can get only values for users who have validated LIKE 'N' and their dateCreated is 10 days before the latest dateCreated? Thanks for your help. Jim Right. MAX() returns the largest value *from the selected rows*. The WHERE clause determines which rows to look at. You can't calculate the MAX based on those rows till after you've chosen them, so you can't choose them based on the MAX. One solution would be to use a variable: SELECT @latest:= MAX(dateCreated); SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(@latest, INTERVAL 10 DAY); I wonder if this is really the best way, though. What happens if nobody validated today (it's a holiday, perhaps)? More to the point, is this query immune to system time errors? I think not. Isn't dateCreated based on system time? If your system time is unreliable, your dateCreated is also unreliable. This is a big problem. Suppose system time is accurate now, as we run the query, but was off some time yesterday. If even one row has a dateCreated more than 10 days from now, you'll get almost every row with the MAX(dateCreated) version. And the incorrect row(s) will continue to appear validated long after it(they) should. On the other hand, if system time was wrong by 10 days or more in the other direction yesterday, accounts validated during that period will show up as unvalidated in either query. I think you are better off fixing your system time than trying to work around it. Couldn't you use a timeserver to keep in sync? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Using the MAX() value in WHERE clause
jim wrote: Hi, The task is to find users whose accounts have not been validated for the last 10 days. This SELECT accomplishes that: SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(CURDATE(), INTERVAL 10 DAY) But, using curdate() is dangerous. What if the system time is messed up? Instead, would be better to use MAX(dateCreated), but that does not work. This does not work: SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(MAX(dateCreated), INTERVAL 10 DAY) Error is 'Invalid use of group function even if a GROUP BY is added. Is there a way to do this, so that the SELECT can get only values for users who have validated LIKE 'N' and their dateCreated is 10 days before the latest dateCreated? Thanks for your help. Jim Right. MAX() returns the largest value *from the selected rows*. The WHERE clause determines which rows to look at. You can't calculate the MAX based on those rows till after you've chosen them, so you can't choose them based on the MAX. One solution would be to use a variable: SELECT @latest:= MAX(dateCreated); Here I am getting the errors: mysql SELECT @latest:= MAX(dateCreated); ERROR 1054: Unknown column 'dateCreated' in 'field list' mysql SELECT @latest:= MAX(user.dateCreated); ERROR 1109: Unknown table 'user' in field list This works: SELECT @latest:= MAX(dateCreated) FROM user; SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(@latest, INTERVAL 10 DAY); Sorry for my newness here, but what is this technique called? You are creating a variable called @latest. I'd like to look this up in the doc. I wonder if this is really the best way, though. What happens if nobody validated today (it's a holiday, perhaps)? More to the point, is this query immune to system time errors? I think not. Isn't dateCreated based on system time? If your system time is unreliable, your dateCreated is also unreliable. This is a big problem. Suppose system time is accurate now, as we run the query, but was off some time yesterday. If even one row has a dateCreated more than 10 days from now, you'll get almost every row with the MAX(dateCreated) version. And the incorrect row(s) will continue to appear validated long after it(they) should. On the other hand, if system time was wrong by 10 days or more in the other direction yesterday, accounts validated during that period will show up as unvalidated in either query. I think you are better off fixing your system time than trying to work around it. Couldn't you use a timeserver to keep in sync? That's absolutely right. I'm already using nntp to keep the clock in synch, but as that relies on several outside factors (nntp server, 'net connection) I was trying to cleverly come up with something more reliable. We get about 150 validated users / day, so I figured that latest registered user would be a reliable place to count backwards from, until signups drop off, and even then this algorithm would err on the side of NOT selecting. You saw the flaw in my logic, though. I'll have to come up with something better. Thanks again for the help and I welcome any other suggestions. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
urgent: how to increase the database size
Hi, I am trying to insert the records in the database. After the insertion of 3millions records, it fails to insert the records further. Can someone help me in this problem. How do I go and increase the capacity of the database. Moreover if someone can provide me the maximum size of the database which is possible. Regards, Anup Mahansaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
Which collation are you using in MS SQL Server? And in MySQL? Jochem MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Using the MAX() value in WHERE clause
jim wrote: snip One solution would be to use a variable: SELECT @latest:= MAX(dateCreated); Here I am getting the errors: mysql SELECT @latest:= MAX(dateCreated); ERROR 1054: Unknown column 'dateCreated' in 'field list' mysql SELECT @latest:= MAX(user.dateCreated); ERROR 1109: Unknown table 'user' in field list This works: SELECT @latest:= MAX(dateCreated) FROM user; Sorry, my fault. You have to tell mysql which table to look in, just like any other query. I thought FROM user, but apparently didn't type it. Not very helpful. I'm glad you figured it out. SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated = DATE_SUB(@latest, INTERVAL 10 DAY); Sorry for my newness here, but what is this technique called? You are creating a variable called @latest. I'd like to look this up in the doc. Sure. No need to apologize. They're called user variables http://dev.mysql.com/doc/mysql/en/Variables.html . snip it. Couldn't you use a timeserver to keep in sync? That's absolutely right. I'm already using nntp to keep the clock in synch, but as that relies on several outside factors (nntp server, 'net connection) I was trying to cleverly come up with something more reliable. We get about 150 validated users / day, so I figured that latest registered user would be a reliable place to count backwards from, until signups drop off, and even then this algorithm would err on the side of NOT selecting. You saw the flaw in my logic, though. I'll have to come up with something better. In my experience, computers do a decent job of keeping time, but tend to drift *slowly* away from correct time. Periodic checking with a timeserver allows correction of the drift. If you can't connect to the timeserver for a while, your system time should still be kept, but should be expected to drift a little. If you've chosen a reliable timeserver, I'd be surprised if your system time were ever wrong by more than a few seconds, even if you only get to talk to the timeserver once every other day. I'd be astounded if system time were off by a day or more. That would indicate a serious problem with your internal clock. Ultimately, no matter what you choose for a query, it will only be as reliable as the data. If your system time is at least accurate to the day, both the CURDATE and MAX user variable queries should return the same thing (so long as someone validated today). If your system time can ever have the wrong date, I don't think any query will save you. Thanks again for the help and I welcome any other suggestions. Jim Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL User Conference - Session Presentations?
Hello, Does anybody knows if User Conference' presentations were published somewhere on the web? I was told that it will be done shortly after the conference, and it is more than a week after the conference end... Thank you, Mihail Manolov ___ Solutions Architect Government Liquidation, LLC - Your Direct Source for Government Surplus 2131 K Street NW, 4th Floor Washington, DC 20037 Telephone: 1 (202) 467-6868 x 227 Fax Number: 1 (202) 467-5475 Cell Phone: 1 (202) 256-9044 [EMAIL PROTECTED] www.govliquidation.com www.liquidityservicesinc.com sql query server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL User Conference - Session Presentations?
On Wed, Apr 28, 2004 at 02:51:50PM -0400, Mihail Manolov wrote: Does anybody knows if User Conference' presentations were published somewhere on the web? I was told that it will be done shortly after the conference, and it is more than a week after the conference end... They will be published at http://www.mysql.com/uc2004 by the end of the week. (Those that we've collected from the speakers, that is. We'll continue to publish additional ones as we receive them.) Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a basic question I can't seem to find an answer.
Here is a basic question I am struggling to find an answer. I want to run mysql under solaris. Security is not a great concern to me and only one application accesses the database locally. I don't have and can't get the root access to the machine. Also, I can't create mysql user and groups on this machine. Can someone tell me if it is possible for me to install and run mysql completely as a normal user on the system? If it is, can you please give me a rough idea on how to proceed with the installation? I searched the mysql mailing lists and rest of the internest pretty extensively ( I think!!) and stil couldn't find a straight answer. Thank you very much for your time and help. _ Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! http://join.msn.com/?pgmarket=en-uspage=hotmail/es2ST=1/go/onm00200362ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent: how to increase the database size
- Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 2:02 PM Subject: urgent: how to increase the database size [snip] Moreover if someone can provide me the maximum size of the database which is possible. Have you tried looking in the MySQL manual? The maximum size is right there in section 1.2.4. http://dev.mysql.com/doc/mysql/en/Table_size.html That should be the FIRST place you look if your need is urgent. Or does urgent just mean I can't be bothered to look in the manual? You would probably also find information on increasing the size of your table in the manual, assuming you haven't exceeded the maximum size. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev.mysql.com/doc/mysql/en/Charset-server.html Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special characters
But I think the collation set only applies to MySQL 4.1, doesn't it? I'm using MySQL 4.0.16. I found no character_set_server system variable. Only character_set. http://dev.mysql.com/doc/mysql/en/Charset-map.html Thanks. --- Jochem van Dieten [EMAIL PROTECTED] escreveu: Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev.mysql.com/doc/mysql/en/Charset-server.html Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring a db with RI enforced
If I'm using mysqldump to dump a database with referential integrity in place, does MySQL build the information in such a way that the referenced tables are loaded first to avoid invalid inserts into a table with a foreign key in place? I'm trying to find an option for mysqldump, and the closest one I see is --disable-keys, but I'm not sure if that's what I need or not. Thanks, Lou
Build mysql 4.0.18 on Aix 5.2
I'm trying to compile 4.0.18 version on Aix 5.2 ML2 (IBM 7044-270 with Two cpu Power3-II), with gcc-2.95 with this parameters(note in mysql.com): CC=gcc -pipe -mcpu=powerpc -Wa,-many \ CXX=gcc -pipe -mcpu=powerpc -Wa,-many \ CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti \ /configure --prefix=/usr/local/mysql --with-low-memory But i get: gcc -pipe -mcpu=powerpc -Wa,-many -DHAVE_CONFIG_H -I. -I. -I.. -I./../includ e -I../include -I..-O3 -DDBUG_OFF -c `test -f 'my_print_defaults.c' || echo './'`my_print_defaults.c my_print_defaults.c:52: incompatible types in initialization my_print_defaults.c:52: initializer element is not constant my_print_defaults.c:52: (near initialization for `my_long_options[6].str_values') gmake[1]: *** [my_print_defaults.o] Error 1 gmake[1]: Leaving directory `/software/mysql-4.0.18/extra' gmake: *** [all-recursive] Error 1 make: 1254-004 El código de error del último mandato es 2. Any idea ? --- Tu portal de Aix en Español http://aixpanish.com --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql error
Hi: I am building perl scripts which can insert data into a database independent of the type of database. The following query works in postgresql, but I am getting the following error when I run sql query in mysql mysql SELECT ipaddress FROM ip_via_pingsweep WHERE ipaddress NOT IN (SELECT ipaddress FROM object_subips); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ipaddress FROM object_subips)' at line 1 The following 2 queries are fine: mysql select ipaddress FROM ip_via_pingsweep; Empty set (0.00 sec) mysql SELECT ipaddress FROM object_subips; Empty set (0.00 sec) I am using 4.0.18-standard binary. Any ideas? Does this version support subselects. TIA Ravi
Re: sql error
- Original Message - From: Ravi Malghan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 6:34 PM Subject: sql error Hi: I am building perl scripts which can insert data into a database independent of the type of database. The following query works in postgresql, but I am getting the following error when I run sql query in mysql mysql SELECT ipaddress FROM ip_via_pingsweep WHERE ipaddress NOT IN (SELECT ipaddress FROM object_subips); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ipaddress FROM object_subips)' at line 1 The following 2 queries are fine: mysql select ipaddress FROM ip_via_pingsweep; Empty set (0.00 sec) mysql SELECT ipaddress FROM object_subips; Empty set (0.00 sec) I am using 4.0.18-standard binary. Any ideas? Does this version support subselects. In a word, no. Subselects are not supported in MySQL until version 4.1.x. This is currently available in alpha mode and I hear it is working pretty well for most people but don't know if you want to take the plunge and try it. Otherwise, you'll need to rewrite your query for MySQL. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql for Family History (genealogy)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 27 April 2004 01:40 pm, zzapper wrote: Hi, Anyone designed a MySql database for family history? Any ideas,recommendations, problems ? I know I've seen a few people with Gedcom to mysql stuff. So I'd use that as a basis for searching. - -- - -- Jayce^ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAkEROA10/0O8cAHgRAgB1AJ4wvDpKg3OkuA9THKtjLxKmLOmPqwCeMkhJ S3asqfZ24J3CjSKn8xlMDGg= =R67R -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
hi, i have a small table with one field that i created to test.i took a backup of database using this stmt: mysqldump --user= --password= db db.sql now i am trying to run this sql file in oracle sqlplus and i am not able to . it doesnt create the table again and i get syntax errors. How can i run this in Oracle??? liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is your mysql debugging strategy?
On 27-Apr-2004 zzapper wrote:
On Tue, 27 Apr 2004 22:26:16 +0100, wrote:
I only use mysql with php so all I need is
php code:
$result = mysql_query($sql) or die(mysql_error());
This always tells me what I did wrong in the query. You could easily
put
together a very short script into which you just drop you query. THis
would output the problem to the page. Dead simple and quick.
Rich
Joshua J. Kugler wrote:
On Tuesday 27 April 2004 04:26 am, zzapper said something like:
Even though I solved the following problem myself, I'd like to know
what debugging strategy people use to solve problems when they get
the
dreaded Error in Mysql look in the manual
Fire up MySQL CC and paste the SQL in there, and see what error it
gives me.
As in 'You have an error near' type messages.
j- k-
When I've got a horrible query with joins etc, I don't find the your
error near .. very useful or am I giving up to easy?
Howzabout :
mysql_query($qry) or
die(sprintf('pBorked query at file %s line %d :br /%s',
__FILE__, __LINE__, nl2br($qry)) .'br /'
.mysql_errno() .'br /' .mysql_error());
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about TINYINT type
On 27-Apr-2004 Paul DuBois wrote: At 13:29 -0400 4/27/04, Keith C. Ivey wrote: On 27 Apr 2004 at 13:59, Ronan Lucio wrote: OK, I understood it, but I didn´t understand why is there an option TINYINT(n) See http://dev.mysql.com/doc/mysql/en/Numeric_types.html | Another extension is supported by MySQL for optionally | specifying the display width of an integer value in | parentheses following the base keyword for the type (for | example, INT(4)). This optional display width specification | is used to left-pad the display of values having a width | less than the width specified for the column. However, the | display width does not constrain the range of values that | can be stored in the column, or the number of digits that | will be displayed for values having a width exceeding that | specified for the column. I haven't found much use for display widths myself, but then different people use MySQL differently. I agree. I cannot think of a time when I've actually specified a display width, except just to see what effect it has on result display. :-) The only time I've used it is in a billing app (w/ zerofill): CREATE TABLE invoice ( id mediumint(6) unsigned zerofill NOT NULL auto_increment, idcust mediumint(5) unsigned zerofill NOT NULL, ... ); You can make some pretty decent reports with a shell script if the DB lends a hand with formatting. -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql error
Rhino wrote: From: Ravi Malghan [EMAIL PROTECTED] Hi: I am building perl scripts which can insert data into a database independent of the type of database. The following query works in postgresql, but I am getting the following error when I run sql query in mysql mysql SELECT ipaddress FROM ip_via_pingsweep WHERE ipaddress NOT IN (SELECT ipaddress FROM object_subips); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ipaddress FROM object_subips)' at line 1 The following 2 queries are fine: mysql select ipaddress FROM ip_via_pingsweep; Empty set (0.00 sec) mysql SELECT ipaddress FROM object_subips; Empty set (0.00 sec) I am using 4.0.18-standard binary. Any ideas? Does this version support subselects. In a word, no. Subselects are not supported in MySQL until version 4.1.x. This is currently available in alpha mode and I hear it is working pretty well for most people but don't know if you want to take the plunge and try it. Otherwise, you'll need to rewrite your query for MySQL. Rhino SELECT i.ipaddress FROM ip_via_pingsweep i LEFT JOIN object_subips o USING (ipaddress) WHERE o.ipaddress IS NULL; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
