newbie desperately seeking clues
Folks, I am EW to the list, and MYsql. I am totally adrift, and looking for some good reading for a raw beginner. PLEASE post some linke for my use. I remain, Kirk Bailey think - | THE BOX | - kniht Defend free speech on the internet. http://www.eff.org/ ! THE WORST THING ABOUT CENSORSHIP IS THA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: book advice
Thanks all for the advice. It seems that no-one is recommending the book I mentioned. I've now got Paul DuBois' book (unfortunately, it's the second edition, but hope that's still okay). Looks rather large! But it also looks very clear. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
book advice
Hi, I'm a newbie and looking for a book to help me learn mysql. I have come across a book called Beginning MySQL by Robert Sheldon and Geoff Moes. Can anyone recommend this book? Or, if not, what book can you recommend for a newbie. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1043 Bad handshake [In MySQL Administrator]
I have just installed MySQL Administrator on FreeBSD 5.2.1. I am trying to use the Connect to MySQL Server Instance
dialog to establish a connection to a version: 4.0.22 MySQL server. I can establish a remote connection using:
mysql -h hostname -p -u username databasename
However, using MySQL Administrator, I get the following error:
Could not connect to host 'hostname'.
MySQL Error Nr. 1043
Bad handshake
I have UPDATED the database password as suggested using the OLD_PASSWORD()
function. Still not able to establish a connection.
Any suggestions?
Thanks for your time,
cb
An answer to my own question. I have solved this problem which seems to be
due to the way
mysql 4.1.7 stores it's passwords. I used the mysql function OLD_PASSWORD to
make mysql
store it's passwords in the pre 4.1.x method. Basically the syntax is:
mysql UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host =
'some_host'
AND User = 'some_user';
mysql FLUSH PRIVILEGES;
Now I seem to be able to connect.
--ja
On Wed, 17 Nov 2004, Gleb Paharenko wrote:
Hello.
Did you use mysql client program from 4.1.7 installation? When you use
it from 4.1.0 or older (on another machine) there some differences in
authentication handshake.
jabbott@/stripped/ wrote:
I have done some looking in the archive for this but can't seem to find anything
recent that seems to apply.
I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with:
CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro
-felide-constructors -fno-exceptions -fno-rtti ./configure --prefix /usr/local/mysql
--enable-assembler --with-mysqld-ldflags=-all-static --datadir /web/data
It starts just fine. This is a new install and I haven't put any data in it so
I don't think that I need to do any sort of permission fixing (Isn't that just for
migrated data?) I can connect to it via local host just fine but if I try to connect from
another machine running 4.1.? I get:
bash-2.05$ mysql landfill -u root -p -h 192.168.1.100
Enter password:
ERROR 1043: Bad handshake
bash-2.05$
Any idea what I am doing wrong?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2 regression failure: error reading communicaton packets
Description: I have just built 4.1.2 from source (in order to include OpenSSL), and find that the regression testing fails on the first test. The master.err contains the following: CURRENT_TEST: alias /usr/local/src/db/mysql-4.1.2-alpha/sql/mysqld: ready for connections. Version: '4.1.2-alpha-debug-log' socket: '/usr/local/src/db/mysql-4.1.2-alpha/mysql-test/var/tmp/master.sock' port: 9306 040607 18:25:01 Aborted connection 1 to db: 'test' user: 'root' host: `localhost' (Got an error reading communication packets) 040607 18:25:01 /usr/local/src/db/mysql-4.1.2-alpha/sql/mysqld: Normal shutdown 040607 18:25:01 /usr/local/src/db/mysql-4.1.2-alpha/sql/mysqld: Shutdown Complete User time 0.00, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 116, Physical pagefaults 604, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 How-To-Repeat: make test Fix: Unknown at this point. Submitter-Id: submitter ID Originator:Charles Bailey Organization: Children's Hospital of Philadelphia/University of Pennsylvania MySQL support: none Synopsis: communication error in regression testing Severity: critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.2-alpha (Source distribution) Server: /usr/local/src/db/mysql-4.1.2-alpha/client/.libs/lt-mysqladmin Ver 8.40 Distrib 4.1.2-alpha, for intel-linux on i686 Server version 4.1.2-alpha-debug-log C compiler:gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) C++ compiler: gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Environment: System: Linux localhost 2.4.20-30.9smp #1 SMP Wed Feb 4 20:27:46 EST 2004 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O3' CXX='gcc' CXXFLAGS='-O3 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 3 2003 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1561228 Nov 12 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2332200 Nov 12 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Nov 12 2003 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Apr 26 2003 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--with-mysqld-user=mysql' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--without-bench' '--without-berkeley-db' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-libwrap' '--with-vio' '--with-openssl' '--with-openssl-includes=/usr/include' '--with-openssl-libs=/usr/lib' '--with-debug' '--enable-thread-safe-client' 'CFLAGS=-O3' 'CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wierd error message from mysqld
Hello, Im running mysql on a Freebsd 4.9.. In going through my logs this morning I saw this error. I find it odd that mysqd would try to open the /etc/hosts.allow file .. this makes me think that someone issued the command through mysql .with that in mind. 1. Is there a way to get mysqld to log to a specific file ? 2. Does the below error mean that someone tried to access these files via mysql commands ? Feb 25 13:44:14 xxx mysqld[175]: warning: /etc/hosts.allow, line 59: cannot open /etc/hosts.allow: Permission denied Feb 25 13:44:14 xxx mysqld[175]: warning: /etc/hosts.allow, line 59: cannot open /etc/hosts.deny: Permission denied Brent Bailey CCNA High Speed Data Services MetroCast Cablevision 603-332-8629 ext:242 [EMAIL PROTECTED]
Why Doesn;t this Date Query Work?
Hello All, I have a very strange problem which makes no sense to me whatsoever. I am trying to find the number of queries that the people have dealt with in a database between two dates. The dates are generated from some drop down boxes and dropped into a variable. This is the code for the query. $sql = 'SELECT Username, Count( Username ) FROM MainDetails, Usernames WHERE MainDetails.UserID = Usernames.UserID and Date Between \'$Date1\' and \'$Date2\' GROUP BY Username'; The strange thing is if I replace the $Date2 variable with a date manually it works -. i.e. $sql = 'SELECT Username, Count( Username ) FROM MainDetails, Usernames WHERE MainDetails.UserID = Usernames.UserID and Date Between \'$Date1\' and \'2004-01-01\' GROUP BY Username'; This would indicate that the variable is wrong but it is not. The code to generate the variable is exactly the same as $Date1 further more it echos on screen and is creating the date in the format as entered in the manual version? Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why Doesn;t this Date Query Work?
Fantastic! This was indeed the problem and has solved it. Many thanks! On Tue, 24 Feb 2004 13:14:51 +0100, Jigal van Hemert wrote: $sql = 'SELECT Username, Count( Username ) FROM MainDetails, Usernames WHERE MainDetails.UserID = Usernames.UserID and Date Between \'$Date1\' and \'$Date2\' GROUP BY Username'; The strange thing is if I replace the $Date2 variable with a date manually it works -. i.e. Not a MySQL issue, but an error in your PHP code. Single quotes do NOT replace variables with their values, so if you use double quotes: $sql = SELECT Username, Count( Username ) FROM MainDetails, Usernames WHERE MainDetails.UserID = Usernames.UserID and Date Between '$Date1' and '$Date2' GROUP BY Username; it will work. Regards, Jigal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help. MySQL Error.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I am currently getting the following error on one of the mysql servers im looking after and wonder if anybody knows what specifically it relates to. Error in accept: Too many open files Im currently running MySQL version '4.0.14' under FreeBSD 5.1 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/tNDpzSfrYDJMXmERAvKBAKC6vY0PnowjAaI8sRIIu+Mumeum8gCfVWAH hRU4PeRdpbIGgWPI9/xWVJY= =wHd+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Someone PLEASE Help Me!!! This should be really easy for you folks!
I desperately need everyone's help. I normally build database driven websites on NT Servers with an MS Access database, using perl 5 to read/write the database. Now I need to build a website that can handle a database with almost a gigabyte worth of images to catalog (no it's not porn). Ideally I would like to use the same method BUT the only hosts i can find that will offer that much disk space only use Unix and MySQL. I, However, have never used MySQL. I have used sql statements in the perl script to read/write the MS Access database, but i have never used MySQL. I need someone to tell me would I have to buy some software to build a MySQL database? Is there a free download that i can use to build the database? Where would I find it to download/purchase it? Is there an easy example of what statements I could use from a perl script to add, delete and read a single entry or a whole range of entries from the database? Can someone please help me? I'm desperate! Alternatively, if someone knows of a host that offers that much space and MS Access capatibility for around $10 a month that would certainly work too. Thank you everyone for your help and time in this. Sincerely, Mike Tiny Bailey Author of What Have We Done? The Dragon Chronicles
Using LOAD DATA IN FILE or SOURCE with MyODBC.
Hello all, I am thinking about produceing a small app that will use MyODBC on a windows machine to connect to a MySQL server on a remote FreeBSD machine. I already have something that will open an MS Access database and output either a MySQL syntax or CSV file and before i start adding an 'export directly to MySQL server' option want to know if it is possiable to issue the 'LOAD DATA IN FILE' or 'SOURCE' SQL commands to export the data from the Windows machine to the FreeBSD machine. -- Regards, William Bailey. Pro-Net Internet Services Ltd. http://www.pro-net.co.uk http://wb.pro-net.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Text field question
Could anyone please tell me how I write an SQL statement to seperate text from one field into multiple fields based on a aprticular character (i.e. tab/comma/space). For example, I have a text field containing: Text=Robyn Bailey Brisbane I want to seperate this (per query) into 3 fields so that I can sort and count the fields: Name=Robyn Surname=Bailey Location=Brisbane I dont want it permanently just per query (for a report). Thanks in advance Robyn Bailey, CISSP This email and any attachments are subject to copyright. They may also contain confidential information. This email and any attachments may not be distributed, reproduced, copied, stored or transmitted in any form or by any means, without the prior written consent of Bridge Point Communications Pty Ltd ABN 29 083 424 668. Any personal information in this email must be handled in accordance with the Privacy Act 1988 (Cth). Emails may be interfered with, may contain computer viruses or other defects and may not be successfully replicated on other systems. Bridge Point Communications Pty Ltd gives no warranties in relation to these matters. If you have any doubts about the authenticity of an email purportedly sent by us, please contact Bridge Point Communications Pty Ltd immediately. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hiding the password
i would try using php to have you page connect to the mysql database.. The code gets parsed first then is loaded into the browser...so the user pass for the database is never seen.. i would use something like: $db = mysql_connect(localhost, mysql-user, mysql-user-password); mysql_select_db(whatever-database-name,$db); Brent Benjamin Pflugmann wrote: Hello. On Wed 2002-12-25 at 13:15:58 +0200, [EMAIL PROTECTED] wrote: Hi all, I want to make a CGI program in Perl that queries a MySQL database, and the problem is that I need to write the password for the database in the program and this password can be seen by any user that has an account on that server. I need to gave 755 permissions to CGI scripts because they need to be executed by the web server account, and not by my account. Do you have any tips for hiding the password, Not really. Whereever you put it, the web server account has be able to access it, so the problem stays. Even if you could arrange that only the web server account can read it (e.g. by changing the owner of a file containing the password), every user with permission to create CGI scripts can still write a script to read the data. or accessing MySQL from CGI scripts is not secure at all? Well, it is as secure as the server is set up. E.g. one can set up Apache so that it executes CGIs as the user to whom the script belongs. I know this has its own problems... it was only intended as example that it is a question of the server configuration. The best way is always a compromise and depends on how the server is used. If the server configuration is not in your hands, I don't there is much you can do, except asking the admin which way she suggests. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Bailey CCNA High Speed Data Services MetroCast Cablevision 603-332-8629 ext:242 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
user password issue help ?!?
Hello ... my name is Brent ..and im using the latest version of mysql on a Freebsd box with apache php3 php4. My problem is that ive set a password for the root mysql user by doing: /usr/local/bin/mysqladmin -u root password 'x' then i installed phpMyAdmin ..and set the correct username password within phpMyAdmin's config file ..and it connects to mysql with no problems. However ..i wrote a form with php3 html ...to connect using the same username password and it will not connect. I know that the form is correct in its code...in that ...i moved the form to another server (running the same versions of mysql such) and it connected with no issues. Ive seen this problem on other servers that i have administerd in the past and have not been able to get it to work correctly without totally uninstalling and re-installing mysql. When i set a password in the form for root@localhost to connect to mysql i get the following error: Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: YES) in /usr/local/www/data/index.php3 on line 25 Warning: 0 is not a MySQL link index in /usr/local/www/data/index.php3 on line 26 to get around this issue temporarily i have set the root@localhost user with NO password ( i hate doing this as i know its not secure) Id rather not run mysql with skip grant tables ... whenever i add a user password to mysql and then set a form to use that UN PW ...it will not connect however if i take that user out and just use a root user with NO password ..it will work..can anyone give me a clue as to what i can do ..?? Any and ALL help is greatly appreciated.. thank you Brent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
username password issues with mysql
Hello ... my name is Brent ..and im using the latest version of mysql on a Freebsd box with apache php3 php4. My problem is that ive set a password for the root mysql user by doing: /usr/local/bin/mysqladmin -u root password 'x' then i installed phpMyAdmin ..and set the correct username password within phpMyAdmin's config file ..and it connects to mysql with no problems. However ..i wrote a form with php3 html ...to connect using the same username password and it will not connect. I know that the form is correct in its code...in that ...i moved the form to another server (running the same versions of mysql such) and it connected with no issues. Ive seen this problem on other servers that i have administerd in the past and have not been able to get it to work correctly without totally uninstalling and re-installing mysql. When i set a password in the form for root@localhost to connect to mysql i get the following error: Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: YES) in /usr/local/www/data/index.php3 on line 25 Warning: 0 is not a MySQL link index in /usr/local/www/data/index.php3 on line 26 to get around this issue temporarily i have set the root@localhost user with NO password ( i hate doing this as i know its not secure) Id rather not run mysql with skip grant tables ... whenever i add a user password to mysql and then set a form to use that UN PW ...it will not connect however if i take that user out and just use a root user with NO password ..it will work..can anyone give me a clue as to what i can do ..?? Any and ALL help is greatly appreciated.. thank you Brent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.0.5 mysqld-max segs on TCP connect under RH 8.0
Description: Test mysql 4.0.5 beta installed from binary RPMs: MySQL-4.0.5-0, MySQL-shared-4.0.5-0, MySQL-devel-4.0.5-0, MySQL-client-4.0.5-0, MySQL-Max-4.0.5-0 mysqld-max starts normally (including binding TCP socket) and handles connections via Unix socket normally, but segs when connection made from another machine via TCP. Quoth the server (my wrap): /usr/bin/mysqld_safe: line 312: 13509 Segmentation fault \ $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION \ --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking \ $err_log 21 with little info in the log (not surprisingly): /usr/sbin/mysqld-max: ready for connections Number of processes running now: 1 mysqld-max process hanging, pid 13512 - killed 021203 13:49:00 mysqld restarted /usr/sbin/mysqld-max: ready for connections Non-max mysqld runs fine so far. Quick ldd doesn't show up any obvious problems (though I do wonder a bit about the i686 libs, if the binary was linked against i386 equivalents): librt.so.1 = /lib/librt.so.1 (0x4002b000) libdl.so.2 = /lib/libdl.so.2 (0x4003d000) libpthread.so.0 = /lib/i686/libpthread.so.0 (0x4004) libz.so.1 = /usr/lib/libz.so.1 (0x4007) libcrypt.so.1 = /lib/libcrypt.so.1 (0x4007e000) libnsl.so.1 = /lib/libnsl.so.1 (0x400ab000) libm.so.6 = /lib/i686/libm.so.6 (0x400c) libc.so.6 = /lib/i686/libc.so.6 (0x4200) libnss_files.so.2 = /lib/libnss_files.so.2 (0x400e3000) libnss_dns.so.2 = /lib/libnss_dns.so.2 (0x400ed000) libresolv.so.2 = /lib/libresolv.so.2 (0x400f1000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x4000) Will work on building from source to see whether error is reproduced. How-To-Repeat: Start mysqld-max. Connect from client. Fix: Don't run mysqld-max. Submitter-Id: submitter ID Originator:Charles Bailey Organization: MySQL support: none Synopsis: beta mysqld-max segs under RH 8.0 i686 Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.5a-beta (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.38 Distrib 4.0.5a-beta, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.5a-beta-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysql.sock Uptime: 5 min 49 sec Threads: 1 Questions: 68 Slow queries: 0 Opens: 68 Flush tables: 1 Open tables: 62 Queries per second avg: 0.195 Environment: System: Linux xxx.xxx.xxx.edu 2.4.18-17.8.0smp #1 SMP Tue Oct 8 10:52:32 EDT 2002 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 6 14:04 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 19:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 18:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 18:50 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Nov 6 14:26 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc
Constraint HELL
sql, query I understand I must use InnoDB tables if I plan to use constraints. If I: CREATE TABLE TEMP_TEST(A CHAR(2),B CHAR(2)) TYPE = InnoDB; Well the test table gets created, but it is, according to SHOW TABLE STATUS, a MyISAM table. How can I set Type to InnoDB and make it stick. And if it is true that InnoDB tables can contain only 1000 or fewer rows, what's the use. I could do that with a text file. Jim Bailey _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Constraint Hell
sql, Query
Hark ye experts!
Here I stand an expert grunt.
Trying to understand,
The syntax of constraint.
I was directed to the constraint doc.
And found the constraint doc doth suck.
Here's my ploy, renewed, refined.
Please see if you can help.
To create my Foreign table I used the following script
CREATE TABLE IIM_InventoryItemMaster(
IIM_InventoryItemMaster_ID MEDIUMINT(8) NOT NULL,
IIM_ItemNm VARCHAR(64),
IIM_ItemDescCD VARCHAR(1) DEFAULT 'U',
IIM_GenderCD VARCHAR(1),
IIM_ItemPN VARCHAR(36),
IIM_Spec1Nm VARCHAR(48),
IIM_Spec2Nm VARCHAR(48),
IIM_RecordCreatedBY VARCHAR(24),
IIM_RecordCreatedDT DATETIME,
IIM_LastEditBY VARCHAR(24),
IIM_LastEditDT DATETIME,
PRIMARY KEY(IIM_InventoryItemMaster_ID),
KEY(IIM_GenderCD),
KEY(IIM_ITEMNm),
KEY(IIM_ItemDescCD),
key(IIM_ItemPN)
)
Type = INNoDB;
Then SHOW CREATE TABLE Told me the table type
is MyISAM. Don't I need InnoDB to use Constraints?
Does INNoDB cause it woe?
===
Now for some clear and Poignant questions about MySQL hell?
===
From the following URL:http://www.mysql.com/doc/en/CREATE_TABLE.html
I found Constraint docs
===
[CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH
FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE
reference_option]
===
Question I.
If there is a Foreign Key in a Foreign Table, then there must be a Native
key in a Native Table.
What is the correct technical name of the {Native} Field?
Question II
I understand CONSTRAINT symbol to be the name of the constraint.
true or false?
Then there is FOREIGN KEY [index_name]
What Index_name, The name of the Foreign Key field's Index
(located in the Foreign Table?)
So this is the name of an index in the Foreign Table?
Question III.
Then comes (index_col_name,...)
I guess this means the name of the Foreign Key Field(s)?
true or false;
Question IV.
Next comes REFERENCES tbl_name. [(index_col_name,...)]
The name of the Native Table or he Fireign Table?
[(index_col_name)], This must mean the name of the Foreign Key Field(s)
AGAIN?
true or false
Question V.
I suppose I should include the Constraint syntax in the SQL that creates the
Native Table and not in the SQL that creats the Foreign Table?
True or false?
Thanks for your help
Jim Bailey
_
Send and receive Hotmail on your mobile device: http://mobile.msn.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Constraint Confusion
sql,query Following are two CREATE TABLE statements. The second one fails because of an error in the last line. Does anyone know why? Jim Bailey STATEMENT #1 CREATE TABLE IIM_InventoryItemMaster( IIM_InventoryItemMaster_ID MEDIUMINT(8) NOT NULL, IIM_ItemNm VARCHAR(64), IIM_ItemDescCD VARCHAR(1) DEFAULT 'U', IIM_GenderCD VARCHAR(1), IIM_ItemPN VARCHAR(36), IIM_Spec1Nm VARCHAR(48), IIM_Spec2Nm VARCHAR(48), IIM_RecordCreatedBY VARCHAR(24), IIM_RecordCreatedDT DATETIME, IIM_LastEditBY VARCHAR(24), IIM_LastEditDT DATETIME, PRIMARY KEY(IIM_InventoryItemMaster_ID), KEY(IIM_GenderCD), KEY(IIM_ITEMNm), KEY(IIM_ItemDescCD), key(IIM_ItemPN) ); STATEMENT #2 CREATE TABLE IID_InventoryItemDesc( IID_ItemDescCD VARCHAR(1) NOT NULL, IID_ItemDesc VARCHAR(24), IID_RecordCreatedBY VARCHAR(24), IID_RecordCreatedDT DATETIME, IID_LastEditBY VARCHAR(24), IID_LastEditDT DATETIME, PRIMARY KEY(IID_ItemDescCD), KEY (IID_ItemDesc), CONSTRAINT IIM_TO_IID FOREIGN KEY IIM_InventoryItemMaster (IIM_ItemDescCD) REFERENCES IIM_InventoryItemDesc( Primary key) MATCH FULL); _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
source command
At the prompt mysql I type a command : SOURCE SCRIPTS\MT_INV_TBL.TXT. The command returns the following error message: Failed to open file [FileName] Error 2. So what is this error 2. What does it mean? Your help will be appreciated. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Joining tables using IN (...) clause returns duplicate rows
I guess I'm a little confused about the MySQL versions. What is the real production version? If 4.0.2 can be called a production version, I'd gladly use it on my web site; however, it doesn't seem to indicate that on the MySQL home page. So if I find a bug in 3.23.47 that was fixed a long time ago as Mr. Milivojevic states, wouldn't I expect it to find its way into the 3.23.X series as the production series? I really like MySQL and don't mean this as any type of a flame, just wondering what the versions mean. Thanks for any insight anyone can shed on this for me Greg Bailey Sinisa Milivojevic wrote: Greg Bailey writes: Description: Joining multiple tables together in a select statement with where clauses using the IN (...) construct generate duplicated output rows. Hi! I tested your case with 4.0.2 and got identical results from both queries. This was some bug probably fixed long time ago. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Joining tables returns duplicated rows
Description: Joining multiple tables together in a select statement with where clauses using the IN (...) construct generate duplicated output rows. How-To-Repeat: 1. ==Load the following into the test_bug database: # MySQL dump 8.14 # # Host: localhostDatabase: test_bug # # Server version3.23.41 # # Table structure for table 'event' # CREATE TABLE event ( id int(11) NOT NULL auto_increment, status int(11) NOT NULL default '0', severity int(11) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY lens_event_0 (status,severity,id) ) TYPE=MyISAM; # # Dumping data for table 'event' # INSERT INTO event VALUES (1,0,2); INSERT INTO event VALUES (2,0,2); INSERT INTO event VALUES (3,0,3); INSERT INTO event VALUES (4,0,3); INSERT INTO event VALUES (5,1,2); INSERT INTO event VALUES (6,2,3); # # Table structure for table 'severity' # CREATE TABLE severity ( severity int(11) NOT NULL default '0', description text ) TYPE=MyISAM; # # Dumping data for table 'severity' # INSERT INTO severity VALUES (0,'Unknown'); INSERT INTO severity VALUES (1,'Alert'); INSERT INTO severity VALUES (2,'Critical'); INSERT INTO severity VALUES (3,'Error'); INSERT INTO severity VALUES (4,'Warning'); INSERT INTO severity VALUES (5,'Notification'); INSERT INTO severity VALUES (6,'Informational'); INSERT INTO severity VALUES (7,'Debugging'); # # Table structure for table 'status' # CREATE TABLE status ( status int(11) NOT NULL default '0', description text ) TYPE=MyISAM; # # Dumping data for table 'status' # INSERT INTO status VALUES (0,'New'); INSERT INTO status VALUES (1,'Acknowledged'); INSERT INTO status VALUES (2,'Working'); INSERT INTO status VALUES (3,'Resolved'); INSERT INTO status VALUES (4,'Closed'); 2. ==Execute the query query_normal.sql which produces the expected output: (all 6 rows of event are returned) select * from event, status, severity where event.status = status.status and event.severity = severity.severity; yields a result set of 6 rows 3. ==Execute the query query_failure.sql which produces the failed output: (Note the IN (...) clauses should have no effect on the output) select * from event, status, severity where event.status = status.status and event.severity = severity.severity and event.status in (0,1,2) and event.severity in (2,3); yields a result set of 30 rows, with duplicated IDs Fix: Submitter-Id: Greg Bailey [EMAIL PROTECTED] Originator:Greg Bailey Organization: LXPRO.COM MySQL support: none Synopsis: Joined tables return duplicate rows with IN (...) construct Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.41 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.41, for redhat-linux-gnu on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.41 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 21 hours 54 min 25 sec Threads: 1 Questions: 3409 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 9 Queries per second avg: 0.021 Environment: System: Linux arizona.i-link.net 2.4.9-21 #1 Thu Jan 17 14:16:30 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='c++' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 14 16:16 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x2 root root 1283964 Dec 8 07:14 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314296 Dec 8 07:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 8 07:02 /usr/lib/libc.so Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --without-debug --without-readline --enable-shared --with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all --disable-assember --with-berkeley-db --enable-large-files=yes --enable-largefile=yes
Too big for max_allowed_packet - service problem or JDBC driver problem?
I'm developing a Java application that uses a pool of JDBC connections to store and retrieve data in a MySQL database. I'm using the 3.23.38 Win32 version of MySQL, on Windows 2000 Professional, and the mm.mysql-2.0.4 JDBC driver. I'm using a table with a MEDIUMTEXT field, which the MySQL documentation says can handle up to 16.7 Megs of data. However, I've run into problems with this Exception being thrown (the [java] prefix is there because I'm running this from inside of an ant script): [java] java.sql.SQLException: Error during query: Unexpected Exception: java.lang.IllegalArgumentException message given: Packet is larger than max_allowed_packet from server configuration of 65536 bytes [java] at org.gjt.mm.mysql.Connection.execSQL(Connection.java:898) [java] at org.gjt.mm.mysql.Connection.execSQL(Connection.java:815) [java] at org.gjt.mm.mysql.Statement.executeUpdate(Statement.java:227) [java] at org.gjt.mm.mysql.jdbc2.Statement.executeUpdate(Statement.java:97) [java] at com.incellico.arrayex.data.MyClass.storeBigField(MyClass.java:xxx) I'm running mysql as a service on my machine, using the mysqld-max-nt executable. When I take a look at the variables, the output reports that the max_allowed_packet size is set to 1 Meg. I've tried explicitly setting max_allowed_packet in the C:\WINNT\my.ini file, by adding a [mysqld] section, and I've tried bumping up the available memory by adding a [safe_mysqld] section with ulimit -d 256000, as was suggested in the MySQL manual. Neither has worked. Could this be a problem with the mm.mysql JDBC driver itself? Peter Bailey (work) [EMAIL PROTECTED] (home) [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The Mysql socket thing using Redhat 7.1
Ed, Thanks for the email. I was a bit tired last night and was a little frustrated (4 nights getting nowhere), so apologies if I was little rude (I've just reread my email) and sarcastic, but thanks for the 'Details' pun. I've dug myself into a hole offering to set up a Linux/Apache Webserver for a friend. There's no rush but wanted to use it as a learning experience and am getting problems with mysql- everything else seems ok. jason bailey ([EMAIL PROTECTED]) writes: I'm consistently getting the error (2002) : Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (111) You mean /var/lib/mysql/mysql.sock? Details are IMPORANT. Yes, thanks for the pun. It was the above /var/lib/mysql/mysql.sock and I noted that deleting it and then running mysql_install_db or possible safe_mysqld recreated. I've deleted it and running something above has brought it back again!- this is good I think Bad idea. Slightly sarcastic- sorry 1. Can i found out if the server is or is not running -which command and what am I looking for? mysqladmin status As I remember and I'm not sure I was going round in circles- this was a call giving the error- I've just read somewhere to check that this is installed, so will do that tonight 2. Can I stop the mysql server mysqladmin shutdown Again an error on that one 3. Can I start it? safe_mysqld Yep it does produces a lot of information and then ends (I think- you see I'm not sure) Where is mysql.sock? In /tmp? If so: ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock mysql.sock is in the directory that the error produces i.e. /var/lib/mysql/mysql.sock ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock Should get you going. This, in my ignorance, looks like a soft link to the mysql.sock in the /var/lib/mysql/mysql.sock location. The file(?) does exist and I'm not sure why a softlink will help unless it's something to do with permissions. Thanks for your suggestions I'll give them a try. Regards Jason Dr Jason Bailey Desktop Applications Consultant Computing Service The University of Sussex - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The Mysql socket thing using Redhat 7.1
Ed, Once again, thanks for the email response. I'm going to try all the suggestions I've received. A colleague has suggested as I'm new to Linux to select the 'Install Everything' option in red hat 7.1. He suggests that it's probably easier (for me) to get everything installed and work backward (uninstall the unnecessary) as I learn more. If the server is creating mysql.sock in and something else is looking for it in /tmp, this is the easiest way to get it to work, besides checking to make sure that the permissions are such that anyone who needs to can read and write to mysql.sock. -- I think this may be my problem in that I couldn't understand whether mysql.sock should be located in /var/lib/mysql but is required to be in /tmp. It certainly appears in /var/lib/mysql I'll try your suggestion for this and check permissions. Well, it wasn't a pun :) Details are critically important, especially when you work with complex systems like these. I was commenting on the lack of detail in your post - My mistake about the Pun. I do agree details are important; I will use mysqlbug next time, if I get no joy. I'd found several, if not more, postings from several people with the same type of question, regarding the error I was getting. I'd assumed that this common problem was well understood and didn't require explicit detail from myself. you ran something and something happened, but you weren't quite sure what. It's much more difficult when people have to guess what you did. I'm not trying to flame you at all - but you will probably be able to get a lot more, and a lot higher quality, help if you can supply as much detail as possible. I would'nt blame you for flaming my first email was a little rude. The detail I will try for next time. I've deleted it and running something above has brought it back again!- this is good I think Bad idea. Slightly sarcastic- sorry No, this really *is* a bad idea! The mysql.sock file is created by the server - if you delete it, you will not be able to connect locally. I'd been sarcastic and was apologising. I realised it was a bad idea but was getting frustrated and wanted to see if it would be recreated as a test if I was running the server at all. On deleting it I was finding that it was being recreated when running something. Something- I wasn't sure about at the time either mysql_install_db or safe_mysqld - still not sure but will check tonight when I get home. Dr Jason Bailey Desktop Applications Consultant Computing Service The University of Sussex - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The Mysql socket thing using Redhat 7.1
HI, I'm a new user to Linux and am interested in MySQL and PHP. I currently use both as a developer elsewhere and am now trying to set up a test server at home. I've searched the MYSQL lists and notice that alot of people are getting a similar problem as myself and I'm not sure if any have got this to work. I'm consistently getting the error (2002) : Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (111) I've read through the manual about problems starting a server but this has not helped and I've tried to follow help in the lists but again no result. I run mysql_install_db and get the messages about starting up I run safe_mysqld and get something about starting and stopping I've found the the mysql.sock and it's in the location that the error mentions the location of the sock thing?? (It's late and I'm tired and a bit fed up -after being told to forget windows and go to Linux- I really wanted to agree but am now finding it hard too!) I've deleted it and running something above has brought it back again!- this is good I think I've uninstalled mysql and reinstalled the client, server etc but still the same error People keep responding about starting up or restarting the server. Someone has mentioned Services Mqsqld Start which does something but every single time I try mysql I get that error. I'm just going round in circles now. Can someone give me some help. 1. Can i found out if the server is or is not running -which command and what am I looking for? 2. Can I stop the mysql server 3. Can I start it? Using anything to do with mysql or mysqladmin results in the error. I'm currently reading about something to do with configure how ??? Please please please help Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Temporary Table problem
I'm using version 3.23.25, and am having trouble setting up a temporary table. I have the following permanent table already set up: bill_pay (pbid int (8) default '0' not null auto_increment, billdesc varchar(20), bpamount int(8), pbdate date, budgetid int(8), primary key (pbid), unique id (pbid)) I have tried to set up a temporary table: CREATE TEMPORARY TABLE tempbills ( billdesc varchar(20), bpamount int(8) ) SELECT billdesc, bpamount FROM bill_pay When I do this I get an error message saying "Duplicate column name "billdesc"". And if I change the column names on the temp table it still won't work - the table doesn't get created. If someone could help me out I'd be grateful! Matt _ Get your FREE download of MSN Explorer at http://explorer.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
