DROP TEMPORARY TABLE waiting for table???
I'm seeing something that I don't think should be happening, but I'm not sure if it's a MySQL bug. To allow some of my stored procedures to operate concurrently, I make a temporary memory copy of some shared tables that are accessed by the procedures. The temporary heap table has the same name as the table I'm copying. The scenario here is a data warehouse with a bunch of data marts and some shared dimensions that are in a separate database. I'm copying the necessary rows of the commonly used shared dimension tables, to overcome the problem of stored procedures locking all the tables they're going to use, which was preventing concurrency. The problem is that despite this, I'm seeing processes that are stuck with status Waiting for table when they are trying to drop the temporary table if it exists (DROP TEMPORARY TABLE IF EXISTS shared_dimensions.page_dim). I always drop and recreate it at the start of a series of analyses, so that they have the most recent copy. I create the temporary heap table in an independent procedure, so it can't be locking the table. There are other procedures using their own temporary heap table copies of the table I'm copying, but they're not using the real table, only the copy. So... my question is, why is there any problem dropping a table that should only be visible to the connection that's trying to drop it? What's even more bizarre is that I get this problem even when the temporary table doesn't exist, on a brand-new connection. I've had this code running for a couple of weeks and just noticed the problem, so I'm not sure if it cropped up right away or not. Haven't had a chance to bounce the server yet. Any insight appreciated. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ANN: Database Workbench 2.8.5 released!
On 3/27/06, Martijn Tonies [EMAIL PROTECTED] wrote: Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.5 has been released today! I'd like to hear more about the stored procedure debugger -- does all the functionality in the documentation work with MySQL? Anybody used this with MySQL, who could describe their experience with it? I would really love a good SP debugger! Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198
Can't materialize a view -- bug?
I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit a strange error. Here's the scenario. CREATE OR REPLACE VIEW x_view AS SELECT [select statement, blah, blah, blah]; DROP TABLE IF EXISTS x_mview; CREATE TABLE x_mview SELECT * FROM x_view; That was all working fine until I created a function that I use inside of the view. Now the last statement, the one that would materialize the view, returns a table locking error! ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES It's difficult to lock a table that doesn't exist yet... SELECT * FROM x_view works fine. The function is a simple bit of logic (it tests a count, moving average and standard deviation to see if the count is more or less than two standard deviations from the mean): CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv MEDIUMINT(8)) RETURNS TINYINT(1) DETERMINISTIC RETURN IF (ma 9 AND stdv 0 AND (cnt = ma + (2 * stdv)) OR cnt = ma - (2 * stdv), IF (cnt = ma + (2 * stdv), 1, -1), 0); If this is a bug, I'll be happy to file a report... but I'd really like a solution that will let me use the function. I hit a problem using functions in stored procedures, too, and I'm wondering if these are related. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198
Bug? Date insert comes out zero on Solaris using Python MySQLdb?
I have Python code that I run on Windows and Solaris. It works properly on Windows. However, on Solaris, when it inserts records, datetime columns end up with a value of zero. I suspect that this began happening after we upgraded the MySQL server to 4.1.10 from a 4.0.x version (I think we were on 4.0.18). I see some changes to datetime handling in the 4.1.x releases, but nothing that seems immediately obvious. I hope to fix this is by re-building MySQLdb based on the 4.1.10 libraries. Any other suggestions? I'm using MySQLdb.times to format the datetimes before inserting. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't get table lock (4.0.23 and InnoDB)
We have something I can't figure out happening on one of our servers. It's running 4.0.23 on OSX. One of the InnoDB tables is locked even though we can't see any process that is even active that could have locked it. SHOW OPEN TABLES doesn't show it as in use or locked. We've tried all sorts of things to get access to it, short of bouncing the server, but it keeps saying it's locked. When we try to acquire a lock on that table, the process list says SYSTEM LOCK and we get the good old Lock wait timeout exceeded; Try restarting transaction message after 30 seconds. I've tried this with AUTOCOMMIT on and off, with LOCK TABLES and with START TRANSACTION... nothing seems to help. Strangely, FLUSH TABLES WITH READ LOCK seems to succeed, but after unlocking them, we still can't get a lock for this table. I've never been able to see how we can find out which connection has the lock on a given table. If that's possible, I'd like to know that, too, so I could at least know how it got locked in the first place. Could a misbehaving client do this in a way that would cause the lock to persist even after the client has disconnected? If so, ugh. Any help appreciated. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT and UPDATE together, with LIMIT? (incremental external indexing)
[EMAIL PROTECTED] wrote: Your solution is as good as any I can think of. If your source tables are InnoDB you could wrap your SELECT/UPDATE processing in a transaction to help make it even more robust. It might speed things up if you omit the offset to your LIMIT clause and just do: Yes, they are InnoDB and I routinely now do such things in transactions. And I did remove the LIMIT offset, which was from the first tests. that way you always find the first 5000 un-indexed records, regardless of physical position. Record order is not guaranteed unless you use an ORDER BY statement and that would just slow you down, wouldn't it? Indeed, which was one reason I went with the HEAP table... which is also a TEMPORARY table so that two clients can work concurrently. Thanks. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
listsql listsql wrote: Since I read about Foaf [ http://www.foaf-project.org/ ], I become interested with Social Networking, What you're doing is often called link analysis -- searches on that term may yield more for you to chew on. There are software tools and visualization tools for answering the kind of questions this data covers. For the latter, Pajek (http://vlado.fmf.uni-lj.si/pub/networks/pajek/) is one of the most interesting. More generally, you're storing a graph in a relational database. Searching on that subject will show you several approaches. The way I deal with this is to create tables pretty much as you have, but instead of self-joins in MySQL, I load it all into a program that stores the graph in hashes, then uses recursion to get the kind of answers you want. This is much, much, much faster than doing the same in the database, though it might become memory constrained if you have a big network. In that case, my next step is to do all of the recursions and store the results in the database as pairs and their distances from one another. Then it's a simple lookup. If you do find a way to do this efficiently in MySQL, I'll be interested! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
Peter Brawley wrote: is, there is some method to iterate in this relation to avoid joining the table in itself each time ? Exactly the problem with trying to model an anything-goes network, a world of ends, in a relational model of a directed graph. I think you need an XML layer in there, eg see http://iswc2004.semanticweb.org/demos/03/. XML isn't necessary and probably just makes it more complicated. As someone wrote, if you have a problem and decide to solve it with XML, now you have two problems. On the other hand, since the semantic web relies on graphs, approaches that work for it do apply... but unless interoperability matters, it's hard for me to imagine a good reason to bother with XML. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table scan in join on primary keys??
I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed with Fields
Dathan Pattishall wrote: Use char And use fixed-length types for *all* columns... one variable-length column makes all records variable-length. Nick -Original Message- From: Marc Michalowski [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 10:28 AM To: mysql@lists.mysql.com Subject: Fixed with Fields I was wondering if there is a way to create fixed width fields. Example: The field is set to 18 but data contained is 11. I need the length to remain 18. Is there anyway to do this? Thanks for your help. -Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners wrote: Is there any other workaround? The reason because we are using InnoDB is because there s full support - for foreign keys, - for joint queries - for rollback on commit Does anybody know any other way of indexing the table in a way, which would allow full text search? Sure -- use an external search engine that has database hooks (or create your own connector). Depending on the sophistication (or existence) of a database connector for the search engine, you'll have to write more or less code to tell it how them to talk to each other -- how to know when there's new data to index, how to retrieve the text data into the search engine for indexing or display. The most sophisticated ones use database triggers to make it all fairly easy. Otherwise, you'll need to write code that hands the text and a pointer (typically the primary key) to the full-text engine when a record is added or modified, and the pointer for deletes. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. Could you explain a bit more about how this relates to MyISAM? Is it just that using LOCK TABLES with InnoDB was causing a lot of deadlocks? If so, that would explain what I've been seeing in MySQL 4.0.21 (lots of deadlocks on a very small table that I use for managing parallel processes). Until we upgrade (which will be soon, I think), is it best to turn AUTOCOMMIT off and COMMIT when appropriate? Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. That's what I was just reading! So... is this the equivalent of using BEGIN and COMMIT, for which I have methods in the Python MySQLdb module? Or is there an advantage to the latter? Thanks again, Nick Arnett Director of Business Intelligence Services Liveworld Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT or SHOW full table name?
I don't see a way to ask MySQL what the full name of a table is... the equivalent of this: SELECT CONCAT(DATABASE(), ., table_name) which would return something like this (assuming the current database is called my_database: my_database.table_name The reason I want this is to ensure that a scripted operation isn't treating the same table as if it were two different tables. I have a method that moves records among tables by copying and then deleting. If the two tables are really the same table, it'll lose the records, since it will have copied them to the same table, then delete them. I can do it the way above, but I'm thinking there might be a better way... Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Really slow shutdown with Innodb, db not accessible?
For the last four hours or so, I've been waiting for MySQL (4.0.12 on W2K) to complete a shutdown. The fast shutdown flag is not set (innodb_fast_shutdown=0), so I assume it is doing a purge and merge... but in the meantime, I don't have any access to the server -- clients simply can't connect. This is a real problem, since it renders the database useless for a long period of time. My Innodb table is about 15 GB and probably has about 10 million records in various tables. When the darn thing finally shuts down, I'll restart with fast shutdown on, but I'm wondering how foolish it would be to kill the process, given that Innodb should then do a crash repair. Would the crash repair take longer than what it's doing now? Would the server be inaccessible as it is now? Besides enabling fast shutdown, what else will help avoid this kind of thing in the future? Thanks for any info... -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic Database Design Question
This is, as you guess, a very basic issue for relational databases. You never want to put multiple entries in one column, as you describe it. That's the scenario in which you create another table and use a key to join the two. For your project, you should have a column in the nms_apps table that uniquely identifies it -- the primary key, usually. You'll want to create a table, perhaps called 'user,' containing the 'used_by' information. That table would have, at a minimum, a column for the nms_apps key and a column that contains the user information. Then to find out who uses a given application, the query would be along these lines (this uses the column 'app_id' as the key: SELECT used_by FROM nms_apps, user WHERE nms_apps.app_id = user.app_id AND app_name = Application Foo Hope that helps. If you grasp this, you'll have the basic idea of relational data. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow shutdown with Innodb, db not accessible?
-Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 12:58 PM crash recovery is usually much faster than purge and merge. Killing the mysqld process is a legal (and the fastest :)) way of shutting down InnoDB. That's good to hear. W2K tells me I don't have permission to kill the process, despite having all admin privileges, so I'll look into that now. Soon, this database will move to Linux or BSD, I hope. Why did you set fast_shutdown=0? I'm asking myself the same question... ;-) I really don't remember. The last time I changed the config was when I started using Innodb, four or five months ago. Don't know what the heck I was thinking. By the way, I am not sure the setting really affects the variable value at all, since in versions 4.0.15 there was a bug that it was specified as a NO_ARG parameter. I noticed some of your other messages about that. It's probably time for me to update. Thanks very much. I really appreciate the speed with which you respond (not just to my messages, I read the list regularly). Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
My hair... I am ready to tear it out. I've been working with Python and the MySQLdb module for a long time, thought I couldn't get snagged by anything, but today I just can't seem to persuade the blasted thing to let me stick an HTML document (a string) into a TEXT column. I'm getting SQL syntax errors, as though the document isn't properly escaped, even though I'm using substitution so that MySQLdb should be taking care of that. I'm wondering if I'm missing something terribly obvious, because this is dead-simple code. Here's the relevant bit and a couple of surrounding lines. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) It's retrieving the document just fine, resulting in a big ol' string (it really is a string, I checked), but that string just won't go into the database. rss_article is a TEXT column in a MyISAM table. I get an SQL syntax error and MySQL tells me to check the syntax with a snippet from right near the beginning of the HTML, where it has lots of (annoying, I suppose) backslashes, quotes and other stuff that is a pain to encode properly by hand. Any help will be most gratefully accepted. My hair will thank you, too. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 9:38 PM To: Nick Arnett; [EMAIL PROTECTED] Well, Paul, just knowing you were on the job inspired me and I finally realized the dumb thing I'd done. The list I'm iterating, urls, comes from a single-column MySQL results list, so it's a list of tuples (url,), not strings. That's why I open url[0], rather than just url in the first line inside the loop. I was smart enough to do that, but not smart enough to remember to use url[0], rather than url, in the INSERT statement. So I was trying to insert a tuple, not a string, and thus MySQL barfed. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) - Have you tried this with *short* HTML documents? What happens? This would help you determine whether it's a length-of-data issue. - Let's see an actual error message (at least the first part), and the corresponding text of the document. - What shows up in the server's query log? By the way, what helped me figure this out was switching the column names and values, which changed the string in the error message to )), rather than the start of the HTML doc. That's when I realized the problem was something about url, not the HTML. This one has bitten me before, but it's been a long time... Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query
-Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 12:05 PM To: 'Ulterior'; [EMAIL PROTECTED] Subject: RE: Insert query Hi, I would use mediumint rather than int for the ID column (int has support for up to 2.1 Billion records wheras mediumint is up to 8.3 million - more efficient for your data type). If he only has a few million records, why would this be more efficient? Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL DB PROBLEM
-Original Message- From: Adam Murphy [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 10:48 PM To: [EMAIL PROTECTED] Subject: MYSQL DB PROBLEM I am trying to insert a table ibf_posts into a localhost database using the MYSQL control center every time i try to insert that one table i get an error [forum] ERROR 2013: Lost connection to MySQL server during query Are you trying to insert a lot of data at once? If so, that will produce this error if it exceeds a certain size. The solutions are to insert fewer records at once or increase MAX_ALLOWED_PACKET, if I recall the correct variable. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query
-Original Message- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 7:35 AM To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior Don't use varchar unless you absolutely have to, that should help. Jerry - Original Message - From: Ulterior [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:13 PM Subject: Insert query Hi, I have a database table: CREATE TABLE FTPFILE ( ID int(11) NOT NULL auto_increment, FTPSITEID int(11) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE int(11) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID) ); when this table reaches 1 records, insert queries are very slow, aproximately 2 records a second. And worst of all mysql-ntd.exe reaches 99% of my processor timing. I am using native mysql C API mysql_query() func for inserting data. Is there any way to speedup insert querys to this table? ( I am planning to have at least 2-5 million entries in this table ) Your VARCHAR columns can become CHAR columns, which should help a lot. But CHAR columns can't be longer than 255, so you're about at the limit. Are you locking the table before inserting, then using the multiple insert syntax? Those should help if you're not doing them, but I don't know what it is in the C API. How many indexes do you have? If you reduce the number of indexes, insertions will go faster, but that might slow down some of your queries, of course. If you're inserting quite a few at a time, you might disable indexing, do the inserts, then allow the indexing to happen all at once. For a large number of inserts, you might also try writing them to a file, then loading it. That's much faster for really large numbers of inserts, and you can also suspend indexing as I mentioned. I use the latter for building externally generated text indexes (frequency tables and such), generating a couple of million records at a shot. It made an enormous difference. But I'm working in Python and can't help you with the C API, as I said. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't this query work?
-Original Message- From: Mikey [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:53 AM To: [EMAIL PROTECTED] Subject: Why doesn't this query work? OK, first of all thanks for the pointers, however, the query I now have doesn't seem to work. If I run the query up until pricelevel IN (1, 2, 3, 4, 5) it returns a large result set (the clauses are fairly inclusive), however, when I add in the rest of the query no results are returned. Any ideas? This is just a guess, but perhaps you are comparing INTs to STRINGs with that last bit, in which case you'd want to change the column type for pricelevel or put quotes around the numbers in the query? I hit that problem all the time using 1 and 0 as Booleans in an ENUM column. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem starting mysql server
-Original Message- From: Rehaz Golamnobee [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:41 AM ... I have just upgraded my MySQL from version 3.23 to 4.0.13. However I cannot start the server. When I type mysqld_safe I get the following : [1] 1730 Linux:/# starting mysqld-max daemon with databases from /var/lib/mysql 030529 11:10:51 mysqld ended [1]+ Done mysqld_safe Sounds like it's never starting up at all, which explains why you can't connect to it. First thing I'd do is check your config file. Are you using the same config file as you did with 3.23? If so, you almost surely need to update it to suit 4.x, working from the examples that came with it. I don't recall details, but I'm quite sure that there are variable settings that will cause this behavior. You might first try one of the generic sample config files that came with your new version, just to see if it'll start up properly. Then try modifying one variable at a time, restarting after each. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pre parsing
You could pre-pend EXPLAIN and see if it generates an error. -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -Original Message- From: Jerry [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 5:46 AM To: [EMAIL PROTECTED] Subject: Pre parsing Hi, Anyone know of a way of pre parsing a *.sql file to make sure that it is syntactically correct , opposed to having to up load a file and when it breaks loosing everything after the line with an error on it. Either that or a way of telling it to carry on after finding an error. i.e. using mysql -u user -ppassword my_dB some_file.sql With the Just do it flag ? Cheers Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stopword file format?
I've searched and searched, but I can't find anything that describes the format of a custom stopword file for fulltext indexing in MySQL. Anybody have a pointer or a description of the format? Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search -- no wildcards in phrases?
It appears to me that fulltext phrase searches cannot include wildcards. For example, I would expect app* serv* to match application server, application services, etc. But it returns no results, so I'm having to run each variation separately. Can anyone confirm that wildcards, indeed, can't be used in phrase searches. I'm doing these in Boolean mode because I need exact counts of occurrences. This is on MySQL-4.0.12-nt. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed of SELECT ... LIMIT #,#?
-Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 1:36 AM .. If you want to speed it up, you have to make it use an index. You need to add a WHERE or an ORDER BY clause. Have you tried : SELECT Message_ID, Body FROM Body_etc ORDER BY Message_ID LIMIT N,M Of course, I assume that Message_ID is indexed ;) Message_ID is the primary key. But your suggestion doesn't help. If anything, it is slower. However, I think I've figured out the right way to do this -- use a server-side cursor. I can completely get rid of the need for a LIMIT in the SELECT statement. I've never used server-side cursors before, so I am a bit surprised to see that even when I do a SELECT for all 1.5 million records, MySQL's memory usage doesn't increase a bit above where it was when I was doing the same queries using a normal cursor. All I have to do is figure out how many records I can safely insert at one shot, which is not a problem. Just noticed something odd, though, with the MySQLdb SSCursor. When close() is called, it does a fetchall(), getting any records that you hadn't retrieved, trying to load all of them into memory. It's actually calling nextset(), even though MySQL doesn't support multiple result sets. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed of SELECT ... LIMIT #,#?
-Original Message- From: Nick Arnett [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 8:48 AM ... Just noticed something odd, though, with the MySQLdb SSCursor. When close() is called, it does a fetchall(), getting any records that you hadn't retrieved, trying to load all of them into memory. It's actually calling nextset(), even though MySQL doesn't support multiple result sets. This is because MySQL requires all rows to be read from a server-side connection before issuing another query on that connection, Andy Dustman tells me. It doesn't seem to be a problem as long as you do, in fact, read all the rows, so this is more an issue of the way I was testing than the real world. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed of SELECT ... LIMIT #,#?
I'm reading 1,000 records at a time from a large table (overcoming the FT indexing problem I wrote about yesterday) and I'm discovering that as the starting record number grows larger, the retrieve speed is dropping rapidly. Any suggestions for how to speed this up? It's a strategy I use fairly often, mainly to keep from using excess memory when retrieving and/or killing the connection when inserting records. In the current case, I'm doing a simple select, no ordering, grouping, etc. This is on MySQL 4.012-nt. Somewhere in the vicinity of 700,000, retrieval speed dropped tremendously. I'm guessing that that's where index caching was no longer sufficient...? I've optimized, analyzed and defragmented the disk, all of which seemed to help a bit. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Your professional opinion Please...
-Original Message- From: Brian [mailto:[EMAIL PROTECTED] ... I have a client with approximately 2 gigabytes of un-indexed document files (includes text and graphics). He wants to be able to enter a few parameters and bring up a list of all documents that fit, and then be able to download them over a web interface - sort of like a private Google search engine. How many documents? What format are they in? Does this require just text searching or is there fielded data, too? How many users would search simultaneously? There are various search engine vendors, including Google itself. The leader is Verity. Autonomy is probably its top current competitor. But since you've posted here, are you considering MySQL? It doesn't have a particularly rich query language for text, and it's up to you to get them into the database in a usable form. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed of SELECT ... LIMIT #,#?
-Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 9:17 PM Please post the query and the output of running it thru EXPLAIN. It is likely sorting the results without an index and having to weed thru more and more records the farther back you look in the list. I'm fairly certain that's true. The query is very simple: SELECT Message_ID, Body FROM Body_etc LIMIT N,M. Those are the only fields in the table; Message_ID is a CHAR field, Body is TEXT. Here's what EXPLAIN gives: +--+--+---+--+-+--+-+--- + | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- + | body_etc | ALL | NULL | NULL |NULL | NULL | 1586994 | | +--+--+---+--+-+--+-+--- + Sorry if I didn't make that clear in my first posting. I can't quite see how to speed things up, though it dawned on me that perhaps I could add a sequence field, index it, then use it, rather than the LIMIT clause, to select the records. It was quite fast at the beginning and caught me by surprise that it slowed down so much. I'm getting there, slowly, by bumping up the max_packet_size and doing three INSERTs of 5,000 for every SELECT, so I'm grabbing about 15,000 records at a time, greatly reducing the number of SELECTs from when I was only getting 1,000. Still taking hours, but I'll be letting it run all night. By the way, I noticed that in one of your presentations, you noted that Handler_read_rnd_next is better if it's smaller. It's at 5.7 million now, after doing about 900K records. I'm assuming that's an indication that there's some inefficiency, but I'm still stumped as to how to address it. The machine I'm running MySQL on has 1 GB of memory, so I have lots of headroom to play with. And since people tend to start asking questions when they realize I'm indexing large amounts of e-mail, etc., I'll explain a little. I'm doing traffic and content analysis to track what's going on in certain large software development communities. Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating fulltext index never completes...?
I'm trying to create a full-text index on a large (2.1GB, 1.6M records), two column table (primary key and a TEXT field), using MySQL 4.0.12 on Win2000. All looks like it is proceeding well, I see .TMP files in the database directory and a couple of temporary files in TEMP. It chugs along for an hour or so, then seems to stop doing anything at all. The process is not using any CPU cycles or I/O. The first time I tried this, I left it overnight, so it had many, many hours to complete. The error log shows nothing related to this. It dawned on me that perhaps the problem had to do with double-byte characters, so I moved those records into another table, cleaned out some leftovers and optimized the table. However, I'm not absolutely certain I got them all. Just dawned on me to look at ASCII() of the first char of each field... and I'm trying that, but now I'm getting a long delay while the processlist says Opening tables. Perhaps this is a clue as to what went wrong with FT indexing? Killed the process and now I'm trying to stop and restart the daemon, but it isn't responding. Ouch. The text field contains bodies of e-mail, newsgroup and web forum messages. Some are multi-part MIME messages, so there are some long lines that are essentially garbage as far as full-text indexing is concerned. Might those also cause the problem I'm seeing?. I have some Python code that will strip out the MIME and double-byte stuff, but if there's a way to convince indexing to work, I'd rather go that way than to have to build a cleaned-up copy of the table. A couple of related feature ideas for FT indexing -- skip words over N chars, skip records using a WHERE clause. Thanks for any suggestions. If I make any progress, I'll post. If I can't solve this soon, I'll be turning to Swish-E. Anybody here have any Python code for MySQL - Swish-E? By the way, I'd be happy to discuss full-text indexing in some depth here. I'd very much like to see it working well, and I have a lot of expertise in that area -- I used to be the product manager for advanced technology at Verity. -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Creating fulltext index never completes...?
-Original Message- From: Nick Arnett [mailto:[EMAIL PROTECTED] ... It dawned on me that perhaps the problem had to do with double-byte characters, Actually, what I think I meant to say was unprintable characters with ASCII 122. Does this break fulltext indexing? Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Interesting
That syntax will always report zero rows affected, but it is very fast. If you want to know how many rows were deleted, use something like DELETE * FROM Sqs WHERE 1=1. The latter query will be much slower, as it examines each record. Nick -Original Message- From: Bhavin Vyas [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 9:40 AM To: [EMAIL PROTECTED] Subject: Interesting I did a select * as such: mysql select * from Sqs; +-+-++ | RecordState | Sqs | SqsKey | +-+-++ | L | unknown | 1 | +-+-++ 1 row in set (0.00 sec) Then, look at the message that delete from gives me '0 rows affected' mysql delete from Sqs; Query OK, 0 rows affected (0.02 sec) However, it did indeed delete one row, since now I get an empty set when I do a select *. mysql select * from Sqs; Empty set (0.00 sec) Anybody knows why, then, delete gave me a '0 rows affected' message instead of saying '1 row affected'? Mysql version 3.23.49 Regards, Bhavin. - 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
executemany UPDATE (MySQL Python)
Can anyone confirm that it is *not* possible to pass a list to the Python MySQLdb module in UPDATE operations? In other words, this kind of thing works: self.dbh.execute(INSERT INTO Foo (blah, blorg,snork) VALUES (%s,%s,%s),myList) ... But this kind doesn't appear to work for me: self.dbh.execute(UPDATE Foo SET blah=%s, blorg=%s WHERE snork=%s,myList) Doing the latter with a loop in Python is much slower than I'd expect that doing it with a list would be. Nick -- [EMAIL PROTECTED] (408) 904-7198 - 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
Strategies for maintaining tables calculated from other tables?
I'm finding that it's not quite as simple as I had imagined to maintain a table whose values are calculated by analyzing other tables. The source tables contain time series data, which can is updated several times a day. To calculate totals by day, week, month, etc., I don't want to have to start over from scratch, so I want to keep track of which records in the source tables have already been analyzed. Further complicating things, there are several analyses that take place and I may add others later. I don't always want to have to do all of them at the same time. So, at the moment, I've added some Boolean enum columns to the source data tables, showing which analyses have been done. Do the analysis, set the Booleans to true. I'm not sure I really like that approach, since it can mean adding columns to rather large tables at times. I suppose those fields could be in an entirely separate table, joined by the primary key, but I don't like having to depart from normal form that way. I also considered having a table that keeps track of what analysis has been done, using a timestamp or auto-increment column, but realized that would get messed up if I don't process data in the order it arrived. I could keep track of a range, which would let me find earlier stuff that hadn't been done. Just to make it all a bit more complicated, the initial analysis is done in a temporary table, for huge performance gain, then inserted into its final resting spot. Any suggestions on how others have solved this sort of problem? Nick P.S. sql,query (to satisfy that danged filter) -- [EMAIL PROTECTED] (408) 904-7198 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Update a table with data from another table?
I'm wondering if there's a way to use the results of a SELECT in an UPDATE operation, the way that it's possible to do so with INSERT and REPLACE. This seems like it should be possible, but as far as I can tell, it's not. This comes up for me repeatedly, but the problem at hand is identifying the parent message for a table containing discussion meta-data. I can query to find the earliest occurrence of each subject, but the only way I seem to be able to update the records with this data in a parent message field is to retrieve them programmatically and do updates. Anything that I can do within MySQL is much, much faster, so I'm trying to avoid that. I could create a temporary table with all of the columns, including the updated one, then do a replace back into the original one, but that seems to be a lot of overhead for what should be an update, rather than a replace. Any suggestions? Nick -- [EMAIL PROTECTED] (408) 904-7198 - 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
MySQLdb and DateTime objects
I'm have a bit of trouble figuring out how to use a date field retrieved from MySQL when updating a record in another table. Rather than a date string, MySQLdb returns a DateTime object. At first, imagining that since it came out of a field, the date object would also then match the field in a subsequent operation. But no, it doesn't -- it produces an error. E.g., SELECT DISTINCT(Date) FROM Foo returns a list of tuples whose first member is a DateTime object, not a date string. If the dates are in a list called myDates, and I do something like, UPDATE Foo SET X=%s WHERE Date=%s I get an error, as MySQLdb tries to insert a reference to the DateTime object, rather than a date string. What seemed to be the obvious solution was to use MySQLdb.times.format_TIMESTAMP() or MySQLdb.times.format_DATE()-- but that doesn't work, it returns an error: File C:\Python22\Lib\site-packages\MySQLdb\times.py, lin STAMP return d.strftime(%Y-%m-%d %H:%M:%S) AttributeError: 'tuple' object has no attribute 'strftime' Nor does str(), but that seemed like a long shot, anyway. How the heck do I get this object back into a form that will work in a query? And is it just me, or is it rather inelegant that MySQLdb converts timestamps into a DateTime object on the way out, but doesn't do the reverse? Thanks in advance. Nick -- [EMAIL PROTECTED] (408) 904-7198 - 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: MySQLdb and DateTime objects
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nick Arnett [snip] What seemed to be the obvious solution was to use MySQLdb.times.format_TIMESTAMP() or MySQLdb.times.format_DATE()-- but that doesn't work, it returns an error: File C:\Python22\Lib\site-packages\MySQLdb\times.py, lin STAMP return d.strftime(%Y-%m-%d %H:%M:%S) AttributeError: 'tuple' object has no attribute 'strftime' I was nailed by a typo -- the above actually *does* work, if one is careful to use the function on the appropriate list member, rather than the entire list. But it still seems inelegant... Nick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to speed up mods to large table?
I haven't been able to dig up any specific information about how to get maximum performance when making changes to large tables. I have a table that's close to 4 GB, which I'm altering to come up with the best trade-off between performance and speed. Dropping a column or an index seems exceedingly slow and I'm wondering what parameters matter for this. I'm already using the highest-performance drive I can lay my hands on. What else matters? It certainly looks like it's I/O bound, so I'm wondering if forcing it to build its temp files on a separate disk subsystem would help. It would be a much lower-performance system, though (IDE v. SCSI-160). Thanks in advance... And now, to get past the (not-very-smart) spam filter... sql, query. Nick -- [EMAIL PROTECTED] (408) 904-7198 - 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 speed up mods to large table?
-Original Message- From: Nick Arnett [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 21, 2002 3:39 PM To: [EMAIL PROTECTED] Subject: How to speed up mods to large table? I haven't been able to dig up any specific information about how to get maximum performance when making changes to large tables. I have a table that's close to 4 GB, which I'm altering to come up with the best trade-off between performance and speed. Dropping a column or an index seems exceedingly slow and I'm wondering what parameters matter for this. To answer my own question, at least in part... since I'm using MySQL 4.0.1, I'm trying the same operation again after doing alter table... disable keys. That appears to make it much, much faster. Strangely, though, it's not using the temporary directory that I gave it in my.ini with the tmpdir variable. I seem to recall having that problem before, but I don't know what solved it. Nick - 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: PERL/SQL, again? - Re: Procedures
-Original Message- From: Russell E Glaue [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 7:15 AM ... Does anyone on this list have a suggestion as what might be the best language to embed into MySQL? Be sure to consider memory efficiency, threadding, speed of the language, and compactness (compared to code and library bulkiness). I'd like to hear what other people think? I do a lot of prototyping, so development time is my top priority. Perl would be fine, but I'd prefer Python. As far as creating a language just to embed in MySQL... this may be tempting, but I think it to be far better to not do this. The problems with inventing a language just for MySQL is stalness of advancing the language, and also innovation and optimization. There are already lots of people in existing communities to improve current program languages. Read my lips -- no new languages. Well, I'm partly kidding. But I would be unhappy with a new language if it were the only thing MySQL supported. A new language *in addition* to Perl and/or Python might be useful, but I'd want to see some darn good reason for it to exist. The area where I could see productivity rising immensely would be tools that made user interface development for MySQL database faster. I'm not sure a new language is needed, but tools that would make it very simple to create a basic layout for a table, then modify it easily (remove unneeded fields, make some read-only, turn ENUMs (oh heavens, I almost let the spell checker turn that into Enemas!) into pull-downs, etc.), would be terrific. Finally, I have to give lip service to Java, at least. It is so widely used that if I were wearing my product manager hat, my prejudice would be to integrate it as thoroughly as possible. But I haven't touched Java much in the last few years, so I don't even know what the state of its relationship with MySQL is. Nick - 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: Table statistics
-Original Message- From: Doug Bishop [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 10:13 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Table statistics Try: ? mysql_connect(localhost, user, password); ... Might be helpful to the original poster to mention that this is Perl. He didn't specify a language, so he may not comprehend what you've offered. I don't mean this as a criticism, but it's interesting that we seem to often assume that Perl is THE language for scripting MySQL. Not true, of course. In fact, I'm finding Python easier, except for the blasted printf type formatting, whose syntax was driving me batty yesterday until... well, I could go on, but I won't. Nick - 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: Database Design Question
-Original Message- From: Ben Holness [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] ... My question is this: Would it be more efficient to have each entry in the list stored in this table in the database, or would I be better off having a reference to a file that is stored on the hard disk? If you mean efficient in terms of performance, the answer depends on how big the data is that would go in the files v. a database record, and how often they'll be accessed. If you mean more efficient in terms of disk space, there's probably little difference if the files are large, but a big difference if there are lots of little ones. How much difference for the latter depends on what file system you're using. In any event, you'll probably have to experiment. If you're talking about a lot of relatively small files, putting the data into database fields will probably win. Nick - 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: Database Design Question
-Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] ... Which begs the questions: - in what way are you analyzing behavior? and - in what way are you analyzing this list-community? There's too much to read, is the simple answer to the first question. Over the last few years, I've developed some methods of identifying the most influential people in on-line discussions by discovering social networks. Among them, I'm quite sure, are people who are opinion leaders. By identifying them, it's possible to track ideas and trends. Not long after I first started doing this, I did some brainstorming about it with O'Reilly Associates, which uses such techniques to help decide which open source projects have momentum, so that they can make better decisions about what books to publish. In fact, MySQL v. mSQL was one of the early areas they studied. I also started a company, Opion Inc., to do this kind of analysis for the stock market, movies and other areas. Opion recently sold its technology to Intelliseek (and I have no idea what they're doing with it, but it's not hard to imagine). They seem to have a server on-line at www.opion.com, but it's secured. As for this community, my analysis is limited to reading the messages that either have interesting subjects or senders. By eyeballing the folder that contains them, that is, since my software tries to figure out what's interesting as well. Just to put some fears to rest, which usually come up quickly, I long ago recognized that the list of influential people is something of a spammer's dream. Equally long ago, it was obvious that such lists have to be unavailable to anyone else. It's an interesting, familiar paradox -- how to use the data without revealing enough that it could be abused. I don't want to get any further into the various issues around this research here unless it has something to do directly with MySQL. Nick - 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: Warnings after LOAD DATA statement
-Original Message- From: Maribel Piloto [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 24, 2002 10:24 AM [snip] The data is loading fine and the NULL values are in fact correctly entered as NULLs but I'm getting this warning after the LOAD statement: Records: 3 Deleted: 0 Skipped: 0 Warnings: 3 I don't understand why I'm getting the warnings or what they're about. When you load data from a file, there's no way to know exactly what the warnings are. However, they are the same ones you'd get from a single insert statement, or from too many or two few columns in your insert file (extra columns are ignored). A suggestion here recently was to do a SELECT ... INTO FILE after your insert, selecting the records you just inserted (easier if you just do this into an empty table), then compare the output file with your input file. Having done that myself, I'm still mystified by where the warnings are coming from on my inserts. I need a better file comparison tool... Of course, the alternative way to figure out what's going on is to do individual INSERT statements and look at the actual warnings. See http://www.mysql.com/doc/L/O/LOAD_DATA.html Nick - 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
Extra return in last field in LOAD DATA INFILE?
I have something odd happening with a bulk insert file and haven't been able
to find anything in docs or archives that gives me a clue what's going on.
I'm creating a bulk insert file programmatically with Python. Everything
seems to work fine, except that in the last field of each record, a return
character ('\n') is getting tacked onto the field data. I'm using LOAD DATA
INFILE's defaults -- tab-separated fields and return-separated records. I
cannot figure out where this extra return is coming from. It seems
downright impossible for it to be there, since MySQL is correctly
identifying the fields and records -- there are no warnings from LOAD DATA
INFILE and I can see that all the records are being loaded.
I've tried changing the Python statement between ending the print statement
with '\n', and just letting it add the return as it does by default if
there's no comma at the end of the statement -- no effect.
I have also tried loading the bulk file from the MySQL client -- same thing
happens.
I'm as certain as I can be that Python is just putting one return character
into the insert file, which is why I'm asking the question here, rather than
on a Python list.
I'm having flashbacks to CR/LF problems when moving Macintosh files to DOS!
This is on Windows 2000, using Python 2.2 and MySQL 3.23.46-nt.
That last field also seems to be truncated on the last record inserted. I'm
mystified. Suggestions appreciated.
Nick
-
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: Extra return in last field in LOAD DATA INFILE?
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 21, 2002 4:03 PM
[snip]
It looks like you have the CRLF problem... Python on win platform outputs
CRLF for '\n', because that is normally the wanted line ending on windows
systems... open your file in binary mode to disable this feature:
f = open('datafile.sql','wb')
Indeed I should have checked again -- that did it. Perhaps I should have
asked on the Python list, after all. Still getting used to Python's
idiosyncracies.
Thanks much!
Nick
-
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
