Re: DB Performance - Celeron vs. P4
Jonathan Hilgeman wrote: Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? It's going to depend of in your queries are disk bound or processor bound. Check the processor usage when a big query is running (and not much else is running). If the CPU usage is at 80-100% the query is processor bound, so you'll see some improvement (though I can't tell you how much, other than the clock rate scaling). On the other hand, if the CPU usage is low, you are likely disk bound, so a faster processor won't help much. It would be better to add RAM, tune your queries or get faster disks (or all three) in this case. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing autoincrement
On Tuesday, August 12, 2003, at 09:59 PM, otherguy wrote: On Tuesday, August 12, 2003, at 09:46 PM, Andrew Rothwell wrote: Hello List, I have a movies database, that I had an autoincrementing field for counting purposes.What I did though was remove some of the rows out of the table, now my table is reporting an incorrect number of movies listed.What I am trying to do is after is have done the following command [snip] There is no 74 Is there a way to force the DB upon removal of a row (74) to renumber the autoincremented fields? Short answer, no. I have tried to flush tables, but that did not work - btw I am using the last stable 3 release - but I will be updating to the latest stable 4 release in the next day or so. THank you Andrew Long answer: that defeats the purpose of an auto-increment. The only good way to do this is to do it programatically, and even then (if there are multiple updates at a time) won't necessarily guarantee unique values (the point of the auto_increment field). I should have said something like programatically with an integer field (as oppose to auto_increment). If all you need is the current number of movies, might I suggest: SELECT COUNT(*) FROM tablename; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dynamic enabling of log files
hi all! can we enable and disable log files (update log, slow query log) dynamically. Please let me know. what should i do to enable it without restarting the server. I am using MySQL 2.23.52 advance thanx! Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT .... SELECT
Then you need to be even more explicit INSERT INTO nye_opskrifter (foo,bar) SELECT foo, bar FROM opskrifter where id in($numbers) -Original Message- From: Lars Rasmussen [mailto:[EMAIL PROTECTED] Sent: 13 August 2003 19:22 To: 'Jay Blanchard'; [EMAIL PROTECTED] Subject: INSERT SELECT I tried that, but i dont work either. I need to insert a way that mysql doese'nt complain when i copy some records that have the same id (or that it just gives it an id according to the AUTO_INCREMENT) Thanks again //Lars Rasmussen -Oprindelig meddelelse- Fra: Jay Blanchard [mailto:[EMAIL PROTECTED] Sendt: 13. august 2003 19:59 Til: Lars Rasmussen; [EMAIL PROTECTED] Emne: RE: INSERT SELECT [snip] I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 [/snip] * does not return a specific number of columns, the work around is to specify the columns explicitly INSERT INTO nye_opskrifter SELECT foo, bar FROM opskrifter where id in($numbers) -- 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]
Does mysql cache results?
Hi, I'm doing some web development and have mysql (3.23.x) installed to test with (win2k, but I don't think this question is particularly platform specific). I have a reasonably complex query which I am trying to optimise. When I first startup mysql, and perform the query, it can take 10 or so seconds. However, further subsequent queries (sorting by the same column) return in a fraction of a second. This is true even with new WHERE or LIMIT clauses. Is mysql creating a 'temporary' index and caching it? Is the original 10 second query the actual time for my query to execute? I'm finding it difficuly to optimise my query as I'm not sure exactly which times are correct. Hope I've explained this OK - sure I'm missing fundamental, so apologies in advance. Regards, Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
The comma after Gerlad R was a typo. As for the 500 actors issue, I'm merely using the movie db as an example. People always post the strangest db's to this list (I have one table for monkeys and one for fishes who speak Urdu, and I need...), so I thought I'd spare everyone the particulars of mine. I'm quite eager to use some of the 4.0/4.1 features, but for now, I have to play in the 3.23 sandbox. Ultimately, I'd like single rows here, but will use PHP to compact the rows for now. Thanks for your help everyone! Patrick I can't say I like the idea of concatenating all those items into one. Look at the parsing difficulty you've gotten yourself into. Is Molen a director? Or is it Gerald R? Most databases don't have any functions for this grouping-concatenation, since it leaves you with a result set that has a field of arbitrary length. What about a movie with 500 actors? So, really, I prefer to order by the movie, then the director. While processing the result set, just be aware you might have duplicate consecutive movies. Then you can roll in the directors as you go. So much for that lecture. If you're willing to use the ALPHA mySQL 4.1, there is a method that promises to do what you want: http://www.mysql.com/doc/en/GROUP-BY-Functions.html, look at GROUP_CONCAT. I think that fits the bill, with the caveat that it might well not be ready for prime-time yet. Oh, and it won't work on (any) other databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The SET field type vs. a lookup table
Does the FIND_IN_SET() function mentioned in the manual have any performance bonuses over doing a LIKE search for a SET fields? I'm trying to decide between using a SET or a separate lookup table at the moment, but data needs to be searchable and could have a lot of values, so it needs to be scalable. The FIND_IN_SET() function is mentioned on the page about the SET field type, but doesn't seem to have a proper manual entry itself. -- Tim Fountain | Web developer | Incutio Limited | www.incutio.com email: [EMAIL PROTECTED] | Tel: +44 8708 700 333 | Fax: +44 7092 181 581 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rollback segment
Susan, you had posted your message to the newsgroup mailing.database.mysql. That is only a mirror of the mailing list [EMAIL PROTECTED], where you should send your postings for them to be readable for all. InnoDB has a data structure which is equivalent to the 'rollback segment' of Oracle. But, contrary to Oracle, you do not need to configure it in any way. InnoDB uses the rollback segment both to implement transaction rollback and read consistency. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL - Original Message - From: Susan Lam [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, August 11, 2003 11:52 AM Subject: rollback segment I am new to mysql. I am just wondering does mysql has rollback segment? If there is, does it use for rollback only or also for read consistency? If not, how do mysql maintain read consistency? Thanks, Susan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext searching
* m n I have the following table with just one record! And when I do a select statement, mysql returns no hits!!! Would you explain to me what is wrong?? You have only one row. URL: http://www.mysql.com/doc/en/Fulltext_Search.html For very small tables, word distribution does not reflect adequately their semantic value, and this model may sometimes produce bizarre results. More specifically, any word found in more than 50% of the rows are considered as a 'stopword', i.e. it does not give a hit. All you words are in 100% of your rows... ;) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-12
Hello All, Redhat 9.0 Mysql 3.23.56 == Running I want to upgarde to 4.0.13 but this is the error it says: [EMAIL PROTECTED] downloads]# rpm -Uvh MySQL-server-4.0.13-0.i386.rpm warning: MySQL-server-4.0.13-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-12 libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3 libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2 If i install with -i will it install with the backward compatibility, it should not break those dependencies. Any comments ? Thanks in Advance! Tina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: mutual declarations produce Error 1005 (errno: 150)
mysql USE company; Database changed mysql mysql DROP TABLE IF EXISTS EMPLOYEE; -- DROP TABLE IF EXISTS EMPLOYEE -- Query OK, 0 rows affected (0.00 sec) mysql mysql CREATE TABLE EMPLOYEE - ( - FNAMEVARCHAR(15) NOT NULL, - MINITCHAR, - LNAMEVARCHAR(15) NOT NULL, - SSN CHAR(9) NOT NULL, - BDATEDATE, - ADDRESS VARCHAR(30), - SEX CHAR, - SALARY DECIMAL(10,2), - SUPERSSN CHAR(9), - DNO INT NOT NULL DEFAULT 1, - PRIMARY KEY (SSN), - INDEX (SUPERSSN), - INDEX (DNO) - )TYPE = INNODB; -- CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB -- Query OK, 0 rows affected (0.00 sec) mysql mysql DESCRIBE EMPLOYEE; -- DESCRIBE EMPLOYEE -- +--+---+---+--+-+-+- --+ | Field| Type | Collation | Null | Key | Default | Extra | +--+---+---+--+-+-+- --+ | FNAME| varchar(15) | latin1_swedish_ci | | | | | | MINIT| char(1) | latin1_swedish_ci | YES | | NULL| | | LNAME| varchar(15) | latin1_swedish_ci | | | | | | SSN | varchar(9)| latin1_swedish_ci | | PRI | | | | BDATE| date | latin1_swedish_ci | YES | | NULL| | | ADDRESS | varchar(30) | latin1_swedish_ci | YES | | NULL| | | SEX | char(1) | latin1_swedish_ci | YES | | NULL| | | SALARY | decimal(10,2) | binary| YES | | NULL| | | SUPERSSN | varchar(9)| latin1_swedish_ci | YES | MUL | NULL| | | DNO | int(11) | binary| | MUL | 1 | | +--+---+---+--+-+-+- --+ 10 rows in set (0.00 sec) mysql SHOW INNODB STATUS \G -- SHOW INNODB STATUS -- *** 1. row *** Status: = 030814 10:37:13 INNODB MONITOR OUTPUT = Per second averages calculated from the last 52 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 32, signal count 32 Mutex spin waits 10, rounds 180, OS waits 1 RW-shared spins 60, OS waits 30; RW-excl spins 1, OS waits 1 TRANSACTIONS Trx id counter 0 5422 Purge done for trx's n:o 0 5408 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 5415, not started, OS thread id 2072 MySQL thread id 8, query id 1088 localhost 127.0.0.1 root SHOW INNODB STATUS FILE I/O I/O thread 0 state: wait Windows aio I/O thread 1 state: wait Windows aio I/O thread 2 state: wait Windows aio I/O thread 3 state: wait Windows aio 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 62 OS file reads, 759 OS file writes, 245 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.04 writes/s, 0.04 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 1 buffer(s) 0.00 hash searches/s, 0.87 non-hash searches/s --- LOG --- Log sequence number 0 880300 Log flushed up to 0 880300 Last checkpoint at 0 873305 0 pending log writes, 0 pending chkp writes 136 log i/o's done, 0.04 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 13601916; in additional pool allocated 232832 Buffer pool size 512 Free buffers 480 Database pages 31 Modified db pages 22 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 28, created 3, written 584 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 1408, state: sleeping 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 1 row in set (0.00 sec) mysql DROP TABLE IF EXISTS DEPARTMENT; -- DROP TABLE IF EXISTS DEPARTMENT --
Re: Accessing Mysql after fresh installation
Hi Mathan. i am totally confused . Actually same problem occurred when install the mysql 2-3 months back. but then i solved the problem , how , i didnt know . but this time when i again start the mysql and did the installation the same problem is unable to resolve after all tries. i would like to mention to Mr. Mathan that i have not set any password after installation. the message appered after installation that you must set the password using /usr/bin/mysqladmin -u root -p password 'new password' /usr/bin/mysqladmin -u root -h localhost.localdomain -p password 'new password' but i have not set the password. there should be default password so that i may be adble to login to mysql . i have followed the the procedure that you have given below , still the same problem occures still hanging sanjay - Original Message - From: mathan [EMAIL PROTECTED] To: sanjay gupta [EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 5:20 PM Subject: Re: Accessing Mysql after fresh installation When i type the command # mysqladmin -p password '' Enter password: # it asks for password. I don't remember the password any more . So it gives the same message Access denied for user [EMAIL PROTECTED] using password np what to do Ok, If you dont have any important data, then remove your data directory, and do the following steps: To find the data directory run the command, the ldata will contains the data directory. # cat mysql_install_db | grep ldata= test -z $ldata ldata=path to data dir for eg assume data dir is /var/opt/mysql, then run the following commands # rm -rf /var/opt/mysql # groupadd mysql # mysql_install_db # chown -R mysql /var/opt/mysql # chmod 700 /var/opt/mysql # safe_mysqld [1] 29828 Starting mysqld daemon with databases from /var/opt/mysql # mysqladmin create testdb # mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.23.54-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql connect testdb; then play with mysql. Let me know if you need any clarifications. thanks --mathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
v4 user privileges
Can someone tell me why the columns ssl_cipher, x509_issuer, x509_subject in the user table are REQUIRED? If they are required what is the default value? I am having a heck of a time working with the mysql gui's and adding records to this table Thanks --- Keith Schuster Schuster Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 12:16:40PM -0400, walt wrote: On Wednesday 06 August 2003 11:50 am, Andy Smith wrote: $ cat mysql/master.info angora-bin.001 20102800 127.0.0.1 repl removed 3306 60 Looks fine to me. :( Looks fine to me as well Are you still getting the 1200 error when you try SLAVE START ? Yes, afraid so.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
uhhh.there are only two fields in the table, but I'll give it a whirl. :) As for Donald's advice: it gave me access denied for user. :\ -Mike -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:08 PM To: Mike At Spy; Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE [snip] 70050;451 70322;451 LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] [/snip] I am going to recommend that you specify which columns the data goes into LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one`(foo, bar) FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: created new user but can't log in as that user
Bennett Haselton [EMAIL PROTECTED] wrote: While logged in to my Linux server as root, I went in to MySQL and (with no databases selected, so that the GRANT statement would apply globally), ran the command: mysql grant all privileges on * to bhaselto identified by 'password'; where password is, of course, the password I wanted to use for the user 'bhaselto'. I can see an entry for that user in the 'user' table in the 'mysql' database: ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ [...] | % | bhaselto | alphanumerics | Y | Y | Y | Y | Y | Y | N | N | N| N | N | Y | Y | Y | where alphanumerics is an alphanumeric code that presumably represents the hash of the password that I entered. However, if I exit mysql and try logging in with the bhaselto username, it doesn't let me: [EMAIL PROTECTED] bhaselto]$ mysql -u bhaselto -p Enter password: [Here I type the password that I created above] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) [EMAIL PROTECTED] bhaselto]$ As far as I can tell from reading http://www.mysql.com/doc/en/GRANT.html I followed the GRANT syntax correctly for creating a new user; why can't I connect to MySQL as that user? Remove from table user entry for ''@'localhost' and then execute FLUSH PRIVILEGES. -- 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]
Join syntax diff 3.23 to 4.x?
Is there something about 3.23 that makes this illegal? It is fine on 4.012. SELECT [lots of columns] FROM new_people p LEFT JOIN new_address ad ON p.address_id = ad.id JOIN list_states ls ON ad.state = ls.id WHERE p.company_id = 1 Will some minor rewording help it run on both versions OK? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi don't starts groups on Linux RedHat 9
Hello, mysqld_multi doesn't start the two groups if I gave this command imediately after stopping them. It will start only one group. I'm working on Linux RedHat 9 MySQL 4.0.14. How can I solve this problem, please? Thanks Anticipated, Iulian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MD5-read permission?
Hey folks - While contemplating the design of a secure web database, an idea struck me. I'm thinking of submitting it as a feature request, so please critique it. I'm having php handle user logon with it's .htaccess emulation. I'm storing usernames and password hashes in a table. The problem is that php needs to open MySQL with *some user* with *some permission*, just to read the user table and check the password. So, It seems that I have to store the password plaintext somewhere in some php file. (I asked the list about this earlier and several others had great suggestions on how to hide this plain-text password -- Thanks Rob! -- but, can we make it better?) So if some wily hacker were to get the contents of this php file, s/he would get a username and password for the database. Now of course, I'm only going to give this user permission to read the user database, and all the passwords are hashed... but : I propose a new permission that I will call MD5read. It's like select, only it just returns hashes. So, say you do something like: SELECT password FROM user; 49726b60ccbf03d6c619632e1db6 f8ec2c9d79b5f969a96be968e7152bbd SELECT username, password FROM user; 24424b444b80831b677594a238f81dd9 | 4549625d8275b97b9b4f9662f1c550fa 1e5143d05b327f7d3cce15f9e3e44ad2 | fe3b4b388a69ceed38d6a0066e6a221b SELECT username+password+somethingelse FROM user; 49726b60ccbf03d6c619632e1db6 f8ec2c9d79b5f969a96be968e7152bbd So that way, if someone gets the username/password for this user, they can't get any data off of the database. One thing you have to watch is that you don't use the md5 function for a user that has only md5read permission, because that would double-hash it, and whatever you're checking would fail. I know you can do SELECT md5(username), md5(password) FROM user (or whatever the syntax is), but the user doing that has to have read permission already. So if a hacker gets that username and password, they are probably not going to hash data they are trying to get out of the database. I would feel safe storing a user's name and password in a plain text php script if they had only this permission. Is this useful? Are there any flaws in my reasoning? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT .... SELECT
Hi, I have some trouble since i've upgradet from 3.23.49 to 4.0.13. I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 I tried IGNORE, but it's just not working. I hope anyone of you got a workaround for this problem, i did'nt find any in the manual. //Lars Rasmussen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does mysql cache results?
I don't know if mySQL caches results, but your system almost surely caches disk sectors read. I've noticed in another database app that I'm working on (not mySQL) that executing the same query twice runs noticably faster the second time, and I know the DBMS isn't caching results because I'm using a lower-level interface so I'm implementing the query myself. Russ writes: Hi, I'm doing some web development and have mysql (3.23.x) installed to test with (win2k, but I don't think this question is particularly platform specific). I have a reasonably complex query which I am trying to optimise. When I first startup mysql, and perform the query, it can take 10 or so seconds. However, further subsequent queries (sorting by the same column) return in a fraction of a second. This is true even with new WHERE or LIMIT clauses. Is mysql creating a 'temporary' index and caching it? Is the original 10 second query the actual time for my query to execute? I'm finding it difficuly to optimise my query as I'm not sure exactly which times are correct. Hope I've explained this OK - sure I'm missing fundamental, so apologies in advance. Regards, Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what are some good options for reiserfs and mysql
I want to get the most out of my Dedicated Red-Hat Linux 6 drive (3 RAID-1 / 3 RAID-10 ) mysql servers using Reiser-fs. Currently I have this in my fstab /dev/sda6 /var/lib/mysql reiserfsdefaults 1 2 I would like to turn off/on some options to get the most out of my disk subsystem Can anyone give me some pointers?
Re: Cant login as a user I thought I created
On Thu, 14 Aug 2003 15:19:46 +0300, Victoria Reznichenko wrote:
Mark Healey [EMAIL PROTECTED] wrote:
I'm trying to learn to use mysql. I don't want to mess with the
system databases so I decided create a database and user to play
with but I can't seem to do it right.
I logged in as root and created a database
mysql create database marksstuff;
Query OK, 1 row affected (0.00 sec)
I then granted priveleges to mark
mysql grant all on marksstuff.* to mark identified by 'password'; not the real
password
Query OK, 0 rows affected (0.01 sec)
I then logged out and tried to login as mark
[EMAIL PROTECTED] mark]$ mysql -u mark -ppassword
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
What am I doing wrong?
Delete anonymous user('') from table user. Don't forget about FLUSH PRIVILEGES.
Thanks, that worked. Why?
Exactly what does 'flush privileges' do anyway? It isn't mentioned in the
O'reilly book.
And now an unrelated question.
Why isn't there a BOOL column type? As I was designing tables in my
head I figured that there would one. So many things in life are
boolean values.
Again, thanks.
Mark Healey
[EMAIL PROTECTED]
This account is only for lists to which I've subscribed.
Any spammers invite the worst revenge I think I can get away with.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
First of all, thank you Jeremy for your answer. You said: It's the amount of time that thread has been in its current state. Is it the current state column value (which is in fact nothing or the text of the running SQL request) or the current command column value (sleeping, opening table, closing table, ...) that is used for the amount of time ? What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? Regards, Marc Mechain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Installation Challenges
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: moyc To: [EMAIL PROTECTED] Subject: Installation challenges - mysqld Description: I've never installed MySql before and I am having a little trouble with the installation procedure. I have installed the binaries as described in the manual for installation and I am able to start mysql through $mysqld_safe --user mysql [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 030812 13:24:04 mysqld ended but you can see that the deamon has ended right away. This should not happen, correct? Then I go to connect to the server (locally) by entering in a mysqladmin command to check the version and the output is below: bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! It does not seem like the deamon is running at all when I go to check (ps -aux | grep mysql). Any ideas. I am a little new to MySql. Note: I have tried the latest version of MySQL and still get the same problems but on a diff computer running the same OS (Linux RH 9.0) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Chris Moy Organization: organization of PR author (multiple lines) MySQL support: [none] Synopsis: synopsis of the problem (one line) Severity: non-critical Priority: medium Category: mysql Class: support Release: mysql-3.23.54 (Source distribution) Environment: Linux Redhat 9.0 System: Linux bioserver 2.4.20-18.9 #1 Thu May 29 07:08:16 EDT 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -g -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -g -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 May 27 07:49 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1549556 Mar 13 19:43 /lib/libc-2.3.2.so -rw-r--r--1 root root 2321376 Mar 13 18:35 /usr/lib/libc.a -rw-r--r--1 root root 204 Mar 13 17:58 /usr/lib/libc.so lrwxrwxrwx1 root root 10 May 27 08:36 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--host=i386-redhat-linux' '--build=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--without-readline' '--without-debug' '--enable-shared' '--with-extra-charsets=complex' '--with-bench' '--localstatedir=/var/lib/mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-mysqld-user=mysql' '--with-extra-charsets=all' '--with-innodb' '--enable-local-infile' '--enable-large-files=yes' '--enable-largefile=yes' '--with-berkeley-db-includes=/usr/include' '--with-berkeley-db-libs=/usr/lib' '--enable-thread-safe-client' 'CFLAGS=-O2 -march=i386 -mcpu=i686 -g -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' 'CXXFLAGS=-O2 -march=i386 -mcpu=i686 -g -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'build_alias=i386-redhat-linux' 'host_alias=i386-redhat-linux' 'target_alias=i386-redhat-linux-gnu' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT .... SELECT
[snip] I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 [/snip] * does not return a specific number of columns, the work around is to specify the columns explicitly INSERT INTO nye_opskrifter SELECT foo, bar FROM opskrifter where id in($numbers) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling Mysql on Aix 4.3
I am hitting a problem compiling mysql on aix 4.3
configure works ok and picks the CC compiler. However the compile fails
as shown in the attachment.
Even following the advise under the IBM - AIX section of the
doccumentation and setting the following
enviromental variables gives the same result. Can anyone assist? Any
help will be much appreciated.
variables
---
export CC=xlc_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192
export CXX=xlC_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192
export CFLAGS=-I /usr/local/include
export LDFLAGS=-L /usr/local/lib
export CPPFLAGS=$CFLAGS
export CXXFLAGS=$CFLAGS
TIA
Making all in pstack
Making all in aout
Target all is up to date.
Target all-am is up to date.
Target all is up to date.
Making all in libmysql
source='libmysql.c' object='libmysql.lo' libtool=yes
depfile='.deps/libmysql.Plo' tmpdepfile='.deps/libmysql.TPlo' depmode=aix /bin/sh
../depcomp /bin/sh ../libtool --mode=compile cc -qlanglvl=ansi
-DDEFAULT_CHARSET_HOME=\/nfs/s2.c1/php/mysql/production\
-DDATADIR=\/nfs/s2.c1/php/mysql/production/var\
-DSHAREDIR=\/nfs/s2.c1/php/mysql/production/share/mysql\ -DUNDEF_THREADS_HACK
-DDONT_USE_RAID
-DMYSQL_CLIENT -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. -O
-DDBUG_OFF -Wa,-many -DUNDEF_HAVE_INITGROUPS -DSIGNALS_DONT_BREAK_READ -c -o
libmysql.lo `test -f libmysql.c || echo './'`libmysql.c
cc -qlanglvl=ansi -DDEFAULT_CHARSET_HOME=\/nfs/s2.c1/php/mysql/production\
-DDATADIR=\/nfs/s2.c1/php/mysql/production/var\
-DSHAREDIR=\/nfs/s2.c1/php/mysql/production/share/mysql\ -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -DMYSQL_CLIENT -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O -DDBUG_OFF -Wa,-many -DUNDEF_HAVE_INITGROUPS -DSIGNALS_DONT_BREAK_READ -c -M
libmysql.c -DPIC -o libmysql.lo
./../include/violite.h, line 41.1: 1506-166 (S) Definition of function Vio requires
parentheses.
./../include/violite.h, line 41.4: 1506-276 (S) Syntax error: possible missing '{'?
./../include/violite.h, line 198.3: 1506-045 (S) Undeclared identifier
SSL_TYPE_NOT_SPECIFIED.
./../include/violite.h, line 199.3: 1506-045 (S) Undeclared identifier SSL_TYPE_NONE.
./../include/violite.h, line 200.3: 1506-045 (S) Undeclared identifier SSL_TYPE_ANY.
./../include/violite.h, line 201.3: 1506-045 (S) Undeclared identifier SSL_TYPE_X509.
./../include/violite.h, line 202.3: 1506-045 (S) Undeclared identifier
SSL_TYPE_SPECIFIED.
./../include/violite.h, line 203.1: 1506-277 (S) Syntax error: possible missing ';'
or ','?
libmysql.c, line 96.51: 1506-277 (S) Syntax error: possible missing ',' or ')'?
libmysql.c, line 96.14: 1506-282 (S) The type of the parameters must be specified in
a prototype.
libmysql.c, line 127.14: 1506-343 (S) Redeclaration of mysql_thread_end differs from
previous declaration on line 115 of libmysql.c.
libmysql.c, line 127.14: 1506-050 (I) Return type void in redeclaration is not
compatible with the previous return type int.
libmysql.c, line 389.3: 1506-045 (S) Undeclared identifier CLIENT_IGNORE_SIGPIPE.
libmysql.c, line 390.7: 1506-022 (S) vio is not a member of struct st_net.
libmysql.c, line 400.22: 1506-045 (S) Undeclared identifier CR_NET_PACKET_TOO_LARGE.
libmysql.c, line 497.27: 1506-099 (S) Unexpected argument.
libmysql.c, line 512.3: 1506-045 (S) Undeclared identifier CLIENT_IGNORE_SIGPIPE.
libmysql.c, line 513.12: 1506-022 (S) vio is not a member of struct st_net.
libmysql.c, line 539.23: 1506-045 (S) Undeclared identifier CR_NET_PACKET_TOO_LARGE.
libmysql.c, line 568.35: 1506-099 (S) Unexpected argument.
libmysql.c, line 569.44: 1506-099 (S) Unexpected argument.
libmysql.c, line 723.12: 1506-022 (S) vio is not a member of struct st_net.
libmysql.c, line 727.5: 1506-045 (S) Undeclared identifier CLIENT_IGNORE_SIGPIPE.
libmysql.c, line 728.21: 1506-022 (S) vio is not a member of struct st_net.
libmysql.c, line 730.10: 1506-022 (S) vio is not a member of struct st_net.
libmysql.c, line 760.38: 1506-099 (S) Unexpected argument.
libmysql.c, line 786.40: 1506-196 (E) Initialization between types char** and
const char** is not allowed.
libmysql.c, line 908.19: 1506-022 (S) charset_dir is not a member of struct
st_mysql_options.
libmysql.c, line 909.11: 1506-022 (S) charset_dir is not a member of struct
st_mysql_options.
libmysql.c, line 912.19: 1506-022 (S) charset_name is not a member of struct
st_mysql_options.
libmysql.c, line 913.11: 1506-022 (S) charset_name is not a member of struct
st_mysql_options.
libmysql.c, line 916.34: 1506-045 (S) Undeclared identifier CLIENT_INTERACTIVE.
libmysql.c, line 928.11: 1506-022 (S) rpl_probe is not a member of struct
st_mysql_options.
libmysql.c, line 931.11: 1506-022 (S) no_master_reads is not a member of struct
st_mysql_options.
libmysql.c, line 934.11: 1506-022 (S) rpl_parse is not a member of struct
st_mysql_options.
libmysql.c, line 937.11: 1506-022 (S) max_allowed_packet is not a member of
struct st_mysql_options.
libmysql.c, line 969.5: 1506-022 (S)
RE: Meaning of Column time in Show Processlist
Thanks very much. Marc. -Message d'origine- De : Jeremy Zawodny [mailto:[EMAIL PROTECTED] Envoyé : mercredi 13 août 2003 17:01 À : Mechain Marc Cc : [EMAIL PROTECTED] Objet : Re: Meaning of Column time in Show Processlist On Wed, Aug 13, 2003 at 03:26:24PM +0200, Mechain Marc wrote: First of all, thank you Jeremy for your answer. You said: It's the amount of time that thread has been in its current state. Is it the current state column value (which is in fact nothing or the text of the running SQL request) or the current command column value (sleeping, opening table, closing table, ...) that is used for the amount of time ? Both, really. I can't think of a time when one changes but the other does not. What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? The client will be disconnected and thread closed. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 431,850,739 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT .... SELECT
I tried that, but i dont work either. I need to insert a way that mysql doese'nt complain when i copy some records that have the same id (or that it just gives it an id according to the AUTO_INCREMENT) Thanks again //Lars Rasmussen -Oprindelig meddelelse- Fra: Jay Blanchard [mailto:[EMAIL PROTECTED] Sendt: 13. august 2003 19:59 Til: Lars Rasmussen; [EMAIL PROTECTED] Emne: RE: INSERT SELECT [snip] I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 [/snip] * does not return a specific number of columns, the work around is to specify the columns explicitly INSERT INTO nye_opskrifter SELECT foo, bar FROM opskrifter where id in($numbers) -- 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: Matching escaped strings
[snip] ID | Name 1 | Author\'s As you can see, the name value has been escaped. Now, the question is, how do you match on a value that has escaped charaters? I've tried the following SELECT * FROM table WHERE Name = 'Author\'s' SELECT * FROM table WHERE Name LIKE 'Author\'s' SELECT * FROM table WHERE Name = '%Author\'s%' SELECT * FROM table WHERE Name = 'Author''s' SELECT * FROM table WHERE Name = Author\'s SELECT * FROM table WHERE Name = Author's [/snip] The only one you didn't try SELECT * FROM table WHERE Name LIKE 'Author%' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: USE database prompt
On Wed, Aug 13, 2003 at 09:54:51PM +, Michael Welsh wrote: On Wednesday 13 August 2003 02:11 pm, Matthew McNicol wrote: see section '4.8.2 mysql, The Command-line Tool' in the manual Thank you Matthew, but, the prompt feature is not available until v4.02. From the docs: From MySQL version 4.0.2 it is possible to change the prompt in the mysql command-line client. I am using MySQL v 3.23.56 for pc-linux-gnu on i686 Time to upgrade then. :-) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 12 days, processed 466,810,618 queries (443/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT crashing mysql 4.0.14
Hi! On Aug 14, Henry Hank wrote: Sergei wrote: How big is your table? The MYD file is 2,406,292,556 bytes. oops :( I actually thought about asking for a copy of your data - it will definitely help to fix the bug, but 2GB is big enough to try everything else first. But if you don't mind - we can skip to this final resort now :) I've started mysqld with: /usr/bin/mysqld_safe --core-file= --core-file-size=100 And I've made it crash (as expected), but for the life of me, I can't find the core file anywhere to send it to you. It should usually be in the datadir. Where pid file and database directories are. One more question: What are results from SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes AGAINST(traded IN BOOLEAN MODE); SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes AGAINST(this IN BOOLEAN MODE); SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes AGAINST(bill IN BOOLEAN MODE); ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB multiple mysqld's on 1 server
I can certainly wait. This system has been working ok for almost a year. I'm very happy! I've been doing a review of all aspects of the system, and many of the maintenance tasks, and internal DB operations have suffered in speed to get the external parts optimal. But when this fix comes, i'll surely upgrade so our internal programs will run faster. thanks much On Wednesday 13 August 2003 17:04, you wrote: On Wed, Aug 13, 2003 at 04:07:24PM -0500, sean peters wrote: Hi all, i've been weighing the pros and cons of running multiple concurrent mysqld's on one server, to have better control over what databases are on what physical disks. System: 4 processor sun box running solaris with eighteen 36Gb drives. The situation is that i have a bunch of databases on one server that can all be classified as either external use or internal use. The internal use databases are consistently hit pretty hard, and we want this to have minimal impact on the external use databases. Currently we're using 64 index MyISAM tables, and with carefully choosing mount points for various physical devices, we have the databases separated as we want them. It is my understanding that with InnoDB, all tables are put into the configured InnoDB file(s) together, which would violate what i am trying to accomplish. The only solution i have come up with to control the physical location of InnoDB databases is to run multiple mysqld servers, each one with its InnoDB files on the desired device. Has anyone experienced any success or failure with this sort of configuration? This is the only reason holding us back from using InnoDB tables for this server. (we're using them on other machines) If you can wait a month or so, Heikki is supposed to have that fixed in InnoDB soon. So you'll be able to have more control over which data lives in which tablespaces. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE TABLE, INSERT INTO with SELECT in parentheses
At 10:19 am 14/08/03, Jim Smith wrote: I repeat. Why do you need the parentheses? Union queries don't require them. Sorry, missed this. They do need them if you want to use ORDER BY on the result of the UNION. Jim -- James Fryer / [EMAIL PROTECTED] / [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem renaming table
Hello All: I would like to rename a table if it exists. The following query IF EXISTS ALTER TABLE gwcc_members RENAME gwcc_members_bak; fails. also ALTER TABLE IF EXISTS gwcc_members RENAME gwcc_members_bak; fails. 1)What is the correct syntax? 2)Where is documentation. I'm using ver. 3.23.41 Thanks very much. tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com http://www.johnsons-web.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext searching
m n [EMAIL PROTECTED] wrote: How fulltext is working!!? From the MySQL manual: The search for the word MySQL produces no results in the above example, because that word is present in more than half the rows. As such, it is effectively treated as a stopword (that is, a word with zero semantic value). This is the most desirable behaviour -- a natural language query should not return every second row from a 1 GB table. You can read more about full-test search at: http://www.mysql.com/doc/en/Fulltext_Search.html I have the following table with just one record! And when I do a select statement, mysql returns no hits!!! Would you explain to me what is wrong?? Tanks Cheers! Adam CREATE TABLE `mytest` ( `id` int(11) NOT NULL default '0', `sub` text, PRIMARY KEY (`id`), FULLTEXT KEY `sub` (`sub`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (0, 'my name is kalle and i live in the North'); SELECT * FROM `mytest` WHERE (MATCH (sub) against (my)); Not hists!!!??? -- 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: A final Windows MySQL PHP plea
I have heard Apache croaks after 130 connections.. -M - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Andrew Rothwell [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 6:34 PM Subject: Re: A final Windows MySQL PHP plea Hi Andrew, I guess your reply was meant for Gary (the original poster of this e-mail). I will foward your request to the mailing list. Best regards Nils Valentin Tokyo/Japan WOW!!! That kind of System Power and you are wasting it on Windows and IIS E! Sorry - but Dual Proc Support, with 2gigs of RAM would Love Redhat 9.0 like a kid loves chocolate. I dont know about the more than 100 concurrent users, but bear in mind (as I understand it) that means that you can have up to 100 queries at 1 time, as soon as the query is over, the next user is available for his query. Linux itself can support 1000's of users at one time - You might be surprised. Andrew -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 PM To: Gary Broughton; [EMAIL PROTECTED] Subject: Re: A final Windows MySQL PHP plea Hi Gary, I understood that the packages provided by MySQL are set to 100 concurrent users by default, so what you ae asking is actually if somebody successfully compiled a version for more than 100 concurrent users and was able to use it in a production environment ? Do I understand that correct ? My guess would be that you are more likely to find Linux users having done such a setup. Unfortunately I haven'Tt had such an experience yet, but as you probably now Dell has made a study (which is also announced on www.mysq. com) which describes their experience, perhaps it contains the one or the other useful tip. http://www.dell.com/us/en/biz/topics/power_ps2q03-jaffe.htm Best regards Nils Valentin Tokyo/Japan 2003 8 13 01:21Gary Broughton : Hi all Is there anybody out there who has managed to successfully configure Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple of hundred users at any one time? I have chucked absolutely everything I can think of at this, but the MySQL (it seems) simply eats all the available CPU within a short space of time (regardless of users) and brings the site to a halt. My last throw of the dice today was to install all on a new Dual 1.8Ghz Pentium, with three hard disks in a RAID array, and 2GB memory, but it's achieved pretty much nothing. I am now desperate, and if anyone has any flash of inspiration for me, I'm all ears. The previous ASP version of the site runs like a dream, but there's something I'm either doing wrong, or this new combination of software simply doesn't like. Many thanks Stressed Gary -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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 debugging this Query?
Hi Andy, Thanks for your comments and my apologies on the late reply. To optimize my queries I restructured the tables - adding another table into the mix. Here is my table structure: CREATE TABLE kcs_thread_types ( id int(11) NOT NULL auto_increment, typeName varchar(255), PRIMARY KEY (id), UNIQUE typeName (typeName) ); CREATE TABLE kcs_threads ( id int(11) NOT NULL auto_increment, dateinserted timestamp(14), manufacturer varchar(255), type_index int(11), newUrlType varchar(255), colour varchar(255), colourID varchar(255), price decimal(8,2), image varchar(255), PRIMARY KEY (id) ); CREATE TABLE kcs_threadgroups ( id int(11) NOT NULL auto_increment, groupName varchar(255), groupNameUrl varchar(255), type_index int(11), thread_index varchar(255), PRIMARY KEY (id) ); The query I am now using is: SELECT * FROM kcs_threads as t1 LEFT JOIN kcs_threadgroups as t2 ON t1.type_index=t2.type_index LEFT JOIN kcs_category_threads as t3 ON t2.type_index=t3.id WHERE t1.manufacturer='DMC' ORDER BY t1.type,t2.groupName; When I do an explain on the query I get: +---++---+-+-+---+-- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- ++ | t1| ALL| NULL | NULL|NULL | NULL | 2067 | where used | | t2| ALL| NULL | NULL|NULL | NULL | 286 || | t3| eq_ref | PRIMARY | PRIMARY | 4 | t2.type_index | 1 || +---++---+-+-+---+-- ++ I am still getting the output I had before in the 77,000+ results being returned. I am at wits end here and don't know where else to look *shrugs* ANY clues? Do you want to see some of the table data? Thanks Aaron -Original Message- From: Andy Jackman [mailto:[EMAIL PROTECTED] Sent: August 11, 2003 2:21 PM To: Aaron Wolski Cc: [EMAIL PROTECTED] Subject: Re: Help debugging this Query? Aaron, It sounds like the join has duplicates on both sides. If you join a-b where a is unique (like a primary key) then you will get count(b) records (where b is the rows that match a). Or if b is unique then you will get count(a) records. However if neither a or b is unique you get count(a) * count(b) records. Without you tabledefs it is difficult to see if this is the case. Try this: select count(*) as xx from kcs_threadgroups group by threadType having xx 1; If you get a result then you have duplicates on threadType select count(*) as xx from kcs_threads where manufacturer='DMC' group by type having xx 1; If you get a result then you have duplicates on Type for manufacturer DMC. If you have dups for both then you are getting the result you are asking for. If this doesn't help, please publish your tabledefs. It's ok to simplify them so we don't have to wade through tons of stuff that has nothing to do with the problem. Regards, Andy. Aaron Wolski wrote: Hi Guys, I have 2 queries: select * FROM kcs_threadgroups AS t1, kcs_threads AS t2 WHERE t2.manufacturer='DMC' AND t1.thread_index=t2.id ORDER BY t2.type, t1.groupName Using the explain command (explain select.) I get: +---++---+-+-+-+ --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-+ --+-+ | t1| ALL| NULL | NULL|NULL | NULL | 286 | Using temporary; Using filesort | | t2| eq_ref | PRIMARY | PRIMARY | 4 | t1.thread_index | 1 | where used | +---++---+-+-+-+ --+-+ select * FROM kcs_threadgroups AS t1, kcs_threads AS t2 WHERE t2.manufacturer='DMC' AND t1.threadType=t2.type ORDER BY t2.type, t1.groupName Using the explain command I get: +---+--+---+--+-+--+--+- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+- + | t1| ALL | NULL | NULL |NULL | NULL | 286 | Using temporary; Using filesort | | t2| ALL | NULL | NULL |NULL | NULL | 2067 | where used | +---+--+---+--+-+--+--+- + With the second query, I am getting over 77,000 results returned and with
Re: Table to csv file ?
At 10:42 AM 8/14/2003, you wrote: I'm sure this is an easy process, but I'm at a total loss on the command to use. I want to send the contents of table x to a file x.csv. I've looked at the mysql_dump but that has way more info than I need. Just the table contents separated by commas, thanks just the facts! Have you tried: select ... into outfile myfile.txt http://www.mysql.com/doc/en/SELECT.html It defaults to tab delimited but CSV is also possible. This outputs the file to the server. You can't do it locally. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cant login as a user I thought I created
To take it one step firther I do mysqluse database This way all the consequent commands are directed at the specified DB Regards, Martin - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 5:44 PM Subject: Re: Cant login as a user I thought I created see below: Mark Healey wrote: On Wed, 13 Aug 2003 15:50:02 -0700, Jeff Weeks wrote: How did you create user mark? I figured the grant statement would take care of that. That's what the books I bought imply anyway. BTW, after issuing a grant you must issue flush privileges. Did that, the problem still persists. Check out mark in the user table in the mysql database. There's your problem! What am I supposed to look for in the user table? There is a user mark with a password but all the permissions are N. On Wednesday, August 13, 2003, at 12:24 PM, Mark Healey wrote: I'm trying to learn to use mysql. I don't want to mess with the system databases so I decided create a database and user to play with but I can't seem to do it right. I logged in as root and created a database mysql create database marksstuff; Query OK, 1 row affected (0.00 sec) I then granted priveleges to mark mysql grant all on marksstuff.* to mark identified by 'password'; not the real password Query OK, 0 rows affected (0.01 sec) I then logged out and tried to login as mark [EMAIL PROTECTED] mark]$ mysql -u mark -ppassword ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) the user mark has permissions to access only the marksstuff database. but you're not specifying the database with the mysql command. presumably, mysql is trying to log you in to another, default database that mark does not have permissions to access. have you tried simply: mysql -u mark -p marksstuff (and then enter the password when prompted) Murad Nayal -- 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: Matching escaped strings
On 13 Aug 2003 at 16:17, Rob wrote: ID| Name 1 | Author\'s As you can see, the name value has been escaped. Now, the question is, how do you match on a value that has escaped charaters? I've tried the following SELECT * FROM table WHERE Name = 'Author\'s' The sequence \' in a MySQL string means an apostrophe. What you want is a backslash followed by an apostrophe, so you need to put in two backslashes before it to represent a backslash: SELECT * FROM table WHERE Name = 'Author\\\'s'; Things are a bit more complicated with LIKE, because you want two backslashes in the string you give to LIKE, which means you need to start with four backslashes (there's an extra level of escaping): SELECT * FROM table WHERE Name LIKE 'Author\'s'; -- 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: FULLTEXT crashing mysql 4.0.14
Hi! On Aug 13, Henry Hank wrote: Environment: I'm setting up a database server on a Dell Poweredge 2650, dual 1.8GHZ pentium with 1GB of memory and RAID5 drives. I've installed RedHat 9, and updated the kernel to 2.4.20-19.9smp. I've installed the RPM binary distribution of MySQL 4.0.14 right from the MySQL website. The only thing I have done is disable InnoDb in my my.cnf file. Database Table: I have a database with 21.4 million records. One field is a VARCHAR(255) field on which I have created a FULLTEXT index. Problem: Most single word full text searches work perfectly and quickly. Occasionally, when someone enters several words (without any operators), MySQL will crash with the following in the error log (see below). Could you please run mysqld with --core-file to get a core dump and upload it to ftp://support.mysql.com/pub/mysql/secret/ ? How big is your table ? Note from the manual: To get a core dump on Linux if `mysqld' dies with a `SIGSEGV' signal, you can start `mysqld' with the `--core-file' option. Note that you also probably need to raise the `core file size' by adding `ulimit -c 100' to `mysqld_safe' or starting `mysqld_safe' with `--core-file-size=100'. *Note `mysqld_safe': mysqld_safe. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escape sequence question
Hi Roger, 2003 8 14 19:33Roger Baklund : * Nils Valentin I have problems understanding why the below two commands would return the same result. [...] mysql select * from sensei where link like '/var/www/html/xoops/2003\'s sum'; [...] mysql select * from sensei where last_name like '/var/www/html/xoops/2003\\\'s sum'; [... Note the three backslashes and the single backsplash (after the 2003) I understood the first sample looks for ...2003's and the second one for ...2003\'s. or am I wrong ? They both look for 2003's. Thats what I also thought at first sight, but there seems to be more to it. The LIKE operator is a pattern matching operator. The operand is evaluated twice: first by the parser, and then when the pattern matching is performed. For your last example, the first evaluation changes 2003\\\'s to 2003\'s, and the second evaluation changes 2003\'s to 2003's. THis example works for the second one , how about the first one in comparison ? Best regards Nils Valentin Tokyo/Japan URL: http://www.mysql.com/doc/en/String_comparison_functions.html -- Roger -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MD5-read permission?
While I understand the concern, it shouldn't be any easier for a hacker to see the raw text of your PHP files than to get directly to your database files. Neither is normally permitted by the web server. So if he somehow can get in and get one, why not the other? That said, if it makes you feel more comfortable, you could create a user id that has permission only to read the user table -- no other tables, no other access. If the passwords on the user table are encrypted, then even if he could somehow access your database without going through one of your existing programs, all he could find out would be user names and not passwords. I suppose you could encrypt user names on that table too, and then you'd have exactly what you're looking for. In any case, even if a hacker could somehow see your PHP files and get a password, then unless you have the mySQL port open to the world, there's no way for him to do his own query unless he can also somehow drop PHP or whatever files on your system and run them. Unless you have your mySQL port open to the world, which would be a way bigger security risk than anything you've discussed so far. If you don't have a firewall that keeps the world out of your PHP port, you should at least have your ids set up to only allow local access. 2003. 8. 14. ... 12:50.Nils Valentin : while the general idea sounds not to bad, I guess the bad guess would I meant the bad guys - what a silly typo ;-) just use a undecrypt function wich they either develop themself or get from somewhere. 2003. 8. 14. ... 03:07.Lefevre, Steven : Hey folks - While contemplating the design of a secure web database, an idea struck me. I'm thinking of submitting it as a feature request, so please critique it. I'm having php handle user logon with it's .htaccess emulation. I'm storing usernames and password hashes in a table. The problem is that php needs to open MySQL with *some user* with *some permission*, just to read the user table and check the password. So, It seems that I have to store the password plaintext somewhere in some php file. (I asked the list about this earlier and several others had great suggestions on how to hide this plain-text password -- Thanks Rob! -- but, can we make it better?) So if some wily hacker were to get the contents of this php file, s/he would get a username and password for the database. Now of course, I'm only going to give this user permission to read the user database, and all the passwords are hashed... but : I propose a new permission that I will call MD5read. It's like select, only it just returns hashes. So, say you do something like: SELECT password FROM user; 49726b60ccbf03d6c619632e1db6 f8ec2c9d79b5f969a96be968e7152bbd SELECT username, password FROM user; 24424b444b80831b677594a238f81dd9 | 4549625d8275b97b9b4f9662f1c550fa 1e5143d05b327f7d3cce15f9e3e44ad2 | fe3b4b388a69ceed38d6a0066e6a221b SELECT username+password+somethingelse FROM user; 49726b60ccbf03d6c619632e1db6 f8ec2c9d79b5f969a96be968e7152bbd So that way, if someone gets the username/password for this user, they can't get any data off of the database. One thing you have to watch is that you don't use the md5 function for a user that has only md5read permission, because that would double-hash it, and whatever you're checking would fail. I know you can do SELECT md5(username), md5(password) FROM user (or whatever the syntax is), but the user doing that has to have read permission already. So if a hacker gets that username and password, they are probably not going to hash data they are trying to get out of the database. I would feel safe storing a user's name and password in a plain text php script if they had only this permission. Is this useful? Are there any flaws in my reasoning? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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]
ssl support in MAX binaries?
Hi, Is ssl support provided in the MAX mysql binary distribution. The website lists some of the features provided in MAX that are not provided in the Standard binary. However, the website does not list the full list of features that are supported in the MAX but not in the Standard. Ren _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql build problem (fwd)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 13 Aug 2003, Annie Xie wrote: Any one could help? 1 anyone has a compiled tarball for mysql new version (-4.0 up) for solaris 2.6 can be shared? The OS was patched latest. 2 I'm trying build it, configure w/o any problem. However, when doing make, it failed w/ error: make[2]: Entering directory `/software/pkg/mysql-4.0.14/client' source='mysql.cc' object='mysql.o' libtool=no \ depfile='.deps/mysql.Po' tmpdepfile='.deps/mysql.TPo' \ depmode=gcc3 /bin/ksh ../depcomp \ g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_RWLOCK_T -c -o mysql.o `test -f mysql.cc || echo './'`mysql.cc mysql.cc: In function `int sql_connect(char*, char*, char*, char*, unsigned int)': mysql.cc:2300: `sleep' undeclared (first use this function) mysql.cc:2300: (Each undeclared identifier is reported only once for each function it appears in.) make[2]: *** [mysql.o] Error 1 make[2]: Leaving directory `/software/pkg/mysql-4.0.14/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/software/pkg/mysql-4.0.14' make: *** [all] Error 2 Unfortunately we do not have access to a Solaris 2.6 system and I can currently not investigate on how to fix this. This bug has been reported to us before: http://bugs.mysql.com/bug.php?id=630 You may want to follow the suggestion for a workaround given in the report. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/OnhiSVDhKrJykfIRAqUxAJ9rPbbik9QFn4I1raXrXJJ6YNUEXgCdG2Rg o8N2j5KzI2oWOEk/MA9Qhx4= =Ok9V -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: mutual declarations produce Error 1064
Please notice that INDEX (DNO, DNUMBER), is changed to be INDEX (DNO), And I managed to get another error message, But it still woun't compile. Also manually typing the code gave another result as Piping the company_01.sql file through an input operator Please tell me what could be wrong ? Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. August 2003 13:54 An: [EMAIL PROTECTED] Betreff: Re: mutual declarations produce Error 1064 Morten Gulbrandsen [EMAIL PROTECTED] wrote: USE company; # SHOW INNODB STATUS; DROP TABLE IF EXISTS DEPARTMENT; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN), # between employee and department FOREIGN KEY (MGRSSN),# a sort of mutually declaration REFERENCES EMPLOYEE(SSN) # this is declared in employee Remove comma before REFERENCES. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication
Thats a good quote on your site, I agree. To sum things up I've changed my design and basically am going to implement the last Q of http://www.mysql.com/doc/en/Replication_FAQ.html. Thanks for the info everyone. PS. Jeremy hows your book coming? I just bought a bunch of MySQL books, and can't wait to get yours. ... Ian Neubert Director of IS TWAcomm.com, Inc. http://www.twacomm.com/ -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, August 11, 2003 12:39 PM To: Adam Nelson Cc: 'Ian Neubert'; [EMAIL PROTECTED] Subject: Re: MySQL Replication On Fri, Aug 08, 2003 at 12:10:18PM -0400, Adam Nelson wrote: Also, one has to work out the cost of high availability. If you're talking about a situation where you reduce downtime from 4 hours/yr to .5 hours/yr and it costs you x dollars, you have to make sure that the extra 3.5 hours of downtime would cost more than that much money. Agreed. In fact, this has come up before... http://jeremy.zawodny.com/blog/archives/000805.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 10 days, processed 336,775,492 queries (387/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mutual declarations produce Error 1064
Morten Gulbrandsen [EMAIL PROTECTED] wrote: USE company; # SHOW INNODB STATUS; DROP TABLE IF EXISTS DEPARTMENT; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN), # between employee and department FOREIGN KEY (MGRSSN),# a sort of mutually declaration REFERENCES EMPLOYEE(SSN) # this is declared in employee Remove comma before REFERENCES. -- 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: select count(*) Problem in PowerBuilder Thru MySQL ODBC 3.51
Fatt Shin wrote: Yes, found the problem, you are right. Thanks. But this is something caused by powerbuilder, in my code I never put any space between count(*), but when it goes to odbc, an space is added. Any idea how to solve this? Wouldn't sum(1) return the same count -theoratically faster-, leaving more room for stray spaces too? HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Errors in Replication Binlog
Could you deascribe it more detailed? Which version of MySQL do you use? If you're talking about my original question, the database is 4.0.13. -- Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: no foreign key and view?
Susan, - Original Message - From: Susan Lam [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, August 11, 2003 8:37 AM Subject: no foreign key and view? I was told that mysql doesn't has foreign key and views. Is it ture? Or is it different across different version such as InnoDB? I looked into the doc it said foreign key syntax only what does that mean? foreign keys are supported only in InnoDB type tables. Views are not supported yet. They are slated for MySQL-6.0, I think. You had posted your message to the newsgroup mailing.database.mysql. That is only a mirror of the mailing list [EMAIL PROTECTED], where you should send your postings. Thanks, Susan Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Functions as default values
I am trying to use a function as a default value for a column but do not seem to get the desired result. I want to use the NOW() function for a last_updated column, here is my code... CREATE TABLE test_table ( last_updated datetime NOT NULL default `NOW()` ) TYPE=MyISAM; This gives an error; CREATE TABLE test_table ( last_updated datetime NOT NULL default 'NOW()' ) TYPE=MyISAM; Now the table shows a default value of -00-00 00:00:00, when I add a new row the value of last_updated is also -00-00 00:00:00. I am using MySQL 3.23.37, can anyone help? funcion as default-value is not allowed AFAIK but timestamp will help you to get what you want! http://www.mysql.com/doc/en/DATETIME.html -- 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]
Re: replication problem
On Wed, Aug 06, 2003 at 07:00:33PM -0700, Steven Roussey wrote: So does anyone else have any ideas what is going on here? Shall I report this as a bug? Did you post how you setup the servers to load the different my.cnf files? Hopefully you don't have one at a default location. I don't use an /etc/my.cnf for this reason. The main one uses /var/lib/mysql/my.cnf and the slave one /data/mysql-backup/mysql/my.cnf. Is there a quick way to have it tell you which files it is reading and what settings will get set? Otherwise, it sounds like the config information is not properly set -- either some user setup error that is alluding all of us, or a config loading error in MySQL. I'd like to check how the config files are located, etc., before doing a bug report. My two cents. PS: I'd also have both servers running the latest version, just in case it was a bug that was already fixed. Good point, I might as well try that now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I enable unicode and japanese support?
I am using cold fusion to make a small custom website using mysql to store the information a user inputs. I want it to be able to save japanese and unicode text, but whenever I enter any it comes back as garbage. Is there anything special I need to do to configure mysql to use unicode? Thanks! -Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Steven Roussey wrote: ORing on two different fields is what I have been asking about :). This is not optimized, and I don't think it is set to be optimized until 5.1 (as per someone else's comment). Using a composite index was suggested This is bad information. It works for AND, not for OR. You have two workarounds: temp tables and unions. By a twitch of algabra, how about ... select * from sometable where not( f1 != 123 and f2 != 123 ), ... at least it gives an AND to optimize. Then again, I maight be too optimistic. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thu, Aug 07, 2003 at 09:07:41PM -0500, Dan Nelson wrote: In the last episode (Aug 07), Jeremy Zawodny said: I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. I don't know how to do this with pthreads but with LT, I'd like to identify a few of the pids for the struck threads and then get a snapshot of the call stack to see where they're waiting. The pstack command (ports/sysutils/pstack) knows about FreeBSD's libc_r threads, so you can run that to get a snapshot of mysql's thread state. Just caught one. We have nealy 300 stuck connections and they all look like this right now: 45855: /home/y/libexec/mysqld - thread 0 (running) - 683f4de8 _sigsuspend (8e0ffbc0, 4, 8e0ff9c4, 683644de, 68373e64, 8dfff9e8) + 8 68364534 __pthread_suspend_old (8e0ffbc0, 0, 8e0ffa04, 6836183a, 68373e64, 3ce3500c) + 64 683619b6 __pthread_alt_lock (8366b04, 0, 8e0ffa34, 68364c5a, 0, 3ce3500c) + 18a 68364db2 pthread_mutex_lock (8366af4, 3ce35504, 0, 826a3b9, 3ce35504, 3ce3500c) + 166 8078c01 ip_to_hostname__FP7in_addrPUi (3ce35504, 8e0ffa9c, 8e0ffb24, 807d490, 3ce1d280, 3ce35504) + f1 807d4a0 check_connections__FP3THD (3ce35000, 0, 1, 807d7dc, 68373e64, 8e0ffbc0) + c4 807d8b2 handle_one_connection (3ce35000, 2, 8e0ffc78, 68365e77, 68373e64, 8e0ffbb8) + 11a 68365f2d _init (8e0ffbc0, 8e0ffbc0, 0, 0, 0, 0) + f119 Now, to figure out what that's telling us... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 250,137,018 queries (400/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 Question
On Thu, Jul 31, 2003 at 10:33:21AM -0700, Richard Sumilang wrote: Anyone know when it will be considered production stable? When it's stable. http://www.mysql.com/doc/en/Release_philosophy.html -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 207,654,129 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tracking a delete
Or do not delete them at all, simply flag them as deleted then proceed otherwise. Develop some routine to purge/backup the deleted records after a given period of time. on 8/8/03 2:08 PM, Chris Boget at [EMAIL PROTECTED] wrote: How can I see if a record was deleted from a database? You can attempt to SELECT it, and if you get no result, it's not there. But that doesn't necesarily mean that it was once there and has now been deleted. If you want to determine that, you'll need to create a log of record deletions. -- Bill Leonard [EMAIL PROTECTED] www.machinemen.com407.464.0147 XrackHosting.com - Mac OS X hosting done right! http://www.xrackhosting.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
I don't believe Windows services can be started with any priority types. MySQL is on it's own box with the ASP version, which works like a dream. It's simply that whenever the PHP version is used (either as a solitary website on another box or as another website on the same box), that's when mysqld goes mad. The odd thing is that the software is effectively identical between the two languages, and has simply had functions changed as and where appropriate. I have also used the programming methods as used in the book PHP and MySQL Web Development too, as well as reading advice from MySQL Second Edition by Paul Dubois. I just wonder if this is a problem that is unable to be solved?! -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED] Sent: 08 August 2003 17:16 To: 'Gary Broughton'; [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time I think I see the problem. Mysql really needs to be on it's own box. It's designed to just use as much power as it can find. This is a good thing for those with dedicated machines. I don't know if there's a configuration setup that tell mysql that it's not the head honcho. Does Windows have a way to start a process (mysql) in low priority? -Original Message- From: Gary Broughton [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- 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]
unable to run mysql_install_db please help
Dear Sir/Madam i am trying to run mysql_install_db but my windows xp command prompt give me the following error: 'mysql_install_db' is not recognized as an internal or external command, operable program or batch file. How can i run this line? please help thank you very much Sandeep Sodhi
mysql shuts down overnight
Hi everyone, I got apache 2.0 + MySql 3.23.52 + PHP 4.3.1 on a Redhat 8.0 server. Somehow MySQL automatically shuts down overnight, anyone has a reason for this? Might because of cron? Thanks in advance!
Mysql processlist sleep time
Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Has anybody any ideas on why this can be? I cannot find out how to tell what is causing the long sleep period. Many thanks Gary
Form values are truncated
All,
I'm building a member signup form. Fields that contain more than one word
are being truncated when being inserted into the MySQL table.
I'm using this code that I got from one of my PHP books.
$as_addr1 = addslashes($_POST['addr1']);$tr_addr1 = trim($as_addr1);
So, if I post 1122 Boogie St. it gets inserted as 1122. If I remove the
addslashes portion, only using trim, I get the same result.
Here's the query:
$query = INSERT INTO subscribers
(email,password,fName,lName,addr1)
VALUES
('$tr_email','$tr_pass','$tr_fName','$tr_lName','$tr_addr1';
echo $query;
Here's the echo:
INSERT INTO subscribers (email,password,fName,lName,addr1)
VALUES('[EMAIL PROTECTED]','','Jim','Johnson','78797 State')
I have other fields in the table that are doing the same thing. All fields
are varchars of varying length. Field addr1 is varchar(50).
Suggestions?
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
I think I see the problem. Mysql really needs to be on it's own box. It's designed to just use as much power as it can find. This is a good thing for those with dedicated machines. I don't know if there's a configuration setup that tell mysql that it's not the head honcho. Does Windows have a way to start a process (mysql) in low priority? -Original Message- From: Gary Broughton [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- 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: A final Windows MySQL PHP plea
Hi Andrew, I guess your reply was meant for Gary (the original poster of this e-mail). I will foward your request to the mailing list. Best regards Nils Valentin Tokyo/Japan WOW!!! That kind of System Power and you are wasting it on Windows and IIS E! Sorry - but Dual Proc Support, with 2gigs of RAM would Love Redhat 9.0 like a kid loves chocolate. I dont know about the more than 100 concurrent users, but bear in mind (as I understand it) that means that you can have up to 100 queries at 1 time, as soon as the query is over, the next user is available for his query. Linux itself can support 1000's of users at one time - You might be surprised. Andrew -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 PM To: Gary Broughton; [EMAIL PROTECTED] Subject: Re: A final Windows MySQL PHP plea Hi Gary, I understood that the packages provided by MySQL are set to 100 concurrent users by default, so what you ae asking is actually if somebody successfully compiled a version for more than 100 concurrent users and was able to use it in a production environment ? Do I understand that correct ? My guess would be that you are more likely to find Linux users having done such a setup. Unfortunately I haven'Tt had such an experience yet, but as you probably now Dell has made a study (which is also announced on www.mysq. com) which describes their experience, perhaps it contains the one or the other useful tip. http://www.dell.com/us/en/biz/topics/power_ps2q03-jaffe.htm Best regards Nils Valentin Tokyo/Japan 2003 8 13 01:21Gary Broughton : Hi all Is there anybody out there who has managed to successfully configure Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple of hundred users at any one time? I have chucked absolutely everything I can think of at this, but the MySQL (it seems) simply eats all the available CPU within a short space of time (regardless of users) and brings the site to a halt. My last throw of the dice today was to install all on a new Dual 1.8Ghz Pentium, with three hard disks in a RAID array, and 2GB memory, but it's achieved pretty much nothing. I am now desperate, and if anyone has any flash of inspiration for me, I'm all ears. The previous ASP version of the site runs like a dream, but there's something I'm either doing wrong, or this new combination of software simply doesn't like. Many thanks Stressed Gary -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - mysqldump
Rafal Kedziorski [EMAIL PROTECTED] wrote: we have problems with import our dumped InnoDB tables. We get this error message: Can't create table '.\mmcms_test\media_lock.frm' (errno: 150) My tables which will be referenced by foreign keys will be dumped in the inncorect order. if I set: SET FOREIGN_KEY_CHECKS=0; sql script SET FOREIGN_KEY_CHECKS=1; Than import works. What is the best way to dump and import InnoDB tables. Using SET FOREIGN_KEY_CHECKS=0 :) -- 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]
mysql_last_value() update
/* Copyright (c) 2003 Aftab Jahan Subedar
mysql_last_value() Version 3.2
--
Replaces NULL column(s) with value from last available column value.
Scenario
Table to be operated on.
table_a
---
record id color
1001 BLACK
2NULLPINK
NULL 002 WHITE
3NULLBLUE
NULL NULLGREEN
NULL 003 YELLOW
4004 BALCK
Table that is converted to.
table_b
---
record id color
1001 BLACK
2001 PINK
2002 WHITE
3002 BLUE
3002 GREEN
3003 YELLOW
4004 BALCK
This is free for public.
Commercial uses require license from
Aftab Jahan Subedar
Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
81/1-A North Jatrbari
Dhaka 1204
Bangladesh
sms://+447765341890
sms://+880171859159
http://www.ceobangladesh.com
http://www.DhakaStockExchangeGame.com
http://www.geocities.com/jahan.geo -- source code found here
+880171859159 sms +447765341890 [EMAIL PROTECTED]
[EMAIL PROTECTED] [EMAIL PROTECTED]
Has Problem?
Feel free to report.
License Fee: USD 25 or equivalent for Lifetime . Bank information:
i. Aftab Jahan Subedar
Sort Code: 800283
Account No. 07271988
Bank Of Scotland
Newington Branch
51 South Clerk Street
Edinburgh EH8 9PP
UK
ii. Aftab Jahan Subedar
Savings Account No. 794-2-4403321-4
[Sort Code 794]
Standard Chartered Bank
32 36 Jalan 52/4
Petaling Jaya
Selangor
Malaysia
iii.Aftab Jahan Subedar
Savings Account No. 18 1757 393 01
[Sort Code 18]
Standard Chartered Bank
53 Kawran Bazar, G.P.O Box #3668
Dhaka 1215
Bangladesh
compile instruction:
cc -o mysql_last_value mysql_last_value.c -I/usr/local/include/mysql
-L/usr/local/lib/mysql -lmysqlclient
usage:
./mysql_last_value -u user -h host -d last_value_test -f table_from
-t table_to -r replace_field1 replace_fieldn -v -p secretpassword
Parameters:
-d database
-f from which table to copy from
-t to which table to be copied to
-r replace field names separated with space. upto
20 fields.
-v display verbose
-v -v display insert statement ;(
example:
./mysql_last_value -d last_value_test -f table_a -t table_b -r
record id -v
./mysql_last_value -d last_value_test -f table_a -t table_b -r
record -v
Training available on C/C++, CGI, Unix , MySQL (or other API) in
Bangladesh and abroad.
*/
#include stdio.h
#include fcntl.h
#include stdlib.h
#include unistd.h
#include string.h
#include ctype.h
#include mysql.h
/* for freeing easily*/
char*host = NULL;
char*user = NULL;
char*passwd = NULL;
char*database = NULL;
char*sql_insert_to = NULL;
char*sql_insert_from = NULL;
int verbose = 1;
int use_supplied = 0;
char*last_value=NULL;
char*replace_field_name=NULL;
char*criterion=NULL;
char*insert_statement=NULL;
char*replace_value[20];
unsigned int*puiQuotes=NULL;
char*pcQuery=NULL;
MYSQL mysql;
MYSQL_RES *pResult=NULL;
unsigned int*puiIndexOfReplaceField=NULL;
unsigned intuiNumOfReplaceField=0;
unsigned long *pulFieldLengths;
unsigned int uiReplaceIndex=0;
void usage(void);
void free_all(void);
char *strupr(char *str);
/*int strcmpp(const char *p1, const char *p2);*/
void append_insert(unsigned long length,char *value);
unsigned int get_replace_index(unsigned int uiCurrentIndex);
void replace_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex,unsigned
int uiReplaceIndex);
void copy_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex);
void append_insert(unsigned long length,char *value)
{
unsigned long old_length=0;
char
Corrupt index = mysqld freeze?
I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, but I think it's a P3 1GHz. It has several tables with 20-40 million rows and a ton of smaller tables with less than 1 million rows. All tables are MyISAM and we have fewer than 10 queries per second. The super large tables are write only for the most part, with most reads taking place in off peak hours (a cron to generate aggregate data). The smaller tables are read/write. We've been experiencing a problem where mysqld stops responding to new connections. Any active connection is fine and can run any query it wants, but all new connections get stuck in the authenticating user phase. CPU and load drop to about zero when this happens, so I don't think it's the notorious threading issue. This freeze happens when more than 10-12 connections drop at the same time, usually when a queue caused by a table lock clears out. One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. It has also been stated that multicolumn indexes are a problem, especially if they contain more than 3 columns. This goes against everything I know about mysql. In my experience if there is file corruption an error gets returned promptly. I also believe multicolumn indexes are a valuable feature. I have been told that I need to get rid of all multicolumn indexes in order to make the server stable. Needless to say, I am not very happy with this solution and don't have a lot of faith in it working. Has anyone else experienced anything similar to this, and if so what did you do to fix it? Anyone want to weigh in on the index theory because it doesn't really sound right to me, but I'm not exactly an expert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by question
I think I figured it out. I had the gallery_id field zero filled... It works now, thank you very much! -m -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 3:49 PM To: mysql Cc: motorpsychkill Subject: Re: order by question * motorpsychkill Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways! It should work also in version 3.23.54... actually, it should work since version 3.20.17: URL: http://www.mysql.com/doc/en/News-3.20.17.html -- Roger -- 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: Reaching max between 1456-1458 connections
Brian Austin says: Have you read the following page in the Manual on their site? http://www.mysql.com/doc/en/Linux.html This gives you some tips to increase thread allocation. Especially interesting is the part about the LinuxThreads hack. That's where I started, and it's why I compiled my own MySQL. However, the constants mentioned on that page are no longer defined as of 2.4 kernels and 2.3 glibcs. It looks like Linux has been somewhat reengineered to remove hard limits on threads since that page was written. And indeed, I don't run into a limit at 1024 threads. So that's why I'm asking here, in case someone has more recent information than the MySQL website. Hope this helps, Thanks, I hope someone has the latest info. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
[snip] 70050;451 70322;451 LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] [/snip] I am going to recommend that you specify which columns the data goes into LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one`(foo, bar) FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join syntax diff 3.23 to 4.x?
Try changing JOIN list_states to INNER JOIN list_states Looks like the earlier version didn't like your (synonymous) wording. -Original Message- From: Jack Dare [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 8:35 AM To: [EMAIL PROTECTED] Subject: Join syntax diff 3.23 to 4.x? Is there something about 3.23 that makes this illegal? It is fine on 4.012. SELECT [lots of columns] FROM new_people p LEFT JOIN new_address ad ON p.address_id = ad.id JOIN list_states ls ON ad.state = ls.id WHERE p.company_id = 1 Will some minor rewording help it run on both versions OK? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load data infile issue
I'm using some default table names and such here...my actual code has the appropriate table, server, un, pw etc...
the logic of full text search
Hi there, I am trying to understand the logic of full text search in mysql. I'm not using mysql 4. The search work OK, be it thast I get hits on certain words, whilst other words are discarded for some reason or other. Why is that. An example: I search in a text field for the word organisation. I get hits. When I search for the word scenario nothing is found. But I can see the word in the paragraphs by myself? Is there an explanation for this? Thanks, Sjef
Re: MySQL Replication
On Thu, Aug 07, 2003 at 01:00:12PM -0700, Ian Neubert wrote: I was trying to design it so that the slaves wouldn't know they had connected to a different master, as they both masters would have the same IP address that gets failed over based on the Linux Virtual Server software and VRRP (like heartbeat from Linux-HA). That path is a very, very, very difficult one. How can you absolutely guarantee that each master's binlog will be indentical in name, size, and content? If you can't, this scenario really falls apart. (I've suggested enhancements to MySQL that would fix this but don't know if they're terribly high on the priority list...) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,501,412 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Joshua, Joshua Spoerri wrote: Forgive me, that example is no good. Oddly, it works, but the following does not: mysql create temporary table x (y int, z int, q int, index (y, z)); insert into x values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1; MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. Best regards -- Are you MySQL certified? - http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple databases creation
Hi, I know that Mysql handles large database very well, but there is a project that requires more than 2000 small databases(about 20 talbes with a few rows) to be created within a Mysql server. Could somebody tell me does it make sense? Thanks for your consideration. likai _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
Sometimes, movies have more than one director, so the association between movies and directors needs to be in its own table, instead of the movies table. (Otherwise, you're limited to some fixed number of directors per film.) Directors may not be the best example. Think about producers, cast, etc. Let's say I need to pull all producers for a movie. For a movie that has three producers (aka three matches with a LEFT OUTER JOIN), I'll get three rows -- all with the same movie, but different producers. Jurassic Park | Kathleen Kennedy | 35 comments Jurassic Park | Gerald R, Molen | 35 comments Jurassic Park | Lata Ryan| 35 comments How can I list all of these producers within a single row? Jurassic Park | Kathleen Kennedy, Gerald R, Molen, Lata Ryan | 35 comments I can't say I like the idea of concatenating all those items into one. Look at the parsing difficulty you've gotten yourself into. Is Molen a director? Or is it Gerald R? Most databases don't have any functions for this grouping-concatenation, since it leaves you with a result set that has a field of arbitrary length. What about a movie with 500 actors? So, really, I prefer to order by the movie, then the director. While processing the result set, just be aware you might have duplicate consecutive movies. Then you can roll in the directors as you go. So much for that lecture. If you're willing to use the ALPHA mySQL 4.1, there is a method that promises to do what you want: http://www.mysql.com/doc/en/GROUP-BY-Functions.html, look at GROUP_CONCAT. I think that fits the bill, with the caveat that it might well not be ready for prime-time yet. Oh, and it won't work on (any) other databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB Performance - Celeron vs. P4
Is the workload I/O bound, CPU bound, or memory bound? On Linux, you can get a sense of this as follows: Run top and vmstat 5 on your MySQL box, and with them running have your system perform whatever slow operations you are concerned about. The key numbers here are the swap used % iowait and % idle from top, and the numbers under the bi and bo columns in vmstat. Interpreting the output: -If your swap number is more than a couple megabytes, and/or grows during these slow operations then you may be memory bound. If your server is swapping that will tend to slow everything else down, and if it's swapping *MySQL* then you'll really have issues. -If your % idle number is very low, then you may in fact be CPU bound -- especially in conjunction with relatively small bi/bo numbers. In this event a new CPU may or may not help much. (See below.) -If *either* of your bi/bo numbers is very high, or your % iowait is high you may be I/O bound. The definition of very high depends upon your disk setup: If you have a single IDE drive then a bi or bo of more than 2,000 is pretty high. If you have a hardware IDE RAID5 array with a lot of disks then the number may be more like 20,000. Solutions: -First and foremost: The largest performance gains to be had come from optimizing your indexes, schema, and queries. Depending upon what you wish to accomplish and how well designed your indexes/schema/queries are, you may be able to achieve several orders of magnitude improvement without any hardware changes. Our hourly reporting queries here wound up taking 36 hours to run eventually despite carefully designed indexes and queries. The problem was that first and foremost our schema had to be optimal for transactional access and this resulted in reporting queries that involved lots of joins and group bys at once. A carefully designed reporting schema and an incremental mechanism for loading new data from the transactional schema into the reporting schema allowed us to eliminate several joins, filesorts, and so forth from the reporting process (as well as making it easier to design queries where we didn't re-process the same data over and over) and the net result was that what once took 36+ hours now takes a couple minutes on exactly the same hardware (but with much more data at this point). Your situation may or may not be conducive to software optimization, but be sure to think beyond how do I make this query fast and consider how do I minimize the amount of work MySQL has to do. That said, more hardware might be beneficial: -Are you swapping? If so, adding more RAM is the obvious choice, however is MySQL is being swapped out then the first step is to curtail MySQL's memory usage. You can tell if MySQL is being swapped out by looking at the row(s) for mysqld in top and seeing if the number under RSS is substantially smaller then the number under SIZE. If this is the case, you should consider lowering MySQL's memory usage and see if that helps before adding more RAM. -Are your bi/bo numbers very high? If so, a new CPU is unlikely to provide a substantial improvement. The hardware approach here is either more RAM (and telling MySQL to use it) or a hardware RAID array (more spindles = faster I/O). Which one is appropriate depends upon whether MySQL is writing to temp tables and/or doing file sorts or is simply changing a LOT of rows. -Is your % idle very low (especially in conjunction with low bi/bo numbers)? If so, a new CPU will probably help. -JF -Original Message- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 5:00 PM To: [EMAIL PROTECTED] Subject: DB Performance - Celeron vs. P4 Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? I'm hoping to see a response like, Oh yeah - I moved to a P4 from a Celeron and operations that used to take 10 minutes now take 1 minute or less - all because MySQL has special options to take full advantage of the P4's power. Or something like that. fingers crossed - Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON UPDATE CASCADE fails
Fraser,
- Original Message -
From: Fraser Hanson [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Wednesday, August 06, 2003 5:46 AM
Subject: ON UPDATE CASCADE fails
Hello,
I have a table which has a foreign key relationship with itself. I
want and expect my updates to cascade (deletes definitely cascade as
expected) but instead I just get error 1217: foriegn key error.
I have written example code to use in reproducing the problem:
# Create the table
drop TABLE IF EXISTS person;
create table person (
name VARCHAR(50) NOT NULL,
parent VARCHAR(50) NOT NULL,
INDEX parent_ind(parent),
FOREIGN KEY(parent)
REFERENCES person(name) # this makes it so that when a page is
deleted/updated, so are all it's sub-pages
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (name)
) TYPE=INNODB;
# Insert some example data
INSERT INTO person (name,parent) VALUES ('Grandpa', 'Grandpa');
INSERT INTO person (name,parent) VALUES ('John', 'Grandpa');
INSERT INTO person (name,parent) VALUES ('Baby Bob', 'John');
mysql select * from person;
+--+-+
| name | parent |
+--+-+
| Grandpa | Grandpa |
| John | Grandpa |
| Baby Bob | John|
+--+-+
# Attempt the update (this fails with error 1217)
UPDATE person SET name='Mary' where name='John';
# I expect to end up with these values:
mysql select * from person;
+--+-+
| name | parent |
+--+-+
| Grandpa | Grandpa |
| Mary | Grandpa |
| Baby Bob | Mary|
+--+-+
I have verified that deletes cascade as expected.
http://www.innodb.com/ibman.html#InnoDB_foreign_keys
A deviation from SQL standards: if ON UPDATE CASCADE or ON UPDATE SET NULL
recurses to update a table for which there already is an update operation in
the stack of cascaded operations, it acts like RESTRICT. In plain English
this means that you cannot use self-referential ON UPDATE CASCADE or ON
UPDATE SET NULL operations. This is to prevent infinite loops resulting from
cascaded updates. A self-referential ON DELETE SET NULL, on the other hand,
works starting from 4.0.13. A self-referential ON DELETE CASCADE has always
worked.
InnoDB does not check what rows it has already updated during the SQL
statement. To prevent infinite recursions I let it simply check if it
recurses to the same table.
Since you normally do not update the primary key of a row, ON UPDATE CASCADE
is seldom used in well-designed schemas.
Any help is greatly appreciated,
--Fraser Hanson
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just testing
Testing ..Please ignore this email
Re: reindexing
Jeff, - Original Message - From: Jeff Mathis [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, August 07, 2003 9:01 PM Subject: Re: reindexing what is the command to update InnoDB table indexes? if you mean update index cardinality statistics, then starting from 4.0.13: ANALYZE TABLE now works also for InnoDB type tables. It makes 10 random dives to each of the index trees and updates index cardinality estimates accordingly. Note that since it is only an estimate, repeated runs of ANALYZE TABLE may produce different numbers. MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you may try using ANALYZE TABLE. If you mean defragmentation, then dump + drop + reimport is the method. Note that you should add the indexes directly into the CREATE TABLE statement before the import. Creating indexes afterwards is slow, because MySQL recreates the whole table for each index you add. Greetings to Marko and Pekka in New Mexico! jeff Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ Brent Baisley wrote: You are referring to fragmentation. In this case I'm pretty sure it's just a matter of updating the indexes so that MySQL makes the correct assumptions about the distribution of data. MySQL assumes data values have an equal distribution in a database, updating the indexes will force MySQL to update it's assumptions and thus do a better job of optimizing searches. On Wednesday, August 6, 2003, at 12:17 PM, Jeff Mathis wrote: I was under the impression that InnoDB tables took care of this for you. You only need to be concerned if you add/delete repeatedly from anywhere but the end rows of the table. I'd like to know if I'm wrong about this. Adam Nelson wrote: I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://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] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select match from a stored delimitated string?
Hi :) I'm not even sure if I'm naming the question correctly. I hope I can ask/explain it clearly enough to get a hint ;) Essentially... 1) I have a table of portfolio 'pieces', each row of which has a unique ID 2) I have a table of 'projects', which are comprised of a number of 'pieces'. There is a column in this projects table called 'pieces', which contains a delimitated string of all the 'pieces' related to that 'project', in the format of '1||2||3||5||12||16||17||24||25' 3) When viewing a data sheet of a single row in the table 'pieces', I'd like to be able to include a reference to any 'projects' that the said piece may be a member of. I've tried a few things like $ID = '1'; SELECT * from projects WHERE pieces LIKE '$ID%' - which will find anything that starts with $ID, such as 1||3||4 SELECT * from projects WHERE pieces LIKE '%$ID%' - which will find anything that contains $ID, such as 1||3||4 5||1||72||8 3||5||21||9 3||5||17||9 What I can't figure out how to do is to select this 3||5||1||9 Without selecting something like 3||5||21||9 or 3||5||17||9 Is this possible in a single sql statement, or do I have to select all rows of 'projects' and use php to somehow loop through the results, explode the delimitated strings, and look for the exact match this way, somehow. If you haven't noticed, I'm a little bit of a newbie, so please be nice :) Salut, verdon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld dead subsys locked
Shut down my Linux PC running MySqld and rebooted this morning and cannot get mysqld up. In services when I click on mysqld I get the message Mysqld dead subsys locked when i restart the server it says mysql succesfully restarted, but it isnt. When I try to stop the server it comes back with an error message failed to shutdown. So I rebooted and during shutdown- shutdown mysql.failed and- mysql killall...failed. Anyone have any ideas as to how I can sort this? would a reinstall of Mysql work? would I lose legacy data doing this? Bob
RE: Connecting to MySQL from ASP
See http://www.connectionstrings.com/ Regards, Ulises -Mensaje original- De: Ratmil Torres [mailto:[EMAIL PROTECTED] Enviado el: Miércoles 6 de Agosto de 2003 07:11 AM Para: [EMAIL PROTECTED] Asunto: Connecting to MySQL from ASP Hi. I am developing a web site using ASP. I need to connect to a MySQL Server. I am using ODBC, so how should the connection string be? I have already installed the ODBC driver for MySQL. Thanks.
Please Help
Dear administrator,
Now I have a quesion and want to get your help.
When I insert an image file data (data size 64KB) to a BLOB field of MySQL database
by com.mysql.jdbc.driber,there is error. Error message as follows:
aq.executeQuery:Communication link failure:comm.mysql.jdbc.packetTooBigException
The part of my program as follows:
..
FileInputStream fis = new FileInputStream(untitl2.gif);
..
conn =
DriverManager.getConnection(jdbc:mysql://dbgserver.ihep.ac.cn/bsrf?user=bsrfpassword=bsrfuseUnicode=truecharacterEncoding=Gb2312);
String ins =insert into myimg values(?,?);
PreparedStatement stmt = conn.prepareStatement(ins);
System.out.println(Test1*);
stmt.setInt(1,1001);
try
{
int len= fis.available();
System.out.println(len);
stmt.setBinaryStream(2,fis,len);
System.out.println(Test***2*);
int rowsupdated = stmt.executeUpdate(); // When program run
in here , it appears error.
System.out.println(Test3*);
System.out.println(RowsUpdated= +rowsupdated);
}
catch(IOException ex) {
System.out.println(IOException:+ex.getMessage());}
Could you help to relve this quesion as soon as. Thank you very much.
I am looking forward ro hearing from you.
Best Regards,
Ma Mei
---
Ma Mei
Computing Center
Institute of High Energy Physics
P.O.Box 918 Ext.7
Beijing 100039
P.R. China
Phone: (8610) 88235037
FAX: (8610) 88236839
E-Mail: [EMAIL PROTECTED]
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance questions
On Fri, Aug 01, 2003 at 01:00:13PM -0400, Jack Coxen wrote: Since starting up RTG not quite 3 months ago, my database has grown to over 10GB in size. If I can get the drive space to keep the 2 years worth of data I want to, my database will be edging upward toward 100GB. There are currently 682 tables ranging in size from 2 KB/1 record to around 310 MB/7570511 records depending on the number of ports on a router and how much traffic flows through it. I'm running around 2500 queries/minute - 99+% of them inserts. My cpu load is minimal - around .40 nominally but it may go as high as 1.80 or so when handling multiple large retrievals. If I look at the iostats output for my server, the drive controller bandwidth utilization is around 30-40% during normal operation but immediately jumps to 100% utilization during retrieves. Retrieval is done via Perl scripts or from Perl/PHP WebPages. I'm trying to get a RAID array loaded with striped drives to hang off the server but until I can talk someone into signing the check, I need to do anything I can to improve performance. This sounds like it'll be a classic disk I/O problem. Sooner or later you hit the wall on what a single disk is able to do for you. The only real solution is a faster disk or adding more disks to spread the work around. Adding memory will help a very small amount. Storing less data, of course, would too. Making the data take less space will also help. But it sounds like you may have done some of that already. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 214,041,264 queries (397/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Message
It's a perl DBI question not a MySQL one but anyways... $sth is just a statement handle and if you print it it gives you a hash ref, so is not an error. After preparing you should do an execute on the statement than fetch the results. Better, use a prepare-execute-fetch all-in-one command like: selectall_arrayref(select * from ...) As its name says, it returns an array ref with your results. man DBI will give you every detail on this however you should get familiar with perl data types and references manipulation. Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net -Original Message- From: upscope [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 12:41 AM To: [EMAIL PROTECTED] Subject: Message I am just learning mysql and I'm trying to write a cgi to update the database from a form. Also just learning Perl. I have the following script started that is called by an HTML form. I get the follwing message when I execute it. Where can I find the message descriptions. DBI::st=HASH(0x1b31f28) this is my script so far: #!c:\perl\bin\perl use DBI(); print Content-type:text/html\n\n; #Connect to database members. $database = members; $table = members; $dbh = DBI-connect(DBI:mysql:$database)or dienice(Can't connect:$DBI::errstr); $sth = $dbh-prepare(select membername,address,city,state,zipcode,phonenumber from members)or dienice(Can't prepare statement: ,$dbh-errstr); print $sth; exit; upscope -- 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: replication problem
On Wed, Aug 06, 2003 at 11:23:31AM -0400, walt wrote: On Wednesday 06 August 2003 11:13 am, Andy Smith wrote: On Wed, Aug 06, 2003 at 03:59:53PM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 10:52:54AM -0400, walt wrote: Andy, I don't see log-slave-updates in your master setup. I see log-bin, but I think that only applies to updates done directly to the database (not replication updates). I'm not 100% sure about that, but it may be worth looking into. OK, so why would that prevent my _slave_ from even doing slave start? OK, so I added log-slave-updates to the master and it did not fix my problem. Thanks for your help though! Any other suggestions? Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? $ netstat -an | grep 3306 tcp0 0 0.0.0.0:33060.0.0.0:* LISTEN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database per user
mailing [EMAIL PROTECTED] wrote: it's possible in mysql set the db folder into home user? You use symbolic links: http://www.mysql.com/doc/en/Symbolic_links_to_databases.html example: user fred, home is /home/fred i want store db data in /home/fred/db. it's possible? i've tested in /home/fred/.my.cnf this: datadir=/$HOME/db but don't function i expose the /etc/my.cnf and /home/fred/.my.cnf for more details. -- 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: MySQL Replication
Good question :) I got a message from a person off the list that suggested I use network disk mirroring or a NAS/SAN/NFS system to handle that. I'm not sure if the mirroring would be 100% perfect, but the NAS/SAN solution should as either server would be reading and writing to the same physical data. But, then I have another point of failure. Heh. I realize that creating the perfect HA system is probably the most difficult thing to do, and doesn't come cheaply either. However, I'm going to think it through and try anyway :) I've read your presentations on your website and have used that info for my plan here, but its a little difficult to get details from just the slides (as you even mentioned on your site) :) Do you bother with multi-masters? How do you ensure redundancy on the write/master server? ... Ian Neubert Director of IS TWAcomm.com, Inc. http://www.twacomm.com/ -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 2:53 PM To: Ian Neubert Cc: Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: Re: MySQL Replication On Thu, Aug 07, 2003 at 01:00:12PM -0700, Ian Neubert wrote: I was trying to design it so that the slaves wouldn't know they had connected to a different master, as they both masters would have the same IP address that gets failed over based on the Linux Virtual Server software and VRRP (like heartbeat from Linux-HA). That path is a very, very, very difficult one. How can you absolutely guarantee that each master's binlog will be indentical in name, size, and content? If you can't, this scenario really falls apart. (I've suggested enhancements to MySQL that would fix this but don't know if they're terribly high on the priority list...) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,501,412 queries (399/sec. avg) -- 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: Form values are truncated
Hi Dan, all,
I don't know PERL, just coming back to speed on PHP after 3+ years with
ColdFusion.
Trim() is a function listed in my PHP Functions book, not something I wrote.
It's supposed to: removes all whitespace characters from both the left and
right ends of a string... A space (ASCII 32) is considered a char to
remove, but since it's in the middle of the string, I don't understand why
it's being removed.
or you could fix or replace the trim function with one that does not remove
that data.
Is there a library of PHP user defined functions out there?
Thanks,
James
-Original Message-
From: Dan Muey [mailto:[EMAIL PROTECTED]
Sent: Monday, August 11, 2003 9:02 AM
To: James Johnson; [EMAIL PROTECTED]
Subject: RE: Form values are truncated
All,
Howdy,
I'm building a member signup form. Fields that contain more
than one word are being truncated when being inserted into
the MySQL table.
I'm using this code that I got from one of my PHP books.
$as_addr1 = addslashes($_POST['addr1']);$tr_addr1 = trim($as_addr1);
I think trim is trimming off the stuff after the first whitespace? What is
the trim function supposed to do?
Mysql isn't doing it your PHP is.
Use Perl and I bet it starts working :) ok, or you could fix or
replace the trim function with one that does not remove that data.
HTH
DMuey
So, if I post 1122 Boogie St. it gets inserted as 1122.
If I remove the addslashes portion, only using trim, I get
the same result.
Here's the query:
$query = INSERT INTO subscribers
(email,password,fName,lName,addr1)
VALUES
('$tr_email','$tr_pass','$tr_fName','$tr_lName','$tr_addr1';
echo $query;
Here's the echo:
INSERT INTO subscribers (email,password,fName,lName,addr1)
VALUES('[EMAIL PROTECTED]','','Jim','Johnson','78797 State')
I have other fields in the table that are doing the same
thing. All fields are varchars of varying length. Field
addr1 is varchar(50).
Suggestions?
Thanks,
James
--
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: InnoDB crash - recover + bug
Greetings all. I have a bit of a problem here, a database i'm administering was somehow = corrupted, and i'm unable to recover it in any way. what happened? A power outage? You deleted the ib_logfiles? Modified my.cnf? Hard disk broke? Thats the weird thing, nothing abnormal happened, i just saw the mysqld using a lot of resources and shut it down. I suppose it must have been a query, or the database beeing to large or something. What does uname -a say about the Linux kernel in Debian-unstable? It says its running a 2.4.19 kernel, i686 on GNU/Linux Is there any way at = all to recover a corrupt InnoDB database? (I read on innodb.com that it = is impossible, but hope it is not) You should always take backups of valuable data, and also keep the MySQL binlog so that you can replay the modifications after the backup. So i understand, i'm used to running MyISAM tables, and have never had any problems with data corruption before now. Nothing a good myisamchk couldent fix anyhow. I do have a backup, just not old enough. I've been on vacation, so therefore the data got rotate out the system and overwritten. I only have corruptet backups. When I run a query from any InnoDB table in the database MySQL crashes = with the following stack trace and errors.=20 Did you resolve the stack trace with the right mysqld.sym file? The trace below is nonsensical. I think so, but i'll have to get back to you with that. What is the query? What query? The one that triggers the segfault below is any SELECT, SHOW TABLE STATUS or what ever reads the files. What is the complete .err log? I cannot find any entry in the error before i restartet the mysql process, then it complained the below. I'm running a GNU/Linux system and MySQL 4.0.13 from the Debian = unstable. Error: trying to access field 4294967295 in rec 030807 13:53:24 InnoDB: Assertion failure in thread 180234 in file = rem0rec.c line 111 InnoDB: Failing assertion: 0 ... thd=3D0x86e3990 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=3D0xbe7fe898, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8102bc3 0x401ad75a 0x82b9a60 0x8230d50 0x822e42c 0x816952f 0x8169c84 0x816bf6a 0x816c2be 0x815e77f 0x8178c60 0x810f8e8 0x8112a15 0x810db3d 0x810d6cc 0x810d059 0x401a7d53 0x4038a3f7 New value of fp=3D(nil) failed sanity check, terminating stack trace! ... 0x8102bc3 mysql_binlog_send__FP3THDPcUxUs + 1419 0x401ad75a _end + 936375294 0x82b9a60 _tr_flush_block + 640 0x8230d50 page_cur_delete_rec + 5780 0x822e42c page_copy_rec_list_end_to_created_page + 392 0x816952f yyparse + 3855 0x8169c84 yylex + 1572 0x816bf6a opt_search_plan_for_table + 742 0x816c2be opt_search_plan_for_table + 1594 0x815e77f row_upd_clust_step + 431 0x8178c60 btr_compress + 3852 0x810f8e8 srv_master_thread + 172 0x8112a15 innobase_start_or_create_for_mysql + 1297 0x810db3d srv_sprintf_innodb_monitor + 425 0x810d6cc srv_suspend_mysql_thread + 1372 0x810d059 srv_table_reserve_slot_for_mysql + 473 0x401a7d53 _end + 936352247 0x4038a3f7 _end + 938328219 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: preserving indexes
Chris, What version of MySQL are you using and what table type (InnoDB, MyISAM, etc.)? I followed your procedure and was unable to replicate the problem with MySQL 4.0.14 on a MyISAM table. -Rob -Original Message- From: Chris Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 4:55 PM To: MySQL List Subject: preserving indexes Hi Every night. I dump the schema of a table, drop it, create the table, and load it up with data. I'm using phpMyAdmin for the most part. I notice that the index cardinality shows up as None in phpMyAdmin for each index I have. If I drop the index and re-create it, all of the indexes show their correct cardinality. I don't think phpMyAdmin is at fault because searching on the table before I fix it is sluggish until afterwards. Is the a command I could run that would re-index everything after I dump the data into the table? Or what is going on? thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication w/o stopping the master
On Tue, Aug 05, 2003 at 02:14:11PM +0100, [EMAIL PROTECTED] wrote: See the command LOAD DATA FROM MASTER ( http://www.mysql.com/doc/en/LOAD_DATA_FROM_MASTER.html ). This should do what you want - it does it for me. As I understand it, this puts a read lock on each table as it copies it. This provides some interruption to service, but nothing like a stop/start. But there's a race condition on a busy MySQL server when you use LOAD DATA to kickstart replication. What if tables are modified all the time? The *only* way to get a consistent image is to lock ALL of them at the same time. mysqlsnapshot will do that for you: http://jeremy.zawodny.com/mysql/mysqlsnapshot/ Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 207,455,614 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thu, Aug 07, 2003 at 03:58:37PM -0700, Jennifer Goodie wrote: One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) A crash was recreated by running a specific query. Oh. You didn't mention crashes in your first note. That changes everything. When myisamchk ran upon restart it said the index file for the table that was being queried was corrupt. After careful observation, it was discovered that this is often the case, indexes for tables mentioned in the update log right before a crash were corrupt upon restart. I'm more inclined to believe that they are corrupt due to us killing mysqld with the tables still open, since we can't authenticate to shutdown. We also get a lot of table handler errors from myisamchk after a crash and kill, go figure. Yeah, if you're killing MySQL by force, you really ought to check all tables and repir broken ones. Otherwise it's a craps shoot. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. We aren't running Linux threads. We didn't seem to be experiencing any of the issues it helps. At least not the obvious ones. :-) We've found that on moderately busy machines here, upgrading to a LinuxThreads-based MySQL reduced CPU utiliization by 30% or so. For a while we'd only have this happen once a month, then it was once a week. Lately it has been a few times a day, but everyone is messing with box. Ugh. In my opinion, for us it definitely happens when an expensive query is run on an active table. Looking at the logs, there's always a bunch of disconnects all at once right before connections stop. Hmm. I hadn't noticed that yet. But I hadn't thought to look at disconnect rates either. I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. I don't know how to do this with pthreads but with LT, I'd like to identify a few of the pids for the struck threads and then get a snapshot of the call stack to see where they're waiting. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 213,656,247 queries (397/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tracking a delete
At 12:54 -0400 8/8/03, Jeff McKeon wrote: How can I see if a record was deleted from a database? Jeff You can attempt to SELECT it, and if you get no result, it's not there. But that doesn't necesarily mean that it was once there and has now been deleted. If you want to determine that, you'll need to create a log of record deletions. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld cannot access innodb
have you looked at the file permissions of the directory itself? looks only maciej:staff may access this one, but no mysql user. -yves -Ursprüngliche Nachricht- Von: Maciej Wiznerowicz [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 7. August 2003 14:56 Betreff: mysqld cannot access innodb Dear Sirs, I can not start MySQL server. More specifically, MySQL was running fine all the time since installation. It stopped working after I used FmPro Migrator to convert some File Maker databases to MySQL It looks like mysqld has no access permissions to InnoDB and I do not know how to fix it. LOG: The following message is written to MySQL log 030807 11:56:24 mysqld started 030807 11:56:24 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030807 11:56:24 mysqld ended Permissions: /library/mysql/var total 41056 drwx-- 13 maciej staff 442 Aug 7 11:50 . drwxrwxr-x 14 rootmysql 476 May 31 08:11 .. -rw-rw 1 mysql mysql 10178 Aug 7 11:56 Trono-Monica.local..err -rw-rw 1 mysql mysql 25088 Jun 30 11:56 ib_arch_log_00 -rw-rw 1 mysql mysql 5242880 Aug 7 09:31 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Jun 30 11:56 ib_logfile1 -rw-rw 1 mysql mysql 10485760 Aug 7 09:17 ibdata1 drwx-- 20 mysql mysql 680 Aug 7 09:31 mice -rw-r--r-- 1 mysql mysql 4821 Jun 30 11:45 my.conf drwx-- 20 mysql mysql 680 Jul 13 20:51 mysql drwx-- 2 mysql mysql68 Jul 13 22:28 pvi drwx-- 2 mysql mysql68 Jul 13 00:22 test drwx-- 5 mysql mysql 170 Jul 17 18:52 virus I am using MacOSX 10.2.6 Please help, Again, I am extremely grateful for your kind help. Maciej Maciej Wiznerowicz MD, PhD Dpt of Genetics and Microbiology Faculty of Medecine at University of Geneva Rue Michel Servet 1, CH-1211 Geneva Switzerland Tel. +41-22-3795717 Fax +41-22-3795702 -- 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: Corrupt index = mysqld freeze?
Setting skip-name-resolve will avoid this code path and the bug. It looks like some DNS funkyness... I've implented this and so far I can't get the box to hang in authentication no matter how mean I am to it. I feel so dumb, for not thinking of this before, I swore I had already done it about a year ago when all of the other boxes were set up to skip name-resolve. I'll post if I can get it to lock up again (let's hope I can't). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
