C API field type values are inacurate - help
I have noticed that when using the C API that in certain instances the field 'type' constant is not correct. When a value is returned from a 'TEXT' type field it is reported as a 'BLOB' type. I realize that a TEXT is an extension of a BLOB, but, since they are different types according to the schema, why are they not different when returned via the API? Actually there are no TEXT type constants. Also, when a command with a union in it, even CHAR types are returned as BLOB types. Is this a known issue? Is there a planned fix? This is particularly important for middle ware that knows nothing about the schema, it must rely on what the type is according to the API. Can any of the developers comment on this? Why are there no TEXT type constants in mysql_com.h? How hard would it be to add them? I would really like to stay with a 'standard' distribution API, but if not, how to I modify the API to provide this? thanks alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding NULL records
On Oct 30, 2006, at 3:27 PM, Jay Blanchard wrote: [snip] I am trying to find records where the value of a filed is NULL. I know that there are records that have null values but the result is always an empty set. eg: select test_id from tests where test_id=NULL always returns an empty set when there are in fact records that have a null value for test_id. Is there some trick to finding null valued records in MySQL? This same sql has always worked on any other dbms I have used. [/snip] Of course this will return an empty set because you have only selected the test_id, try this; SELECT * FROM tests WHERE test_id IS NULL Yes, that works, but I was also trying SELECT * instead of just the key field (just a typo in the example). The problem was in the equal sign versus the 'IS' operator. Any reason why MySQL does not honor =NULL? Seems kind of odd. alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding NULL records
I am trying to find records where the value of a filed is NULL. I know that there are records that have null values but the result is always an empty set. eg: select test_id from tests where test_id=NULL always returns an empty set when there are in fact records that have a null value for test_id. Is there some trick to finding null valued records in MySQL? This same sql has always worked on any other dbms I have used. thanks alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating new table from distinct entries
MySQL 4.1.21-community-nt I have a table in my database that has a Primary key on 2 fields (MyID and MyChange) and a field that indicates if there is a problem with the record (MyError)- I want to create a new table that only has unique MyIDs and where there is more than 1 I only want the record with the highest MyChange number. The table has about 50 fields so I have the following code:- CREATE TABLE mystats SELECT *, DISTINCT MyID FROM oldstats WHERE MyError IS NULL ORDER BY MyChange DESC ; but I am getting an MySQL error #1064. Any ideas or suggestions as to where I am going wrong? Alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hello everyone
my name is alan madsen. while i am very well grounded in complex systems and database management that is archaic by today's standards, i am looking at a creating a server-side php/mysql environment for a very simple database application with only the experience of recent light reading and knowing that i've successfully installed a wamp serverkit on windows 2k (uniform server) that includes mysql5: The Uniform Server is a lightweight server solution for running a web server under the WindowsOS. 5.79MB! It includes the latest versions of Apache2, Perl5, PHP5, MySQL5, [and] phpMyAdmin http://sourceforge.net/projects/miniserver/ running on a laptop, i've seen this installation's instance of apache serve web pages to the net. very nice. i'd like to create, load, and maintain, a mysql database table containing fewer than 8,000 records, each with 5 fields (rows?) - lengths ranging from 10 bytes to 80 bytes - of character data, one field of which would be used as data and as a isamkey (com- pound keys would be nice, but they are not necessary). assuming a working installation of mysql5 and that a csv data file exists, will someone outline what steps are necssary to: 1. create such a table, 2. load it, and 3. the mysql methodology to access it via php/mysql_isam? any comment would be appreciated. regards, -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hello everyone
my name is alan madsen. while i am very well grounded in complex systems and database management that is archaic by today's standards, i am looking at a creating a server-side php/mysql environment for a very simple database application with only the experience of recent light reading and knowing that i've successfully installed a wamp serverkit on windows 2k (uniform server) that includes mysql5: The Uniform Server is a lightweight server solution for running a web server under the WindowsOS. 5.79MB! It includes the latest versions of Apache2, Perl5, PHP5, MySQL5, [and] phpMyAdmin http://sourceforge.net/projects/miniserver/ running on a laptop, i've seen this installation's instance of apache serve web pages to the net. very nice. i'd like to create, load, and maintain, a mysql database table containing fewer than 8,000 records, each with 5 fields (rows?) - lengths ranging from 10 bytes to 80 bytes - of character data, one field of which would be used as data and as a isamkey (com- pound keys would be nice, but they are not necessary). assuming a working installation of mysql5 and that a csv data file exists, will someone outline what steps are necssary to: 1. create such a table, 2. load it, and 3. the mysql methodology to access it via php/mysql_isam? any comment would be appreciated. regards, Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GUI Tools for administering and reporting
All, Is there a recommended GUI that will administer multiple MySQL 4.x databases. I need the ability to monitor connections, health, users, etc. and notify me when there is a problem with an instance. Regards, Alan L. Fisher GPI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoiDB Backups
All, I would like to knew if anyone knows of a way to automate innoDB Hot Backups of several databases that can be set to occur at off hours. Also, is it possible to run several backups at one time. I am using MySQL 4.1.x on a Solaris system. Thanks, Alan Fisher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Replication
All, I have been tasked with setting up DR between two different sites. Is there a favorite tool or GUI that someone could recommend for this task? Regards, Alan L. Fisher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Major Difference in response times when using Load Infile utility
> Test 1 > Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. > > Test2 > Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Is this an InnoDB database by any chance? If it is, and it is a clean import, then disable the FOREIGN_KEY_CHECKS. SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; This is a small tip i picked up on the MySQL documentation that someone had left in the comments and has been to date one of those tips that has literally saved DAYS of my life. a ps Remember to put them back on again after you finish the import SET AUTOCOMMIT = 1; SET FOREIGN_KEY_CHECKS=1; -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: cluster or replication
> I've 10 server in differents locations, I want to make a broadcast, I > would like to have always datas synchronized between this network, each > database have the same tables and same structure. Each insert or update > in database will be executed for each server within this broadcast, > what's the best choice ? At the moment, replication is your best option. Replication works very nicely over wide-area-networks, where the bandwidth between each node could be dramatically different. The only constraint you'll have here is that there can only be one MASTER that has to accept all the INSERT/UPDATE/DELETE (or anything that will make the data change). All other nodes would be considered SLAVEs and be READ-ONLY. Hope this helps. a -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LVM-snapshot + mysqldump -- is this a reasonable backup strategy?
> This recipe is intended to minimize the impact on ongoing database > operations by inhibiting writes only during a relatively speedy > operation (creating a snapshot). The long dump operation can ... This seems to be a rather long winded way of doing this. Why not replicate the database and therefore not have to bring it down ever. I wrote a blog entry about this very thing, and had some interesting comments back on http://blog.spikesource.com/mysql_hotbackup.htm Hope this helps, alan -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HTML in MySQL?
> If you use textarea field of a form, it produces "null" characters (\n) in the > end of every string. I recommed to replace them with "" tags before > writing into the database. It'll help to avoid output problems. Use > preg_replace(); for it. Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. And i would highly recommend *not* replacing them with tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance of DB with many tables
> But what about INNODB? And are there > table engine-independent implementation details which might cause > performance problems for a database with many tables? This thread got me wondering how many file-handles are open for INNODB tables since it is only one large file on the file system. A quick look at our production server that is running approximately 50 tables within an INNODB instance, I see the number of open file handles is significantly less than 50. Which is what i would have expected. Looking at the other table formats, I do see a file handle open for every single table (more than one file handle since there is multiple files that describe a table under MyISAM). BTW on Linux systems you can check this using: % lsof -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: show master/slave status privileges ?
> I have made a user with the following command: > GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r' Silly question Morten, and I am sure you have probably done it, but you are definitely running: % mysql> FLUSH PRIVILEGES; -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup and restore a database in a query ？
> Could we do a database dump/backup in a query like below ? > mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u > root -p -C mydb>mydbfile > or restore a database in a query like below ? > mysql.exe -h 192.168.0.1 -u root -p -C mydb > Then we can do backup and restore in GUI mode without call > mysqldump.exe,mysql.exe in character mode . Ah okay now i see what you are trying to do. The [mysqldump] is a utility that sits outside of the main mysql engine. You cannot invoke this from within the mysql shell from the best of my knowledge. As for pulling in backups from the mysql shell, then yes that is possible using a number of techniques: % mysql> SOURCE [path to your file] or % mysql> LOAD DATA [path to your file] Links to more information: http://dev.mysql.com/doc/mysql/en/load-data.html http://blog.spikesource.com/mysql_hotbackup.htm hope this helps, alan -- Alan Williamson, Technology Evangelist SpikeSource Inc. t: 650 249 4279 b: http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Security and Updating/Retrieving Values
MySQL Server v4.1.x Is there a table in any instance of mysql that I write some queries and insert/update statement against for moving databases between systems? Any assistance would be appreciated.
Update a Field in a table to Uppercase
Does anyone have a sample of a simple update statement I can run to update a field in a table to all uppercase values. Any help or web references are appreciated.
Porting Tables Between Windows and Linux Version of MySQL
We have 2 distinct groups of developers - Windows vs. Linux. Our production server is going to run on Solaris. The issue is that all developers run there own instance of MySQL server Linux or Windows based and the same version 4.1.7. Also, each developer has the database for their particular project so everything from their MySQL server instance would be ported. As we move the systems to the Solaris production server is there any simple way to port all the work onto this platform (backup/restore, etc.)? Any help or weblink, reference material recommendations are appreciated.
Re: Right join after inner join has wrong result
Hi, On 2004-08-04 21:20, gerald_clark wrote: [...] SELECT C.B FROM A INNER JOIN C ON C.A = C.A Perhaps you meant: INNER JOIN C on C.A = A.A RIGHT JOIN B ON B.B = C.B WHERE C.B IS NULL [...] Yes, thanks. I didn't realize that mysqlbug sends the report to a mailing list. I have proceeded to submit it as bug 4893 anyway. The syntax there should be correct. -- Regards, Alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Right join after inner join has wrong result
>Description: See how-to-repeat. >How-To-Repeat: CREATE TABLE A (A int); CREATE TABLE B (B int); CREATE TABLE C (A int, B int); INSERT INTO A VALUES (1),(2); INSERT INTO B VALUES (1),(2); INSERT INTO C VALUES (1,1); SELECT C.B FROM A INNER JOIN C ON C.A = C.A RIGHT JOIN B ON B.B = C.B WHERE C.B IS NULL Expected Result: A3 -- 2 Actual Result: A3 -- 1 2 2 Tried in postgresql and it works like the expected result. >Fix: none >Submitter-Id: >Originator:Alan Tam >Organization: >MySQL support: none >Synopsis: Right join after inner join has wrong result >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-4.0.20 (Source distribution) >Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.20, for pc-linux-gnu on i386 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.20-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 1 day 1 hour 3 min 32 sec Threads: 22 Questions: 237517 Slow queries: 0 Opens: 1720 Flush tables: 1 Open tables: 64 Queries per second avg: 2.633 >C compiler:i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-4) >C++ compiler: i386-linux-g++ (GCC) 3.3.4 (Debian 1:3.3.4-4) >Environment: System: Linux delta 2.6.7-1-686 #1 Thu Jul 8 05:36:53 EDT 2004 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.4/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc i486-linux Thread model: posix gcc version 3.3.4 (Debian 1:3.3.4-3) Compilation info: CC='i386-linux-gcc' CFLAGS='' CXX='i386-linux-g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 2004-06-20 00:46 /lib/libc.so.6 -> libc-2.3.2.so -rw-r--r-- 1 root root 1243856 2004-05-26 02:40 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2640410 2004-05-26 02:40 /usr/lib/libc.a -rw-r--r-- 1 root root 204 2004-05-26 02:16 /usr/lib/libc.so Configure command: ./configure '--build=i386-linux' '--host=i386-linux' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb' 'build_alias=i386-linux' 'host_alias=i386-linux' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL testing programs
First of all, thank you for providing a really informative list. I have listened to many of the suggestions going back and forth and have learnt a lot in the past few months. I have a question. We have a fresh MySQL installation on a new box, and we want to basically start tuning it to our particular application. Can some advise what tools we could use to basically send shed loads of [differing] queries to it in a predictable/repeatable manner. Would like a tool that produces some statistics on the results of each test. Allowing us to tune/tweek, and rerun to see the effect of our results. thanks alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Linux
Thank you, a much reasoned and sensible reply. This is information people can use, as oppose to the posts that 'say well its okay for me, you must be stupid' types. ;) Dan Nelson wrote: In the last episode (Apr 06), Alan Williamson said: the most popular would have been Red Hat, which doesn't have this limit you speak of, even plain vanilla install (no twiddling needed). Not to spoil a perfectly good pontification ... but i have to say that we have a Redhat8 distribution running on a Dell PowerEdge Server and when Apache gets to the 2GB size on its access file, it does indeed stop. This is not old hardware (12months old). That is because although Linux binaries can access files over 2gb, they do not do so by default. Apache was probably not compiled with the required defines (-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64), so that's why it stops at 2gb even though both the kernel and filesystem most likely do support larger files. So the question still remains. What would happen in MySQL when that file isn't allowed to grow any further? Mysql's configure script checks for systems that require special flags to access large files, so no mysql binaries should have this problem on modern Linux systems (i.e. any 2.4 kernel) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Linux
dan wrote: the most popular would have been Red Hat, which doesn't have this limit you speak of, even plain vanilla install (no twiddling needed). Not to spoil a perfectly good pontification ... but i have to say that we have a Redhat8 distribution running on a Dell PowerEdge Server and when Apache gets to the 2GB size on its access file, it does indeed stop. This is not old hardware (12months old). So don't be spouting any sweeping statements. If your distribution doesn't have that limitation, then fantastic, good for you. But for others it is indeed a real limitation. The original question was indeed a geniue one, and while the poster accidently typed in the wrong size, i wouldn't be so quick to jump all over him. So the question still remains. What would happen in MySQL when that file isn't allowed to grow any further? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CURDATE() bug?
RTFM! and what was the reason for this rudeness? Can't you explain yourself without descending into this sort of language? I do read the manual, and it is this reason i posted to the list. Clarity is a wonderful thing, and sadly the manual isn't clear on this matter. I stand by my original statement. If you believe it not to be a bug then so be it; we agree to disagree. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CURDATE() bug?
Does CURDATE() support numeric addition like this? Or is the "+0" purely a casting-hack to get the right format. Its not meant as pure addition. Yes, hav a look at http://www.mysql.com/doc/en/Date_and_time_functions.html for explaination further for addition, use date_add(curdate(), interval 1 day) or watever i know how to add dates, that wasn't the point of the post! but thank you nonetheless! ;) I was merely looking for clarification. If "+0" is purely a casting hack then it should be highlighted as such so people don't assume. By using "+0" does suggest its a numerical addition and therefore why stop at "0". Why not "1" etc etc etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CURDATE() bug?
Could anyone tell me if this is a bug or not. SQL: SELECT CURDATE()+0; RESULT: 20040331 Thats good. However consider this: SQL: SELECT CURDATE()+1; RESULT: 20040332 Not so good. Infact with this version any WHERE clauses you would put this in, fails to bring back the right result. Does CURDATE() support numeric addition like this? Or is the "+0" purely a casting-hack to get the right format. Its not meant as pure addition. Thoughts? thanks alan -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Thanks for that Chris, interesting thoughts. For clarification, there is *NO* UPDATEs running on this table. Not a single one! :) Many more SELECTs than INSERTs Chris Nolan wrote: Alan Williamson wrote: A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks How often are DELETE and UPDATE statements executed on this table? MyISAM is damned quick when it comes to workloads that always result in INSERTs ending up at the end of the tablespace. As MyISAM can allow SELECTs to execute while INSERTs are in progress at the end of the table (i.e When no DELETEs have been issued) thanks to it's versioning you'll find that thousands of queries a second is quite doable on modest hardware. That said, InnoDB's speed defies belief. Given that it's multiversioned, transactional and able to lock at the row level the fact that it's even in the same leauge as MyISAM performance-wise for these sorts of loads is impressive. When you have UPDATEs flying around, InnoDB may edge MyISAM out for heavy workloads. Many places have moved to InnoDB due to concurrency issues of that type. In summary, test test test! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs. INNODB for a single blob table
A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just the list please!
Can people please just email the list and not the person *AND* the list!!! i get duplicate emails and its very annoying to what is a great list so far. kinda puts me off from answering peoples questions! thanks! :) - Original Message - From: "Alan Williamson" <[EMAIL PROTECTED]> To: "cvarda" <[EMAIL PROTECTED]> Sent: Tuesday, March 16, 2004 5:30 PM Subject: Re: Blocking INSERT/UPDATE on SLAVE (replication) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using OR
Keith wrote: is there any alternative to using OR for selecting between values? ie: pla.type='1' OR pla.type='2' OR pla.type='3' OR pla.type='4' Keith for stuff like this i try and arrange things in numerical blocks so i am doing selects like pla.type >= 1 AND play.type <= 4, probably not needing both constraints depending on how you arrange your query. I also had a table that had two columns that needed an OR on. I moved it to another table, and used a join on those two tables and boy did that make a difference. Removing the OR makes a huge performance gain. hope this helps, i'll let the real SQL experts jump in here and give their response. -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC timeout after 4.0.8 -> 4.0.18 upgrade?
With respect to this problem, I am not running on Windows, but Redhat, and seeing this problem often. Which part of: http://www.mysql.com/documentation/connector-j/index.html#id2803835 should i be looking at? thanks alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: procedure entry point question
Thanks to anyone who was looking at this question. I have resolved the issue myself. During a prior MySql installation (earlier version) I moved the LibMysql.dll to the WINNT/system32 directory so that it was available throughout the system. After upgrading to a newer version of MySql, all of my programs were of course still using this outdated .dll because I failed to move the new version's .dll to the WINNT/system32 directory. If I could make a suggestion for the Windows installers, it would be that the newest LibMySql.dll be copied to the system directory to avoid this easily made error. Thanks. - Original Message - From: Alan To: [EMAIL PROTECTED] Sent: Saturday, February 28, 2004 5:07 PM Subject: procedure entry point question Hello, I'm working with MySql 4.0.18 on Windows2000 and have a question about the C API function mysql_real_escape_string( ). The program I'm working on compiles and links flawlessly, but at runtime, I get a system pop-up with the error: "The procedure entry point mysql_real_escape_string could not be located in the dynamic link library LIBMYSQL.dll." This is the case whether I compile the program using Cygwin or MSVC, and so far only occurs when I include this one function call. I am hoping that there is just different .dll that I need to download to solve this problem. Any help would be appreciated. Thanks __ ICQ#: 135430808 Current ICQ status: + More ways to contact me __
procedure entry point question
Hello, I'm working with MySql 4.0.18 on Windows2000 and have a question about the C API function mysql_real_escape_string( ). The program I'm working on compiles and links flawlessly, but at runtime, I get a system pop-up with the error: "The procedure entry point mysql_real_escape_string could not be located in the dynamic link library LIBMYSQL.dll." This is the case whether I compile the program using Cygwin or MSVC, and so far only occurs when I include this one function call. I am hoping that there is just different .dll that I need to download to solve this problem. Any help would be appreciated. Thanks __ICQ#: 135430808 Current ICQ status: + More ways to contact me __
Re: index change moving files to other computer?
On Wed, Feb 04, 2004 at 07:21:30PM +, [EMAIL PROTECTED] wrote: > The older system is choosing to use a different index. I would suggest Any idea why it would choose this? Shouldn't mysql keep using the same indexes? > running an analyze on your new tables and see if you can get the newer > system to use the same Postsindex8 index. I ran myisamchk -a on this, which according to the documentation is the same. No changes. I did downgrade to 4.0.14 however and happy happy day it's back up to the speed that I was used to! Now I'd love to know why I can't upgrade :) Sorry for being such a lamer n00b, but it's not my DB and mysql has always "just worked" for me (though I don't use many 600k row tables :) Alan -- Alan <[EMAIL PROTECTED]> - http://arcterex.net "There are only 3 real sports: bull-fighting, car racing and mountain climbing. All the others are mere games."-- Hemingway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index change moving files to other computer?
Hi folks. I'm in the midst of moving from a debian unstable system to a gentoo system and I'm having some problems getting mysql to give me the same performance. On the new gentoo system (with more hardware) a fairly complex query (a search on a UBBThreads forum) is taking 10+ seconds to complete, while on the debian system it's in the 0.01s range. Before I'm critisized on my distro choice, it appears I've traced part of the problem down with EXPLAIN: (sorry about the width :( ) New but slower system: +---+++-+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+--+-+ | t1| ref| w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex7 | 3 | const | 6607 | Using where; Using filesort | | t2| eq_ref | indx1 | indx1 | 100 | t1.B_Board|1 | | | t3| eq_ref | PRIMARY,indx3 | PRIMARY | 4 | t1.B_PosterId |1 | | +---+++-+-+---+--+-+ Old but faster system: +---+++-+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+---+-+ | t1| range | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex8 | 4 | NULL | 19645 | Using where | | t2| eq_ref | indx1 | indx1 | 100 | t1.B_Board| 1 | | | t3| ref| PRIMARY,indx3 | indx3 | 4 | t1.B_PosterId | 1 | | +---+++-+-+---+---+-+ The big thing here as I've read and understood it is that the gentoo system is "Using filesort", which is horribly slow compared to plain old where and indexes. However, I've made no changes to the database files, just copied /var/lib/mysql/ from the old system to the new. The faster box is a 4.0.14 system and the slower is using 4.0.16. Based on my reading of some of the docs on mysql.com using filesort is used when mysql can't use indexes for the order by clause. Would these indexes not be there and still available when moved to the new system? It seems very strange to me. Oh, and I also tried dumping just that database and re-importing it from the (sql) dump file, with the same results. Please help! BTW, specs on the systems: Old: debian unstable running linux 2.4.24 with mysql 4.0.14 XP1800 with 1G ram on two IDE drives with software RAID1 New: gentoo stable, kernels used were 2.4.24, 2.4.25_pre6, and 2.6.1 with and without preempt. Mysql tried 4.0.16 static and dynamic with various cflags and 4.0.17 binaries from mysql.com. my.cnf has been set to the same as o nthe old box, the default config, and the huge, large and medium sample configs, all with the same results. Many thanks. alan -- Alan <[EMAIL PROTECTED]> - http://arcterex.net "There are only 3 real sports: bull-fighting, car racing and mountain climbing. All the others are mere games."-- Hemingway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TCP Wrappers in 3.23.58
Recently I became aware that MySQL 3.23.58 on my Solaris 8/x86 box was ignoring my /etc/hosts.allow and /etc/hosts.deny files and permitting unrestricted connection access to my MySQL daemon. I use entries such as "mysqld: 127.0.0.1" and "mysqld: ALL" in hosts.allow and hosts.deny, respectively. After attempting to configure with --with-libwrap=/path/to/tcp_wrappers, I would get a failure with make. I found an article [1] which presents an updated tcpd.h file to use instead of the one native with tcp_wrappers_7.6_ipv6 which I have been using. Now configure recognizes and uses tcpd.h, but it seems that, unless I've looked in the wrong places, sql/mysqld.cc doesn't include wrapper checks unless HAVE_LIBWRAP is defined at compile time, and in my case it is not. Has anyone else been successful including wrapper support with tcpd.h and libwrap.a from the tcp_wrappers compiled source? [1] http://linuxfromscratch.org/pipermail/blfs-dev/2002-December/001770.html -- Alan W. Rateliff, II: RATELIFF.NET Independent Technology Consultant :[EMAIL PROTECTED] (Office) 850/350-0260: (Mobile) 850/559-0100 - [System Administration][IT Consulting][Computer Sales/Repair] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] Trouble With Counting New Documents With Complex Query
In all probability it's "(x_section.Status & 1) = 0" and
"(x_instance.Status & 255) = 0" that's giving you the problem.
Unfortunately this is a database schema problem not a query fix. By putting
a computation on a field into the WHERE clause, you're forcing the database
to do that computation on every record that meets the other WHERE criteria
(given that the optimizer is working well and you have the right indexes --
worst case you're doing those computations on *every* record in the table).
Generally speaking, bit masks incur performance penalties in return for
space gains... but storage is cheap and time isn't. This penalty is worse
for databases. The general rule is that bit-mask fields and databases are a
bad combination. If you break bit zero out of x_section.Status into say
x_section.isEmpty (defined as a tinyint or char(1) if you are really
worried about space), then add an index on x_section.isEmpty, then you'll
get the performance gain. Repeat as required with x_instance.Status.
If breaking out the bit masks is going to be really painful, then consider
getting a result set without the mask criteria in the query, make sure the
remaining fields in the WHERE are indexed, then filter out the results you
want in the script. This depends on which percentage of the result set you
eliminate with those masks... if the result set is 10% bigger, then this
works, if the result set is 10,000% bigger then take the pain and go break
up the bit-mask fields.
At 2004/01/02 12:59, Adam i Agnieszka Gasiorowski FNORD wrote:
I need help width formulating the most
effective (in terms of processing time)
SQL query to count all the "new"
documents in the repository, where "new" is
defined as "from 00:00:01 up to 23:59:59
today". My current query does not give me
satisfactory results, it creates a visible
delay in rendering of the main page of one of
the departments (Drugs) :8[[[
(at least I, for now, think it's the culprit).
It's for the https://hyperreal.info >
site, see for yourself, notice the delay
https://hyperreal.info/drugs/go.to/index >.
Currently I ask MySQL to (offending
PHP fragment follows, I hope it is self-
explanatory).
$suma = 0;
$pytanie = "SELECT COUNT(DISTINCT x_article.ID) AS CNT ";
$pytanie .= "FROM x_article ";
$pytanie .= "LEFT JOIN x_instance ";
$pytanie .= "ON x_article.ID = x_instance.Article ";
$pytanie .= "LEFT JOIN x_section ";
$pytanie .= "ON x_instance.Section = x_section.ID ";
$pytanie .= "WHERE (x_section.Status & 1) = 0 "; // not empty
$pytanie .= "AND (x_section.Dept = 2 OR x_section.Dept = 5) "; // Drugs,
NeuroGroove
$pytanie .= "AND (x_instance.Status & 255) = 0 "; // not hidden, etc
$pytanie .= "AND UNIX_TIMESTAMP(x_article.Date) BETWEEN " . mktime(0, 0,
1, date('m'), date('d'), date('Y')) . " AND UNIX_TIMESTAMP(NOW()) ";
$pytanie .= "GROUP BY x_article.ID";
$wynik = mysql_query($pytanie);
while ($tmp = mysql_fetch_array($wynik))
{
$suma += $tmp['CNT'];
}
if ($suma)
{
// pretty-printing of the result
$dzisdodano = str_pad((string)(int)$suma, 4, '0', STR_PAD_LEFT);
}
else $dzisdodano = '';
?>
The table layout is as follows:
mysql> DESC x_article;
+-+--+--+-+--++
| Field | Type | Null | Key | Default |
Extra |
+-+--+--+-+--++
| ID | int(10) unsigned | | PRI | NULL |
auto_increment |
| Name| varchar(255) | YES | MUL |
NULL ||
| Description | varchar(255) | YES | |
NULL ||
| Keywords| varchar(255) | YES | |
NULL ||
| Content |
mediumtext | | | ||
| Date| datetime | | | 2001-01-01
00:00:00 ||
| Author | varchar(100) | | | [EMAIL PROTECTED]
||
| Feedback| varchar(100) | YES | |
NULL ||
| Size| int(32) | YES | |
NULL ||
| Words | int(32) | YES | |
NULL ||
| Images | int(32) | YES | |
NULL ||
+-+--+--+-+--++
mysql> DESC x_instance;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Article | mediumint(9) | | MUL | 0 | |
| Section | mediumint(9) | | MUL | 0 | |
| Priority | tinyint(4) | | | 0 | |
| Status | int(16) unsigned | | | 0 | |
+--
Variation of SELECT DISTINCT
I've got a query that looks like this..
"SELECT Foot_Id, Dir_Uni, Part_Suffix, Part_Number, Appln_No, Description,
Product_Ref, Vehicle_Ref, Part_Prompt, Part_Description, Foot_Id,
Qualifier, Years
FROM application_parts
WHERE (('$id' = Appln_No) and (Years = '$dropdown') and (Dir_Uni <> 'U')
and (Part_Description <> 'Pipe Kit')
and (Part_Description <> 'Universal Converter') and (Part_Description <>
'Pre Cat'))
ORDER BY Part_Prompt";
(sorry if the spacing is weird)
I need to drop the rows returned that have the same part_description field,
like the DISTINCT clause does for rows, i need the same thing for a field
value, how can I do this?
Thanks for the help,
Alan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
building link error
Dear sirs, I have mysql 4.01 installed on my pc (not source,only binary)on win98 (I tried in win XP too).I downloaded myodbc source (3.51)and tryed compiled release with nmake (using makefile included in source)but aving a link error "unresolved esternal _aulldvrm in mysqlclient" (same message for function "_ftol2"). How can I solve this problem? - Yahoo! Mail: 6MB di spazio gratuito, 30MB per i tuoi allegati, l'antivirus, il filtro Anti-spam
Reported to SpamCop
A few minutes ago I noticed the emails from MySQL list were being rejected at my server because of the mysql.com server being listed in SpamCop. Information from http://spamcop.net/w3m?action=checkblock&ip=62.119.101.229 reveals that some of the spam messages that have made it through the list were either reported to SpamCop by users, or filtered by SpamCop. Just a heads-up. Generally, it's not a good, nor fair, idea to report spam received through a mailing list to SpamCop or other spam-catching entities, for this very reason. Now MySQL's list server has wound up on a block list. query, mysql -- Alan W. Rateliff, II: RATELIFF.NET Independent Technology Consultant :[EMAIL PROTECTED] (Office) 850/350-0260: (Mobile) 850/559-0100 - [System Administration][IT Consulting][Computer Sales/Repair] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The Solaris 2.7 version of the binaries
- Original Message - From: "Nesh Nenad Mijailovic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 18, 2003 5:23 PM Subject: The Solaris 2.7 version of the binaries > > Hi All, > > is there a reason why there are no MySQL binaries for Solaris 2.7 any more. > I have been downloading the binaries before for Solaris 2.7 from the Web > Site and now trying to compile the source doesn't work. > > Is it possible to put a link on the download page to a Solaris 2.7 binaries > if you have them? > > Thanks, > > Nesh Nenad Mijailovic The crew noted a while back that their Solaris 7 machine died and has yet to be replaced. Until then, what kind of problems are you having compiling on Solaris 7? Alternately, check out SunFreeware < http://sunfreeware.com > as Steve has MySQL 3.23.53 ready to go for 7/SPARC. -- Alan W. Rateliff, II: RATELIFF.NET Independent Technology Consultant :[EMAIL PROTECTED] (Office) 850/350-0260: (Mobile) 850/559-0100 - [System Administration][IT Consulting][Computer Sales/Repair] - 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
last_insert_id() returns 0 in windows
Okay, I've seen just about every question on last_insert_id(), except this one: I am running MySQL on Win XP and when I generate a test table (test) with an AUTO_INCREMENT column (aid) and a second column (a) then use an insert statement like: INSERT INTO test (a) values (1); then: SELECT LAST_INSERT_ID(); I get a return value of 0. I considered the fact that maybe my connection is closing, but when I create the same table on a remote Linux server it returns the proper AUTO_INCREMENT ID. Is there a server variable I need to set or something that I should be looking for in order to make this work on Windows? Al Kearns, Sales Representative/WebMaster, MCP [EMAIL PROTECTED] 1-866-858-9200 - 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: cannot get foreign keys to work
Dear Ben,
> i have been using the following sql to try and get foreign keys
> working, the table creation works fine but when i try and delete data
> from the parent table it deletes as would normally happen in mysql,
> also the desired results do not happen if i use RESTRICT.
> is this a problem to do with indexes???
>
> DROP TABLE IF EXISTS parent;
> CREATE TABLE parent(id INT NOT NULL, name char(5), PRIMARY KEY (id))
> TYPE=INNODB;
> DROP TABLE IF EXISTS child;
> CREATE TABLE child(id INT NOT NULL, parent_id INT, INDEX par_ind
> (parent_id), PRIMARY KEY (id),
> FOREIGN KEY (parent_id) REFERENCES parent(id)
> ON DELETE RESTRICT
> ) TYPE=INNODB;
>
> insert into parent(name) values('asdfg');
> insert into child(parent_id) values(last_insert_id());
When you installed MySQL, did you follow the instructions in the manual
to enable the innobb features? You can check by:
./mysqladmin variables | grep have_innodb
If you see this: sorry, it's fine and you need a better guru :)
| have_innodb | YES |
If you see this: Read the doc snippets below and follow the
instructions in the MySQL manual.
| have_innodb | DISABLED |
You can also verify the existance of the foreign keys by:
mysql> show create table parent;
Snippet from the MySQL documentation:
2.3.1 Quick Installation Overview
If you want to have support for InnoDB tables, you should edit the
/etc/my.cnf file and remove the # character before the parameter that
starts with innodb_ See section 4.1.2 `my.cnf' Option Files, and
section 7.5.2 InnoDB Startup Options.
Unfortunately, MySQL silently accepts and ignores 'innodb' type tables
when innodb is disabled. All the foreign key constraints are accepted
and ignored too.
Good luck,
Alan.
-
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
perl, MySQL, and Lost Connection while executing error!
Hey everyone I just joined this group today with an error that has been
plaguing me for the past 2 weeks and I can't figure out what's wrong!!
What's happening is I'll start up my perl program and sometimes, for no
apparent reason, I will get the Lost Connection to MySQL Server while
executing query, followed by a fetch() without execute() error. Again,
the error isn't constant and if I restart my programs a few times,
sometimes it will work just fine. I tried $DBH->trace(2) but that
didn't seem to help me. I noticed this however:
<- prepare('SELECT sendmsg FROM regnicks WHERE nick=?')=
DBI::st=HASH(0x840379c) at Nickserv.pm line 141
!! ERROR: 2013 'Lost connection to MySQL server during query'
I'm not sure what error 2013 is or if this could help me solve my error.
I was also looking to see if there was some type of error log that I
could reference to check for more information?
Thanks for any and all help!! If you need any more information, I would
be more than happy to answer any questions!
Alan
-
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 long is my piece of string?
You need indexes as soon as (or rather just before) they provide a performance difference. Alan -Original Message- From: Iain Lang [mailto:[EMAIL PROTECTED]] Sent: Sunday, 22 December 2002 11:15 AM To: [EMAIL PROTECTED] Subject: How long is my piece of string? . Dear List, I'm using php & MySQL for a cycling club website, results, guest-book, events and so on. I've just started and have faithfully created indices all over the place. At present, we have less than 400 records, be they of members, of image URLs, whatever. Each year will, I expect, create an additional 400 records. Am I gilding the lily adding indices for such a small database? Does such a small database really *need* indices, and beyond what number of records might indices provide faster extraction/presentation? I realise how vague a question it is, hence the subject title. Yooors, Iain. - "Most progress has been the result of the actions of unreasonable men." G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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: Extracting foreign key info via ODBC (via JDBC)
> Mark Matthews wrote: > > > Alan Hodgkinson wrote: > > > > I would like to extract the meta data describing foreign key > > relationships using ODBC (actually in Java using JDBC). > > > > My current version of mySQl 3.23.49 does not seem to support > > it. That is the methods DatabaseMetaData.getImportedKeys(...) > > and DatabaseMetaData.getExportedKeys(..) return empty sets > > on tables which have foreign keys defined on them. > > [snip] > > I don't want to do an upgrade unless I'm certain that I will > > get the feature. > > First, you need to use InnoDB tables to be able to extract foreign key > information. Ah.. thank you. I had though that native tables in the newer MySQL version, stored and we able re-display, the foreign key definitions, but that they didn't actually enforce them as constraints. This means I MUST have 'type=innodb' in my create statements, right? That's a small inconvenience, but acceptable. > Second, are you trying to use the JDBC:ODBC bridge (not recommended at > all), or the Type IV JDBC driver (MySQL Connector/J) to do this? Type IV. I've been using: mm.mysql-2.0.14, which I beleive is the old version of Connector/J. You probably know more about that than me :) > Connector/J supports DatabaseMetaData.getImported/ExportedKeys(). Cool! I'll upgrade. THE MOST IMPORTANT QUESTION --- Do you know which version of _MySQL_ supports the foreign key info? Is it only in version 4.x or does the latest 3.32.x have it too? The documentation and change log are relatively vague about it. Why am I doing this? My goal is to be able to extract meta-data out from an existing database and then generate a Turbine/Torque compliant XML configuration file. I must have the foreign key info. I will be performing the foreign key meta-data extraction as a development step (as opposed to in production with lots of data). The DB could in principal be empty. Performance is NOT an issue. ...and yeah, I'm willing to share my code (I am planning to give it to the Torque gang). Many thanks, Alan. - 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
Extracting foreign key info via ODBC (via JDBC)
Dear All, I would like to extract the meta data describing foreign key relationships using ODBC (actually in Java using JDBC). My current version of mySQl 3.23.49 does not seem to support it. That is the methods DatabaseMetaData.getImportedKeys(...) and DatabaseMetaData.getExportedKeys(..) return empty sets on tables which have foreign keys defined on them. Do newer versions of MySQL allow you to extract foreign key relation information? The change log on the in the documentation at the MySQL site implies that this _may_ be available for InnoDB tables (but makes no statement for non-InnoDB tables), as of version 3.32.50. I don't want to do an upgrade unless I'm certain that I will get the feature. Can anyone enlighten me? Many thanks in advance, Alan. - 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: more about using sets
> From: David T-G [mailto:[EMAIL PROTECTED]] > ...I still have to figure > out how to make sure that our credit card types and skill levels don't > get corrupted (MC, MasterCard, mastercard, ...), but I guess that gets > enforced in the software interface, right? Isn't the card type a piece of derived data? You should never have to enter it directly; it's computable from (the first digit(s) of) the card number. - 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: ADO Bulk Inserts
> From: Michael She [mailto:[EMAIL PROTECTED]] > ...So are you saying that the Windows ODBC MySQL driver > doesn't support multiple statements? So far as I know, *no* drivers support multiple statements. However, that's not what you want for "bulk inserts". What you want is multiple value sets in one INSERT statement: INSERT INTO tablename(col1, col2) VALUES (val1a, val2a), (val1b, val2b), (val1c, val2c) That's what you're doing already, right? So I don't understand your issue. - 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: Desc question
> From: Alex Behrens [mailto:[EMAIL PROTECTED]]
> ...How can I make it so only one form
> selection adds a DESC tag to the query, is this possible?
>
> I'm using this code:
>
> $fetch = mysql_query("SELECT * FROM players ORDER BY $var");
>
>
> Name
> Position
> Number
> Grade
>
> I want only the Grade option to use the desc option with the
> $fetch query.
If you want DESC to go along with grade_num, just put DESC with grade_num:
Grade
This seems obvious to me, so I must be missing something. :-)
-
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 someone explain this?
> mysql> select sum(trial_signups) from campaign_t where > datestamp='20021204'\g > ++ > | sum(trial_signups) | > ++ > |100 | > ++ > > > mysql> SELECT site_id, sum(raws) As RawHits, sum(uniques) As > UniqueHits, > sum(trial_signups) As Sales FROM campaign_t WHERE datestamp >= > '20021204' AND datestamp <= '20021204' GROUP BY site_id\g > +-+-++---+ > | site_id | RawHits | UniqueHits | Sales | > +-+-++---+ > | 1 |6231 | 3672 | 1 | > | 2 | 143 | 96 | 0 | > | 3 | 256 |128 | 0 | > | 4 | 16 | 11 | 0 | > | 6 | 9 | 9 | 0 | > | 7 | 88 | 45 | 2 | > | 8 |1801 | 1055 |11 | > | 9 |2805 | 1979 | 2 | > | 10 |2251 |669 | 0 | > +-+-++---+ > > It's JUST the date '20021204'.. the rest of the dates (for the past 4 > months) have been working fine. > > Any ideas? Only one: Are there any NULL values in the site_id column for that date? - 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: Embarrassing: can't log in
it only takes a few minutes to uninstall and install... > -Original Message- > From: Amittai Aviram [mailto:[EMAIL PROTECTED]] > Sent: Monday, 2 December 2002 9:37 > To: Mysql > Subject: Embarrassing: can't log in > > > I've got an embarrassing problem. I installed MySQL on my WinXP machine > months ago. Since then, though, I've continued to use the MySQL > on a remote > (FreeBSD) host, so I haven't had occasion to use my local > version. Now as I > went back to it, I found that I couldn't log in. I can't remember my > username and password. All the usernames and passwords that I could think > of would fail. What to do now? Thanks! > > Amittai Aviram > > > > - > 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: date conversion problem
this might give you some ideas select CONCAT(DAYOFMONTH(p.DATEGOLIVE),'.',MONTH(p.DATEGOLIVE),'.',YEAR(p.DATEGOLIV E)) DATEGOLIVEF from my table p > -Original Message- > From: Alex Behrens [mailto:[EMAIL PROTECTED]] > Sent: Friday, 22 November 2002 13:01 > To: MYSQL > Subject: date conversion problem > > > Hey All, > > I'm storing date values for hockey game information using the > DATE value and > they are stored as MMDD and when I retrieve them from the > database they > are shown as: "2002-11-23" I was wondering if there was a way to have them > converted to November, 23, 2002 when they are displayed on my displayed on > the page. Is this possible? > > mysql > > Thanks! > > -Alex "Big Al" Behrens > E-mail: [EMAIL PROTECTED] > Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) > Phone: 651-482-8779 > Cell: 651-329-4187 > Fax: 651-482-1391 > ICQ: 3969599 > Owner of the 3D-Unlimited Network: > http://www.3d-unlimited.com > Send News: > [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
Fw: mySQL capabilities
I was wondering if some things can be done with mySQL: Select rows that have a number in ANY column, without specifying every column in the select statement. Perform statistical analysis on the numbers in a column (like regression). I've only seen simple things like maximum and sum. - 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: trouble with LOAD command
I'd pick up the text file and search/replace all end of line character(s) with a end of line"00 "comma combination so it reads > 00,Abe,Lincoln,8347 on each line and try your first method. If that fails, I would drop the ID field off the table, then do the import with original file, then atlter the table again to add the ID field Alan > -Original Message- > From: Chris Walcott [mailto:cwalcott@;macromedia.com] > Sent: Thursday, 14 November 2002 9:42 > To: Mysql-L (E-mail) > Subject: trouble with LOAD command > > > How do I LOAD a text file into a table that has a Primary Key defined? > > I have the following table defined: > mysql> describe phoneList; > +--+-+--+-+-++ > | Field| Type| Null | Key | Default | Extra | > +--+-+--+-+-++ > | ID | int(11) | | PRI | NULL| auto_increment | > | First_Name | varchar(20) | YES | | NULL|| > | Last_Name| varchar(20) | YES | | NULL|| > | Phone_Number | varchar(20) | YES | | NULL|| > +--+-+--+-+-++ > > I'm attempting to load a comma delimited list using load. The > text file looks like this: > > Abe,Lincoln,8347 > Herb,Albert,9387 > George,Washington,9283 > ... > > When I do this: > mysql> load data local infile "/home/cwalcott/PhoneList_b.txt" > into table phoneList > -> fields terminated by ',' (First_Name, Last_Name, Phone_Number); > > I get this on select * > ++++--+ > | ID | First_Name | Last_Name | Phone_Number | > ++++--+ > |e| Lincoln| 8347 > |rb | Albert | 9387 > |orge | Washington | 9283 > ++++--+ > > if I do this: > load data local infile "/home/cwalcott/PhoneList_b.txt" into > table phoneList; > > I get: > +++---+--+ > | ID | First_Name | Last_Name | Phone_Number | > +++---+--+ > | 1 | NULL | NULL | NULL | > | 2 | NULL | NULL | NULL | > | 3 | NULL | NULL | NULL | > +++---+--+ > > I've also tried using a text file with the first column set to > index numbers but the results are very similar. > > If I do this: > > mysql> load data local infile > "/home/cwalcott/PhoneList_small.txt" into table phoneList > -> fields terminated by ',' (First_Name, Last_Name, Phone_Number); > > I get: > +++---+--+ > | ID | First_Name | Last_Name | Phone_Number | > +++---+--+ > | 1 | 1 | Abe | Lincoln | > | 2 | 2 | Herb | Albert | > | 3 | 3 | George| Washington | > +++---+--+ > > If I do this: > mysql> load data local infile > "/home/cwalcott/PhoneList_small.txt" into table phoneList > -> fields terminated by ',' (ID, First_Name, Last_Name, Phone_Number); > > I get this: > ++++--+ > | ID | First_Name | Last_Name | Phone_Number | > ++++--+ > |e| Lincoln| 8347 > |rb | Albert | 9387 > |orge | Washington | 9283 > +-++---+--+ > > - > 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: Count Rows in two tables
Ah, yes - sorry Alan > -Original Message- > From: [EMAIL PROTECTED] > [mailto:murad@;godel.bioc.columbia.edu]On Behalf Of Murad Nayal > Sent: Wednesday, 13 November 2002 12:58 > Cc: [EMAIL PROTECTED] > Subject: Re: Count Rows in two tables > > > > Alan McDonald wrote: > > > > You can't count the join? > > Alan > > if you count the (unqualified) join you'll end up with the product of > the two table counts. > > Murad > > > > > > -Original Message- > > > From: Rick Baranowski [mailto:rickb@;baranconsulting.com] > > > Sent: Wednesday, 13 November 2002 12:10 > > > To: [EMAIL PROTECTED] > > > Subject: Count Rows in two tables > > > > > > > > > Hello all, > > > > > > Does anybody have a SQL string to count the rows in two different > > > tables and > > > give you a total number of rows? I have been trying to find an > > > answer for a > > > couple of days and seems like a simple string. > > > > > > Thank you > > > > > > Rick > > > > > > > > > - > > > > - > 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: Count Rows in two tables
You can't count the join? Alan > -Original Message- > From: Rick Baranowski [mailto:rickb@;baranconsulting.com] > Sent: Wednesday, 13 November 2002 12:10 > To: [EMAIL PROTECTED] > Subject: Count Rows in two tables > > > Hello all, > > Does anybody have a SQL string to count the rows in two different > tables and > give you a total number of rows? I have been trying to find an > answer for a > couple of days and seems like a simple string. > > Thank you > > Rick > > > - > 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: last_insert_id()
The .Neta Adapter.. does it make a persistent connection? If the connection drops between the first insert and the call to select, then the return would be zero Alan > -Original Message- > From: Cain O'Sullivan [mailto:cos@;iinet.net.au] > Sent: Wednesday, 13 November 2002 16:13 > To: [EMAIL PROTECTED] > Subject: last_insert_id() > > > Hi, > > I am using C# with ODBC.Net to communicate with MySQL. I want to > determine > the last ID of an auto_increment field in the database. When I manually > perform the insert using the MySQL command window I can then > follow up with > "select last_insert_id()" and I get the correct value, however, when using > ODBC to perform the insert (via a .Net DataAdapter) the last_insert_id() > returns 0. > > Can anyone provide some insight into this? > > Best Regards, > > Cain O'Sullivan > [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: How to port MS Access to MySQL ??
MySQLFront will create the tables and pump the data with one button press Alan > -Original Message- > From: Terry [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, 12 November 2002 22:02 > To: [EMAIL PROTECTED] > Subject: RE: How to port MS Access to MySQL ?? > > > > MyODBC works fine, > but what to do if you have hundreds of tables?=20 > > its painful job, maybe theres some > easier way to do that ? > > regards, > terry > > sql > > > - > 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: Newbie Question - Query works in access but not MySQL
Do you have a space between PartNumber and Like? Also there's not need to ORDER BY - the GROUP BY does that anyway (I know that's the case elsewhere) and finally, I have to guess that it's objecting to Obsolete not being in the main select.. have you tried including it and grouping by it? you can ignore the obsolete in the main select when it's returned since it will all be the same value... put it first. Alan > -Original Message- > From: Ed Reed [mailto:ereed@;nearfield.com] > Sent: Tuesday, 12 November 2002 18:54 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: Newbie Question - Query works in access but not MySQL > > > UPDATE: > > Still trying to solve this and I think I have something that's a little > easier to understand. If I run the following query against MySQL it > returns "Unknown column 'Obsolete' in 'having clause'". If I run the > query using MSAccess as a frontend to MySQL the query runs correctly (it > returns a recordset with 9 records). If I remove the Obsolete column > from the Having clause the MySQL server appears to hang up while it > processes the query but it never returns even if left for an hour. > > Please, has anyone got any ideas? > > SELECT Products.PartNumber, Sum(tblInvTransaction.Qty) AS SumOfQty > FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = > tblInvTransaction.ProductID > GROUP BY Products.PartNumber > HAVING ((Products.PartNumberLike "%A-000%") AND > (Products.Obsolete<>-1)) > ORDER BY Products.PartNumber; > > > - > 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: PHP bias (Way OT)
I agree mostly,... but why is ASP worthless? Alan > -Original Message- > From: [EMAIL PROTECTED] [mailto:vanboers@;why.dedserius.com]On > Behalf Of Van > Sent: Monday, 11 November 2002 6:21 > To: Paul DuBois > Cc: MySQL > Subject: Re: PHP bias (Way OT) > > > Paul: > > Not sure why my post didn't cc to the list, originally. I think > I picked up on > your tone, but the one thing I had intended to add was the > dynamic that the more > I do this stuff, the more I find myself just having to "get over" > some of the > more ugly and cludged implementations due to limitations in what our > end-users/clients are willing to let us do. In other words: who cares. > They'll never really look at it that closely and if another > developer comes in > several years later to make any changes/enhancements, they'll > probably just do a > rewrite. > > Once upon a time, I thought you could code elegantly, and C/C++ > is about the > only realm in which I see this to still be possible. PHP/ASP and > _especially!!!_ ColdFusion are impossible to write elegant and > easy to document > code, but I think PHP is the best of these evils since it has such a huge > function set. ASP is worthless for other reasons, and CF is just > disgusting to > look at. > > I _realize_ Perl is fast, and powerful. I really _do_ realize > that. But, it's > ugly. It looks like Snoopy swearing characters, and you can talk > (write) about > how it's elegant until you're blue in the face, but show it to a > CIO/CFO and > they'll tell you it's ugly, and they're quite correct. > > But, people program in it extensively, and do great things with > it; it's just > ugly. And, so are most implementations. In a perfect world, > everyone would use > C/C++, but they don't and I don't care. I'll use it if I choose > to. I'll use > Perl to do quick and dirty things. And, I'll code ColdFusion if > someone is > offering me money to do so. At the end of the day, no > programming/technology is > pretty; where are those beer nuts. > > And, BTW, clearly there's no one better equipped to point out > your original > thoughts on what _can_ be done in Perl. > > --mysql, table, drop, explain, database-- > > Best Regards, > Van > -- > = > Linux rocks!!! http://www.dedserius.com/ > = > Paul DuBois wrote: > > (some stuff I snipped) > > http://www.kitebird.com/mysql-perl/ > > > > >You can do all these things from perl. Or PHP, or CF, or ASP. > Just pass your > > >variables to an external web-server running it's own > proprietary web database > > >and you'll get what you need. > > > > > >Sorry; am I speaking out of step, here? We all do this... I > run MySQL on my > > >own servers, but my clients are still pretty thick-headed... they > > >use M$Access > > >and ColdFusion. And, I can still drop and add columns through a > > >web-interface. > > >It's very ugly, but it still works. Is there anyone here > making a living that > > >doesn't have to work through such interface-specific things? > > > > > >Didn't think so; but, in a perfect world, we'd all be doing > things ANSI SQL > > >92... Whatever... pass the beer nuts. >:) > > > > > >Van > > - > 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: little problem, I need some help...
Every SQL database requires the ability to locate a record which is unique
in some way. You can't have two records which look the same.
That's why you need to define a primary KEY on a column or coumns.
You need to read a primer on SQL databases
Alan
> -Original Message-
> From: 3mip1s4la-Emilio Pisanty [mailto:emipisala@;lancaster.edu.mx]
> Sent: Sunday, 10 November 2002 11:34
> To: R. Hannes Niedner
> Cc: MySQL Mailinglist
> Subject: Re: little problem, I need some help...
>
>
>
> > > tables have met with a 1175 error ("You are using safe update
> mode and you
> > > tried to update a table without a WHERE that uses a KEY column").
> > Nothing is obvious and is hard to advise you if you don't give
> us some more
> > info on the table structure and the update query you have trouble with.
> >
> > /h
>
> ok. thable structure is this:
> mysql> describe pupils;
> +--+-+--+-+-+---+
> | Field| Type| Null | Key | Default | Extra |
> +--+-+--+-+-+---+
> | name | varchar(20) | YES | | NULL| |
> | surname | varchar(20) | YES | | NULL| |
> | surname2 | varchar(20) | YES | | NULL| |
> | form | char(3) | YES | | NULL| |
> | tutor| varchar(20) | YES | | NULL| |
> | sex | char(1) | YES | | NULL| |
> | birth| date| YES | | NULL| |
> | math | char(3) | YES | | NULL| |
> | optA | char(3) | YES | | NULL| |
> | optB | char(3) | YES | | NULL| |
> +--+-+--+-+-+---+
> 10 rows in set (0.00 sec)
>
> it was built in version 3.23, and we recently updated to 4.0.4 beta.
>
> the query I'm running is
>
> mysql> UPDATE pupuils SET tutor = 'URIOSTEGUI' WHERE group = 'U6U';
> (setting the name of the tutor for Upper 6)
>
> and I meet with:
> ERROR 1175: You are using safe update mode and you tried to
> update a table
> without a WHERE that uses a KEY column
>
> I don't quite understand what a "KEY column" is, can someone
> explain it to
> me? if you can't answer, where can I find error listings? because they
> don't appear to be on the site.
>
> hope this helps you help me...
>
> thanks in advance,
>
>Emilio Pisanty
>
>
>
>
> -
> 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: mysqld refuse to die
Also In My Humble Opinion (IMHO) For What It's Worth (FWIW) > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Saturday, 9 November 2002 23:37 > To: Jocelyn Fournier; Jack Chen; [EMAIL PROTECTED] > Subject: Re: mysqld refuse to die > > > Yes.All processes are named "mysqld"...less one "mysqld_safe" > which is "main > guilty" for keeping mysql daemon in "life". > > P.S. > Please tell me (if you wish)...what means AFAIK? > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Jocelyn Fournier" <[EMAIL PROTECTED]> > To: "Gelu Gogancea" <[EMAIL PROTECTED]>; "Jack Chen" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Saturday, November 09, 2002 2:27 PM > Subject: Re: mysqld refuse to die > > > > just kill mysqld_safe and then the mysqld process :) > > (but AFAIK all the mysql thread are names mysqld ??) > > - Original Message - > > From: "Gelu Gogancea" <[EMAIL PROTECTED]> > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Jack Chen" > <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Saturday, November 09, 2002 12:20 PM > > Subject: Re: mysqld refuse to die > > > > > > > ...because not all mysql processes are named "mysql" and is > one (and you > > > know about this) which is for safe running(mysqld_safe or safe_mysqld) > > which > > > create new threads when another is "killed". > > > Regards, > > > > > > Gelu > > > _ > > > G.NET SOFTWARE COMPANY > > > > > > Permanent e-mail address : [EMAIL PROTECTED] > > > [EMAIL PROTECTED] > > > - Original Message - > > > From: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > To: "Gelu Gogancea" <[EMAIL PROTECTED]>; "Jack Chen" <[EMAIL PROTECTED]>; > > > <[EMAIL PROTECTED]> > > > Sent: Saturday, November 09, 2002 2:12 PM > > > Subject: Re: mysqld refuse to die > > > > > > > > > > Hi, > > > > > > > > Why not trying killall -9 mysqld ? > > > > > > > > Regards, > > > > Jocelyn > > > > - Original Message - > > > > From: "Gelu Gogancea" <[EMAIL PROTECTED]> > > > > To: "Jack Chen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > > Sent: Saturday, November 09, 2002 12:08 PM > > > > Subject: Re: mysqld refuse to die > > > > > > > > > > > > > Hi, > > > > > If you really wish to kill the mysql daemon in this way : > > > > > You should try : > > > > > ps -ef |grep "mysql" > > > > > ...show all the mysqld processes and after this must enumarate all > pid > > > of > > > > > processes in a single kill command. > > > > > E.g. > > > > > kill -9 1024 1056 ...(processes which are open). > > > > > But it's more "health" if you can stop the server using : > > > > > /etc/rc.d/mysqld stop > > > > > > > > > > Regards, > > > > > > > > > > Gelu > > > > > _ > > > > > G.NET SOFTWARE COMPANY > > > > > > > > > > Permanent e-mail address : [EMAIL PROTECTED] > > > > > [EMAIL PROTECTED] > > > > > - Original Message - > > > > > From: "Jack Chen" <[EMAIL PROTECTED]> > > > > > To: <[EMAIL PROTECTED]> > > > > > Sent: Saturday, November 09, 2002 7:57 AM > > > > > Subject: Re: mysqld refuse to die > > > > > > > > > > > > > > > > I have just figured out: > > > > > > > > > > > > kill -9 xxx (process number) > > > > > > > > > > > > Thanks, > > > > > > > > > > > > Jack > > > > > > > > > > > > > > > > > > Jack Chen, Stein Lab, Cold Spring Harbor Labs > > > > > > 1 Bungtown Road, Cold Spring Harbor, NY, 11724 > > > > > > Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > On Sat, 9 Nov 2002, Jack Chen wrote: > > > > > > > > > > > > > Hi All, > > > > > > > > > > > > > > Please help me with this problem: > > > > > > > > > > > > > > For some reason, I could not bring down my mysqld by running > > > > > > > > > > > > > > mysqld stop > > > > > > > > > > > > > > An error message indicate: fail > > > > > > > > > > > > > > What's going on? > > > > > > > > > > > > > > Thanks, > > > > > > > > > > > > > > Jack > > > > > > > > > > > > > > > > > > > > > Jack Chen, Stein Lab, Cold Spring Harbor Labs > > > > > > > 1 Bungtown Road, Cold Spring Harbor, NY, 11724 > > > > > > > Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > - > > > > > > > Before posting, please check: > > > > > > >http://www.mysql.com/manual.php (the manual) > > > > > > >http://lists.mysql.com/ (the list archive) > > > > > > > > > > > > > > To request t
RE: mysqld refuse to die
As Far As I Know > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Saturday, 9 November 2002 23:37 > To: Jocelyn Fournier; Jack Chen; [EMAIL PROTECTED] > Subject: Re: mysqld refuse to die > > > Yes.All processes are named "mysqld"...less one "mysqld_safe" > which is "main > guilty" for keeping mysql daemon in "life". > > P.S. > Please tell me (if you wish)...what means AFAIK? > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Jocelyn Fournier" <[EMAIL PROTECTED]> > To: "Gelu Gogancea" <[EMAIL PROTECTED]>; "Jack Chen" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Saturday, November 09, 2002 2:27 PM > Subject: Re: mysqld refuse to die > > > > just kill mysqld_safe and then the mysqld process :) > > (but AFAIK all the mysql thread are names mysqld ??) > > - Original Message - > > From: "Gelu Gogancea" <[EMAIL PROTECTED]> > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Jack Chen" > <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Saturday, November 09, 2002 12:20 PM > > Subject: Re: mysqld refuse to die > > > > > > > ...because not all mysql processes are named "mysql" and is > one (and you > > > know about this) which is for safe running(mysqld_safe or safe_mysqld) > > which > > > create new threads when another is "killed". > > > Regards, > > > > > > Gelu > > > _ > > > G.NET SOFTWARE COMPANY > > > > > > Permanent e-mail address : [EMAIL PROTECTED] > > > [EMAIL PROTECTED] > > > - Original Message - > > > From: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > To: "Gelu Gogancea" <[EMAIL PROTECTED]>; "Jack Chen" <[EMAIL PROTECTED]>; > > > <[EMAIL PROTECTED]> > > > Sent: Saturday, November 09, 2002 2:12 PM > > > Subject: Re: mysqld refuse to die > > > > > > > > > > Hi, > > > > > > > > Why not trying killall -9 mysqld ? > > > > > > > > Regards, > > > > Jocelyn > > > > - Original Message - > > > > From: "Gelu Gogancea" <[EMAIL PROTECTED]> > > > > To: "Jack Chen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > > Sent: Saturday, November 09, 2002 12:08 PM > > > > Subject: Re: mysqld refuse to die > > > > > > > > > > > > > Hi, > > > > > If you really wish to kill the mysql daemon in this way : > > > > > You should try : > > > > > ps -ef |grep "mysql" > > > > > ...show all the mysqld processes and after this must enumarate all > pid > > > of > > > > > processes in a single kill command. > > > > > E.g. > > > > > kill -9 1024 1056 ...(processes which are open). > > > > > But it's more "health" if you can stop the server using : > > > > > /etc/rc.d/mysqld stop > > > > > > > > > > Regards, > > > > > > > > > > Gelu > > > > > _ > > > > > G.NET SOFTWARE COMPANY > > > > > > > > > > Permanent e-mail address : [EMAIL PROTECTED] > > > > > [EMAIL PROTECTED] > > > > > - Original Message - > > > > > From: "Jack Chen" <[EMAIL PROTECTED]> > > > > > To: <[EMAIL PROTECTED]> > > > > > Sent: Saturday, November 09, 2002 7:57 AM > > > > > Subject: Re: mysqld refuse to die > > > > > > > > > > > > > > > > I have just figured out: > > > > > > > > > > > > kill -9 xxx (process number) > > > > > > > > > > > > Thanks, > > > > > > > > > > > > Jack > > > > > > > > > > > > > > > > > > Jack Chen, Stein Lab, Cold Spring Harbor Labs > > > > > > 1 Bungtown Road, Cold Spring Harbor, NY, 11724 > > > > > > Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > On Sat, 9 Nov 2002, Jack Chen wrote: > > > > > > > > > > > > > Hi All, > > > > > > > > > > > > > > Please help me with this problem: > > > > > > > > > > > > > > For some reason, I could not bring down my mysqld by running > > > > > > > > > > > > > > mysqld stop > > > > > > > > > > > > > > An error message indicate: fail > > > > > > > > > > > > > > What's going on? > > > > > > > > > > > > > > Thanks, > > > > > > > > > > > > > > Jack > > > > > > > > > > > > > > > > > > > > > Jack Chen, Stein Lab, Cold Spring Harbor Labs > > > > > > > 1 Bungtown Road, Cold Spring Harbor, NY, 11724 > > > > > > > Tel: 1 516 3676904; e-mail: [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]>
RE: Copy Records in a table...
maybe it doesn't then in MySQL - it does elsewhere Alan > -Original Message- > From: David Felio [mailto:david@;ark.org] > Sent: Saturday, 9 November 2002 1:59 > To: MySQL List > Subject: Re: Copy Records in a table... > > > Is the implementation of this different than the docs say? According to > the manual, I didn't think this would work: > > "* The target table of the INSERT statement cannot appear in the FROM > clause of the SELECT part of the query because it's forbidden in ANSI > SQL to SELECT from the same table into which you are inserting." > - http://www.mysql.com/doc/en/INSERT_SELECT.html > > On Thursday, November 7, 2002, at 08:56 PM, Alan McDonald wrote: > > > insert into mytable(field1, field2, field3) select field1, field2, > > newvalue > > from mytable where productcode=xx > > > >> -Original Message- > >> From: Doug Coning [mailto:lists@;coning.com] > >> Sent: Friday, 8 November 2002 14:35 > >> To: [EMAIL PROTECTED] > >> Subject: Copy Records in a table... > >> > >> > >> Hi everyone, > >> > >> I'm still learning MySQL. I have a database of 600 items. I am still > >> adding products. Several of these products are identical in nature, > >> but > >> have maybe one or two columns that are different. Is there a way > >> to write a > >> SQL command that selects these items and then inserts them, and > >> then updates > >> them respectively with one command? > >> > >> Thanks, > >> > >> Doug > > > - > 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: How to port MS Access to MySQL ??
MySQLfront does it all > -Original Message- > From: tmb [mailto:topmailbox@;yahoo.com] > Sent: Friday, 8 November 2002 23:18 > To: [EMAIL PROTECTED] > Subject: How to port MS Access to MySQL ?? > > > Is there a tool for doing a quick port from MS Access > to MySQL? > > Or must you manually create all the tables & sql > statements in MySQL and then export the MS Access data > to a comma delimited file... then import it into > MySQL? > > Thanks for any help - tmb > > > __ > Do you Yahoo!? > U2 on LAUNCH - Exclusive greatest hits videos > http://launch.yahoo.com/u2 > > - > 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: Copy Records in a table...
insert into mytable(field1, field2, field3) select field1, field2, newvalue from mytable where productcode=xx > -Original Message- > From: Doug Coning [mailto:lists@;coning.com] > Sent: Friday, 8 November 2002 14:35 > To: [EMAIL PROTECTED] > Subject: Copy Records in a table... > > > Hi everyone, > > I'm still learning MySQL. I have a database of 600 items. I am still > adding products. Several of these products are identical in nature, but > have maybe one or two columns that are different. Is there a way > to write a > SQL command that selects these items and then inserts them, and > then updates > them respectively with one command? > > Thanks, > > Doug > > > > > > 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 > - 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: load data infile syntax
>If the data contains "\", then database will automatically takes "\" >away and shift 1 byte left. >My data will mass up. That is why I still need "\" Does your data actually include backslash characters? If you want to import them as they are, you definitely don't want to ESCAPE BY them. Escaping is used to treat otherwise special characters as nonspecial. For example, quotes and commas have special meaning in some text files, but you can 'escape' the special interpretation by using a scheme like prefixing them with a character that's "more special" than they are. The prefix is usually a backslash, which means that if you really do want a backslash, you need to use two of them (the first one says to treat the next character in a different way than usual). So the moral of the story is this: If you're trying to import backslashes into an SQL table, and your text file doesn't have backslashes doubled, don't choose a backslash as your ESCAPE BY character. - 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 and amanda
I was under the impression that hotbackup was only available with innodb tables where do you get amanda from? Alan > -Original Message- > From: Lewis Watson [mailto:lists@;visionsix.com] > Sent: Wednesday, 6 November 2002 14:22 > To: mysql > Subject: MySQL and amanda > > > Hey MySQL users! > I have been using amanda as backup software. It seems that it is working > fine as a backup for the MySQL data directory but I am wondering > is there a > better way to backup up the databases? They are production Db's > so I really > do not want to stop the server, but isn't this necessary to > properly backup > up the db directory? > Thanks. > Lewis > > > > - > 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: Removal of Primary Key in Mysql
Because a primary key is a table property and the index of the key is a property of the primary key. If you want ot drop a primary key you alter the table > -Original Message- > From: Eric Frazier [mailto:ef@;kwinternet.com] > Sent: Tuesday, 5 November 2002 15:26 > To: Paul DuBois > Cc: Uma Shankari T.; [EMAIL PROTECTED] > Subject: Re: Removal of Primary Key in Mysql > > > Hi, > > This is a why questionk, which may be somewhat pointless, but. Why is this > under alter table instead of drop index? > > Thanks, > > Eric > > At 01:10 AM 11/5/02 -0600, Paul DuBois wrote: > >At 12:08 +0530 11/5/02, Uma Shankari T. wrote: > >>Hello, > >> > >> I have set one of my field in the mysql table as primary > key..no i want > >>to remove that primary key setting in mysql..Can anyone please > tell me how > >>to do that ??? > >> > >>Regards, > >>Uma > > > >ALTER TABLE tbl_name DROP PRIMARY KEY; > > > >- > >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 > > > > (250) 655 - 9513 (PST Time Zone) > > "Inquiry is fatal to certainty." -- Will Durant > > > > > > - > 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: update question
> -Original Message- > From: Jörgen Winqvist [mailto:jorgen@;winqvist.net] > > I need to let the values in two columns change place with each other. > I've tried to "update xxx set a=b, b=a" but that doesn't work > (b=a uses > the "new" a). Here's a cute trick for swapping two numbers without using a temporary variable: set a=a-b, b=b+a, a=b-a I don't know how applicable it is to your query, but it might be worth considering. - 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
Error Code Question
This error comes up when altering table structure BUT it times out after a while. Error: 7 - Error on rename of '.\DBNAME\tablename.MYI' to '.\DBNAME\#sql2-61-19.MYI' (Errcode: 13) Some have said - "oh yeah, that's a permissions problem" but if it times out and finally the command executes after a while, then how can it be a permissions problem. It's more likely that the server will not allow change while connections are live... but I wait until the connections are not there (in MySQLAdmin) and still the error persists for a while longer. Finally it executes. Is there something else at play here? Alan ' Keywords: ' myODBC, mySQL, ' Error: ' Error: 7 - Error on rename of '.\DBNAME\tablename.MYI' to '.\DBNAME\#sql2-61-19.MYI' ' (Errcode: 13) ' Environment: ' WkStn - Windows 2000 WkStn, 512 meg, Pentium 1000 mhz, build 2195, SP3 ' myODBC 3.51 - WinX ' ' Server - Windows NT 4.0 Server SP6, 256 meg, Pentium II 450 mhz ' mySQL - 3.23.52 - NT - 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: Access2MySQL
I use MySQLFront and is creats the tables and imports the data with one click never had a problem Alan > -Original Message- > From: [EMAIL PROTECTED] [mailto:Sam4Software@;aol.com] > Sent: Wednesday, 30 October 2002 9:15 > To: [EMAIL PROTECTED] > Subject: Access2MySQL > > > Hi all, > > After setting the field data type in MySQL to Text, which is the > same data > type for Access data type where the data is being imported from, > the import > process was succeful and the same number of rows was imported, BUT I > don't see any data in the MySQL, all I see is Null, Null. any > comment ?? > > Sam > > > Sam > > - > 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: Altering Table errors
Then, why..., if I wait a little longer, does the alter command work? I do not change the the permissions in the meantime. I just wait. Alan > -Original Message- > From: gerald_clark [mailto:gerald_clark@;suppliersystems.com] > Sent: Tuesday, 29 October 2002 1:38 > To: Alan McDonald > Cc: [EMAIL PROTECTED] > Subject: Re: Altering Table errors > > > Error 13 is an operating system privilege violation error. > the user 'mysql' does not own or have access to something in > the database. > > Alan McDonald wrote: > > >MySQL > > > > > >I asume that when I add a field or rename a field etc and get the errcode > >13, that I must wait til a connection timeout has occurred to be able to > >make this structural change. While I'm developing, is there a > quicker way to > >do this? > >Stopping/starting the service is not that quick.. Is there another way? > > > >thanks > >Alan > > > > > > > >- > >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: Need a little query help
> -Original Message- > From: Chris Mason [mailto:masonc@;masonc.com] > [...] > The problem is, it is easy to test for one amenity, but I need to test > that the hotel has all of the amenities. The query above > returns all the > hotels that have ANY of the amenities, I need the hotels having ALL of > the amenties only. Try turning the problem around. Instead of including only the hotels having all desired amenities, you might get the results you want by excluding any hotels _lacking_ a desired amenity. - 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: Altering Table errors
But after a while it does, cause I only have to wait a while with no activity on the database, and the command executes. So it's not permissions Alan > -Original Message- > From: gerald_clark [mailto:gerald_clark@;suppliersystems.com] > Sent: Tuesday, 29 October 2002 1:38 > To: Alan McDonald > Cc: [EMAIL PROTECTED] > Subject: Re: Altering Table errors > > > Error 13 is an operating system privilege violation error. > the user 'mysql' does not own or have access to something in > the database. > > Alan McDonald wrote: > > >MySQL > > > > > >I asume that when I add a field or rename a field etc and get the errcode > >13, that I must wait til a connection timeout has occurred to be able to > >make this structural change. While I'm developing, is there a > quicker way to > >do this? > >Stopping/starting the service is not that quick.. Is there another way? > > > >thanks > >Alan > > > > > > > >- > >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
Altering Table errors
MySQL I asume that when I add a field or rename a field etc and get the errcode 13, that I must wait til a connection timeout has occurred to be able to make this structural change. While I'm developing, is there a quicker way to do this? Stopping/starting the service is not that quick.. Is there another way? thanks Alan - 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
Import from files
Is it possible to update a field using SQL with the contents of a text file previoously written to disk? I want to update a mediumtext field with the contents of c:\data\mytext.txt file. update mytable set mymediumtext= filecontents where id=number... ? Alan - 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
adUseClient
SQL The docs say that recordset1.RecordCount will return the correct value if adUseClient is used as the cursorlocation setting. I find that this is not correct - is there another setting which needs to be mae to make this return correctly - my return matching rows setting is also set. Alan McDonald http://www.meta.com.au - 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
FW: The request properties can not be supported by this ODBC Driver.
SQL ASP I can't seem to find any examples of saving text area form inputs to TEXT type fields. They all seem to be text inputs or strings being saved to varchar fields... Can someone point me to an example of saving large text quantities to a TEXT Type field in ASP? Thanks Alan McDonald http://www.meta.com.au - 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
error in the list?
can someone tell me why I get this error back from postings? Your message was not delivered for the following reason: E-mail Account: lists-mysql is over the limit of 31457280 bytes. Automated Postmaster Alan McDonald http://www.meta.com.au - 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: timestamp bug increments by one day
isn't that the month changing? it's changing from october to january...?? Alan > -Original Message- > From: Jay X [mailto:sparqz50@;hotmail.com] > Sent: Monday, 21 October 2002 12:20 > To: [EMAIL PROTECTED] > Subject: timestamp bug increments by one day > > > Hi There, > > Just reciently (after no changes) my MySQL database is automatically > incrementing timestamps by one day. > > Timestamp format is timestamp(14). > > An example date would be 20021021143513 > > when I use command such as: > INSERT INTO MyTable SET timestamp = 200210211143513; > > and then: > SELECT * FROM MyTable; > > I get : > > > timestamp > > 200201221143513 > > > This is happening on Redhat 7.1, MySQL Ver 11.15 Distrib 3.23.38, for > pc-linux-gnu (i686) > > when I type: > SELECT NOW(); > > I get the correct date, and when I type: > INSERT INTO MyTable SET timestamp = NOW(); > > that also increments the timestamp by one day just like it does > when I set > the > timestamp manually. > > Thanks, > > Stuart > > > _ > Surf the Web without missing calls! Get MSN Broadband. > http://resourcecenter.msn.com/access/plans/freeactivation.asp > > > - > 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
FW: Select statment
John, StudioName? GenreName? F_Name? L_Name? DatabaseName? ServerName? Alan I can't seem to figure out the select statement to get the name. Do I need the the StarID or ActorID in Titles table? Thank you. I am using PHP and MYSQL. Studios (StudioName, StudioID) Genres (GenreName, GenreID) Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) Actors (F_Name, L_Name, ActorID) Stars (TitleID, ActorID) TitleGenres (TitleID, GenreID) - 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: a mysql question
I think you need to give us the SQL you are using to do the search - we'll ba ble to see what your are trying to do better Alan > -Original Message- > From: µÑ ¶Ì [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 17 October 2002 15:37 > To: [EMAIL PROTECTED] > Subject: a mysql question > > > I'm a chinese software engineer, and I has use mysql > for three years ago.Now,I get a matter from it. > recently,I'm deal big data use mysql first.every > table's data is one million,so user search the data > will use long time.now ,if some user search one table > togeter,the table is dead , and I find the connection > to the mysql cann't close,my God,I only restart it. > > my develop language is JAVA.But I can't think the > error is in JAVA code.my question is wheather mysql > can't support a lot of connection search one table > together? > > I want get a help or an advance for you ,thanks. > > _ > Do You Yahoo!? > ÐÂÏʵ½µ×,ÓéÀÖµ½¼Ò - ÑÅ»¢ÍƳöÃâ·ÑÓéÀÖµç×ÓÖܱ¨! > http://cn.ent.yahoo.com/newsletter/index.html > > - > 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: Inserting Master and Details records
Sorry your second link makes that claim a little clearer - it's on a per-connection basis Alan > -Original Message- > From: Roger Baklund [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, 16 October 2002 23:58 > To: [EMAIL PROTECTED] > Cc: Alan McDonald > Subject: Re: Inserting Master and Details records > > > * Alan McDonald > > My task is to insert a new master record and several detail > records within > > the one transaction. > > > > There is a foreign key on the detail table set to the unique > key (autoinc) > > field of the master table. > > > > Inserting a master record, even with a special field value so > > that it can be > > quickly returned with the newly created primary key, so that I > might then > > insert the detail records with this primary key as their > foreign key, does > > not seem very reliable to me (as suggested a few days ago). > > > > There must be a more reliable way to do this under heavy traffic. > > I'm afraid > > I'm used to being able to grab a generator ID and using that > > (guaranteed to > > be unique) for both the primary key of the master and the foreign > > key of the > > detail records. Surely there is a good method for use with MySQL? > > Yes, there is. Check out the function LAST_INSERT_ID(): > > http://www.mysql.com/doc/en/Miscellaneous_functions.html > > http://www.mysql.com/doc/en/mysql_insert_id.html > > > -- > Roger > - 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: Inserting Master and Details records
Peter, Thanks you - I looked up Insert_ID() in the manual... Page 171-172 If I insert into person but before I insert into short, someone else inserts into person, surely my inserts into shirt will have their last Insert_ID()? Do you know if this is true only in the same connection context only? Or does insert_id() return another connections last insert? Alan > -Original Message- > From: Peter Lovatt [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 17 October 2002 0:59 > To: Alan McDonald; [EMAIL PROTECTED] > Subject: RE: Inserting Master and Details records > > > Hi > > insert_id returns the key value. I use php, and the mysql_insert_id is the > last insert_id on that connection, so even if other processes > have added new > records in the time the script runs the insert_id is the correct one. > > You can then use that as the key in the detail records. > > HTH > > Peter > > --- > Excellence in internet and open source software > --- > Sunmaia > www.sunmaia.net > tel. 0121-242-1473 > --- > > -Original Message- > From: Alan McDonald [mailto:[EMAIL PROTECTED]] > Sent: 16 October 2002 13:45 > To: [EMAIL PROTECTED] > Subject: Inserting Master and Details records > > > My task is to insert a new master record and several detail records within > the one transaction. > > There is a foreign key on the detail table set to the unique key (autoinc) > field of the master table. > > Inserting a master record, even with a special field value so > that it can be > quickly returned with the newly created primary key, so that I might then > insert the detail records with this primary key as their foreign key, does > not seem very reliable to me (as suggested a few days ago). > > There must be a more reliable way to do this under heavy traffic. > I'm afraid > I'm used to being able to grab a generator ID and using that > (guaranteed to > be unique) for both the primary key of the master and the foreign > key of the > detail records. Surely there is a good method for use with MySQL? > > Alan McDonald > http://www.meta.com.au > > > > - > 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
Inserting Master and Details records
My task is to insert a new master record and several detail records within the one transaction. There is a foreign key on the detail table set to the unique key (autoinc) field of the master table. Inserting a master record, even with a special field value so that it can be quickly returned with the newly created primary key, so that I might then insert the detail records with this primary key as their foreign key, does not seem very reliable to me (as suggested a few days ago). There must be a more reliable way to do this under heavy traffic. I'm afraid I'm used to being able to grab a generator ID and using that (guaranteed to be unique) for both the primary key of the master and the foreign key of the detail records. Surely there is a good method for use with MySQL....? Alan McDonald http://www.meta.com.au - 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 downgrading
On Tue, Oct 15, 2002 at 03:41:24PM -0700, Karl Stubsjoen wrote: > Hello, > > Any help downgrading my MySQL Server from 4.0.0.0ALPHA to 3.23 would be a > huge help. I'm not sure where to start. Do I need to save records to a > flat file and then reimport them? I'm not sure if this is the right answer, but under gentoo I've moved from 3.x to 4.0.x and back again with no changes to the database, re-importing, or anything, I just installed the new mysql and restarted it. If I'd *thought* before doing this I would have doing a "mysqldump -c --all-databases" first, but I didn't :) Lucky for me nothing happened! YMMV of course, but I'd probably suggest just doing a mysqldump to make sure you have your data safe, and then just switch binaries. Depending on the OS you're running and the packaging system you're using you may have to worry about it deleting /var/lib/mysql when you uninstall the old package, but I'd hope that package maintainers wouldn't just nuke a db directory without warning :) alan -- Alan "Arcterex" <[EMAIL PROTECTED]> -=][=- http://arcterex.net "I used to herd dairy cows. Now I herd lusers. Apart from the isolation, I think I preferred the cows. They were better conversation, easier to milk, and if they annoyed me enough, I could shoot them and eat them." -Rodger Donaldson - 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: Problems installing on Solaris/Intel
- Original Message - From: "Jesse Sheidlower" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, October 15, 2002 5:07 PM Subject: Re: Problems installing on Solaris/Intel > > I did check out the link below, in which someone else has an > error identical to mine, and Sinisa Milivojevic replied > basically saying that the answer is described in detail in the > Manual. > > Well, I can't find it in the Manual. There are things somewhat > related in the various Solaris sections, and I've tried them, > such as adding -DHAVE_CURSES_H to the CFLAGS and CXXFLAGS, > adding /opt/sfw/lib to the LD_LIBRARY_PATH variable, and > adding the flags suggested at the Solaris X86 section. > > None of these work; it always breaks in the same way at the > same place. > > I appreciate that it's probably my ignorance that's preventing > me from figuring this out, if it is indeed described so obviously > in the Manual, but I've discussed it with someone who's very > knowledgable about MySQL and he's stumped too. So I would be > very grateful if anyone could explain to me what it is I'm > missing. > > I seem not to have mentioned in my original post that I'm running > gcc 2.95.2 and SunOS 5.8 on x86. First, install gcc 3.2. Then, check out this link (thanks to John Warburton): http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:116929:200208:ngkbacmgkmgkdbbf gjdo The line numbers are different as of the latest MySQL version, but modifying the configure script as shown (just search for some keyword using your favorite text editor) works like a charm, promise. Also, if you have crle configured already, you needn't worry about the LD_LIBRARY_PATH variable. I don't have it set on any of my boxen, and everything runs peachy. -- Alan W. Rateliff, II: RATELIFF.NET Independent Technology Consultant :[EMAIL PROTECTED] (Office) 850/350-0260: (Mobile) 850/559-0100 - [System Administration][IT Consulting][Computer Sales/Repair] - 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: full-text search problems (newbie)
On Sun, Oct 13, 2002 at 10:58:53PM +, Sergei Golubchik wrote:
> Hi!
>
> On Oct 13, Alan wrote:
> >
> > mysql> select title from content where match
> > (title,center_content,right_content) against ('updates');
> > +-+
> > | title |
> > +-+
> > | eXI Systems' Latest Updates |
> > +-+
> > 1 row in set (0.00 sec)
> >
> > But when I try to match on 'exi', I get:
> >
> > mysql> select title from content where match
> > (title,center_content,right_content) against ('exi');
> > Empty set (0.00 sec)
>
> Try MATCH ... AGAINST (... IN BOOLEAN MODE).
> If it'll work - it means that the word is present in more than 50% of
> rows. See the manual about 50% threshold.
Ah, it all makes sense now! Looks like my simple example search was
fubar due to the 50% thing :\ Mucho thanks!
--
Alan "Arcterex" <[EMAIL PROTECTED]> -=][=- http://arcterex.net
"I used to herd dairy cows. Now I herd lusers. Apart from the isolation, I
think I preferred the cows. They were better conversation, easier to milk, and
if they annoyed me enough, I could shoot them and eat them." -Rodger Donaldson
-
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: full-text search problems (newbie)
On Sun, Oct 13, 2002 at 02:48:38PM -0500, Paul DuBois wrote: [snip] > >As far as I can tell, this *should* be working. Can anyone help please? > > Try dropping the FULLTEXT index and then add it again. (Alternatively, > dump and reload the table). I did drop the table this morning, just in case, but when I execute the query again, I still get the same results. Any other ideas? This works for others I am guessing. I've tried this on the binary 4.0.4-beta on a redhat 7.2 system and 4.0.1-alpha compiled from source on a gentoo system. -- Alan "Arcterex" <[EMAIL PROTECTED]> -=][=- http://arcterex.net "I used to herd dairy cows. Now I herd lusers. Apart from the isolation, I think I preferred the cows. They were better conversation, easier to milk, and if they annoyed me enough, I could shoot them and eat them." -Rodger Donaldson - 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
full-text search problems (newbie)
Hi folks, new to the list, hoping I'm posting to the right one.
I've been using the full-text search for a clients site and it works
great... the only problem is I want to change the minimum word length
from 4 to lower, such as 2 or 3. Reading the docs this is simple, and I
followed the instructions I found on various newsgroups and of course
the mysql documentation.
(BTW, I'm using the linux binary distribution of 4.0.4-beta)
Anyway, in /etc/my.cnf I set:
set-variable = ft_min_word_len=3
And then I did an "alter table foo type=MyISAM" as instructed, restarted
mysql, and logged in.
I can see the ft_min_word_len is set to 3 with show variables
| flush_time | 0 |
| ft_min_word_len | 2 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort| 20 |
My table is set up as follows:
CREATE TABLE content (
articledate int(10) NOT NULL default '0',
title varchar(255) default '',
center_content text,
right_content text,
main_content int(10) default '0',
token varchar(64) default '',
UNIQUE KEY sd (articledate),
KEY ti (title),
FULLTEXT KEY txt (title,center_content,right_content)
) TYPE=MyISAM COMMENT='Content Table';
When I execute the following statement, I get results:
mysql> select title from content where match
(title,center_content,right_content) against ('updates');
+-+
| title |
+-+
| eXI Systems' Latest Updates |
+-+
1 row in set (0.00 sec)
But when I try to match on 'exi', I get:
mysql> select title from content where match
(title,center_content,right_content) against ('exi');
Empty set (0.00 sec)
As far as I can tell, this *should* be working. Can anyone help please?
Regards and TIA.
alan
--
Alan "Arcterex" <[EMAIL PROTECTED]> -=][=- http://arcterex.net
"I used to herd dairy cows. Now I herd lusers. Apart from the isolation, I
think I preferred the cows. They were better conversation, easier to milk, and
if they annoyed me enough, I could shoot them and eat them." -Rodger Donaldson
-
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
weblogic integration problem
I am trying to create a mysql connection pool in Weblogic 6.1 using the JConnector driver and failing. All I get is "no suitable driver" which usually means that the URL and properties aren't right for the driver I'm using. I defined the pool as: name: MysqlPool URL: jdbc:mysql//192.168.1.100:3306/Objectworks?user=root;password=jude370 driver: com.mysql.jdbc.Driver properties: user=root;password=jude370 I put the jconnector jar file at the head of the weblogic classpath. Before that, it complained about not finding the driver class. THe properties seemed redundant but when I removed them, weblogic complained that there were no properties - apparently, properties are required. The URL fits the pattern defined in the JConnector Readme file. So, I'm just stumped. Can anyone tell me the correct incantation to get the jconnector driver to work in this context? thanks, ag - 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 I compiled MySQL on Solaris 8
- Original Message - From: "John Warburton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 13, 2002 3:17 AM Subject: How I compiled MySQL on Solaris 8 > Hi All > > It seems that compiling MySQL on a Solaris 8 box is a non-trivial task if your > environment is different to the MySQL developers (whatever that is). It has > taken me a couple of days, hacking & trawling the mailing list to get a > successful compile. I thought I would share my findings with others, and hope > that maybe it might be incorporated into the build and documentation (do I have > to submit a bug??). Worked like a charm. Thanks for all your hard work. Looks like I'll be updating my aged HOWTO :) -- Alan W. Rateliff, II: RATELIFF.NET Independent Technology Consultant :[EMAIL PROTECTED] (Office) 850/350-0260: (Mobile) 850/559-0100 - [System Administration][IT Consulting][Computer Sales/Repair] - 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: Tables in MySQL
Don't do it this way. Do something like the following:
Assume two tables, "transaction" and "itemlist".
transaction
---
transaction_id
customer_id
purchase_date
whatever other junk is relevant to a given transaction...
itemlist
transaction_id
item_id
here is a way do it in php then just off the top of my head as an example.
$sql = "select transaction_id from itemlist where item_id = 12";
$result = mysql_query($sql,$link);
//loop over transaction_ids
while ($row = mysql_fetch_object($result) ) {
$transaction_id = $row -> transaction_id;
// get all the items from each of those transactions
$sql = "select item_id from itemlist where ".
"transaction_id = '$transaction_id' and ".
"item_id != '12' ";
$result2 = mysql_query($sql,$link);
// loop over these items and tabulate the frequency
while ($row2 = mysql_fetch_object($result2)) {
// add 1 to the array of possible suggestions in
// the index of the item_id you found
$suggestion[$row2 -> item_id] += 1;
}
// sort the suggestion list so that the ones with the
// highest totals are on the top and get the item_ids
// of the first few or something like that...
}
whiskyworld.de wrote:
> Hi,
>
> im currently developing a Webshop system. One of the new features of it
> should be a "Costumers that bought this product also bought" feature -
> concerning this im currently unsure how to implement it - (LAMPS) - my
> current thought is following:
>
> Costumer A buys Products with NO: 12, 13 , 25 -> system says OK, looks for
> Tables 12,13,25 -> finds nothing creates table 12, inserts 13 and 25 and
> sets sold of each to 1, then creates table 13 and 25 and inserts like it did
> in table 12
>
> now cosumter B buys products 13,12,19 -> system says OK, looks for tables
> 13,12,19 and finds only 12 created, adds 19 into table 12 and updates sold
> from 13 in table 12 -> then does this with table 13 and finally creates
> table 19 (because new) and inserts like in Cosumter A's way...
>
> now the question: is MySQL aware of being with over 1500 tables ??? - is
> there a better way or more efficent way to do the same ?
>
> Hope sb. knows a trick :)
>
> Yours Sincerely
>
> Korbinian Bachl
> www.whiskyworld.de
--
Alan E. Munter NIST Center for Neutron Research
Physical Scientist 100 Bureau Dr., Stop 8562
[EMAIL PROTECTED] Gaithersburg, MD 20899-8562
http://www.ncnr.nist.gov/ (301)975-6244
-
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: Internal Select statements using IN and NOT IN
Never mind, I figured it out by reading a previous question and I understand how to use Left join to do this. -Original Message- From: Alan Coleman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 5:22 AM To: [EMAIL PROTECTED] Subject: Internal Select statements using IN and NOT IN I've noticed that MySQL doesn't support the use of internal selects with the in and not in methods such as in this example from a where statement. and shift_id not in (select shift_assoc from facdba.staff_schedule_temp where status <> 'EXL') This functionality is available in Oracle but I'm trying to use a program I wrote on a sever running MySQL. I'm able to do everything else with some changes to my sql statements, but I need the ability to do an internal select on the fly in my sql statements so that I can compare a value from one table to a list of results from another select. In this particular example, I do not have the ability to call the internal select first and then insert it into the next database call. It needs to be done on the fly because unlike the simple example above, this call is very complex and involves may variables and unioned selects. Joins don't work especially since I'm using the "NOT IN" clause. I find it really hard to believe that MySQL doesn't have support for this or some other functionality to replace it. I mean it has everything else and then some. But I've searched the documentation and haven't found anything. If anyone knows a way to perform an internal select similar to what is shown above, please let me know. --- Alan Coleman [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
Internal Select statements using IN and NOT IN
I've noticed that MySQL doesn't support the use of internal selects with the in and not in methods such as in this example from a where statement. and shift_id not in (select shift_assoc from facdba.staff_schedule_temp where status <> 'EXL') This functionality is available in Oracle but I'm trying to use a program I wrote on a sever running MySQL. I'm able to do everything else with some changes to my sql statements, but I need the ability to do an internal select on the fly in my sql statements so that I can compare a value from one table to a list of results from another select. In this particular example, I do not have the ability to call the internal select first and then insert it into the next database call. It needs to be done on the fly because unlike the simple example above, this call is very complex and involves may variables and unioned selects. Joins don't work especially since I'm using the "NOT IN" clause. I find it really hard to believe that MySQL doesn't have support for this or some other functionality to replace it. I mean it has everything else and then some. But I've searched the documentation and haven't found anything. If anyone knows a way to perform an internal select similar to what is shown above, please let me know. --- Alan Coleman [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with JDBC2 driver across databases
Thanks Jeff, I'll see if it fixes it. I've got round the problem
in the meantime by opening two connections, one to each database,
but that's a bit messy and shouldn't be necessary.
Incidentally, the MySQL page for JDBC
(http://www.mysql.com/downloads/api-jdbc.html)
still offers version 2.0.4 for download. Should this be updated?
--Alan
-Original Message-
From: Jeff Kilbride [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 25, 2002 6:44 PM
To: Alan Jones; [EMAIL PROTECTED]
Subject: Re: Problem with JDBC2 driver across databases
First, I would recommend upgrading to the latest release of mm.mysql --
which I think is 2.0.12. You can get the latest from sourceforge:
http://mmmysql.sourceforge.net/
2.0.4 is pretty old now.
--jeff
- Original Message -
From: "Alan Jones" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 25, 2002 7:10 AM
Subject: Problem with JDBC2 driver across databases
> Hello.
>
> There appears to be a problem when using the JDBC driver on a
> foreign database (i.e. not your "current" database).
>
> MySQL version is : 3.23.49-max-debug
> JDBC version is : mm.mysql-2.0.4-bin.jar
>
> Consider the following situation:
>
> use sales;
> create table contacts (uid integer, name varchar(30));
> ...
> use sales_demo;
> select * from sales.contacts where ...;
>
> This behaves as expected; the rows are shown.
>
> Now try this in JDBC to insert a new row into sales.contacts.
>
> Connection conn =
> DriverManager.getConnection("jdbc:mysql://localhost/sales_demo");
> PreparedStatement ps = conn.prepareStatement("select uid, name from
> sales.contacts where uid = ?",
> ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
> ps.setInt(1, 42);
> ResultSet rs = ps.executeQuery();
> if (!rs.next())
> {
> rs.moveToInsertRow();
> rs.setInt(1, 42);
> rs.setString(2, "Fred Bloggs");
> rs.insertRow();
> }
>
> An SQLException is reported saying that table "sales_demo.contacts" does
not
> exist. It should
> be inserting into table "sales.contacts".
>
> --Alan
>
>
>
>
>
> Alan Jones, Senior Software Engineer
> Yospace: Creating Value for Wireless
> 7 The Courtyard, High Street, Staines, UK, TW18 4DR
> Tel: +44 1784 466388
> Fax: +44 1784 466387
> http://www.yospace.com
>
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> Any unauthorised dissemination or copying of this email or its
attachments,
> and any use or disclosure of any information contained in them, is
strictly
> prohibited and may be illegal. If you have received the email in error
> please notify [EMAIL PROTECTED] and delete it from your system.
>
>
>
> -
> 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: Problem with JDBC2 driver across databases
Thanks Mark. I've now downloaded 2.0.13 and I'll see if the
problem is still there. Meanwhile I've used a workaround involving
two connections, one to each database, so the insert works.
Not ideal, though.
--Alan
-Original Message-
From: Mark Matthews [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 25, 2002 10:08 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Problem with JDBC2 driver across databases
MM.MySQL uses the table name provided by MySQL itself to produce the query
for updatable result sets, so it appears as if MySQL server is returning the
wrong table name for your query.
-Mark
Original message:
------
From: "Alan Jones" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Problem with JDBC2 driver across databases
Date: Thu, 25 Apr 2002 15:10:58 +0100
Message-ID: <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Hello.
There appears to be a problem when using the JDBC driver on a
foreign database (i.e. not your "current" database).
MySQL version is : 3.23.49-max-debug
JDBC version is : mm.mysql-2.0.4-bin.jar
Consider the following situation:
use sales;
create table contacts (uid integer, name varchar(30));
...
use sales_demo;
select * from sales.contacts where ...;
This behaves as expected; the rows are shown.
Now try this in JDBC to insert a new row into sales.contacts.
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost/sales_demo");
PreparedStatement ps = conn.prepareStatement("select uid, name from
sales.contacts where uid = ?",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ps.setInt(1, 42);
ResultSet rs = ps.executeQuery();
if (!rs.next())
{
rs.moveToInsertRow();
rs.setInt(1, 42);
rs.setString(2, "Fred Bloggs");
rs.insertRow();
}
An SQLException is reported saying that table "sales_demo.contacts" does not
exist. It should
be inserting into table "sales.contacts".
--Alan
-
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
