Re: Searching through an alphabetical range
Hello Paul, You can try this: SELECT DISTINCT lastname FROM employee WHERE lastname BETWEEN 'm' AND 'z'; Thanks, ViSolve DB Team. - Original Message - From: Paul Nowosielski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 21, 2006 10:33 PM Subject: Searching through an alphabetical range Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- 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: Struggling with the logic
Thanks to all for the responses, as always problem now solved and I learnt a few things. Regards John Berman -Original Message- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: 23 July 2006 16:02 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Struggling with the logic At 15:43 +0100 23/7/06, John Berman wrote: I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ Why? If you're storing the date in this format you can only be storing it as a string (char, varchar or text), so no wonder you're having trouble with it, when MySQL has a perfectly good date storage type in the form -mm-dd. I want to display all records for 7 days only from their approved date Assuming you've changed the way you store your dates: SELECT * FROM submissions WHERE DATE_ADD(approvedate, INTERVAL 7 DAY) = NOW () For what it's worth, the standard American date format of mm/dd/ has always mystified me, as it's the least logical possible way to do it. The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. Over here in Europe we at least use dd/mm/ (increasing unit size order), which is the next most logical, but to start with the middle-sized unit, put the smallest unit in the middle and end with the largest is just... weird! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Marriage has driven more than one man to sex. -- Peter de Vries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.3/395 - Release Date: 21/07/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
Hi, Try using the optimize table tablename ,but this will keep the data accordingly,but really if it is a disk space constraint you can go with re-org process in which you will have to get a down time for mysql db.Process is something like . Dump all the Innodb tables drop the existing innodb tables and shutdown mysql, clear the Innodb log-space as ibdata1 indata2 iblogfile0 iblogfile1 and also the redo logs of the innodb. Then start the mysql this will create innodb logs 1 innodb2 as what u have mentioned in ur cnf file and import the dump . In this case u can able to reduce the space usage of innodb. Try this it might help u out. With Regards Dilipkumar [EMAIL PROTECTED]: Hi, all I know the Innodb use MVCC to achieve very high concurrency. Can Innodb reuse the deleted rows disk space? I have an database which have many update operation. If Innodb can\'t reuse the space of deleted rows, I worry about that MySQL will exhaust our disk space very quickly. Any recommend will be welcome! Regards, Leo Huang -- 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: Struggling with the logic
Hi Chris, John all! Chris' explanation is nearly correct: Chris Sansom wrote: At 15:43 +0100 23/7/06, John Berman wrote: I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ Why? If you're storing the date in this format [[...]] John, you should ensure to differ between storage format and presentation format(s): - For storage, ensure that it can be sorted on (order year, month, day). Typically, this is achieved by telling the database a proper type (like DATE or DATETIME) and then just supplying a value. - For presentation, use what suits the users' taste. This may be configurable or depending on context (like drop the year if it is obvious etc.), and you can achieve all (?) desired results by the DATE_FORMAT() function. There should be only one storage format, but there may be many different ways to present a date. If you do not use the proper SQL type (DATE, DATETIME, ...), you cannot rely on SQL doing the desired operations (sorting, calculations, ...). [[...]] The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. [[...]] That is correct except for the designation SQL: It really is an ISO format. The important property is having the most significant value (year) in the first (leftmost) place. Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer 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]
Select Query taking time
Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. In such a case should this query be considered as slow? We are actually checking for queries which take longer than 12 secs and regarding such queries as slow. The moment we find such a query, a mail is sent to the DBA saying that the query is slow. So in a day there are more than 400 such slow query notifications flowing into the mail box. My questions are, Should the simultaneous queries take so long? Should'nt both queries have finished by 6 secs rather than 10 secs? Is this a right strategy to track slow queries? Any suggestions would help. Thanks, Ratheesh K J
Re: Select Query taking time
On Monday 24 July 2006 09:05, Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. Consider this: With the query cache enabled, running a query for the first time will take 5 seconds. Running it again immediately should be instantaneous, as the result set is in memory (and if it isn't, it should be in the OS disk cache [assuming a small result set]). Two queries executed simultaneously will cause disk contention, because the query isn't cached by mysql, and the OS cache probably hasn't had time to commit the data coming from the disks either. In the case of a single disk serving up the data, two simultaneous queries for the same data will cause the disk to go back and forth trying to satisfy each query. Even with a mirrored pair of disks, you're going to have problems unless you have a very intelligent disk controller that can split the requests across the two disks. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
On Monday 24 July 2006 09:06, Duncan Hill wrote: On Monday 24 July 2006 09:05, Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using In the case of a single disk serving up the data, two simultaneous queries for the same data will cause the disk to go back and forth trying to satisfy each query. Even with a mirrored pair of disks, you're going to have problems unless you have a very intelligent disk controller that can split the requests across the two disks. Forgot to add - do the queries require table locks? If so, the first one is going to lock the table, run in 5 seconds, unlock. Then the second one, and then the third. Assuming no query cache. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
At 17:31 -0700 23/7/06, [EMAIL PROTECTED] wrote: The logic is that it follows the natural spoken format, i.e. July 23, 2006; which became the written standard; which... Hmmm. Is 'July the 23rd, 2006' any more natural to say than 'the 23rd of July, 2006'? I think we probably say either, equally. Sorry - I don't buy that. :-) Myself: I've never let local standards stand in the way of my using international ones. Excellent. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ To see tomorrow's PC, look at today's Macintosh -- Byte 1995 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
undefined reference to `mysqlpp::Connection::Connection(bool)'
Hi, I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i want to connect to MySQL through C. When I am compiling the program, I am getting the following error. #gcc -o custom1 -lmysqlclient -lz custom1.cpp /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostreamchar, std::char_traitschar std::operator std::char_traitschar (std::basic_ostreamchar, std::char_traitschar , char const*)'... ... ... ... ... ...std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::~basic_string()' /tmp/ccPQEF5p.o:(.gnu.linkonce.r._ZTIN7mysqlpp8BadQueryE[typeinfo for mysqlpp::BadQuery]+0x0): undefined reference to `vtable for __cxxabiv1::__si_class_type_info' /tmp/ccPQEF5p.o:(.eh_frame+0x12): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status ### my code is standard code in mysql++ examples directory please help me. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
RE: undefined reference to `mysqlpp::Connection::Connection(bool)'
Hi Ali, Your problem is as it states /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status The linker, ld, does not know where to find the library libmysqlclient.so. You need to tell the linker using either ldconfig (man ldconfig) or LD_LIBRARY_PATH variable. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: ali asghar torabi parizy [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 6:54 PM To: mysql@lists.mysql.com Subject: undefined reference to `mysqlpp::Connection::Connection(bool)' Hi, I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i want to connect to MySQL through C. When I am compiling the program, I am getting the following error. #gcc -o custom1 -lmysqlclient -lz custom1.cpp /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostreamchar, std::char_traitschar std::operator std::char_traitschar (std::basic_ostreamchar, std::char_traitschar , char const*)'... ... ... ... ... ...std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::~basic_string()' /tmp/ccPQEF5p.o:(.gnu.linkonce.r._ZTIN7mysqlpp8BadQueryE[typeinfo for mysqlpp::BadQuery]+0x0): undefined reference to `vtable for __cxxabiv1::__si_class_type_info' /tmp/ccPQEF5p.o:(.eh_frame+0x12): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status ### my code is standard code in mysql++ examples directory please help me. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: undefined reference to `mysqlpp::Connection::Connection(bool)'
Hi.thanks logan. I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i trying too many pathes in gcc cammand but that error continued. I am getting the following error. # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp #gcc -o custom1 -lmysqlclient -L /usr/local/mysqlpp/include/mysql++ custom1.cpp gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ -L /usr/local/mysqlpp/include/mysql++ custom1.cpp for all statements following error continued:( # /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostream std::char_traits std::operator (std::basic_ostream , char const*)'... ... ... ... ... ...std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_string, std::allocator ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_string, std::allocator ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_string, std::allocator ::~basic_string()' /tmp/ccPQEF5p.o:(.gnu.linkonce.r._ZTIN7mysqlpp8BadQueryE[typeinfo for mysqlpp::BadQuery]+0x0): undefined reference to `vtable for __cxxabiv1::__si_class_type_info' /tmp/ccPQEF5p.o:(.eh_frame+0x12): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status ### my code is standard code in mysql++ examples directory please help me. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
RE: undefined reference to `mysqlpp::Connection::Connection(bool)'
Hi Ali, I'll repeat what I've already said, it is the ld command that is having the problem. If you don't know what the ld command is or does, you need to look elsewhere for the answer as this is beyond the scope of this list. As I have previously mentioned, try $ man ldconfig Your problem is as it states /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient -- here is the problem collect2: ld returned 1 exit status The linker, ld, does not know where to find the library libmysqlclient.so. You need to tell the linker using either ldconfig (man ldconfig) or LD_LIBRARY_PATH variable. eg. export LD_LIBRARY_PATH=path to mysqlclient library Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: ali asghar torabi parizy [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 7:29 PM To: mysql@lists.mysql.com Subject: RE: undefined reference to `mysqlpp::Connection::Connection(bool)' Hi.thanks logan. I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i trying too many pathes in gcc cammand but that error continued. I am getting the following error. # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp #gcc -o custom1 -lmysqlclient -L /usr/local/mysqlpp/include/mysql++ custom1.cpp gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ -L /usr/local/mysqlpp/include/mysql++ custom1.cpp for all statements following error continued:( # /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostream std::char_traits std::operator (std::basic_ostream , char const*)'... ... ... ... ... ...std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_string, std::allocator ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_string, std::allocator ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_string, std::allocator ::~basic_string()' /tmp/ccPQEF5p.o:(.gnu.linkonce.r._ZTIN7mysqlpp8BadQueryE[typeinfo for mysqlpp::BadQuery]+0x0): undefined reference to `vtable for __cxxabiv1::__si_class_type_info' /tmp/ccPQEF5p.o:(.eh_frame+0x12): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status ### my code is standard code in mysql++ examples directory please help me. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. In such a case should this query be considered as slow? We are actually checking for queries which take longer than 12 secs and regarding such queries as slow. The moment we find such a query, a mail is sent to the DBA saying that the query is slow. So in a day there are more than 400 such slow query notifications flowing into the mail box. My questions are, Should the simultaneous queries take so long? Should'nt both queries have finished by 6 secs rather than 10 secs? Without query caching enabled, yes it is perfectly normal that the time spent is rising in a linear fashion, eg 4 simulatious would be 20 seconds, 5 25 secs and so on - this just means that your query run by itself is able to utilize all available resources such as cpu time. Look at it this way: 1 query will use 100% of the available cpu and it takes 5 seconds. When you run two at the same time they each have 50% cpu to use, and thus take 10 seconds (5 seconds * 100 / 50). With 3 they each have 33,1/3% and take 15 seconds ( 5 seconds * 100 / 33,1/3) and so on. Is this a right strategy to track slow queries? Yes and no. It is always wise to test your queries to see how the do speed wise, but if you only measure time you aren't really getting the full picture. You have to also look at what else the system is doing - if a query is bottlenecked only by available cpu, it will run at very different speeds depending on how busy the system is with other things - try to bzip2 a 500MB file while running the query and see how much time it takes then for instance ;) And as always remember to use explain to see how mysql optimizes your query so you can modify it if needed, especially complicated joins can sometimes be alot faster if you tweak them a bit. Any suggestions would help. Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching through an alphabetical range
Depending on the size of your table, it can be faster using SELECT lastname FROM employee WHERE lastname BETWEEN 'm' AND 'z' GROUP BY lastname; On a table with 2,5 Mill. records the speed diff is over 1000% on my system. ViSolve DB Team wrote: Hello Paul, You can try this: SELECT DISTINCT lastname FROM employee WHERE lastname BETWEEN 'm' AND 'z'; Thanks, ViSolve DB Team. - Original Message - From: Paul Nowosielski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 21, 2006 10:33 PM Subject: Searching through an alphabetical range Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- 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: Struggling with the logic
As mentioned before you really should sue the date data type in mysql, or alternativly use an int and store the date as seconds since the epox, so that you can do simple math for this type of query. Any other way of storing dates is basically shooting yourself in the foot. That said, if you choose to store it as a varchar use: Select * from submissions where approvedate in ('07/01/2006', '07/02/2006', '07/03/2006',...); Be aware how extremely slow this performs compared to the options mentioned above tho. John Berman wrote: Hi I'm struggling with some logic I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ I want to display all records for 7 days only from their approved date so I guess something like Select * from submissions were approvedate - this is were im getting stuck Pointers appreciated, im sure its simple ? Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:28 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- -- - - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- - - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives:
RE: Replication Problem
David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
RE: Replication Problem
Hi Brett, Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Will UPDATE block on SELECT?
I would like to know whether a SELECT query would block an Update on the same table. The table is of InnoDB type. Since InnoDB tables apply row level locks should the Update queries be blocked until the select query completes? I experienced such a scenario wherein an update query had to wait until the select query completed. Also how different is the locking when there is a CREATE TEMPORARY TABLE tblname AS SELECT * FROM TBL_TEST and an Update on the table TBL_TEST simultaneously? Pls Note: We are not using transactions. Currently all our tables have been converted to InnoDB type. Very soon we are planning to use transactions. Thanks, Ratheesh K J
RE: Replication Problem
David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLHotCopy
I read in the manual that MySQLHotCopy would be better than MySQLDump for backing up MyISAM tables. However, I cannot find a .exe in the bin directory by that name. I found a script by that name in a 4.1 installation that I have, but I don't think it's the latest version. Is this a script? If so, does anyone know where can I get the latest copy? I've checked MySQL.com, but can't seem to find it there. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Restore Help
I have a backup that was created by a MySQL 5 server using MySQLDump. When I try to restore the database using the following command: mysql -u root -p -D BPA c:\backup\mydata.sql I get the error: ERROR 1064 (42000) at line 29765: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/' at line 1 Here is what line 29765 says in the backup file: /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; This is obviously one of the triggers that I've created. I don't know why it's commented them out in the backup, but I don't seem to be able to overcome this. I'd rather it ignore these lines anyway. How do I get passed this? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Synro MySQL databases
HI all i have a question, I have A server MySQL database in our office location that stores Contact information / inventory etc... and also we have a Internet Site NOw what id like to perform is Whenver Someone changes something in the office to our Database, it also changes on our website ,, how do i do this ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Synro MySQL databases
On Monday 24 July 2006 16:16, Brian E Boothe wrote: HI all i have a question, I have A server MySQL database in our office location that stores Contact information / inventory etc... and also we have a Internet Site NOw what id like to perform is Whenver Someone changes something in the office to our Database, it also changes on our website ,, how do i do this ??? Replication with the office as the master, internet as slave? -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Synro MySQL databases
Hello, Does this change need to occur at exactly the same moment? Might want to take a look at replication for starters if you are not familiar with the technology. http://dev.mysql.com/doc/refman/5.0/en/replication.html SQLyog also offers some interesting synching http://www.webyog.com/sqlyog/index.php Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Brian E Boothe [mailto:[EMAIL PROTECTED] Sent: Monday, July 24, 2006 10:16 AM To: MySQL List Subject: Synro MySQL databases HI all i have a question, I have A server MySQL database in our office location that stores Contact information / inventory etc... and also we have a Internet Site NOw what id like to perform is Whenver Someone changes something in the office to our Database, it also changes on our website ,, how do i do this ??? -- 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]
Compilation Failure
Dear List, I compiled 5.1.6 on Linux, works perfectly. Been running for a few weeks, now want to port to live server. Same compilation on AIX 5.2.0.0, I get: Making all in storage/archive if gcc -DHAVE_CONFIG_H -I. -I. -I../.. -I../../include -I../../include -DDBUG_OFF -O2 -mpowerpc64 -mminimal-toc -maix64 -Wa,-many -MT azio.o -MD -MP -MF .deps/azio.Tpo -c -o azio.o azio.c; then mv -f .deps/azio.Tpo .deps/azio.Po; else rm -f .deps/azio.Tpo; exit 1; fi In file included from ../../include/my_sys.h:825, from ../../mysys/mysys_priv.h:18, from azlib.h:36, from azio.c:17: /usr/include/sys/mman.h:115: error: conflicting types for `mmap64' /usr/include/sys/mman.h:110: error: previous declaration of `mmap64' In file included from azio.c:17: azlib.h:147:1: warning: Z_ASCII redefined In file included from azlib.h:34, from azio.c:17: /opt/freeware/include/zlib.h:175:1: warning: this is the location of the previous definition Are there any AIX experts who may have found/fixed this problem? Any help would be wonderful! Regards, Ben PS, configure: ./configure \ --with-low-memory \ --prefix=$ROOT \ --enable-thread-safe-clinet \ --enable-local-infile \ --with-named-z-libs=no \ --disable-shared \ --localstatedir=$DATA \ --with-unix-socket-path=$SOCKET \ --includedir=/usr/linux/include \ --with-charset=utf8 \ --sysconfdir=$ETC \ --libexecdir=$SERVER_DIR \ --with-extra-charsets=complex \ --with-innodb \ --with-issam \ --with-extra-charsets=complex \ --with-cvs-storage-engine \ --with-archive-storage-engine \ --enable-local-infile \ --enable-assembler \ --enable-thread-safe-client \ --with-openssl=/usr/linux \ --with-openssl-includes=/usr/linux/include \ --with-openssl-libs=/usr/linux/lib \ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compilation Failure
On Monday 24 July 2006 08:36 am, Ben Clewett wrote: Making all in storage/archive if gcc -DHAVE_CONFIG_H -I. -I. -I../.. -I../../include -I../../include -DDBUG_OFF -O2 -mpowerpc64 -mminimal-toc -maix64 -Wa,-many -MT azio.o -MD -MP -MF .deps/azio.Tpo -c -o azio.o azio.c; then mv -f .deps/azio.Tpo .deps/azio.Po; else rm -f .deps/azio.Tpo; exit 1; fi In file included from ../../include/my_sys.h:825, from ../../mysys/mysys_priv.h:18, from azlib.h:36, from azio.c:17: /usr/include/sys/mman.h:115: error: conflicting types for `mmap64' /usr/include/sys/mman.h:110: error: previous declaration of `mmap64' In file included from azio.c:17: azlib.h:147:1: warning: Z_ASCII redefined In file included from azlib.h:34, from azio.c:17: /opt/freeware/include/zlib.h:175:1: warning: this is the location of the previous definition Well, looking things over either a) glibc headers are jacked up (which is what /usr/include/sys/mman.h is installed by). If things worked before, check if any updates to glibc were done. Otherwise I'd just google around a bit for AIX+mmap64 and see what comes up. -- Chris White PHP Programmer/DBit Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Segmentation fault error accrued when gcc -o test -L/usr/lib/mysql -lmysqlclient test.c
On Sunday 23 July 2006 10:26 pm, ali asghar torabi parizy wrote: while ((row = mysql_fetch_row(res_set)) != NULL) { while (row = mysql_fetch_row(res_set)) would do the same thing, NULL check shouldn't be necessary. for (i=0; iltmysql_num_fields(res_set); i++) { I hope your client is inserting that lt in there, that may be what's causing your problem (not to mention it's lt;...) printf(%s\n,row[i] != NULL ? row[i] : NULL); } } -- Chris White PHP Programmer/DBanned! Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compilation Failure
Chris, Previous version which did compile was 4.1.9. May be glibc was not required for this version. Glibc is not native to AIX so I can see some fun ahead. Thanks, Regards, Ben Chris White wrote: On Monday 24 July 2006 08:36 am, Ben Clewett wrote: Making all in storage/archive if gcc -DHAVE_CONFIG_H -I. -I. -I../.. -I../../include -I../../include -DDBUG_OFF -O2 -mpowerpc64 -mminimal-toc -maix64 -Wa,-many -MT azio.o -MD -MP -MF .deps/azio.Tpo -c -o azio.o azio.c; then mv -f .deps/azio.Tpo .deps/azio.Po; else rm -f .deps/azio.Tpo; exit 1; fi In file included from ../../include/my_sys.h:825, from ../../mysys/mysys_priv.h:18, from azlib.h:36, from azio.c:17: /usr/include/sys/mman.h:115: error: conflicting types for `mmap64' /usr/include/sys/mman.h:110: error: previous declaration of `mmap64' In file included from azio.c:17: azlib.h:147:1: warning: Z_ASCII redefined In file included from azlib.h:34, from azio.c:17: /opt/freeware/include/zlib.h:175:1: warning: this is the location of the previous definition Well, looking things over either a) glibc headers are jacked up (which is what /usr/include/sys/mman.h is installed by). If things worked before, check if any updates to glibc were done. Otherwise I'd just google around a bit for AIX+mmap64 and see what comes up. -- Ben Clewett +44(0)1923 46 Project Manager Road Tech Computer Systems Ltd http://www.roadrunner.uk.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a struggle with float type display and width
Hi, I've recently changed MySQL versions and I am puzzled by the difference in display of float values. CREATE TABLE test ( id int unsigned NOT NULL auto_increment, val1 float default NULL, val2 float(3,3) default NULL, val3 float(5,3) default NULL, val4 float(10,6) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; INSERT INTO test VALUES (1,36705.586,36705.586,36705.586,36705.586); select version(); select * from test; drop table test; === OUTPUT 1 mysql select version(); +-+ | version() | +-+ | 4.0.16-standard | +-+ 1 row in set (0.00 sec) mysql mysql select * from test; ++-+---+---+--+ | id | val1| val2 | val3 | val4 | ++-+---+---+--+ | 1 | 36705.6 | 36705.586 | 36705.586 | 36705.585938 | ++-+---+---+--+ 1 row in set (0.00 sec) === OUTPUT 2 mysql select version(); +---+ | version() | +---+ | 4.1.13| +---+ 1 row in set (0.00 sec) mysql mysql select * from test; ++-+---++--+ | id | val1| val2 | val3 | val4 | ++-+---++--+ | 1 | 36705.6 | 9.999 | 99.999 | 1.00 | ++-+---++--+ 1 row in set (0.00 sec) How is it possible that the two versions behave so differently? Regards, Martin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a struggle with float type display and width
I searched some more and then I found Bug # 10897 - FLOAT and DOUBLE obeying precision and scale. No documentation. http://bugs.mysql.com/bug.php?id=10897 Regards, Martin. Hi, I've recently changed MySQL versions and I am puzzled by the difference in display of float values. CREATE TABLE test ( id int unsigned NOT NULL auto_increment, val1 float default NULL, val2 float(3,3) default NULL, val3 float(5,3) default NULL, val4 float(10,6) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; INSERT INTO test VALUES (1,36705.586,36705.586,36705.586,36705.586); select version(); select * from test; drop table test; === OUTPUT 1 mysql select version(); +-+ | version() | +-+ | 4.0.16-standard | +-+ 1 row in set (0.00 sec) mysql mysql select * from test; ++-+---+---+--+ | id | val1| val2 | val3 | val4 | ++-+---+---+--+ | 1 | 36705.6 | 36705.586 | 36705.586 | 36705.585938 | ++-+---+---+--+ 1 row in set (0.00 sec) === OUTPUT 2 mysql select version(); +---+ | version() | +---+ | 4.1.13| +---+ 1 row in set (0.00 sec) mysql mysql select * from test; ++-+---++--+ | id | val1| val2 | val3 | val4 | ++-+---++--+ | 1 | 36705.6 | 9.999 | 99.999 | 1.00 | ++-+---++--+ 1 row in set (0.00 sec) How is it possible that the two versions behave so differently? Regards, Martin. -- 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]
mysqldump large file (2GBs) : some tests
Hello This is kind of a follow up of the previous thread Got errno 27 on write. file too large and kind of a survey. The problem was : on a solaris 9, 64 bits and mysql 4.1.7, I could not mysqldump in a file more than 2G . Thank you to the people that help me. I make a new thread since my question is different: is there anybody using that version of mysql (more or less) and that version of Solaris and be able to mysqldump more than 2GBs. If so I would appreciate to have more details on revision etc. ? Here is why I am asking this question: Since it appears it was the revision on my server that might be the problem, but because the upgrade could not be done in short term and because Sun Support did not find any problem of that sort on the system, I decide to forget about the mysqldump for now and copy files for backup. But then I wondered if, later on, as my datafiles increase in size, I will have the problem with the 2 gbs limitation for the datafiles themselves . Which would be much worse. But I succeded in dumping 4gbs in one table. Good news! Then I wonder if my OS allows to do that, how come it does not work for mysqldump. Thank you in advance Johanne Duhaime [EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 11:06 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert:
mysql_stmt_bind_param error
I tried to execute the example code (see below) from the function mysql_stmt_execute I found in mysql 4.1 documentation (http://dev.mysql.com/doc/refman/4.1/en/mysql-stmt-execute.html). This source code runs perfect under linux red hat with version 4.1.7 but it prints out an error if I try to run it under windows XP SP2. This is the output of this code prepare, INSERT successful total parameters in INSERT: 3 mysql_stmt_bind_param() failed Using unsupported buffer type: 838860800 (parameter: 2) It seems that the function mysql_stmt_bind_param is not working under this environment. I tried installing version 4.1.20 and linking my program with the new library but the result is the same. Is that a bug of windows version? Can anyone help? Thanks in advanced Javier Arias /*** **/ #define STRING_SIZE 50 #define DROP_SAMPLE_TABLE DROP TABLE IF EXISTS test_table #define CREATE_SAMPLE_TABLE CREATE TABLE test_table(col1 INT,\ col2 VARCHAR(40),\ col3 SMALLINT,\ col4 TIMESTAMP) #define INSERT_SAMPLE INSERT INTO test_table(col1,col2,col3) VALUES(?,?,?) MYSQL_STMT*stmt; MYSQL_BINDbind[3]; my_ulonglong affected_rows; int param_count; short small_data; int int_data; char str_data[STRING_SIZE]; unsigned long str_length; my_bool is_null; if (mysql_query(mysql, DROP_SAMPLE_TABLE)) { fprintf(stderr, DROP TABLE failed\n); fprintf(stderr, %s\n, mysql_error(mysql)); exit(0); } if (mysql_query(mysql, CREATE_SAMPLE_TABLE)) { fprintf(stderr, CREATE TABLE failed\n); fprintf(stderr, %s\n, mysql_error(mysql)); exit(0); } /* Prepare an INSERT query with 3 parameters */ /* (the TIMESTAMP column is not named; the server */ /* sets it to the current date and time) */ stmt = mysql_stmt_init(mysql); if (!stmt) { fprintf(stderr, mysql_stmt_init(), out of memory\n); exit(0); } if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE))) { fprintf(stderr, mysql_stmt_prepare(), INSERT failed\n); fprintf(stderr, %s\n, mysql_stmt_error(stmt)); exit(0); } fprintf(stdout, prepare, INSERT successful\n); /* Get the parameter count from the statement */ param_count= mysql_stmt_param_count(stmt); fprintf(stdout, total parameters in INSERT: %d\n, param_count); if (param_count != 3) /* validate parameter count */ { fprintf(stderr, invalid parameter count returned by MySQL\n); exit(0); } /* Bind the data for all 3 parameters */ memset(bind, 0, sizeof(bind)); /* INTEGER PARAM */ /* This is a number type, so there is no need to specify buffer_length */ bind[0].buffer_type= MYSQL_TYPE_LONG; bind[0].buffer= (char *)int_data; bind[0].is_null= 0; bind[0].length= 0; /* STRING PARAM */ bind[1].buffer_type= MYSQL_TYPE_STRING; bind[1].buffer= (char *)str_data; bind[1].buffer_length= STRING_SIZE; bind[1].is_null= 0; bind[1].length= str_length; /* SMALLINT PARAM */ bind[2].buffer_type= MYSQL_TYPE_SHORT; bind[2].buffer= (char *)small_data; bind[2].is_null= is_null; bind[2].length= 0; /* Bind the buffers */ if (mysql_stmt_bind_param(stmt, bind)) { /*TH E ERROR IS HERE/ fprintf(stderr, mysql_stmt_bind_param() failed\n); fprintf(stderr, %s\n, mysql_stmt_error(stmt)); exit(0); }
Re: Compilation Failure
Hi Ben, all! Ben Clewett wrote (re-ordered): Chris, Chris White wrote: On Monday 24 July 2006 08:36 am, Ben Clewett wrote: Making all in storage/archive if gcc -DHAVE_CONFIG_H -I. -I. -I../.. -I../../include -I../../include -DDBUG_OFF -O2 -mpowerpc64 -mminimal-toc -maix64 -Wa,-many -MT azio.o -MD -MP -MF .deps/azio.Tpo -c -o azio.o azio.c; then mv -f .deps/azio.Tpo .deps/azio.Po; else rm -f .deps/azio.Tpo; exit 1; fi In file included from ../../include/my_sys.h:825, from ../../mysys/mysys_priv.h:18, from azlib.h:36, from azio.c:17: /usr/include/sys/mman.h:115: error: conflicting types for `mmap64' /usr/include/sys/mman.h:110: error: previous declaration of `mmap64' In file included from azio.c:17: azlib.h:147:1: warning: Z_ASCII redefined In file included from azlib.h:34, from azio.c:17: /opt/freeware/include/zlib.h:175:1: warning: this is the location of the previous definition Well, looking things over either a) glibc headers are jacked up (which is what /usr/include/sys/mman.h is installed by). If things worked before, check if any updates to glibc were done. Otherwise I'd just google around a bit for AIX+mmap64 and see what comes up. Previous version which did compile was 4.1.9. May be glibc was not required for this version. Glibc is not native to AIX so I can see some fun ahead. First: The binaries built and distributed by MySQL for AIX are done using the IBM C compiler, not gcc, so we at MySQL do not encounter this problem - that's why I can only speculate. I know that gcc on AIX uses the header files provided by IBM, but modifies them when gcc is installed - a vague memory says the tool for this is called fixincludes. Try to search for this, there are good chances in the archives of comp.unix.aix (or via Google Groups). AFAIR, gcc on AIX does not link to glibc but to IBM's libc, that's why the system headers must be used. According to the error message, the system-provided header file /usr/include/sys/mman.h is now inconsistent between lines 110 and 115 - this might be a problem caused by fixincludes. Can you check the file and see what is happening there, maybe post the lines here or on comp.unix.aix ? The warning about Z_ASCII being redefined seems to be less critical, but you should check that also to be sure. I propose you look at the two conflicting definitions of Z_ASCII and compare them. If you have sources for the zlib and can compile them using gcc, this might be a way out (after you solved the conflict of the definitions). If you do not need the archive table handler, you might also exclude that from the configuration and try without it - bypassing the problem, not solving it. Regarding the successful build of 4.1.9: Most likely it did not need the header file(s) in question, so the inconsistency did not matter. Also, if you upgraded gcc in the meantime, this might also be a reason. I hope you get it solved! Regards, Joerg PS: Curious: 1) What is your reason to compile yourself ? 2) I assume using IBM C is no alternative, because you do not have it ? 3) Remember that 5.1.6 does not have production quality, and please check the changelogs so that you do not get stuck on a later change. -- Joerg Bruehe, Senior Production Engineer 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]
replicated queries are not placed in the binary log of dual master replication set up
Hi, I'm building a dual master set up that looks something like this: master A - master B || slave A slave B Replication between the masters works. Replication between the slaves and their respective masters works (ie, inserts on master A are replicated to slave A, and inserts on master B are replicated to slave B). However, queries that are run on master A, do *not* make it all the way to slave B. The queries execute on master B but I have verified that they are not being written to master B's binary log. Hence, slave B is not seeing the updates. I can't find any extra configuration options in the docs that shed light on this issue. Have I missed something, or have I found a bug? I'm running mysql-5.0.22 on Solaris. Thanks!
mysql + LVS
How stable has it become to run an instance of mysql ontop of a LVS cluster to produce a massively parrallel system to be used for huge databases? Back in the day i remember tyring to do it with mosix, not sure where they are now a days. but either way i am looking for some current information on the subject. I know this differs from the idea that you can have a mysql cluster that preforms reads on all the slaves, and writes only on the master. thanks winn johnston __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql + LVS
Hello, Just to make sure we separate the two... mysql cluster that preforms reads on all the slaves, and writes only on the master. The above can be used to describe a typical use case for MySQL Replication. In a MySQL Cluster there is no need to load-balance your reads and writes. Thanks, Jimmy Guerrero MySQL, Inc -Original Message- From: Winn Johnston [mailto:[EMAIL PROTECTED] Sent: Monday, July 24, 2006 3:26 PM To: mysql@lists.mysql.com Subject: mysql + LVS How stable has it become to run an instance of mysql ontop of a LVS cluster to produce a massively parrallel system to be used for huge databases? Back in the day i remember tyring to do it with mosix, not sure where they are now a days. but either way i am looking for some current information on the subject. I know this differs from the idea that you can have a mysql cluster that preforms reads on all the slaves, and writes only on the master. thanks winn johnston __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Granting all to a user with a db name prefix
Hello All, I'm trying to GRANT ALL to a user only on DBs that math a prefix, but i can't find the way to so it on the documentation. Let's assume the username is john. I want him to have all privileges only on databases with the prefix john, so he can: - create and drop databases starting ONLY with john (like john_sessions, john123, john_mytest, john_mail, etc) - have any kind of privileges on such databases According to the documentation, i can use % and _ as wildcards. However, mysql gives my an error if i try to use % wildcard. Only _ wildcard is accepted, but the following example: GRANT ALL ON JOHN_.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; ..only allows user john to create databases starting with john, followed by ONE single character. Using this, i can give 32 different grants in order to allow up to 32 characters after 'john', but i'm sure that's not the way. If i try the wildcard %, i get an error. I've tried the following: GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN%'.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN%'.'*' to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON JOHN*.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN*'.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; ..and almost all similar ways. Am i missing something? I temporarily fixed the problem by directly editing mysql.db to change the wildcard _ for % in the respective record, and it works fine. However, i really want to know the right way to do it. I hope someone there gives me the answer. Thanks a lot, have fun. Francisco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 9:19 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: Replication Problem
David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Another suggestion, look for other files on the system ending in .MYD or put a query through that you know has updated a particular table/database and do a find looking for a file that has been updated in the last 5 minutes. See which ones have. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 10:18 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: Replication Problem
David, that error is only when doing a stop/shutdown. So it doesnt make a lot of sense that would be the problem. Here's the log where I did a stop and start. 060724 20:54:25 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown 060724 20:54:25 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060724 20:54:25 [Note] Slave I/O thread killed while reading event 060724 20:54:25 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.01', position 5582202 060724 20:54:25 [Note] Error reading relay log event: slave SQL thread was killed 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 6 user: 'bharvey' 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 5 user: 'bharvey' 060724 20:54:28 InnoDB: Starting shutdown... 060724 20:54:29 InnoDB: Shutdown completed; log sequence number 0 44044 060724 20:54:29 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete 060724 20:55:10 InnoDB: Started; log sequence number 0 44044 060724 20:55:10 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.22-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060724 20:55:10 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.01' at position 5582202, relay log '.\FMSbuilding-relay-bin.05' position: 17820 060724 20:55:10 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.01' at position 5582202 I did some manual inserts into the database on the slave, just to make sure there was no problem - those worked fine and were searchable. Brett Are there any other messages apart from the 2013 error? Are there any options on the filesystem that are perhaps stopping any writing by the mysql user? At this point, I have to say I'm just about out of ideas. Regards -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]