RE: Replication blocked
There is only one thread for replication on the slave. It does one step a time. If you use mysqlbinlog on one of your binary files on your master, you will see exactly how it all works. Multi-threaded would probably cause thousands of problems. Unless it was threaded per table, but that would still cause problems because of multi-table deletes and updates. Donny -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 1:00 AM To: [EMAIL PROTECTED] Subject: Replication blocked Hi, I have 2 DB server, running as master and slave. I just add an index to one of my table on master, it took about 12 minutes. During adding the index, I have insert/update queries to other tables. On master this has no problem at all. The problem is, on slave these queries were blocked by the previous 12 minutes query. Does this mean that there is only 1 thread to run the SQL from master? Can this be set to multithread? Thank you very much. Regards, bk -- 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: GUI for MySQL
This is my first attempt to design and test MySQL. I have used MS SQL for number of years. I do appreciate if members of this list can recommend a good GUI application for MySQL. I want the GUI application to design DB, design Quiries, etc. Take a look at Database Workbench - www.upscene.com With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quoting keywords
Hopefully a simple problem!? ... MySQL produces a server-error when performing a query like SELECT char FROM mytable WHERE id=4711 This seems to be 'char' is a MySQL-keyword. Ok! But how can i quote it??? (I could not find any information about this issue in the documentation...) Help appreciated by guenter buehrle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quoting keywords
Hi, Hopefully a simple problem!? ... MySQL produces a server-error when performing a query like SELECT char FROM mytable WHERE id=4711 This seems to be 'char' is a MySQL-keyword. Ok! But how can i quote it??? (I could not find any information about this issue in the documentation...) Using backticks should work: select `char` from mytable With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Escaped BLOB data in XML
If you base64 encode your binary, it will be valid inside the xml. As far as I know this is the accepted way to transfer binary objects using xml. chris -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: 13 August 2004 05:09 To: Keith Ivey; [EMAIL PROTECTED] Subject: Re: Escaped BLOB data in XML This leads me to another question. What are the valid ASCII characters that XML parser understands. Are they only a-1,A-Z,0-9,., etc or some other characters. Regards, Karam --- Karam Chand [EMAIL PROTECTED] wrote: Hello, Hmmm. I was figuring that out. mysql_escape_string() only escapes characters like \r, \n, \\, 0 etc. it still keep other non-character data same like it keep ascii 15 to ascii 15 that no parser is able to handle. Isnt there any better way then base64 to handle this. Just like replacin to lt; solves the problem in the data? Regards, Karam --- Keith Ivey [EMAIL PROTECTED] wrote: Karam Chand wrote: i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. This doesn't have anything to do with MySQL. XML isn't really designed for directly containing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- 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]
Mysqlcheck -r very slow on big tables
Hello! :) mysqlcheck -r is very slow when repair big tables (over 4GB data - repair it 1h and 40m). Is there any config option to fasten it. Regards: Kosyo __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by with one exception
On Wed, 11 Aug 2004 23:40:27 -0700, Scott Haneda [EMAIL PROTECTED] wrote: I made a mailing list archiver, I thread discussions by subject. I chose to not use message-id's since so many people hijack threads. Why not select/group on subject, and then thread on messageid. Given this case: Subject ID RE: Order by with one exception 1 RE: Order by with one exception 2 RE: Order by with one exception 3 Order by with one exception 4 RE: Order by with one exception 5 RE: Order by with one exception 6 RE: Order by with one exception 7 As you can see, these are in correct order, but in this case, I want to push the one without the Re: to the top. I can not just order by subject, id, since not a subject could start with a letter after R. Suggestions? SELECT * FROM table ORDER BY subject NOT LIKE 're:%', ID Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
Replace deletes and inserts. ? what do you mean? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help, slave wont stay running!
Anybody else have any ideas? I cant keep the slave up only thing I have not tried is upgrading to 4.0.20, however, nothing changed to cause this problem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexes
mysql show index from urun; +---++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+-- -+-+--++--++-+ | urun | 0 | PRIMARY |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 0 | UC_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | IDX_urun_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | ktgr |1 | ktgr| A | 512 | NULL | NULL | | BTREE | | +---++--+--+-+-- -+-+--++--++-+ 4 rows in set (0.00 sec) Also my show create table urun looks like this.. .. PRIMARY KEY (`urun_id`), UNIQUE KEY `UC_urun_id` (`urun_id`), KEY `IDX_urun_urun_id` (`urun_id`), KEY `ktgr` (`ktgr`) ) TYPE=MyISAM | Isn't this KEY `IDX_urun_urun_id` (`urun_id`), and UNIQUE KEY `UC_urun_id` (`urun_id`), indexes are unnecessary? Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple server versions on 1 box startup error#
Have you set it up so that these instances run on different ports and different sockets? If you are not using mysqld multi you will have to specify which mysql/bin/safe_mysqld you want to start on the command line. -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/12/04 5:33 PM Subject: multiple server versions on 1 box startup error# I am trying to run two different MySQL server versions on the same solaris machine. I already have a 4.0.2 running, and i have successfully installed 4.1.3 beta. I ran mysql_install_db successfully (after setting LD_LIBRARY_PATH), but when i try to run mysqld_safe --user=mysql I get the error: A mysqld process already exists [1]+ Exit 1 ./mysqld_safe --user=mysql This is certainly true, because my 4.0.2 is running, but i dont understand why this is a problem. I am running the mysqld_safe for version 4.1.3, and when i built 4.1.3 i used the configure options: (among others) --prefix=/usr/loca/mysql-4.1.3 --with-tcp-port=3306 --with-unix-socket-path=/tmp/mysql-4.1.3.sock --datadir=/var/mysql-4.1.3 And these options are all different from the 4.0.2 configuration. So i assumed that things would run ok. The only thing i can think of is that the /vaar/mysql-4.1.3/my.cnf is not being read correctly. Perhaps i have an error in it, but i'd think that mysqld_safe would let me know. If its not being read, then some options from my default /etc/my.cnf are not being overridden, and that could be the problem. Here is the info on the /var/mysql-4.1.3/my.cnf file: -rw-r--r-- 1 root other 2042 Aug 12 18:08 my.cnf and the permissions ownership are identical to that for /etc/my.cnf I am at a loss thanks much. sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexes
Yes. Those keys are redundant. -Original Message- From: Cemal Dalar To: Group MySQL List Sent: 8/13/04 7:12 AM Subject: Indexes mysql show index from urun; +---++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+-- -+-+--++--++-+ | urun | 0 | PRIMARY |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 0 | UC_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | IDX_urun_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | ktgr |1 | ktgr| A | 512 | NULL | NULL | | BTREE | | +---++--+--+-+-- -+-+--++--++-+ 4 rows in set (0.00 sec) Also my show create table urun looks like this.. .. PRIMARY KEY (`urun_id`), UNIQUE KEY `UC_urun_id` (`urun_id`), KEY `IDX_urun_urun_id` (`urun_id`), KEY `ktgr` (`ktgr`) ) TYPE=MyISAM | Isn't this KEY `IDX_urun_urun_id` (`urun_id`), and UNIQUE KEY `UC_urun_id` (`urun_id`), indexes are unnecessary? Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- 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: datadir specification, etc
If possible, and for testing purposes try starting the 4.1.3 server with the options given on the command line. (datadir, pid, socket, port, etc...) -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/12/04 6:37 PM Subject: datadir specification, etc I am 100% convinced that mysql 4.1.3 beta is not properly reading the my.cnf configuration files. If i remove the /etc/my.cnf file and try to start mysql 4.1.3 with (im working from /usr/local/mysql-4.1.3/bin) ./mysqld_safe i get the following output: (mccoy is the name of the machine im on) touch: /usr/local/mysql-4.1.3/var/mccoy.err cannot create chown: /usr/local/mysql-4.1.3/var/mccoy.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql-4.1.3/var ./mysqld_safe: /usr/local/mysql-4.1.3/var/mccoy.err: cannot create If i remove the /var/mysql-4.1.3/my.cnf file, i get the same output as above, so its not being read either way. And i did specify /var/mysql-4.1.3/ as my datadir with .configure when building 4.1.3 if i put the /etc/my.cnf file back, i get the following: A mysqld process already exists So clearly, the /etc/my.cnf is being read, but the /var/mysql-4.1.3/my.cnf is not. so i guess it doesnt matter what i specify in there at this point. One strange thing is that ./msqyd_safe tries to use the databases in /usr/local/mysql-4.1.3/var/ But i specified a different datadir with configure! my configure --prefix=/usr/local/mysql-4.1.3 but why should that matter? In fact, when i installed 4.1.3 (make install), the directory /usr/local/mysql-4.1.3/var/ was NOT created. I dont think most of the info ive given matters, because my run-time configuration doesnt appear to be the problem. I dont believe that my build configuration took effect properly. Does any of this make sense? Still completely lost. thanks sean peters [EMAIL PROTECTED] *** Here's some my.cnf data, if it really matters *** Here is part of the /var/mysql-4.1.3/my.cnf file: [client] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ [mysqld] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ And here is info from /etc/my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock -- 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, slave wont stay running!
I reset the master, flush logs, reset master, show master status.. shows FINANCE-bin.186 at position 79 so I started the slave CHANGE MASTER TO MASTER_HOST='192.168.1.168', MASTER_USER='repl', MASTER_PASSWORD='Daredevil22', MASTER_LOG_FILE='FINANCE-bin.186', MASTER_LOG_POS=79; start slave; and I get this error after a few seconds.. 040813 8:55:15 Slave SQL thread initialized, starting replication in log 'FINANCE-bin.186' at position 79, relay log '.\databasebackup-relay-bin.001' position: 4 040813 8:55:15 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FINANCE-bin.186' at position 79 040813 8:55:39 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040813 8:55:39 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040813 8:55:39 Slave I/O thread exiting, read up to log 'FINANCE-bin.186', position 79 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: Replace deletes and inserts. ? what do you mean? Replace does a delete followed by an insert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help, slave wont stay running!
More info.. I dont see anythign wrong with the binlog the slave has E:\mysql\datamysqlbinlog databasebackup-relay-bin.001 /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 4 #691231 19:00:00 server id 1 log_pos 0 Rotate to FINANCE-bin.186 pos:79 # at 46 #691231 19:00:00 server id 1 log_pos 0 Rotate to FINANCE-bin.186 pos:79 E:\mysql\data -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyOLEDB and Stored Procedures
Hi, Just wanted to check if anyone would know if the MyOLEDB / myODBC has been updated to support stored procedures / functions in MySQL server version 5.0.0 alpha. I am getting error DB_E_ERRORSOCCURRED(0x80040E21) when trying to execute Non Query from ADO.NET (OLEDB provider MyOLEDB). Typically this error means that that provider is not installed. However I am sure the MyOledb is installed on my machine. Any info on this will be helpful Regards Naresh -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the originator of the message. This footer also confirms that this email message has been scanned for the presence of computer viruses. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of DA Group. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by with one exception
Many threaded discussion groups create a thread_ID for the first topic in any thread. That thread_ID is usually stored invisibly with each response. Each response also maintains (invisibly, too) the message id of it's parent. Of course the parent_ID of the root message in any thread would be null. I think I understand that you are archiving mailing list responses, that precludes the idea of knowing the parent message id at the time a new message is created. So, all you could do is to parse the responses and see if any of them match to a subject line without any RE:, FW:, PR:, etc then associate them with an identified thread. The problem with this is in the timing. Using your sample data, you couldn't determine a thread_id for messages 1-3 until message 4 arrives. More complex parsing could help you to determine if more than one message is in the body (replies with history) and determine parental lineage from the content. I guess you could combine subject headers and message senders into some kind of key value for this purpose but you have to be careful to make the proper match. What you are doing is not as easy as running your own discussion group as you can't embed useful metadata into each message/response. Sorry I couldn't help more. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jochem van Dieten [EMAIL PROTECTED] wrote on 08/13/2004 06:58:07 AM: On Wed, 11 Aug 2004 23:40:27 -0700, Scott Haneda [EMAIL PROTECTED] wrote: I made a mailing list archiver, I thread discussions by subject. I chose to not use message-id's since so many people hijack threads. Why not select/group on subject, and then thread on messageid. Given this case: Subject ID RE: Order by with one exception 1 RE: Order by with one exception 2 RE: Order by with one exception 3 Order by with one exception 4 RE: Order by with one exception 5 RE: Order by with one exception 6 RE: Order by with one exception 7 As you can see, these are in correct order, but in this case, I want to push the one without the Re: to the top. I can not just order by subject, id, since not a subject could start with a letter after R. Suggestions? SELECT * FROM table ORDER BY subject NOT LIKE 're:%', ID Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help, slave wont stay running!
Have you considered that a proxy server may be in the way. I have been watching this thread but I can't remember if you said anything about your network connectivity (sorry!). I have seen several programs make what they thought was a connection then fail because they don't know they are connecting through a proxy and not the real server. Also if your proxy is dropping your session, it could cause the same interrupted behavior. my 2 cents Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt ryan [EMAIL PROTECTED] wrote on 08/13/2004 08:58:43 AM: I reset the master, flush logs, reset master, show master status.. shows FINANCE-bin.186 at position 79 so I started the slave CHANGE MASTER TO MASTER_HOST='192.168.1.168', MASTER_USER='repl', MASTER_PASSWORD='Daredevil22', MASTER_LOG_FILE='FINANCE-bin.186', MASTER_LOG_POS=79; start slave; and I get this error after a few seconds.. 040813 8:55:15 Slave SQL thread initialized, starting replication in log 'FINANCE-bin.186' at position 79, relay log '.\databasebackup-relay-bin.001' position: 4 040813 8:55:15 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FINANCE-bin.186' at position 79 040813 8:55:39 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040813 8:55:39 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040813 8:55:39 Slave I/O thread exiting, read up to log 'FINANCE-bin.186', position 79 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help, slave wont stay running!
[EMAIL PROTECTED] wrote: Have you considered that a proxy server may be in the way. I have been watching this thread but I can't remember if you said anything about your network connectivity (sorry!). I have seen several programs make what they thought was a connection then fail because they don't know they are connecting through a proxy and not the real server. Also if your proxy is dropping your session, it could cause the same interrupted behavior. my 2 cents Both servers are connected to the same switch, no proxy servers between them. The slave will connect, and will process all the way up to the current event as soon as it hits the current event it dies, all I have to do is wait 5 min for more events to build up, and start slave and it takes off again Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can connect with PHP to MYSQL
I can connect in the php by using 'root' and the root pw. But when I tried adding a user ( sarah ) with GRANT I cannot connect from php. The php is simple, what am I overlooking? Again putting the root user name and pw it'll work in php but not for sarah: the php mysql_pconnect(localhost,sarah,camera) or die(ERROR: Could not connect to database!); mysql_select_db(howto); *But* on the command line the sarah user works OK: C:\Documents and Settings\Administratormysql -u sarah -p Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.1.3a-beta-nt-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from howto.page; +-+-+ | page_id | page_url| +-+-+ | 1 | http://www.lg.netfarms.org | +-+-+ 1 row in set (0.00 sec) mysql show grants for [EMAIL PROTECTED]; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT ALL PRIVILEGES ON *.* TO 'sarah'@'localhost' IDENTIFIED BY PASSWORD '*6043233C67ADBE7E9242089D718763C3C5E1A1E2' | +---+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help
If I want to update these day fields in this table: day, date, month, year -1 August 2004 -1 August 2004 I want on to be Sunday Lunchtime the other to be Sunday Evening. What query do I use that won't update both fields with the same data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Please post the entire contents of SHOW CREATE TABLE for this table and we will have enough information to answer your question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/13/2004 09:48:21 AM: If I want to update these day fields in this table: day, date, month, year -1 August 2004 -1 August 2004 I want on to be Sunday Lunchtime the other to be Sunday Evening. What query do I use that won't update both fields with the same data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
john henry bonham wrote: If I want to update these day fields in this table: day, date, month, year -1 August 2004 -1 August 2004 I want on to be Sunday Lunchtime the other to be Sunday Evening. What query do I use that won't update both fields with the same data? maybe something like this : UPDATE ... LIMIT 1; so only the first one will be updated. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(dumb) embedded question
hi is it possible to have 2 applications update/insert/delete records from a embedded database. example: app1 on a repetitive process checks for record in table1 while app2 inserts a record in table1. if u think about it, it should be possible. technically, the mysqld process is now imbedded into a app. it should be possible to have any amount of embedded mysql apps accessing the same database. tom Disclaimer http://www.shoprite.co.za/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaped BLOB data in XML
At 10:46 -0700 8/12/04, Karam Chand wrote: Hello, i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. I assume you mean mysql_real_escape_string(), not mysql_real_escape()? mysql_real_escape_string() is intended for escaping data that you are including in statements to be sent *to* the server. It is not for escaping data that you get back *from* the server in the result from a query. The issue you're describing is an XML issue, not a MySQL issue. You'll probably get more help if you post your question (which boils down to how can I write out binary data to an XML file?) on an XML-related list. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can connect with PHP to MYSQL
i usually do $link = mysql_connect($server, $userid, $password); and it works, except when the pw is incorrect suomi leegold wrote: I can connect in the php by using 'root' and the root pw. But when I tried adding a user ( sarah ) with GRANT I cannot connect from php. The php is simple, what am I overlooking? Again putting the root user name and pw it'll work in php but not for sarah: the php mysql_pconnect(localhost,sarah,camera) or die(ERROR: Could not connect to database!); mysql_select_db(howto); *But* on the command line the sarah user works OK: C:\Documents and Settings\Administratormysql -u sarah -p Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.1.3a-beta-nt-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from howto.page; +-+-+ | page_id | page_url| +-+-+ | 1 | http://www.lg.netfarms.org | +-+-+ 1 row in set (0.00 sec) mysql show grants for [EMAIL PROTECTED]; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT ALL PRIVILEGES ON *.* TO 'sarah'@'localhost' IDENTIFIED BY PASSWORD '*6043233C67ADBE7E9242089D718763C3C5E1A1E2' | +---+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datadir specification, etc
In my experience, where the my.cnf file is concerned, mysqld does not care what you define for the value of the --datadir flag. The order of precedence for locating my.cnf files is: 1) /etc/my.cnf 2) my.cnf in the COMPILED-IN DEFAULT datadir 3) .my.cnf in the user's $HOME That compiled-in default makes all the difference. You can start mysqld with as many different renditions of the --datadir flag as you want, but if one of them isn't the default path which was compiled in at build time (using the --localstatedir flag for configure) then mysqld will not automatically locate any my.cnf file in the specified datadir. For instance, I start all my servers with a --datadir=/path/to/mysql/data flag and have a my.cnf file in that directory. However, depending upon the platform and installation, the mysqld server will be looking in a number of different (and often non-existent) locations for the my.cnf file instead, such as /usr/local/mysql/var or similar. This is something which has caused many headaches on the machines I administer, occassionally leading to an intricate web of links to allow the server to locate the appropriate file. These links are not the only way to direct the server towards the appropriate my.cnf file. To be honest, they're only a hack and I wouldn't recommend them. One way to handle this is to rebuild MySQL from a source distribution, using the appropriate configure flags to set new default paths to be compiled into the binaries. A much easier way is to use the --defaults-file and/or --defaults-extra-file flags when starting the mysqld server. These flags--and not the value of any datadir flag--are what really tell mysqld where to locate the options file(s) it should use. The one drawback I've found with these flags is remembering to use the same flag(s) on any client programs which are run and training users to do the same. This has been enough of a pain to make it worth my while to deal with the web of links at this point. When all the machines are upgraded to MySQL 4.0.20 later this year, they will be receiving self-compiled binaries with our own flavor of default paths so none of these workarounds will be necessary. For more information about this sort of thing, check this page in the manual: http://dev.mysql.com/doc/mysql/en/Option_files.html Also, Paul DuBois' MySQL book has good information presented in a very accessible manner. Cheers, --V sean c peters wrote: I am 100% convinced that mysql 4.1.3 beta is not properly reading the my.cnf configuration files. If i remove the /etc/my.cnf file and try to start mysql 4.1.3 with (im working from /usr/local/mysql-4.1.3/bin) ./mysqld_safe i get the following output: (mccoy is the name of the machine im on) touch: /usr/local/mysql-4.1.3/var/mccoy.err cannot create chown: /usr/local/mysql-4.1.3/var/mccoy.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql-4.1.3/var ./mysqld_safe: /usr/local/mysql-4.1.3/var/mccoy.err: cannot create If i remove the /var/mysql-4.1.3/my.cnf file, i get the same output as above, so its not being read either way. And i did specify /var/mysql-4.1.3/ as my datadir with .configure when building 4.1.3 if i put the /etc/my.cnf file back, i get the following: A mysqld process already exists So clearly, the /etc/my.cnf is being read, but the /var/mysql-4.1.3/my.cnf is not. so i guess it doesnt matter what i specify in there at this point. One strange thing is that ./msqyd_safe tries to use the databases in /usr/local/mysql-4.1.3/var/ But i specified a different datadir with configure! my configure --prefix=/usr/local/mysql-4.1.3 but why should that matter? In fact, when i installed 4.1.3 (make install), the directory /usr/local/mysql-4.1.3/var/ was NOT created. I dont think most of the info ive given matters, because my run-time configuration doesnt appear to be the problem. I dont believe that my build configuration took effect properly. Does any of this make sense? Still completely lost. thanks sean peters [EMAIL PROTECTED] *** Here's some my.cnf data, if it really matters *** Here is part of the /var/mysql-4.1.3/my.cnf file: [client] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ [mysqld] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ And here is info from /etc/my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can connect with PHP to MYSQL
What error are you getting? My problem when moving to 4.1 was forgetting that I was using an older MySQL client. I'm not positive, but I think that in order to connect to MySQL 4.1 with PHP you need to use the mysqli functions, not mysql, and mysqli requires PHP 5. If you want to keep using MySQL 4.1 with PHP 4, then try changing your user's password with the OLD_PASSWORD() function. http://dev.mysql.com/doc/mysql/en/Encryption_functions.html Wes On Aug 13, 2004, at 9:48 AM, leegold wrote: I can connect in the php by using 'root' and the root pw. But when I tried adding a user ( sarah ) with GRANT I cannot connect from php. The php is simple, what am I overlooking? Again putting the root user name and pw it'll work in php but not for sarah: the php mysql_pconnect(localhost,sarah,camera) or die(ERROR: Could not connect to database!); mysql_select_db(howto); *But* on the command line the sarah user works OK: C:\Documents and Settings\Administratormysql -u sarah -p Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.1.3a-beta-nt-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from howto.page; +-+-+ | page_id | page_url| +-+-+ | 1 | http://www.lg.netfarms.org | +-+-+ 1 row in set (0.00 sec) mysql show grants for [EMAIL PROTECTED]; +-- -+ | Grants for [EMAIL PROTECTED] | +-- -+ | GRANT ALL PRIVILEGES ON *.* TO 'sarah'@'localhost' IDENTIFIED BY PASSWORD '*6043233C67ADBE7E9242089D718763C3C5E1A1E2' | +-- -+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Probable SPAM] Re: datadir specification, etc
Which flag did you use to define the datadir for configure? --datadir doesn't do the trick. --localstatedir does. Also, you might want to consider setting --prefix as well. --V sean c peters wrote: The problem is that i did build 4.1.3 florm a source distribution, and set the datadir via configure to be /var/mysql-4.1.3/, and it doesnt read my.cnf from there anyway. In regard to the section of the manual mentioned: http://dev.mysql.com/doc/mysql/en/Option_files.html It states: DATADIR represents the location of the MySQL data directory. Typically this is `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with --datadir when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any command-line arguments. So, even by specifying the datadir at the command line when starting mysql, according to this documentation, mysql wont even bother looking in the command line specified datadir for a my.cnf On Friday 13 August 2004 10:15, V. M. Brasseur wrote: In my experience, where the my.cnf file is concerned, mysqld does not care what you define for the value of the --datadir flag. The order of precedence for locating my.cnf files is: 1) /etc/my.cnf 2) my.cnf in the COMPILED-IN DEFAULT datadir 3) .my.cnf in the user's $HOME That compiled-in default makes all the difference. You can start mysqld with as many different renditions of the --datadir flag as you want, but if one of them isn't the default path which was compiled in at build time (using the --localstatedir flag for configure) then mysqld will not automatically locate any my.cnf file in the specified datadir. For instance, I start all my servers with a --datadir=/path/to/mysql/data flag and have a my.cnf file in that directory. However, depending upon the platform and installation, the mysqld server will be looking in a number of different (and often non-existent) locations for the my.cnf file instead, such as /usr/local/mysql/var or similar. This is something which has caused many headaches on the machines I administer, occassionally leading to an intricate web of links to allow the server to locate the appropriate file. These links are not the only way to direct the server towards the appropriate my.cnf file. To be honest, they're only a hack and I wouldn't recommend them. One way to handle this is to rebuild MySQL from a source distribution, using the appropriate configure flags to set new default paths to be compiled into the binaries. A much easier way is to use the --defaults-file and/or --defaults-extra-file flags when starting the mysqld server. These flags--and not the value of any datadir flag--are what really tell mysqld where to locate the options file(s) it should use. The one drawback I've found with these flags is remembering to use the same flag(s) on any client programs which are run and training users to do the same. This has been enough of a pain to make it worth my while to deal with the web of links at this point. When all the machines are upgraded to MySQL 4.0.20 later this year, they will be receiving self-compiled binaries with our own flavor of default paths so none of these workarounds will be necessary. For more information about this sort of thing, check this page in the manual: http://dev.mysql.com/doc/mysql/en/Option_files.html Also, Paul DuBois' MySQL book has good information presented in a very accessible manner. Cheers, --V sean c peters wrote: I am 100% convinced that mysql 4.1.3 beta is not properly reading the my.cnf configuration files. If i remove the /etc/my.cnf file and try to start mysql 4.1.3 with (im working from /usr/local/mysql-4.1.3/bin) ./mysqld_safe i get the following output: (mccoy is the name of the machine im on) touch: /usr/local/mysql-4.1.3/var/mccoy.err cannot create chown: /usr/local/mysql-4.1.3/var/mccoy.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql-4.1.3/var ./mysqld_safe: /usr/local/mysql-4.1.3/var/mccoy.err: cannot create If i remove the /var/mysql-4.1.3/my.cnf file, i get the same output as above, so its not being read either way. And i did specify /var/mysql-4.1.3/ as my datadir with .configure when building 4.1.3 if i put the /etc/my.cnf file back, i get the following: A mysqld process already exists So clearly, the /etc/my.cnf is being read, but the /var/mysql-4.1.3/my.cnf is not. so i guess it doesnt matter what i specify in there at this point. One strange thing is that ./msqyd_safe tries to use the databases in /usr/local/mysql-4.1.3/var/ But i specified a different datadir with configure! my configure --prefix=/usr/local/mysql-4.1.3 but why should that matter? In fact, when i installed 4.1.3 (make install), the directory /usr/local/mysql-4.1.3/var/ was NOT created.
problem with tables crashing
Hi, I'm using mysql 4.0.12 and I got a database that holds the DMOZ data (master/slave config). Now this is a (to me anyways) pretty big database(4 million some links, with fulltext indexes). However I have a problem with it. For some reasons I can't get a grip on, the table crashes. (error 145). Due to an error on the slave I wanted to resync the slave by copying the master database and all, and followed the instructions on http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html. However, after bringing up the slave, I got a error 145 on the slave, and also on the master :-( Can someone please tell me why this happened, how I can stop this from happening again as repairing the table takes a LONG time Any help would be really appreciated Regards, Johan Jonkers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave crashing
I'm currently on research on SQL selection for our future Database project, and so far I've noticed that there are problem with slave, is it common for MySQL to have slave problem? I'm looking into Microsoft SQL Server, PostgreSQL and MySQL for our future project. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with tables crashing
$ perror 145 Error code 145: Error 145 occurred. 145 = Table was marked as crashed and should be repaired I think `myisamchk` needs to come into play here (both on slave and master at this point). http://dev.mysql.com/doc/mysql/en/myisamchk_syntax.html http://dev.mysql.com/doc/mysql/en/Repair.html Cheers, --V Johan Jonkers wrote: Hi, I'm using mysql 4.0.12 and I got a database that holds the DMOZ data (master/slave config). Now this is a (to me anyways) pretty big database(4 million some links, with fulltext indexes). However I have a problem with it. For some reasons I can't get a grip on, the table crashes. (error 145). Due to an error on the slave I wanted to resync the slave by copying the master database and all, and followed the instructions on http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html. However, after bringing up the slave, I got a error 145 on the slave, and also on the master :-( Can someone please tell me why this happened, how I can stop this from happening again as repairing the table takes a LONG time Any help would be really appreciated Regards, Johan Jonkers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pinging
Hi list, I get this error from MySQL administrator, everything was fine before I converted the db and uploaded it to our host company. What is going on here? Thanks a lot for any help Could not connect to the specified host MySQL error Nr.2003 Cant connect to MySQL server on local host (10061) Click the ping button to see if there is a networking problem Pinging localhost Reply from 127.0.0.1: Time=0ms TTL=128 Reply from 127.0.0.1: Time=0ms TTL=128 I turn on the service as well and it turns off after about 5 seconds
Tuning InnoDB situation
All: I have been nosing about for some time now and think I need some help. The Problem: Mytop is telling me that I am running no more than 1000 queries per second, and the key efficiency is 100%. But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. The queries normally take no longer than 5 seconds in a test environment on a slower machine. In production (where the problem is) they can last from 90 to 1400 seconds. A few of these bring our site to a crawl. Suspicions: I have seen this machine run at 3000 to 6000 qps and still move data out fast. Some of the queries it is now performing slow are were part of this performance in the past. So my first idea is that the server, rather than the query, needs to be tuned. Here is the my.cnf stuff: [mysqld] port= 3306 socket = /var/run/mysqld/mysqld.sock skip-locking skip-bdb set-variable= key_buffer=16M set-variable= max_allowed_packet=10M set-variable= max_connections=1200 set-variable= table_cache=256 set-variable= sort_buffer=2M set-variable= net_buffer_length=64K set-variable= myisam_sort_buffer_size=32M log-bin server-id = 2 pid-file= /var/run/mysqld/mysqld.pid #log = /var/log/mysql/mysql.log log-slow-queries basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english default-table-type = innodb query-cache-type = 1 query-cache-size = 20M set-variable = net_read_timeout=600 set-variable= net_write_timeout=600 innodb_data_home_dir = /var/lib/mysql/innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:15G:autoextend set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I am considering raising the table_cache to 1500 and the innodb_buffer_pool_size to 2.5GB. Comments on this would be appreciated as well. If this is not the issue then I suspect there is contention in some of the busy tables. Where do I look at the SHOW INNODB STATUS output to detect this situation? What am I looking for? Can I schedule InnoDB transactions? We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem I've switched to innodb but performance isnt very good while the insert runs, here's what I get for performance select count(*) from rondon; 1 row in .13 sec select count(*) from rondon; 1 row in 21.88 sec select count(*) from rondon; 1 row in 42.47 sec select count(*) from rondon; 1 row in 1 min 47.69 sec not sure why the first was so fast, the rest SUCK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with tables crashing
V. M. Brasseur wrote: $ perror 145 Error code 145: Error 145 occurred. 145 = Table was marked as crashed and should be repaired I think `myisamchk` needs to come into play here (both on slave and master at this point). Yea I know, and I had done that like a few days ago because it gave me the same error then. The table was fine before I started the procedure to resync the slave by giving it a copy of the masters database. After that it was marked crashed. I don't think that it should do that, should it? So in short: table is fine, I want to resync slave b/c of an error, on the master I do: mysql FLUSH TABLES WITH READ LOCK; mysql SHOW MASTER STATUS; mysql UNLOCK TABLES; and my table is marked crashed :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem I've switched to innodb but performance isnt very good while the insert runs, here's what I get for performance select count(*) from rondon; 1 row in .13 sec select count(*) from rondon; 1 row in 21.88 sec select count(*) from rondon; 1 row in 42.47 sec select count(*) from rondon; 1 row in 1 min 47.69 sec not sure why the first was so fast, the rest SUCK Well, this a particularly bad command to use to test innodb performance. With MyISAM table, the record count is immediately available. With Innodb, the server has to actually count records. Different users will get different counts depending on the transaction isolation mode and number of uncommitted records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pinging
Can you describe your network setup? Are you expecting port 3306 to be open on your ISP? Are you ssh'ed in and you can not connect? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 12:02 PM Subject: Pinging Hi list, I get this error from MySQL administrator, everything was fine before I converted the db and uploaded it to our host company. What is going on here? Thanks a lot for any help Could not connect to the specified host MySQL error Nr.2003 Cant connect to MySQL server on local host (10061) Click the ping button to see if there is a networking problem Pinging localhost Reply from 127.0.0.1: Time=0ms TTL=128 Reply from 127.0.0.1: Time=0ms TTL=128 I turn on the service as well and it turns off after about 5 seconds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slave crashing
That depends on the setup and configuration and network and other factors. I have been running slaves for months and a year with little to no problems. Are you asking something in specific or just looking for feedback? -Original Message- From: Scott Hamm To: 'Mysql ' (E-mail) Sent: 8/13/04 11:51 AM Subject: Slave crashing I'm currently on research on SQL selection for our future Database project, and so far I've noticed that there are problem with slave, is it common for MySQL to have slave problem? I'm looking into Microsoft SQL Server, PostgreSQL and MySQL for our future project. Scott -- 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: Tuning InnoDB situation
Boyd E. Hemphill wrote: But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. innodb_data_file_path = ibdata1:15G:autoextend We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. This might be a long shot, but is InnoDB getting slowed down by extending its tablespace? You've got more data than your initial ibdata file will hold, so clearly it autoextended to hold your data. I think InnoDB extends in 10M increments, so if you're doing lots of inserts it could be repeatedly extending the file which seems like it would add some overhead. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using SUM in a special way
Hi, I would like to use SUM or any other function to sum a series of records as in the following example. I have this table T1 Col1Col2 1 20 2 10 1 10 25 1 20 3 10 and would like to obtain this result from a query Col1Col2Col3 1 20 20 1 10 30 1 20 50 2 10 10 25 15 3 10 10 Column Col3 should carry forward and sum values from Col2 Is that possible ?, with SQL I mean... Thank you Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can connect with PHP to MYSQL
On Fri, 13 Aug 2004 11:24:46 -0400, Wesley Furgiuele [EMAIL PROTECTED] said: What error are you getting? My problem when moving to 4.1 was forgetting that I was using an older MySQL client. I'm not positive, but I think that in order to connect to MySQL 4.1 with PHP you need to use the mysqli functions, not mysql, and mysqli requires PHP 5. I think that's the problem. Could be a lot of editing PHP scripts for a lot of people(!?) Thanks If you want to keep using MySQL 4.1 with PHP 4, then try changing your user's password with the OLD_PASSWORD() function. http://dev.mysql.com/doc/mysql/en/Encryption_functions.html Wes On Aug 13, 2004, at 9:48 AM, leegold wrote: I can connect in the php by using 'root' and the root pw. But when I tried adding a user ( sarah ) with GRANT I cannot connect from php. The php is simple, what am I overlooking? Again putting the root user name and pw it'll work in php but not for sarah: the php mysql_pconnect(localhost,sarah,camera) or die(ERROR: Could not connect to database!); mysql_select_db(howto); *But* on the command line the sarah user works OK: C:\Documents and Settings\Administratormysql -u sarah -p Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.1.3a-beta-nt-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from howto.page; +-+-+ | page_id | page_url| +-+-+ | 1 | http://www.lg.netfarms.org | +-+-+ 1 row in set (0.00 sec) mysql show grants for [EMAIL PROTECTED]; +-- -+ | Grants for [EMAIL PROTECTED] | +-- -+ | GRANT ALL PRIVILEGES ON *.* TO 'sarah'@'localhost' IDENTIFIED BY PASSWORD '*6043233C67ADBE7E9242089D718763C3C5E1A1E2' | +-- -+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SUM in a special way
I am afraid not. A spreadsheet is the right tool for that job, not a database. Sorry! Of course you could always script your own solution (calculating running totals, etc.) Best Wishes, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/13/2004 06:29:18 PM: Hi, I would like to use SUM or any other function to sum a series of records as in the following example. I have this table T1 Col1 Col2 1 20 2 10 1 10 25 1 20 3 10 and would like to obtain this result from a query Col1 Col2 Col3 1 20 20 1 10 30 1 20 50 2 10 10 25 15 3 10 10 Column Col3 should carry forward and sum values from Col2 Is that possible ?, with SQL I mean... Thank you Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SUM in a special way
--Try this, using variable @foo: SELECT t1.col1, t2.col2, @foo := @foo + (t1.col1 * t1.col2) col3 FROM t1 On Fri, 13 Aug 2004 15:29:18 -0700, Mauricio Pellegrini [EMAIL PROTECTED] wrote: Column Col3 should carry forward and sum values from Col2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SUM in a special way
Mauricio Pellegrini wrote: and would like to obtain this result from a query Col1Col2Col3 1 20 20 1 10 30 1 20 50 2 10 10 25 15 3 10 10 Column Col3 should carry forward and sum values from Col2 Something like this should work, using two variables, @total and @prev: SELECT Col1, Col2, @total := IF(@prev = Col1, @total + Col2, Col2 + (@prev := Col1) - Col1) FROM table_name ORDER BY Col1; The way I'm setting @prev every time Col1 changes is a bit klugy (having to add it in and then subtract Col1 to fix it), but it seems to work. Hmm, if you change the order of the result columns you can avoid the kluge: SELECT Col2, @total := IF(@prev = Col1, @total + Col2, Col2), @prev := Col1 FROM table_name ORDER BY Col1; -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pinging
Thanks for the post Victor, I can connect fine to my isp(through port 3306) , but not to localhost that is the problem - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'EWAGW ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 13, 2004 1:09 PM Subject: RE: Pinging Can you describe your network setup? Are you expecting port 3306 to be open on your ISP? Are you ssh'ed in and you can not connect? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 12:02 PM Subject: Pinging Hi list, I get this error from MySQL administrator, everything was fine before I converted the db and uploaded it to our host company. What is going on here? Thanks a lot for any help Could not connect to the specified host MySQL error Nr.2003 Cant connect to MySQL server on local host (10061) Click the ping button to see if there is a networking problem Pinging localhost Reply from 127.0.0.1: Time=0ms TTL=128 Reply from 127.0.0.1: Time=0ms TTL=128 I turn on the service as well and it turns off after about 5 seconds -- 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: Can connect with PHP to MYSQL
I think that's the problem. Could be a lot of editing PHP scripts for a lot of people(!?) Thanks Try to compile PHP against the client library for MySQL 4.1. I'll wonder if it won't work. ext/mysqli is required if you like to use things like prepared statements... Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem Doh another problem innodb has no merge option, I have too much data, and the only way to deal with it, is partition the data and then tie it together with merge views. Unfortunatly innodb will not work for me :( Anybody know if SQL Server desktop supports what I need? I know oracle does, but the cost is an issue, maxdb costs too much too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pinging
Do you know if the user you are using is allowed to connect from the localhost? Logically one would think that if the ISP granted you the ability to connect from outside the localhost, your host value would be wildcarded and thus you should be able to connect from the localhost as well. Do you have shell access to this MySQL server? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 1:57 PM Subject: Re: Pinging Thanks for the post Victor, I can connect fine to my isp(through port 3306) , but not to localhost that is the problem - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'EWAGW ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 13, 2004 1:09 PM Subject: RE: Pinging Can you describe your network setup? Are you expecting port 3306 to be open on your ISP? Are you ssh'ed in and you can not connect? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 12:02 PM Subject: Pinging Hi list, I get this error from MySQL administrator, everything was fine before I converted the db and uploaded it to our host company. What is going on here? Thanks a lot for any help Could not connect to the specified host MySQL error Nr.2003 Cant connect to MySQL server on local host (10061) Click the ping button to see if there is a networking problem Pinging localhost Reply from 127.0.0.1: Time=0ms TTL=128 Reply from 127.0.0.1: Time=0ms TTL=128 I turn on the service as well and it turns off after about 5 seconds -- 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]
Re: Replace delayed locks table
I hate to pry (snoop) but my curiosity is just going nuts! If this is a sensitive issue, please ignore my questions What are you doing that requires you to mass-replace so many records so often? Are they design or processing requirements (or both) that require this kind of bulk exchange of records? How open are you to the idea of possibly changing the way you deal with these records? I would feel horrible if after all this time and all of the brains that subscribe to this list that you would end this thread without some kind of improvement to your situation. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt ryan [EMAIL PROTECTED] wrote on 08/13/2004 03:26:57 PM: matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem Doh another problem innodb has no merge option, I have too much data, and the only way to deal with it, is partition the data and then tie it together with merge views. Unfortunatly innodb will not work for me :( Anybody know if SQL Server desktop supports what I need? I know oracle does, but the cost is an issue, maxdb costs too much too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tuning InnoDB situation
Do vmstat and top reveal anything about the server's performance? Can you post the show status output? -Original Message- From: Boyd E. Hemphill To: [EMAIL PROTECTED] Sent: 8/13/04 12:17 PM Subject: Tuning InnoDB situation All: I have been nosing about for some time now and think I need some help. The Problem: Mytop is telling me that I am running no more than 1000 queries per second, and the key efficiency is 100%. But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. The queries normally take no longer than 5 seconds in a test environment on a slower machine. In production (where the problem is) they can last from 90 to 1400 seconds. A few of these bring our site to a crawl. Suspicions: I have seen this machine run at 3000 to 6000 qps and still move data out fast. Some of the queries it is now performing slow are were part of this performance in the past. So my first idea is that the server, rather than the query, needs to be tuned. Here is the my.cnf stuff: [mysqld] port= 3306 socket = /var/run/mysqld/mysqld.sock skip-locking skip-bdb set-variable= key_buffer=16M set-variable= max_allowed_packet=10M set-variable= max_connections=1200 set-variable= table_cache=256 set-variable= sort_buffer=2M set-variable= net_buffer_length=64K set-variable= myisam_sort_buffer_size=32M log-bin server-id = 2 pid-file= /var/run/mysqld/mysqld.pid #log = /var/log/mysql/mysql.log log-slow-queries basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english default-table-type = innodb query-cache-type = 1 query-cache-size = 20M set-variable = net_read_timeout=600 set-variable= net_write_timeout=600 innodb_data_home_dir = /var/lib/mysql/innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:15G:autoextend set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I am considering raising the table_cache to 1500 and the innodb_buffer_pool_size to 2.5GB. Comments on this would be appreciated as well. If this is not the issue then I suspect there is contention in some of the busy tables. Where do I look at the SHOW INNODB STATUS output to detect this situation? What am I looking for? Can I schedule InnoDB transactions? We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- 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: Using SUM in a special way
Hey!!! thanks thanks thanks to all of you!. Your just wonderfull and the help you provide is priceless. I think I will solve my problem now. I've read about the use of variables in the manual but It would have taken me a lot of time to figure something like this. Thanks again and to all the rest of the people who answered my question Gratefully Mauricio On Fri, 2004-08-13 at 11:55, Keith Ivey wrote: Mauricio Pellegrini wrote: and would like to obtain this result from a query Col1Col2Col3 1 20 20 1 10 30 1 20 50 2 10 10 25 15 3 10 10 Column Col3 should carry forward and sum values from Col2 Something like this should work, using two variables, @total and @prev: SELECT Col1, Col2, @total := IF(@prev = Col1, @total + Col2, Col2 + (@prev := Col1) - Col1) FROM table_name ORDER BY Col1; The way I'm setting @prev every time Col1 changes is a bit klugy (having to add it in and then subtract Col1 to fix it), but it seems to work. Hmm, if you change the order of the result columns you can avoid the kluge: SELECT Col2, @total := IF(@prev = Col1, @total + Col2, Col2), @prev := Col1 FROM table_name ORDER BY Col1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication blocked
On Fri, Aug 13, 2004 at 01:19:14AM -0500, Donny Simonton wrote: There is only one thread for replication on the slave. It does one step a time. If you use mysqlbinlog on one of your binary files on your master, you will see exactly how it all works. No, there are 2 threads: the IO (or relay) thread, and the SQL thread. Multi-threaded would probably cause thousands of problems. Unless it was threaded per table, but that would still cause problems because of multi-table deletes and updates. Agreed. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to increase max allowed keys?
Hello mysql-help, How i can increase max allowed keys in myisam tables? I find this sting in myisam.h: #define MI_MAX_KEY 32 /* Max allowed keys */ Is it so simple? Or I must correct somthing more? What is the max value of MI_MAX_KEY ? Michael Monashev http://softsearch.ru/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clarifying the MySQL Licensing Policy Documents
Greetings All, As a way to cooperatively work to address some of the licensing concerns held by members of the MySQL community and the broader Free Software/Open Source community, we are initiating a community review of our licensing policy documents. This means that we are soliciting feedback about issues in the policy documents that people find inaccurate or unclear. From this feedback we will work to improve the policy documents. Please note that we cannot guarantee that we will address or accept all of the issues raised or suggestions made. The licensing is both complex and is the cornerstone of our business. Past experience shows that we must work carefully, incrementally and with community involvement in this area. As with development of the FLOSS exception though, I hope that we can reach a compromise that is satisfactory for almost everyone. If you wish to participate, the process for doing so is simple. Review some or all of the following documents: * http://www.mysql.com/products/licensing/ * http://www.mysql.com/products/licensing/commercial-license.html * http://www.mysql.com/products/licensing/opensource-license.html * http://www.mysql.com/products/licensing/faq.html When you encounter an issue that you find confusing or inaccurate, please send a note to the MySQL community list or to me personally on the issue: Ideally the note will state: * where the issue is (which document, where in the document) * what specific aspects of the issue concern you * the severity of the issue (is it cosmetic, minor or major) * a suggested fix or set of fixes You are also welcome to directly file an issue report in the system that I use for tracking these issues. Visit http://zak.greant.com:/licensing/tktnew to do so. I would like the discussion to take place on the MySQL community list, as it is easier to keep track of the issues in a single, low-traffic setting. I prefer not to Cc the MySQL General list beyond this initial email, so as to avoid cluttering an already busy mailing list. An initial list of issues raised by various community members exist at: * http://zak.greant.com:/licensing/tktview?tn=32 * http://zak.greant.com:/licensing/tktview?tn=40 I am currently working on new draft policy documents to correct some of the simple issues and errors, and hope to post it next week for community review after it goes through internal review. Cheers! -- Zak Greant MySQL AB Community Advocate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY optimization headscratcher
Hi all. Got a weird one. Mysql 4.0.20. Let's say for the sake of argument that I've got two tables, T1 T2. Here are the two create statements: CREATE TABLE `T1` ( `guid` smallint(5) unsigned NOT NULL default '0', `qid` smallint(5) unsigned NOT NULL default '0', `a` tinyint(2) NOT NULL default '-2', `d` tinyint(2) NOT NULL default '-2', KEY `IX_FW_qid` (`qid`), KEY `IX_FW_d` (`d`) ) TYPE=HEAP CREATE TABLE `T2` ( `guid` mediumint(8) unsigned NOT NULL default '0', `qid` tinyint(3) unsigned NOT NULL default '0', `a` tinyint(4) NOT NULL default '0', `d` decimal(1,0) unsigned NOT NULL default '0', PRIMARY KEY (`guid`,`qid`), KEY `IX_s23aw_d` (`d`), KEY `IX_s23aw_qid` (`qid`) ) TYPE=HEAP So, in T1 there are as many records as there are qids (around 150) (there's only one user in T1). In T2 there are as many records as there are qids * user id's = (around 497,964). The weirdness comes when I try to join them and do a group by at the same time. The following: SELECT T1.guid, sum(T1.d + T2.d) as theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T1.guid takes 1 second to run. This seems absurdly long. Explain shows that everything seems fine (although it shows that T2 has a key length of 2, which is weird, and I don't really understand), and if I do the same query without the group by or the sum, it goes in 0.01 seconds. So, I ran the same query without the group by and the sum and stored the result set in a temporary table, and then did a group by on guid for that temporary table, and that ran 0.01 seconds. so I've got a solution to my problem already, even though it's a two-query solution. However, I'm really curious as to why MySQL takes so long on my original query. It seems like it's creating a temporary table in memory and doing the group by on that, which is exactly what I was doing the second time around. and I find it hard to believe that I'm that much smarter than the MySQL preprocessor. So, if anyone has any thoughts on this strange disparity in time, I'd be interested to hear them! Thanks a lot! -Matt
MYSQL and COMMANDFOR DUMP FILE
Dear Driends, From command prompt I want to generate dump files of database tables. Any guidance how do I do the same. Inform, please.
RE: MYSQL and COMMANDFOR DUMP FILE
Hi, Is this what you need/want? mysqldump db_name /path/to/whereever/dump.sql Kind Regards, Christian Biggins Web Developer Web: http://www.fusiononline.com.au Email: [EMAIL PROTECTED] Phone: 0410 596 841 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, 14 August 2004 3:34 PM To: [EMAIL PROTECTED] Subject: MYSQL and COMMANDFOR DUMP FILE Dear Driends, From command prompt I want to generate dump files of database tables. Any guidance how do I do the same. Inform, please. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
