Re: Problem with Selecting Text and Blob fields
Hi, I have had problems reading BLOB fields using MyODBC (I can't see if this is Your way of doing this). I have solved reading/updating BLOB using the samples from http://www.vbmysql.com/ - http://www.vbmysql.com/articles/visual-basic_mysql/blobaccessvb.html Best regards Peter - Original Message - From: "Jacob Joseph" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 21, 2004 6:32 PM Subject: Problem with Selecting Text and Blob fields When a select query is run on a mysql table with text and blob fields, the text fields return the whole text but the blob fields return only the first 4 bytes of the blob data. I use MySQL 4.0.17 with VB6.0 on Windows 2000. What should I do to retrieve complete data from the blob field? Any help is appreciated and thanks in advance. Jacob Joseph. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 2/13/04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Selecting Text and Blob fields
When a select query is run on a mysql table with text and blob fields, the text fields return the whole text but the blob fields return only the first 4 bytes of the blob data. I use MySQL 4.0.17 with VB6.0 on Windows 2000. What should I do to retrieve complete data from the blob field? Any help is appreciated and thanks in advance. Jacob Joseph. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 2/13/04
Re: Help! How to handle Massive index file???
Sure! Here it is: CREATE TABLE `index2` ( `id` varchar(50) NOT NULL default '', `recordid` varchar(20) NOT NULL default '', `transid` varchar(20) NOT NULL default '', `formid` varchar(20) NOT NULL default '', `groupid` varchar(20) NOT NULL default '', `clientid` varchar(20) NOT NULL default '', `userid` varchar(20) NOT NULL default '', `keyid` varchar(50) NOT NULL default '', `active_recordid` varchar(20) default NULL, `replacing` varchar(20) default NULL, `created` datetime default NULL, `lastmodified` datetime default NULL, `issaved` decimal(18,0) NOT NULL default '0', `isclosed` decimal(18,0) NOT NULL default '0', `isdeleted` decimal(18,0) NOT NULL default '0', `indexfield` varchar(50) default NULL, `indexvalue` varchar(200) default NULL, PRIMARY KEY (`id`), KEY `recordid_idxfield_idxvalue` (`recordid`,`indexfield`,`indexvalue`), KEY `indexfield_idxvalue` (`indexfield`,`indexvalue`), KEY `Fixed_fields_and_generic_fields` (`recordid`,`transid`,`formid`,`clientid`,`active_recordid`,`issaved`,`isclo sed`,`isdeleted`,`indexfield`,`indexvalue`), KEY `recordid_idxfield_formid_activeRid_isvars_idxval` (`recordid`,`indexfield`,`formid`,`active_recordid`,`issaved`,`isclosed`,`is deleted`,`indexvalue`), KEY `formid_idxfield_idxvalue` (`formid`,`indexfield`,`indexvalue`) ) TYPE=MyISAM Thanks, Eric "Chris Nolan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Can you send us the CREATE TABLE statement for this troublesome table? > > Regards, > > Chris > > Eric B. wrote: > > >Sorry - forgot to mention it. I've already tried both an OPTIMIZE TABLE and > >ANALYZE TABLE to try to improve performance, but with no result. > > > >Any other ideas? > > > >Thanks, > > > >Eric > > > > > >"Chris Nolan" <[EMAIL PROTECTED]> wrote in message > >news:[EMAIL PROTECTED] > > > > > >>Hmm...if there's lots of thrashing, it might be to do with > >>fragmentation. Have you tried running OPTIMIZE TABLE on the table in > >>question? > >> > >>Does anyone on the list have anything to say about putting the MYD and > >>MYI files on seperate disks or using RAID MyISAM tables?? > >> > >>Regards, > >> > >>Chris > >> > >>Eric B. wrote: > >> > >> > >> > >>>Help! > >>> > >>>Okay - so I've been working around on my indexing of a table. My table > >>> > >>> > >is > > > > > >>>currently over 5M rows (close to 500Mb) and growing at a quick rate. In > >>>order to handle different types of queries, I am forced to create > >>> > >>> > >multiple > > > > > >>>indexes for the table. But by doing so, I end up with an MYI index file > >>> > >>> > >of > > > > > >>>over 2Gig!! > >>> > >>>Now the problem is that my query is still taking way to long to execute > >>> > >>> > >(ie: > > > > > >>>30 secs). If I try an "Explain" on the query, it tells me that it only > >>>needs to examine 30 000 rows (which is not bad considering there are over > >>> > >>> > >5 > > > > > >>>million in the table), however, when I actually execute it, I can see the > >>>disk thrashing an enormous amount. Is there any way to know if the > >>> > >>> > >thrasing > > > > > >>>because it is actually reading through the DB and retrieving the rows or > >>>trying to read through this gigantic index file? > >>> > >>>Is there anything I can do to help optimize this? Loading a 2G index > >>> > >>> > >file > > > > > >>>into RAM doesn't seem realistic since this index file will grow with > >>> > >>> > >time, > > > > > >>>and I can't imagine needing to constantly add more RAM to handle a bigger > >>>and bigger index file. > >>> > >>>Is there any way to determine what the I/O is due to? Is the thrashing > >>>MySQL reading the DB or reading the index file? Or is there anything > >>> > >>> > >else I > > > > > >>>can do to help optimize my queries further? I'm using MyISAM tables, if > >>> > >>> > >it > > > > > >>>makes any difference > >>> > >>>Thanks for any insight! > >>> > >>>Eric > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >> > >>-- > >>MySQL General Mailing List > >>For list archives: http://lists.mysql.com/mysql > >>To unsubscribe: > >> > >> > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > >> > >> > > > > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting variables on update
Hello, I'm trying to run an SQL update statement like: UPDATE ControlTable SET @prevval := NextID, NextID = NextID + 1 I've tried using a range of different syntax, but nothing seems to work. This is something that does work in another SQL server I used to use. Does anybody know if this is something MySQL can do, and if so, what is the correct syntax? Note that I can't use an autoincrement column because the IDs aren't being put into a database. I've searched through MySQL docs and mailing lists but couldn't find any mention of this. Any help would be appreciated. Matt Fagan [EMAIL PROTECTED] Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help! How to handle Massive index file???
Can you send us the CREATE TABLE statement for this troublesome table? Regards, Chris Eric B. wrote: Sorry - forgot to mention it. I've already tried both an OPTIMIZE TABLE and ANALYZE TABLE to try to improve performance, but with no result. Any other ideas? Thanks, Eric "Chris Nolan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hmm...if there's lots of thrashing, it might be to do with fragmentation. Have you tried running OPTIMIZE TABLE on the table in question? Does anyone on the list have anything to say about putting the MYD and MYI files on seperate disks or using RAID MyISAM tables?? Regards, Chris Eric B. wrote: Help! Okay - so I've been working around on my indexing of a table. My table is currently over 5M rows (close to 500Mb) and growing at a quick rate. In order to handle different types of queries, I am forced to create multiple indexes for the table. But by doing so, I end up with an MYI index file of over 2Gig!! Now the problem is that my query is still taking way to long to execute (ie: 30 secs). If I try an "Explain" on the query, it tells me that it only needs to examine 30 000 rows (which is not bad considering there are over 5 million in the table), however, when I actually execute it, I can see the disk thrashing an enormous amount. Is there any way to know if the thrasing because it is actually reading through the DB and retrieving the rows or trying to read through this gigantic index file? Is there anything I can do to help optimize this? Loading a 2G index file into RAM doesn't seem realistic since this index file will grow with time, and I can't imagine needing to constantly add more RAM to handle a bigger and bigger index file. Is there any way to determine what the I/O is due to? Is the thrashing MySQL reading the DB or reading the index file? Or is there anything else I can do to help optimize my queries further? I'm using MyISAM tables, if it makes any difference Thanks for any insight! Eric -- 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: Help! How to handle Massive index file???
Sorry - forgot to mention it. I've already tried both an OPTIMIZE TABLE and ANALYZE TABLE to try to improve performance, but with no result. Any other ideas? Thanks, Eric "Chris Nolan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hmm...if there's lots of thrashing, it might be to do with > fragmentation. Have you tried running OPTIMIZE TABLE on the table in > question? > > Does anyone on the list have anything to say about putting the MYD and > MYI files on seperate disks or using RAID MyISAM tables?? > > Regards, > > Chris > > Eric B. wrote: > > >Help! > > > >Okay - so I've been working around on my indexing of a table. My table is > >currently over 5M rows (close to 500Mb) and growing at a quick rate. In > >order to handle different types of queries, I am forced to create multiple > >indexes for the table. But by doing so, I end up with an MYI index file of > >over 2Gig!! > > > >Now the problem is that my query is still taking way to long to execute (ie: > >30 secs). If I try an "Explain" on the query, it tells me that it only > >needs to examine 30 000 rows (which is not bad considering there are over 5 > >million in the table), however, when I actually execute it, I can see the > >disk thrashing an enormous amount. Is there any way to know if the thrasing > >because it is actually reading through the DB and retrieving the rows or > >trying to read through this gigantic index file? > > > >Is there anything I can do to help optimize this? Loading a 2G index file > >into RAM doesn't seem realistic since this index file will grow with time, > >and I can't imagine needing to constantly add more RAM to handle a bigger > >and bigger index file. > > > >Is there any way to determine what the I/O is due to? Is the thrashing > >MySQL reading the DB or reading the index file? Or is there anything else I > >can do to help optimize my queries further? I'm using MyISAM tables, if it > >makes any difference > > > >Thanks for any insight! > > > >Eric > > > > > > > > > > > > > > > > > > > > -- > 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: Improving seek/access times -- does RAID help?
In the last episode (Feb 20), Matt W said: > Can anyone tell me whether or not some kind of RAID will improve the > seek/access times during lots of random reads from, say, MyISAM data > files? I *do not care* about improved [sequential] transfer rates; I > want the fastest possible random access. > > I'm thinking that RAID won't give an improvement in this case, > because the disks can't know where to read from until MySQL issues > the seek calls. :-( About the only thing I can think of that may help > is if you're using striping, there won't be as much data on each disk > so the head would need shorter seeks. Depends on whether you're talking about a single client doing these random reads or multiple ones. The bottleneck with random I/O is fact that a read means physically moving the disk head. If you have multiple disks striping (or mirroring) the data, you distribute the load across all the heads. It won't speed up a single client, but with more and more disks, it's less and less likely that multiple clients will be needing to read data off the same disk. Mirrors speed up reads more than stripes, but since you have to write to both mirrors, they don't help writes. Raid-5 lets you safely scale past 2 disks. With a single client, get enough RAM to cache the entire table in memory :) Or at least the entire index. That way you only need to do one seek per query. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help! How to handle Massive index file???
Hmm...if there's lots of thrashing, it might be to do with fragmentation. Have you tried running OPTIMIZE TABLE on the table in question? Does anyone on the list have anything to say about putting the MYD and MYI files on seperate disks or using RAID MyISAM tables?? Regards, Chris Eric B. wrote: Help! Okay - so I've been working around on my indexing of a table. My table is currently over 5M rows (close to 500Mb) and growing at a quick rate. In order to handle different types of queries, I am forced to create multiple indexes for the table. But by doing so, I end up with an MYI index file of over 2Gig!! Now the problem is that my query is still taking way to long to execute (ie: 30 secs). If I try an "Explain" on the query, it tells me that it only needs to examine 30 000 rows (which is not bad considering there are over 5 million in the table), however, when I actually execute it, I can see the disk thrashing an enormous amount. Is there any way to know if the thrasing because it is actually reading through the DB and retrieving the rows or trying to read through this gigantic index file? Is there anything I can do to help optimize this? Loading a 2G index file into RAM doesn't seem realistic since this index file will grow with time, and I can't imagine needing to constantly add more RAM to handle a bigger and bigger index file. Is there any way to determine what the I/O is due to? Is the thrashing MySQL reading the DB or reading the index file? Or is there anything else I can do to help optimize my queries further? I'm using MyISAM tables, if it makes any difference Thanks for any insight! Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
looking for a MySQL DBA, San Francisco, building large system from scratch
We have a job opening at MyPoints.com in San Francisco for a MySQL database administrator/database developer. We are building a new system from scratch with distributed and replicated MySQL databases, Apache, Java, Linux. Actual duties will depend on the particular skills and interests of the candidate (could include everything from basic database administration tasks: tuning, replication/backup/restore, monitoring, reporting, etc. to data warehousing to architectural contributions.) Anyone interested (or who knows someone who is interested) may respond to [EMAIL PROTECTED]
Help! How to handle Massive index file???
Help! Okay - so I've been working around on my indexing of a table. My table is currently over 5M rows (close to 500Mb) and growing at a quick rate. In order to handle different types of queries, I am forced to create multiple indexes for the table. But by doing so, I end up with an MYI index file of over 2Gig!! Now the problem is that my query is still taking way to long to execute (ie: 30 secs). If I try an "Explain" on the query, it tells me that it only needs to examine 30 000 rows (which is not bad considering there are over 5 million in the table), however, when I actually execute it, I can see the disk thrashing an enormous amount. Is there any way to know if the thrasing because it is actually reading through the DB and retrieving the rows or trying to read through this gigantic index file? Is there anything I can do to help optimize this? Loading a 2G index file into RAM doesn't seem realistic since this index file will grow with time, and I can't imagine needing to constantly add more RAM to handle a bigger and bigger index file. Is there any way to determine what the I/O is due to? Is the thrashing MySQL reading the DB or reading the index file? Or is there anything else I can do to help optimize my queries further? I'm using MyISAM tables, if it makes any difference Thanks for any insight! Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
Sasha Pachev wrote: Jochem van Dieten wrote: Sasha Pachev wrote: Heikki Tuuri wrote: C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) http://citeseer.nj.nec.com/shah01java.html That's nice, of course. But can you think of a one database server written in Java that comes anywhere close in performance and reliability to their C/C++ counterparts? I suspect that a hard-core tough system software (in Heikki's sense) programmer, the kind of guy it would take to write high-performance and high reliability code, finds Java unappealing. Java's safety mechanisms are helpful when you do not quite know what you are doing. When you do, for every instance of help, you get ten instances of hindernance. The problem is that C took off for, amongst other reasons, being just the right distance from the metal. It's close enough so you can write things that execute quickly yet has useful enough abstractions so that you can represent whatever data structures you need with a reasonable amount of effort. Personally, I think Java's memory management advantages are terribly overrated. Some of the dodgy things you can do in C with malloc() allow you to very efficiently allocate ahead of time without initialising all that pre-allocated space ready for usage as whatever struct it is you're intrested in. Additionally, Java's main strength is arguably the sheer number of libraries it comes with. Another great strength is the community-accessible documentation, but this can also be a problem as every single one of my friends at uni insists that to use any class that is provided with ther JDK, you must extend it. The old recommendation of favouring composition over inheritence seems to be lost on them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
Sasha Pachev wrote: C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. My opinion is based on: Amen! When you look at a DBMS, "complex" barely does justice to the code at hand from what I've read. You basically have to build your own OS (especially in the case of MySQL/InnoDB, considering it runs on UNIXes, Linuxes, BSDs, NetWare and Windows). 1) The object-oriented paradigm recommends that algorithms are structured around 'objects', that is, around data structures. But I think it is easier for humans to understand complex algorithms, like the splitting of a node in a B-tree, if the algorithms are presented on their own terms, and not fragmented around the 'objects' involved. Donald Knuth of the Stanford University remarked that proving the correctness of an 'object-oriented' algorithm is hard, because keeping track of autonomous 'objects' is difficult. Amen! A lot of people at my university would say "But an object is just like a struct! How hard can it be??" I bet you're really going to love writing the method BTreeNode::splitInSomeManner() that produces something that can be used for spliting on the downward traversal and BTreeNode:splitInSomeOtherManner that can be used on the upward traversal. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) Additionally, I find it amusing when an exception chain in Java ends inside the Java VM. Additionally, Java's too nice. When I stuff something up, I want to see something disheartening like "Segmentation Fault" not "NullPointerException". How dare Java take away the spice of life which is the fun of debugging C code! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving seek/access times -- does RAID help?
Hi Ted, Heh. :-) This could be many GBs. There's no problem reading rows that are in RAM (cached by the OS) -- can read over 10,000/second. If there's enough RAM, the OS will take care of it (you could cat table.MYD to /dev/null). No ramdisk necessary. :-) BTW, this is for MySQL's full-text search. It works pretty well (fast) as far as doing the lookups and searching in the index. That's not a concern at all. The problem is that it *has to* read the data file for each matching row (and possibly non-matching rows, depending on the search). :-( Searches need to be reasonably fast on millions of rows, while possibly reading 10s of thousands of data rows. It takes a lot more time when those rows aren't cached. The only thing I've thought of so far is symlinking the data file on a separate drive, but I'm not sure how much that will actually help. Matt - Original Message - From: <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 7:24 PM Subject: RE: Improving seek/access times -- does RAID help? > Run everything off a ramdisk ;-) > > Ted Gifford > > -Original Message- > From: Matt W > Sent: Friday, February 20, 2004 5:21 PM > Subject: Improving seek/access times -- does RAID help? > > Hi all, > > Can anyone tell me whether or not some kind of RAID will improve the > seek/access times during lots of random reads from, say, MyISAM data files? > I *do not care* about improved [sequential] transfer rates; I want the > fastest possible random access. > > I'm thinking that RAID won't give an improvement in this case, because the > disks can't know where to read from until MySQL issues the seek calls. :-( > About the only thing I can think of that may help is if you're using > striping, there won't be as much data on each disk so the head would need > shorter seeks. > > If RAID doesn't help the situation, any other ideas if the sub-6ms access > times of the fastest 15K SCSI drives isn't fast enough? :-) > > > Thanks, > > Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Improving seek/access times -- does RAID help?
Run everything off a ramdisk ;-) Ted Gifford -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 5:21 PM To: [EMAIL PROTECTED] Subject: Improving seek/access times -- does RAID help? Hi all, Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. I'm thinking that RAID won't give an improvement in this case, because the disks can't know where to read from until MySQL issues the seek calls. :-( About the only thing I can think of that may help is if you're using striping, there won't be as much data on each disk so the head would need shorter seeks. If RAID doesn't help the situation, any other ideas if the sub-6ms access times of the fastest 15K SCSI drives isn't fast enough? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Improving seek/access times -- does RAID help?
Hi all, Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. I'm thinking that RAID won't give an improvement in this case, because the disks can't know where to read from until MySQL issues the seek calls. :-( About the only thing I can think of that may help is if you're using striping, there won't be as much data on each disk so the head would need shorter seeks. If RAID doesn't help the situation, any other ideas if the sub-6ms access times of the fastest 15K SCSI drives isn't fast enough? :-) Thanks, Matt
Re: InnoDB Hot Backup + MySQL embedded?
Jochem van Dieten wrote: Sasha Pachev wrote: Heikki Tuuri wrote: C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) http://citeseer.nj.nec.com/shah01java.html That's nice, of course. But can you think of a one database server written in Java that comes anywhere close in performance and reliability to their C/C++ counterparts? I suspect that a hard-core tough system software (in Heikki's sense) programmer, the kind of guy it would take to write high-performance and high reliability code, finds Java unappealing. Java's safety mechanisms are helpful when you do not quite know what you are doing. When you do, for every instance of help, you get ten instances of hindernance. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with with-extra-charsets=none
Jeremy March wrote: > I think --with-extra-charsets=none means that no additional character > sets will be _compiled_ into the server. Complex character sets must be > compiled into MySQL, but simple ones can be loaded dynamically. I think > the character sets you are seeing are ones that can be loaded > dynamically. For instance the big5 character set is probably not listed > because it is one that has to be compiled in. Well, you surely know better Mysql than me, but let me express my point of view. After many hours of compiling, finally I got the following point: The option with-extra-charsets=none does what is should do, but 'show variables' still shows NOT COMPILED IN variables. The correct values of 'show variables' show up after starting the server with option --character-set . I think it's kinda bug. Let me prove: 1) Configuring and compiling with '--with-charset=latin1 --with-extra-charsets=none'. Starting by simple 'mysqld_safe ...'. 'Show variables' still says, that I have a whole bunch of charsets. But starting with 'mysqld_safe --character-set=latin2 ...' and doing 'show variables' *does not* say 'character_set: latin2', but: 'character_set: latin1'! It does like that, because latin2 was not compiled in in fact! 2) Configuring and compiling with '--with-charset=latin2 --with-extra-charsets=none'. Starting by simple 'mysqld_safe ...'. 'Show variables' says, that I have a whole bunch of charsets. Starting with 'mysqld_safe --character-set=latin2 ...' and doing 'show variables' *says* 'character_set: latin2'! It does, because latin2 was compiled in this time. I hope it's clear what I've just written. -- ./ premax ./ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
Sasha Pachev wrote: Heikki Tuuri wrote: C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) http://citeseer.nj.nec.com/shah01java.html Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't get automake, make or make install to work
I am running a debian linux server and I need mysqlplus installed. There isn't a package yet for debian (at least not to my knowledge) to install it. So, I downloaded the mysql++ 1.7.9 source to install it. I am running version 2.95.4 of gcc. Is there something I am doing wrong? Do I need to install newer distributions? Here are the various error messages I get. When I run "automake" I get the errors: automake: Makefile.am: required file `./INSTALL' not found automake: Makefile.am: required file `./NEWS' not found automake: Makefile.am: required file `./COPYING' not found automake: Makefile.am: required file `./AUTHORS' not found automake: Makefile.am: required file `./ChangeLog' not found automake: couldn't open `Configure': No such file or directory When I run "make" I get: /usr/bin/ld: cannot find -lz collect2: ld returned 1 exit status make[2]: *** [libsqlplus.la] Error 1 make[2]: Leaving directory `/var/www/mysql/mysql++-1.7.9/sqlplusint' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/var/www/mysql/mysql++-1.7.9' make: *** [all-recursive-am] Error 2 And when I run "make install" I get: /usr/bin/ld: cannot find -lz collect2: ld returned 1 exit status make[1]: *** [libsqlplus.la] Error 1 make[1]: Leaving directory `/var/www/mysql/mysql++-1.7.9/sqlplusint' make: *** [install-recursive] Error 1 balrog:/home/gandalf/mysql/mysql++-1.7.9# Thanks
Re: mysql low performance on sun/sparc/e4500
[EMAIL PROTECTED] wrote: Hi all, I'm currently experiencing some very strange behaviour at my MySQL server. Our production server is a Sun-E4500 (8x400 MHz 4 GB Ram running Solaris 8 and Solstice Disksuite...) and a single A5000 "Storedge" with 127 GB disk space (12x9.1GB @ 10K RPM) array connected over a single FC-AL adapter. The MySQL server we use is 4.0.17 and as all queries such as select and insert are really fast, all "update" queries are extremely slow. In numbers it is 17 seconds for a single update query on a 500K record table which looks like this : AGID| int(10) unsigned | | PRI | NULL| auto_increment period | datetime | | MUL | -00-00 00":00:00 | key_ | varchar(100) | | MUL | | type_ | tinyint(3)| | MUL | 0 | name| varchar(100) | | | | value | int(100) unsigned | | | 0 | lastrun | datetime | | | -00-00 00":00:00 | acl | varchar(50) | | | | Now my question : Is this problem a hardware problem (are there any sun specialists here) or are there any known issues with solaris/sparc/a5000 and MySQL any special configuration needed.The server is not under heavy load compared to a single 600 MHz CPU Intel/Linux machine which can handle this tasks without any problems. One time I dealt with a support case where bring the OS patch level on a Sun resulted in a 10 fold performance increase. That would be the first thing I would try in this case. If you are not particularly attached to your Sun, you do get a better bang for the buck out of x86 machines, and very possibly a better bang period regardless of the buck. In 3 years of working on the MySQL support team I have never seen a Sparc that was proven to outperform a top of the line dual x86 machine. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How fast replication
Kittiphum Worachat wrote: Hi. How fast or what about time different between master and slave suppose when I insert row into master and then select from slave in replication environment. both machine have all the same specification If your slave is able to keep up with the load, and your network is able to keep up with the trafic, the delay is less than 1 second. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. My opinion is based on: Amen! 1) The object-oriented paradigm recommends that algorithms are structured around 'objects', that is, around data structures. But I think it is easier for humans to understand complex algorithms, like the splitting of a node in a B-tree, if the algorithms are presented on their own terms, and not fragmented around the 'objects' involved. Donald Knuth of the Stanford University remarked that proving the correctness of an 'object-oriented' algorithm is hard, because keeping track of autonomous 'objects' is difficult. Amen! 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log mysql connections
António Fernandes wrote: Hello, I'm am trying to make MySQL Server to log connections (attempts, successes, failures) to a Syslog. I know that it's possible to log all queries but I just want the connections. Has anyone already done this? Is there a patch file that I can use? Wanring - I've never done this myself, I've just seen the option and the code in the source. So take my words with a bit of a grain of salt. Make sure libwrap is installed, and then build MySQL using --with-libwrap option to configure. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: oracledump.pl error
"SELECT TABLE_NAME FROM USER_TABLES". I found the error message on Oracle's web site, but their description of the error doesn't help me much: *Cause: * The buffer length was less than the minimum required or greater than its length at bind time minus two bytes. I don't know what buffer this is referring to, and since I don't know Perl, I've had no success trying to find anything in oracledump.pl that might be the buffer. I do not think the problem is the Perl buffers per say, but rather some misunderstanding between the DBD driver and the server, possibly in the buffer size of the driver. You could possibly solve the issue by slightly rewriting the query so that it will produce the same results, but will be handled by a different part of the driver. One thing to try is to do it without ? placeholders Perl is fairly easy to learn if you know some other programming langauge, and is a good thing to have on your resume. I would predict that you would be fluent enough in it to make the change I am suggesting in no more than two hours. Start with "Hello, world", play with string concatenation a bit, and then go straight to the DBI/DBD examples. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trouble with replication
Liying Huang wrote: I have been struggling with one way replication for weeks, still can't make it work. I used windows, MySql 4.0.17 version on both. I have set bin-log option in my.cfg in master computer, in slave computer, set master-host, master-user=repl,master-password, replicate-do-db=test and replicate-ignore-db=mysql, skip-slave-start in my.cfg. I have zipped the database from master, copy the zip to slave and unzip it, then I start master server, after I set all privileges for repl in master server, I start slave. When I do some update in master computer in database, the query ( update test.experiement set .., database is specified in the query) showed in master-bin.001 and it is shown in slave-relay-bin.001 too (slave IO thread is working), but test database in slave doesn't update. What could be wrong? I have donw show slave status, SQL thread seems to be running too, but why it doesn't execute the query from liyingtop-realy-bin.001? I wonder if anyone has experienced the similar situation and know what's wrong with what I did. Change replicate-do-db=test to replicate-wild-do-table=test.% or get rid of it altogether. Alternatively, try use test; update experiment ... instead of update test.experiment ... -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: White Space
Thanks for the posts folks. Right after I posted I re-wrote my parser to use ~ for commas and ^ for quotes. It seems to be working. I didn't want to say I was doing that because it seemed kinda "hack" >:). I'm not in favor of running TRIM() functions due to the overhead with so many records. We'll see if this modification works for all 120million. So far it worked for a 6mill sample. Thanks. Chris. -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 3:04 PM To: 'Dan Nelson'; 'Chris Fossenier' Cc: [EMAIL PROTECTED] Subject: RE: White Space Chris, Another option is to use | instead of commas. Unless you have pipes in your file. :) Donny > -Original Message- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > Sent: Friday, February 20, 2004 2:55 PM > To: Chris Fossenier > Cc: [EMAIL PROTECTED] > Subject: Re: White Space > > In the last episode (Feb 20), Chris Fossenier said: > > What is the best way to deal with white space in a mysql database? > > > > What I'm talking about is trailing spaces in larger fields. We > > receive data in fixed width format, so when you import it, you get a > > bunch of trailing white spaces that take up space. Multiply these > > white spaces by 120million records and it adds up. > > If you use VARCHAR fields, mysql will automatically strip trailing > whitespace. If you use CHAR fields, stripping space won't help you > since it's a fixed-width field :) > > If you need leading and trailing space stripped, you can postprocess > the field with the TRIM() function. > > -- > Dan Nelson > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transferring comma-delimited list imto mysql table
Hi Eve, That error is because the LOCAL part of LOAD DATA is disabled. See here: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html Since your file is probably on the same system as the MySQL server, it should work if you remove the LOCAL word. Hope that helps. Matt - Original Message - From: "Eve Atley" Sent: Friday, February 20, 2004 12:58 PM Subject: Transferring comma-delimited list imto mysql table > > I have a large email database that has been translated into a CSV, simply > like this: > [EMAIL PROTECTED];[EMAIL PROTECTED]; (etc.) > > I need to get this into SOME format. I have tried Access, which said it was > too large - so I am trying my mysql. I created a table with fields ID, and > email. I just want to put each email into the email field. I get this error: > > LOAD DATA LOCAL INFILE 'C:\\PHP\\uploadtemp\\php7.tmp' REPLACE INTO TABLE > `email` > FIELDS TERMINATED BY ';' > > MySQL said: > > > #1148 - The used command is not allowed with this MySQL version > > Can anyone help me? Excel choked, Access choked. My intent is to pull all > these from MySQL and into Outlook. If MySQL isn't the app to use, perhaps > another solution can be recommended. > > Thanks so much, > Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed searching with OR ?
Chris, The good news is that MySQL 5.0 can finally use multiple indexes per table. I just noticed this page in the manual a few days ago: http://www.mysql.com/doc/en/OR_optimizations.html :-) Matt - Original Message - From: "Chris Nolan" Sent: Monday, February 16, 2004 7:13 AM Subject: Re: Indexed searching with OR ? > Hi! > > MySQL's optimizer has a slight problem. OR queries cause it to get very > confused. > > Try the following to get the best performance: > > Rewrite SELECT FROM table WHERE (condition1) OR (condition2); > > As: > > (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE > condition2); > > Hope this helps! > > Regards, > > Chris > > On Tue, 2004-02-17 at 00:05, Andreas Pardeike wrote: > > Hi List, > > > > Can someone explain the results below? It seems that MySQL has a hard > > time choosing keys for 'or' searches. The example here is very simple > > but reflects the more complex cases where lots of rows or joins are > > used perfectly: > > > > > > What's the point of indices if I cannot combine two indexed fields with > > OR ? > > > > Any help appreciated, > > Andreas Pardeike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: White Space
Just use something else beside commas to delimit your file. I have a file that I need to regularly import that has commas, tabs and returns in the data. I delimit the fields with ^^ and the records with ~~. You can tell MySQL what the delimiters are when you import the data. On Feb 20, 2004, at 3:45 PM, Chris Fossenier wrote: What is the best way to deal with white space in a mysql database? What I'm talking about is trailing spaces in larger fields. We receive data in fixed width format, so when you import it, you get a bunch of trailing white spaces that take up space. Multiply these white spaces by 120million records and it adds up. I wrote a C program to parse out the file and eliminate white space and put the data into a comma-delimited format. Now I have a new problem. I think that there are some commas in the data and that is resulting in an innacurate load (the commas in the data through off the LOAD INFILE command I'm guessing). So..if I stick with the fixed field, my load gets all records but I get a bunch of extra data in the form of spaces. if I parse the file and put it into comma-delimited, I miss some data because of commas in the data. Any tips are appreciated. Thanks. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf in MySQL-Server-4.0.17
Hi Ronan, Did you perhaps set MAXDSIZ is your kernel configuration above "(2047UL*1024*1024)" ? Ken - Original Message - From: "Ronan Lucio" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 19, 2004 3:06 PM Subject: my.cnf in MySQL-Server-4.0.17 > Hi All, > > I´m trying to do some customization in a MySQL-Server-4.0.17 > to gain a better performance. > > We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD > on a FreeBSD-5.2.1 box dedicated for MySQL. > > My trouble is when I create the my.cnf file and start MySQL. > MySQL don´t stat. > > the /usr/local/mysql/aguia.err file shows: > - > 040219 16:34:46 mysqld started > mysqld in malloc(): error: allocation failed > mysqld got signal 6; > This could be because you hit a bug. It is also possible that this binary > or one of the libraries it was linked against is corrupt, improperly built, > or misconfigured. This error can also be caused by malfunctioning hardware. > We will try our best to scrape up some info that will hopefully help > diagnose > the problem, but since we have already crashed, something is definitely > wrong > and this may fail. > > key_buffer_size=134217728 > read_buffer_size=1044480 > max_used_connections=0 > max_connections=100 > threads_connected=0 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = > 335471 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > mysqld in free(): error: recursive call > Fatal signal 6 while backtracing > 040219 16:34:46 mysqld ended > - > > My my.cnf file is: > - > [client] > port= 3306 > socket = /tmp/mysql.sock > > [mysqld] > port= 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer = 128M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 1M > read_buffer_size = 1M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > thread_concurrency = 8 > > log-bin > > server-id = 1 > > innodb_data_home_dir = /usr/local/mysql/ > innodb_data_file_path = ibdata1:1000M:autoextend > innodb_log_group_home_dir = /usr/local/mysql/ > innodb_log_arch_dir = /usr/local/mysql/ > innodb_buffer_pool_size = 512M > innodb_additional_mem_pool_size = 20M > innodb_log_file_size = 128M > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 50 > > [mysqldump] > quick > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > > [isamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [myisamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [mysqlhotcopy] > interactive-timeout > - > > Without the /etc/my.cnf file the MySQL works fine, except in > some moments that it get slow. > > Does anyone knows what could be wrong? > Any help would be Appreciated. > > Thank´s > Ronan > > > > > -- > 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: White Space
On 20 Feb 2004 at 14:55, Dan Nelson wrote: > If you need leading and trailing space stripped, you can postprocess > the field with the TRIM() function. But note that, unlike similar functions in some languages, the MySQL TRIM() function removes spaces (ASCII 32 characters), not whitespace (which generally includes tabs, carriage returns, and line feeds -- and sometimes vertical tabs, form feeds, or nulls -- along with spaces). -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: White Space
Chris, Another option is to use | instead of commas. Unless you have pipes in your file. :) Donny > -Original Message- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > Sent: Friday, February 20, 2004 2:55 PM > To: Chris Fossenier > Cc: [EMAIL PROTECTED] > Subject: Re: White Space > > In the last episode (Feb 20), Chris Fossenier said: > > What is the best way to deal with white space in a mysql database? > > > > What I'm talking about is trailing spaces in larger fields. We > > receive data in fixed width format, so when you import it, you get a > > bunch of trailing white spaces that take up space. Multiply these > > white spaces by 120million records and it adds up. > > If you use VARCHAR fields, mysql will automatically strip trailing > whitespace. If you use CHAR fields, stripping space won't help you > since it's a fixed-width field :) > > If you need leading and trailing space stripped, you can postprocess > the field with the TRIM() function. > > -- > Dan Nelson > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: White Space
In the last episode (Feb 20), Chris Fossenier said: > What is the best way to deal with white space in a mysql database? > > What I'm talking about is trailing spaces in larger fields. We > receive data in fixed width format, so when you import it, you get a > bunch of trailing white spaces that take up space. Multiply these > white spaces by 120million records and it adds up. If you use VARCHAR fields, mysql will automatically strip trailing whitespace. If you use CHAR fields, stripping space won't help you since it's a fixed-width field :) If you need leading and trailing space stripped, you can postprocess the field with the TRIM() function. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
White Space
What is the best way to deal with white space in a mysql database? What I'm talking about is trailing spaces in larger fields. We receive data in fixed width format, so when you import it, you get a bunch of trailing white spaces that take up space. Multiply these white spaces by 120million records and it adds up. I wrote a C program to parse out the file and eliminate white space and put the data into a comma-delimited format. Now I have a new problem. I think that there are some commas in the data and that is resulting in an innacurate load (the commas in the data through off the LOAD INFILE command I'm guessing). So..if I stick with the fixed field, my load gets all records but I get a bunch of extra data in the form of spaces. if I parse the file and put it into comma-delimited, I miss some data because of commas in the data. Any tips are appreciated. Thanks.
Re: Speeding up index creation under InnoDB
Sasha, Thanks for the reply. It's actually during index creation (while creating a database). I am creating the tables, loading the data, then applying the indexes and foreign keys. There are five or six indexes on some tables, the first taking 5 minutes, the second taking 5:45, etc. In this case, is the first index still being rebuilt when the second index is added? My guess was that there was re-organization being done in the tablespace for each existing index, and thus each additional index took even longer. The tablespace is on the disk, and therefore a faster disk would help. David. - Original Message - From: "Sasha Pachev" <[EMAIL PROTECTED]> To: "David Griffiths" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 8:41 AM Subject: Re: Speeding up index creation under InnoDB > David Griffiths wrote: > > I was wondering what the bottleneck was. I'm adding a dozen indexes to > > the same large-ish InnoDB table. Each successive index takes a bit > > longer (45 seconds or so on a dual P3-933 with 2 gig of RAM). > > Every time you add a new index or do any non-trivial modification to the schema, > the old ones are being re-created. Because of that, you should , if possible, do > all schema modifications at once ( eg alter table add key(col1),add key(col2), > add n int not null instead of alter table add key(col1); alter table add > key(col2); alter table add n int not null) > > > > Is it disk additional tables-space management that is taking the extra > > time? Would faster disks help? > > As a rule of thumb, when you feel tempted to add a faster disk to a MySQL > server, you should resist the temptation. In three years of working on the MySQL > support team I do not recall ever recommending to buy a faster disk - we've > always been able to find a more elegant solution. > > -- > Sasha Pachev > Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does thread_concurrency do anything on a non-Solaris OS?
In the last episode (Feb 20), Bill Marrs said: > Looking around in the docs, I found this regarding thread_concurrency: > > thread_concurrency > On Solaris, mysqld will call thr_setconcurrency() with this value. > thr_setconcurrency() permits the application to give the threads > system a hint for the desired number of threads that should be run at > the same time. > > I had noticed that it is set to 10 on my system, though I only have 2 > CPUs (I've also seen that the recommended value is #CPUS*2. So, I > was considering dropping it to 4. But, after reading the above, I > wonder if it makes difference on a Linux system (or any other > non-Solaris system). thr_setconcurrency is an obsolete Solaris-only function. pthread_setconcurrency is the replacement. This means that mysql can only set concurrency on Solaris, and according to my reading of the manpage on Solaris 9, the value passed to the thr_ version is ignored. http://docs.sun.com/db/doc/816-0216/6m6ngupp2?a=view ( thr_ ) http://docs.sun.com/db/doc/816-0216/6m6nguplf?a=view ( pthread_ ) According to the glibc 2.3.2 documentation: pthread_setconcurrency is unused in LinuxThreads due to the lack of a mapping of user threads to kernel threads. It exists for source compatibility. It is supported to some degree on Tru64, AIX, and FreeBSD 5.x (with libkse). I don't know how much difference it makes to mysql. http://www.freebsd.org/cgi/man.cgi?query=pthread_setconcurrency&manpath=FreeBSD+5.2-current&format=html http://h30097.www3.hp.com/docs/base_doc/DOCUMENTATION/V51B_HTML/MAN/MAN3/2224.HTM http://www16.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/basetrf1/pthread_getconcurrency.htm -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date_Format
If you need the date like, '2004/01/01', then shouldn't it be '%Y/%m/%d'?
You still ahve the dashes (the '-' character) in your date-format, and you
need a forward-slash (the '/' character) instead.
Here's what I ran on MySQL:
mysql> select date_format(NOW(), '%Y/%m/%d');
++
| date_format(NOW(), '%Y/%m/%d') |
++
| 2004/02/20 |
++
1 row in set (0.00 sec)
David
- Original Message -
From: "Jacque Scott" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, February 20, 2004 10:31 AM
Subject: Date_Format
> I have the following query where the user will put in a date. They will
> put it in like '01/01/04'. I use this date in my WHERE clause but MySQL
> needs the date like '2004/01/01'. I tried using Date_Format('04/01/01',
> '%Y-%m-%d') to change the way it is formatted but I don't get the
> correct data back. Can someone help with this?
>
> SELECT DISTINCTROW Products.NSIPartNumber, Sum(tblInvTransaction.Qty)
> AS SumOfQty
> FROM tblInvTransaction INNER JOIN Products ON
> tblInvTransaction.ProductID = Products.ProductId
> WHERE tblInvTransaction.Date <= Date_Format('01/01/04', '%Y-%m-%d')
> GROUP BY Products.NSIPartNumber
> HAVING Sum(tblInvTransaction.Qty)>0;
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't create a new thread (errno 11)
Check the max_connections variable Mysql> show variables liks '%connection%'; -James -Original Message- From: Christof Egner [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 9:03 AM To: [EMAIL PROTECTED] Subject: Can't create a new thread (errno 11) Hello, I'm having problems with a 4.0.18 MySQL Server. Since this afternoon I get the error "Can't create a new thread (errno 11)" when MySQL has forked about 200-300 threads. What does it mean? I'm not running out of RAM or disk space. Best Regards, Christof -- 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]
Does thread_concurrency do anything on a non-Solaris OS?
Looking around in the docs, I found this regarding thread_concurrency: thread_concurrency On Solaris, mysqld will call thr_setconcurrency() with this value. thr_setconcurrency() permits the application to give the threads system a hint for the desired number of threads that should be run at the same time. I had noticed that it is set to 10 on my system, though I only have 2 CPUs (I've also seen that the recommended value is #CPUS*2. So, I was considering dropping it to 4. But, after reading the above, I wonder if it makes difference on a Linux system (or any other non-Solaris system). -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date_Format
I have the following query where the user will put in a date. They will
put it in like '01/01/04'. I use this date in my WHERE clause but MySQL
needs the date like '2004/01/01'. I tried using Date_Format('04/01/01',
'%Y-%m-%d') to change the way it is formatted but I don't get the
correct data back. Can someone help with this?
SELECT DISTINCTROW Products.NSIPartNumber, Sum(tblInvTransaction.Qty)
AS SumOfQty
FROM tblInvTransaction INNER JOIN Products ON
tblInvTransaction.ProductID = Products.ProductId
WHERE tblInvTransaction.Date <= Date_Format('01/01/04', '%Y-%m-%d')
GROUP BY Products.NSIPartNumber
HAVING Sum(tblInvTransaction.Qty)>0;
Upgrade from 3.23.x to 4.0.18
Quick question. I just did an upgrade from source from 3.23.x to 4.0.18. A few things came up. 1. chown -R mysql var causes my RedHat 7.3 to complain. chown: getting attributes of `var': No such file or directory 2. Stopped mysql by typing shell> service mysql stop 3. Tried to start using "/usr/local/mysql/bin/mysqld_safe --user=mysql &" [EMAIL PROTECTED] mysql]# touch: creating `/usr/local/mysql/var/narsil.err': No such file or directory chown: getting attributes of `/usr/local/mysql/var/narsil.err': No such file or directory Starting mysqld daemon with databases from /usr/local/mysql/var /usr/local/mysql/bin/mysqld_safe: /usr/local/mysql/var/narsil.err: No such file or directory /usr/local/mysql/bin/mysqld_safe: /usr/local/mysql/var/narsil.err: No such file or directory tee: /usr/local/mysql/var/narsil.err: No such file or directory 040220 13:04:57 mysqld ended tee: /usr/local/mysql/var/narsil.err: No such file or directory Typing "ps -aux | grep my" displays: mysql32238 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32240 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32241 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32242 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32243 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32244 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32245 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32246 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32247 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ mysql32248 0.0 2.3 54068 11836 ? S13:04 0:00 /usr/local/mysql/ So it would appear that the mysql daemon is running but now I can't access the databases with the mysql client e.g., mysql -u root -p. Help would be most graciously appreciated. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird Sorting Question. REPOST (Modified by carreraSC)
carreraSC wrote:
No one has any ideas?
Hi,
I've got a sorting question that I can't figure out. The page in
question is here:
http://toolstudios.com/intera/careers/index.php
I'd like it to show up like this:
Accounting
job 1
job 2
Engineer
job 1
job 2
IT
job 1
job 2
Basically, I'd like the categories of employment to be alphabetical.
Here's the SQL stuff now:
$connection = @mysql_connect("$db_host","$db_user","$db_pass") or
die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");
$sql ="SELECT
id,status,title,summary,description,post_date,hidden,job_id,contact_emai l
FROM careers
WHERE hidden = '2'
ORDER BY job_id,title ";
Here you retrieve all jobs ordered by job_id, which, I assume, is the
key to the category table. So, your categories are in id order, rather
than alphabetical by name (category.category).
$result = @mysql_query($sql, $connection) or die("Error #".
mysql_errno() . ": " . mysql_error());
while ($row = mysql_fetch_array($result)) {
$id=$row['id'];
$status=$row['status'];
$title=$row['title'];
$summary=$row['summary'];
$description=$row['description'];
$post_date=$row['post_date'];
$hidden=$row['hidden'];
$job_id=$row['job_id'];
$contact_email=$row['contact_email'];
// lookup sub_cat name and display, only if it's new
if ($job_id != $old_job_cat){
// New Connection and selection from Sub Category db
$connection = @mysql_connect("$db_host","$db_user","$db_pass")
> or die("Couldn't Connect.");
There's no need to reconnect. In fact, this adds a lot of overhead.
You are reconnecting for every job, when your original connection is
still good.
$db = @mysql_select_db($db_name, $connection) or
die("Couldn't select database.");
Same here. You aren't changing dbs, so don't reselect the same db.
$cat_sql ="SELECT job_id, category FROM career_categories
WHERE job_id = '$job_id' ORDER BY category ASC";
You only need to select category here, as you already have $job_id.
$cat_result = @mysql_query($cat_sql, $connection) or
die("Error #". mysql_errno() . ": " . mysql_error());
while ($cat_row = mysql_fetch_array($cat_result)) {
$job_id=$cat_row['job_id'];
Redundant. This sets $job_id = $job_id.
$category=$cat_row['category'];
// We then print out the RESULT row
$job_heading = " \n $category\n";
$display_block .= "$job_heading \n";
}
}
// This ends the Sub Cat Row, and continues the Job Listings
Row
$old_job_cat = $job_id;
$display_block .= " $title - $summary\n";
}
$num = @mysql_num_rows($result);
if ($num != 0) {
include("list_results.php");
} else {
include("list_no_rslt.php");
}
?>
The problem is, when the script looks for "job_ID" and attempts to see
if it matches anything, it doesn't return an array of results...just 1.
So, can't really alphabetize one result. Any workarounds?
Exactly, you are querying the db for the job category once for each job.
You would be better off using a join to get the category for each job
in the original query, ordered by the category so you get the sorting
you want, then print the header row each time the category changes. See
the sample code below.
Michael
$connection = @mysql_connect("$db_host","$db_user","$db_pass")
or die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection)
or die("Couldn't select database.");
$sql ="SELECT c.id, c.status, c.title, c.summary, c.description,
c.post_date, c.hidden, c.job_id, c.contact_email,
cat.category
FROM careers c, career_categories cat
WHERE c.job_id=cat.job_id AND hidden = '2'
ORDER BY cat.category, c.title";
$result = @mysql_query($sql, $connection)
or die("Error #". mysql_errno() . ": " . mysql_error());
$old_cat = ''; //make sure we get a header for the first job
while ($row = mysql_fetch_array($result))
{
$id=$row['id'];
$status=$row['status'];
$title=$row['title'];
$summary=$row['summary'];
$description=$row['description'];
$post_date=$row['post_date'];
$hidden=$row['hidden'];
$job_id=$row['job_id'];
$contact_email=$row['contact_email'];
$category=$row['category'];
// display category name if it's new
if ($category != $old_cat)
{
$job_heading = " \n $category\n";
$display_block .= "$job_heading \n";
}
// display the job listing row
$display_block .= "$title - $summary\n";
$old_cat = $category; //update $old_cat
}
$num = @mysql_num_rows($result);
if ($num != 0)
{
include("list_results.php");
}
else
{
include("list_no_rslt.php");
}
?>
--
MySQL Gene
Re: SUM() the results of a COUNT()
>But how would i get something like this? >+++ >| A | B | >+++ >| 5 | 10 | >+++ >Where A = the number of unique UID's (the number of rows from table 2) >and B = the SUM of C (derived from >"SELECT count(*) as C from table GROUP BY uid") SELECT SUM( C ) FROM ( SELECT count(*) as C from table GROUP BY uid ) AS counts; PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM() the results of a COUNT()
That is SPOT on, takes 2seconds to process but thats still better than 5 :) -- Dave - Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "David Scott" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 5:30 PM Subject: Re: SUM() the results of a COUNT() > > > David Scott wrote: > > Hiya peeps... > > > > If i have a table like this: > > +++ > > | id | uid| > > +++ > > | 1 | 26 | > > | 2 | 26 | > > | 3 | 267| > > | 4 | 267| > > | 5 | 269| > > | 6 | 269| > > | 7 | 271| > > | 8 | 271| > > | 9 | 271| > > | 10 | 424| > > +++ > > > > I could do this to get the count of uid's: > > SELECT count(*) as C from table GROUP BY uid > > +++ > > | C | uid| > > +++ > > | 2 | 26 | > > | 2 | 267| > > | 2 | 269| > > | 3 | 271| > > | 1 | 424| > > +++ > > > > But how would i get something like this? > > +++ > > | A | B | > > +++ > > | 5 | 10 | > > +++ > > > > Where A = the number of unique UID's (the number of rows from table > > 2) and B = the SUM of C (derived from "SELECT count(*) as C from table > > GROUP BY uid") > > > > I have been googling for over 1 hour now, looked through the MySQL > > manual, searched the forum archives, please someone put me out of my > > misery... > > > > PS: > > I could use code and loop through the results adding up as I go along, but the real table has over 19,000 records and looping through that many records in ASP takes around 5 seconds. > > > > Cheers > > Dave > > Perhaps I'm misunderstanding what you need, but wouldn't > >SELECT COUNT(DISTINCT(uid)) AS A, COUNT(uid) AS B FROM table; > > give the result you want? > > Michael > > > -- > 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: SUM() the results of a COUNT()
David Scott wrote: Hiya peeps... If i have a table like this: +++ | id | uid| +++ | 1 | 26 | | 2 | 26 | | 3 | 267| | 4 | 267| | 5 | 269| | 6 | 269| | 7 | 271| | 8 | 271| | 9 | 271| | 10 | 424| +++ I could do this to get the count of uid's: SELECT count(*) as C from table GROUP BY uid +++ | C | uid| +++ | 2 | 26 | | 2 | 267| | 2 | 269| | 3 | 271| | 1 | 424| +++ But how would i get something like this? +++ | A | B | +++ | 5 | 10 | +++ Where A = the number of unique UID's (the number of rows from table 2) and B = the SUM of C (derived from "SELECT count(*) as C from table GROUP BY uid") I have been googling for over 1 hour now, looked through the MySQL manual, searched the forum archives, please someone put me out of my misery... PS: I could use code and loop through the results adding up as I go along, but the real table has over 19,000 records and looping through that many records in ASP takes around 5 seconds. Cheers Dave Perhaps I'm misunderstanding what you need, but wouldn't SELECT COUNT(DISTINCT(uid)) AS A, COUNT(uid) AS B FROM table; give the result you want? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird Sorting Question. REPOST (Modified by carreraSC)
No one has any ideas?
Hi,
I've got a sorting question that I can't figure out. The page in
question is here:
http://toolstudios.com/intera/careers/index.php
I'd like it to show up like this:
Accounting
job 1
job 2
Engineer
job 1
job 2
IT
job 1
job 2
Basically, I'd like the categories of employment to be alphabetical.
Here's the SQL stuff now:
$connection = @mysql_connect("$db_host","$db_user","$db_pass") or
die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");
$sql ="SELECT
id,status,title,summary,description,post_date,hidden,job_id,contact_emai
l
FROM careers
WHERE hidden = '2'
ORDER BY job_id,title ";
$result = @mysql_query($sql, $connection) or die("Error #".
mysql_errno() . ": " . mysql_error());
while ($row = mysql_fetch_array($result)) {
$id=$row['id'];
$status=$row['status'];
$title=$row['title'];
$summary=$row['summary'];
$description=$row['description'];
$post_date=$row['post_date'];
$hidden=$row['hidden'];
$job_id=$row['job_id'];
$contact_email=$row['contact_email'];
// lookup sub_cat name and display, only if it's new
if ($job_id != $old_job_cat){
// New Connection and selection from Sub Category db
$connection = @mysql_connect("$db_host","$db_user","$db_pass") or
die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't
select database.");
$cat_sql ="SELECT job_id, category FROM career_categories WHERE
job_id = '$job_id' ORDER BY category ASC";
$cat_result = @mysql_query($cat_sql, $connection) or die("Error #".
mysql_errno() . ": " . mysql_error());
while ($cat_row = mysql_fetch_array($cat_result)) {
$job_id=$cat_row['job_id'];
$category=$cat_row['category'];
// We then print out the RESULT row
$job_heading = " \n $category\n";
$display_block .= "$job_heading \n";
}
}
// This ends the Sub Cat Row, and continues the Job Listings
Row
$old_job_cat = $job_id;
$display_block .= " $title - $summary\n";
}
$num = @mysql_num_rows($result);
if ($num != 0) {
include("list_results.php");
} else {
include("list_no_rslt.php");
}
?>
The problem is, when the script looks for "job_ID" and attempts to see
if it matches anything, it doesn't return an array of results...just 1.
So, can't really alphabetize one result. Any workarounds?
Thanks,
mto
--
Michael O'Neal
Head Honcho
http://www.WhirledMedia.com
[EMAIL PROTECTED]
Boulder, Colorado
720.436.2922
IM: AOL-emptyo1
MSN: [EMAIL PROTECTED]
Yahoo: bambisapartment
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SUM() the results of a COUNT()
Hiya peeps... If i have a table like this: +++ | id | uid| +++ | 1 | 26 | | 2 | 26 | | 3 | 267| | 4 | 267| | 5 | 269| | 6 | 269| | 7 | 271| | 8 | 271| | 9 | 271| | 10 | 424| +++ I could do this to get the count of uid's: SELECT count(*) as C from table GROUP BY uid +++ | C | uid| +++ | 2 | 26 | | 2 | 267| | 2 | 269| | 3 | 271| | 1 | 424| +++ But how would i get something like this? +++ | A | B | +++ | 5 | 10 | +++ Where A = the number of unique UID's (the number of rows from table 2) and B = the SUM of C (derived from "SELECT count(*) as C from table GROUP BY uid") I have been googling for over 1 hour now, looked through the MySQL manual, searched the forum archives, please someone put me out of my misery... PS: I could use code and loop through the results adding up as I go along, but the real table has over 19,000 records and looping through that many records in ASP takes around 5 seconds. Cheers Dave
Can't create a new thread (errno 11)
Hello, I'm having problems with a 4.0.18 MySQL Server. Since this afternoon I get the error "Can't create a new thread (errno 11)" when MySQL has forked about 200-300 threads. What does it mean? I'm not running out of RAM or disk space. Best Regards, Christof -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3.23 JOIN describe to another query
Egor Egorov wrote: Colleen Dick <[EMAIL PROTECTED]> wrote: Is it possible to JOIN the results of a DESCRIBE TABLE to some other query? The semantics of what I would like to do is the following: SELECT d_fldcomment from desc LEFT JOIN DESCRIBE footable ON desc.d_table = 'footable' AND desc.d_field = Field; so the resultset would look like a regular DESCRIBE except it would also include my tacked on comment. I KNOW the need for this will be obsoleted in mySQL 5 but people will be stuck on prev versions for the next 2 years. Any clever way to do this or fake it so everything is in one resultset? I already did it in the obvious boneheaded way and as always when that happens one things "THERE HAS TO BE A BETTER WAY" still on 3.23 You can't mix JOIN and DESCRIBE TABLE. Retrieve data with DESCRIBE TABLE statement and then use programming language to put result of the DESCRIBE to the test of SELECT query. Yes, that is what I had to do. It is klunky. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Replication scheme - DMZ - LAN
Fabbro Alberto wrote: Network configuration: - Firewall connected to Internet, with two ports: LAN (protected) and DMZ - Web server on the DMZ segment - Internal Servers on the LAN segment. We would like to introduce a web database application on the Web server, using a Slave replication of a Master database running on the LAN Server. We have two kinds of problems: - For running replication we need to open ports on the LAN segment of the firewall to let the Slave read binary logs. - If we want web users to change information we need to operate on the Master Database (and open other ports). Is there another kind of replication schema that keep security of the LAN segment ? Alberto: Perhaps a better question to ask is - is there a different kind of topology that will allow you replicate securely and meet your other goals? E.g - keep both the master and the slave on the secure LAN, and open the MySQL port on the master to only to your web server in the DMZ. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bigint support in MySql 4
sorry, I don't know that one. Never have used any version of mysql lower than 4.04. a suggestion might be to call your 4.0 column an unsigned bigint, or if possible an unsigned int if the numbers can fit in 32 bits. unsigned means its always positive. jeff ps. you may want top "reply all" so the rest of the group can comment. you sent it to me directly. [EMAIL PROTECTED] wrote: Thanks for your quick reply. I had another question. I have some tables with columns of tyoe bigint running on MySql 3.23. When i copied these tables on to MySql 4.0 the values of these columns are displayed negative. When I copy them back the values are correctly displayed. Could you tell me as to how I can handle this situation ? yes it does. we use a bigint(20) for example. [EMAIL PROTECTED] wrote: Hi, I would like to know whether MySql 4.0 supports bigint. If not what is the corresponding data type that I ccould use if I am imoprting tables from MySql 3.2 to MySql 4.0 Thanks, Sharath -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speeding up index creation under InnoDB
David Griffiths wrote: I was wondering what the bottleneck was. I'm adding a dozen indexes to the same large-ish InnoDB table. Each successive index takes a bit longer (45 seconds or so on a dual P3-933 with 2 gig of RAM). Every time you add a new index or do any non-trivial modification to the schema, the old ones are being re-created. Because of that, you should , if possible, do all schema modifications at once ( eg alter table add key(col1),add key(col2), add n int not null instead of alter table add key(col1); alter table add key(col2); alter table add n int not null) Is it disk additional tables-space management that is taking the extra time? Would faster disks help? As a rule of thumb, when you feel tempted to add a faster disk to a MySQL server, you should resist the temptation. In three years of working on the MySQL support team I do not recall ever recommending to buy a faster disk - we've always been able to find a more elegant solution. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [4.1.1] 1062 errors on non-unique index during data load
heh, sometimes the obvious is right in front of you... I still can't get the full dataset to load with indexes on the table, but I _can_ apply indexes after the load. the indexes apply cleanly, so it doesn't seem to have been a data issue. in any case, I can continue prototyping. it remains to be seen whether this will crop back up during incremental update of the already filled (and indexed) table, but I've got a good bit of coding to do before I'll have an answer to that question... - mark mark warren bracher wrote: In the last few days I've been doing some prototyping on mysql 4.1.1 (mainly because I want fulltext indexes against utf-8 data). I have a table, artists_search_A, in which I want to load ~100K records. My load process will routinely load 14783 records successfully. After 14783 inserts, any insert attempt results in a 1062 error, duplicate entry; specifically, Duplicate entry 'en-us' for key 2 error 1062 recorded [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: operator OR slows down query?
> I am having problem using the operator OR. when I test for condition A > OR B in a query, the query becomes extensively slow or even fails, > even though a test on condition A or B alone gives result right away. I learned recently that you can often use a UNION to solve problems like this. It's true that even with indexes (if you aren't using indexes already, you should try that first) OR queries can be slow. But you can often rewrite a query like this: SELECT Row FROM MyTable WHERE MyColumn = 'value1' or MyColumn = 'value2'; as: (SELECT Row FROM MyTable WHERE MyColumn = 'value1') UNION (SELECT Row FROM MyTable WHERE MyColumn = 'value2') ...and get very fast, indexed results that are otherwise identical. It's also worth noting that you can take an ORDER BY on the end of UNION to order both sets as one. Like so: SELECT Row FROM MyTable WHERE MyColumn = 'value1' or MyColumn = 'value2' ORDER BY Row; becomes: (SELECT Row FROM MyTable WHERE MyColumn = 'value1') UNION (SELECT Row FROM MyTable WHERE MyColumn = 'value2') ORDER BY Row; I recently was able to speed up several queries in my application using these UNIONs in place of ORs. Good Luck. -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf on MySQL-Server-4.0.17
Ronan Lucio wrote: Hi All, I´m trying to do some customization in a MySQL-Server-4.0.17 to gain a better performance. We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD on a FreeBSD-5.2.1 box dedicated for MySQL. The diagnostic error messages are a fairly strong indication that you could have bad RAM. If you can take the machine down for a day, run memtest86 on it. If not, try different memory modules or underclock your RAM in BIOS. This could also be a bug in FreeBSD kernel or libc. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: operator OR slows down query?
On 20 Feb 2004 at 10:57, Yun Li wrote: > I am having problem using the operator OR. when I test for condition A > OR B in a query, the query becomes extensively slow or even fails, > even though a test on condition A or B alone gives result right away. See http://www.mysql.com/doc/en/MySQL_indexes.html -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transferring comma-delimited list imto mysql table
I have a large email database that has been translated into a CSV, simply like this: [EMAIL PROTECTED];[EMAIL PROTECTED]; (etc.) I need to get this into SOME format. I have tried Access, which said it was too large - so I am trying my mysql. I created a table with fields ID, and email. I just want to put each email into the email field. I get this error: LOAD DATA LOCAL INFILE 'C:\\PHP\\uploadtemp\\php7.tmp' REPLACE INTO TABLE `email` FIELDS TERMINATED BY ';' MySQL said: #1148 - The used command is not allowed with this MySQL version Can anyone help me? Excel choked, Access choked. My intent is to pull all these from MySQL and into Outlook. If MySQL isn't the app to use, perhaps another solution can be recommended. Thanks so much, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
operator OR slows down query?
hello, everyone I am having problem using the operator OR. when I test for condition A OR B in a query, the query becomes extensively slow or even fails, even though a test on condition A or B alone gives result right away. If you happened to know what the problem it or have any suggestion, please drop me an Email. Thanks a lot. Good day. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary log
Seems to be the binary log saves all INSERT statements, even if it is a duplicate error. How could I restore my table using mysqlbinlog if there are duplicates in the log file. Please help Thanks, George Mathew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning memory vars in /etc/my.cnf
I'm interested in trying to tune MySQL's memory usage variables in my /etc/my.cnf file. I'm currently using the default settings (except for max_allowed_packet, which I've had to increase to support some large queries that I make), they work well. Here is what I've got: key_buffer_size 8M max_allowed_packet8M myisam_sort_buffer_size 8M net_buffer_length 16K sort_buffer_size 2M table_cache 64 thread_concurrency10 read_buffer_size 128K This is on a Linux box with 1.5GB ram and dual 600Mhz P3s. It also runs Apache for a site that doesn't get much load. It's runs a db for that small site and another db for a larger site that gets a fair amount of load (and has it's web server running on another box). In the past, I have tried the my-huge.cnf settings, but they made things slower, so I backed off and went back to the defaults. But, I figure I can probably do better than the defaults, so I was going to try to increase some of these more moderately to see if it helps. Here's what I was planning to try No change: max_allowed_packet8M net_buffer_length 16K sort_buffer_size 2M Some changes: key_buffer_size 32M myisam_sort_buffer_size 32M # Try number of CPU's*2 for thread_concurrency (this note from the "my-huge" file) thread_concurrency4 table_cache 128 read_buffer_size 256K Anyway, I just thought I'd run these be you all to see if I could get any comments. I haven't found much in the way of recommendations for settings to try for my situation, so I'm just sort of guessing here. -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query problem
On Friday 20 February 2004 15:19, Claire Lee wrote: > Hi All, > I have a query problem here. Say I have a table with > employee records of three different departments. If > each department manager wants to see employee info of > their own department. Three different queries will be > needed. Is there a way that I can write one single > query and let SQL decide which department info to > display at the run time? Thanks. What language are you doing this in? You should be able to provide the appropriate 'where' clause when generating the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query problem
Hi All, I have a query problem here. Say I have a table with employee records of three different departments. If each department manager wants to see employee info of their own department. Three different queries will be needed. Is there a way that I can write one single query and let SQL decide which department info to display at the run time? Thanks. __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No version info for libmysql.dll
Currently I am working under a win32 environment developing applications to work with mysql. I have found some inconsistencies with the newer version of libmysql.dll versus older files that are supplied with your current package I have found that there is no version information tied to the different releases that have been provided. It would be an attribute to developers to include the version information for this file when compiled. If this information is supplied with the file, please let me know how I may obtain the version information using win32 api call. Thank you Eric King
Re: Help - Problem in using UNION???
Achuthan <[EMAIL PROTECTED]> wrote: > [-- text/plain, encoding 7bit, charset: us-ascii, 31 lines --] > > hi all > I am facing a problem in fetching the data from two different tables > I have tables called A and B. > Thro PHP code i am trying to connect to MySQL to fetch the desired datas frm both A > & B > My code Goes like this > > "select ename,exp,salary from A Union select ename,exp,sal from B LIMIT 0, 30" > > Case1: > I could find the following inferences when i execute on my Local Host > - The Query is working very well & i could get the datas from A & B on my Localhost > My Localhost has PHP (4.2.3), MySQL(3.23.39) and phpMyAdmin(2.3.2) > I am happy with my Query when i execute. > > > Case2: > I could find the following inferences when i execute on the Online Server > - My Query is throwing the following error > You have an error in your SQL syntax near 'UNION select ename,exp,sal from B LIMIT > 0, 30. > My Online server has PHP (4.3.1), MySQL(3.23.54), phpMyAdmin(2.5.1) > What will be the rootcause for my problem can anyoen Help me out to find the Bug??? Your version of MySQL doesn't support UNION. UNION is implemented in MySQL 4.0.0. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help - Problem in using UNION???
hi all I am facing a problem in fetching the data from two different tables I have tables called A and B. Thro PHP code i am trying to connect to MySQL to fetch the desired datas frm both A & B My code Goes like this "select ename,exp,salary from A Union select ename,exp,sal from B LIMIT 0, 30" Case1: I could find the following inferences when i execute on my Local Host - The Query is working very well & i could get the datas from A & B on my Localhost My Localhost has PHP (4.2.3), MySQL(3.23.39) and phpMyAdmin(2.3.2) I am happy with my Query when i execute. Case2: I could find the following inferences when i execute on the Online Server - My Query is throwing the following error You have an error in your SQL syntax near 'UNION select ename,exp,sal from B LIMIT 0, 30. My Online server has PHP (4.3.1), MySQL(3.23.54), phpMyAdmin(2.5.1) What will be the rootcause for my problem can anyoen Help me out to find the Bug??? Thanks in advance Longing to get the solution Regards Achuthan - Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.
Re: Problems with subquery and 'not in'
Duncan Hill <[EMAIL PROTECTED]> wrote: > Mysql version: 4.1.1 > Platform: Linux, pre-compiled RPMs from mysql.com > [skip] > > My problem: > Right now, I use a routine that selects the IDs that haven't been seen, and > promptly does an insert into notifications_seen to flag that it has been > seen. This works fine. The moment I do alter table notifications_seen add > unique unq_notifid_loginid (notifid, notif_loginid), my query starts > returning utter foolishness - basically, no records where there were records. > Why does adding a unique index cause this? (Or have I just found a bug?) > > Stage 1: No unique index: > SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT > notifid FROM notifications_seen WHERE notif_loginid=2); > +---+-+ > | recid | notify_title| > +---+-+ > | 3 | Some title here | > +---+-+ > > explain SELECT recid, notify_title FROM notifications WHERE recid NOT IN > (SELECT notifid FROM notifications_seen WHERE notif_loginid=2)\G > *** 1. row *** > id: 1 > select_type: PRIMARY >table: notifications > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 2 >Extra: Using where > *** 2. row *** > id: 2 > select_type: DEPENDENT SUBQUERY >table: notifications_seen > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 30 >Extra: Using where > > > Stage 2: Add unique . > SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT > notifid FROM notifications_seen WHERE notif_loginid=2); > Empty set (0.00 sec) > > explain SELECT recid, notify_title FROM notifications WHERE recid NOT IN > (SELECT notifid FROM notifications_seen WHERE notif_loginid=2); > *** 1. row *** > id: 1 > select_type: PRIMARY >table: notifications > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 2 >Extra: Using where > *** 2. row *** > id: 2 > select_type: DEPENDENT SUBQUERY >table: notifications_seen > type: index_subquery > possible_keys: unq_notif_login > key: unq_notif_login > key_len: 8 > ref: func,const > rows: 2 >Extra: Using index; Using where > We need tables to be able to reproduce this behavior. Please, upload them to ftp.mysql.com:/pub/mysql/Incoming and let me know file names. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how setting default for SQL_SELECT_LIMIT ?
"Franz, Fa. PostDirekt MA" <[EMAIL PROTECTED]> wrote: > Dear Ladies and Sirs, > > is ist possible to set a default for SQL_SELECT_LIMIT ? > I tried to put this in my.cnf but it didn't work (server didn't start). > > It's MySQL 4.017 on LINUX. > Default value for the global SQL_SELECT_LIMIT is the maximum value for the CPU. For 32-bit CPU it's 4Gb. So there is no default value. Default value for session SQL_SELECT_LIMIT is the value of global SQL_SELECT_LIMIT. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reporting Engines for MySQL
We are using XSLT and XSL:FOP for our reports. We generate a simple XML file then transform it into a nicely laid out PDF. You can layout the pdf's easily with a nice product called XSLFast www.xslfast.com. Joe On Thursday, February 19, 2004, at 07:33 PM, Matt Silva wrote: I'm looking for a good report generator (similar to Crystal Reports) for MySQL that runs on a Linux/Apache system. I'm currently using a php scripts that I wrote, but its being unbearable to keep up with the report demand. So i'm looking for something I could easily integrate into my current web app. I took a look at the MySQL portal software area on their site (http://www.mysql.com/portal/software/reporting/index.html), but could not find anything just for reporting. It seem most of the utils where for administrating. I was wondering if anybody from the community could recommend anything? Thanks Matt -- Matt Silva Empower Software Technologies, LLC 27851 Bradley Rd. Suite 120 Sun City, CA 92586 PH: (909) 672-6257 WB: www.storagecommander.com EM: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trouble with replication
I have been struggling with one way replication for weeks, still can't make it work. I used windows, MySql 4.0.17 version on both. I have set bin-log option in my.cfg in master computer, in slave computer, set master-host, master-user=repl,master-password, replicate-do-db=test and replicate-ignore-db=mysql, skip-slave-start in my.cfg. I have zipped the database from master, copy the zip to slave and unzip it, then I start master server, after I set all privileges for repl in master server, I start slave. When I do some update in master computer in database, the query ( update test.experiement set .., database is specified in the query) showed in master-bin.001 and it is shown in slave-relay-bin.001 too (slave IO thread is working), but test database in slave doesn't update. What could be wrong? I have donw show slave status, SQL thread seems to be running too, but why it doesn't execute the query from liyingtop-realy-bin.001? I wonder if anyone has experienced the similar situation and know what's wrong with what I did. Liying > > > > *** 1. row *** > > Master_Host: lhuang > > Master_User: repl > > Master_Port: 3306 > > Connect_retry: 60 > > Master_Log_File: lhuang-bin.001 > > Read_Master_Log_Pos: 79 > >Relay_Log_File: liyingtop-relay-bin.001 > > Relay_Log_Pos: 120 > > Relay_Master_Log_File: lhuang-bin.001 > > Slave_IO_Running: Yes > > Slave_SQL_Running: Yes > > Replicate_do_db: test > > Replicate_ignore_db: mysql > >Last_errno: 0 > >Last_error: > > Skip_counter: 0 > > Exec_master_log_pos: 79 > > Relay_log_space: 116 > > 1 row in set (0.00 sec) > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pool Connections
Hello! I´m using MySQL 4, VB6 and ODBC 3.51 and my connection is going down for some reason. Do not know why. Do I need to change the "Connection Pooling" at MyODBC 3.51 Driver? I saw two options and I don´t know exactly what it means. - Don´t pool connections to this driver - Pool connections to this driver Time that unused connections remain in the pool in seconds <<< this is the default, with 60 seconds >>> Thanx! Best regards, Aldo Nakamura aldonakamura@bol.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cleaning up large file ibdata1??
Hi List, I'm having quite a large data file ibdata1 in \mysql\data. The file occupies over 500 MB, while all of my databases at the moment are fairly small (recently, i deleted some old databases). I wonder how it is possible that this file gets so large, compared to the space the rest of my databases take. Does it, for example, contain lots of old data from databases since long deleted?? And I wonder if I can clean it up one way or another; it takes quite some disk space. thanks, rinke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enumerated column
Hello Segismundo, Friday, February 20, 2004, 2:45:20 PM, you wrote: S> Hi, all. S> Another question: IS it possible to produce on a sql consult a new S> column that enumerate the results with order or not on the other columns? S> What is the syntax? S> Thanks, S> Segismundo SET @a=0 SELECT @a:[EMAIL PROTECTED] as counter, field1, field2 from Table however in some cases with complex queries it's not work -- Best regards, artemmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does Dropping a table affect it's indexes?
> The command you're looking for that drops and recreates in one go is > TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is > the same as: > > DROP TABLE table; > CREATE TABLE table (...); > > For MyISAM tables, this is likely to be much faster as MySQL can just > delete the MYI and MYD files associated with the particular table. I'm using MyIsam table, doing a drop and insert into once a night, then just reading from the table so no optimizing should be needed. If I understand you correctly then, the Truncate command WILL recreate any indexes the table had when it recreates the table? Thanks for the help! Jeff > -Original Message- > From: Chris Nolan [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 19, 2004 9:35 PM > To: Steve Edberg > Cc: Jeff McKeon; [EMAIL PROTECTED] > Subject: Re: Does Dropping a table affect it's indexes? > > > Steve Edberg wrote: > > > It's my understanding that doing a simple delete > > > > delete from table_name > > > > actually DOES drop and recreate the table (and thus its indexes). On > > the other hand, if you are continually adding & deleting > records, you > > might well need to do a periodic 'analyze table_name' or 'optimize > > table_name' to maintain optimum performance & clear the > deleted record > > chain. > > > > steve > > Sort of. There's a subtlty here: > > DELETE FROM table; > > Will go and delete all rows from a table. If you're using > InnoDB tables, > new transactions will not see this take effect until you have > issued a > COMMIT (unless they're set to READ_UNCOMMITED isolation > level). I'm not > sure if MyISAM is optimised for this special case. > > The command you're looking for that drops and recreates in one go is > TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is > the same as: > > DROP TABLE table; > CREATE TABLE table (...); > > For MyISAM tables, this is likely to be much faster as MySQL can just > delete the MYI and MYD files associated with the particular table. > > For InnoDB tables, dropping a table involves manipulating the > tablespace. As a result, it doesn't currently support the TRUNCATE > statement. DELETE does specifically delete rows one by one, > so actually > DROPing the table and reCREATE-ing it will be faster. > > Regards, > > Chris > > > > > > > At 03:21 PM 2/19/04, Jeff McKeon wrote: > > > >> Quick question... > >> > >> What you drop a table are the indexes for that table dropped to? > >> > >> I'm about to write a script to take a data pull every night and > >> re-populate a table with the results, then have my apps run off of > >> the new consolidated table for a speed increase. If I drop the > >> Consolidated table, then re-create it with the new data > pull, will I > >> need to re-create the indexes as well? > >> > >> Is there any performance cost/benefit to simply deleting all data > >> from the table and then re-populating it as opposed to droping and > >> re-creating it? > >> > >> Thanks, > >> > >> Jeff > > > > > > > > > > > +- > ---+ > > > > | Steve Edberg > > [EMAIL PROTECTED] | > > | Database/Programming/SysAdmin > > (530)754-9127 | > > | University of California, Davis > > http://pgfsun.ucdavis.edu/ | > > +-- Gort, Klaatu barada nikto! > > --+ > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3.23 JOIN describe to another query
Colleen Dick <[EMAIL PROTECTED]> wrote: > Is it possible to JOIN the results of a DESCRIBE TABLE to some other > query? The semantics of what I would like to do is the following: > > SELECT d_fldcomment from desc LEFT JOIN DESCRIBE footable ON > desc.d_table = 'footable' AND desc.d_field = Field; > > so the resultset would look like a regular DESCRIBE except it > would also include my tacked on comment. > > I KNOW the need for this will be obsoleted in mySQL 5 but people will be > stuck on prev versions for the next 2 years. > > Any clever way to do this or fake it so everything is in one resultset? > I already did it in the obvious boneheaded way and as always when that > happens one things "THERE HAS TO BE A BETTER WAY" > > still on 3.23 You can't mix JOIN and DESCRIBE TABLE. Retrieve data with DESCRIBE TABLE statement and then use programming language to put result of the DESCRIBE to the test of SELECT query. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enumerated column
Hi, all. Another question: IS it possible to produce on a sql consult a new column that enumerate the results with order or not on the other columns? What is the syntax? Thanks, Segismundo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client running xp TIMEOUT PROBLEMS
FOUND ! After many days to search the solution we found it ! The problem is solved in the computer runnig microsoft jet 3.5. You must change to 0 then values in the registry key hkey_local\machine\software\microsoft\jet\3.5\engines\odbc; se the dword ConnectionTimeout. The default is 600 in decimal as the second numbers. If you put 0 the temporary table remain always active. wonderful ! Massimo - Original Message - From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> To: "Massimo Petrini" <[EMAIL PROTECTED]> Cc: "MySQL list" <[EMAIL PROTECTED]> Sent: Friday, January 23, 2004 6:20 PM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client running xp > Hi, > > 1) Try to reproduce the situation with pure myscl client (command-line > client). > 2) There is no any MySQL parameter that change life time of temporary > tables. > Only two parameter that defines life time for connection to mysql: > - interactive_timeout The number of seconds the server waits for activity > on an interactive connection before closing it. An interactive client is > defined as a client that uses the CLIENT_INTERACTIVE option to > mysql_real_connect(). See also wait_timeout. > > - wait_timeout The number of seconds the server waits for activity on a not > interactive connection before closing it. On thread startup > SESSION.WAIT_TIMEOUT is initialized from GLOBAL.WAIT_TIMEOUT or > GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by > the CLIENT_INTERACTIVE connect option). See also interactive_timeout. > > But by default they are quite big: 28800 seconds. > > Mikhail. > > - Original Message - > From: "Massimo Petrini" <[EMAIL PROTECTED]> > To: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > Sent: Friday, January 23, 2004 5:11 PM > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client > running xp > > > > 1) yes, I am sure because the problem is reproducible also with a sql , > > without to use the msaccess; if you use mysqlfront you can create the > table, > > insert data, view data. But if you move your point of view on another > table > > you lost the connection. > > 2) to connect a temporary table in mysql from msaccess, it is necessary to > > create the table on server not temporary, connect the table from msaccess, > > drop the table on server; this is necessary only once; after use a sql > > statement from msaccess, via sql pass through to create the temporary. > Now > > youn can insert data and use as a normal table. When you close the > msaccess > > session the temporary table is dropped immediately; otherwise if you > leave > > the session msaccess opened, but not in use, after some minutes the table > is > > dropped. I think there is a variable to set in my.ini, but I don't > > understand which is the correct name . > > > > So I don't understand. > > > > - Original Message - > > From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > To: "Massimo Petrini" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Thursday, January 22, 2004 2:16 PM > > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > > > > 2 questions: > > > > > > 1. Are you sure that client or someone between client and MySQl server > > > doesn't close connection? > > > Because > > > > the client have still the result of the query on the screen > > > > > > is not a proof that connection is still active. > > > > > > 2. How do you link your msaccess application with MySQL server? > > > Are you sure that your application or someone between doesn't use > > > connections pool? > > > > > > Mikhail. > > > > > > - Original Message - > > > From: "Massimo Petrini" <[EMAIL PROTECTED]> > > > To: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > > Cc: <[EMAIL PROTECTED]> > > > Sent: Thursday, January 22, 2004 1:35 PM > > > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > > > > > > > We have a server running NT4 with mysql 4.017 (with the my.ini as in > > > > previuous mail). In the network the client (the single pc ) run > msaccess > > > > application linked to mysql database. In such case we use a temporary > > > table, > > > > created as follow > > > > > > > > CREATE TEMPORARY TABLE wrk_AlzateIntervalli(Lotto CHAR(10) NOT > > NULL,Indice > > > > CHAR(3) NOT NULL,NMisura SMALLINT NOT NULL,IndiceIntv CHAR(1) NOT > > > > NULL,ValMin REAL NULL,ValMax REAL NULL, PRIMARY KEY (Lotto, Indice, > > > NMisura, > > > > IndiceIntv)); > > > > > > > > The statement sql is lunched from the client to the server; it create > a > > > > temporary table used only from the client creator. > > > > Is clear for you ? > > > > Thanks > > > > > > > > > > > > - Original Message - > > > > From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> > > > > To: "Massimo Petrini" <[EMAIL PROTECTED]> > > > > Cc: <[EMAIL PROTECTED]> > > > > Sent: Thursday, January 22, 2004 11:12 AM > > > > Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 > > > > > > > > >
Re: delete/replace character on column
Segismundo <[EMAIL PROTECTED]> wrote: > Hi, all. > > I've a table with a varchar column, and I need replace a character on > the column, but this character may be on someplace on the field. > Is it possible to change or delete it? What will be the sintax? > If you want to replace all occurrences of some character, use REPLACE() function. For example: UPDATE table SET column=REPLACE(column,'a','b') WHERE .. http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
delete/replace character on column
Hi, all. I've a table with a varchar column, and I need replace a character on the column, but this character may be on someplace on the field. Is it possible to change or delete it? What will be the sintax? Thanks, Segismundo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how setting default for SQL_SELECT_LIMIT ?
Dear Ladies and Sirs, is ist possible to set a default for SQL_SELECT_LIMIT ? I tried to put this in my.cnf but it didn't work (server didn't start). It's MySQL 4.017 on LINUX. thank you Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql low performance on sun/sparc/e4500
Hi all, I'm currently experiencing some very strange behaviour at my MySQL server. Our production server is a Sun-E4500 (8x400 MHz 4 GB Ram running Solaris 8 and Solstice Disksuite...) and a single A5000 "Storedge" with 127 GB disk space (12x9.1GB @ 10K RPM) array connected over a single FC-AL adapter. The MySQL server we use is 4.0.17 and as all queries such as select and insert are really fast, all "update" queries are extremely slow. In numbers it is 17 seconds for a single update query on a 500K record table which looks like this : AGID| int(10) unsigned | | PRI | NULL| auto_increment period | datetime | | MUL | -00-00 00":00:00 | key_ | varchar(100) | | MUL | | type_ | tinyint(3)| | MUL | 0 | name| varchar(100) | | | | value | int(100) unsigned | | | 0 | lastrun | datetime | | | -00-00 00":00:00 | acl | varchar(50) | | | | Now my question : Is this problem a hardware problem (are there any sun specialists here) or are there any known issues with solaris/sparc/a5000 and MySQL any special configuration needed.The server is not under heavy load compared to a single 600 MHz CPU Intel/Linux machine which can handle this tasks without any problems. For the config of the MySQL server we used the configuration described in the current handbook of MySQL. We are really desperate about this machine at the moment since we are not very experienced Sun/Solaris admins and until now tried every trick we could think of to get a bit more performance. If you need more config data please name it and I'll send you all I can get hold off. __ Nachrichten, Musik und Spiele schnell und einfach per Quickstart im WEB.DE Screensaver - Gratis downloaden: http://screensaver.web.de/?mc=021110 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How fast replication
Hi. How fast or what about time different between master and slave suppose when I insert row into master and then select from slave in replication environment. both machine have all the same specification Thank. Kittiphum Worachat. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with subquery and 'not in'
Mysql version: 4.1.1 Platform: Linux, pre-compiled RPMs from mysql.com Table 1: CREATE TABLE `notifications` ( `recid` int(11) NOT NULL auto_increment, `recdate` datetime NOT NULL default '-00-00 00:00:00', `expiry` datetime default NULL, `notify_title` varchar(150) default NULL, `notify_body` text, PRIMARY KEY (`recid`), KEY `idx_recdate` (`recdate`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 Table 2: CREATE TABLE `notifications_seen` ( `notifid` int(11) NOT NULL default '0', `notif_loginid` int(11) NOT NULL default '0', `seenon` datetime default NULL ) TYPE=MyISAM DEFAULT CHARSET=latin1 Table 1 contains a list of notifications that exist. Table 2 contains mappings of which notification IDs have been seen by which loginids. (If you're a lloyds TSB user, you'll know what I'm aiming at.) My query string: SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=$loginid); My problem: Right now, I use a routine that selects the IDs that haven't been seen, and promptly does an insert into notifications_seen to flag that it has been seen. This works fine. The moment I do alter table notifications_seen add unique unq_notifid_loginid (notifid, notif_loginid), my query starts returning utter foolishness - basically, no records where there were records. Why does adding a unique index cause this? (Or have I just found a bug?) Stage 1: No unique index: SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=2); +---+-+ | recid | notify_title| +---+-+ | 3 | Some title here | +---+-+ explain SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=2)\G *** 1. row *** id: 1 select_type: PRIMARY table: notifications type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: notifications_seen type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 30 Extra: Using where Stage 2: Add unique . SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=2); Empty set (0.00 sec) explain SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=2); *** 1. row *** id: 1 select_type: PRIMARY table: notifications type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: notifications_seen type: index_subquery possible_keys: unq_notif_login key: unq_notif_login key_len: 8 ref: func,const rows: 2 Extra: Using index; Using where -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OperationalError: 2013
Hi! I always get OperationalError: 2013 ( 'Lost connection to MySQL server during query' ) while running a query using "mysql front end" on localhost, after 30 seconds. This doesn't happen, when i connect from remote hosts - using any front end . All "timeout" parameters are set to default. Any idea? Best regards, K. Bungarz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Problems
"Scott Purcell" <[EMAIL PROTECTED]> wrote: > > I am trying to create some tables that I can use the delete on cascade = > function for. This would help me code the project and ensure data = > integrity. I am on the docs @ = > http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html but I am = > not quite understanding the syntax. > > I am creating two test tables to work out the logic. What I want to do = > is delete a certain 'ID' from 'table 'ONE' and have it delete the same = > foreign key id of the same value in table 'TWO'. > > Here is what I have entered into the mysql command. > mysql> create table one (id varchar(2) not null, name varchar(20), = > primary key ( > id)) type=3DINNODB; > That is table one, with a primary key on ID; > Now the foreign key table; > > mysql> create table two (fid varchar(2), fname varchar(20), fOREIGN KEY = > (fid) RE > FERENCES one(id) on delete cascade); > > Insert data: > one: > idname > 1'scott' > 2'benjamin' > > two: > fidname > 1 'twoscott' > 2 'twobenjamin' > > > > Problem is when I delete from one where id =3D '1' it does not delete = > the foreign key one in table two. > Check type of the tables with SHOW CREATE TABLE or SHOW TABLE STATUS statements. I guess they have MyISAM table type. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
