RE: array vs. more table cells
Grant, >From my old relational database days we'd create a table of courses and index this so it could be linked from your semester table. This way, you'd have a single entry for each course. Should the course change, the maintenance nightmare is alleviated by only having to change the course entry. In your case, it would be set up something like this: SEMESTER MASTER TABLE A table that describes the semester SEMESTER COURSE TABLE A table that has multiple entries; one for each course. The entry will contain a SEMETER MASTER TABLE ID, and a COURSE TABLE ID, linking the two tables. COURSE TABLE This will contain one record per course, with description of the course, etc. I am uncertain how this would be done with MySQL, but I think the principles are the same. -m- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
I should have mentioned the error: [kryten2] ERROR 2013: Lost connection to MySQL server during query - Original Message - From: "Michael Piko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 9:01 AM Subject: Querying a Linux machine > I have mysql on a Linux machine and can connect to through 'localhost' and > its real IP/hostname. I cannot connect to it from other machines. Is there > anything I need to do to make this happen? > > > Michael Piko > > > -- > 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]
InnoDB questions for all!
Hi everyone! My silly questions for today concern the not-silly-at-all InnoDB table backend. 1. If I do an ALTER TABLE tbl_name TYPE=InnoDB on an InnoDB table, Heikki has indicated that a defrag of that table should happen. Does anyone have any comments regarding the result of a power failure while this operation is in progress? 2. If I do a SELECT * on an InnoDB table and dump the output to a file inside a single transaction, will INSERT statements still complete correctly? I've completed a 3rd year Database course at a decent uni and know the theory as well as the practice, but admittedly I don't know enough about InnoDB's innards to answer this question for myself. My gut feeling says that INSERTs will complete successfully while this process is going on and that UPDATEs may complete, depending on InnoDB's method of multiversioning. Thanks all! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Querying a Linux machine
I have mysql on a Linux machine and can connect to through 'localhost' and its real IP/hostname. I cannot connect to it from other machines. Is there anything I need to do to make this happen? Michael Piko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selectiing a month out of a date
* Creative Solutions New Media > I have a table with a date field (called myDate for example) > formatted like -xx-xx. > > I want to do a query searching for all dates with the month of > August, for example. > > How would I do that? SELECT datecol FROM mytable WHERE MONTHNAME(datecol)="August"; I suppose this would be slightly faster: SELECT datecol FROM mytable WHERE MONTH(datecol) = 8; http://www.mysql.com/doc/en/Date_and_time_functions.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
array vs. more table cells
I'm building a program calendar describing each Year's courses. I have a field for a semester and it's current courses in one cell separated by a ",". When I go to print this out I create an array out of the cell than do it's sorting. Or Should I just take the time and create a new table and separate each course matching it with its course? This would make the database more complicated but less likely that dumb person will screw things up by misplacing a , or adding to many spaces. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up fulltext
Hi,
no - adding a limit doesn't really help.
thanks,
- Mark
On Thu, 28 Aug 2003 16:12:41 -0400, John Larsen wrote:
>Mark wrote:
>Why don't you just always put a limit 1000 on it, do you ever need
>more
>than that?
>
>>Hi,
>>I have a fulltext index on a table with 80,000 rows. when I do a
>>search for a common word it is very slow, for example:
>>
>>select count(*) from resources where match title,keywords
>>against('common word');
>>
>>might take over a minute if there are a 5,000 or so rows that
>>match.
>>I'm looking for a way to speed this up and I'm thinking about
>>adding
>>as stop words any word that occurs in more than 1000 records. is
>>there a way to find these? or is there something else someone can
>>suggest?
>>
>>here are some of my variables:
>>ft_boolean_syntax | + -><()~*:""&
>>ft_min_word_len | 4
>>ft_max_word_len | 254
>>ft_max_word_len_for_sort| 20
>>ft_stopword_file| (built-in)
>>key_buffer_size | 268435456
>>myisam_sort_buffer_size | 67108864
>>
>>here is my table size on disk:
>>-rw-rw1 mysqlmysql8976 Aug 27 10:20
>>resources.frm
>>-rw-rw1 mysqlmysql134471560 Aug 28 09:33
>>resources.MYD
>>-rw-rw1 mysqlmysql61629440 Aug 28 10:23
>>resources.MYI
>>
>>any tips are appreciated.
>>thanks,
>>- Mark
>>
>>
>>
>>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TCP on Solaris 8
On Thu, Aug 28, 2003 at 03:02:20PM -0500, gerald_clark wrote: > Do you have > skip-networking > in your /etc/my.cnf file? > Well, you can call me an idiot. Yes. I was trying to put a second test installation of 4.0.x on while we are still running a 3.x series and I forgot that we had added that option to the main config file. Man do I feel dumb now! Thanks -Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are Duplicate keys allowed???
At 03:30 PM 8/28/2003, you wrote: In the last episode (Aug 28), mos said: > Example. Join_Time is usually NULL (but not always). I've discovered that > if Join_Time is NULL, MySQL allows for duplicate rows! ! > > Data: > Cust_Type, Area, Join_Date, Join_Time > 'BIG', 'N', '2003-01-01',NULL > 'BIG', 'N', '2003-01-01',NULL > 'BIG', 'N', '2003-01-01',NULL > > These rows are allowed even though they are all duplicates. I could > have the same row duplicated a thousand times. Why? Apparently MySQL > only prevents duplicate rows if Join_Time is not NULL. It seems that > if Join_Time is NULL then all bets are off and you can have as many > duplicates as you like. I don't understand the reasoning behind this > loophole. NULL is a special value, and you are allowed to have more than one of them them even in a UNIQUE index. Since the test "NULL = NULL" returns false, two otherwise identical records with a NULL in them are not equal either and are not cinsidered duplicates. See http://www.mysql.com/doc/en/Working_with_NULL.html and http://www.mysql.com/doc/en/Problems_with_NULL.html for more info. -- Dan Nelson [EMAIL PROTECTED] Dan, Thanks. That tends to make sense. I'll change the Join_Time to NOT NULL and add a default value to catch the duplicates. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many hours - brain not working.....
Thanks to everyone that replied on and off list - off to the group by docs ;) On 8/28/03 5:09 PM, "William R. Mussatto" <[EMAIL PROTECTED]> wrote: > Mike Morton said: >> Hey y'all - I have been at this too long today - this is driving me >> nuts! >> >> Table: >> Date (y-m-d),amount (decimal),dealercode >> >> I am looking for a query that will give me the sum of each of the >> distinct dealercodes for a specific date range. >> >> Something to the effect of: >> Select (distinct dealercode),sum(amount) as total where date between >> 'this' and 'that' >> >> The trouble that I am running into is that I cannot seem to get that >> done in one query - splitting it up into 2 is easy enough - do the >> distinct dealercode, then loop through those to do the sum between date >> query - but ideally I would like to do it in one query. If this is >> possible to do - how? >> >> TIA! >> >> >> >> -- >> Cheers >> >> Mike Morton >> >> >> * >> * Tel: 905-465-1263 >> * Email: [EMAIL PROTECTED] >> * >> >> >> "Indeed, it would not be an exaggeration to describe the history of the >> computer industry for the past decade as a massive effort to keep up >> with Apple." >> - Byte Magazine >> >> Given infinite time, 100 monkeys could type out the complete works of >> Shakespeare. Win 98 source code? Eight monkeys, five minutes. >> -- NullGrey > Welcome to 'Group By' > Table: Date (y-m-d),amount (decimal),dealercode > select sum(amount),dealercode from table where date > 'date1' and date < > 'date2' group by dealercode > > > William R. Mussatto, Senior Systems Engineer > Ph. 909-920-9154 ext. 27 > FAX. 909-608-7061 > > -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many hours - brain not working.....
Mike Morton said: > Hey y'all - I have been at this too long today - this is driving me > nuts! > > Table: > Date (y-m-d),amount (decimal),dealercode > > I am looking for a query that will give me the sum of each of the > distinct dealercodes for a specific date range. > > Something to the effect of: > Select (distinct dealercode),sum(amount) as total where date between > 'this' and 'that' > > The trouble that I am running into is that I cannot seem to get that > done in one query - splitting it up into 2 is easy enough - do the > distinct dealercode, then loop through those to do the sum between date > query - but ideally I would like to do it in one query. If this is > possible to do - how? > > TIA! > > > > -- > Cheers > > Mike Morton > > > * > * Tel: 905-465-1263 > * Email: [EMAIL PROTECTED] > * > > > "Indeed, it would not be an exaggeration to describe the history of the > computer industry for the past decade as a massive effort to keep up > with Apple." > - Byte Magazine > > Given infinite time, 100 monkeys could type out the complete works of > Shakespeare. Win 98 source code? Eight monkeys, five minutes. > -- NullGrey Welcome to 'Group By' Table: Date (y-m-d),amount (decimal),dealercode select sum(amount),dealercode from table where date > 'date1' and date < 'date2' group by dealercode William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selectiing a month out of a date
Hi, I have a table with a date field (called myDate for example) formatted like -xx-xx. I want to do a query searching for all dates with the month of August, for example. How would I do that? Tim Winters Manager, Creative Development Sampling Technologies Incorporated (STI) [EMAIL PROTECTED] [EMAIL PROTECTED] W: 902 450 5500 C: 902 430 8498 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on data structures
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Pascal, et al -- ...and then Pascal Francq said... % % Hi, Hi! % I have a question. I need to store documents (>100.000) and the word % containing in them. % % They are two way to do the job : [snip] What about a third way? One table for the docs with a docid, one table for all of the words with a wordid, and one table to tie them together containing the docid and the wordid. Duplicate words only require one row in the word table and would have required separate docid entries anyway, so you save. HTH & HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/Tm43Gb7uCXufRwARAmsQAKDijezqJNt28nKZus1PJxyPZNeixACeNzNx 5DBbgx0Tye/BmxLYTl+TeNU= =PK0V -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Selects
Hi Jay, There seemed to be a problem with the first use of RAND() on new connections (is this in a script?). It was supposed to be fixed in a recent version of MySQL (I think in 3.23.56 and 4.0.10). Anyway, the workaround I discovered to work is to just call RAND() at least once before using it in your random query. e.g. just run a query like this first: SELECT RAND(), RAND(), RAND(); BTW, what version of MySQL are you using? Try running your query multiple times from the command line and it will probably work correctly after the first time. So either upgrade MySQL or use my above workaround. :-) Hope that helps. Matt - Original Message - From: "Jay Paulson" Sent: Thursday, August 28, 2003 2:57 PM Subject: Random Selects Hello- I'm trying to get random information out of my table and the query I'm using keeps returning the same row every time. In the table I have 3 rows and I want to choose at random in the sql which row to return. Here's an example query I have. SELECT * FROM banner ORDER BY rand() asc limit 0,1 This returns the first row in the table every time. What's strange is that I have a query for a table that has about 500 rows in it and it works fine with the exact same syntex (except the the table is different). Is there anything I'm doing wrong? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many hours - brain not working.....
Hey y'all - I have been at this too long today - this is driving me nuts! Table: Date (y-m-d),amount (decimal),dealercode I am looking for a query that will give me the sum of each of the distinct dealercodes for a specific date range. Something to the effect of: Select (distinct dealercode),sum(amount) as total where date between 'this' and 'that' The trouble that I am running into is that I cannot seem to get that done in one query - splitting it up into 2 is easy enough - do the distinct dealercode, then loop through those to do the sum between date query - but ideally I would like to do it in one query. If this is possible to do - how? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up fulltext
Because his query only returns one record. A limit wouldn't make any
difference.
John Larsen wrote:
Mark wrote:
Why don't you just always put a limit 1000 on it, do you ever need
more than that?
Hi,
I have a fulltext index on a table with 80,000 rows. when I do a
search for a common word it is very slow, for example:
select count(*) from resources where match title,keywords
against('common word');
might take over a minute if there are a 5,000 or so rows that match.
I'm looking for a way to speed this up and I'm thinking about adding
as stop words any word that occurs in more than 1000 records. is
there a way to find these? or is there something else someone can
suggest?
here are some of my variables:
ft_boolean_syntax | + -><()~*:""&
ft_min_word_len | 4
ft_max_word_len | 254
ft_max_word_len_for_sort| 20
ft_stopword_file| (built-in)
key_buffer_size | 268435456
myisam_sort_buffer_size | 67108864
here is my table size on disk:
-rw-rw1 mysqlmysql8976 Aug 27 10:20 resources.frm
-rw-rw1 mysqlmysql134471560 Aug 28 09:33 resources.MYD
-rw-rw1 mysqlmysql61629440 Aug 28 10:23 resources.MYI
any tips are appreciated.
thanks,
- Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are Duplicate keys allowed???
In the last episode (Aug 28), mos said: > Example. Join_Time is usually NULL (but not always). I've discovered that > if Join_Time is NULL, MySQL allows for duplicate rows! ! > > Data: > Cust_Type, Area, Join_Date, Join_Time > 'BIG', 'N', '2003-01-01',NULL > 'BIG', 'N', '2003-01-01',NULL > 'BIG', 'N', '2003-01-01',NULL > > These rows are allowed even though they are all duplicates. I could > have the same row duplicated a thousand times. Why? Apparently MySQL > only prevents duplicate rows if Join_Time is not NULL. It seems that > if Join_Time is NULL then all bets are off and you can have as many > duplicates as you like. I don't understand the reasoning behind this > loophole. NULL is a special value, and you are allowed to have more than one of them them even in a UNIQUE index. Since the test "NULL = NULL" returns false, two otherwise identical records with a NULL in them are not equal either and are not cinsidered duplicates. See http://www.mysql.com/doc/en/Working_with_NULL.html and http://www.mysql.com/doc/en/Problems_with_NULL.html for more info. -- 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: FreeBSD 4.8 runaway MySQL 4.0.14
Ken, Thanks for the response. I really wish MySQL AB would compile FreeBSD binaries with LinuxThreads. It's really a pain when you find a bug, the first thing they ask is "have you compiled this yourself" when in this case you have no choice but to compile it yourself. Maybe Jeremy could compile "unofficial but recommended" binaries that MySQL AB would support more like official binaries? :) If he's got nothing better to do of course, he already puts in so much time to the MySQL community as is. Nick -Original Message- From: Ken Menzel [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 4:14 PM To: Nick Gaugler; [EMAIL PROTECTED] Subject: Re: FreeBSD 4.8 runaway MySQL 4.0.14 Hi Nick, This is due (so I am told) to a problem in FreeBSD threads. I have this happen once in a while also. There have been some threads changes for 4.9 but I am not sure there is any change in this. The common suggestion is to compile with LINUXTHREADS option using the ports tree. This will solve that and give you better performance on a multi CPU system. (cd /usr/ports/databases/mysql41-server; more Makefile ) or see http://jeremy.zawodny.com/blog/archives/000458.html Another option would be to update to FreeBSD 5.1 and use the new threading (not so easy). I personaly hope 4.9 will settle this down some as I don't see the need to go to LINUX Threads for a single proc machine! Best of Luck Ken - Original Message - From: "Nick Gaugler" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 28, 2003 3:53 PM Subject: FW: FreeBSD 4.8 runaway MySQL 4.0.14 > I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all > MyISAM tables, and after alittle while mysqld will jump to 100% of my > CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can > see below, there is nothing happening with Innodb. Infact my Innodb > files have not even changed since I last restarted. But for some reason > MySQLd uses 100% of my CPU with no queries happening. I tried to run > ktrace and this was all I saw: > > > 30488 mysqld CALL gettimeofday(0x2846e288,0) > 30488 mysqld RET gettimeofday 0 > 30488 mysqld CALL poll(0x83b6000,0xc,0) > 30488 mysqld RET poll 0 > 30488 mysqld CALL poll(0x83b6000,0xd,0x126) > 30488 mysqld RET poll 1 > 30488 mysqld CALL gettimeofday(0x2846e288,0) > 30488 mysqld RET gettimeofday 0 > 30488 mysqld CALL poll(0x83b6000,0xd,0x126) > 30488 mysqld RET poll 1 > > With an occasional > > 30488 mysqld CALL poll(0x83b6000,0xd,0x126) > 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 > code=0x0 > 30488 mysqld RET poll 1 > 30488 mysqld CALL gettimeofday(0x2846e288,0) > 30488 mysqld RET gettimeofday 0 > 30488 mysqld CALL sigreturn(0x83a9db8) > 30488 mysqld RET sigreturn JUSTRETURN > > > Has anyone experienced a runway mysqld on FreeBSD before? Is there > anything I can do to fix this or solve this problem? Below is show > innodb status\g > > = > 030828 14:36:37 INNODB MONITOR OUTPUT > = > Per second averages calculated from the last 16 seconds > -- > SEMAPHORES > -- > OS WAIT ARRAY INFO: reservation count 4, signal count 4 > Mutex spin waits 0, rounds 0, OS waits 0 > RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 > > TRANSACTIONS > > Trx id counter 0 1280 > Purge done for trx's n:o < 0 0 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 0, not started, OS thread id 299134976 > MySQL thread id 176926, query id 516226 localhost root > show innodb status > > FILE I/O > > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 40 OS file reads, 4 OS file writes, 4 OS fsyncs > 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s > - > INSERT BUFFER AND ADAPTIVE HASH INDEX > - > Ibuf for space 0: size 1, free list len 0, seg size 2, > 0 inserts, 0 merged recs, 0 merges > Hash table size 34679, used cells 0, node heap has 0 buffer(s) > 0.00 hash searches/s, 0.00 non-hash searches/s > --- > LOG > --- > Log sequence number 0 43892 > Log flushed up to 0 43892 > Last checkpoint at 0 43892 > 0 pending log writes, 0 pending chkp writes > 9 log i/o's done, 0.00 log i/o's/second > -- > BUFFER POOL AND MEMORY > -- > Total memory allocated 16338600; in additional pool allocated 641280 > Buffer pool size 512 > Fr
Re: FreeBSD 4.8 runaway MySQL 4.0.14
Hi Nick, This is due (so I am told) to a problem in FreeBSD threads. I have this happen once in a while also. There have been some threads changes for 4.9 but I am not sure there is any change in this. The common suggestion is to compile with LINUXTHREADS option using the ports tree. This will solve that and give you better performance on a multi CPU system. (cd /usr/ports/databases/mysql41-server; more Makefile ) or see http://jeremy.zawodny.com/blog/archives/000458.html Another option would be to update to FreeBSD 5.1 and use the new threading (not so easy). I personaly hope 4.9 will settle this down some as I don't see the need to go to LINUX Threads for a single proc machine! Best of Luck Ken - Original Message - From: "Nick Gaugler" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 28, 2003 3:53 PM Subject: FW: FreeBSD 4.8 runaway MySQL 4.0.14 > I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all > MyISAM tables, and after alittle while mysqld will jump to 100% of my > CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can > see below, there is nothing happening with Innodb. Infact my Innodb > files have not even changed since I last restarted. But for some reason > MySQLd uses 100% of my CPU with no queries happening. I tried to run > ktrace and this was all I saw: > > > 30488 mysqld CALL gettimeofday(0x2846e288,0) > 30488 mysqld RET gettimeofday 0 > 30488 mysqld CALL poll(0x83b6000,0xc,0) > 30488 mysqld RET poll 0 > 30488 mysqld CALL poll(0x83b6000,0xd,0x126) > 30488 mysqld RET poll 1 > 30488 mysqld CALL gettimeofday(0x2846e288,0) > 30488 mysqld RET gettimeofday 0 > 30488 mysqld CALL poll(0x83b6000,0xd,0x126) > 30488 mysqld RET poll 1 > > With an occasional > > 30488 mysqld CALL poll(0x83b6000,0xd,0x126) > 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 > code=0x0 > 30488 mysqld RET poll 1 > 30488 mysqld CALL gettimeofday(0x2846e288,0) > 30488 mysqld RET gettimeofday 0 > 30488 mysqld CALL sigreturn(0x83a9db8) > 30488 mysqld RET sigreturn JUSTRETURN > > > Has anyone experienced a runway mysqld on FreeBSD before? Is there > anything I can do to fix this or solve this problem? Below is show > innodb status\g > > = > 030828 14:36:37 INNODB MONITOR OUTPUT > = > Per second averages calculated from the last 16 seconds > -- > SEMAPHORES > -- > OS WAIT ARRAY INFO: reservation count 4, signal count 4 > Mutex spin waits 0, rounds 0, OS waits 0 > RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 > > TRANSACTIONS > > Trx id counter 0 1280 > Purge done for trx's n:o < 0 0 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 0, not started, OS thread id 299134976 > MySQL thread id 176926, query id 516226 localhost root > show innodb status > > FILE I/O > > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 40 OS file reads, 4 OS file writes, 4 OS fsyncs > 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s > - > INSERT BUFFER AND ADAPTIVE HASH INDEX > - > Ibuf for space 0: size 1, free list len 0, seg size 2, > 0 inserts, 0 merged recs, 0 merges > Hash table size 34679, used cells 0, node heap has 0 buffer(s) > 0.00 hash searches/s, 0.00 non-hash searches/s > --- > LOG > --- > Log sequence number 0 43892 > Log flushed up to 0 43892 > Last checkpoint at 0 43892 > 0 pending log writes, 0 pending chkp writes > 9 log i/o's done, 0.00 log i/o's/second > -- > BUFFER POOL AND MEMORY > -- > Total memory allocated 16338600; in additional pool allocated 641280 > Buffer pool size 512 > Free buffers 493 > Database pages 19 > Modified db pages 0 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 19, created 0, written 0 > 0.00 reads/s, 0.00 creates/s, 0.00 writes/s > No buffer pool page gets since the last printout > -- > ROW OPERATIONS > -- > 0 queries inside InnoDB, 0 queries in queue > Main thread id 158370816, state: waiting for server activity > Number of rows inserted 0, updated 0, deleted 0, read 0 > 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s > > END OF INNODB MONITOR OUTPUT > > Nick > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/m
Re: speeding up fulltext
Mark wrote:
Why don't you just always put a limit 1000 on it, do you ever need more
than that?
Hi,
I have a fulltext index on a table with 80,000 rows. when I do a
search for a common word it is very slow, for example:
select count(*) from resources where match title,keywords
against('common word');
might take over a minute if there are a 5,000 or so rows that match.
I'm looking for a way to speed this up and I'm thinking about adding
as stop words any word that occurs in more than 1000 records. is
there a way to find these? or is there something else someone can
suggest?
here are some of my variables:
ft_boolean_syntax | + -><()~*:""&
ft_min_word_len | 4
ft_max_word_len | 254
ft_max_word_len_for_sort| 20
ft_stopword_file| (built-in)
key_buffer_size | 268435456
myisam_sort_buffer_size | 67108864
here is my table size on disk:
-rw-rw1 mysqlmysql8976 Aug 27 10:20 resources.frm
-rw-rw1 mysqlmysql134471560 Aug 28 09:33
resources.MYD
-rw-rw1 mysqlmysql61629440 Aug 28 10:23 resources.MYI
any tips are appreciated.
thanks,
- Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why are Duplicate keys allowed???
I have a table with a UNIQUE compound index, Cust_Primary that is composed of 4 columns: Cust_Type, Area, Join_Date, Join_Time. This index should allow for only unique entries, but it doesn't. If Join_Time is NULL then it allows for duplicates. Why? CREATE TABLE `CustHistory2` ( `Cust_Id` int(10) unsigned NOT NULL auto_Increment, `Area` char(2) default NULL, `Cust_Type` char(17) default NULL, `Join_Date` date default NULL, `Join_Time` time default null, PRIMARY KEY (`Cust_Id`), UNIQUE KEY `Cust_Id` (`Cust_Id`), UNIQUE KEY `Cust_Primary` (`Cust_Type`,`Area`,`Join_Date`,`Join_Time`) ) TYPE=MyISAM Example. Join_Time is usually NULL (but not always). I've discovered that if Join_Time is NULL, MySQL allows for duplicate rows! ! Data: Cust_Type, Area, Join_Date, Join_Time 'BIG', 'N', '2003-01-01',NULL 'BIG', 'N', '2003-01-01',NULL 'BIG', 'N', '2003-01-01',NULL These rows are allowed even though they are all duplicates. I could have the same row duplicated a thousand times. Why? Apparently MySQL only prevents duplicate rows if Join_Time is not NULL. It seems that if Join_Time is NULL then all bets are off and you can have as many duplicates as you like. I don't understand the reasoning behind this loophole. The only way around this is to specify a NOT NULL for Join_Time and define a default value like 0 so it defaults to 12:00:00AM. I don't see why I have to do this. Can someone shed some light on this? TIA Mike MySQL version 4.10 Alpha-Max-Nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New to MySQL, Suggestions welcome!
Dear All. I hope all is well with you. A projects requires that for the 1st time ever for me, install a bugzilla system with MySQL and Apache IWhat would you recommend when it comes to installing, designing, or tips about My SQL? perhaps stuff I should know no matter what. I am reading on information about MySQL and so far, I;m getting the picture of it all, however, would appreciate some feedback from the real world experiences or what to do and not to do, etc. Thanks in advance! Mike - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: TCP on Solaris 8
Do you have skip-networking in your /etc/my.cnf file? Scott Barron wrote: Hello, I am attempting to get MySQL to listen on a TCP socket on Solaris 8. From what I've seen in the documentation, and my experience with MySQL on Linux this should happen automatically. I've tried 4.0.14 from source and binaries as well as 3.23.57 binaries without success. If I compile with the --with-tcp-port set to a certain port, just starting mysqld_safe leaves the following in the logs: mysqld: ready for connections. Version: '4.0.14' socket: '/tmp/mysql.sock.2' port: 0 If i specify -P 3306 on the command line it logs: mysqld: ready for connections. Version: '4.0.14' socket: '/tmp/mysql.sock.2' port: 3306 Yet I cannot connect on port 3306 and netstat does not show anything for port 3306 (or any port that I might specify at build or run time). I'm pretty lost because everything I do on the solaris box I can repeat on the Linux box and have it work like I expect. Has anyone else had a similar problem? Am I just missing something? Thanks, -Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Selects
Hello- I'm trying to get random information out of my table and the query I'm using keeps returning the same row every time. In the table I have 3 rows and I want to choose at random in the sql which row to return. Here's an example query I have. SELECT * FROM banner ORDER BY rand() asc limit 0,1 This returns the first row in the table every time. What's strange is that I have a query for a table that has about 500 rows in it and it works fine with the exact same syntex (except the the table is different). Is there anything I'm doing wrong? Thanks!
FW: FreeBSD 4.8 runaway MySQL 4.0.14
I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all MyISAM tables, and after alittle while mysqld will jump to 100% of my CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can see below, there is nothing happening with Innodb. Infact my Innodb files have not even changed since I last restarted. But for some reason MySQLd uses 100% of my CPU with no queries happening. I tried to run ktrace and this was all I saw: 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xc,0) 30488 mysqld RET poll 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 With an occasional 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 code=0x0 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL sigreturn(0x83a9db8) 30488 mysqld RET sigreturn JUSTRETURN Has anyone experienced a runway mysqld on FreeBSD before? Is there anything I can do to fix this or solve this problem? Below is show innodb status\g = 030828 14:36:37 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 TRANSACTIONS Trx id counter 0 1280 Purge done for trx's n:o < 0 0 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 299134976 MySQL thread id 176926, query id 516226 localhost root show innodb status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 40 OS file reads, 4 OS file writes, 4 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 43892 Log flushed up to 0 43892 Last checkpoint at 0 43892 0 pending log writes, 0 pending chkp writes 9 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16338600; in additional pool allocated 641280 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 158370816, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TCP on Solaris 8
Hello, I am attempting to get MySQL to listen on a TCP socket on Solaris 8. >From what I've seen in the documentation, and my experience with MySQL on Linux this should happen automatically. I've tried 4.0.14 from source and binaries as well as 3.23.57 binaries without success. If I compile with the --with-tcp-port set to a certain port, just starting mysqld_safe leaves the following in the logs: mysqld: ready for connections. Version: '4.0.14' socket: '/tmp/mysql.sock.2' port: 0 If i specify -P 3306 on the command line it logs: mysqld: ready for connections. Version: '4.0.14' socket: '/tmp/mysql.sock.2' port: 3306 Yet I cannot connect on port 3306 and netstat does not show anything for port 3306 (or any port that I might specify at build or run time). I'm pretty lost because everything I do on the solaris box I can repeat on the Linux box and have it work like I expect. Has anyone else had a similar problem? Am I just missing something? Thanks, -Scott -- NO ONE CAN DEFEAT THE QUAD LASER! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to start MYSQL
Hi Team, I am unable to start the mysql service at my red hat 8, below is the error. What it is all about ? /usr/sbin/mysqld: ERROR: unknown option '--log-bin # required for re plication' 030829 01:01:37 mysqld ended Thanks in advance Suresh A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speeding up fulltext
Hi,
I have a fulltext index on a table with 80,000 rows. when I do a
search for a common word it is very slow, for example:
select count(*) from resources where match title,keywords
against('common word');
might take over a minute if there are a 5,000 or so rows that match.
I'm looking for a way to speed this up and I'm thinking about adding
as stop words any word that occurs in more than 1000 records. is
there a way to find these? or is there something else someone can
suggest?
here are some of my variables:
ft_boolean_syntax | + -><()~*:""&
ft_min_word_len | 4
ft_max_word_len | 254
ft_max_word_len_for_sort| 20
ft_stopword_file| (built-in)
key_buffer_size | 268435456
myisam_sort_buffer_size | 67108864
here is my table size on disk:
-rw-rw1 mysqlmysql8976 Aug 27 10:20 resources.frm
-rw-rw1 mysqlmysql134471560 Aug 28 09:33
resources.MYD
-rw-rw1 mysqlmysql61629440 Aug 28 10:23 resources.MYI
any tips are appreciated.
thanks,
- Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup procedure
All, I'm looking for opinions/suggestions on a backup procedure I plan on implementing. All databases (DBXX) will be MySQL ver 4.0 All our applications work with DB01. DB01 replicates to DB02. Once a day I will Stop the slave on DB02, lock the tables, flush the logs and perform a mysqldump of the database. I will then copy the existing bin.log files and config filesls to a backup directory and then backup the db dump and bin.logs to an external backup device. A) this seems like the most non-intrusive way to get a clean backup of the database, is it? B) I'm not sure how I'm going to automate the unlocking of the tables and the restart of the slave only AFTER the dump is done. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeateable read isolation level question
Chris, please send your messages to [EMAIL PROTECTED] The newsgroup mailing.database.mysql is only a mirror. - Original Message - From: "Chris" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, August 28, 2003 5:00 PM Subject: Repeateable read isolation level question > I think I understand the concept of repeatable read, but this scenario > doesn't make sense to me, if someone can explain. > > Say you have an innodb table test with a string column s = 'blank' and > are using the default isolation level of repeatable read. > > Session 1: > begin; > > Session 2: > begin; > > S1: > update test set s = 'session one' where id = 1; > commit; > > S2: > mysql> select * from test; > +---+--+ > | s | id | > +---+--+ > | blank |1 | > +---+--+ > 1 row in set (0.00 sec) > > (so we cannot see the changes from S1's transaction yet, only when S2 > commits or rolls back will it see the changes) > > mysql> update test set s = 'session two' where s = 'blank' and id = 1; > Query OK, 0 rows affected (0.00 sec) > Rows matched: 0 Changed: 0 Warnings: 0 > > mysql> update test set s = 'session two' where s = 'session one' and > id = 1; > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > So this is very weird to me, that the first update fails. You cannot > see the updates from session 1 in a select due to the isolation level, > yet your where clause sees the updates...??? > > Mind you, this is good since it avoids lost updates, but I just don't > understand it--it seems inconsistent. The idea is that a plain SELECT is a consistent non-locking read which reads a historical snapshot of the database. But, of course, an UPDATE must not read a historical snapshot. An UPDATE must get the latest data, and lock the rows. If you make a locking SELECT, then you see what is going on in the UPDATE, because a locking SELECT always reads the latest data: SELECT ... LOCK IN SHARE MODE; or SELECT ... FOR UPDATE; > Thanks, > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL API C & leaks
Andreï V. FOMITCHEV wrote:
Hello everyone,
I use Valgrind to check my programs and this last found leaks in
libmysqlclient.so.
My code is simple:
char * requete = "SELECT * FROM "NOM_TABLE_1;
Looks like a misplaced '"' .
MYSQL * mysql = mysql_init((MYSQL *)NULL);
MYSQL_RES * mysql_resultat;
MYSQL_ROW mysql_ligne;
if(mysql == NULL)
{
return(-1);
}
if (!mysql_real_connect(mysql, ADRESSE_BDD, LOGIN_BDD, PASSWORD_BDD,
NOM_BDD, 0, NULL, 0))
{
fprintf(stderr, "Impossible de se connecter au serveur %s\n
Error=%s\n", ADRESSE_BDD, mysql_error(mysql));
mysql_close(mysql);
return(-1);
}
if(mysql_query(mysql, requete) != 0)
{
fprintf(stderr, "Impossible d'executer la requête %s\nError=%s\n",
requete, mysql_error(mysql));
}
else
{
mysql_resultat = mysql_store_result(mysql);
if(mysql_resultat != NULL)
{
for(i = 0; i < mysql_resultat->row_count; i++)
{
mysql_ligne = mysql_fetch_row(mysql_resultat);
// traitement
}
}
mysql_free_result(mysql_resultat);
}
mysql_close(mysql);
Result of Valgrind: ==3691== 64 bytes in 1 blocks are still reachable
in loss record 1 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x40254A2D: my_malloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025A218: init_dynamic_array (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AE96: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
==3691== ==3691== 4088 bytes in 1 blocks are still reachable in loss
record 2 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x4025A64C: my_once_alloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025ACB0: read_charset_index (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AEAB: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
Is it a BUG or did I something?
Best regards,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: 2.6.0-testX and InnoDB (was: Re: 2.6.0-test2-mm3 and mysql)
Sergey, I replied to the Linux kernel mailing list 10 min ago :). Below my reply. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ . Sergey, does it always crash when you start mysqld? It is page number 0 in the InnoDB tablespace. That is, the header page of the whole tablespace! The checksums in the page are ok. That shows the page was not corrupted in the Linux file system. InnoDB is trying to do an index search, but that of course crashes, because the header page is not any index page. The reason for the crash is probably that a page number in a pointer record in the father node of the B-tree has been reset to zero. The corruption has happened in the mysqld process memory, not in the file system of Linux. Otherwise, InnoDB would have complained about page checksum errors. No one else has reported this error. I have now added a check to a future version of InnoDB which will catch this particular error earlier and will hex dump the father page. By the way, I noticed that a website http://www.linuxtestproject.org has made an extensive regression test suite for Linux. They have also successfully run big MySQL and DB2 stress tests on their computers, on 2.5.xx kernels. If there is something wrong with 2.5.xx or 2.6.0, it apparently does not concern all computers. " The Linux Test Project test suite, ltp-20030807, has been released. The latest version of the testsuite contains 2000+ tests for the Linux OS. " The general picture about InnoDB corruption is that reports have almost stopped after I advised people on the mailing list to upgrade to Linux-2.4.20 kernels. With apologies, Heikki Innobase Oy http://www.innodb.com " 030827 15:34:10 InnoDB: Page checksum 1165918361, prior-to-4.0.14-form checksum 4088416325 InnoDB: stored checksum 1165918361, prior-to-4.0.14-form stored checksum 4088416325 InnoDB: Page lsn 0 4080819655, low 4 bytes of lsn at page end 4080819655 InnoDB: Page directory corruption: supremum not pointed to 030827 15:34:10 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 457e809900 00f33c5dc7f356ce97000100 040f040240006c000200040 1b6000401de000400028000144e0004009e 003601160002800015de0b41000 2000200260002b5e50026000200027d300026000119 3a002600014000207e0001809e0 003 ... 000f3b04845f33c5dc7 " . Subject: Fwd: 2.6.0-testX and InnoDB (was: Re: 2.6.0-test2-mm3 and mysql) From: Sergey S. Kostyliov Date: Thu, 28 Aug 2003 21:50:02 +0400 Hello all, This is the copy of my post to linux-kernel list over a hour ago. Am I the only one having problems with 2.6.0-testX linux kernels and InnoDB? -- Forwarded Message -- Subject: 2.6.0-testX and InnoDB (was: Re: 2.6.0-test2-mm3 and mysql) Date: Thursday 28 August 2003 20:15 From: "Sergey S. Kostyliov" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Hello all, On Wednesday 27 August 2003 19:52, Sergey S. Kostyliov wrote: > On Monday 04 August 2003 04:05, Matt Mackall wrote: > > All Linux kernels prior to 2.6.0-test2-mm3-1 would silently fail to > > complete fsync() and msync() operations if they encountered an I/O > > error, resulting in corruption. If a particular disk subsystem was > > producing these errors, the symptoms would likely be: > > > > - no error reported > > - no messages in logs > > - independent of kernel version, etc. > > - suddenly appear at some point in drive life > > - works flawlessly on other machines > > > > If you can reproduce this corruption, please try running against mm3-1 > > and seeing if it reports problems (both to fsync and in logs). > > I've just got another one InnoDB crash with 2.6.0-test4. > As in previous case there was no messages in kernel log. > You can find mysql error log here. > Re: 2.6.0-test2-mm3 and mysql And here is another one InnoDB crash I've just got with 2.6.0-test4. http://sysadminday.org.ru/linux-2.6.0-test4_InnoDB_crash-20030828 No messages in kernel log :(( > It's a development server, so this isn't a big problem. > I do understand that this can easily be a hardware problem, > but the kernel silence is really sad in such case. > Memory is fine (at least according to memtest 3.0). > > Any hints will be appreciated. -- Best regards, Sergey S. Kostyliov <[EMAIL PROTECTED]> Public PGP key: http://sysadminday.org.ru/rathamahata.asc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: 2.6.0-testX and InnoDB (was: Re: 2.6.0-test2-mm3 and mysql)
Hello all, This is the copy of my post to linux-kernel list over a hour ago. Am I the only one having problems with 2.6.0-testX linux kernels and InnoDB? -- Forwarded Message -- Subject: 2.6.0-testX and InnoDB (was: Re: 2.6.0-test2-mm3 and mysql) Date: Thursday 28 August 2003 20:15 From: "Sergey S. Kostyliov" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Hello all, On Wednesday 27 August 2003 19:52, Sergey S. Kostyliov wrote: > On Monday 04 August 2003 04:05, Matt Mackall wrote: > > All Linux kernels prior to 2.6.0-test2-mm3-1 would silently fail to > > complete fsync() and msync() operations if they encountered an I/O > > error, resulting in corruption. If a particular disk subsystem was > > producing these errors, the symptoms would likely be: > > > > - no error reported > > - no messages in logs > > - independent of kernel version, etc. > > - suddenly appear at some point in drive life > > - works flawlessly on other machines > > > > If you can reproduce this corruption, please try running against mm3-1 > > and seeing if it reports problems (both to fsync and in logs). > > I've just got another one InnoDB crash with 2.6.0-test4. > As in previous case there was no messages in kernel log. > You can find mysql error log here. > Re: 2.6.0-test2-mm3 and mysql And here is another one InnoDB crash I've just got with 2.6.0-test4. http://sysadminday.org.ru/linux-2.6.0-test4_InnoDB_crash-20030828 No messages in kernel log :(( > It's a development server, so this isn't a big problem. > I do understand that this can easily be a hardware problem, > but the kernel silence is really sad in such case. > Memory is fine (at least according to memtest 3.0). > > Any hints will be appreciated. -- Best regards, Sergey S. Kostyliov <[EMAIL PROTECTED]> Public PGP key: http://sysadminday.org.ru/rathamahata.asc --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on data structures
* Harald Fuchs [...] > 4) The documents are stored in a table 'docs' and identified by a 'docid'. >The words are found by a FULLTEXT index. The FULLTEXT feature is usefull if you don't need to search fast on 'words' like C++, TCP/IP, IBM, XP, WIN, W2K etc. What these 'words' have in common, is that they are smaller than four characters and/or contains special characters. AFAIK the FULLTEXT feature can not handle these words, unless you tweak some system files and compile the server yourself. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to code an 'IS - A' relationship ?
> Hello MySQL programmers, > > suppose we have an Enhanced ER diagram, > with entities as classes/ subclasses connected through > some IS-A relationship. > > How can this be Coded in MySQL Please? > > My prerequisites are the basic database texts from > > http://www-db.stanford.edu/~ullman/dscb.html > > http://www.aw-bc.com/info/database/elmasri.html My copy of elmasri has a section "EER-to_Relational Mapping," which I followed when I wanted to do the same thing. It lists 4 methods for modelling EER class/subclass relationships in terms of the relational database model. For me, I did it as follows. Suppose A and B are subclasses of C. Then for C, I had an INT for primary key, plus an ENUM to describe whether the object was in A or B (here, the enum might have two values, 'a' and 'b'). The primary key of A was the same as that of C; similarly for B. The pk of A should satisfy a foreign key constraint with regard to C (similarly for B). If an object cannot belong to both A and B, one has to also check that no pk in C belongs to both A and B. That's not hard to do, but it's not clear to me that it falls under one of the standard integrity issues in the relational model. HTH, sjfromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to ver 4.0
We are currently running production on ver 3.23. We have two db servers that are in need of hardware upgrade. DB1 replicates to DB2. I plan on taking DB2 offline, upgrading RAM and Processors, installing latest RH OS and MySQL 4.0. Then replace DB1 with the upgraded DB2 making it the new DB1 and then repeating the process with the old DB1 making it the new DB2. Here are my questions. Any known problmes with RH9.0 and MySQL 4.0? Our current 3.23 db uses MyISAM tables. I've read the "how to" on upgrading from 3.23 to 4.0 but I'm not really upgrading as much as "moving" the database to another server that just happens to be ver 4.0 instead of 3.23. If I do a On new server: # mysql create dbname On old server: # mysqldump dbname | mysql -h newserver dbname Will this take care of any table changes or will this cause problems? If I do things this way will I still need to run the mysql_fix_privilege_tables, mysql_convert_table_format etc? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Text fields
On Thursday, August 28, 2003, at 07:09 AM, Sreesekhar Palaparthy wrote: I wud like to rephrase my problemactually i want some method to insert large text/binary text values into BLOB fields in chunks , instead of inserting the values at once in entireity. So for that , i want to know how do i append text to a field. (I can tune server parameter max_allow_packet , but that's not a good option) update mytable set textfield = textfield + newchunk where pk = [ your pk ] ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL API C & leaks
Hello everyone,
I use Valgrind to check my programs and this last found leaks in
libmysqlclient.so.
My code is simple:
char * requete = "SELECT * FROM "NOM_TABLE_1;
MYSQL * mysql = mysql_init((MYSQL *)NULL);
MYSQL_RES * mysql_resultat;
MYSQL_ROW mysql_ligne;
if(mysql == NULL)
{
return(-1);
}
if (!mysql_real_connect(mysql, ADRESSE_BDD, LOGIN_BDD, PASSWORD_BDD, NOM_BDD, 0,
NULL, 0))
{
fprintf(stderr, "Impossible de se connecter au serveur %s\n Error=%s\n",
ADRESSE_BDD, mysql_error(mysql));
mysql_close(mysql);
return(-1);
}
if(mysql_query(mysql, requete) != 0)
{
fprintf(stderr, "Impossible d'executer la requête %s\nError=%s\n", requete,
mysql_error(mysql));
}
else
{
mysql_resultat = mysql_store_result(mysql);
if(mysql_resultat != NULL)
{
for(i = 0; i < mysql_resultat->row_count; i++)
{
mysql_ligne = mysql_fetch_row(mysql_resultat);
// traitement
}
}
mysql_free_result(mysql_resultat);
}
mysql_close(mysql);
Result of Valgrind:
==3691== 64 bytes in 1 blocks are still reachable in loss record 1 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x40254A2D: my_malloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025A218: init_dynamic_array (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AE96: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
==3691==
==3691== 4088 bytes in 1 blocks are still reachable in loss record 2 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x4025A64C: my_once_alloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025ACB0: read_charset_index (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AEAB: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
Is it a BUG or did I something?
Best regards,
--
Andreï V. FOMITCHEV [Quand faut-il arrêter l'informatique]
Software R&D Engineer [Lorsque, dans un kilo, on trouve 1024 grammes]
Odixion SAS, FRANCE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on data structures
* Pascal Francq
> I have a question. I need to store documents (>100.000) and the word
> containing in them.
>
> They are two way to do the job :
> 1°) The documents are stored in a table 'docs' and identified by
> an 'docid'. The words are then stored in a table 'docsbywords'
> where each pair (docid, word) is stored.
> 2°) The documents are stored in a table 'docs' and identified by
> an 'docid'. But, for each document a table is created 'doc1bywords',
> 'doc2bywords', ... that contains only the words of a given document.
>
> In the first solution, two tables with one of them can be very
> large. For the second solution, many tables but with no large sizes.
> Is one of the solutions better than another with regards to the way
> MySQL handles tables and row (in fact, for practical reasons, I
> prefer the second solution).
3) The documents are stored in a table 'docs' and identified by an 'docid'.
The words are stored in a table 'words', one row for each unique word,
identified by 'wordid'. A third table 'worddoc' contains the columns 'docid'
and 'wordid', and two unique compound indexes are defined, one on
('wordid','docid') and one ('docid','wordid')
There will be many rows in 'worddoc', but each row will be small.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
Antony, On Thu, Aug 28, 2003 at 06:45:27PM +0400, Antony Dovgal wrote: > On Thu, 28 Aug 2003 16:37:41 +0200 > Fred van Engen <[EMAIL PROTECTED]> wrote: > > > Please elaborate. > > I've already answered: > On the list I just saw your 'bad advice' message without explanation. > On Thu, 28 Aug 2003 17:07:19 +0400 > Antony Dovgal <[EMAIL PROTECTED]> wrote: > > > You don't need execute UPDATE's in the loop in this case. > Correct. > and you don't need to make another one connection, of course. > In the general case you may need to, but not in the usual case in which you use mysql_query. I thought you did, but just double-checked. You only need to if you use mysql_unbuffered_query and don't read all results first. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Possible: Update query within another query's loop?
Yes, that advice worked perfectly. Now I understand. This is one very nice data storage system. It's fast, and it can deal with multiple pointers - separate sessions - into the same table without conflict. I guess it's up to the programmer to make certain that one connection doesn't change data the other is dependent on :) Thanks for the great idea. -m- -Original Message- From: Antony Dovgal [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 10:30 To: [EMAIL PROTECTED] Subject: Re: Possible: Update query within another query's loop? On Thu, 28 Aug 2003 16:23:35 +0200 Fred van Engen <[EMAIL PROTECTED]> wrote: > You need to make a separate connection to MySQL for the outer query to > prevent the inner query from messing up the outer query's result set. > See the PHP manual for obtaining the connection id's from mysql_connect > and using them with other mysql functions. ouch! it's a kinda bad advice. --- WBR, Antony Dovgal aka tony2001 [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: Possible: Update query within another query's loop?
On Thu, 28 Aug 2003 16:37:41 +0200
Fred van Engen <[EMAIL PROTECTED]> wrote:
> Please elaborate.
I've already answered:
On Thu, 28 Aug 2003 17:07:19 +0400
Antony Dovgal <[EMAIL PROTECTED]> wrote:
> You don't need execute UPDATE's in the loop in this case.
> Try smthing like that:
> .
>
> $ids = Array();
> while ($row = mysql_fetch_assoc($result1))
> {
> $ids[] = $row["id"];
> }
>
> $ids_string = implode(',',$ids);
>
> //of course, we need to check if $id_string is not empty
>
> $q2 ="UPDATE table SET review = 1 WHERE id IN (".$ids_string.")";
>
> .
> ?>
there is no need to use nested loop at all.
just collect id's and execute 1(one) query, which will update all records.
and you don't need to make another one connection, of course.
---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text searching
"Rob" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm having a bit of trouble with a full text search in mysql 3.23.25. I
> have the following table with the following entries:
>
> | id | document_id |document_text
>
> =
> | 19 | 1 | Konekta
> | 20 | 2 | Cuentas actualizadas|
> | 21 | 3 | dot contrato servicios Contrato sms
>
> There is a full text index on the document_text column.
>
> When I run the following sql query
>
> SELECT *, MATCH(document_text) AGAINST ('servicios') AS score
> from document_text
>
> I get back a score of zero for all entries.
>
> What am I doing wrong?
Worked like a charm for me.
mysql> SELECT *, MATCH(body) AGAINST ('servicios') AS score from ft;
++-+--+
| id | body| score|
++-+--+
| 1 | Konekta |0 |
| 2 | Cuentas actualizadas| |0 |
| 3 | dot contrato servicios Contrato sms | 0.50317577871617 |
++-+--+
--
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]
Re: Possible: Update query within another query's loop?
Antony, On Thu, Aug 28, 2003 at 06:29:54PM +0400, Antony Dovgal wrote: > On Thu, 28 Aug 2003 16:23:35 +0200 > Fred van Engen <[EMAIL PROTECTED]> wrote: > > > You need to make a separate connection to MySQL for the outer query to > > prevent the inner query from messing up the outer query's result set. > > See the PHP manual for obtaining the connection id's from mysql_connect > > and using them with other mysql functions. > > ouch! > it's a kinda bad advice. > Please elaborate. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to code an 'IS - A' relationship ?
Hello MySQL programmers, suppose we have an Enhanced ER diagram, with entities as classes/ subclasses connected through some IS-A relationship. How can this be Coded in MySQL Please? My prerequisites are the basic database texts from http://www-db.stanford.edu/~ullman/dscb.html http://www.aw-bc.com/info/database/elmasri.html Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
On Thu, 28 Aug 2003 16:23:35 +0200 Fred van Engen <[EMAIL PROTECTED]> wrote: > You need to make a separate connection to MySQL for the outer query to > prevent the inner query from messing up the outer query's result set. > See the PHP manual for obtaining the connection id's from mysql_connect > and using them with other mysql functions. ouch! it's a kinda bad advice. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
Mark,
On Thu, Aug 28, 2003 at 09:59:18AM -0400, Mark Richards wrote:
> I am still quite new to MySQL and have a basic question. I am using PHP, so
> forgive me if this is more a PHP issue.
>
> I want to perform an update to a specific record based on a condition. In
> the outer loop, I have Query1, which returns set Result1. Inside this loop,
> I run an UPDATE query which returns Result2.
>
> // executed first query.
> while ($row = mysql_fetch_assoc($result1))
> {
> // get the record ID for the row we are on.
> $recid = $row["id"];
> // construct a new query
> $q2 ="UPDATE `table` SET `review` = 1 where id =
> '".$recid."';";
> $result2 = mysql_query($q2)
> }
>
> Is this a valid use of MySQL? I am concerned that executing the update
> query might somehow effect the first one that is used in the outer loop.
>
You need to make a separate connection to MySQL for the outer query to
prevent the inner query from messing up the outer query's result set.
See the PHP manual for obtaining the connection id's from mysql_connect
and using them with other mysql functions.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
63 LSB ok but bit 64 is sign bit
mysql> select -> 0x+0 AS '18446744073423220462', -> 0x7fff+0 AS '9223372036568444654 ', -> 0x8fff+0 AS '10376293541175291630' ; +--+--+--+ | 18446744073423220462 | 9223372036568444654 | 10376293541175291630 | +--+--+--+ | -286331154 | 9223372036568444654 | -8070450532534259986 | +--+--+--+ 1 row in set (0.00 sec) Hi, on my operating system Mysql offers this output, is it correct ? I think not. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible: Update query within another query's loop?
I am still quite new to MySQL and have a basic question. I am using PHP, so
forgive me if this is more a PHP issue.
I want to perform an update to a specific record based on a condition. In
the outer loop, I have Query1, which returns set Result1. Inside this loop,
I run an UPDATE query which returns Result2.
// executed first query.
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row["id"];
// construct a new query
$q2 ="UPDATE `table` SET `review` = 1 where id =
'".$recid."';";
$result2 = mysql_query($q2)
}
Is this a valid use of MySQL? I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.
Mark Richards
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ERROR in LOAD DATA
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > I am currently using MySQL 4.0.14-standard along with PHP 4.3.2 in Apache > 1.3.28. > > I have written following code to upload some data from the hosting > directory to database table. But LOAD DATA statement always fails. I have > checked the same SQL using Sql-Front. It is showing Access denied. But > table is creating, data is not loading using same connection. > User must have FILE privilege. -- 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]
Question on data structures
Hi, I have a question. I need to store documents (>100.000) and the word containing in them. They are two way to do the job : 1°) The documents are stored in a table 'docs' and identified by an 'docid'. The words are then stored in a table 'docsbywords' where each pair (docid, word) is stored. 2°) The documents are stored in a table 'docs' and identified by an 'docid'. But, for each document a table is created 'doc1bywords', 'doc2bywords', ... that contains only the words of a given document. In the first solution, two tables with one of them can be very large. For the second solution, many tables but with no large sizes. Is one of the solutions better than another with regards to the way MySQL handles tables and row (in fact, for practical reasons, I prefer the second solution). Thanks. -- Dr Ir Pascal Francq Researcher Université Libre de Bruxelles CAD/CAM Department Avenue F.D. Roosevelt, 50 CP 165/14 B-1050 Brussels BELGIUM Tel. +32-2-650 47 65 Fax +32-2-650 47 24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
On Thu, 28 Aug 2003 08:54:54 -0400
"Mark Richards" <[EMAIL PROTECTED]> wrote:
> I am still quite new to MySQL and have a basic question. I am using PHP, so
> forgive me if this is more a PHP issue.
>
> I want to perform an update to a specific record based on a condition. In
> the outer loop, I have Query1, which returns set Result1. Inside this loop,
> I run an UPDATE query which returns Result2.
>
> // executed first query...
> while ($row = mysql_fetch_assoc($result1))
> {
> // get the record ID for the row we are on.
> $recid = $row["id"];
> // construct a new query
> $q2 ="UPDATE `table` SET `review` = 1 where id =
> '".$recid."';";
> $result2 = mysql_query($q2)
> }
You don't need execute UPDATE's in the loop in this case.
Try smthing like that:
--
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible: Update query within another query's loop?
I am still quite new to MySQL and have a basic question. I am using PHP, so
forgive me if this is more a PHP issue.
I want to perform an update to a specific record based on a condition. In
the outer loop, I have Query1, which returns set Result1. Inside this loop,
I run an UPDATE query which returns Result2.
// executed first query...
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row["id"];
// construct a new query
$q2 ="UPDATE `table` SET `review` = 1 where id =
'".$recid."';";
$result2 = mysql_query($q2)
}
Is this a valid use of MySQL? I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.
Mark Richards
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ERROR in LOAD DATA
Hi,
I am currently using MySQL 4.0.14-standard along with PHP 4.3.2 in Apache
1.3.28.
I have written following code to upload some data from the hosting
directory to database table. But LOAD DATA statement always fails. I have
checked the same SQL using Sql-Front. It is showing Access denied. But
table is creating, data is not loading using same connection.
Please help me by telling what has happened wrong.
My code :
";
echo "";
$dbname = "biplab";
$dbserver = "localhost";
$dbuser = "sumit";
$dbpassword = "xx329x";
$connect=mysql_connect($dbserver,$dbuser,$dbpassword)
or die("could not connect to MySQL server in localhost !");
$database=mysql_select_db($dbname) or die("could not select database");
$tablename="AIS_COURSES";
$query="DROP TABLE $tablename";
$result=mysql_query($query);
$query="CREATE TABLE $tablename
(CRS_CODE VARCHAR(10) NOT NULL
,CRS_NAME VARCHAR(80) NOT NULL
)";
$result=mysql_query($query) or die("Create Table $tablename failed");
$query="Load Data Infile './BUWebCourses.txt'
INTO TABLE $tablename Fields Terminated By '|'
(CRS_CODE,CRS_NAME)" ;
echo $query; //just to check
$result=mysql_query($query) or die("Load Table $tablename failed");
echo "Table $tablename has been updated
successfully.";
mysql_close();
echo "";
echo "";
?>
Thanks in advance
Sumit
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dayname() function
Dear MySQL users, Is it possible to configure MySQL in such a way that the function dayname() will return the name of the day my local language. (In my case polish). thank you very much for help, regards, Lukasz
Re: RE: Text fields
I wud like to rephrase my problemactually i want some method to insert large text/binary text values into BLOB fields in chunks , instead of inserting the values at once in entireity. So for that , i want to know how do i append text to a field. (I can tune server parameter max_allow_packet , but that's not a good option) On Thu, 28 Aug 2003 Rob wrote : For arbitrary sizes of text, try the MEDIUMTEXT or LONGTEXT column types. Data is then stored as a BLOB. Check out the docs at http://www.mysql.com/doc/en/BLOB.html -Original Message- From: Sreesekhar Palaparthy [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 9:28 AM To: [EMAIL PROTECTED] Subject: Text fields Hi, How do we insert arbitary sized texts into MYSQL Text fields?? Is there any way to split the text into chunks and then append those chunks to the field??? ___ Art meets Army ; Swapna Weds Capt. Rajsekhar. Rediff Matchmaker strikes another interesting match !! Visit http://matchmaker.rediff.com?2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Art meets Army ; Swapna Weds Capt. Rajsekhar. Rediff Matchmaker strikes another interesting match !! Visit http://matchmaker.rediff.com?2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text searching
Hi,
I'm having a bit of trouble with a full text search in mysql 3.23.25. I
have the following table with the following entries:
| id | document_id |document_text
=
| 19 | 1 | Konekta
| 20 | 2 | Cuentas actualizadas|
| 21 | 3 | dot contrato servicios Contrato sms
There is a full text index on the document_text column.
When I run the following sql query
SELECT *, MATCH(document_text) AGAINST ('servicios') AS score
from document_text
I get back a score of zero for all entries.
What am I doing wrong?
Thanks
---
Rob
**
Rob Cherry
mailto:[EMAIL PROTECTED]
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: large mysql processes
Welcome to the world of Linux! You see, even though I love Linux to death, LinuxThreads are a bit inferior to what everyone else has in their OS of choice (If Xavier reads this, please don't kill me or submit some kernel patch that will get even with me. :-) ). LinuxThreads essentially are processes - you get the advantage of threads (shared address space and intra-process concurrency) but each thread appears as a process. RedHat 9.0 ships with NPTL - a newer thread implementation that "acts properly". Regards, Chris P.S - Be thankful you're running Linux - at least we have kernel-space threads. The poor people forced to run SCO's inferior products quite often have to put up with user-space threads. sanjay gupta wrote: Hi all, I am using Mysql-4.0.12 on my 7.1 redhat. every thing is working fine but when i give command #ps -ae it shows various process including mysqld_safe and mysql . the mysqld process are eleven in number with respective pids on my machine . I want to know why there are large number of mysql processes are running becasue it should show only single mysqld process. any idea sanjay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Text fields
For arbitrary sizes of text, try the MEDIUMTEXT or LONGTEXT column types. Data is then stored as a BLOB. Check out the docs at http://www.mysql.com/doc/en/BLOB.html -Original Message- From: Sreesekhar Palaparthy [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 9:28 AM To: [EMAIL PROTECTED] Subject: Text fields Hi, How do we insert arbitary sized texts into MYSQL Text fields?? Is there any way to split the text into chunks and then append those chunks to the field??? ___ Art meets Army ; Swapna Weds Capt. Rajsekhar. Rediff Matchmaker strikes another interesting match !! Visit http://matchmaker.rediff.com?2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Script for Database[MySQL]::Web interfacing
Joseph Maxwell wrote: Hello, Could someone recommend a decent php script that could be used for database[MySQL]::Web interfacing and management. Preferably open source, freeware or otherwise [I'll just have to grin & bare it] phpMyAdmin -> www.phpmyadmin.net -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
large mysql processes
Hi all, I am using Mysql-4.0.12 on my 7.1 redhat. every thing is working fine but when i give command #ps -ae it shows various process including mysqld_safe and mysql . the mysqld process are eleven in number with respective pids on my machine . I want to know why there are large number of mysql processes are running becasue it should show only single mysqld process. any idea sanjay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Text fields
Hi, How do we insert arbitary sized texts into MYSQL Text fields?? Is there any way to split the text into chunks and then append those chunks to the field??? ___ Art meets Army ; Swapna Weds Capt. Rajsekhar. Rediff Matchmaker strikes another interesting match !! Visit http://matchmaker.rediff.com?2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ingres vs MySQL
> I'm working currently on the project where we need > to make a decision what engine would be our main > production database engine. > > Could you please help me and shed a light on what > pros and cons MySQL vs Ingres: features, performance, > support, portability among Wintel and Unix based > machines (FreeBSD, Linux, NetBSD). > > I like very much MySQL, but could not find any good > resource or information about Ingres, even from maker. > > Any help and suggestions highly appreciated. I plugged "ingres" in at google.com and came up with the following: http://www3.ca.com/Solutions/Product.asp?ID=1013 Note that's Computer Associates you would be dealing with on that one. Mixed in with the links to information about the artist, Jean Auguste Dominique Ingres, there were other links to potential sources of information. HTH -- Joel Rees, programmer, Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
