Replication is currupted ...Please help me...
Hi all. My company has three Mysql DB servers. one is master, the other are slaves. master's version is 3.23.54-log. two slaves's version is 4.0.17 and Os of all servers is linux 7.3 2 days ago, another slave's replication was currupted unexpactedly, but the other slave was ok. I don't know what this replaction was currepted. Anybody know what I do? Please let me know... this is err_message at that time. - 040609 13:09:14 Error in Log_event::read_log_event(): 'Event too big', data_len: 1852795251, event_type: 110 040609 13:09:14 Error reading relay log event: slave SQL thread aborted because of I/O error 040609 13:09:14 Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0 040609 13:32:16 Slave I/O thread exiting, read up to log 'www5-bin.001', position 84904657
MySQL 3.23.40 and 4.0.20 parallely on the same data-source
Hi there, just one quick question I didn't find an answer in the docu: Is it possible (stable and without data loss :-)) to run e.g. mysql-3.23.40 on Port 3306 and mysql-4.0.20 on Port 3307 on the same machine (SuSE Linux with 2.4.4-kernel) !AND! on the same data-directory without the two versions conflicting (e.g. when both version would try to change some database value within the same time)? How good is mysql's locking in that constellation? Thanks for your support in advance. Greetz, Oliver Neumann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Merge Table,ERROR 1016: Can't open file: '#sql-407f_f.MRG'. (errno: 144)
Hello *, a) what could be reason /solution for the following Problem: ERROR 1016: Can't open file: '#sql-407f_f.MRG'. (errno: 144) b) in which directory the below reported intermediate file should be generated mysql use syslog Database changed mysql ALTER TABLE logs UNION=( logs_20040512,logs_20040513,logs_20040514,logs_20040515,logs_20040516,logs_20040517,logs_20040518,logs_20040519,logs_20040520,logs_20040521,logs_20040522,logs_20040523,logs_20040524,logs_20040525,logs_20040526,logs_20040527,logs_20040528,logs_20040529,logs_20040530,logs_20040531,logs_20040601,logs_20040602,logs_20040603,logs_20040604,logs_20040605,logs_20040606,logs_20040607,logs_20040608,logs_20040610, logs_20040609 ) INSERT_METHOD=LAST; ERROR 1016: Can't open file: '#sql-407f_f.MRG'. (errno: 144) TIA Micha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication is currupted ...Please help me...
May be a full off the filesystem where the relay logbin file is ? Marc. -Message d'origine- De?: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Envoye?: mercredi 9 juin 2004 09:46 A?: [EMAIL PROTECTED] Objet?: Replication is currupted ...Please help me... Hi all. My company has three Mysql DB servers. one is master, the other are slaves. master's version is 3.23.54-log. two slaves's version is 4.0.17 and Os of all servers is linux 7.3 2 days ago, another slave's replication was currupted unexpactedly, but the other slave was ok. I don't know what this replaction was currepted. Anybody know what I do? Please let me know... this is err_message at that time. - 040609 13:09:14 Error in Log_event::read_log_event(): 'Event too big', data_len: 1852795251, event_type: 110 040609 13:09:14 Error reading relay log event: slave SQL thread aborted because of I/O error 040609 13:09:14 Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0 040609 13:32:16 Slave I/O thread exiting, read up to log 'www5-bin.001', position 84904657 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql-php] mysqlimport error
[EMAIL PROTECTED] wrote: is that a problem? I want to avoid ftp db.txt files and then mysqlimport them You should run mysqlimport on the Windows box and specify MySQL server host with -h option. - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 4:38 PM Subject: Re: [mysql-php] mysqlimport error nikos [EMAIL PROTECTED] wrote: I'm trying mysqlimport --local -d --fields-enclosed-by=| --fields-terminated-by=; - -lines-terminted-by=\n -unikos -p mydb c://temp//programs.txt but i get an Error: File 'c:/temp/programs.txt' not found (Errcode: 2), when using table: programs What I'm doing wrong? RH-9 - APACHE v2 - mysql 3.23.54 Looks like you run mysqlimport on the server host but file is located on Windows box. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-row INSERTs
Hello, I tried searching the archives for this, but since the search tool is returning no results when searching for 'INSERT' I figured it's not working. :-) Anyway, my question is this. If I do a single-statement multi-line insert, are the auto-increment IDs of the rows inserted guaranteed to be sequential? Bear in mind also that I'm using InnoDB tables here. Now, I can't find the answer to this in the documentation, and nobody I've asked knows for a fact what the answer is. I personally suspect that it is, since I don't think any other operation will be able to insert in the middle of the multi-row insert, since that will be executed as one statement (which is what it is). However, I can't use this unless I know for an absolute fact that this is indeed the case. Conversely, if I know for a fact that it is not guaranteed, I know that I need to think of something else. :-) Thanks, -- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: MySQL 3.23.40 and 4.0.20 parallely on the same data-source
Hi, Is it possible (stable and without data loss :-)) to run e.g. mysql-3.23.40 on Port 3306 and mysql-4.0.20 on Port 3307 on the same machine (SuSE Linux with 2.4.4-kernel) yes !AND! on the same data-directory without the two versions conflicting (e.g. when both version would try to change some database value within the same time)? How good is mysql's locking in that constellation? no Locking doesn't work with this, because server 1 doesn't know what server 2 does (locks) and vice versa. And what shall the poor reading server do when the writing server just changes the data ? Maybe, if you build all aplications round this, to always fully lock a table on both servers before changing data, might work. But even then, cached data might be a problem. Why do you want to do this ? prosit Klaus Antworten in Deutsch tun's auch :o) -Ursprüngliche Nachricht- Von: Oliver Neumann [New Identity AG] [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juni 2004 10:50 An: [EMAIL PROTECTED] Betreff: MySQL 3.23.40 and 4.0.20 parallely on the same data-source Hi there, just one quick question I didn't find an answer in the docu: Is it possible (stable and without data loss :-)) to run e.g. mysql-3.23.40 on Port 3306 and mysql-4.0.20 on Port 3307 on the same machine (SuSE Linux with 2.4.4-kernel) !AND! on the same data-directory without the two versions conflicting (e.g. when both version would try to change some database value within the same time)? How good is mysql's locking in that constellation? Thanks for your support in advance. Greetz, Oliver Neumann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-row INSERTs
Anyway, my question is this. If I do a single-statement multi-line insert, are the auto-increment IDs of the rows inserted guaranteed to be sequential? Bear in mind also that I'm using InnoDB tables here. Conversely, if I know for a fact that it is not guaranteed, I know that I need to think of something else. :-) Will locking the table work for you? If you lock the table for writing no other process can slip a query in between your queries for certain... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL not finidng openssl/opensslv.h
No one else has had this problem? Just wondering if this may be a bug in the configure script... craig. Craig Harding wrote: I'm configuring mysql-4.0.20 source and I have openssl (OpenSSL 0.9.7d 17 Mar 2004) installed in /usr/local/ssl (default) Below is the output from a make after configuring as follows: ./configure --prefix=/usr/local/mysql --with-openssl I also tried --with-openssl --with-openssl-includes=/usr/local/ssl/include/ --with-openssl-libs=/usr/local/ssl/lib/ and it breaks at the same point. When it's configuring and spurting out the output it does find OpenSSL: checking for OpenSSL... yes Any other people in the same boat? I've checked online and some other people have had the same issue and putting -I/usr/local/ssl/include in the path seems to work but shouldn't this work from the configure command? thanks, craig. - Making all in strings make[2]: Entering directory `/usr/src/mysql-4.0.20/strings' if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I../include-O3 -DDBUG_OFF -MT strxmov.o -MD -MP -MF .deps/strxmov.Tpo \ -c -o strxmov.o `test -f 'strxmov.c' || echo './'`strxmov.c; \ then mv -f .deps/strxmov.Tpo .deps/strxmov.Po; \ else rm -f .deps/strxmov.Tpo; exit 1; \ fi In file included from strxmov.c:33: ../include/my_global.h:1129:30: openssl/opensslv.h: No such file or directory make[2]: *** [strxmov.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.0.20/strings' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.0.20' make: *** [all] Error 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DELETE from more than one table
Hi Michael, Thankyou for your reply, unfortunately I am unable to use InnoDB tables... From: Michael Stassen [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: DELETE from more than one table Date: Tue, 08 Jun 2004 12:50:39 -0400 The manual says ON DELETE CASCADE with foreign keys is available for InnoDB tables starting with mysql 3.23.50 http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html. Michael shaun thornburgh wrote: Hi Robert, Thanks for the reply but unfortunately I am still using version 3.23.54 and I am unable to change it! From: Robert A. Rosenberg [EMAIL PROTECTED] At 14:01 + on 06/07/2004, shaun thornburgh wrote about Re: DELETE from more than one table: Hi Paul, Thanks for the reply. I actually need to delete data from about 10 tables, is this possible? If the field in the tables is defined as a Foreign Key (with ON DELETE CASCADE), deleting the root key will do this (you can then insert it if you did not want to delete it but only the records that pointed at it). I do not know how helpful this is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: MySQL not finidng openssl/opensslv.h
How did you install OpenSSL? From source or packages (e.g. debian or rpm). In that case you also have to install the dev-Package of OpenSSL (openssl-dev) Sincerly, Oliver Craig Harding wrote: No one else has had this problem? Just wondering if this may be a bug in the configure script... craig. Craig Harding wrote: I'm configuring mysql-4.0.20 source and I have openssl (OpenSSL 0.9.7d 17 Mar 2004) installed in /usr/local/ssl (default) Below is the output from a make after configuring as follows: ./configure --prefix=/usr/local/mysql --with-openssl I also tried --with-openssl --with-openssl-includes=/usr/local/ssl/include/ --with-openssl-libs=/usr/local/ssl/lib/ and it breaks at the same point. When it's configuring and spurting out the output it does find OpenSSL: checking for OpenSSL... yes Any other people in the same boat? I've checked online and some other people have had the same issue and putting -I/usr/local/ssl/include in the path seems to work but shouldn't this work from the configure command? thanks, craig. - Making all in strings make[2]: Entering directory `/usr/src/mysql-4.0.20/strings' if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I../include-O3 -DDBUG_OFF -MT strxmov.o -MD -MP -MF .deps/strxmov.Tpo \ -c -o strxmov.o `test -f 'strxmov.c' || echo './'`strxmov.c; \ then mv -f .deps/strxmov.Tpo .deps/strxmov.Po; \ else rm -f .deps/strxmov.Tpo; exit 1; \ fi In file included from strxmov.c:33: ../include/my_global.h:1129:30: openssl/opensslv.h: No such file or directory make[2]: *** [strxmov.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.0.20/strings' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.0.20' make: *** [all] Error 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-row INSERTs
On Wed, 9 Jun 2004 12:58:01 +0200, Jigal van Hemert [EMAIL PROTECTED] wrote: Anyway, my question is this. If I do a single-statement multi-line insert, are the auto-increment IDs of the rows inserted guaranteed to be sequential? Bear in mind also that I'm using InnoDB tables here. Conversely, if I know for a fact that it is not guaranteed, I know that I need to think of something else. :-) Will locking the table work for you? If you lock the table for writing no other process can slip a query in between your queries for certain... Regards, Jigal. Unfortunately locking the table isn't an option as the table is being accessed extremely regularly by other clients performing similar inserts. The key to this is speed and overhead: at present I'm inserting the rows individually and recording each row's ID as I go. However I want to be able to reduce the number of queries involved, so I've though of two possibilities: 1) If the inserted rows have sequential IDs in the same order that they appeared in the INSERT statement, I can do them all in one go, use LAST_INSERT_ID to get the ID of the first and derive the rest by incrementing in the application logic. 2) Insert them all in one statement and then select them back to get each row's ID. Now, I know that 2) will work, and it will allow me to reduce the number of queries per process from N (where N is on average about 9) to 2. However, if 1) will work it will allow me to reduce the number of queries to 1, (plus a call to LAST_INSERT_ID), and wouldn't involve having to match up the rows from the second query in 2). If nobody knows the answer I'll just go with 2, but I thought it was an interesting bit of trivia in addition to being useful to my specific circumstance. Thanks. -- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Configuration
Hi, We are about to build some new database servers and I have some questions which I'd like some advice on. The machines we are building have 4 Xeon 2GHz CPU's, 4 x 32GB SCSI disk using RAID 1+0 (so thats 64GB of storage) and 4 Gig of RAM. The OS will be Redhat 7.3. Other than the mysql database all other tables will be InnoDB, there are only 8 tables with a few of them having millions of records. The data stored will be a cache of third party information for my application to use so there will be many read/writes. Now my questions are : Which file system would you recommend for this ? I've seen many recommendations for ReiserFS but have no experience of it. Should I use a pre-compiled binary or should I compile one myself ? Should the 2 disks for storage be split up into partitions or just 1 large partition per disk ? Is there anything else I should consider when configuring the machines that affect the performance ? I'm quite happy with configuration of the my.cnf for an InnoDB setup and also taking into account of the Linux GLIBC 2GB memory bug. Many Thanks. Marvin __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Configuration
On Wed, Jun 09, 2004 at 01:45:49PM +0100, Marvin Wright wrote: Hi, We are about to build some new database servers and I have some questions which I'd like some advice on. The machines we are building have 4 Xeon 2GHz CPU's, 4 x 32GB SCSI disk using RAID 1+0 (so thats 64GB of storage) and 4 Gig of RAM. Consider RAID10: http://www.acnc.com/04_01_10.html As opposed to 0+1: http://www.acnc.com/04_01_0_1.html You'd think they're the same but they're subtly different leading to very different characteristics. Note the Recommended Application for 10 is a database server. Which file system would you recommend for this ? I've seen many recommendations for ReiserFS but have no experience of it. I use xfs on my Debian MySQL server. Specs are pretty similar, two 2.8GHz Xeons, 4 36GB U320 drives (in RAID10, which is superb), and 4GB of memory. My /db has 418 inodes used, and 16G used out of the 30G on it; making for quite a large average filesize. To be honest, the filesystem isn't really my bottleneck - with 4GB, MySQL and the OS have tons of caching room, and the filesystem is doing maybe 40k/s of sustained activity with the odd burst of real work. You'll probably like to at least check xfs out. Should I use a pre-compiled binary or should I compile one myself ? I found it makes so little difference it's not worth worrying about. I use the apt package for ease of upgrade and dependencies. Should the 2 disks for storage be split up into partitions or just 1 large partition per disk ? Always partition. You get to choose which filesystem suits each partition best. My preference; ext3 for /, xfs for /db, ext2 for /dump. / does very little work but I want it consistant so ext3 is fine. /dump stores backups (which are mirrored elsewhere) and I don't care if its trashed, but I want it fast when I am using it. Is there anything else I should consider when configuring the machines that affect the performance ? Linux 2.6 probably isn't in RedHat 7.3 base, but you'll want to try it. It's faster than 2.4. My configuration was quite happy doing 35,000 selects per second (with super-smack, an arbitrary benchmarking tool); with 2.4 it was a few thousand lower. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on NetWare and Backups
Hi. Hope someone can help me. I am running MySQL on a NetWare 6.x server. I am using Veritas BackupExec. I currently manually copy the files before a backup or manually unload the MySQL server. I would like to automate the backup. What is the best way to do a backup on a NetWare server. Thanks. -Mark Goldfarb [EMAIL PROTECTED]
MySQL not finidng openssl/opensslv.h
Hi! How did you install OpenSSL? From source or packages (e.g. debian or rpm). In that case you also have to install the dev-Package of OpenSSL (openssl-dev) Sincerly, Oliver I was able to repeat this situation. It happens only when OpenSSL is installed to non-standart directory and no OpenSSL includes in /usr/include/openssl. Fix was sent. Regards, Gluh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE...WHERE ... IN (SELECT...)
Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. Thanks Markus Källander
RE: UPDATE...WHERE ... IN (SELECT...)
Sub-select not supported in version you are using. -Original Message- From: Markus Källander To: [EMAIL PROTECTED] Sent: 6/9/04 11:25 AM Subject: UPDATE...WHERE ... IN (SELECT...) Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. Thanks Markus Källander -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE...WHERE ... IN (SELECT...)
-Original Message- From: Markus Källander [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 10:25 AM To: [EMAIL PROTECTED] Subject: UPDATE...WHERE ... IN (SELECT...) Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. Thanks Markus Källander Sub-queries aren't supported in your version of MySQL. I believe you must have version = 4.1 for that to work. -- Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE...WHERE ... IN (SELECT...)
On Wed, 9 Jun 2004 18:25:11 +0200 Markus Källander [EMAIL PROTECTED] wrote: Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. ^^^ 4.1 or higher is required for sub-selects. http://dev.mysql.com/doc/mysql/en/Subqueries.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Webyog releases SQLyog 3.71 SJA 2.0
Webyog has released SQLyog 3.71 SQLyog Job Agent ( SJA ) v2.0 Some of the major features added in SQLyog 3.71 are - -- SQLyog Notification Services - This new feature allows delivery of formatted resultset(s) over email at scheduled intervals. -- SQLyog Database Synchronization Tool now supports 2-way synchronization. -- Now you can execute SJA jobs from SQLyog using Tools - Job Manager. -- Improved Online Help. -- Fully compatible with MySQL versions 3.23.58 to 5.x. You can download a full featured Trial version of SQLyog at http://www.webyog.com/sqlyog/download.html Regards, Karam __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General log file in v3.23.52
How do I turn on the General log file in version 3.23.52? Can I put an entry in the my.cnf file and reboot? Thanks, Don
inserting works fine but I cant update.
I am having a update problem, I have a table that has 28 columns they are name 1-28. I am able to insert data into the table. When I try to update info into the table it does not work. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
In the last episode (Jun 09), Blake Schroeder said: I am having a update problem, I have a table that has 28 columns they are name 1-28. I am able to insert data into the table. When I try to update info into the table it does not work. Any ideas? Not until you tell us the error message. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not getting matching records
Has anyone seen a post on this issue? If not, can anyone offer any advice? I have a TBL of users and I have created a search screen where you can type in first or last name and it will retrieve the appropriate records. Here is the statement: Select * from STUDENTS WHERE FName LIKE '% .$_REQUEST['searchit']. %' OR LName LIKE '% .$_REQUEST['searchit']. %' OR idStudent LIKE '% .$_REQUEST['searchit']. %' The statement works great for the most part. However, it is a bit sporadic. For example, I type in my name (because I know I am in the DB and it will NOT pull back any results. I even ran this command from the UNIX box directly and it will not work. So I have another page which pulls ALL records from another TBL and joins the USER TBL and I AM LISTED!! Here is the statement for that page: Select * FROM CAMPREG INNER JOIN STUDENTS on STUDENTS.idStudent = CAMPREG.idStudent ORDER BY LName; Again, the first one will NOT retrieve my name..the second one will list me in the master list. Help :-)
Re: inserting works fine but I cant update.
Sorry Query UPDATE table SET 1 = '1' WHERE id = '1' |#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = '1' WHERE id = '1'' at line 1 -Blake | Dan Nelson wrote: In the last episode (Jun 09), Blake Schroeder said: I am having a update problem, I have a table that has 28 columns they are name 1-28. I am able to insert data into the table. When I try to update info into the table it does not work. Any ideas? Not until you tell us the error message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
I am having a update problem, I have a table that has 28 columns they are name 1-28. I am able to insert data into the table. When I try to update info into the table it does not work. Any ideas? 1) What are the QUERY you´re using to UPDATE datas? 2) What is the error message? 3) What are the version of MySQL you are using? 4) Do you have GRANT privileges to UPDATE data in such table? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
Blake, Query UPDATE table SET 1 = '1' WHERE id = '1' |#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = '1' WHERE id = '1'' at line 1 Is the column 1 string type? If it´s numec type. You should use SET 1=1 instead of SET 1='1' Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
The type is a varchar. -Blake Ronan Lucio wrote: Blake, Query UPDATE table SET 1 = '1' WHERE id = '1' |#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = '1' WHERE id = '1'' at line 1 Is the column 1 string type? If it´s numec type. You should use SET 1=1 instead of SET 1='1' Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update problem
You have to use UPDATE MyTABLE set MyTABLE.1 = myValue. Otherwise you are saying that the numeric value 1 = some other numeric value. God Bless GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
In the last episode (Jun 09), Blake Schroeder said: Query UPDATE table SET 1 = '1' WHERE id = '1' |#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = '1' WHERE id = '1'' at line 1 Mysql is seeing the 1 as a literal number 1. You can force it to be a column/table name by putting backtics around it: `1`. You will probably be happier if you change the column names to something that doesn't require special treatment, though, like col1 .. col28. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update problem
Paul Thanks alot that help out alot. -Blake Paul McNeil wrote: You have to use UPDATE MyTABLE set MyTABLE.1 = myValue. Otherwise you are saying that the numeric value 1 = some other numeric value. God Bless GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
Dan Great suggestion also. -Blake Dan Nelson wrote: In the last episode (Jun 09), Blake Schroeder said: Query UPDATE table SET 1 = '1' WHERE id = '1' |#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = '1' WHERE id = '1'' at line 1 Mysql is seeing the 1 as a literal number 1. You can force it to be a column/table name by putting backtics around it: `1`. You will probably be happier if you change the column names to something that doesn't require special treatment, though, like col1 .. col28. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data into 2 tables and set id
Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting table types
Hello, I was wondering what happens when you change table types. Does MySQL automatically get rid of the old table files? I'm looking into changing out some tables from MyISAM to InnoDB. I'm not tight on space, but I wasn't sure how it handled the conversion, since the storage location changes with this. -- Greg Macek | Senior IT Manager Marketing Resources, Inc. Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE...WHERE ... IN (SELECT...)
Hi Markus, You cannot use sub-query in mysql 3.23.49 Regards, Laercio Cavalcanti. -Original Message- From: Markus Källander [mailto:[EMAIL PROTECTED] Sent: quarta-feira, 9 de junho de 2004 13:25 To: [EMAIL PROTECTED] Subject: UPDATE...WHERE ... IN (SELECT...) Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. Thanks Markus Källander -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
J S said: Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger What language did you use to do the parsing. If it was perl I'd recommend looking at using perl's DBI interface and doing it progromaticaly. Do you have any idea of how many different url's you have. Basic steps: Get record, check to see if url is in database, if it is get the url_id. if not insert it and get the generated url_id. insert the user record using the url_id you now have. repeat until you run out of records. Not elegent but it will get the job done. Note look into documentation on how to get the new url_id after you do an insert. Its in the DBD::mysql for perl. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unauthenticated user
Hello, I have had a few systems that began to generate max connection errors. When I run a processlist, there are a lot of the following entries: 5899 | unauthenticated user | xx.xx.xx.xx | NULL | Connect | NULL | Reading from net | NULL | When I restart mysql the entries are the same, except for the State colums. They are populated with login. These servers are Redhat 3 systems and have the 2.4.21-9.0.3.EL kernel. Has anyone seen these unauthenticated user entries and where they come from? Thanks, John G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication died
Hello - I just upgraded my master to 4.0.20 from 3.23.54 (ran the mysql_fix_privileges script) My slaves are all 4.0.[16,17,18] All seems well EXCEPT the Slave_IO_Running will NOT start. All my slave a stuck at the point prior to the upgrade. Error logs reveal nothing out of the ordinary. I'm baffeled, anybody have some insight??? Thanks Michael
A Table with a timestamp as transaction date and primary key...
I read some articles that the use of SQL's TIMESTAMP in a table is use for recording the actual date/time that the row was inserted and for row(s) that is/are updated. I'm a little troubled by that because I want a table with a timestamp in the first column to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. I also want the TIMESTAMP to be a primary key. So, what is my options? Thanks...
Foreign Key on text field
Hi, I am using MySQL 4.0.17. When I try to create a foreign key on a field of the type text, I get this error: 040609 15:02:31 Error in foreign key constraint of table db_name/tb_name: There is no index in the table db_name/tb_name where the columns appear as the first columns. Constraint: FOREIGN KEY (CEA_key) REFERENCES relation (CEA_key) ON DELETE CASCADE )TYPE=InnoDB; Can I create a foreign key on the text field?? In the parent table the parent column has a index of length 64. Thanks Sudip _ Looking to buy a house? Get informed with the Home Buying Guide from MSN House Home. http://coldwellbanker.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select a Default column Value without a DESC?
Does anyone know of a way to use a SELECT statement to get the default value for a column? A DESCRIBE tabl_name col_name gives the values, but I need it inside a SELECT. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Table with a timestamp as transaction date and primary key...
Scott Fletcher wrote: I read some articles that the use of SQL's TIMESTAMP in a table is use for recording the actual date/time that the row was inserted and for row(s) that is/are updated. I'm a little troubled by that because I want a table with a timestamp in the first column to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. Then you need 2 timestamps. Both get updated on insert, The first gets updated on updates. I also want the TIMESTAMP to be a primary key. Sorry. This would allow only one insert per second. So, what is my options? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Schema Design
At 15:53 +1000 on 06/07/2004, Ligaya Turmelle wrote about Re: Advice on Database Schema Design: I think I get what you are trying to do and it sounds good (though I am still a beginner). The only thing I can think that might cause a problem is since you have more then a couple of tables you MAY have to write joins for all of them . Only you would know if you can do that and/or be comfortable doing it. To get a stronger idea though of your design I would need to see either a logical ER diagram or all of the creates (I am very visual so I like pictures). *shrugs* whether or not you want to pass those along is up to you. Respectfully, Ligaya Turmelle Thanks for the reply. I'm still at the stage where I am deciding what I want the tables to look like before doing the CREATE TABLE statements. I do not think I will need JOINS since it is going to be more of a WHERE t1.x=t2.x (and SELECT t2.y [from the t2.x selected row]) type situation where I am selecting the fields form the support tables based on the link (Foreign Key) field not actually merging/matching tables. Robert A. Rosenberg [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I am converting a form that was originally designed to be Email Submitted into a Submit-to-PHP-Page Form (which will then insert the data into a MySQL Database). I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data. I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with. Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them). There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing. There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName. There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column. To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot QuickDirty PHP page, I will read the table and recreate the Option Statements which would the be CutPasted into the original HTML code replacing the old versions of the tags. The states go though the same Turn into INSERT Commands but there is no need for post processing or HTML Tag replacement. Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity. Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
At 02:34 PM 6/9/2004, you wrote: Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. js, Is this a one time operation? If so, you could create a temporary table with all the fields: BIG_TABLE user_id date_time url_id size url category and use Load Data InFile to load the data into this one table. Then create the two smaller tables using: create table USER_TABLE select user_id, date_time, url_id, size from BIG_TABLE; create table URL_TABLE select url_id, url, category from BIG_TABLE; drop table BIG_TABLE; So there you have it in 3 lines of code. :-) Mike P.S. If you could break the text file into two files to begin with, then two Load Data InFile statements would handle things nicely. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Table with a timestamp as transaction date and primary key...
TIMESTAMP has a resolution of only 1 second. Not good enough as a primary key for most applications. You may want an auto_incrementing int field (or bigint depending on the size of your data) and make that your primary key. Only the first TIMESTAMP column is automatically updated with the update date. If you want to have a static date/time field you can use a datetime field and populate it with the NOW() function during insert. Unfortunately all default values must be constants or you could set the default of the column to be the NOW() function and not have to ever reference that column in an INSERT statement. The fine manual covers your situation fairly well. I suggest you start at : http://dev.mysql.com/doc/mysql/en/DATETIME.html and follow the links to the rest of the articles describing TIMESTAMPS and their uses and drawbacks. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: Fax to: 06/09/2004 04:24 Subject: A Table with a timestamp as transaction date and primary key... PM I read some articles that the use of SQL's TIMESTAMP in a table is use for recording the actual date/time that the row was inserted and for row(s) that is/are updated. I'm a little troubled by that because I want a table with a timestamp in the first column to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. I also want the TIMESTAMP to be a primary key. So, what is my options? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger What language did you use to do the parsing. If it was perl I'd recommend looking at using perl's DBI interface and doing it progromaticaly. Do you have any idea of how many different url's you have. Basic steps: Get record, check to see if url is in database, if it is get the url_id. if not insert it and get the generated url_id. insert the user record using the url_id you now have. repeat until you run out of records. Not elegent but it will get the job done. Note look into documentation on how to get the new url_id after you do an insert. Its in the DBD::mysql for perl. Thanks for your reply William. I am using a perl script. If I have to insert these records one by one it's going to be really slow isn't it? Maybe the quickest way is to parse the logs twice i.e. fill the URL_TABLE first using your procedure above, then on the second run, create a LOAD file for USER_TABLE? js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
J S said:
Hi,
I need some help please! I have 60GB of proxy logs to parse and load
into a mysql database. I've written a parsing script but I'm stuck
now on how to load the data in.
I have a database called PROXY_LOG with 2 tables:
USER_TABLE
user_id date_time url_id size
and
URL_TABLE
url_id url category
The data values I have for each record are:
user_id date_time size url category
for example:
u752359 2004-04-02 12:33:04 3403
http://www.mysql.com/index.html
business
The problem is I'm not sure how to load the data into the 2 tables
and set the url_id. Could anyone point me in the right direction
please?
Many thanks,
js.
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
What language did you use to do the parsing. If it was perl I'd
recommend looking at using perl's DBI interface and doing it
progromaticaly. Do you have any idea of how many different url's you
have.
Basic steps:
Get record,
check to see if url is in database, if it is get the url_id.
if not insert it and get the generated url_id.
insert the user record using the url_id you now have.
repeat until you run out of records.
Not elegent but it will get the job done. Note look into documentation
on how to get the new url_id after you do an insert. Its in the
DBD::mysql for perl.
Thanks for your reply William. I am using a perl script. If I have to
insert these records one by one it's going to be really slow isn't it?
Maybe the quickest way is to parse the logs twice i.e. fill the
URL_TABLE first using your procedure above, then on the second run,
create a LOAD file for USER_TABLE?
js.
How will you get the information for the url-id's? I can see splitting
the logs and using a load file for the url_table (if you can eliminate
duplicates). You can save some time, if you can build a perl hash with
the
$url{urlvalue} = url_id. Test that and only do inserts if you need to.
Hash look up is faster than db query, but you will have to have the hash
in memory. You can use the hash to prepare the USER_TABLE and then load
infile that.
Just thought, url is going to have to be a unique key? You can speed up
the initial inserts by inserting without that key (using the perl hash to
avoid collisions) and then altering table to add the key in. However,
question comes back to do you have enough memory for the hash in perl?
Notice also, that you don't have a rowID equivalent in the USER_TABLE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linking Embedded MySQL on Mac OS X
I am having the same problem that someone else posted about back in December and I didn't see any responses for: http://lists.mysql.com/mysql/156346 Specifically, I am trying to link with libmysqld.a on Mac OS X and am getting 16 undefined link errors such as fixsfdi, floatdisf, udivdi3, moddi3, etc. I have inferred from various google searches that most of these are linked in with a library called libkern on other *nix systems. However, the libkern.h on Mac doesn't list these particular methods and I don't know which library I should include. Regardless of whether you use CodeWarrior, XCode, gcc, or whatever, can anyone point me in the right direction? Thanks, Eric --- Eric Sword Group Logic, Inc. [EMAIL PROTECTED] 800.476.8781 (800.GROUP81) http://www.grouplogic.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication died
Never mind...I've fixed it. Thanks Michael -Original Message- From: DePhillips, Michael P Sent: Wednesday, June 09, 2004 4:17 PM To: '[EMAIL PROTECTED]' Subject: Replication died Hello - I just upgraded my master to 4.0.20 from 3.23.54 (ran the mysql_fix_privileges script) My slaves are all 4.0.[16,17,18] All seems well EXCEPT the Slave_IO_Running will NOT start. All my slave a stuck at the point prior to the upgrade. Error logs reveal nothing out of the ordinary. I'm baffeled, anybody have some insight??? Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EJB - Entity Beans
All: First let me say I know only the most basic of Java stuff. I am working with a Java colleague on the development of a data model (MySQL of course :-) and we seem to run into the problem an Entity Beans cannot support joins. Can anyone tell me if this is true (If so, what use are they for any sort of complex data model?). If no, can you send me some information on how to implement a join with the Beans and some possible resources to consult? My naïveté leads me to believe I should be able to create a container of beans that is the result of any query I can write against the DB. Or something like this anyway! Please help! Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Sudip Shekhawat [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: Foreign Key on text field Hi, I am using MySQL 4.0.17. When I try to create a foreign key on a field of the type text, I get this error: 040609 15:02:31 Error in foreign key constraint of table db_name/tb_name: There is no index in the table db_name/tb_name where the columns appear as the first columns. Constraint: FOREIGN KEY (CEA_key) REFERENCES relation (CEA_key) ON DELETE CASCADE )TYPE=InnoDB; Can I create a foreign key on the text field?? In the parent table the parent column has a index of length 64. Thanks Sudip _ Looking to buy a house? Get informed with the Home Buying Guide from MSN House Home. http://coldwellbanker.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: EJB - Entity Beans
Boyd, First off, I should note that I haven't actually used EJB since about 1.1 or so and have only been casually keeping up with some of the specs. Therefore, some of what I say may be a bit out of date. Please ingest large granules of salt while reading this. What you're referring to is actually CMP Entity Beans (Container Managed Persistence). What you want can be achieved by using BMP Entity Beans (Bean Managed Persistence), where you write JDBC (or JDO or Hibernate or...) code in the Entity bean for accessing the database however you like. Essentially, a BMP Entity bean can be used to represent an arbitrarily complex data model but there are drawbacks -- you have to write and maintain much more code, performance may not be as good as with CMP (or such was the case when I last used EJB), and so forth. I was under the impression that EJB 2.0 introduced a query language (EQL) allowing very sophisticated mappings using a variation of SQL that was more object-centric. Is EQL up to the task? Additionally, depending on the app server you're using, there may be proprietary approaches available to you. As I understand it, JBoss integrates very nicely with Hibernate -- and I seem to recall reading that Hibernate is set to become the officially sanctioned persistence framework for EJB 3.0. I have to wonder however, if using EJB is really the right course of action for your project. EJB is not a tool for beginners, and the areas where it can be usefully applied are far narrower and harder to identify than the Sun hype machine would lead you to believe. It's not a panacea, or a general purpose solution no matter how much it may resemble one. Perhaps your needs would be better served by ditching EJB and just using a more general persistence framework by itself? -JF -Original Message- From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 5:14 PM To: [EMAIL PROTECTED] Subject: EJB - Entity Beans All: First let me say I know only the most basic of Java stuff. I am working with a Java colleague on the development of a data model (MySQL of course :-) and we seem to run into the problem an Entity Beans cannot support joins. Can anyone tell me if this is true (If so, what use are they for any sort of complex data model?). If no, can you send me some information on how to implement a join with the Beans and some possible resources to consult? My naïveté leads me to believe I should be able to create a container of beans that is the result of any query I can write against the DB. Or something like this anyway! Please help! Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Sudip Shekhawat [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: Foreign Key on text field Hi, I am using MySQL 4.0.17. When I try to create a foreign key on a field of the type text, I get this error: 040609 15:02:31 Error in foreign key constraint of table db_name/tb_name: There is no index in the table db_name/tb_name where the columns appear as the first columns. Constraint: FOREIGN KEY (CEA_key) REFERENCES relation (CEA_key) ON DELETE CASCADE )TYPE=InnoDB; Can I create a foreign key on the text field?? In the parent table the parent column has a index of length 64. Thanks Sudip _ Looking to buy a house? Get informed with the Home Buying Guide from MSN House Home. http://coldwellbanker.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
accessing mysql 4.1.2 unicode features using C API
Hi, I am really pleased with the new unicode features of MySQL4.1.2 alpha release. I am just a little surprised that there is still only one API call which returns the character set??? (And it returns the collating encoding instead of the real character set?) I was wondering if there is a way to access all unicode features (DATABASE default character set/collating, TABLE character set/collating and COLUMN character set/collating) from the C API? Also, any mysql_string_literal() support for unicode would also be appreciated. I looked all over for more info on this with no success, If anyone was able to use unicode features from C (or through any other language which uses the C API), please drop me a note, regards, Paul
RE: Multi-row INSERTs
Russ, We use #2 currently, and we are actually about to switch back to the inserting them one at a time. The reason is very simple. In our case we have a insert statement that will insert a maximum of 600 entries at a time. But we could have up to 25 different programs running that could possibly be doing that its own insert of 600 records. The problem is that say the first one takes 3 seconds to insert all 600, but 1 second after the first one starts the second program tries to insert, well, it will need to wait for the first one to finish. So what ends up happening, if all 25 programs try to insert at the same time, all 25 inserts can take about 5 minutes because they are all waiting on each other. Now if you didn't need the auto_increment id, then you could just use an insert delayed which would be a million times faster for the program itself, but not necessarily for mysql. Now if you do the one insert at a time, and each of the 25 programs started inserting one at a time, in theory they would all finish at the same time. Would it be faster then the massive inserts? Again it should be slower, but we have found that it's faster in the long run. But it could also be that our table we are inserting these records into has 252 million rows in it right now. So I would definitely benchmark it yourself, before taking my word for it. We have run into the same problem with INSERT ... ON DUPLICATE KEY UPDATE..., with a small table when it's mainly doing inserts, it's super fast. But with a table with 44 million rows and only 3 columns it takes about 1-2 seconds to do the update part of the insert. But again, we found this by noticing that when the table was small or it's doing inserts the command is super fast. But as time goes on, it gets slower. Donny -Original Message- From: Russ Brown [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 7:45 AM To: [EMAIL PROTECTED] Subject: Re: Multi-row INSERTs On Wed, 9 Jun 2004 12:58:01 +0200, Jigal van Hemert [EMAIL PROTECTED] wrote: Anyway, my question is this. If I do a single-statement multi-line insert, are the auto-increment IDs of the rows inserted guaranteed to be sequential? Bear in mind also that I'm using InnoDB tables here. Conversely, if I know for a fact that it is not guaranteed, I know that I need to think of something else. :-) Will locking the table work for you? If you lock the table for writing no other process can slip a query in between your queries for certain... Regards, Jigal. Unfortunately locking the table isn't an option as the table is being accessed extremely regularly by other clients performing similar inserts. The key to this is speed and overhead: at present I'm inserting the rows individually and recording each row's ID as I go. However I want to be able to reduce the number of queries involved, so I've though of two possibilities: 1) If the inserted rows have sequential IDs in the same order that they appeared in the INSERT statement, I can do them all in one go, use LAST_INSERT_ID to get the ID of the first and derive the rest by incrementing in the application logic. 2) Insert them all in one statement and then select them back to get each row's ID. Now, I know that 2) will work, and it will allow me to reduce the number of queries per process from N (where N is on average about 9) to 2. However, if 1) will work it will allow me to reduce the number of queries to 1, (plus a call to LAST_INSERT_ID), and wouldn't involve having to match up the rows from the second query in 2). If nobody knows the answer I'll just go with 2, but I thought it was an interesting bit of trivia in addition to being useful to my specific circumstance. Thanks. -- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
