mysqldump feature request
Hello, I have a feature request for mysqldump that would greatly ease a current task I have; in my replicated setup my master is currently MyISAM (as are most slaves), but I have one slave that is InnoDB for testing. Somehow or other, it's gotten itself out of sync, so I'm in the position of needing to resync it. If I were to do the usual mysqldump --add-drop-table $db | mysql then everything will be recreated as MyISAM. If mysqldump had a couple of extra options; --truncate-table --create-if-not-exists Which, respectively, truncate a table before inserting any rows to it, and only create a table if it doesn't already exist (merely by placing the relevant already-implemented commands in 4.1 in the sql dump) I would have a one-step process for resyncing my MyISAM master to a slave of differing table types, by keeping the already-created slave tables. I'm sure these could probably come in useful for other scenarios too. Would this be possible/feasible/useful to anyone else? Thanks, -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I dissable transactions?
Dear MySQL, I am having a lot of problems deleting a large amount of data. Say 20GB from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, but bad hardware), then MySQL roles back the transaction, which takes as many hours and starting the transaction. I also get this a lot: Lock wait timeout exceeded; Try restarting transaction With InnoDB is there a way of completely disabling transactions on a session. So I can delete data without rollback and on bad termination, can restart and continue deleting where I left off? Many thanks for an answer to this problem, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more complexity (was: select where multiple joined records match)
In article [EMAIL PROTECTED], AM Thomas [EMAIL PROTECTED] writes: Now, if I understand how this is working: SELECT r.TITLE FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID) WHERE g.SUBJECT = 'English' AND (g.GRADE = 1 OR g.GRADE = 2) GROUP BY r.ID HAVING COUNT(*) = 2; will give an incorrect result, because the number of rows returned for each matching ID will be unpredictable. It could be 7 rows for ID = 1 (which is a correct match), or 3 rows for ID = 3 (which shouldn't match since it only has grade 2). How about HAVING count(DISTINCT g.grade) = 2? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: increased disk ops after alter table
Hello. The problem could be related to the filesystem layer. You may use a raw disk partition or decrease the size of your tablespace (if it is autoextend). Pradeep Hodigere [EMAIL PROTECTED] wrote: Hi All, I had to do an alter table on a very large InnoDB table (100 million rows avg. row size 300 bytes inclusive of index length). This table is on a 200G disk and the server has 4G RAM. I also archived 50 million rows to another table. So the total table size is now 1/2 of its original. Since the alter table was run, a reporter process (that does selects on the table) has been taking longer than before. The performance has been degrading with evey attempt to fix the issue. I tried the following to fix the problem: 1. analyze table table name; 2. alter table table Name type=InnoDB; (to fix defragmented index data after large deletes) 3. alter table table name order by timestamp; (to make the data sequential if the alter table from previous steps had screwed up the order) Each of the above steps (done over a few days) have progressively degraded the performance of the reporting process to an extent that it now takes twice the time to run the same queries. The disk is 100% busy when the query is being executed. Before the alter table was run, the disk was Idle all thro' the reporter's run and there was very little disk access. It seemed that the data was being served from the filesystem cache. Running explain on the queries indicate that the correct index is being used and there are no table scans. Although all the queries run by the reporter are now logged as slow the number of rows examined is always equal to number of rows sent. Has anyone come across a similar problem. If so, what should i do to fix it? Your responses would be greatly appriciated. thanks, -pradeep PS: The InnoDB buffer pool size was also increased to 800MB after the alter table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql ended problem
Hello. See: http://dev.mysql.com/doc/mysql/en/communication-errors.html Sorry I missed to attach the error logs. I could not infer from the log files. Below line is getting printed when I try to start MySql 050211 2:35:14 Aborted connection 12 to db: 'AMDB' user: 'root' host: `localhost.localdomain' (Got an error reading communication packets) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why MySQL doesn't cache queries that populate temp tables?
Hello. As said at: http://dev.mysql.com/doc/mysql/en/query-cache-how.html A query will not be cached, if it uses TEMPORARY tables. Homam S.A. [EMAIL PROTECTED] wrote: Why MySQL insists on ignoring the query cache whenever I use the same query repeatedly to populate a temp table? So I have: create temporary table MyTable select SQL_CACHE * from SomeTable WHERE (A bunch of criteria) limit 1000; SomeTable is a read-only table. If I issue the query without the temp table population, it gets cached fine. It's just when I use the temp table MySQL stops caching. Is there a way to force MySQL to repopulate the temp table from the cache? Thanks! __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My Problem.Help me
Hello. Why do you link both libmysqld and libmysqlclient with you file at once? Mohsen Pahlevanzadeh [EMAIL PROTECTED] wrote: Dears,I have following Makefile : INCS=-I/usr/include/mysql LIBS=-L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm LIBS_R=-L/usr/lib/mysql -lmysqlclient_r -lpthread -lz -lcrypt -lnsl -lm -lpthread MYSQL_LIBS=-L/usr/lib/mysql -lmysqld -lpthread -lz -lcrypt -lnsl -lm -lpthread -lrt CXXFLAGS=-march=i486 -mcpu=i686 CXX=g++ sql2sql : sql2sql.o $(CXX) $(LIBS) $(LIBS_R) $(MYSQL_LIBS) -o sql2sql sql2sql.o ; sql2sql.o : sql2sql.cpp $(CXX) -c $(INCS) core.cpp mysql_engine.cpp sql2sql.cpp; clean : rm -rf sql2sql.o mysql.o core.o But when i run make utility,I receive following error: g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib/mysql -lmysqlclient_r -lpthread -lz -lcrypt -lnsl -lm -lpthread -L/usr/lib/mysql -lmysqld -lpthread -lz -lcrypt -lnsl -lm -lpthread -lrt -o sql2sql sql2sql.o ; /usr/lib/gcc-lib/i486-slackware-linux/3.3.4/../../../../i486-slackware-linux/bin/ld: cannot find -lmysqld collect2: ld returned 1 exit status make: *** [sql2sql] Error 1 My distro is Slackware 10.0 .I didn't add anything to my Linux.It mean i just i don't install everything to my Linux. Please help me.. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert to character set (upgrading from 4.0 to 4.1)
Hello. As said at: http://dev.mysql.com/doc/mysql/en/charset-conversion.html You should avoid trying to convert directly from latin1 to the real character set. If you have a backup, import a table from it. Check that your character_set_xxx variables have a corresponding values. See: http://dev.mysql.com/doc/mysql/en/charset-defaults.html Eli [EMAIL PROTECTED] wrote: Hi.. I encountered some problems with character sets. On the 4.0 version I stored UTF-8 strings. When upgraded to 4.1, I saw the default character set was 'latin1', so I converted to UTF-8 using this: ALTER TABLE my_table CONVERT TO CHARACTER SET 'utf8', DEFAULT CHARACTER SET 'utf8'; When viewing the pages, I see that some of the characters were corrupted (the same chars always). I tried to return to convert back to 'latin1' but the problem remained. What was the problem? How can I fix it? BTW: for some reason I cannot reply to posts in this mailing list using Thunderbird. (On PHP mailing list replying works). please help... -thanks, Eli -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump feature request
Hello. You can solve your problem using --all command line option (--create-options after 4.1.2) which is on by default as of MySQL 4.1. Chris Elsworth [EMAIL PROTECTED] wrote: Hello, I have a feature request for mysqldump that would greatly ease a current task I have; in my replicated setup my master is currently MyISAM (as are most slaves), but I have one slave that is InnoDB for testing. Somehow or other, it's gotten itself out of sync, so I'm in the position of needing to resync it. If I were to do the usual mysqldump --add-drop-table $db | mysql then everything will be recreated as MyISAM. If mysqldump had a couple of extra options; --truncate-table --create-if-not-exists Which, respectively, truncate a table before inserting any rows to it, and only create a table if it doesn't already exist (merely by placing the relevant already-implemented commands in 4.1 in the sql dump) I would have a one-step process for resyncing my MyISAM master to a slave of differing table types, by keeping the already-created slave tables. I'm sure these could probably come in useful for other scenarios too. Would this be possible/feasible/useful to anyone else? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: instable behaviour of mysql
Hello. Please send us an ouput of the following statement, which you should perform in the middle of your test case: show grants for current_user(); Can you find, if your grants change? Can you reproduce a problem on the latest release (4.1.9 now)? schlubediwup [EMAIL PROTECTED] wrote: Hi again mysql-listers regarding the problem i mentionned this morning: for a description check out my message of this morning. in the meantime i have found out 1. how to reproduce the problem 2. how to work around the problem 1. how to reproduce: DROP TABLE afir_accounts ; DROP TABLE afir_contacts ; DROP TABLE afir_knowledge ; DROP TABLE afir_tasks ; DROP TABLE afir_timer ; DELETE FROM participants WHERE ident = 'afir' ; DELETE FROM part_val WHERE ident = 'afir' ; REVOKE all ON afir_accounts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_contacts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_knowledge FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_tasks FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_timer FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_files FROM 'afir'@'myhost.tld' ; REVOKE select ON participants FROM 'afir'@'myhost.tld' ; REVOKE select,update ON part_val FROM 'afir'@'myhost.tld' ; REVOKE GRANT OPTION ON *.* FROM 'afir'@'myhost.tld' ; DROP USER 'afir'@'myhost.tld'; you may substitute whatever string for afir. after such a sequence of commands the root-userid i described in my message earlier today cannot be used to access the mysql db anymore. you get the error message host myhost.tld is not allowed to access this mysql server. 2. how to work around i just added FLUSH PRIVILEGES ; to the above sequence, and everything works well. this command, however, according to mysql docu, should not be necessary after such a sequence of commands. suomi -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 Character set problem ( Ithink?)
Hello. Please tell us, what output the following statement produces: SHOW VARIABLES LIKE '%char%'; You can use hexademical values for inserting the data. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html Ian Gibbons [EMAIL PROTECTED] wrote: Hi List, I am having a strange problem on Linux Fedora Core 3 with MySQL 4.1.7 ( of= fical mysql rpms). The data was originally stored in MySQL 3.something and was = placed into the database via a MySQLDump file. It is too late to reload the data= . I have a table called fees: CREATE TABLE `fees` ( `refID` int(11) NOT NULL default '0', `price` text, `tuitionFee` tinyint(4) default NULL, `examFee` tinyint(4) default NULL, `otherFee` tinyint(4) default NULL, `feeText` text, `pending` tinyint(4) default '0', PRIMARY KEY (`refID`), KEY `refID` (`refID`) ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin2 When I try updating the price field for one record, it doesn't seem to rec= ognise the pound sign (=A3): mysql UPDATE fees SET price=3D '=A345' WHERE refID=3D732; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql select price from fees where refID=3D732; +---+ | price | +---+ | ?45 | +---+ 1 row in set (0.00 sec) The same result ?45 is returned via php as well, so its not a console disp= lay problem. I have also tried this with the latin1 character set with the same results= . I know I am probably better off changing the field type to a double and pl= acing the pound sign in my php code, but I am curious as to why this happens. Is it a problem with the character sets? Should I be using a different ch= aracter set for English language text ( no international chars ). Any help will be appreciated. Ian -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query: Get 100 itemid's for each id.
This is my table: CREATE TABLE items ( id int(10) UNSIGNED NOT NULL default '0', itemid int(10) UNSIGNED NOT NULL auto_increment, PRIMARY KEY (`id`) ); I'd like to get 100 itemid's for each id. Is that possible. If so, please show me how. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 Character set problem ( Ithink?)
On 12 Feb 2005 at 14:09, Gleb Paharenko wrote: Hello. Please tell us, what output the following statement produces: SHOW VARIABLES LIKE '%char%'; Hi Gleb, mysql SHOW VARIABLES LIKE '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.08 sec) I assume the character_set_system being utf8 is the problem, but how do I change it? You can use hexademical values for inserting the data. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html I've taken a look at this and it could prove very useful. I wish I had the time to read the whole manual! Thanks Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query: Get 100 itemid's for each id.
This is my table: CREATE TABLE items ( id int(10) UNSIGNED NOT NULL default '0', itemid int(10) UNSIGNED NOT NULL auto_increment, PRIMARY KEY (`itemid`) ); I'd like to get 100 itemid's for each id. Is that possible. If so, please show me how. - you have id set as the primary key... which means each value of it will be unique, so you cannot have more than one value of itemid for each value of id. Your right. I have changed the key to itemid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query: Get 100 itemid's for each id.
This is my table: CREATE TABLE items ( id int(10) UNSIGNED NOT NULL default '0', itemid int(10) UNSIGNED NOT NULL auto_increment, PRIMARY KEY (`itemid`) ); I'd like to get 100 itemid's for each id. I guess I could solve my problem with a subselect. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto-increment field stops working
Hi list, I have a very strange (and worrying..!!!) problem with my tables. I'm running a 4.0.9 gamma (no choice for that) adn I had a MyIsam table with an auto-increment field. Everything worked fine friday, and today the auto-increment field is just not working anymore. When I want to insert a new value, the auto-index is blocked on number 127. Then it complains: duplicate entry for key 127. I deleted thatrecord, re insert a new row, and again same problem My table is basic taskId (int auto-increment) ownerId (int) targetId(int) date (date) My insert is just insert into tableTask (`ownerId`,`targetId`,`date`) values ($ownerId,$targetId,'$date') I think this syntax is correct, the auto_increment field should be generated automatically (it has always been until today) Does anybody ever had such a problem? I put a backup from friday on line it works fine, but of course I lost a few data. Can it come from a table corruption? any other ideas?I'm just worrying how I could know that kind of error, because it's one of my users who warned me I have a problem or I wouldn't have noticed it. Thanks a lot for your help. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL dump (OT?)
Hi all, This is perhaps off-topic, but I need to dump my MySQL database into a format that FileMaker Pro will understand. Does anyone have any tips for doing this? Thanks in advance, Erich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto-increment stops at 127
additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated.. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: auto-increment stops at 127
Change the column from a TINYINT (which has a maximum value of 127), see here: http://dev.mysql.com/doc/mysql/en/numeric-types.html HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: 14 February 2005 13:55 To: mysql@lists.mysql.com Subject: auto-increment stops at 127 additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated.. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- 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 Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto-increment field stops working
Hi, could you check to make absolutely sure that your taskId column isn't tinyint, which should explain it as it's max ( being signed ) is 127. /Johan mel list_php wrote: Hi list, I have a very strange (and worrying..!!!) problem with my tables. I'm running a 4.0.9 gamma (no choice for that) adn I had a MyIsam table with an auto-increment field. Everything worked fine friday, and today the auto-increment field is just not working anymore. When I want to insert a new value, the auto-index is blocked on number 127. Then it complains: duplicate entry for key 127. I deleted thatrecord, re insert a new row, and again same problem My table is basic taskId (int auto-increment) ownerId (int) targetId(int) date (date) My insert is just insert into tableTask (`ownerId`,`targetId`,`date`) values ($ownerId,$targetId,'$date') I think this syntax is correct, the auto_increment field should be generated automatically (it has always been until today) Does anybody ever had such a problem? I put a backup from friday on line it works fine, but of course I lost a few data. Can it come from a table corruption? any other ideas?I'm just worrying how I could know that kind of error, because it's one of my users who warned me I have a problem or I wouldn't have noticed it. Thanks a lot for your help. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto-increment stops at 127
mel list_php [EMAIL PROTECTED] wrote on 14/02/2005 13:54:35: additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated.. Please show your table description. This behaviour corresponds to the AUTO_INCREMENT column being defined as a TINYINT, range -128..+127. You probably need to change the definition of your key column. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select last row
Hi All, I have a table which is being continuosly updated, I just wanted to know how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select last row
Mulley, Nikhil wrote: Hi All, I have a table which is being continuosly updated, I just wanted to know how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil Do you use a recno? If you have 4.1 you can use select * from table where recno = (select max recno from table) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select last row
If you have an auto_increment column, SELECT * FROM my_table ORDER BY auto_increment_col DESC LIMIT 1 If you don't have an auto_increment this might be a good time to add one. -Original Message- From: Mulley, Nikhil [mailto:[EMAIL PROTECTED] Sent: Monday, February 14, 2005 08:15 To: mysql@lists.mysql.com Subject: select last row Hi All, I have a table which is being continuosly updated, I just wanted to know how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL dump (OT?)
You could try setting up FileMaker to query MySQL directly, using ODBC. Otherwise, you can't create a FileMaker database directly, so you need to create an import format that FileMaker understands (tab, command, sylk, etc.). You would need to keep in mind that you may have embedded returns and/or tabs in your data that would screw up things like tab-delimited or comma delimited formats. For embedded returns, FileMaker uses vertical tabs (ascii 11). So you would need to convert all your returns (ascii 13) to vertical tabs before exporting in a tab-delimited format. Embedded tabs you'll have to come up with some other scheme, like replacing them with 4 spaces before exporting from MySQL. On Feb 14, 2005, at 8:47 AM, Erich Beyrent wrote: Hi all, This is perhaps off-topic, but I need to dump my MySQL database into a format that FileMaker Pro will understand. Does anyone have any tips for doing this? Thanks in advance, Erich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show status - questions variable
Questions: The number of queries that have been sent to the server. Is this the number of queries since the mysql installation, or the number of questions since the last reboot ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
queries slower on InnoDB
Hi, there, I am in the middle of replacing a MyISAM database with InnoDB. Queries show table status and select count are extremely slow which gave me some pain. I am wondering if there are any other queries on InnoDB that are significantly slower than those on MyISAM other than these two? And does MySQL have any plan to speed up this kind of queries? Thank you very much in advance for your reply! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Time in VBA for Excel
Hello, I have a table in my database with two fields set as Time types. The issue I seem to have is when I use an ADO connection in VBA for Excel to select the two fields. Instead of the expected format HH:MM:SS held within my recordset, I get the current date in the format DD/MM/. Then should it reach a record where the time has been set to the default 00:00:00 I get an error Invalid Use of Null I tried outputting the data from the database to a webpage using scripting and I'm returned with the correct data as entered into the database. This is also confirmed when I view the data in the Query Browser. I've gone through every VBA site possible and found no clues so thought there might be a MySQL wizz that knows what is going on Any help or pointers would be greatly appreciated!! Thanks in advance [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I dissable transactions?
On Monday 14 February 2005 03:52 am, Ben Clewett wrote: I am having a lot of problems deleting a large amount of data. Say 20GB from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, but bad hardware), then MySQL roles back the transaction, which takes as many hours and starting the transaction. I also get this a lot: There is a feature of DB2 that can do this.. Its really not always all its cracked up to be.. In this case, it would happily delete, if something goes wrong, your table is now marked bad.. The other 20million rows are now gone.. Is that what you want? What you need to do, is set up a simple script to delete 20,000 rows a time, and commit, just keep doing it till its done.. This way you could do 20,000 rows, wait a bit, do it again. or whatever. If it fails, you only rollback what it was doing during the transaction and you wont have to start all over. Jeff pgpd6v4TIoxmG.pgp Description: PGP signature
Re: show status - questions variable
At 11:00 -0500 2/14/05, Mayuran Yogarajah wrote: Questions: The number of queries that have been sent to the server. Is this the number of queries since the mysql installation, or the number of questions since the last reboot ? An easy test: If you restart the server, you'll see that the variable begins counting from 0 again. -- 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: select where multiple joined records match
Try this Select * from resources, goals where resources.ID = goals.RESOURCE_ID and (SUBJECT=English and GRADE=1) OR (SUBJECT=English and GRADE=2); -Original Message- From: AM Thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, February 13, 2005 7:23 AM To: mysql@lists.mysql.com Subject: select where multiple joined records match I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas -- Virtue of the Small / (919) 929-8687 -- 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 issue: I/O thread dies immediately after START SLAVE with no logged errors
In response to Gleb Paharenko and Bruce Dembecki: Gleb Paharenko wrote: Please send us an output of SHOW MASTER STATUS ans SHOW SLAVE STATUS. Can you reproduce the problem using official binaries? I have included the output of those two commands below. I will try to reproduce the problem using non-debian-specific binaries. (As a side note, I originally attempted to set these servers up with version 4.0.23-4 (debian package) before reinstalling with version 4.1.9-2 (debian package), and I appeared to have the same problem in both cases.) Bruce Dembecki wrote: Looking at your my.cnf files I don't see where you've told the slave what server to connect to. The slave needs to know what server is the master. This is usually accomplished by including a couple of lines in my.cnf. [...] If the master.info file exists and is blank or doesn't include enough information, delete it. If it's there and looks right, include it's contents in your next mail here (you can blank out the username/password info). I used the CHANGE MASTER TO command interactively, rather than setting the master in the config file. I assumed that this would be sufficient, and from the log on the master (see my original email) it does appear that the slave is logging in, at least very briefly. I've included the contents of my master.info file below. It seems appropriate to me, but I've never seen one before. :) Thanks, Tierney mysql SHOW MASTER STATUS\G *** 1. row *** File: mysql-bin.01 Position: 79 Binlog_Do_DB: replicated Binlog_Ignore_DB: mysql,test 1 row in set (0.00 sec) mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Master_Host: 192.168.0.19 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.01 Read_Master_Log_Pos: 79 Relay_Log_File: training1-relay-bin.07 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.01 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: replicated Replicate_Ignore_DB: mysql,test 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: 79 Relay_Log_Space: 4 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: NULL 1 row in set (0.00 sec) # cat master.info 14 mysql-bin.01 79 192.168.0.19 username password 3306 60 0 Tierney Thurban [EMAIL PROTECTED] wrote: Hi all. Sorry if you get this twice -- it was posted to mysql-replication earlier, but it doesn't look like that list is really used. I'm having a problem with my replication setup. This is my first time setting up replication, so this may be a simple problem. I'm using one master and one slave, both running debian-testing, and they both have brand new 4.1.9 mysql installs (via apt-get). The problem is that each time I do a START SLAVE, the I/O thread dies almost immediately. I can see it running only if I do START SLAVE; SHOW SLAVE STATUS\G on a single line. The master's log shows the following each time I START SLAVE or START SLAVE IO_THREAD: 6 Connect slave@IP on 6 Query SELECT UNIX_TIMESTAMP() 6 Query SHOW VARIABLES LIKE 'SERVER_ID' 6 Query SELECT @@GLOBAL.COLLATION_SERVER 6 Query SELECT @@GLOBAL.TIME_ZONE 6 Query SHOW SLAVE HOSTS 6 Quit There are no error messages in the .err file on either server, even with --log-warnings on both. I've added everything appropriate that I've been able to find to my.cnf on each server (see below). If anyone has any suggestions, please let me know -- I've been looking through docs and mailing lists for quite some time now, with no luck. Thanks, Tierney Here's what I did to set up replication: I added / changed a number of fields in my.cnf on both machines (see below). Master: Started mysqld. Created a new database, a new table, and put a single row in it. Created a slave account: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'x' Locked the database: FLUSH TABLES WITH READ LOCK Tar'd the data_dir/replicated directory and transferred it to the slave. Checked the binlog file and position and unlocked the database: SHOW MASTER STATUS UNLOCK TABLES Slave: Started mysqld. Set the master: CHANGE MASTER TO MASTER_HOST='IP',
Re: select where multiple joined records match
Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? - AM On Mon, 14 Feb 2005 12:30:44 -0600, Gordon [EMAIL PROTECTED] wrote: Try this Select * from resources, goals where resources.ID = goals.RESOURCE_ID and (SUBJECT=English and GRADE=1) OR (SUBJECT=English and GRADE=2); -Original Message- From: AM Thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, February 13, 2005 7:23 AM To: mysql@lists.mysql.com Subject: select where multiple joined records match I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas -- Virtue of the Small / (919) 929-8687 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Hi, Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? How about this: SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS goal_a ON resources.id=goal_a.resource_id AND goal_a.subject=English AND goal_a.grade=1 LEFT JOIN goals AS goal_b ON resources.id=goal_b.resource_id AND goal_b.subject=English AND goal_b.grade=2 WHERE goal_a.id IS NOT NULL AND goal_b.id IS NOT NULL Alternately: SELECT resources.id, resources.title, COUNT(*) as nr FROM resources LEFT JOIN goals ON resources.id=goals.resource_id AND goals.subject=English WHERE goals.grade IN (1, 2) GROUP BY resources.id HAVING nr = 2 (The above is untested, since you didn't provide your example table in SQL, and I am unwilling to re-format it into CREATE TABLE/INSERT statemnts to test things. The concepts are solid.) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Empty 'Relay_Master_Log_File'?
Yesterday I set up 4 new MySQL 4.0.22 replication slaves on 2 FreeBSD 4.10-R machines (via mysqld_multi). We have 10 other identical slaves and I followed the same procedure as always when seeding them. The new slaves were working fine, however when I issued SHOW SLAVE STATUS the Relay_Master_Log_File field was empty. I reseeded one of them [with freshly created seeds] a couple of times with the same result every time. Then this morning I woke up and found that Relay_Master_Log_File was populated.. I haven't been able to find anything about this online yet, and I am wondering if anyone else has experienced this? The reason why I caught this is that our monitoring software relies on that field being populated to calculate replication delay. Thanks, Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Dan Wareham wrote:
Hello,
I have a table in my database with two fields set as Time types.
The issue I seem to have is when I use an ADO connection in VBA for
Excel to select the two fields.
Instead of the expected format HH:MM:SS held within my recordset, I
get the current date in the format DD/MM/. Then should it reach a
record where the time has been set to the default 00:00:00 I get an
error Invalid Use of Null
I tried outputting the data from the database to a webpage using
scripting and I'm returned with the correct data as entered into the
database. This is also confirmed when I view the data in the Query
Browser.
I've gone through every VBA site possible and found no clues so
thought there might be a MySQL wizz that knows what is going on
Any help or pointers would be greatly appreciated!!
Thanks in advance
[EMAIL PROTECTED]
I'm not sure about Excel, but Access doesn't understand Time. You have
to give it DateTime, in the format: '30-12-1899 HH:MM:SS'. All DateTime
values starting with '30-12-1899' are interpreted as being a Time field.
Pretty strange stuff. Having said that, we have a number of scripts that
pull Time values into Excel without the issue you're experiencing. In
some places, we just chuck a single quote at the start of the Time value:
select concat('\'', SomeTimeColumn) as MyTime
You could also try casting your values as text:
select cast(SomeTimeColumn as varchar(20) as MyTime
... or something like that. Can't remember if that's exactly how cast()
works, but this is the general idea.
Otherwise post your table def / code and I'll test it out.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty 'Relay_Master_Log_File'?
Hi Atle, Yesterday I set up 4 new MySQL 4.0.22 replication slaves on 2 FreeBSD 4.10-R machines (via mysqld_multi). We have 10 other identical slaves and I followed the same procedure as always when seeding them. The new slaves were working fine, however when I issued SHOW SLAVE STATUS the Relay_Master_Log_File field was empty. I reseeded one of them [with freshly created seeds] a couple of times with the same result every time. Then this morning I woke up and found that Relay_Master_Log_File was populated.. I haven't been able to find anything about this online yet, and I am wondering if anyone else has experienced this? The reason why I caught this is that our monitoring software relies on that field being populated to calculate replication delay. AFAIK, the log file name is not known by the slave unless it either receives a rotate log event (go to next log) or you have started it against a particular log file. If you start replication from the beginning (not specifying a log file) against a master, the first filename is not passed down. In order to force a filename to be displayed you could either: FLUSH LOGS on the master, or specify the log file name in the CHANGE MASTER on the slave. Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
collate latin1_general_ci Error
I just installed a new software package that included a MySQL upgrade. Everything was working fine until I tried to import one of my tables to my online database and got this error: #1064 - 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 'collate latin1_general_ci NOT NULL default '', `IDRealm` varc What does this mean, and what's the solution? Also, I noticed that when I create tables, I no longer have a choice for InnoDB, so I've been choosing MyISAM. If I leave it at default, what kind of table would I get? Actually, I'm leaning towards MyISAM anyway. I thought InnoDB were generally considered superior, but when my computer crashed, I lost most of my InnoDB tables but none of my MyISAM tables. Thanks. __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: queries slower on InnoDB
Total row count is cached in the header for MyISAM tables. InnoDB has no such mechanism for this because transactions make it impossible to keep an exact row count. In order for InnoDB to get a row count it has to do a full scan inside a transaction which will take a long time. One way around this is to maintain row count in another table. For show table status it's better to only check the tables you need. Example: show table status like 'my_table' I'm not aware of any plans to speed up either of these commands on InnoDB. On Mon, 14 Feb 2005 12:06:09 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, I am in the middle of replacing a MyISAM database with InnoDB. Queries show table status and select count are extremely slow which gave me some pain. I am wondering if there are any other queries on InnoDB that are significantly slower than those on MyISAM other than these two? And does MySQL have any plan to speed up this kind of queries? Thank you very much in advance for your reply! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty 'Relay_Master_Log_File'?
On Mon, 14 Feb 2005, Jeremy Cole wrote: AFAIK, the log file name is not known by the slave unless it either receives a rotate log event (go to next log) or you have started it against a particular log file. If you start replication from the beginning (not specifying a log file) against a master, the first filename is not passed down. In order to force a filename to be displayed you could either: FLUSH LOGS on the master, or specify the log file name in the CHANGE MASTER on the slave. Hi Jeremy, If that's the case it would have to mean that the SQL thread knew about it while the IO thread did not, correct? Here's a snip of the SHOW SLAVE STATUS from yesterday: [..] Master_Log_File: db1-bin.248 Read_Master_Log_Pos: 428653772 Relay_Log_File: db9-relay-bin.002 Relay_Log_Pos: 1186147 Relay_Master_Log_File: [..] The way the seeding was done was that the slave got a copy of the replicated DB + master.info from an existing slave. Thanks, Atle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join speed vs. 2 queries
Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND data.name_id = names.id AND data.value_id = value.id; This query should pull out all of the index content from the data table for campaign 22. name_id and value_id are indexed, as are the name and value fields of the names and values tables. campaign_id is also indexed in the data table and each name and value is unique per campaign. The vardata dataset for this campaign that has around 163000 entries and the above query takes nearly a minute to run. Total size of data table is around 3 million records. On the same machine, the following query takes roughly 2 seconds to run: CREATE TEMPORARY TABLE IF NOT EXISTS names_temp SELECT names.id as var_id, values.id as val_id FROM values, names WHERE names.campaign_id = 22 AND values.campaign_id = names.campaign_id AND names.name = 'content' AND values.value = 'index'; SELECT COUNT(*) FROM vardata, names_temp WHERE vardata.varNameId = names_temp.var_id AND vardata.varValueId = names_temp.val_id; After looking at EXPLAIN for both, I understand that the latter is faster because it is doing lookups based on constant values, but I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table... Is there any way to optimize the performance of the join query without having to go with the two-query option? -- Thanks, Mathew .. Mathew J. Ray Sr. Interactive Developer IQ Television Group .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty 'Relay_Master_Log_File'?
Did you copy the relay-log.info and relay logs from the seed slave? Relay_Master_Log_file is line 3 in the relay-log.info file. This file is managed by the SQL thread and helps the slave keep track of what it has execute in the relay logs. http://dev.mysql.com/doc/mysql/en/slave-logs.html -Eric On Mon, 14 Feb 2005 14:03:08 -0800 (PST), Atle Veka [EMAIL PROTECTED] wrote: On Mon, 14 Feb 2005, Jeremy Cole wrote: AFAIK, the log file name is not known by the slave unless it either receives a rotate log event (go to next log) or you have started it against a particular log file. If you start replication from the beginning (not specifying a log file) against a master, the first filename is not passed down. In order to force a filename to be displayed you could either: FLUSH LOGS on the master, or specify the log file name in the CHANGE MASTER on the slave. Hi Jeremy, If that's the case it would have to mean that the SQL thread knew about it while the IO thread did not, correct? Here's a snip of the SHOW SLAVE STATUS from yesterday: [..] Master_Log_File: db1-bin.248 Read_Master_Log_Pos: 428653772 Relay_Log_File: db9-relay-bin.002 Relay_Log_Pos: 1186147 Relay_Master_Log_File: [..] The way the seeding was done was that the slave got a copy of the replicated DB + master.info from an existing slave. Thanks, Atle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb error 995
Hi I'm running version MySQL 4.0.20a-debug windows server 2003 on a dedicated database server with an external SCSI RAID array with a 1 TB of disk space (over 400 GIG free) 1 gig of RAM basic settings key buffer 512 MB sort Buffer 32 Mb innof db Buffer Pool Size 512 MB my innodb datafile is about 200 gig is size and I have 6 innodb databses. 3 are very large 40+ gigs with about 800 million records. The other 3 are very small few MB is size. Every two weeks I rebuild one of the large db and use the other 2 as backups. This configuration as worked fine for the last 2 months. I was in the process of rebuilding one of the large databases when I got the following error. 050214 16:46:56 InnoDB: Operating system error number 995 in a file operation. InnoDB: See http://www.innodb.com/ibman.php for installation help. InnoDB: See section 13.2 at http://www.innodb.com/ibman.php InnoDB: about operating system error numbers. InnoDB: File name .\ibdata1 InnoDB: File operation call: 'Windows aio'. InnoDB: Cannot continue operation. after restarting mysql, innodb rolled back my tables in the db that crashed and the rest of my data seems fine. I have googled around and there seems to be some info that this errors is some kind windows driver. Is there any more info regarding this error? Any help would be useful. Thank you -Ben -- Ben Kutsch Developer Suite 304, 121 South 13th ST Dept. of Computer Science and Eng. phone: (402) 472-2340 University of Nebraska-Lincoln fax: (402) 472-3135 Lincoln NE 68588-0115 email: [EMAIL PROTECTED] http://nadss.unl.edu -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty 'Relay_Master_Log_File'?
On Mon, 14 Feb 2005, Eric Bergen wrote: Did you copy the relay-log.info and relay logs from the seed slave? Relay_Master_Log_file is line 3 in the relay-log.info file. This file is managed by the SQL thread and helps the slave keep track of what it has execute in the relay logs. http://dev.mysql.com/doc/mysql/en/slave-logs.html -Eric Hi Eric, I do not include the relay logs/relay-log.info when I seed slaves as from what I understand the new slave will start populating new relay logs starting at the position from master.info. Thanks, Atle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: queries slower on InnoDB
what about other functions like doing a min() or max()? are those slow limitations of innodb tables as well? On Mon, 14 Feb 2005 15:52:21 -0600, Eric Bergen [EMAIL PROTECTED] wrote: Total row count is cached in the header for MyISAM tables. InnoDB has no such mechanism for this because transactions make it impossible to keep an exact row count. In order for InnoDB to get a row count it has to do a full scan inside a transaction which will take a long time. One way around this is to maintain row count in another table. For show table status it's better to only check the tables you need. Example: show table status like 'my_table' I'm not aware of any plans to speed up either of these commands on InnoDB. On Mon, 14 Feb 2005 12:06:09 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, I am in the middle of replacing a MyISAM database with InnoDB. Queries show table status and select count are extremely slow which gave me some pain. I am wondering if there are any other queries on InnoDB that are significantly slower than those on MyISAM other than these two? And does MySQL have any plan to speed up this kind of queries? Thank you very much in advance for your reply! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ryan McCullough mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty 'Relay_Master_Log_File'?
My understanding is that relay-info.log stores Exec_Master_Log_Pos so in order to only use master.info in a snapshot you need to make sure that the slave is caught up when the snapshot is taken. It's common for the I/O thread (controls master.info and downloads logs files) to be a few queries ahead of the SQL thread (controls relay-log.info and executes queries). If I'm wrong here please correct me. If you have Exec_Master_Log_Pos you can use it for Read_Master_Log_Pos on change master command on the new slave so it starts downloading relay logs at the proper position. On Mon, 14 Feb 2005 15:55:49 -0800 (PST), Atle Veka [EMAIL PROTECTED] wrote: On Mon, 14 Feb 2005, Eric Bergen wrote: Did you copy the relay-log.info and relay logs from the seed slave? Relay_Master_Log_file is line 3 in the relay-log.info file. This file is managed by the SQL thread and helps the slave keep track of what it has execute in the relay logs. http://dev.mysql.com/doc/mysql/en/slave-logs.html -Eric Hi Eric, I do not include the relay logs/relay-log.info when I seed slaves as from what I understand the new slave will start populating new relay logs starting at the position from master.info. Thanks, Atle -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty 'Relay_Master_Log_File'?
On Mon, 14 Feb 2005, Eric Bergen wrote: My understanding is that relay-info.log stores Exec_Master_Log_Pos so in order to only use master.info in a snapshot you need to make sure that the slave is caught up when the snapshot is taken. It's common for the I/O thread (controls master.info and downloads logs files) to be a few queries ahead of the SQL thread (controls relay-log.info and executes queries). If I'm wrong here please correct me. If you have Exec_Master_Log_Pos you can use it for Read_Master_Log_Pos on change master command on the new slave so it starts downloading relay logs at the proper position. Eric, interesting point, I will have to get to the bottom of that so that I can ensure that my slaves have identical data! ;) Luckily I alway seed from an idle slave so it should only be minor inconsistencies if that ever occurred. Thanks! Atle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: queries slower on InnoDB
min() and max() can use indexes so they shouldn't be slower. On Mon, 14 Feb 2005 17:13:07 -0700, Ryan McCullough [EMAIL PROTECTED] wrote: what about other functions like doing a min() or max()? are those slow limitations of innodb tables as well? On Mon, 14 Feb 2005 15:52:21 -0600, Eric Bergen [EMAIL PROTECTED] wrote: Total row count is cached in the header for MyISAM tables. InnoDB has no such mechanism for this because transactions make it impossible to keep an exact row count. In order for InnoDB to get a row count it has to do a full scan inside a transaction which will take a long time. One way around this is to maintain row count in another table. For show table status it's better to only check the tables you need. Example: show table status like 'my_table' I'm not aware of any plans to speed up either of these commands on InnoDB. On Mon, 14 Feb 2005 12:06:09 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, I am in the middle of replacing a MyISAM database with InnoDB. Queries show table status and select count are extremely slow which gave me some pain. I am wondering if there are any other queries on InnoDB that are significantly slower than those on MyISAM other than these two? And does MySQL have any plan to speed up this kind of queries? Thank you very much in advance for your reply! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ryan McCullough mailto:[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and gb.GoalNumber='2' where ga.goal_id IS NOT NULL and gb.goal_id IS NOT NULL group by r.id; - AM On Mon, 14 Feb 2005 11:07:48 -0800, Jeremy Cole [EMAIL PROTECTED] wrote: Hi, Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? How about this: SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS goal_a ON resources.id=goal_a.resource_id AND goal_a.subject=English AND goal_a.grade=1 LEFT JOIN goals AS goal_b ON resources.id=goal_b.resource_id AND goal_b.subject=English AND goal_b.grade=2 WHERE goal_a.id IS NOT NULL AND goal_b.id IS NOT NULL Alternately: SELECT resources.id, resources.title, COUNT(*) as nr FROM resources LEFT JOIN goals ON resources.id=goals.resource_id AND goals.subject=English WHERE goals.grade IN (1, 2) GROUP BY resources.id HAVING nr = 2 (The above is untested, since you didn't provide your example table in SQL, and I am unwilling to re-format it into CREATE TABLE/INSERT statemnts to test things. The concepts are solid.) Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to do a simple fulltext match? Thanks!
Hi!
I am having a problem searching a fulltext field. I'm setting up a little code
library program for a few of us who work together. One field (TEXT) is
keywords and it is indexed as well as fulltext. I send a simple query via my
form to the code below. The connection is successfully made, and I attempt to
match my little search (for the word short) contained in the variable
$searchterms. However, I get back the message that $peeky (below) is not a
valid MySql resource. Can someone tell me what I am doing wrong? Thanks so
much!
Cheers!
-Warren
=== Errant Code follows ===
$dbh=mysql_connect (localhost, something_here, something_here) or die
('Connection failed because: ' . mysql_error());
mysql_select_db (thedatabase);
$peeky=MYSQL_QUERY(select MATCH(keywords) AGAINST \$searchterms\ from
CodeLib );
$peek=mysql_fetch_array($peeky);
$howMany=$peek[0];
echo brHowmany=$howMany ;
===
Re: join speed vs. 2 queries
I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table Yep but remember the query engine uses one index per table so without seeing your EXPLAIN output I'd try indexing ... the data table on name_id,value_id,campaign_id, the names table on id,name, the values table on id,value, then writing the query as ... SELECT COUNT(*) FROM data INNER JOIN names ON data.name_id=names.id INNER JOIN values ON data.value_id=values.id WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' PB - Mathew Ray wrote: Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND data.name_id = names.id AND data.value_id = value.id; This query should pull out all of the index content from the data table for campaign 22. name_id and value_id are indexed, as are the name and value fields of the names and values tables. campaign_id is also indexed in the data table and each name and value is unique per campaign. The vardata dataset for this campaign that has around 163000 entries and the above query takes nearly a minute to run. Total size of data table is around 3 million records. On the same machine, the following query takes roughly 2 seconds to run: CREATE TEMPORARY TABLE IF NOT EXISTS names_temp SELECT names.id as var_id, values.id as val_id FROM values, names WHERE names.campaign_id = 22 AND values.campaign_id = names.campaign_id AND names.name = 'content' AND values.value = 'index'; SELECT COUNT(*) FROM vardata, names_temp WHERE vardata.varNameId = names_temp.var_id AND vardata.varValueId = names_temp.val_id; After looking at EXPLAIN for both, I understand that the latter is faster because it is doing lookups based on constant values, but I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table... Is there any way to optimize the performance of the join query without having to go with the two-query option? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 2/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
