replication problem, empty Binlog_do_db ?
Running 4.0.20-standard-log on Fedora Core 1 on two boxes. Running with a largely untouched my-huge.cnf - innodb configs uncommented. Followed http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html to setup replication. On the master I see: mysql> show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | db2-bin.005 | 185 | | | +-+--+--+--+ The HOWTO seems to suggest that Binlog_do_db should have dbs I want to replicate. The slave seems forever stuck in: *** 2. row *** Id: 2 User: system user Host: db: NULL Command: Connect Time: 797 State: Waiting for master to send event Info: NULL Since I have InnoDB tables and don't have the money for InnoDB Hot Backup, I did a 'flush tables with read lock; show master status' on the master, shutdown the master and rsync'd /var/lib/mysql to the slave. I started the master back up, started the slave, ran the 'change master to' query and started the slave. Log shows: 040527 23:03:49 Slave SQL thread initialized, starting replication in log 'db2-bin.004' at position 211, relay log './db3-relay-bin.001' position: 4 040527 23:03:49 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db2-bin.004' at position 211 and no other errors. I have test.mrztest and can insert rows on the master but they never appear on the slave. Did I miss a step? Is Binlog_do_db supposed to be empty? -- matthew zeier - "Nothing in life is to be feared. It is only to be understood." - Marie Curie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are Views Supported?
On May 27, 2004, at 11:16 PM, Jake Johnson wrote: I was wondering when or will views be supported? http://dev.mysql.com/doc/mysql/en/ANSI_diff_Views.html -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Are Views Supported?
Hello, I was wondering when or will views be supported? -- Jake Johnson http://www.plutoid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is MySQL.com using MnoGoSearch for searching its site???
On May 27, 2004, at 10:17 PM, mos wrote: As most people already know, MySQL has FullText indexing built into it, so why is mysql.com using MnoGoSearch? (There is an icon "Powered by MnoGoSearch" on the search page.) 1) Is there something wrong with MySQL's FullText search for handling a lot of data? 2) Is MnoGoSearch better? 3) Why isn't MySQL using their own Full Text search engine? The two searches are unrelated. MnoGoSearch searches the entire web site like atomz or other such site indexing tools -- it searches the net result of the pages of the site which may contain many static components not contained in a MySQL database that the FullText search would never see. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is MySQL.com using MnoGoSearch for searching its site???
As most people already know, MySQL has FullText indexing built into it, so why is mysql.com using MnoGoSearch? (There is an icon "Powered by MnoGoSearch" on the search page.) 1) Is there something wrong with MySQL's FullText search for handling a lot of data? 2) Is MnoGoSearch better? 3) Why isn't MySQL using their own Full Text search engine? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to see if db exists...........mysql_query( myQuery)
On Thu, May 27, 2004 at 03:59:46PM -0400, Bono, Saroj AA R62 wrote: > I am going to use mysql_query() and want to find out if a certain > database exists. If mysql_real_connect() fails there are many errors > that could account for this. The database may exist , and I cant take > the error returned from the failed query to mean the db isnt there and > should be created. So what sort of query can I use to see whether the db > exists? I thought of "USE myDb" but once again a failed mysql_query cant > rule out other factors that can cause the failure. Any suggestions? You could use "SHOW DATABASES LIKE 'myDb'". http://dev.mysql.com/doc/mysql/en/Show_database_info.html Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT temporarily removes passwods until FLUSH
>Description: Using the GRANT command causes the password to be
deleted until 'FLUSH PRIVILEGES' is executed. This is a security
problem.
>How-To-Repeat:
New 4.1 BINARY install; installed as noted in instructions.
No users except root exist.
Execute MySQL> GRANT SELECT ON bugg.* TO 'cwolf'@'%';
SET PASSWORD FOR 'cwolf'@'%' = PASSWORD('test');
FLUSH PRIVILEGES
SHOW GRANTS FOR 'cwolf';
GRANT USAGE ON *.* TO 'cwolf'@'%' IDENTIFIED BY PASSWORD
'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
GRANT ALL PRIVILEGES ON `test`.* TO 'cwolf'@'%'
GRANT SELECT ON `bugg`.* TO 'cwolf'@'%'
Result: User cwolf can connect and must provide password 'test'.
Execute MySQL> GRANT INSERT ON bugg.* TO 'cwolf'@'%'
Result: User cwolf can now connect WITH NO PASSWORD! and
SHOW GRANTS FOR 'cwolf'@'%' does not show an 'IDENTIFIED BY' clause.
GRANT USAGE ON *.* TO 'cwolf'@'%'
GRANT ALL PRIVILEGES ON `test`.* TO 'cwolf'@'%'
GRANT SELECT, INSERT ON `bugg`.* TO 'cwolf'@'%'
Execute MySQL> FLUSH PRIVILEGES
Result: User cwolf must now use original password to connect, and
SHOW GRANTS FOR 'cwolf'@'%' shows an 'IDENTIFIED BY' clause.
GRANT USAGE ON *.* TO 'cwolf'@'%' IDENTIFIED BY PASSWORD
'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
GRANT ALL PRIVILEGES ON `test`.* TO 'cwolf'@'%'
GRANT SELECT, INSERT ON `bugg`.* TO 'cwolf'@'%'
>Fix:
Unknown.
>Submitter-Id:
>Originator:Christopher Wolf
>Organization: private
>MySQL support: none
>Synopsis: GRANT removes password until FLUSH
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.1.1-alpha-standard (Official MySQL-standard binary)
>C compiler:2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux sdsweb 2.4.18-bf2.4 #1 Son Apr 14 09:53:28 CEST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2
-mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Jul 18 2003 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x1 root root 1153784 Apr 8 2003 /lib/libc-2.2.5.so
-rw-r--r--1 root root 2391002 Apr 8 2003 /usr/lib/libc.a
-rw-r--r--1 root root 178 Apr 8 2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex'
'--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile'
'--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static'
'--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server'
'--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro
-felide-constructors' 'CXX=gcc'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Scenario
WOuld the following scenario be possible I currently have about 1.2 Tb of data that i need to transform into mysql and be able to server a very high amount of pages from a discussion board. would i be able to place the mysql/data directory in a main server with huge storage and then have several mysql server instances running on different machines accessing the same /mysql/data directory? would there be any limitations. i need to be able to server about 200/sec on each server and have about 40 servers. basically my ideas is to have several mysql servers running but one main data repository. any help is appreciated Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FW: Could not start MySQL after reinstall - Can't open privil ege tables: Table 'mysql.host' doesn't exist
Ok, I found the cause which caused the problem. I wiped out the whole installation directory but the c:\my.cnf file was modified incorrectly. Sine I would like to use innoDB, I had to created database subdirectories manually. I did that but I specified datadir by mistake: datadir = e:/mysql411/database it should be: datadir = e:/mysql411/data I misunderstood the value of datadir in the my.cnf file. Anyway, thanks for your kindly support. Michael Wu [EMAIL PROTECTED] -Original Message- From: Robert J Taylor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 5:46 PM To: MICHAEL_WU Cc: [EMAIL PROTECTED] Subject: Re: FW: Could not start MySQL after reinstall - Can't open privilege tables: Table 'mysql.host' doesn't exist Did you change the permissions for the files to include read/write/change for the user/account MySQL uses on your system? This doesn't require deleting, as far as I recall Windows permissions... Oh, can you verify that the file host under directory mysql DOES exist? If so, it's really most likely a permissions problem. MICHAEL_WU wrote: >Since the database I created was not critical, I could simply throw it away. >Therefore, >delete the whole MySql installation directory and unzip the alpha release >zip file again >to create the installation directory tree. However, the following error >persists: > > Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't >exist > >Everything should be wiped out I thought. Does MySQL saves some data >somewhere other than >the installation directory? More help, please? >Michael Wu > >-Original Message- >From: Robert J Taylor [mailto:[EMAIL PROTECTED] >Sent: Thursday, May 27, 2004 4:52 PM >To: "michael_wu[¡±d¡±?1F]" >Cc: [EMAIL PROTECTED] >Subject: Re: Could not start MySQL after reinstall - Can't open privilege >tabl es: Table 'mysql.host' doesn't exist > > >Check permissions on the mysql\data directory and files/folders below >for the MySQL process/user (sorry for not knowing the right Windows >terminology, I'm not a Windows user). > >HTH, > >Robert J Taylor >[EMAIL PROTECTED] > >michael_wu[§d§»¹F] wrote: > > > >>Hello, >>I run into a problem after re-installing mySQL 4.1.1 today. When I >>tried to start the service on my Windows2K, I got the following error: >> >> 040527 15:37:49 Fatal error: Can't open privilege tables: Table >>'mysql.host' doesn't exist >> >>Can some one tell me how to solve the problem? >> >>Thanks in advance! >>Michael Wu >> >> >> >> >> > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client Apps Using a Config File
If I understand the docs correctly, I can use a config file on my client machine for the given mysql client apps such as the mysql command line tool. What if I'm using a 3rd-party app? Will the libraries somehow know to read the config file or would the app have to take manual steps to do it? Thanks, Lou
Re: Duplicate does not exist
fr0g wrote: Good evening all. I'm facing a problem with a specific table and the uniques that it has. I have a table, hosting data of peoples names. Some of it's columns are, name_english, surname_english, name_original, surname_original, name_greek, surname_greek. I have as unique each combination of columns of the same language (i.e. name_english, surname_english, unique, etc). As I am "Robert James Taylor" I can give anecdotal evidence that full name does by no means make a good candidate key for a database of any population larger than...sayGeorge Foreman's immediate family (he named all his boys the exact same name, if you didn't catch the reference). More than once I have entered confusing periods of mis-identification due to database designers relying on name alone to match people. In fact, a short tale that is true. I moved from one West Coast state to another in 1998 and was denied a drivers license after passing the tests will flying colors because I had "a DUI conviction" in an East Coast state. The other Robert James Taylor, who was born the same year and day I was, fortunately had a different Social Security Number (which is not a panacea either...but I digress). This may sound far-fetched and unlikely to happen again but it was real, scary and made me curse database designers at the DMV. Please don't do that. Thanks. Robert James Taylor West Coast and Sober [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL not finidng/using NON-system OpenSSL
hi all, i'm building MySQL-4.0.20 from source on OSX 10.3.3 fwiw, my build env includes: gcc (GCC) 3.3 20030304 (Apple Computer, Inc. build 1640) autoconf --verautomake (GNU automake) 1.8.2 autoconf (GNU Autoconf) 2.59 ltmain.sh (GNU libtool) 1.5.6 (1.1220.2.94 2004/04/10 16:27:27) i've installed a NON-SYSTEM openssl (OpenSSL 0.9.7d 17 Mar 2004) /usr/local/ssl. this causes some headaches which, from what i've seen on the boards, are previously known, but, AFAIK, NOT fixed. i'm trying to get MySQL to build with/use *my* install of OpenSSL, i.e. the one in /usr/local/ssl. in order to get it to work, I'm having to make the following 'tweaks'/workarounds ... first, in order to get the build to find MY ssl install, I tried the "usual" ENV settings: unsetenv CFLAGS CPPFLAGS CXX CXXFLAGS LDFLAGS LDDLFLAGS LD_PREBIND LC_ALL LANG LINGUAS ;\ setenv LDFLAGS "-L/usr/local/ssl/lib -lssl -lcrypto -L/sw/lib -lreadline" ;\ setenv CPPFLAGS "-I/usr/local/ssl/include -I/sw/include" with a "configure" of: ./configure \ --prefix=/usr/local/mysql \ --localstatedir=/var/mysql \ --enable-shared --enable-static \ --enable-large-files \ --disable-maintainer-mode \ --enable-thread-safe-client \ --enable-assembler \ --with-pthread \ --with-mysqld-user=mysql \ --with-libwrap \ --with-vio \ --with-openssl \ --with-openssl-includes=/usr/local/ssl/include \ --with-openssl-libs=/usr/local/ssl/lib \ --with-isam \ --with-innodb \ --without-berkeley-db \ --without-docs \ --without-debug \ --without-bench unfortunately, that does not seem to work ... the build keeps finding the /usr/lib system install first. after a little digging, I find in "/usr/ports/mysql-4.0.20/configure:34077" ... for d in /usr/ssl/include /usr/local/ssl/include /usr/include \ ... on a whim, I set the following symlinks: ln -s /usr/local/ssl/include /usr/ssl/include ln -s /usr/local/ssl/lib /usr/ssl/lib and, now (!) the build uses MY /usr/local/ssl openssl install. after getting a little further in MAKE, it fails, complaining about 'gcc, -o, -S, and multiple connections'. some more digging on the web, and I track down the problem to a bug (?) in "libmysql_r/Makefile.in". the fix is: = (EDITOR) libmysql_r/Makefile.in INCLUDES = -I$(srcdir)/../include -I../include \ --- -I$(srcdir)/.. -I$(top_srcdir) -I.. $(openssl_includes) +++ -I$(srcdir)/.. -I$(top_srcdir) -I.. -I$(openssl_includes) = NOTE: since i'm building the thread-safe client, its using the libmysql_r dir; i presume that libmysql/Makefile doesw (may?) have similar issues ... with the changes above, now unsetenv CFLAGS CPPFLAGS CXX CXXFLAGS LDFLAGS LDDLFLAGS LD_PREBIND LC_ALL LANG LINGUAS ;\ setenv LDFLAGS "-L/usr/local/ssl/lib -lssl -lcrypto -L/sw/lib -lreadline" ;\ setenv CPPFLAGS "-I/usr/local/ssl/include -I/sw/include" glibtoolize --force --copy aclocal -I bdb/dist/aclocal -I innobase -I libmysql autoconf ./configure \ --prefix=/usr/local/mysql \ --localstatedir=/var/mysql \ --without-debug \ --without-bench make make install is successful. This seems to be wrapped up in the ID'ing/finding/verification of openssl libs ... but I don't know where to START hunting for the origination point of the probel to structure a bug fix ... i'm seeing libssl/libcrypto issues in a subsequent mysqlcc build that MAY be related to this ... but I can't tell (yet!). thoughts/suggestions? richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate does not exist
Good evening all. I'm facing a problem with a specific table and the uniques that it has. I have a table, hosting data of peoples names. Some of it's columns are, name_english, surname_english, name_original, surname_original, name_greek, surname_greek. I have as unique each combination of columns of the same language (i.e. name_english, surname_english, unique, etc). The records are at about 7000 and the problem that i have now is that when i try to add a specific name, it doesn't take it, echoing that it's duplicate. When I searched the table though, I couldn't find any name matching the one trying to insert. What I found was a name (name & surname) that it's almost identical but with one differente letter (i.e. there is on the database George Spyrou and I was trying to insert George Spytou). Could it be this similarity that causes problems, or should I look for something elese? Any help would be appreciated and I would be more that greatfull. If more info is needed I could give it. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
building 4.0.20 on Solaris2.7-x86 - make errors after configure
>Description: make v3.77 (and same with 3.8) aborts with these errors make[2]: Entering directory `/usr/local/mysql-4.0.20/readline' rm -f libreadline.a false cru libreadline.a readline.o funmap.o keymaps.o vi_mode.o parens.o rltty.o complete.o bind.o isearch.o display.o signals.o util.o kill.o undo.o macro.o input.o callback.o terminal.o xmalloc.o history.o histsearch.o histexpand.o histfile.o nls.o search.o shell.o tilde.o make[2]: *** [libreadline.a] Error 1 make[2]: Leaving directory `/usr/local/mysql-4.0.20/readline' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/mysql-4.0.20' make: *** [all] Error 2 >How-To-Repeat: Run configure with following options...other options give same result CC=gcc CFLAGS="-03 -fno-omit-frame-pointer" CXX=gcc CXXFLAGS="=-03 \ > -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \ > ./configure --prefix=/usr/local --localstatedir=/usr/home/mysql/data \ > --libexecdir=/usr/local/bin --with-extra-charsets=complex --enable-local-infile \ > --disable-shared --with-innodb THEN run make >Fix: >Submitter-Id: Bill Lane >Originator:same >Organization: GloryWorks >MySQL support: none...first time admin >Synopsis: make aborts with readline building mysql-4.0.20 >Severity: serious >Priority: high >Category: mysql >Class: sw-bug >Release: mysql-4.0.20 (Source distribution) >C compiler:2.95.3 >C++ compiler: 2.95.3 >Environment: System: SunOS paul.gloryworks.com 5.7 Generic_106542-02 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/i386-pc-solaris2.7/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='ccache gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1557648 Dec 11 1998 /lib/libc.a lrwxrwxrwx 1 root root 11 Jul 21 1999 /lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 947632 Dec 11 1998 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1557648 Dec 11 1998 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Jul 21 1999 /usr/lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 947632 Dec 11 1998 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-extra-charsets=complex' '--with-innodb' '--with-berkeley-db' '--with-embedded-server' '--enable-thread-safe-client' '--with-openssl' '--with-vio' '--with-raid' '--enable-local-infile' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=ccache gcc' Perl: This is perl, version 5.005_02 built for i86pc-solaris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to see if db exists...........mysql_query( myQuery)
I am going to use mysql_query() and want to find out if a certain database exists. If mysql_real_connect() fails there are many errors that could account for this. The database may exist , and I cant take the error returned from the failed query to mean the db isnt there and should be created. So what sort of query can I use to see whether the db exists? I thought of "USE myDb" but once again a failed mysql_query cant rule out other factors that can cause the failure. Any suggestions? Many thanks, sb
Re: Libraries and header files/MySQL Devel RPM
nevermind mates, it is. --- Carlos Sunden <[EMAIL PROTECTED]> wrote: > Hello > Libraries and header files RPM download at > http://dev.mysql.com/downloads/mysql/4.0.html > is the mysql-devel package, right? > Thanks! > Carlos > > > - > Do you Yahoo!? > Friends. Fun. Try the all-new Yahoo! Messenger __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"
Well, my "hack" (which is sort of like what you suggest) is to change my
primary key from just an auto_increment 'id' field to a combination of two
other fields (mac/scanner_id) that I know must be unique. Then I rely upon
the fact that mySQL will not allow a duplicate PK. (I did say it was a
hack). A co-worker assures me that a SELECT is cheap, however a version I
tried (without my hack) still allowed duplicates to slip through because I
wasn't locking the tables. I have multiple scanners hitting the same table
and locking seems to me a bad idea.
Also, I guess my TIMESTAMP brainstorm won't work b/c the resolution of that
field is 1 second and these queries happen faster than that. *Neuman!* :-/
REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.
http://daevid.com
> -Original Message-
> From: Steve Meyers [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 27, 2004 7:42 AM
> To: Daevid Vincent
> Cc: [EMAIL PROTECTED]
> Subject: Re: Feature Request: UPDATE 'error codes' or
> mysql_affected_rows() to be more "accurate"
>
> http://dev.mysql.com/doc/mysql/en/INSERT.html
>
> INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
> [INTO] tbl_name [(col_name,...)]
> VALUES ({expr | DEFAULT},...),(...),...
> [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
>
> If you specify the ON DUPLICATE KEY UPDATE clause (new
> in MySQL
> 4.1.0), and a row is inserted that would cause a duplicate value
> in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is
> performed.
>
>
> Daevid Vincent wrote:
> > I'm developing a program where I try an "UPDATE ... LIMIT 1" and if
> > mysql_affected_rows == 0, then I know nothing was updated
> and so I do an
> > INSERT. I find this is much cleaner and the majority of the
> time, I'm going
> > to do UPDATES, so I didn't want to waste a SELECT (even
> though I hear
> > they're "cheap"). I'm doing these queries several times per second.
> >
> > however... Of course UPDATE doesn't 'ERROR" if the record
> doesn't exist, it
> > just didn't do anything (therefore that's why I use the
> mysql_num_rows() to
> > check). The problem is that if I am actually doing an
> UPDATE to a record
> > where nothing actually changed in the existing record, I still get
> > mysql_affected_rows() equal to 0. *grrr*.
> >
> > It would be extremely useful to somehow get a result of
> maybe -1 if I tried
> > to update a record that didn't exist, versus a result of -2
> if I tried to
> > update a record that did exist, but mySQL didn't change anything.
> >
> > I don't know exactly what I'm asking for other than a way
> to know the
> > difference...
> >
> > At the very least, it seems to me that if I update a record
> that exists
> > already (even if no data changed), I should still get
> mysql_affected_rows()
> >
> >>0 (since in theory I matched something, even if mySQL
> behind the scenes
> >
> > didn't change the data).
> >
> > Out of curiosity, if I have a TIMESTAMP column, would that
> solve my problem,
> > since mySQL should be forced to update that TIMESTAMP
> right?? [btw, I know I
> > could try this idea, but I'm home and my code is at work
> right now and I
> > just had the idea! ;-]
> >
> > http://daevid.com
> >
> >
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Password on Latest MySQL, etc
Dear All, The mysql client & mysql-devel packages did it. Was able to change the password which I did with a very easy one. Now when I want to change the existing password & it fails. It does not let me. Thanks agaiN! --- Carlos Sunden <[EMAIL PROTECTED]> wrote: > Ok. > So far I got advises to install the MySQL-client > package & the mysql-devel RPM. > Will do that & report back. > I wouldn't think all that was needed when the > MySQL-server-rpm was installed. The functionality to > assign a password should be there. > I'm such a newbie at this, thanks to all again! > > Carlos > > > Victoria Reznichenko > <[EMAIL PROTECTED]> wrote: > Carlos Sunden wrote: > > > > Installed the MySQL-server-4.0.20-0.i386.rpm on a > RHL8 system > > > > Initialized the grant tables and then did: > > /usr/bin/mysqladmin -u rt password ACTUALPASSWORD > > And got: > > -bash: /usr/bin/mysqladmin: No such file or > directory > > > > What gives mates? > > I had used the exact command before in another > RHL8 system but it was a tar.gz package, I think. > > > > How can I changed or apply a password to mysql? > > > > Also, are there any other post-installation things > to do after a Mysql installation? > > Newbie here. I'd like to know how to make Mysql > secure & more efficient. > > > > Install MySQL-client package. > > > -- > 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] > > > - > Do you Yahoo!? > Friends. Fun. Try the all-new Yahoo! Messenger __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.0.13 (Production) Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.0.13, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. Version 3.0.13 is a bugfix release for the production tree that is suitable for use with any MySQL version including MySQL-4.1 or MySQL-5.0. This release is intended to fix three minor bugs, two which impact users using MysqlConnectionPoolDataSource, and one bug fix for prepared statements with batched parameters creating all keys for getGeneratedKeys(). It is now available in source and binary form from the Connector/J download pages at http://dev.mysql.com/downloads/connector/j/3.0.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) -Mark - From the changelog: - Fixed BUG#3848 - Using a MySQLDatasource without server name fails - Fixed BUG#3920 - "No Database Selected" when using MysqlConnectionPoolDataSource. - Fixed BUG#3873 - PreparedStatement.getGeneratedKeys() method returns only 1 result for batched insertions -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAtjsetvXNTca6JD8RAsc6AJ96nLVvVeOK6ODQcvBeC+xmWJ0eRgCfbiMk Azz5OAldJjoBD8UElM65E7Q= =cIfj -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Benchmark.
At 11:02 AM 5/27/2004 -0700, you wrote: Hi, I am sure all FreeBSD users are sick and tired of saying this. USE 4.10 Lots of people blindly follow version numbers but 5.x is a lot different than 4.x in ways I don't yet feel comfortable with using on production machines. Only one of those issues is with benchmark numbers, but that is certainly one of them. Thank you, Eric Eric, The FreeBSD dev team sure isn't saying this. We have/had lengthy threads going in FreeBSD-threads and other mailing lists regarding this problem, nobody brought up running 4.x there. The FreeBSD dev team members basically suggested to run 5.2.x-CURRENT using libpthreads which produced horrible results. We tried both with kernels configured with _ULE and _4BSD. We tried probably 30 different combinations of OS, MySQL version etc. Also, Jeremy Zawodny's site (and book, iirc) suggests to use FreeBSD 5.x, although I think there is somewhat of a disclaimer that it is just what he has heard from others, and not what he has witnessed himself. I'm not saying that is the correct thing to do, I'm just saying this is the information that is out there on this subject from authoritative figures, and that it should be updated to reflect the truth. I'll try FreeBSD 4.x later (with LinuxThreads) but I don't see how it will do much better. - Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Enable Full Query Logging?
"Robinson, Eric" <[EMAIL PROTECTED]> wrote: > Hi, > > I included the following statement in my.ini: > >log=3Dc:\log.txt > > I then executed some queries. > > The file log.txt only shows the following: > > MySql, Version: 4.0.13-nt-log, started with: > TCP Port: 3306, Named Pipe: MySQL > Time Id CommandArgument > 040527 9:31:57 1 Connect [EMAIL PROTECTED] as anonymous on=20 > 040527 9:32:07 1 Query show status > 040527 9:32:17 1 Query show status > 040527 9:32:27 1 Query show status > 040527 9:32:37 1 Query show status > 040527 9:32:47 1 Query show status > 040527 9:32:57 1 Query show status > > I thought the log file was supposed to show the full text of SELECT, > INSERT, and UPDATE statements. Am I incorrect? You are right. Probably you didn't execute any INSERT/UPDATE/SELECT statements, that is why they are not in the log file. > > How do I see this information? > -- 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: MySQL Benchmark. OT and beyond...
Err... I guess I owe Jeremy Zawodny an apology for mistyping his name. I promise next time I won't type without reading. His site is at http://jeremy.zawodny.com/. And I really recommend his book "High Performance MySQL". Sorry for the typo, Zawodny! RV Tec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF-8 settings and woes (update)
hi mark,
What happens if you explicitly specify the table character set to be
'utf-8'? (i.e. you're relying on the database default character set to
take care of that for you right now)...
'CREATE TABLE foo CHARACTER SET utf8'
the same.
All I can say is that with the testcase I posted, it is shown that what
you put in in UTF-8 format is what you get out, byte-for-byte with no
double transformations (getBytes() _never_ uses charset information, so
comparing ResultSet.getBytes() with a String.getBytes("utf-8") shows
that the data is retrieved in UTF-8 form).
here we are in strong agreement. what you put in you get out. ;-)
anyway i guess we are nearing to an end. i have discovered a way
cool feature of "SQL Query Plugin" (i named it wrongly SQLExplorer)
by stefan stiller.
you can switch the display-format of the query result if you like.
for example you can display your strings as bytes in different
encodings. guess what i did ;-)
lets see the results. i have only take the family_name from my
examples and left out the russian cyrilic values and the UTF-16
representations.
a) write via a script (console or sqlexplorer)
write | read | bytes | enc
Käßsel | Käßsel | 4b c3 a4 c3 9f 73 65 6c | UTF-8
Käßsel | Käßsel | 4b e4 df 73 65 6c | ISO-8859-1
Ægÿl | Ægÿl | c3 86 67 c3 bf 6c | UTF-8
Ægÿl | Ægÿl | c6 67 ff 6c | ISO-8859-1
b) write with my test case
write | read | bytes | enc
Käßsel | KäÃ?sel | 4b c3 83 c2 a4 c3 83 c2 9f 73 65 6c | UTF-8
Käßsel | KäÃ?sel | 4b c3 a4 c3 9f 73 65 6c | ISO-8859-1
Ægÿl | Ã?gÿl | c3 83 c2 86 67 c3 83 c2 bf 6c | UTF-8
Ægÿl | Ã?gÿl | c3 86 67 c3 bf 6c | ISO-8859-1
as you can see the values in b) are being transformed twice.
for example the 'ä' (LATIN SMALL LETTER A WITH DIAERESIS -
codepoint U+00E4) is being escaped into 'c3a4' for UTF-8. now during
a second transformation somebody interprets that as an 8-bit encoding
with the codepoints U+00C3 (LATIN CAPITAL LETTER A WITH TILDE) and
U+00A4 (CURRENCY SIGN) and escapes them gain for UTF-8. the first
gets transformed into 'C383' and the second into 'C2A4'.
see http://www1.tip.nl/~t876506/utf8tbl.html for the encoding-table.
so basically we know how. but we dont know why and even more important
where to switch that off.
i dont think it is a database problem as other apps work seemlessly
with the db. i dont even think that is a bug on the driver. as other
apps are using the same driver with the same connection-url i use
in my test-app.
i am quite sure it is a matter of configuring the driver or
driver-manager correctly. can you assist there?
thank you for your patience.
ciao robertj
smime.p7s
Description: S/MIME Cryptographic Signature
Re: MySQL Benchmark. OT and beyond...
Eric, > I am sure all FreeBSD users are sick and tired of saying this. USE > 4.10 > Lots of people blindly follow version numbers but 5.x is a lot different > than 4.x in ways I don't yet feel comfortable with using on production > machines. Only one of those issues is with benchmark numbers, but that > is certainly one of them. If you don't feel comfortable with 5_branch on production, that's fine, I respect your choice. On the other hand, we have lots of official documentation saying that LinuxThreads, threading, memory handling, disk access is improved in 5_branch (compared to 4). I'm looking for an OS that can handle my database. OpenBSD -- which, I just proved by a test machine -- performs better than FreeBSD. That's why I can't even imagine how poorly FreeBSD 4.10 (which has just been released) would handle my database. I'm not looking for the cutting edge OS. I'm not trying to find one that performs .1ms better than the other. I just want a OS that handle my database on a decent i386 machine -- and yes, I would be glad to give AMD64 a try, as long as someone tells me that it will be able to handle my load (50 concurrent connections, 6GB database, increasing by 1 million rows/month). Heck, if it was up to me, I would still be using Linux 0.99q on 386SX. Take care, RV Tec PS: There's no need to CC my address. I'm subscribed to the list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select
"MYSQL" <[EMAIL PROTECTED]> wrote: > Is it possible to write a SELECT statement that will select all columns but 1 or 2 > certain > ones? Nope. > > I know I can do SELECT Col1,Col2,Col3 etc. but if there are a few columns, I would > like to > be able to do somehting like SELECT * (NOT Col12,Col13), if that makes any sense. -- 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: MySQL Benchmark. OT and beyond...
Hi, I am sure all FreeBSD users are sick and tired of saying this. USE 4.10 Lots of people blindly follow version numbers but 5.x is a lot different than 4.x in ways I don't yet feel comfortable with using on production machines. Only one of those issues is with benchmark numbers, but that is certainly one of them. Thank you, Eric At 10:48 AM 5/27/2004, RV Tec wrote: >JG, > >> I am a FreeBSD user, but after having run benchmarks for >> the past 2 weeks, I think you'll be surprised when you see >> the results from Linux. >> >> Linux will outperform *BSD by nearly double when it comes to >> MySQL. > >That's what I expect and hope for. Although, I thought that FreeBSD >would perform much better than OpenBSD. I am going to compile MySQL >with pthread on FreeBSD. This way I can truly compare those OS's. > >Linux is going to be my next test. > >Best regards, >RV Tec > >-- >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 Benchmark.
JG, > I am a FreeBSD user, but after having run benchmarks for > the past 2 weeks, I think you'll be surprised when you see > the results from Linux. > > Linux will outperform *BSD by nearly double when it comes to > MySQL. That's what I expect and hope for. Although, I thought that FreeBSD would perform much better than OpenBSD. I am going to compile MySQL with pthread on FreeBSD. This way I can truly compare those OS's. Linux is going to be my next test. Best regards, RV Tec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select
Hi, Is it possible to write a SELECT statement that will select all columns but 1 or 2 certain ones? I know I can do SELECT Col1,Col2,Col3 etc. but if there are a few columns, I would like to be able to do somehting like SELECT * (NOT Col12,Col13), if that makes any sense. Thanks
Re: Textfile to table
I will try that.
Thanks.
- Original Message -
From: "Victor Pendleton" <[EMAIL PROTECTED]>
To: "'Haplo '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "'Paul '" <[EMAIL PROTECTED]>
Sent: Thursday, May 27, 2004 1:28 PM
Subject: RE: Textfile to table
> How is the data delimited? You may be able to use the LOAD DATA INFILE
> command.
>
> -Original Message-
> From: Haplo
> To: [EMAIL PROTECTED]
> Cc: Paul
> Sent: 5/27/04 12:23 PM
> Subject: Textfile to table
>
> Hi,
> I am trying to add this info into a table and I know there is a way to
> do it without using the insert command on every line of data.
>
> 201 200 NJ Jersey City
>
>
>
> INSERT INTO categories VALUES ('201', '200', 'NJ', 'Jersey City');
>
>
>
> there are way too many lines to do by manual inserts.
>
>
>
> Any comments would help. Thanks
>
>
>
> Paul
>
>
>
>
>
>
>
> --
> 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: Textfile to table
How is the data delimited? You may be able to use the LOAD DATA INFILE
command.
-Original Message-
From: Haplo
To: [EMAIL PROTECTED]
Cc: Paul
Sent: 5/27/04 12:23 PM
Subject: Textfile to table
Hi,
I am trying to add this info into a table and I know there is a way to
do it without using the insert command on every line of data.
201 200 NJ Jersey City
INSERT INTO categories VALUES ('201', '200', 'NJ', 'Jersey City');
there are way too many lines to do by manual inserts.
Any comments would help. Thanks
Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Benchmark.
Facing this new scenario, I am going to give Linux a shot -- definitively, this one is going to outperform OpenBSD. Using the same hardware, and the same options (as possible). Does anyone have a hint for this? Again, thanks a lot! Best regards, RV Tec I am a FreeBSD user, but after having run benchmarks for the past 2 weeks, I think you'll be surprised when you see the results from Linux. Linux will outperform *BSD by nearly double when it comes to MySQL. JeremyZ's BLOG should be updated again. The information it is giving out regarding FreeBSD 5.x solving a myriad of problems is not entirely accurate - if we are talking about FreeBSD vs Linux MySQL performance anyway. Even with LinuxThreads FreeBSD lags way behind. ::sigh:: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Textfile to table
Hi,
I am trying to add this info into a table and I know there is a way to do it without
using the insert command on every line of data.
201 200 NJ Jersey City
INSERT INTO categories VALUES ('201', '200', 'NJ', 'Jersey City');
there are way too many lines to do by manual inserts.
Any comments would help. Thanks
Paul
Re: UTF-8 settings and woes (update)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
robert kuzelj wrote:
> hi mark,
>
>> If you specify UTF-8 as the characterEncoding connection property, then
>> that is the transform that is used from client -> server. The transform
>> that is used from server -> client is whatever character set the column
>> in the table is set to when you created the table (or conversely if you
>> use cast/convert in SQL to change it to some other character set).
> so you say ;-) and i still have my doubts about it (but more on that
> later). what i have done now is to extract a junit test as requested
> by you (TestUTF8.java). i executed the test on two different machines
> now (suse linux 9.0 and WinXp both running mysql 4.1.1-a). as you would
> expect the tests run perfectly well.
What happens if you explicitly specify the table character set to be
'utf-8'? (i.e. you're relying on the database default character set to
take care of that for you right now)...
'CREATE TABLE foo CHARACTER SET utf8'
All I can say is that with the testcase I posted, it is shown that what
you put in in UTF-8 format is what you get out, byte-for-byte with no
double transformations (getBytes() _never_ uses charset information, so
comparing ResultSet.getBytes() with a String.getBytes("utf-8") shows
that the data is retrieved in UTF-8 form).
-Mark
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAtiEZtvXNTca6JD8RAtYAAKCmKK/6VQr5W7X4Pz299zufHrDdtwCdHddv
Amm4puj9SmCSuuQzMPfBZjw=
=rPtF
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF-8 settings and woes (update)
hi mark,
If you specify UTF-8 as the characterEncoding connection property, then
that is the transform that is used from client -> server. The transform
that is used from server -> client is whatever character set the column
in the table is set to when you created the table (or conversely if you
use cast/convert in SQL to change it to some other character set).
so you say ;-) and i still have my doubts about it (but more on that
later). what i have done now is to extract a junit test as requested
by you (TestUTF8.java). i executed the test on two different machines
now (suse linux 9.0 and WinXp both running mysql 4.1.1-a). as you would
expect the tests run perfectly well.
but again there is the problem that every other client displays
garbage. now i have tried 3 additional clients on my xp-machine
(SqlExplorer in intellij, mysqlcc.0.9.4 and mysql on a console).
when running the junit tests all oft these win-clients display
garbage but that was to be expected.
now i again made the reverse check. i executed some insert and
select scripts on the console (cygwin) and displayed the results
(attached con_create.sql and con_select.sql) again no real surprise.
sqlexplorer displayed the data correct as did mysqlcc. only on
the console the results were garbled. this was to be expected as
i dont know how to set the locale on windows. when i piped the
results of the con_select.sql into a file and displayed this via a
UTF-8-enabled editor everything was sound (attached out.txt)
so the facts are as this
- if i write with my own test-app only my test-app is able to read
the data correctly.
- if i write via a sql-script (executed via the shell or via a
java-application like sqlexplorer [that btw uses the same driver
and the same connection-str as my own test-app]) every client
app can display the data correctly (except the windows console)
ONLY my app is not able to read it correctly (well it has to switch to
getBytes instead of getString then it works).
at the moment my guess is that in my test-app a double transformation
is taking place, that does not happen in the other java-apps
(JFaceDBC and SqlExplorer).
any comments?
ciao robertj
ID FAMILY_NAME GIVEN_NAME
01 KäßselBöb
02 Ægÿl Àlbért
03 интернацион интернацион
drop database if exists UTF8_TEST;
create database UTF8_TEST;
use UTF8_TEST;
drop table if exists UTF8_TEST;
create table UTF8_TEST
( ID CHAR(02),
FAMILY_NAME CHAR(32),
GIVEN_NAME CHAR(32),
) TYPE = INNODB;
insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) values ('01', 'Käßsel', 'Böb');
insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) values ('02', 'Ægÿl',
'Àlbért');
insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) values ('03',
'интернацион', 'интернацион');
use UTF8_TEST;
select * from UTF8_TEST order by ID;
package org.pragmatico.ctpe.test.service.umlaut;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import junit.framework.TestCase;
import junit.framework.Test;
import junit.framework.TestSuite;
public class TestUTF8 extends TestCase
{
public TestUTF8(String _name)
{
super(_name);
}
public static Test suite()
{
TestSuite suite;
suite = new TestSuite(TestUTF8.class);
return suite;
}
public void testUTF8() throws Exception
{
String url =
"jdbc:mysql://localhost/?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";
String cls = "com.mysql.jdbc.Driver";
String user = "";
String pwd = "";
Connection con = null;
Statement stmt;
try
{
Class.forName(cls);
con = DriverManager.getConnection(url, user, pwd);
stmt = con.createStatement();
stmt.executeUpdate( "drop database if exists UTF8_TEST");
stmt.executeUpdate( "create database UTF8_TEST");
stmt.executeUpdate( "use UTF8_TEST");
stmt.executeUpdate( "drop table if exists UTF8_TEST");
stmt.executeUpdate( "create table UTF8_TEST" +
"( ID CHAR(02)," +
"FAMILY_NAME
CHAR(32)," +
"GIVEN_NAME
CHAR(32)," +
") TYPE = INNODB");
stmt.executeUpdate("insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) " +
"values ('01', 'Käßsel', 'Böb')");
stmt.executeUpdate("insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) " +
How to Enable Full Query Logging?
Hi, I included the following statement in my.ini: log=c:\log.txt I then executed some queries. The file log.txt only shows the following: MySql, Version: 4.0.13-nt-log, started with: TCP Port: 3306, Named Pipe: MySQL Time Id CommandArgument 040527 9:31:57 1 Connect [EMAIL PROTECTED] as anonymous on 040527 9:32:07 1 Query show status 040527 9:32:17 1 Query show status 040527 9:32:27 1 Query show status 040527 9:32:37 1 Query show status 040527 9:32:47 1 Query show status 040527 9:32:57 1 Query show status I thought the log file was supposed to show the full text of SELECT, INSERT, and UPDATE statements. Am I incorrect? How do I see this information? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for a C API if it exisits
On Tue, May 25, 2004 at 03:36:08PM -0600, Sarix wrote: > Cause when I do my web end to all this life > is easy cause I can do $name = $row['Name'] and it works for PHP so I > figured there has to be an API that would do that.. But I can't seem to find > it in the documention. Is there something that will help with this, or is C > just too old to have helpful ablitles like this. :) People have written libraries for C to emulate higher-level languages. Here is an example, but I've not used it myself: http://www.annexia.org/freeware/c2lib/doc/ It speaks of 'vectors', and you could easily wrap your DB access routines in this, to give you something very like the named hashes that Perl and PHP use. -- Brian Reichert <[EMAIL PROTECTED]> 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about mysql_query()
In C API, after running mysql_query(), I want to stop this query by mysql_kill(). But I can not get the specific process (query) ID. How could I solve this problem? mysql_list_processes() return a list of all processes (quries) running on the server. But it is possible that different user may running the same query, so I still cannot figure out which process ID I should kill. In fact, if mysql_query() return the process ID of the query, then there would be no such problem. I am wondering why it returns 0 but not process ID. Thanks SY __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Libraries and header files/MySQL Devel RPM
Hello Libraries and header files RPM download at http://dev.mysql.com/downloads/mysql/4.0.html is the mysql-devel package, right? Thanks! Carlos - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
help reading test-select
Hi, I was running the test-select under the /usr/local/mysql/sql-bench dir and was wondering if someone could help break it down for me ( Or at least the "Testing big selects on the table" section ) Thanks : [EMAIL PROTECTED] sql-bench]# perl test-select --password='d' Testing server 'MySQL 4.0.20 standard log' at 2004-05-27 8:29:45 Testing the speed of selecting on keys that consist of many parts The test-table has 1 rows and the test is done with 500 ranges. Creating table Inserting 1 rows Time to insert (1): 17 wallclock secs ( 0.28 usr 0.13 sys + 0.00 cusr 0.00 csys = 0.41 CPU) Test if the database has a query cache Time for select_cache (1): 3 wallclock secs ( 1.84 usr 0.20 sys + 0.00 cusr 0.00 csys = 2.04 CPU) Time for select_cache2 (1): 64 wallclock secs ( 2.29 usr 0.32 sys + 0.00 cusr 0.00 csys = 2.61 CPU) Testing big selects on the table Time for select_big (70:17207): 1 wallclock secs ( 0.22 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.26 CPU) Time for select_range (410:1057904): 19 wallclock secs (13.03 usr 1.86 sys + 0.00 cusr 0.00 csys = 14.89 CPU) Time for min_max_on_key (7): 17 wallclock secs (13.88 usr 1.18 sys + 0.00 cusr 0.00 csys = 15.06 CPU) Time for count_on_key (5): 12 wallclock secs ( 9.78 usr 0.94 sys + 0.00 cusr 0.00 csys = 10.72 CPU) Time for count_group_on_key_parts (1000:10): 2 wallclock secs ( 1.40 usr 0.12 sys + 0.00 cusr 0.00 csys = 1.52 CPU) Testing count(distinct) on the table Time for count_distinct_key_prefix (1000:1000): 0 wallclock secs ( 0.18 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.21 CPU) Time for count_distinct (1000:1000): 0 wallclock secs ( 0.23 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.24 CPU) Time for count_distinct_2 (1000:1000): 1 wallclock secs ( 0.24 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.26 CPU) Time for count_distinct_group_on_key (1000:6000): 0 wallclock secs ( 0.27 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.29 CPU) Time for count_distinct_group_on_key_parts (1000:10): 3 wallclock secs ( 1.34 usr 0.22 sys + 0.00 cusr 0.00 csys = 1.56 CPU) Time for count_distinct_group (1000:10): 2 wallclock secs ( 1.44 usr 0.11 sys + 0.00 cusr 0.00 csys = 1.55 CPU) Time for count_distinct_big (100:100): 15 wallclock secs (11.96 usr 1.52 sys + 0.00 cusr 0.00 csys = 13.48 CPU) Total time: 156 wallclock secs (58.39 usr 6.73 sys + 0.00 cusr 0.00 csys = 65.12 CPU) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Benchmark.
Folks, Following some advices, I have decided to give FreeBSD a shot. So, I got one test machine (P4 2.0GHz, 512MB), installed FreeBSD 5.2.1 on one disk, and OpenBSD 3.5 on the other (both 40GB 7200RPM IDE disks) -- so I could compare the results. MySQL (4.0.20) is compiled from source, using the very same config options as possible... I have followed the advice from Jeremy Zawadony's site, and compiled FreeBSD using LinuxThreads. With that, everything was pointing out that FreeBSD+LinuxThreads would outperform OpenBSD+pthread. Honestly, I am not sure if the binaries provided by MySQL.com would be a good way to really test this, since they do not offer binaries for OpenBSD 3.5 and FreeBSD 5.2.1. But if needed, I am willing to try everything. I am no test engineer, I can not say that this is 100% accurate, or even if this is the right way to test MySQL performance. Maybe you guys can tell me how to test this better. I have chosen Super-smack for this, using the standard smacks. I have run each test three times in a row, and then, three times with a fresh boot in between. Definitively, I am very impressed with the results -- I can make them available if needed. There is an amazing coherence in those results. And the most amazing thing is: OpenBSD is performing better than FreeBSD! Please, I am not trying to say that OpenBSD is better... the fact is that OpenBSD is not able to handle my database anymore, and I am trying to find another OS. Facing this new scenario, I am going to give Linux a shot -- definitively, this one is going to outperform OpenBSD. Using the same hardware, and the same options (as possible). Does anyone have a hint for this? Again, thanks a lot! Best regards, RV Tec Quick results: OpenBSD 3.5 super-smack -d mysql select-key.smack 50 10 Query Barrel Report for client smacker1 connect: max=53ms min=33ms avg= 43ms from 50 clients Query_type num_queries max_timemin_timeq_per_s select_index100043 7 5686.01 364.867u 217.078s 29:19.04 33.0%0+0k 104+4io 0pf+0w super-smack -d mysql update-select.smack 50 10 Query Barrel Report for client smacker connect: max=52ms min=30ms avg= 42ms from 50 clients Query_type num_queries max_timemin_timeq_per_s select_index500 28 8 2867.73 update_index500 33 8 2867.73 298.203u 171.242s 29:03.84 26.9%0+0k 104+4io 0pf+0w FreeBSD 5.2.1 super-smack -d mysql select-key.smack 50 10 Query Barrel Report for client smacker1 connect: max=13002ms min=33ms avg= 10956ms from 50 clients Query_type num_queries max_timemin_timeq_per_s select_index100012 0 3811.89 426.858u 518.244s 43:43.59 36.0%112+502k 4+0io 7pf+0w super-smack -d mysql update-select.smack 50 10 Query Barrel Report for client smacker connect: max=39ms min=26ms avg= 30ms from 50 clients Query_type num_queries max_timemin_timeq_per_s select_index500 16 0 1737.15 update_index500 17 0 1737.15 334.275u 374.935s 47:58.63 24.6%112+501k 57+0io 9pf+0w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow insert into select statement
Something I have done in the past (with another DB system) was to put indexes on my temp tables. I have just gone over my copy of the MySQL docs and I don't see where that is NOT allowed so I think its fair game to try it. You have two options to do this: Create the temp tables then populate them (two statements) -or- Use ALTER TABLE your temp tables to create the indexes. I have no data about which is definitely faster although I have read many times that if you index a table AFTER filling it with data, the entire operation tends to finish quicker (especially if you have lots of data). Since you are using multiple values in your joins (when you are collecting the historical prices) lets try indexing tmpLatestDates and tmpPrevDates on both columns. Do you have an index on rptPricingTest for (commodity, PricingDt) ? That could also speed up the joins. You could also combine the collection of one set of data (I chose to do your previous months values)with your final results calculations. Maybe this will work faster SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt > @date1 AND PricingDt <= @date2 GROUP BY commodity; ALTER TABLE tmpLatestDates ADD KEY (commodity, MaxDate); CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; ALTER TABLE tmpLatestPrices ADD KEY(commodity); CREATE TEMPORARY TABLE tmpPrevDates SELECT a.commodity, MAX(a.PricingDt) as PrevDate FROM rptPricingTEST a, tmpLatestPrices b WHERE a.PricingDt < @date1 AND a.commodity = b.commodity GROUP BY commodity; ALTER TABLE tmpPrevDates ADD KEY(commodity, PrevDate); /* I eliminated one temp table and added some indexes */ SELECT a.commodity, a.PricingDtas PrevDate, a.PricingAvgPrice as PrevAvg, tlp.PricingDtas LatestDate, tlp.PricingAvgPrice as LatestAvg, ((tlp.PricingAvgPrice - a.PricingAvgPrice) /a.PricingAvgPrice) * 100 as priceChange FROM rptPricingTEST a INNER JOIN tmpPrevDates tpd ON a.commodity = tpd.commodity AND a.PricingDt = tpd.PrevDate INNER JOIN tmpLatestPrices tlp ON tlp.commodity = a.commodity; DROP TABLE IF EXISTS tmpLatestDates, tmpPrevDates, tmpLatestPrices SELECT TIMEDIFF(NOW(),@start); Now, because we are using INNER JOINS, only those commodities that actually existed in the previous month will be returned. That actually makes some sense as you cannot calculate a % change from "nonexistence" to "some value". Let us know how this works out, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine nyem <[EMAIL PROTECTED]To: [EMAIL PROTECTED] .my> cc: Fax to: 05/27/2004 05:01 Subject: Re: slow insert into select statement AM Thanks for all the feedback. Here's my latest attempt: SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); LOCK TABLES rptPricingTEST READ, rptPricingTEST a READ; CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt > @date1 AND PricingDt <= @date2 GROUP BY commodity; CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; CREATE TEMPORARY TABLE tmpPrevDates SELECT
MySQL clustering support for Windows
I wished to use MySQL clustering for application development that is Windows-based. Since the binaries aren't available as yet, I tried using the source distribution (tried to build using cygwin) but it gave a number of errors. Is there any other way of installing MySQL 4.1 with Windows ( that has clustering support) ? If anyone has used MySQL clustering with Windows, I would also like to know its performance and support for Windows.. Thanks, Nandan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Defaul of NOW()
I didn't think you could have a DEFAULT of NOW() because it's not a true static value. Seems I read that in one of Paul DuBois' books. Jim Winstead wrote: On Wed, May 26, 2004 at 06:20:22PM -0700, Scott Haneda wrote: I have a field in mysql 4, using InnoDB Field is timestamp 14 and defualt is set to 00, which I want to be the result of NOW() so that every record made will get NOW() as the value, I can not get it to work... ALTER TABLE `addresses` CHANGE `added` `added` TIMESTAMP( 14 ) DEFAULT 'NOW()'; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 It tells me it worked, but then it reverts back to the zero's. If you do a SHOW CREATE TABLE on the table, you'll see that it has actually ignored your DEFAULT. (What is has done is actually transformed it to '00', since that is what the string 'NOW()' becomes when you convert it to a TIMESTAMP.) Read this section in the manual for information on how the default value for TIMESTAMP columns is handled: http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html Support for specifying how TIMESTAMP columns get updated is coming in 4.1.2. Right now, it is only documented in the change notes: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html Jim Winstead MySQL AB -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, > > Why not? What's wrong with this: > > > > BORROWER > > BorrowerID > > > > BOOKS > > BookID > > BorrowerID (nullable) > > > > FK from Books.BorrowerID to Borrower.BorrowerID > > > > I haven't checked, but this _should_ be possible. > > > > With regards, > > Its a foreign key, you can not null foreign keys.. Thats the problem. If this really is the case with MySQL, then this is the only database engine that I know that doesn't allow this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"
http://dev.mysql.com/doc/mysql/en/INSERT.html
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL
4.1.0), and a row is inserted that would cause a duplicate value
in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is
performed.
Daevid Vincent wrote:
I'm developing a program where I try an "UPDATE ... LIMIT 1" and if
mysql_affected_rows == 0, then I know nothing was updated and so I do an
INSERT. I find this is much cleaner and the majority of the time, I'm going
to do UPDATES, so I didn't want to waste a SELECT (even though I hear
they're "cheap"). I'm doing these queries several times per second.
however... Of course UPDATE doesn't 'ERROR" if the record doesn't exist, it
just didn't do anything (therefore that's why I use the mysql_num_rows() to
check). The problem is that if I am actually doing an UPDATE to a record
where nothing actually changed in the existing record, I still get
mysql_affected_rows() equal to 0. *grrr*.
It would be extremely useful to somehow get a result of maybe -1 if I tried
to update a record that didn't exist, versus a result of -2 if I tried to
update a record that did exist, but mySQL didn't change anything.
I don't know exactly what I'm asking for other than a way to know the
difference...
At the very least, it seems to me that if I update a record that exists
already (even if no data changed), I should still get mysql_affected_rows()
0 (since in theory I matched something, even if mySQL behind the scenes
didn't change the data).
Out of curiosity, if I have a TIMESTAMP column, would that solve my problem,
since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I
could try this idea, but I'm home and my code is at work right now and I
just had the idea! ;-]
http://daevid.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 27 May 2004 03:00 am, Martijn Tonies wrote: > Why not? What's wrong with this: > > BORROWER > BorrowerID > > BOOKS > BookID > BorrowerID (nullable) > > FK from Books.BorrowerID to Borrower.BorrowerID > > I haven't checked, but this _should_ be possible. > > With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. - -- Practice safe sin. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtfoTld4MRA3gEwYRApVcAJ9RNWF9qZrresicBlct3TP2fpEL1wCg3xIx VER2P5MGzLgUqLRc7JZLFys= =qVNw -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Password on Latest MySQL, etc
Ok. So far I got advises to install the MySQL-client package & the mysql-devel RPM. Will do that & report back. I wouldn't think all that was needed when the MySQL-server-rpm was installed. The functionality to assign a password should be there. I'm such a newbie at this, thanks to all again! Carlos Victoria Reznichenko <[EMAIL PROTECTED]> wrote: Carlos Sunden wrote: > > Installed the MySQL-server-4.0.20-0.i386.rpm on a RHL8 system > > Initialized the grant tables and then did: > /usr/bin/mysqladmin -u rt password ACTUALPASSWORD > And got: > -bash: /usr/bin/mysqladmin: No such file or directory > > What gives mates? > I had used the exact command before in another RHL8 system but it was a tar.gz > package, I think. > > How can I changed or apply a password to mysql? > > Also, are there any other post-installation things to do after a Mysql installation? > Newbie here. I'd like to know how to make Mysql secure & more efficient. > Install MySQL-client package. -- 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] - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
Re: Foreign Key Constraints
You could create a special "borrower" account to signify that it is not loaned out and assign that to the book. If this is for a library system (multiple branches) you could create one account for each branch. That way you would know where the book is at all times, "borrowed" or not ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine <[EMAIL PROTECTED] m> To: [EMAIL PROTECTED] cc: 05/27/2004 12:22 Fax to: AM Subject: Foreign Key Constraints Please respond to kyuubi Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. Msg sent via Spymac Mail - http://www.spymac.com -- 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: Defaul of NOW()
Only constant values are allowed as default values. If this is the first timestamp column it will be updated on insert and with every update. -Original Message- From: Scott Haneda To: MySql Sent: 5/26/04 8:20 PM Subject: Defaul of NOW() I have a field in mysql 4, using InnoDB Field is timestamp 14 and defualt is set to 00, which I want to be the result of NOW() so that every record made will get NOW() as the value, I can not get it to work... ALTER TABLE `addresses` CHANGE `added` `added` TIMESTAMP( 14 ) DEFAULT 'NOW()'; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 It tells me it worked, but then it reverts back to the zero's. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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: Problems with "Order By" (phpMyAdmin)
David Blomstrom wrote: I'm working on an add/edit form, illustrated by the screehnshot at http://www.geoworld.org/addedit2.gif I decided to arrange the rows by ID, rather than alphabetically. So I opened the table in phpMyAdmin, clicked Operations, then changed "Order by" from a field named SCode to ID. When I clicked through, it defaulted to SCode. To get the order right on your web page you may need to add "order by ID" to the query in your script. The "alter table order by ID" statement executed from phpMyAdmin reorders the table's current rows, but after inserts or deletes the table will no longer be in that order. http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html Mike -- http://mike.kruckenberg.com | [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Could not start MySQL after reinstall - Can't open privilege tables: Table 'mysql.host' doesn't exist
Did you change the permissions for the files to include read/write/change for the user/account MySQL uses on your system? This doesn't require deleting, as far as I recall Windows permissions... Oh, can you verify that the file host under directory mysql DOES exist? If so, it's really most likely a permissions problem. MICHAEL_WU wrote: Since the database I created was not critical, I could simply throw it away. Therefore, delete the whole MySql installation directory and unzip the alpha release zip file again to create the installation directory tree. However, the following error persists: Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Everything should be wiped out I thought. Does MySQL saves some data somewhere other than the installation directory? More help, please? Michael Wu -Original Message- From: Robert J Taylor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:52 PM To: "michael_wu[¡±d¡±?1F]" Cc: [EMAIL PROTECTED] Subject: Re: Could not start MySQL after reinstall - Can't open privilege tabl es: Table 'mysql.host' doesn't exist Check permissions on the mysql\data directory and files/folders below for the MySQL process/user (sorry for not knowing the right Windows terminology, I'm not a Windows user). HTH, Robert J Taylor [EMAIL PROTECTED] michael_wu[§d§»¹F] wrote: Hello, I run into a problem after re-installing mySQL 4.1.1 today. When I tried to start the service on my Windows2K, I got the following error: 040527 15:37:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Can some one tell me how to solve the problem? Thanks in advance! Michael Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Could not start MySQL after reinstall - Can't open privilege tables: Table 'mysql.host' doesn't exist
Since the database I created was not critical, I could simply throw it away. Therefore, delete the whole MySql installation directory and unzip the alpha release zip file again to create the installation directory tree. However, the following error persists: Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Everything should be wiped out I thought. Does MySQL saves some data somewhere other than the installation directory? More help, please? Michael Wu -Original Message- From: Robert J Taylor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:52 PM To: "michael_wu[¡±d¡±?1F]" Cc: [EMAIL PROTECTED] Subject: Re: Could not start MySQL after reinstall - Can't open privilege tabl es: Table 'mysql.host' doesn't exist Check permissions on the mysql\data directory and files/folders below for the MySQL process/user (sorry for not knowing the right Windows terminology, I'm not a Windows user). HTH, Robert J Taylor [EMAIL PROTECTED] michael_wu[§d§»¹F] wrote: >Hello, > I run into a problem after re-installing mySQL 4.1.1 today. When I >tried to start the service on my Windows2K, I got the following error: > > 040527 15:37:49 Fatal error: Can't open privilege tables: Table >'mysql.host' doesn't exist > > Can some one tell me how to solve the problem? > >Thanks in advance! >Michael Wu > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow insert into select statement
Thanks for all the feedback. Here's my latest attempt: SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); LOCK TABLES rptPricingTEST READ, rptPricingTEST a READ; CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt > @date1 AND PricingDt <= @date2 GROUP BY commodity; CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; CREATE TEMPORARY TABLE tmpPrevDates SELECT a.commodity, MAX(a.PricingDt) as PrevDate FROM rptPricingTEST a, tmpLatestPrices b WHERE a.PricingDt < @date1 AND a.commodity = b.commodity GROUP BY commodity; CREATE TEMPORARY TABLE tmpPrevPrices SELECT a.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpPrevDates b ON b.commodity = a.commodity AND b.PrevDate = a.PricingDt; SELECT a.commodity, a.PricingDtas PrevDate, a.PricingAvgPrice as PrevAvg, b.PricingDtas LatestDate, b.PricingAvgPrice as LatestAvg, ((b.PricingAvgPrice - a.PricingAvgPrice) /a.PricingAvgPrice) * 100 as priceChange FROM tmpPrevPrices a, tmpLatestPrices b WHERE a.commodity = b.commodity AND a.PricingAvgPrice < b.PricingAvgPrice; UNLOCK TABLES; DROP TABLE IF EXISTS tmpLatestDates, tmpPrevDates, tmpLatestPrices, tmpPrevPrices; SELECT TIMEDIFF(NOW(),@start); ++ | TIMEDIFF(NOW(),@start) | ++ | 00:00:08 | ++ 1 row in set (0.00 sec) Now I get it all executed in 8 seconds. [EMAIL PROTECTED] wrote: .. snip After reviewing your original post, I am not confident that you are answering your question with this query. You said "I need to generate a report of price change for all commodity for the date interval selected by users." which to me implies a report something like: +-+-+---+--++-+-+-+ |commodity|starting date|ending date|starting price|ending price|max price|min price|avg price| +-+-+---+--++-+-+-+ I am just not sure where you are going with the query you are building. Can you provide a template of the results you want? Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine You were right about this. I was merely posting the first part of my query, as that's where my trouble began. Here's the result table that I'm looking for, where priceChange is the % increase in price for the two date interval. +---+--+-++---+-+ | commodity | PrevDate | PrevAvg | LatestDate | LatestAvg | priceChange +--+-++---+-+ The slowest part of the script is when generating this table (5.00 sec). How could I optimise it further? I don't mind creating more temp tables as long as the total execution time is much reduced. I'm using mysql 4.1.1a-alpha on win2k. Thanks, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
Joshua J. Kugler wrote: There is one instance in which it is *not* convenient to store in seperate files: when you are exporting to another machine (maybe a sub set of data from an internal server to an external web server) or doing replication. Very true. We use MySQL to store our blobs for a JBoss application for this reason (among others; the main one being the system architect likes it that way -- it's good to be king!). j- k- On Wednesday 19 May 2004 01:01 pm, Greg Willits said something like: On May 19, 2004, at 1:19 PM, David Blomstrom wrote: I'd like to get some feedback on storing images in MySQL databases. The stuff I've read so far suggests that it's fairly difficult to work with images in MySQL, and they also slow down databases. One thing to remember when you have a blob (or text, iirc) column in a tuple (row, sorry; just Codding around and Dating myself) is that when MySQL examines the row the entire blob is loaded into memory even if that particular column isn't referenced in the query. So, use a split table for blobs -- a main table with the blob attributes for searching and a dependent table with its primary key set as a foreign key to the main table holding the blob for direct access to the blob as needed. This helped us emmensely. I've also read that there isn't much you can do with BLOB's that you can't do with PHP manipulating images stored in an ordinary folder. So I just wondered if BLOB's are worth my time. For example, I'm working on a database with information about the 50 states. If I have maps of each state, pictures of each state's capital, etc., is there some BLOB feature that I would find really useful? In your case, a field holding the data particulars with a middleware parsible URN (a local filesystem path or remote URL, et al) to the blob or text body should suffice. All "conventional wisdom" I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename &/or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. -- greg willits Luckily, MySQL handles the bloat quite well in our experience, with the caveat that we don't include the blob in the search details table HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with "Order By" (phpMyAdmin)
I'm working on an add/edit form, illustrated by the screehnshot at http://www.geoworld.org/addedit2.gif I decided to arrange the rows by ID, rather than alphabetically. So I opened the table in phpMyAdmin, clicked Operations, then changed "Order by" from a field named SCode to ID. When I clicked through, it defaulted to SCode. Tips? __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error restoring and dumping
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 27 May 2004 02:56 pm, Victoria Reznichenko wrote: > OPTION is a reserved word in MySQL: > http://dev.mysql.com/doc/mysql/en/Reserved_words.html > > Use backticks to quote column names: `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 Thanks Victoria, Yes, when I looked into the original mambo's sql, it was backquoted. And also, my problem was solved by using mysqlhotcopy, so I was by-passing the dumping and restoring process, thus the syntax problem. Thanks again. - -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org 15:51:05 up 6:16, Mandrake Linux release 9.2 (FiveStar) for i586 public key: https://www.arinet.org/fajar-pub.key -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAtayykp5CsIXuxqURAleMAJ9kMpq9DbN5MCmh206tZtfk0qOLIQCgtF+b iiXb2cjfTA64VNeaXWa0DZo= =whxM -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could not start MySQL after reinstall - Can't open privilege tabl es: Table 'mysql.host' doesn't exist
Check permissions on the mysql\data directory and files/folders below for the MySQL process/user (sorry for not knowing the right Windows terminology, I'm not a Windows user). HTH, Robert J Taylor [EMAIL PROTECTED] michael_wu[§d§»¹F] wrote: Hello, I run into a problem after re-installing mySQL 4.1.1 today. When I tried to start the service on my Windows2K, I got the following error: 040527 15:37:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Can some one tell me how to solve the problem? Thanks in advance! Michael Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013
Mauricio Pellegrini <[EMAIL PROTECTED]> wrote: > I'm using MySql 4.0.18 on Linux (SuSE 8.2) > and I'm getting this error > > ERROR 2013: Lost connection to MySQL server during query > > a few seconds after launching > the following command > > LOAD DATA LOCAL INFILE '/root/hc.txt' into table af_afiliados ; > > soon after that I repeat the command > > mysql> load data local infile "/root/hc.txt" into table > af_afiliados; > > and the error seems to be slightly different > >ERROR 2006: MySQL server has gone away >No connection. Trying to reconnect... >Connection id:6 >Current database: hrrg > >ERROR 2013: Lost connection to MySQL server during query > > Could ERROR 2013 be generated by some inconsistency in the data stored > in "/root/hc.txt" file ? (this is a ASCII file,Which has been working > perfectly till now, nonetheless there had been some changes into its > data) > Check that it's not max_allowed_packet or wait_timeout issue: http://dev.mysql.com/doc/mysql/en/Gone_away.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]
Problems with "Order By" (phpMyAdmin)
I'm working on an add/edit form, illustrated by the screehnshot at http://www.geoworld.org/addedit2.gif I decided to arrange the rows by ID, rather than alphabetically. So I opened the table in phpMyAdmin, clicked Operations, then changed "Order by" from a field named SCode to ID. When I clicked through, it defaulted to SCode. Tips? __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
There is one instance in which it is *not* convenient to store in seperate files: when you are exporting to another machine (maybe a sub set of data from an internal server to an external web server) or doing replication. j- k- On Wednesday 19 May 2004 01:01 pm, Greg Willits said something like: > On May 19, 2004, at 1:19 PM, David Blomstrom wrote: > > I'd like to get some feedback on storing images in > > MySQL databases. The stuff I've read so far suggests > > that it's fairly difficult to work with images in > > MySQL, and they also slow down databases. > > > > I've also read that there isn't much you can do with > > BLOB's that you can't do with PHP manipulating images > > stored in an ordinary folder. > > > > So I just wondered if BLOB's are worth my time. For > > example, I'm working on a database with information > > about the 50 states. If I have maps of each state, > > pictures of each state's capital, etc., is there some > > BLOB feature that I would find really useful? > > All "conventional wisdom" I've ever come across for this type of > application is that there's no advantage to keeping the image in the db > itself. Just keep them as files on the server, store a filename &/or > location in the db if necessary, and use your middleware to display the > images. Its faster, easier to maintain, and easier to backup. IMO, > storing images in the db just bloats the file and complicates all the > backup issues. > > -- greg willits -- 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]
Re: Changing Password on Latest MySQL, etc
Carlos Sunden <[EMAIL PROTECTED]> wrote: > > Installed the MySQL-server-4.0.20-0.i386.rpm on a RHL8 system > > Initialized the grant tables and then did: > /usr/bin/mysqladmin -u rt password ACTUALPASSWORD > And got: > -bash: /usr/bin/mysqladmin: No such file or directory > > What gives mates? > I had used the exact command before in another RHL8 system but it was a tar.gz > package, I think. > > How can I changed or apply a password to mysql? > > Also, are there any other post-installation things to do after a Mysql installation? > Newbie here. I'd like to know how to make Mysql secure & more efficient. > Install MySQL-client package. -- 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]
Could not start MySQL after reinstall - Can't open privilege tabl es: Table 'mysql.host' doesn't exist
Hello, I run into a problem after re-installing mySQL 4.1.1 today. When I tried to start the service on my Windows2K, I got the following error: 040527 15:37:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist Can some one tell me how to solve the problem? Thanks in advance! Michael Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, > On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote: > > Hi, I am trying to use the foreign key constraints from InnoDB > > and creating indexes is a requirement for foreign key. > > The problem is that by creating index for my foreign key, > > it does not allow my foreign key to have null or blank values which my > > records will have. For eg. a BorrowerID is a foreign key on a Book table, > > but when the book is not borrowed, the BorrowerID will be null and I can't > > seem to import the data containing null values for the foreign key. Is > > there a way to solve this? > > Thanks. > > I am not to sure how its possible to fix it.. Thinking about this.. I would > have a bookid which is never null. Considering no matter if the book is out > or not, you have that book. I then would have a borrow table, lets say, as > the foreign key would be bookid.. I date borrowed, and date returned would be > how I would know if its out or not.. > > Just thinking off the top of my head as there probably is a better way to do > it. > > I am sure there are other ways to do this.. Foreign keys can't be null. Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error restoring and dumping
Fajar Priyanto <[EMAIL PROTECTED]> wrote: > > Hi all, > I'm trying to move my Mambo (content management) database from one server into > another. > > In the old server, the mysql version is 4.0.13, while in the new one it's > 4.0.15. > > The error was when restoring: > ERROR 1064 at line 141: 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 'option varchar(50) DEFAULT '' NOT NULL, > ordering int(11) unsi > > These are the corresponding lines: > > # > # Table structure for table `mos_components` > # > > CREATE TABLE mos_components ( > id int(11) NOT NULL auto_increment, > name varchar(50) NOT NULL default '', > link varchar(255) NOT NULL default '', > menuid int(11) unsigned NOT NULL default '0', > parent int(11) unsigned NOT NULL default '0', > admin_menu_link varchar(255) NOT NULL default '', > admin_menu_alt varchar(255) NOT NULL default '', > option varchar(50) NOT NULL default '', > ordering int(11) unsigned NOT NULL default '0', > admin_menu_img varchar(255) NOT NULL default '', > iscore tinyint(4) NOT NULL default '0', > PRIMARY KEY (id) > ) TYPE=3DMyISAM; > > Is there any syntax that I should fix? OPTION is a reserved word in MySQL: http://dev.mysql.com/doc/mysql/en/Reserved_words.html Use backticks to quote column names: `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]
Re: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. If you have a foreign key constraint that requires the foreign key field to be populated then you effectively have a "MANY TO ONE" with Min of 1 and Max of 1 relationship between the book table and the borrower table -- thus you cannot have a book without a borrower. That is a logical problem, and the one you are describing. Separate the BorrowerID from table `book`. Make a table called, oh, `book_borrower` and put BookID BorrowerID DateOut DateDue DateReturned ... Then you can query for borrowed books using a join like: SELECT a.BookID, b.BorrowerID, c.FullName from ( book a inner join book_borrower b on a.BookID = b.BookID ) inner join borrower c on b.BorrowerID = c.BorrowerID WHERE b.DateReturned is null Available books could be found: SELECT a.BookID from book a left join book_borrower b on a.BookID=b.BookID WHERE b.BookID is null and b.DateReturned is null (Which says show me all the book.BookID that fail to have a non-returned book in the book_borrower table. Usually I put the "and b.DateReturned is null" with the join statement, instead of the WHERE clause.) Now you can have a book without a borrower and can easily track borrowing history for books and borrowers. Be sure to index the fields you'll be using as selection criteria! HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimization
"Abdul Aziz" <[EMAIL PROTECTED]> wrote: > > How can we optimize MySQL queries, plz define easy method > comparing as well as better performance for data retrieval. > The following chapter of the manual will be helful to you: http://dev.mysql.com/doc/mysql/en/Query_Speed.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: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. I think you have missed the point of a foreign key constraint. It is to ensure referential integrity. A constraint stops you doing things, ie entering a value that does not exist in another table. Or create a borrower called NO-ONE and always change the borrower to this when a book is returned. Or just use an outer join on an indexed field when doing reports is easiest and no constraint. Colin Bull -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimization
try using explain > > > Hi All, > > How can we optimize MySQL queries, plz define easy method > comparing as well as better performance for data retrieval. > > Regards: > aaziz > > > > > -- > 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]
