RE: permissions error for the slave server
has anyone got any idea why it wont let the slave connect properly ? here is my slave settings port= 3307 socket = /usr/local/etc/mysqlslave/tmp/mysql.sock master-host=localhost master-user=replication master-password=*** master-port=3306 master-connect-retry=60 report-host=localhost report-port=3307 server-id=2 -Original Message- From: Dan Rossi [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 7:55 PM To: [EMAIL PROTECTED] Subject: RE: permissions error for the slave server hi there my sql slave server cannot replicate as its getting permissions errors dont know what happened but its not happy now 030302 7:09:24 Slave I/O thread killed while connecting to master 030302 7:09:24 Slave I/O thread exiting, read up to log 'FIRST', position 4 030302 7:09:29 Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' errno: 1045 retry-time: 60 retries: 86400 i did this on the master GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '*'; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to Install MySQL 4.0
Dear SIr/Madam, I am using MySQL 3.23 for production and I am planning to upgrade to 4.0. before that I want to install 4.0 and do some RD on that before Upgrading. Please guide me which version to down load and how to down load for RedHat LINUX version 7.2 and splease send me steps to install. With the ReaHat Linux 7.2 by default MySQL 3.23 will come . If we install Mysql 3.23 with Linux , How to replace MySQL 4.0 with 3.23. Please help me on this. My Email Id Is: [EMAIL PROTECTED] Thanks Balaji. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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: How to Install MySQL 4.0
i'd download the lastest gamma release 4.0.11 ?? do a configure like so ./configure --prefix=/usr/local/etc/mysql --sysconfdir=/usr/local/etc/mysql --exec-prefix=/usr/local/etc/mysql --with-unix-socket-path=/usr/local/etc/my sql/tmp/mysql.sock --with-mysqld-user=mysql --with-innodb then make then make install , try a seperate directory to where the current mysql is -Original Message- From: Balaji Kolla [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 9:12 PM To: [EMAIL PROTECTED] Subject: How to Install MySQL 4.0 Dear SIr/Madam, I am using MySQL 3.23 for production and I am planning to upgrade to 4.0. before that I want to install 4.0 and do some RD on that before Upgrading. Please guide me which version to down load and how to down load for RedHat LINUX version 7.2 and splease send me steps to install. With the ReaHat Linux 7.2 by default MySQL 3.23 will come . If we install Mysql 3.23 with Linux , How to replace MySQL 4.0 with 3.23. Please help me on this. My Email Id Is: [EMAIL PROTECTED] Thanks Balaji. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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 - 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: permissions error for the slave server
a completely different error now i tried a different user and for some reason my grants are not working on the replication user 030303 8:43:30 Slave I/O thread: connected to master '[EMAIL PROTECTED]:330 6', replication started in log 'FIRST' at position 4 030303 8:43:30 Error reading packet from server: Binary log is not open (server_ errno=1236) 030303 8:43:30 Got fatal error 1236: 'Binary log is not open' from master when r eading data from binary log 030303 8:43:30 Slave I/O thread exiting, read up to log 'FIRST', position 4 -Original Message- From: Dan Rossi [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 7:33 PM To: Dan Rossi; [EMAIL PROTECTED] Subject: RE: permissions error for the slave server has anyone got any idea why it wont let the slave connect properly ? here is my slave settings port= 3307 socket = /usr/local/etc/mysqlslave/tmp/mysql.sock master-host=localhost master-user=replication master-password=*** master-port=3306 master-connect-retry=60 report-host=localhost report-port=3307 server-id=2 -Original Message- From: Dan Rossi [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 7:55 PM To: [EMAIL PROTECTED] Subject: RE: permissions error for the slave server hi there my sql slave server cannot replicate as its getting permissions errors dont know what happened but its not happy now 030302 7:09:24 Slave I/O thread killed while connecting to master 030302 7:09:24 Slave I/O thread exiting, read up to log 'FIRST', position 4 030302 7:09:29 Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' errno: 1045 retry-time: 60 retries: 86400 i did this on the master GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '*'; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can't find /tmp/mysql.sock
Hi, Well I had the same problem when I installed Mysql the first time. I solved this problem by using a symbolic link like this. cd /tmp ln -s /mysql/mysql.sock mysql.sock Remeber to be Root when you do a symbolic link :-) Now make a ls -lia in your /tmp folder, then you should have something similar to this: 228713 lrwxrwxrwx1 root root 17 feb 22 15:28 mysql.sock - /mysql/mysql.sock Now you should be able to start your Mysql database... :-) Regards Frank - Original Message - From: Ferrell-1, Eman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 28, 2003 10:14 PM Subject: can't find /tmp/mysql.sock Please help. I downloaded the binary distribution for mysql-3.23.55-pc-linux-i686 to run on redhat linux 8.0 I've done the following commands: shell cd /usr/local shell gunzip /downloads/mysql-3.23.55-pc-linux-i686.tar.gz | tar xvf - shell groupadd mysql shell useradd -g mysql mysql shell ln -s mysql-3.23.55-pc-linux-i686 mysql shell cd /usr/local/mysql shell scripts/mysql_install_db shell bin/safe_mysqld when I do this this is what I get: starting mysql with databases from /var/lib/mysql 030228 16:00:55 mysqld ended Also when I do the following: shell ./mysqladmin ping I get the error message: ./mysqladmin: connect to server at 'localhost' failed error: 'can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! When I ran: shell ./mysqlbug I got the following dialogue: Description: Getting the following error: ERROR 2002 can't connect to local MySQL server through socket /tmp/mysql.sock the sql daemon (mysqld) can't start. I think it's because it can't find the /tmp/mysql.sock file This file doesn't exist. It's not located anywhere on the system. Please tell me where to get this file. thanks. How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.55-max (Official MySQL-max binary) Environment: machine, os, target, libraries (multiple lines) System: Linux webserv.ksc.nasa.gov 2.4.18-24.8.0 #1 Fri Jan 31 06:51:30 EST 2003 i686 i686 i386 GNU/Linux 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/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='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Jan 2 10:31 /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 Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex' '--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-berkeley-db' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' So where do I get this /tmp/mysql.sock file? any help would be appreciated. thanks. Ema Ferrell Communications Services Branch TA-B2 321-861-2198 (Phone #) 321-232-5339 (Beeper #) email: [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 - 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
replication features
MySQL only supports one master and many slaves. In 4.x, we will add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce 'agent' processes to help do load balancing by sending select queries to different slaves. i dont want to sound pushy but shouldnt this have already been intergrated already ? its a pretty needed feature - 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: How to connect to mysql server without myODBC
There are some components named zeos library that can connect to a remote mysql server without myodbc. - Mensaje original - De: Liu Qianghua-qch1942 [EMAIL PROTECTED] Para: [EMAIL PROTECTED] Enviado: domingo, 02 de marzo de 2003 7:49 Asunto: How to connect to mysql server without myODBC All, I want to develop application with Delphi which can connect to remote mysql server. Whether I must install myODBC? If are there another way, please tell me? Thanks. Best Regards, Q.H. Liu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Scripting MySQL Commands
I'm doing all kinds of exercises and tutorials to get up to speed on MySQL on my PowerBook with OS X.2 running. I just tried an exercise that, for the first time, had me creating a table not right in the MySQL monitor, but, rather, via a script that I saved as a textfile--Temp.sql--and then call the script up from the command line in the MySQL monitor. Here's the script: CREATE DATABASE Temp; USE DATABASE Temp; CREATE TABLE Test_Table (Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Test_Name VARCHAR(30), Test_Date DATETIME, Test_Giver VARCHAR(30)); INSERT INTO Test_Table (Test_ID, Test_Name, Test_Date, Test_Giver) VALUES (NULL, 'Test','2000-03-02','Etienne'); The line I used to call the script up is: bin/mysql -p bin/mysql -p /[the complete pathname, honest]/Temp.sql I didn't get the expected result, receiving the following message: ERROR 1064: You have an error in your SQL syntax near 'bin/mysql -p /Users/stephent/Sites/Temp.sql' at line 1 Can anyone see what I've done wrong? Thank you. Steve Tiano mail2web - Check your email from the web at http://mail2web.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: Scripting MySQL Commands
Hy Steve, bin/mysql -p bin/mysql -p /[the complete pathname, honest]/Temp.sql try the following command : bin/mysql -p /[the complete pathname, honest]/Temp.sql HTH Oliver - 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: Scripting MySQL Commands
Oliver, Thanks for responding. Foolish of me to be in such a rush that I wasn't watching what I was copying and pasting. I actually did use the command you suggest below. And that's what drew the error message. So I'm back to the drawing board. Take care, and thanks again-- Steve Hy Steve, bin/mysql -p bin/mysql -p /[the complete pathname, honest]/Temp.sql try the following command : bin/mysql -p /[the complete pathname, honest]/Temp.sql HTH Oliver
Installatoin problem
I've just finished installing a MySQL rpm and when I went to run the script to setup a password (./mysql_setpermissions) this is the error: #./mysql_setpermission Password for user to connect to MySQL: install_driver(mysql) failed: Can't load './mysql' for module DBD::mysql: ./mysql: cannot dynamically load executable at /usr/lib/perl5/5.6.0/i386-linux/DynaLoader.pm line 200, STDIN line 1. at (eval 7) line 3 Compilation failed in require at (eval 7) line 3, STDIN line 1. Perhaps a required shared library or dll isn't installed where expected at ./mysql_setpermission line 65 I have installed the following: Data-Dumper-2.081-1 Msql-Mysql-DBI-perl-bin-1.1823-1 DBI-perl-bin-0.93-rh50.1 php-mysql-4.1.2-7.2.6 mysql-3.23.54a-3.72 any ideas on how to fix? or should I uninstall the entire lot? Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby - 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
mysql + mysqlcc
i have a problem when i use mysqlcc connect server Linux 7.3 ( Client Windows98se ) show massage Error 1130 Host 192.168.0.11 not allowed connect this Mysql Server _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail - 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: Scripting MySQL Commands
At 8:53 -0500 3/2/03, [EMAIL PROTECTED] wrote: I'm doing all kinds of exercises and tutorials to get up to speed on MySQL on my PowerBook with OS X.2 running. I just tried an exercise that, for the first time, had me creating a table not right in the MySQL monitor, but, rather, via a script that I saved as a textfile--Temp.sql--and then call the script up from the command line in the MySQL monitor. Here's the script: CREATE DATABASE Temp; USE DATABASE Temp; CREATE TABLE Test_Table (Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Test_Name VARCHAR(30), Test_Date DATETIME, Test_Giver VARCHAR(30)); INSERT INTO Test_Table (Test_ID, Test_Name, Test_Date, Test_Giver) VALUES (NULL, 'Test','2000-03-02','Etienne'); The line I used to call the script up is: bin/mysql -p bin/mysql -p /[the complete pathname, honest]/Temp.sql I didn't get the expected result, receiving the following message: ERROR 1064: You have an error in your SQL syntax near 'bin/mysql -p /Users/stephent/Sites/Temp.sql' at line 1 Can anyone see what I've done wrong? Type the command at the shell prompt, not after you've already invoked mysql. The error message is from mysql itself; it indicates that you typed a shell command to mysql. Thank you. Steve Tiano mail2web - Check your email from the web at http://mail2web.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: mysql + mysqlcc
You have to add a new user with hostip 192.168.0.11 With kind regards, Richard Pijnenburg Klik-on Internet Solutions -Original Message- From: Narin Cherdchoosat [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 3:38 PM To: [EMAIL PROTECTED] Subject: mysql + mysqlcc i have a problem when i use mysqlcc connect server Linux 7.3 ( Client Windows98se ) show massage Error 1130 Host 192.168.0.11 not allowed connect this Mysql Server _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL server thread keeps crashing?
Does anyone know why I'm getting this error? Does it have to do with character locale problems (notice the search.glyphs LIKE part)? This code used to work fine before, but lately it's been getting MySQL server has gone away errors every time it executes, so I'm confused. Breakpoint 2, main (argc=2, argv=0xbfffedf4) at search.c:65 65 query(mysql, query); (gdb) x/2s query 0xbfff2b80: SELECT lyrics.lnum, romaji, glyphs, type, shandle, lhandle, name, title FROM series_name, lyrics, series_lyric, series, search WHERE lyrics.lnum = series_lyric.lnum AND series.snum = series_lyric.snum... 0xbfff2c48: AND series_lyric.alias = 0 AND lyrics.lnum = search.lnum AND series_name.snum = series.snum AND series_name.alias = 0 AND search.glyphs LIKE '%\227 §\224\212\201%' ORDER BY type, name, title (gdb) next MySQL Error: MySQL server has gone away - 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: Scripting MySQL Commands
Paul, Thanks very much for responding. I tried what you suggested, but got: ERROR 1102: Incorrect database name '/Users/stephent/Sites/Temp.sql' I don't get this. What wrong database name? The script is supposed to create the database and a table in it. What an I too dense to see? Thanks again-- Steve Tiano Type the command at the shell prompt, not after you've already invoked mysql. The error message is from mysql itself; it indicates that you typed a shell command to mysql. - 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: Re: Scripting MySQL Commands
Hy Steve, USE DATABASE Temp; try to change this into : USE Temp; and try it again, this should work. HTH Oliver for the filter : sql,query,queries,smallint - 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
Growing list of open files...
Hi there @ the MySQL list, I'm currently using mysql-server-3.23.52-3 on RedHat Linux 8.0 with Apache httpd-2.0.40-8 After some problems with the httpd server, I found out that the list of open files spawn by mysqld had more than 20,000 entries, although the PID's from the related processes didn't turn up in `ps aux`. As of today, I found out that within my board software, YaBBSE, there was a bug, causing a hang in another function (getimagesize) while a request was still open. The downside was that even after the request was cancelled, the mysqld still kept the files open... Is there any way of defining a timeout, so that mysqld will really close all its open files and connections to the httpd? Thanks in advance for your support, /jochen webmaster of ejwsites.net - 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
Using SUM in a select statement.
Hi, Not sure how to explain this - but here is what I need. I have a hockey pool database with three tables - one has the players and their stats, one has the team owner, and the 3rd is the reference file that ties the two together. I want to be able to do a query for all the players on a particular team that are forwards and get a grand total of all their points. I already have a query that does something similar, but it just displays the individual players stats. Example: Team 1PlayerPosition GoalsPoints *Bob Bonk F 2250 HossaF3341 Total5591 This is what I want to achieve - and then the same for the rest of the teams. *This is my query that get the individual players points. $query = select team.name, player, position, gp, goals, ppg, gwg, shg, ass, pm, shots, pim, points from roster join reference join team where team.idn=reference.idn and reference.idp=roster.idp and team.idn = '$team' and position like '$position' order by points desc; TIA - 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
Scripting MySQL Commands
Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me incorrect database name. Well, yes, the script is to create a nonexistent database and then a table called Temp in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - 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
Update: mysqld compiled on Red Hat 8.0 crashes
Hi! Some new information about the MySQL-4.0.10 compilation problem on Red Hat 8.0 (x86), but unfortunately no solution found yet, except running a MySQL official binary you can download from www.mysql.com. A 4 day stress test of an official binary ran fine. One of MySQL AB employees said that on his Red Hat 8.0 computer mysqld sometimes crashes, but only if he compiles with -O3. But, in my 8.0 computer all versions, whether compiled with gcc-3.2 or gcc-2.95.3, with -O or -O3, always crash in the first SQL statement. Thus, the bug is apparently connected to hardware, and it is also non-deterministic. It might be some library bug which only appears when the code is physically located in a certain way. As many of our users have reported the problem from Red Hat 8.0 it is certainly a very common problem. Since a stable MySQL can be compiled on all other Unix platforms except Red Hat 8.0, that suggests the bug is in Red Hat 8.0. When I run mysqld inside gdb, the debugger seems to get confused when mysqld creates a new pthread for a new connection. Probably the stack gets badly corrupt in that phase. I solved the problem in my computer by installing Red Hat 7.3. When MySQL is compiled with gcc-2.96 there, it is stable. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL sql query - 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: Scripting MySQL Commands
Guys, Guys-- I got it! I stopped being a dimwit long enough to realize that the change to USE Temp; below was for INSIDE the script. When I made that change and again went to a new shell and typed (actually, I dragged the file from the window in which it sat in the Finder into the Terminal after typing): /usr/local/bin/mysql -u [name] -p /Users/[my username]/Sites/Temp.sql I entered my password, was presented with a new prompt and typed: /usr/local/bin/mysql -u [name] -p Again I was asked for my password, which I typed in. At the next prompt I typed USE Temp. The database changed and at the prompt that followed, I typed SHOW TABLES. Test_Table was there. I did a SELECT * FROM Test_Table and the test row of data was indeed there. Thank you all-- Steve - 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: Scripting MySQL Commands
I'm fairly new to MySQL as well (although, I do have experience with Oracle). Even though you create a new database doesn't mean that MySQL will automatically start using that database. After all you might want to create 10 or 15 databases all at once (why? I don't know), then start creating tables for the 5th one you created. So your script needs to do something like: create database 'Temp'; use 'Temp'; (You need the quotes [or maybe it's the backquotes] if you actually want a mixed case name.) I'm sure that if I'm off, someone here will correct me. jeff At 12:27 -0500 3/2/03, Stephen Tiano wrote: Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me incorrect database name. Well, yes, the script is to create a nonexistent database and then a table called Temp in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - 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 -- Jeff Shapiro, Colorado Springs, CO, USA At work I *have* to use a Windows machine, at home I *get* to use a Mac. - 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: Using SUM in a select statement.
What you are looking for appears to be report layout. Keep in mind that SQL will only return multiple rows, all with the same columns populated. To even get close to what you want, you can create a query that returns: TeamOwner, Player, Position, Goals, Points And then your application can massage it into the output format you want. A query to return BOTH detail information on players AND summary information on owners would have to be a union of two queries - one returning the player information and the other returning owner summary information (UNION requires version 4.0): SELECT O.OwnerName, 1 as SortOrder, P.Player, P.Position, Goals, Points FROM owners as O INNER JOIN teamplayers as T ON O.OwnerID = T.OwnerID INNER JOIN players as P ON T.PlayerID = P.PlayerID UNION SELECTO.OwnerName, 2 as SortOrder, 'Total' as Player, P.Position, Sum(Goals) as Goals, Sum(Points) as Points FROM owners as O INNER JOIN teamplayers as T ON O.OwnerID = T.OwnerID INNER JOIN players as P ON T.PlayerID = P.PlayerID GROUP BY O.OwnerName, SortOrder, Player, P.Position ORDER BY O.OwnerName, SortOrder, Player, P.Position Based on this, it is basically a question of suppressing repeated values of OwnerName. Adjust the query to match your actual situation. If you are using version 3.23, AFAIK you'll either have to use two separate queries or calculate the totals in your app from the detail data. HTH, Tore. - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 11:54 AM Subject: Using SUM in a select statement. Hi, Not sure how to explain this - but here is what I need. I have a hockey pool database with three tables - one has the players and their stats, one has the team owner, and the 3rd is the reference file that ties the two together. I want to be able to do a query for all the players on a particular team that are forwards and get a grand total of all their points. I already have a query that does something similar, but it just displays the individual players stats. Example: Team 1PlayerPosition GoalsPoints *Bob Bonk F 2250 HossaF3341 Total5591 This is what I want to achieve - and then the same for the rest of the teams. *This is my query that get the individual players points. $query = select team.name, player, position, gp, goals, ppg, gwg, shg, ass, pm, shots, pim, points from roster join reference join team where team.idn=reference.idn and reference.idp=roster.idp and team.idn = '$team' and position like '$position' order by points desc; TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
At 12:25 -0500 3/2/03, Stephen Tiano wrote: Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me incorrect database name. Of course. The first non-option argument, if there is one, is taken as the name of the default database. You need a character before the filename if you want to redirect the input of the command to read from the file: /usr/local/bin/mysql --local-infile -u root -p filename Well, yes, the script is to create a nonexistent database and then a table called Temp in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - 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
segmentation fault.
Dear list, I've installed mysql 3.32.55 And for some reason, I'm getting this error message on my screen: /usr/local/mysql/bin/safe_mysqld: line 280: 8207 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 8210 - killed 030302 19:41:26 mysqld restarted I have configed mysql as following : ./configure \ --prefix=/usr/local/mysql \ --enable-large-files \ --with-innodb \ --with-mysqld-user=mysql \ --disable-maintainer-mode \ --without-docs \ --without-debug \ --without-bench \ --enable-assembler \ --with-named-z-libs=/usr/local/zlib/lib The only time I get this, is when cyrus wants to login to get some user data ( login data for mail user ) With 3.23.54 I didn't had any problems. I hope some one can help me with this. Thanks in advance. With kind regards, Richard Pijnenburg Klik-on Internet Solutions - 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
List commands
Hi, i'am new in this list. In my subscrition I did give wrong mailbox ... How can i change mailbox from my subscrition ? Thanks Fernando mySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql not replicating
i finally got mysql to login to the master server by adding log-bin in my.cnf for the slave server , i can do a show slave status; and show master status; so it all seems to be working but the tables on the slae dont seem to wanna sync :| what could be the prob ? - 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: [Web-cyradm] mysql segmentation fault ( cyrus checking )
Nope all the tables are good. (Also just installed it) When I start cyrus I get the following error message in my messages log : Mar 2 21:19:06 dev tls_prune[836]: DBERROR db4: 4 lockers When trying to connect to the mail server I get these error's : maillog Mar 2 21:20:26 dev postfix/smtpd[841]: warning: dict_nis_init: NIS domain name not set - NIS lookups disabled Mar 2 21:20:27 dev postfix/smtpd[841]: connect from unknown[192.168.0.31] Mar 2 21:20:27 dev postfix/smtpd[841]: 743B828294: client=unknown[192.168.0.31] Mar 2 21:20:27 dev postfix/trivial-rewrite[844]: warning: connect to mysql server 192.168.0.32: Lost connection to MySQL server during query Mar 2 21:20:27 dev postfix/trivial-rewrite[844]: fatal: mysql:/etc/postfix/mysql-mydestination.cf: table lookup problem Mar 2 21:20:28 dev postfix/smtpd[841]: warning: premature end-of-input on private/rewrite socket while reading input attribute name Mar 2 21:20:28 dev postfix/smtpd[841]: warning: resolve_clnt_query: bad read: Success Mar 2 21:20:28 dev postfix/master[814]: warning: process /usr/libexec/postfix/trivial-rewrite pid 844 exit status 1 And on my mysql server I get : /usr/local/mysql/bin/safe_mysqld: line 280: 9375 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 9385 - killed 030302 21:05:43 mysqld restarted I don't know what's wrong I hope some one can help me. Met vriendelijke groet, Richard Pijnenburg Klik-on Internet Solutions Mobiel : +31 (0)6 20 76 70 46 Web : www.klik-on.nl -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Seifert Sent: Sunday, March 02, 2003 8:24 PM To: [EMAIL PROTECTED] Subject: Re: [Web-cyradm] mysql segmentation fault ( cyrus checking ) You may want to run mysqlcheck/check table ... maybe the table for cyrus is corrupted. Thomas On Sun, 2 Mar 2003 20:11:13 +0100 richard pijnenburg [EMAIL PROTECTED] wrote: Dear list, I've installed mysql 3.32.55 And for some reason, I'm getting this error message on my screen: /usr/local/mysql/bin/safe_mysqld: line 280: 8207 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 8210 - killed 030302 19:41:26 mysqld restarted The only time I get this, is when cyrus wants to login to get some user data ( login data for mail user ) With 3.23.54 I didn't had any problems. I hope some one can help me with this. Met vriendelijke groet, Richard Pijnenburg Klik-on Internet Solutions Mobiel : +31 (0)6 20 76 70 46 Web : www.klik-on.nl ___ Web-cyradm mailing list [EMAIL PROTECTED] http://www.web-cyradm.org/mailman/listinfo/web-cyradm - 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: update question
On Sun, 2 Mar 2003 02:06:40 -0500 Tore Bostrup [EMAIL PROTECTED] wrote: I assume you are storing your dates in a char/varchar column - not a good choice to start with... :- yes, varchar. I'm still learning this stuff, and experimenting with it. Assuming all the values are supposed to be stored as MM-DD-YY (anothoer marginal choice, but the problem may not rear its head again for another 96+ years), you can do the following: heh, heh, I don't think I'll be around another 96 years to find out. Lets see, I would be 139 years old. Probably wouldn't be pushing too many keys on the keyboard at that age. This particular database/tables are not for business use, just my own learning. UPDATE mytable SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) WHERE mydatestr LIKE '__-__-__-__' Thanks for the help, worked great. I didn't know about the underscore being a wildcard character. I should look for a better MySQL book, the ones I have don't cover that info. Any suggestions for one that does? -- Chip HTH, Tore. - Original Message - From: chip wiegand [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 1:46 AM Subject: update question I need to make a change to a field in a table and don't know how to write the correct sql statement. I made an error and now have a date field with the year repeated twice - 01-01-03-03 - there are aproximately 100 rows like this, and maybe 20 or so that are formatted properly. How can I remove the last 3 characters while leaving other rows that do not have this problem alone? (other than manaully editing each row of course) Thanks, Chip W. www.wiegand.org --- -- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Troubles with joining tables
Hi list, I am sure that there is an efficient way to do this in SQL, I just can't figure out what it is. I am dealing with two tables. One, I'll call table1 has about 90 columns and 20k rows, each number in column xy is unique. The other has about 90 columns and about 200k rows, and there will be around 10 duplicate entries in xy for each value of xy. So, if I SELECT a given number in table1.xy, one row is returned, then if I SELECT the same number in table2.xy, about 10 rows will be returned. There is always at least one entry in table2.xy for each entry in table1.xy I am looking for the best way to join them so for each row in the result is a row of table1 lined up with one (and only one) row from table2. A SELECT of everything would return 20k rows, in this case. In the WHERE statement there will be limitations put on both values in other columns in table1 and table2. The best I can think of is a LEFT JOIN, but it returns a table where every row in table1 has been duplicated about 10 times. (and I am hoping for it to return a table of every row in table1 joined with only one row of table2 where table1.xy=table2.xy.) I am aware that I could simply GROUP BY xy, but I am also trying to use this with a few AVG(some other column) clauses to get the average of all rows returned. GROUP BY xy only returns the average of each grouping. I am also guessing that GROUP By in this instance would be rather inefficient because It would first have to build a large table (200k rows) and then SELECT from it. I am using static MyISAM tables and everything in the table is either a float or an int. Is the best way for me to do this just to go ahead and do the GROUP BY, return a table of 20k rows, and then calculate the AVG (and COUNT, STD, MIN, and MAX) in my script? I was hoping to do this in a less time consuming fashion. I can calculate all of this in a timely manner when I am only using values from table1. ~Seth - 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: HELP mysql_server_init embedded in a DLL?
Hi, - Original Message - From: Derick Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 28, 2003 9:09 PM Subject: Re: HELP mysql_server_init embedded in a DLL? Attempts at creating an embedded mySQL DLL for VB: I can successfully use the mysql_init() function from libmysql.lib to create a DLL for VB. However, when I use this function (in a DLL or lib)linked against the libmysqld.lib or mysqlserver.lib it crashes the VB application but not the C application. I tried hard coding the arguments and servergroups for mysql_server_init (I also used memset to initialize the strings to 0), I get the same result. The lib I create will always work with a C program, but the DLL will not work with VB. You must take care with CALLING CONVENTIONS(__stdcall).This is be one reason for crash. Does anyone have any more suggestions on how to use the embedded library in a Visual C DLL to link with VB? You can not access directly the mysql api function from VB.Are ,at least,two big problems :the VB type STRING which is UNICODE ; passing the data structure (like MYSQL which contain node lists) from VB to C or reverse.In my opinion you should make functions which will be interface between MYSQL C API and VB.Also you should make your own function to convert char to unicode and reverse.It's better if you don't use MultiByteToWideChar()...you will get a lot of unpleasure surprises. Of course you can do some improvisation (like in mysql_init or to convert a STRING into ARRAY of BYTE) but is not a solution because VB imediatly will give you some error like OUT OF MEMORY. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] Thanks Eric p.s: Email received from another individual (Thanks!!): Put the libMySql.dll in the wint\system32 directory (or in the directory where your executable is!) In vb, I use this one the declares: Public Declare Function mysql_init Lib libmySQL (ByVal lMYSQL As Long) As Long Public Declare Function mysql_real_connect Lib libmySQL _ (ByVal lMYSQL As Long, _ ByVal sHostName As String, _ ByVal sUserName As String, _ ByVal sPassword As String, _ ByVal sDbName As String, _ ByVal lPortNum As Long, _ ByVal sSocketName As String, _ ByVal lFlags As Long) As Long Public Function OpenConnection(ByVal sHostName As String, _ ByVal sUserName As String, _ ByVal sPassword As String, _ ByVal sDbName As String, _ Optional ByVal lPortNum As Long = 3306, _ Optional ByVal lFlags As MYSQL_FLAG) As MYSQL_CONNECTION_STATE mlMYSQL = mysql_init(mlMYSQL) If mlMYSQL = 0 Then no connection handler Else 'connect to server If mysql_real_connect(mlMYSQL, msHostname, msUsername, msPassword, msDbName, mlPortNum, , mlFlags) = 0 Then ' oops Else 'connection established ... state is now open End If End If 'setup the return value End Function I myselve created an dll (in vb) which encapsulates all the stuff to connect to the database, use recordsets, (as close as possible to the ADO object model: so with connection, recordset - objects, bookmarks, fields, ...) Hope this helps. From: Gelu Gogancea [EMAIL PROTECTED] To: Derick Smith [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: HELP mysql_server_init embedded in a DLL? Date: Fri, 28 Feb 2003 14:40:31 +0200 Hi, - Original Message - From: Derick Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 10:33 PM Subject: Re: HELP mysql_server_init embedded in a DLL? I am posting this again, in hope that I will get a response. I tried doing the following (see VB code below. I got this code from another site): I get the same error message as if I was calling the DLL I created in C which calls mysql_server_init. Is it impossible to call this function from VB or embedded in another DLL from VB? Can I only use the embedded mysql library in C or C++? Thanks Eric Public Declare Function mysql_server_init Lib C:\mysql40\source\lib_release\libmysqld.dll _ (ByVal argc As Long, _ ByVal argv As Byte, _ ByVal groups As Byte) As Long Private Sub cmdmysq_server_init_Click() Dim argv(0) As Byte, groups(0) As Byte Dim i As Long argv(0) = Asc(0) groups(0) = Asc(0) i = mysql_server_init(0, argv, groups) End Sub In my opinion,this will never work because argv and groups are pointed to array which have a pointer char*(*); But
Troubles with joining tables (cont)
Since writing this message, I have discovered another possible way for doing this with two successive SQL statements. It would look something like this: 1) CREATE TEMPORARY TABLE temp SELECT DISTINCT xy FROM table2 WHERE all of table2 conditions; (going by data below, maximum of 20k rows because it is DISTINCT) 2) SELECT AVG(column1),all other group calculations FROM table1 LEFT JOIN temp ON temp.xy=table1.xy WHERE all of table1 conditions AND temp.xy IS NOT NULL; (maximum of 20k rows, less after both WHERE statements tho) Does this sound like the best way to accomplish this? Any suggestions? Thx, Seth On Sunday, March 2, 2003, at 03:41 PM, Seth Price wrote: Hi list, I am sure that there is an efficient way to do this in SQL, I just can't figure out what it is. I am dealing with two tables. One, I'll call table1 has about 90 columns and 20k rows, each number in column xy is unique. The other has about 90 columns and about 200k rows, and there will be around 10 duplicate entries in xy for each value of xy. So, if I SELECT a given number in table1.xy, one row is returned, then if I SELECT the same number in table2.xy, about 10 rows will be returned. There is always at least one entry in table2.xy for each entry in table1.xy I am looking for the best way to join them so for each row in the result is a row of table1 lined up with one (and only one) row from table2. A SELECT of everything would return 20k rows, in this case. In the WHERE statement there will be limitations put on both values in other columns in table1 and table2. The best I can think of is a LEFT JOIN, but it returns a table where every row in table1 has been duplicated about 10 times. (and I am hoping for it to return a table of every row in table1 joined with only one row of table2 where table1.xy=table2.xy.) I am aware that I could simply GROUP BY xy, but I am also trying to use this with a few AVG(some other column) clauses to get the average of all rows returned. GROUP BY xy only returns the average of each grouping. I am also guessing that GROUP By in this instance would be rather inefficient because It would first have to build a large table (200k rows) and then SELECT from it. I am using static MyISAM tables and everything in the table is either a float or an int. Is the best way for me to do this just to go ahead and do the GROUP BY, return a table of 20k rows, and then calculate the AVG (and COUNT, STD, MIN, and MAX) in my script? I was hoping to do this in a less time consuming fashion. I can calculate all of this in a timely manner when I am only using values from table1. ~Seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
case sensitivity in a delete query
hi i have a delete query that will delete a record based on the company name: delete from members where company='$company'; the query works fine and all but there is a problem because if there is more than 1 company listed but with different use of case in their name then allrecords that match $company are deleted.. so: BLACK black Black BlacK and so on are all considered the same entry by delete and will wipe them all even if all i wanted to do was to delete BLACK.. so was just wondering how to fix that if it is even possible.. i fear that the same problem will happen with my update statements also and want them changed too because if BLACK needs to be updated because the phone number changes then i dont need BlacK to inherrit the same phone number anybody know how to fix? tnx --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.458 / Virus Database: 257 - Release Date: 2/24/2003 - 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: case sensitivity in a delete query
At 17:51 -0500 3/2/03, Sunfire wrote: hi i have a delete query that will delete a record based on the company name: delete from members where company='$company'; the query works fine and all but there is a problem because if there is more than 1 company listed but with different use of case in their name then allrecords that match $company are deleted.. so: BLACK black Black BlacK and so on are all considered the same entry by delete and will wipe them all even if all i wanted to do was to delete BLACK.. so was just wondering how to fix that if it is even possible.. i fear that the same problem will happen with my update statements also and want them changed too because if BLACK needs to be updated because the phone number changes then i dont need BlacK to inherrit the same phone number anybody know how to fix? If your column is CHAR, VARCHAR, or TEXT, use CHAR BINARY, VARCHAR BINARY, or BLOB instead. Or just change the query to ... where company = BINARY '$company'. Comparisons of binary strings are case sensitive. Best to read the sections in the manual that describe the string columns to get an idea of how they work. It'll help you avoid problems like this in the future. - 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: update question
Personally, I usually like Reference books better than Idiot's Guide books. And my favorite reference handbook for the SQL language (ANSI SQL-92 standard) is Martin Gruber, SQL Instant Reference (SYBEX). There is (was) at least a 2nd edition available. No fluff, just standard syntax and a good description of the key things you need to know for writing SQL queries. You'll still need to check the MySQL documentation to find out what is/isn't supported, what differes froim the standard, and what specific functions are available, etc. HTH, Tore. - Original Message - From: chip wiegand [EMAIL PROTECTED] To: Tore Bostrup [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 4:25 PM Subject: Re: update question On Sun, 2 Mar 2003 02:06:40 -0500 Tore Bostrup [EMAIL PROTECTED] wrote: I assume you are storing your dates in a char/varchar column - not a good choice to start with... :- yes, varchar. I'm still learning this stuff, and experimenting with it. Assuming all the values are supposed to be stored as MM-DD-YY (anothoer marginal choice, but the problem may not rear its head again for another 96+ years), you can do the following: heh, heh, I don't think I'll be around another 96 years to find out. Lets see, I would be 139 years old. Probably wouldn't be pushing too many keys on the keyboard at that age. This particular database/tables are not for business use, just my own learning. UPDATE mytable SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) WHERE mydatestr LIKE '__-__-__-__' Thanks for the help, worked great. I didn't know about the underscore being a wildcard character. I should look for a better MySQL book, the ones I have don't cover that info. Any suggestions for one that does? -- Chip HTH, Tore. - Original Message - From: chip wiegand [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 1:46 AM Subject: update question I need to make a change to a field in a table and don't know how to write the correct sql statement. I made an error and now have a date field with the year repeated twice - 01-01-03-03 - there are aproximately 100 rows like this, and maybe 20 or so that are formatted properly. How can I remove the last 3 characters while leaving other rows that do not have this problem alone? (other than manaully editing each row of course) Thanks, Chip W. www.wiegand.org --- -- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL newbie confusion
Hi, my experience comes from VB6 with access databases. I'am trying to get one quick and practical overview about the mySQL capabilities (web online database). But so far i'am really loss and confuse regarding the mySQL setup and installation procedure. I'am using WIN98 with mySQL4.01.11 and follow step by step http://www.mysql.com/doc/en/index.html manual. So far i did install the mySQL and create the my.ini file in windows folder. [mysqld] basedir=C:\Progra~1\Mysql datadir=C:\Progra~1\Mysql\Data At this point i'am really confuse and thinking what dummy am i ... can someone help me please to turn this frustrating stage. Thanks Fernando - 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
libmysql.dll(v4.0.10) + delphi 7 + dbexpress
Hi all When i have installed version 4.0.10 of mysql server everything works great... but when I try to use this libmysql.dll with Delphi + dbexpress I always get in trouble with the error msg unable to load libmysql.dll With libmysql.dll 3.23.55 everything works fine (but I think I cant use some of the new features of version 4.0) Im correct ??? There is any workaround ??? TIA José Longo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL newbie confusion
...and your problem is? -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: Fernando Martins [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 6:00 PM To: [EMAIL PROTECTED] Subject: mySQL newbie confusion Hi, my experience comes from VB6 with access databases. I'am trying to get one quick and practical overview about the mySQL capabilities (web online database). But so far i'am really loss and confuse regarding the mySQL setup and installation procedure. I'am using WIN98 with mySQL4.01.11 and follow step by step http://www.mysql.com/doc/en/index.html manual. So far i did install the mySQL and create the my.ini file in windows folder. [mysqld] basedir=C:\Progra~1\Mysql datadir=C:\Progra~1\Mysql\Data At this point i'am really confuse and thinking what dummy am i ... can someone help me please to turn this frustrating stage. Thanks Fernando - 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: libmysql.dll(v4.0.10) + delphi 7 + dbexpress
Jos Longo wrote: Hi all When i have installed version 4.0.10 of mysql server everything works great... but when I try to use this libmysql.dll with Delphi + dbexpress I always get in trouble with the error msg unable to load libmysql.dll With libmysql.dll 3.23.55 everything works fine (but I think I cant use some of the new features of version 4.0) Im correct ??? There is any workaround ??? Borland typically add support for MySQL client libraries to DBExpress approx 2 years after their release. One of the many reasons I gave up on Kylix... You should be able to use old client libraries with the new server, but as you noted, new features won't be available. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.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
Bug Report: mysql-3.23.38-win
I am attempting to get mysql-3.23.38-win on a Windows 98 SE box. The mysql-3.23.38-win.zip install wizard allowed me to install to D:\MYSQL I have tweaked the my.cfg file as follows: # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking default-character-set=latin1 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 # Uncomment the following row if you move the MySQL distribution to another # location #basedir = [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash basedir = d:/mysql datadir= d:/mysql/data [mysqld] innodb_data_file_path = ibdata:30M innodb_data_home_dir=\mysql\data [isamchk] set-variable= key=16M [client_fltk] #help_file= c:\mysql\sql_client\MySQL.help #client_file= c:\mysql\MySQL.options history_length=20 database = test queries_root= d:\mysql\queries last_database_file= d:\mysql\lastdb -- When mySQL is launched via D:\mysql\bin\mysqld.exe --basedir D:\mysql I get the following: Innobase: Assertion failure in thread 4225946759 in file M:\mysql-3.23\innobase os\os0file.c line 187 Innobase: we intentionally generate a memory trap. Innobase: Send a bug report to [EMAIL PROTECTED] 030302 18:45:05 D:\MYSQL\BIN\MYSQLD.EXE: Got signal 11. Aborting! 030302 18:45:05 Aborting InnoDB: Warning: shutting down not properly started database 030302 18:45:05 D:\MYSQL\BIN\MYSQLD.EXE: Shutdown Complete Sincerely, Keith E. [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
having a problem with query using multiple sources
hi.. having a problem with a mysql query where 2 different sources use the same query.. the only problem is that in my where clause i.e.: where '$edit[company]'==$company the $edit[company] is not valid for one of the sources... is there a way to let both sources use the same query like that.. my where clauses would have to be: where '$edit[company]'=$company where '$company'=$company --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.458 / Virus Database: 257 - Release Date: 2/24/2003 - 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: Troubles with joining tables
Seth Price wrote: I am dealing with two tables. One, I'll call table1 has about 90 columns and 20k rows, each number in column xy is unique. The other has about 90 columns and about 200k rows, and there will be around 10 duplicate entries in xy for each value of xy. So, if I SELECT a given number in table1.xy, one row is returned, then if I SELECT the same number in table2.xy, about 10 rows will be returned. There is always at least one entry in table2.xy for each entry in table1.xy I am looking for the best way to join them so for each row in the result is a row of table1 lined up with one (and only one) row from table2. I'd better ask the obvious question... *which* one? Is there one in particular that you're after, or would you be equally happy with any of them? The latter would be a bit odd. I am aware that I could simply GROUP BY xy, but I am also trying to use this with a few AVG(some other column) clauses to get the average of all rows returned. GROUP BY xy only returns the average of each grouping. I am also guessing that GROUP By in this instance would be rather inefficient because It would first have to build a large table (200k rows) and then SELECT from it. This is complex enough so that I'll need a more detailed example of your query to explain what you're trying to accomplish. But, on the surface, it sounds as though you're already using GROUP BY in the same SELECT, in which case you won't be taking on a significant amount of additional overhead -- you're already scanning all the table2 rows because of your GROUP BY. Or, are you talking about multiple SELECT statements? Bruce Feist - 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: Troubles with joining tables (cont)
Seth Price wrote: Since writing this message, I have discovered another possible way for doing this with two successive SQL statements. It would look something like this: 1) CREATE TEMPORARY TABLE temp SELECT DISTINCT xy FROM table2 WHERE all of table2 conditions; (going by data below, maximum of 20k rows because it is DISTINCT) So the bulk of the selection criteria are on table2, not table1? 2) SELECT AVG(column1),all other group calculations FROM table1 LEFT JOIN temp ON temp.xy=table1.xy WHERE all of table1 conditions AND temp.xy IS NOT NULL; (maximum of 20k rows, less after both WHERE statements tho) Wouldn't you get the same result from your query by using an INNER JOIN and dropping the temp.xy IS NOT NULL clause? Bruce Feist - 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
mysqld issue?
Having problems installing phpBB and Nuke-PHP on my OpenBSD box, all seem to be stemming from attempts to access MySQL. For instance, phpBB gives the error Could not connect to the database when using the install.php script. The install script for Nuke just fails to load completely so I'll try tackling that later. Also, phpinfo yields 0 Active Persistent Links and 0 Active Links, although it recognizes the version installed and socket. Is this normal? I can access everything fine through 'mysql -u root -p' and create a database for the bulletin board. MySQLd was installed, by the way, from the OpenBSD package collection. PS shows the following, which seems normal to me: /bin/sh/usr/local/bin/safe_mysqld --user=mysql --log Any ideas/hints? Is this even the right list to be querying? - 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
embedded mysql
Hi, we are developing an application in java that needs to have an embedded database so that we needn't install the database on the client machine. Is it possible to achieve this using mysql. I read that mysql 4.0 supports embedded database, but I find that embedded MySQL server library is only for C developers. please help thanks rai _ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plusref=lmtplus - 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
A Simple Query! - SOLVED
Working solution Bruce Feist SELECT Films.idFilm, title FROM Films LEFT JOIN Loans ON (Films.idFilm = Loans.idFilm) AND (Loans.return_date IS NULL) WHERE Loans.idFilm IS NULL; Note: This solution works for me as it considers that I give no value (NULL) to the attribute return_date at the moment of borrowing a film to someone. I only give this attribute a value when the film is returned. Thanks a lot for proving me that my question has a solution! Finally Ill be able to make my program work correctly! Additionally, I will be able to prove to my Professor that MySQL has one less limitation. However, honestly I dont understand why this works, and that bothers me. If it isnt asking too much, I appreciate it if you could explain me how LEFT JOIN works and helps me to produce the result I wanted. As I said, I am new to MySQL. Actually, my only experience with databases is on a strictly theoretical level. Now I am trying to use my knowledge to create an application for my own using MySQL and PHP, which I know is a popular combination! -- Remi André Mikalsen Homepage - http://mikalsen.no.sapo.pt Email - [EMAIL PROTECTED] PS! Uttam; your solution shows me which films were on loan at least once Sam Funk; your solution shows me which films are currently on loan Thank you anyway!
innodb deadlock leads to server crash
Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 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.0) Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so -rwxr-xr-x 1 root root 2194520 Feb 6 12:32 /usr/lib/libc-client.a Configure command: ./configure '--prefix=/usr' '--without-debug' '--enable-shared' '--without-mit-threads' '--libexecdir=/usr/sbin' '--localstatedir=/var/mysql' '--enable-thread-safe-client' '--sysconfdir=/etc' '--datadir=/usr/share' '--enable-large-files' '--without-readline' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/run/mysql.socket' '--enable-strcoll' '--with-comment=noris network MySQL' '--with-docs' '--with-bench' '--without-berkeley-db' '--without-bench' '--with-innodb' 'CPPFLAGS=-DTHREAD_SAFE_CLIENT' 'CXXFLAGS=-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' - Before posting, please check:
Re: Using SUM in a select statement.
Hmm, after seeing this it looks as if the way I have it is a lot simpler and easier to handle. Currently I just put them in a PHP array and sum the array. Thanks for the input. - Original Message - From: Tore Bostrup [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 1:12 PM Subject: Re: Using SUM in a select statement. What you are looking for appears to be report layout. Keep in mind that SQL will only return multiple rows, all with the same columns populated. To even get close to what you want, you can create a query that returns: TeamOwner, Player, Position, Goals, Points And then your application can massage it into the output format you want. A query to return BOTH detail information on players AND summary information on owners would have to be a union of two queries - one returning the player information and the other returning owner summary information (UNION requires version 4.0): SELECT O.OwnerName, 1 as SortOrder, P.Player, P.Position, Goals, Points FROM owners as O INNER JOIN teamplayers as T ON O.OwnerID = T.OwnerID INNER JOIN players as P ON T.PlayerID = P.PlayerID UNION SELECTO.OwnerName, 2 as SortOrder, 'Total' as Player, P.Position, Sum(Goals) as Goals, Sum(Points) as Points FROM owners as O INNER JOIN teamplayers as T ON O.OwnerID = T.OwnerID INNER JOIN players as P ON T.PlayerID = P.PlayerID GROUP BY O.OwnerName, SortOrder, Player, P.Position ORDER BY O.OwnerName, SortOrder, Player, P.Position Based on this, it is basically a question of suppressing repeated values of OwnerName. Adjust the query to match your actual situation. If you are using version 3.23, AFAIK you'll either have to use two separate queries or calculate the totals in your app from the detail data. HTH, Tore. - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 11:54 AM Subject: Using SUM in a select statement. Hi, Not sure how to explain this - but here is what I need. I have a hockey pool database with three tables - one has the players and their stats, one has the team owner, and the 3rd is the reference file that ties the two together. I want to be able to do a query for all the players on a particular team that are forwards and get a grand total of all their points. I already have a query that does something similar, but it just displays the individual players stats. Example: Team 1PlayerPosition GoalsPoints *Bob Bonk F 2250 HossaF3341 Total5591 This is what I want to achieve - and then the same for the rest of the teams. *This is my query that get the individual players points. $query = select team.name, player, position, gp, goals, ppg, gwg, shg, ass, pm, shots, pim, points from roster join reference join team where team.idn=reference.idn and reference.idp=roster.idp and team.idn = '$team' and position like '$position' order by points desc; TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Duplicate
Hi, I am still learning MySQL and trying to find certain things I use to do in Access. I having been looking and can not find an way to set a field so that it can not be duplicated. Since I can not find that I tryed looking for the record using SELECT and WHERE but doing an IF on the result of the QUERY does not help. Does anyone know how I can check for a duplicate in a database before adding a record. I am checking a text field for duplicate entry. Which means that I have to ignore case and spacing. Thanks for any help. Dee - 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: Troubles with joining tables
I'd better ask the obvious question... *which* one? Is there one in particular that you're after, or would you be equally happy with any of them? The latter would be a bit odd. Each row in table1 has several corresponding rows in table2. Any of them that match the query would be good. So the bulk of the selection criteria are on table2, not table1? It shouldn't matter either way (and I cannot tell which will be the bulk ahead of time). Wouldn't you get the same result from your query by using an INNER JOIN and dropping the temp.xy IS NOT NULL clause? I am not sure, that is why I am asking y'all. My only problem with that is does INNER JOIN create a 200k table with 180 columns and do the selection from that? Or does it SELECT from one table, JOIN the results to the other table, then SELECT again? (which is what I want) I have played around with it a bit and if I am doing my guess-tamating correctly, these two SQL statements will normally take less than a second to do the selection from the two tables and return the statistics. This compares very well with the .92 sec required to pull up the stats on all of table1. (the query in question will pull up some subset of table1) Another advantage is with other queries of this set I will have half of the query cached for me (depending on the query, of course) in the table temp. In case anyone is wondering with these massive tables, I am building a dynamic web site that will let scientists view statistics on various environments/landscapes and how they relate to each other. Currently the largest table is basically the entire state of Wisconsin split up into 6km x 6km chunks at 30m resolution. This yields 7k chunks to keep track of, and on average 10 different environments per chunk. I am trying to design it well enough though that we could in the future use all of the Landsat imagery for the entire U.S. (also 6km x 6km chunks at 30m resolution). There are about 90 different possible statistics per landscape. Thx, Seth On Sunday, March 2, 2003, at 06:31 PM, Bruce Feist wrote: Seth Price wrote: I am dealing with two tables. One, I'll call table1 has about 90 columns and 20k rows, each number in column xy is unique. The other has about 90 columns and about 200k rows, and there will be around 10 duplicate entries in xy for each value of xy. So, if I SELECT a given number in table1.xy, one row is returned, then if I SELECT the same number in table2.xy, about 10 rows will be returned. There is always at least one entry in table2.xy for each entry in table1.xy I am looking for the best way to join them so for each row in the result is a row of table1 lined up with one (and only one) row from table2. I'd better ask the obvious question... *which* one? Is there one in particular that you're after, or would you be equally happy with any of them? The latter would be a bit odd. I am aware that I could simply GROUP BY xy, but I am also trying to use this with a few AVG(some other column) clauses to get the average of all rows returned. GROUP BY xy only returns the average of each grouping. I am also guessing that GROUP By in this instance would be rather inefficient because It would first have to build a large table (200k rows) and then SELECT from it. This is complex enough so that I'll need a more detailed example of your query to explain what you're trying to accomplish. But, on the surface, it sounds as though you're already using GROUP BY in the same SELECT, in which case you won't be taking on a significant amount of additional overhead -- you're already scanning all the table2 rows because of your GROUP BY. Or, are you talking about multiple SELECT statements? Bruce Feist On Sunday, March 2, 2003, at 06:32 PM, Bruce Feist wrote: Seth Price wrote: Since writing this message, I have discovered another possible way for doing this with two successive SQL statements. It would look something like this: 1) CREATE TEMPORARY TABLE temp SELECT DISTINCT xy FROM table2 WHERE all of table2 conditions; (going by data below, maximum of 20k rows because it is DISTINCT) So the bulk of the selection criteria are on table2, not table1? 2) SELECT AVG(column1),all other group calculations FROM table1 LEFT JOIN temp ON temp.xy=table1.xy WHERE all of table1 conditions AND temp.xy IS NOT NULL; (maximum of 20k rows, less after both WHERE statements tho) Wouldn't you get the same result from your query by using an INNER JOIN and dropping the temp.xy IS NOT NULL clause? Bruce Feist - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before
Re: Connector J (3.1.0) - invalid return upon select last_insert_id()
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tea Yu wrote: Not really, I substituted the queryStmt with the actual one but it gave the same result. After some work: select last_insert_id();//getInt() should return 14 //but now it gives java.sql.SQLException: Invalid value for getInt() - 'qt' at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1348) where thisRow([0]) that ResultSet holds was = (49, 52) I haven't looked deeper into ResultSet and StringUtils... Is this as expected? My platform is WinME and my JRE is 1.4.1_01 thanks tea The only way I can debug this is if you give me a repeatable test case. Since the test case I showed you does not repeat the bug, you will need to generate a standalone test case, with schema, data, and the java code that demonstrates the issue. -Mark Hey mark, here are the files that repeats my test. P.S. Test.class was compiled by J2SDK1.4.1_01 on WinME, just in case you need to compare this with your compiled ver. JDBCDriver used was mysql-connector-java-3.1.0-alpha-bin.jar. Thanks Tea I actually found what was causing this earlier today. It is an odd case that only happens when your JVM's default encoding is not single-byte (so I never saw it in my testsuites). It is fixed in the nightly snapshots of both Connector/J 3.0.x and 3.1.x that will be up by tomorrow (the compile and upload happens at 00:00 GMT), see http://mmmysql.sourceforge.net/snapshots/. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Yr5gtvXNTca6JD8RAlBQAKCd+op/ojys+Gf9ZuOZz22jUfl6YgCfStul MB8f3v64KjADyGs9TqdRrBk= =4Eu5 -END PGP SIGNATURE- - 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: update question
On Sun, 2 Mar 2003 17:58:13 -0500
Tore Bostrup [EMAIL PROTECTED] wrote:
Personally, I usually like Reference books better than Idiot's Guide
books. And my favorite reference handbook for the SQL language (ANSI
SQL-92 standard) is
Martin Gruber, SQL Instant Reference (SYBEX). There is (was) at
least a
2nd edition available.
No fluff, just standard syntax and a good description of the key
things you need to know for writing SQL queries.
You'll still need to check the MySQL documentation to find out what
is/isn't supported, what differes froim the standard, and what
specific functions are available, etc.
HTH,
Tore.
Thanks, after reviewing my 'library' I find the books I have with mysql
info are PHP books that have a chapter or two on MySQL. So, I'll have to
buy a SQL book specifically.
Regards,
Chip
- Original Message -
From: chip wiegand [EMAIL PROTECTED]
To: Tore Bostrup [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, March 02, 2003 4:25 PM
Subject: Re: update question
On Sun, 2 Mar 2003 02:06:40 -0500
Tore Bostrup [EMAIL PROTECTED] wrote:
I assume you are storing your dates in a char/varchar column - not
a good choice to start with... :-
yes, varchar. I'm still learning this stuff, and experimenting with
it.
Assuming all the values are supposed to be stored as MM-DD-YY
(anothoer marginal choice, but the problem may not rear its head
again for another 96+ years), you can do the following:
heh, heh, I don't think I'll be around another 96 years to find out.
Lets see, I would be 139 years old. Probably wouldn't be pushing too
many keys on the keyboard at that age. This particular
database/tables are not for business use, just my own learning.
UPDATE mytable
SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3)
WHERE mydatestr LIKE '__-__-__-__'
Thanks for the help, worked great. I didn't know about the
underscore being a wildcard character.
I should look for a better MySQL book, the ones I have don't cover
that info. Any suggestions for one that does?
--
Chip
HTH,
Tore.
- Original Message -
From: chip wiegand [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Sunday, March 02, 2003 1:46 AM
Subject: update question
I need to make a change to a field in a table and don't know how
to write the correct sql statement. I made an error and now have
a date field with the year repeated twice - 01-01-03-03 - there
are aproximately 100 rows like this, and maybe 20 or so that are
formatted properly. How can I remove the last 3 characters while
leaving other rows that do not have this problem alone? (other
than manaully editing each row of course)
Thanks,
Chip W.
www.wiegand.org
---
-- Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
[EMAIL PROTECTED] To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
[EMAIL PROTECTED] To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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: embedded mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 mahesh rai wrote: Hi, we are developing an application in java that needs to have an embedded database so that we needn't install the database on the client machine. Is it possible to achieve this using mysql. I read that mysql 4.0 supports embedded database, but I find that embedded MySQL server library is only for C developers. please help thanks rai It is not currently planned to support 'libmysqld' from Java via the JDBC driver, as it is not technically feasible at this point in time due to threading and data conversion issues. This doesn't mean that you can't 'embed' MySQL through Java, though. There are plenty of users who do this by controlling the regular 'mysqld' through Runtime.exec() calls. 'mysqld' has enough command-line options that make it fully configurable, and controllable using plain command-line calls through Runtime.exec(). Connector/J 3.1.x will ship with utility classes to help with this (they are already included in the nightly snapshots from http://mmmysql.sourceforge.net/snapshots/dev/) -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Yr8ktvXNTca6JD8RAhD9AJ9YTFw19RTXwZa2xnvku4RvpQj5hgCdHT/W /fZrYX1vcn8z/ONCkaU6qv0= =URTk -END PGP SIGNATURE- - 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
mysql.sock
I don't know why when i run mysql the message Can't connect to mysql server throught port /tmp/mysql.sock appear. please answer for me. thank you very much. My email: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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: Connector J (3.1.0) - invalid return upon select last_insert_id()
The only way I can debug this is if you give me a repeatable test case. Since the test case I showed you does not repeat the bug, you will need to generate a standalone test case, with schema, data, and the java code that demonstrates the issue. -Mark Hey mark, here are the files that repeats my test. P.S. Test.class was compiled by J2SDK1.4.1_01 on WinME, just in case you need to compare this with your compiled ver. JDBCDriver used was mysql-connector-java-3.1.0-alpha-bin.jar. Thanks Tea I actually found what was causing this earlier today. It is an odd case that only happens when your JVM's default encoding is not single-byte (so I never saw it in my testsuites). It is fixed in the nightly snapshots of both Connector/J 3.0.x and 3.1.x that will be up by tomorrow (the compile and upload happens at 00:00 GMT), see http://mmmysql.sourceforge.net/snapshots/. -Mark Hey, you're a lifesaver! I look forward to the new drivers! Thanks and regards Tea - 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: Duplicate
Simply create a UNIQUE Index on the column to avoid duplicates. That will be case insensitive, and trailing spaces AFAIK, but spaces inside the columns text have significance. HTH, Tore. - Original Message - From: Dee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 9:33 PM Subject: Duplicate Hi, I am still learning MySQL and trying to find certain things I use to do in Access. I having been looking and can not find an way to set a field so that it can not be duplicated. Since I can not find that I tryed looking for the record using SELECT and WHERE but doing an IF on the result of the QUERY does not help. Does anyone know how I can check for a duplicate in a database before adding a record. I am checking a text field for duplicate entry. Which means that I have to ignore case and spacing. Thanks for any help. Dee - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Troubles with joining tables
Seth Price wrote: Wouldn't you get the same result from your query by using an INNER JOIN and dropping the temp.xy IS NOT NULL clause? I am not sure, that is why I am asking y'all. My only problem with that is does INNER JOIN create a 200k table with 180 columns and do the selection from that? Or does it SELECT from one table, JOIN the results to the other table, then SELECT again? (which is what I want) The specifics of your script would help. But, in general, I'm against trying to out-guess SQL optimizers. It's usually counterproductive to do so, since you can end up spending a lot of time and effort micromanaging something that the optimizer may do adequately now and brilliantly in another release. Instead, tell it what you want, and see if it's fast and efficient enough. If it isn't, *then* worry about it. That said, you seem to have a case where a temporary table might be effective as a starting point for multiple other queries. In that case, it could make sense to introduce that temporary table from the start, because you can be reasonably (not completely) confident that it'll always give you a benefit, and it will also make maintenance simpler in the future since you've in effect modularized a bit. One other consideration, though, is how much overlap there is between the successive queries. If you can actually fold it up into a single, more complex, query, that will probably be the most effective solution overall. YMMV. I'm a newbie to MySQL, so my generalities may not hold up for this specific DBMS. Bruce Feist - 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: A Simple Query!
Hi Bruce, yep, u r right. Anyway, MySQL query optimizer will take care of it ;) regds, -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 04:00 To: [EMAIL PROTECTED] Subject: Re: A Simple Query! Hello, Uttam; We can simplify this, actually... any time L.idFilm is NULL (i.e., no row in Loans is found), L.dateReturn will have to be NULL as well. So, specifying the L.idFilm IS NULL condition is redundant. It's sufficient to look only at L.dateReturn IS NULL in the WHERE clause. Yours is an improvement over my original, though, in that it's correct g. Mine would have missed films having Loans with non-NULL dateReturns. Uttam wrote: here's my version of the answer: SELECT F.idFilm, F.title FROM Films F LEFT JOIN Loans L ON F.idFilm = L.idFilm WHERE (L.idFilm IS NULL) OR (L.dateReutrn IS NULL) ; regds, -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 08:13 select F.idFilm, F.title from Films F left join Loans L on F.idFilm = L.idFilm and L.dateReturn IS NULL where L.idFilm IS NULL; Remi Mikalsen wrote: Scheme: Films (idFilm, title) Loans (idFilm, dateLoan, dateReturn, idContact, idLoan) Contacts (idContact, name) Question: What films aren't on loan right now? Using the SQL standards it should be possible to write the following query. SELECT F.idFilm, F.title FROM Films F WHERE F.idFilm NOT IN ( SELECT L.idFilm FROM Loans L WHERE L.dateReturn IS NULL) - 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
importing data from pervasvie to MySQL
Hi all, I have a database in Pervasive and I want to import it to MySQL. I tried to convert the Pervasive data to a text file and then import it into MySQL but it failed. Is there any tool to do this directly? Does MySQLCC provide any export option through which I can get the data from Pervasive? Thanks, Prasanth - 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: importing data from pervasvie to MySQL
Hi all, Hi, I have a database in Pervasive and I want to import it to MySQL. I tried to convert the Pervasive data to a text file and then import it into MySQL but it failed. Is there any tool to do this directly? Does MySQLCC provide any export option through which I can get the data from Pervasive? Try Perl. You can do everything You need. Best regards, Maciej Bobrowski - 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: MyODBC 3.51 - Memory allocation error with BLOBs
On Sun, 2003-03-02 at 21:19, Aaron O'Neil wrote: How-To-Repeat: I suspect there is a bug in the ODBC driver in relation to blobs. I'm now using 3.51.06, but was having this same problem on 3.51.05. I'm using Visual C++ and their CRecordset wrapper for the ODBC calls. I'm using the positioned updates. As long as I keep the binary data I'm trying to transfer under the default buffer size (8192) it works fine. I'm guessing it is a default buffer size from looking at the debug text. Here is the output info from setting a binary field that works, the data for the binary field is 6933, less than the 8192. Thanks for the clear report. I just tested this, and noticed that this is happening only with the debug version of the driver and not with the release version, and thats why it wasn't even caught with the regression test suite before the releases. Did you tested this with the release version of the driver ? If yes, what was the outcome. I will investigate and fix why 'my_realloc' is crashing if using the debug version of the MySQL client libraries. Thanks SQLSetPos | enter: irow: 1 fOption: SQL_UPDATE Lock: 0 | init_dynamic_string | | _mymalloc | | | enter: Size: 1024 | | | exit: ptr: 11ae198 | | _mymalloc | init_dynamic_string | find_used_table | | _mymalloc | | | enter: Size: 8 | | | exit: ptr: 10238a8 | | _mymalloc | find_used_table | dynstr_realloc | dynstr_realloc | dynstr_realloc | dynstr_realloc | extend_buffer | | enter: current_length: 0 length: 6934 buffer_length: 8192 | extend_buffer | info: param: 0x12eddc ctype: -2 SqlType: -4 data: 0x1412c58 length: 6933 actual_len: 6933 pos_in_query: 00FCF764 | info: param: 0x12eddc ctype: 1 SqlType: 4 data: 0xfced54 length: 1 actual_len: 1 pos_in_query: 00FCF764 That above works just fine. Here is what happens whenever I try to set binary data 8192: SQLSetPos | enter: irow: 1 fOption: SQL_UPDATE Lock: 0 | init_dynamic_string | | _mymalloc | | | enter: Size: 1024 | | | exit: ptr: 11ae198 | | _mymalloc | init_dynamic_string | find_used_table | | _mymalloc | | | enter: Size: 8 | | | exit: ptr: 1023808 | | _mymalloc | find_used_table | dynstr_realloc | dynstr_realloc | dynstr_realloc | dynstr_realloc | extend_buffer | | enter: current_length: 0 length: 11016 buffer_length: 8192 | | _myrealloc | | | _mymalloc | | | | enter: Size: 16384 | | | | exit: ptr: 11ae5f0 | | | _mymalloc | | | _myfree | | | | enter: ptr: f37d58 | | | _myfree | | _myrealloc | extend_buffer | info: param: 0x12eddc ctype: -2 SqlType: -4 data: 0x1412d30 length: 11015 actual_len: 11015 pos_in_query: 00FCF764 | extend_buffer | | enter: current_length: 2582681 length: 11527 buffer_length: 8192 | | _myrealloc | | | safe: Reallocating unallocated data at line 145, 'execute.c' | | _myrealloc | extend_buffer | copy_error | | error: code :4001, state: S1001, err :(null) | | exit : SQL_ERROR This happens every time I try to store more than 8192 bytes. - 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 -- Regards, Venu For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA ___/ www.mysql.com Join MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug Report: mysql-3.23.38-win
Keith, please upgrade to 3.23.55. Your MySQL version is very old. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Original Message - From: Keith Engelhardt [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 1:52 AM Subject: Bug Report: mysql-3.23.38-win I am attempting to get mysql-3.23.38-win on a Windows 98 SE box. The mysql-3.23.38-win.zip install wizard allowed me to install to D:\MYSQL I have tweaked the my.cfg file as follows: # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking default-character-set=latin1 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 # Uncomment the following row if you move the MySQL distribution to another # location #basedir = [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash basedir = d:/mysql datadir= d:/mysql/data [mysqld] innodb_data_file_path = ibdata:30M innodb_data_home_dir=\mysql\data [isamchk] set-variable= key=16M [client_fltk] #help_file= c:\mysql\sql_client\MySQL.help #client_file= c:\mysql\MySQL.options history_length=20 database = test queries_root= d:\mysql\queries last_database_file= d:\mysql\lastdb -- When mySQL is launched via D:\mysql\bin\mysqld.exe --basedir D:\mysql I get the following: Innobase: Assertion failure in thread 4225946759 in file M:\mysql-3.23\innobase os\os0file.c line 187 Innobase: we intentionally generate a memory trap. Innobase: Send a bug report to [EMAIL PROTECTED] 030302 18:45:05 D:\MYSQL\BIN\MYSQLD.EXE: Got signal 11. Aborting! 030302 18:45:05 Aborting InnoDB: Warning: shutting down not properly started database 030302 18:45:05 D:\MYSQL\BIN\MYSQLD.EXE: Shutdown Complete Sincerely, Keith E. [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 - 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: innodb deadlock leads to server crash
Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 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.0) Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt i' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so -rwxr-xr-x 1 root root 2194520 Feb 6 12:32
