Re: more queries vs a bigger one
From: [EMAIL PROTECTED] Subject: Re: more queries vs a bigger one Hello, approx. how long does it take your big query to run as it is now? Are these queries appending a table? or are they buiding a result (from a chain of queries)? Have you tried separating them out? Any difference? -sam That query takes more than 2 minutes. I have a table with less than 90.000 records, and this is the bigger table from the database, so the query is very slow, because as you may see, the tables are not very big. But that query might return more than 9.000 records, even though I limit it to first 30. I will split it into more smaller queries and I will report if it works faster. The query is something like the example below, but I don't have it here right now to send it to the list, but I will send it soon: select a.pre_title, a.title, a.post_title, substring(a.body, 1, n.preview_size) as preview, n.title as publication, a.id_category, cs.label, count(aco.hash_articles) as comments, count(act.hash_articles) as counter from articles a left join newspapers n on(a.id_newspapers=n.id) left join sections s on(a.id_sections=s.id and s.id_newspapers=n.id) left join sections_categories cs on(a.id_categories=cs.id) inner join articles_counters act on(a.body_hash=act.hash_articles) inner join articles_comments aco on(a.body_hash=aco.hash_articles) where a.id_category=20 and a.date between '2005-01-01' and '2005-12-31' group by a.body_hash order by rand(); The table newspapers has only 20 records. The table sections has under 300 records, but the tables articles_counter and articles_comments might have many records... hundread of thousands millions. Thanks. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations
Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default What default charset do your slave's tables have? The coercibility value of system constants has changed in 4.1.11. Though it seems not related to your problem I recommend you to upgrade to 4.1.12. The default charset of the slave table is latin1, the same on the master. Slave Table create statement: = CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; which is identical with the Master create statement: CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Marco Marco Poehler http://www.kontaktlinsen-preisvergleich.de Am Montag, den 18.07.2005, 21:34 +0300 schrieb Gleb Paharenko: Hello. Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default What default charset do your slave's tables have? The coercibility value of system constants has changed in 4.1.11. Though it seems not related to your problem I recommend you to upgrade to 4.1.12. Marco P$hler [EMAIL PROTECTED] wrote: Hello List, I've tried to set up a slave from an existing master using innobackup. The copy of the database was successful, but when I started the replication, the following error occured: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum + 1 WHERE day = '2005-7-16' AND type = 'redirect' AND client = '1234' AND channel = '43' AND campaign = '...' AND grouping = 'Singleb$se' AND `key` = 'Single Berlin' AND afftraf = 'NULL' I read the docs and compared charset/collation configuration as you can see below, but it seems to be the same on both servers. any hints welcome ! thanks in advance Marco = Master: MySQL 4.1.10 mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 7349 ?Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2005.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information = Slave: MySQL 4.1.10 --- mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 23970 pts/0Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2006.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Views in 5.0.1
Thanks for the detailed information, this is much clearer. I look forward to 5.0.x becoming release. Kind regards, Ben Clewett. Joerg Bruehe wrote: Hi Ben, all! Ben Clewett wrote ((re-ordered into posting sequence)): Joerg Bruehe wrote: Hi Ben! Ben Clewett wrote: [[...]] Approximately when will 5.0.1 be available as stable release? 5.0.1 will never change, it is out (and obsolete by now). [[...]] I am trying to work out how stable 5.0.x is. Related to why MySQL advise people to wait for the 'release' status. 5.0.9-beta (the current published version) still has some bugs which a production release should not have, and we also want to give the 5.0 release series still more test coverage. You say 5.0.1 is old and obsolete. Yet is not at release stage yet. This is curious. 5.0.1 was the first alpha version of the 5.0 release series. It became obsolete when 5.0.2-alpha was published, 2004-Dec-02. Will 5.0.1 be changed before release? For example: Will large errors (eg, server crash) be retrospectively fixed in 5.0.1 if found in this release? Errors have been fixed (and will still be fixed), but with new version numbers. Any version number is associated with a certain source code, published as a tar.gz file. Whenever anything is changed, be it security fix or feature, the published result is a new code version which gets a new version number. The way I thought of it was: New features would demand a new release. Major new features will enter into a new release series. Depending on the feature complexity, they must be completed when that series is in the alpha or beta stage. Critical bug fixes would be made in *all* live versions. Otherwise why have multiple versions at different stages? Correct if by version you mean the release series, like 4.0, 4.1, or 5.0. But within that series, the new version gets a higher number like 4.0.25 or 4.1.13. If not yet recommended for production purposes, it gets the label also, like 5.0.9-beta. For more details, check http://dev.mysql.com/doc/mysql/en/choosing-version.html But if no changes are going to occur, why is it not 'release' now? Sorry for my confusion, I hope I got it solved. Joerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
Privet! What is faster: UNION or temporary table or something else ? Sometimes MERGE tables could be used, but the speed difference between UNION solution and MERGE is rather low. The temporary table for sorting (ORDER clause causes this) should be created in all cases. So, if you have to reuse data returned from the query several times, create a temporary table from results. BTW - what query (queries) are you going to use for sorting data from several tables? I see simple subquery like: create temporary table t1 select a from ( select a from d1 union select a from d2 ) as foo order by a; If you have faster solution (in several times) please introduce it. I've seen similar issues at archives at: http://lists.mysql.com/mysql odnako, I haven't found an exact answer. :) íÉÈÁÉÌ íÏÎÁÛ£× [EMAIL PROTECTED] wrote: Hello, I have 10 tables: table0: id, description, date table2: id, description, date ... table9: id, description, date Every table has 1 000 000 rows. How can I select all rows from this 10 tables ordered by date? What is faster: UNION or temporary table or something else ? mysql 4.1 Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- 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: datetime/timestamps/4.1.12
Hello. According to the output of mysqld --help --verbose these variables (date_format and datetime_format) don't work yet, they're exists for future purposes. [EMAIL PROTECTED] wrote: Mike, Have you tried creating a new table with a field for some sort of date? Try adding some data and see if the new date time format you specified in the my.cnf file. See if that works. -sam Hello, Nevermind - duh -datetime is not timestamp (oneday I fullfill my promise to myself and not work on Sundays when my 'duh' level is a bit higher). Sofrom what I gather, the backward compatibility comes in the form of adding a +0 (string to int). This is most inconvenient and annoying. Any plans on rectifying this, or has anyone found a workaround, or are we left to go SIOH (hint OH stands for our hat) :-} Later... Michael -Original Message- From: DePhillips, Michael P Sent: Sun 7/17/2005 11:05 AM To: mysql@lists.mysql.com Cc: Subject: datetime/timestamps/4.1.12 Hello, I just upgraded to 4.1.12 from 4.0.22 and my timestamps changed formats to %Y-%m-%d %H:%i:%s So I added the following lime to my my.cnf file datetime_format=%Y%m%d%H%i%s Which is the format I prefer, I restart the server and my time stamps still appear as %Y-%m-%d %H:%i:%s The 'show variables' command now confirms that the format is what I defined in the my.cnf but the output of the query does not change... What am I missing? Thanks, Michael -- 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]
error in mysql
Hi, I have installed linux 9 with mysql-standard-4.0.15-pc-linux-i686.. mysql responding very slow when i request to find out any database its took long time to respons... plz help me Manish Popli - Fiorano MailServer All incoming and outgoing mails are scanned for Virus and Spam http://www.fiorano.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared statements in MySQL
hi, i've got some questions: what happens when we deallocate a prepare statement? can we use same name for 2 prepared statements in a SP by deallocating first one before defining second one? can we use a prepare statement in a loop? can we use more than 1 prepared statement in a single SP? Thanks in advance Chagh - Start your day with Yahoo! - make it your home page
RE: Prepared statements in MySQL
Hi, Though MySQl initially provided support for Prepared statements in MySQL 5.0, they have removed that support in the later beta versions of MySQL 5.0. Right now MySQL does not recommend using prepared statements inside SP. So it would be better if you go for some work around's like using 'if else' loops. sujay -Original Message- From: Shaghayegh Sahebie [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 19, 2005 1:55 PM To: mysql@lists.mysql.com Subject: Prepared statements in MySQL hi, i've got some questions: what happens when we deallocate a prepare statement? can we use same name for 2 prepared statements in a SP by deallocating first one before defining second one? can we use a prepare statement in a loop? can we use more than 1 prepared statement in a single SP? Thanks in advance Chagh - Start your day with Yahoo! - make it your home page -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restore.sql database
hello i am taking backup in *.sql format automatically how can i restore the same in the server automatically from visual basic regards p rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cant Get Access to My Databases
Hello I recommend you install mysql on your _home_ computer. After install last version MySQL-Front (www.mysql-front.com) and trying to connect from it. TH I have finally gotten my MySQL server up and running. However, I am TH having a bit of difficulty getting connected. TH I am using Dreamweaver to construct PHP pages for dynamic data, and TH phpMyAdmin to manage the databases, however when I try to connect by TH either method, I get a message that states Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
Hello BM Are you looking to compare a few last records in each table or ?? BM Can I get a little bit more details? I have 10 similar tables. 3 years ago it was one big table. Now I split it to 10 tables. 3 years ago I could write: SELECT * from BigTable WHERE date_col$some_date ORDER BY date_col Now I have to write: SELECT * from Table0 WHERE date_col$some_date UNION SELECT * from Table1 WHERE date_col$some_date ... UNION SELECT * from Table9 WHERE date_col$some_date after push this rows into temporary table and after write: SELECT * from TempTable ORDER BY date_col How to make it simply and faster? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
I had a similar setup, involving log parsing. It was impractical to put all of the data in one table and expect to get timely results. In order to do it, I scripted the generation of the temp table (think I used a merge, since it is not actually moving data and is fast). So... I scripted the date range, assembled the merge statement, then pulled the query on the merged table. I would imagine that there is a way to do it all in sql, but I found it much easier to script it since the end result was html. --Original Message-- From: Michael Monashev To: Berman, Mikhail ReplyTo: Michael Monashev Sent: Jul 19, 2005 1:58 PM Subject: Re: Multitable selection Hello BM Are you looking to compare a few last records in each table or ?? BM Can I get a little bit more details? I have 10 similar tables. 3 years ago it was one big table. Now I split it to 10 tables. 3 years ago I could write: SELECT * from BigTable WHERE date_col$some_date ORDER BY date_col Now I have to write: SELECT * from Table0 WHERE date_col$some_date UNION SELECT * from Table1 WHERE date_col$some_date ... UNION SELECT * from Table9 WHERE date_col$some_date after push this rows into temporary table and after write: SELECT * from TempTable ORDER BY date_col How to make it simply and faster? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.9 build problem
Hi! Peregrine wrote: On Monday 18 July 2005 03:06am, Joerg Bruehe wrote: [[...]] You are correct. The compile was successfully completed, as in there were no compile errors. However, I am building RPMs; that was the build process that I was referring to. Sorry to have been confusing. No confusion - I just wanted to be explicit. Which platform are you using, or which specific features are you combining, so that you build by yourself? Fedora, RHEL, CentOS SUSE distributions for i386 AMD64. Now that these distributions have sufficiently current packages of 4.1 available, I no longer build those; just 5.0 (as close to the way those distributions would probably build them) for development and testing. Ok, that makes sense. [[...]] mysql [ pass ] mysql_client_test [ fail ] Errors are (from /home/lamontp/rpmbuild/BUILD/mysql-5.0.9-beta/mysql-test/var/log/mysqltes t-time) : mysql_client_test.c:3811: check failed: 'rc == 0' [[...]] Yes, both look good probably. You just ran into one reason why version 5.0 is still in beta state. It is a known bug, MySQL development is working on this. Is there any better reference to the bug available (bug ID) so that I could follow it? I could also try contribute a fix, in that case. No, sorry there is not. Depending on machine and test mode (especially embedded), mysql_client_test shows different test failures. When I first replied, I thought I had seen exactly this error, but now my search showed it only for embedded tests. If your experiments show the reason of the failure or even lead to a patch, that would be great and very welcome! I propose to run make test-force, so that this failing test does not prevent the subsequent tests from being taken. I will do that for testing purposes. Currently, I build 5.0.x for development and testing, not for production, so I will need to build a set of RPMs without test-force to continue distributing for those purposes. Ok, it is up to you in which way you run the tests; I just want to be sure you know about this way to run them all. Not needed. Typically, tests are skipped if they are declared to test a component which is not included in the binary being tested. [[...]] Which is what I expected. Thanks for the confirmation. I only offered or the sake of completeness. Yes, that is how I understood it. Regards, Jörg -- 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]
Re: Multitable selection
Hello Now I use temporary table, but I can`t use SQL_CACHE in queries, which contain temporary table :-( In theory 90% queries can be cached. Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multitable selection
Hello GP If you have faster solution (in several times) please introduce it. I've GP seen similar issues at archives at: GP http://lists.mysql.com/mysql I find it in docs :-) : (SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10) ORDER BY a; Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multitable selection
You have not tried merge? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 19, 2005 5:46 AM To: Peter J Milanese; mysql@lists.mysql.com Subject: Re: Multitable selection Hello Now I use temporary table, but I can`t use SQL_CACHE in queries, which contain temporary table :-( In theory 90% queries can be cached. Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.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: error in mysql
I do hope that you understand that this is not a productive post (question?) Very slow is a bit vague. What is it that you are trying to do? -Original Message- From: Manish [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 19, 2005 3:20 AM To: mysql@lists.mysql.com Subject: error in mysql Hi, I have installed linux 9 with mysql-standard-4.0.15-pc-linux-i686.. mysql responding very slow when i request to find out any database its took long time to respons... plz help me Manish Popli -- --- Fiorano MailServer All incoming and outgoing mails are scanned for Virus and Spam http://www.fiorano.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: repair table (error 27)
Dirk, Some suggestions. 1. can you still dump the table using mysqldump? Then you can import it again (with the mysql commandline client), creating a 'fresh' table. 2. make a backup of the index file (.MYI), then throw it away. MySQL will create a new index for you. HTH, Martijn -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Dirk Vleugels Sent: dinsdag 12 juli 2005 19:37 To: mysql@lists.mysql.com Subject: repair table (error 27) Hi, the DB resided on a network appliance filer (happily for a year). A nfs problem corrupted some tables, all got fixed but this one: mysql repair table inmail extended; +-++--+--- + | Table | Op | Msg_type | Msg_text | +-++--+--- + | eps4.inmail | repair | warning | Can't change size of indexfile, error: 27 | | eps4.inmail | repair | error| 27 for record at pos 9037492 | | eps4.inmail | repair | error| 27 when trying to write bufferts | | eps4.inmail | repair | error| 27 when updateing keyfile | | eps4.inmail | repair | status | Operation failed | +-++--+--- + 5 rows in set (11.03 sec) perror 27 says 'file to large', but the files in question are _small_: -rw-rw 1 mysqlother9037816 Jul 12 14:51 inmail.MYD -rw-rw 1 mysqlother3377152 Jul 12 14:50 inmail.MYI -rw-rw 1 mysqlother 9358 Dec 8 2004 inmail.frm I tried myisamchk with different options, but no go. Any clues? Any way to recover the data (for re-import)? Regards, Dirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more queries vs a bigger one
Octavian Rasnita [EMAIL PROTECTED] wrote on 07/19/2005 02:45:58 AM: From: [EMAIL PROTECTED] Subject: Re: more queries vs a bigger one Hello, approx. how long does it take your big query to run as it is now? Are these queries appending a table? or are they buiding a result (from a chain of queries)? Have you tried separating them out? Any difference? -sam That query takes more than 2 minutes. I have a table with less than 90.000 records, and this is the bigger table from the database, so the query is very slow, because as you may see, the tables are not very big. But that query might return more than 9.000 records, even though I limit it to first 30. I will split it into more smaller queries and I will report if it works faster. The query is something like the example below, but I don't have it here right now to send it to the list, but I will send it soon: select a.pre_title, a.title, a.post_title, substring(a.body, 1, n.preview_size) as preview, n.title as publication, a.id_category, cs.label, count(aco.hash_articles) as comments, count(act.hash_articles) as counter from articles a left join newspapers n on(a.id_newspapers=n.id) left join sections s on(a.id_sections=s.id and s.id_newspapers=n.id) left join sections_categories cs on(a.id_categories=cs.id) inner join articles_counters act on(a.body_hash=act.hash_articles) inner join articles_comments aco on(a.body_hash=aco.hash_articles) where a.id_category=20 and a.date between '2005-01-01' and '2005-12-31' group by a.body_hash order by rand(); The table newspapers has only 20 records. The table sections has under 300 records, but the tables articles_counter and articles_comments might have many records... hundread of thousands millions. Thanks. Teddy This is your original query (above) unwrapped and tabified: select a.pre_title , a.title , a.post_title , substring(a.body, 1, n.preview_size) as preview , n.title as publication , a.id_category , cs.label , count(aco.hash_articles) as comments , count(act.hash_articles) as counter from articles a left join newspapers n on(a.id_newspapers=n.id) left join sections s on(a.id_sections=s.id and s.id_newspapers=n.id) left join sections_categories cs on(a.id_categories=cs.id) inner join articles_counters act on(a.body_hash=act.hash_articles) inner join articles_comments aco on(a.body_hash=aco.hash_articles) where a.id_category=20 and a.date between '2005-01-01' and '2005-12-31' group by a.body_hash order by rand(); I see ONE GLARING problem with this query right away:If you weren't using MySQL, this would be an illegal query. Your GROUP BY clause does not contain enough columns and the column it *does* contain doesn't appear in your SELECT clause. MySQL has a way of making queries like this work but your results are not guaranteed to be deterministic. Another issue that is killing your speed is your ORDER BY RAND(); I believe it would be much faster to break this into 3 smaller queries: first to pick your articles, second to get your statistics, the third will combine the first two with some additional information to provide your finished report. I am assuming that `articles`.`body_hash` has an index on it and is unique. I assume it is unique and indexed because you are using it as a foreign key to the `articles_counters` and `articles_comments` tables. If you do not have an index on `articles`.`body_hash`, add one then try your query again. If it is still too slow, try this query: /* start query */ CREATE TEMPORARY TABLE tmpArtHash (key(body_hash, rndval)) SELECT body_hash, rand() as rndval FROM articles WHERE id_category=20 AND `date` between '2005-01-01' and '2005-12-31'; CREATE TEMPORARY TABLE tmpArtCounters (key(body_hash)) SELECT th.body_hash , count(aco.hash_articles) as comments , count(act.hash_articles) as counter FROM tmpArtHash th LEFT JOIN articles_counters act on th.body_hash=act.hash_articles LEFT JOIN articles_comments aco on th.body_hash=aco.hash_articles GROUP BY th.body_hash SELECT a.pre_title , a.title , a.post_title , substring(a.body, 1, n.preview_size) as preview , n.title as publication , a.id_category , cs.label , tc.comments , tc.counter FROM tmpArtHash th INNER JOIN articles a on a.body_hash = th.body_hash left join newspapers n on a.id_newspapers=n.id left join sections s on a.id_sections=s.id and s.id_newspapers=n.id left join sections_categories cs on a.id_categories=cs.id LEFT JOIN tmpArtCounters tc ON tc.body_hash = th.body_hash order by th.rndval; DROP TEMPORARY TABLE tmpArtHash, tmpArtCounter; /* end query */ Let us know how fast it works. It should take less than 3 seconds or so
Re: error in mysql
Hello. You've provided not enough information to give you a good suggestion. Are you connecting through UNIX socket or TCP? Similar behavior is often caused by DNS problems. 4.0.15 is a very old MySQL. Use 4.1.12 (or 4.0.25). What does it mean 'find out any database'? Did you mean that MySQL worked slowly during the 'USE database_name' statement? Manish [EMAIL PROTECTED] wrote: Hi, I have installed linux 9 with mysql-standard-4.0.15-pc-linux-i686.. mysql responding very slow when i request to find out any database its took long time to respons... plz help me Manish Popli - Fiorano MailServer All incoming and outgoing mails are scanned for Virus and Spam http://www.fiorano.com -- 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: restore.sql database
Hello. In what way do you create your backups? Why do you want to use visual basic instead of mysql command line client for example? prathima rao [EMAIL PROTECTED] wrote: hello i am taking backup in *.sql format automatically how can i restore the same in the server automatically from visual basic regards p rao -- 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: authentication - which hostname is used?
Hello. Searching through the code shown me that all stuff is located in sql/hostname.cc file. MySQL depends on gethostbyaddr_r and uses the official name of the host which it returns. According to rfc897: Hosts have an official (or primary) name and possibly several nicknames. When mail is sent from a host, the official name is used in the mail header address fields. The official name is only one, and MySQL uses it. For more details see the source. Per Jessen [EMAIL PROTECTED] wrote: Hi, when an IP-address is reverse mapped to multiple names, which one is used by mysql for user authentication? Right now (4.1.11) it looks like the first record is used, which I'm not sure good enough. Shouldn't mysql check all the returned names and see if one of them authenticates? /Per Jessen, Z$rich -- 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: Illegal mix of collations
Hello. `key` varchar(255) character set utf8 collate utf8_bin NOT NULL ^^^ Field's character set should be latin1 as well. Change it. The default charset of the slave table is latin1, the same on the master. Slave Table create statement: = CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; which is identical with the Master create statement: CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Marco Marco Poehler http://www.kontaktlinsen-preisvergleich.de Am Montag, den 18.07.2005, 21:34 +0300 schrieb Gleb Paharenko: -- 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]
ANN: Database Workbench 2.7.5 released!
Ladies, gentlemen, Upscene Productions is happy to announce the next version of the popular database development tool: Database Workbench 2.7.5 has been released today! Based on user feedback, there are several new features, enhancements and bugfixes. Download a trial at: http://www.upscene.com Features and fixes: http://www.upscene.com/news/20050719.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, MSDE 1 2 ) - MySQL 4, 4.1 - Oracle Database ( 8i, 9i, 10g ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New - DataPump: ability to perform all transfers in a single transaction and avoid a COMMIT if errors occured - Right click code editor and select Search Schema For... will open the Search Schema window with the selected text or word under cursor - Ability to print SQL Editor grid and Data grid (Table/View Editor) - Oracle: syntax check for package and package body in Package Editor - Oracle, MSSQL: added syntax check to Trigger Editor - Oracle, MSSQL, MySQL: rename implemented for objects that support it (DB Navigator) - IB/Fb: ability to view connected databases and users for SuperServer Enhancements - Added standard popupmenu to Trigger Editors - DBNavigator: warning if there are pending changes when doing a refresh from db - Several enhancements to the SQL/Script Editor - MySQL: ability to extract DDL for users - MSSQL: ability to extract DDL for logins - MSSQL: ability to extract DDL for multiple Linked Servers - MSSQL: not null attribute editable for existing columns - MSSQL: identity attribute editable for existing tables and new columns - Oracle: fetching of function/procedure parameters faster - Oracle: added Extract DDL buttons to User, Role, Tablespace and Rollback Segment Managers - Oracle: improved datatype mapping in Schema Migrator - Oracle: has a keep alive function on connections so that Oracle won't disconnect you after (default 15 minutes) a period of idle-time Fixes - BLOB Editor: fixed error with not being able to delete data from BLOBs - CSV import failed parsing particular CSVs when first row is header was checked - SQL Catalog: run from the popup menu didn't work - DataPump AV fixed when closing DataPump after closing destination connection - Schema Migrator: - migrating auto-inc columns to a target that doesn't support them could fail - migrating constraints with owner-name-supporting source could fail - possible DDL extract errors fixed for certain combinations of migrating objects - fixed possible AV when migrating a constraint type, included with a table, that's not supported in the target datasource - IB/Fb: fixed specific parse issue in the Procedure Debugger - IB/Fb: fixed DECIMAL/NUMERIC without precision and/or scale in Procedure Debugger - IB/Fb: fixed assigning floats to INTEGERs in Procedure Debugger/numerics with scale 0 - IB/Fb: fixed AV error after using Grant Manager and closing the connection - IB/Fb: fixed an error with getting the check-constraint condition if the condition had a CHECK( part in it eg: EMAILCHECK(value) - IB/Fb: debugger would not emulate string truncations for [FOR] SELECT ... INTO statements - IB/Fb/MSSQL/Oracle: when recompiling a stored procedure/function, it will reload the source from the database first to make sure it has the latest instead of a local cached version - IB/Fb/MSSQL/Oracle: duplicating a trigger and editting it before saving would default to standard timing etc... - IB: fixed possible thread deadlock in Connections monitor - MSSQL - fixed error with case sensitive database collations and fetching metadata - adding or viewing linked servers was broken - reserved words in view column names weren't delimited - fixed some view-sources not being parsed properly when loading from database resulting in mangled source in DBW - fixed some trigger-sources not being parsed properly when loading from database resulting in mangled source in DBW - fixed AV error when executing a procedure multiple times - fixed errr with executing a procedure that has no input parameters - MySQL, MSSQL: sometimes, indices didn't properly refresh in the DB Navigator when saving them from the Index Editor - MySQL, MSSQL: fixed a bug when dropping indices from the Index Editor when some other table had the same index name - Oracle: - fixed Alter Tablespace when adding new files - exporting clobs didn't get recognized as string data - couldn't connect to Oracle 8.1.6 due to missing CASE SQL functionality - fetching parameters of encrypted functions/procedures failed - DBW handles encrypted procedures/functions more cracefully - creating a package body via the editor could fail - reported % free
Re: optimize a order by statement
王 旭 [EMAIL PROTECTED] wrote on 07/18/2005 03:17:51 AM: Follow is the sql statement: explain SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and o_id0 ORDER BY SUM(ol_qty)DESC Follow is the explain output: 1, 'SIMPLE', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 129615, 'Using where; Using index; Using temporary' 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, '' The query plan use Using temporary.Can i optimize this sql statement? Yes I think there is a way to improve the performance of your query. Without a GROUP BY clause, the function SUM() results in the same value that was originally in the field. You may be able to make this query much faster if you eliminate the useless function call and rewrite your query as: SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and o_id0 ORDER BY ol_qty DESC; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: optimize a sql statement
王 旭 [EMAIL PROTECTED] wrote on 07/18/2005 03:12:28 AM: NO effect :-( From: pow [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Mon, 18 Jul 2005 11:51:23 +0800 Do u have composite index on order_line.ol_o_id AND order_line.ol_i_id? You could try that... 王 旭 wrote: Now,I make this sql statement to easy. Follow is the sql statement: - SELECT ol_i_id FROM orders,order_line WHERE order_line.ol_o_id = orders.o_id GROUP BY ol_i_id - Follow is the explain output: - 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231, 'Using index; Using temporary; Using filesort' 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'PRIMARY', '4', 'tpcw.orders.o_id', 1, '' - Can it be optimized? From: 王 旭 [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: optimize a sql statement Date: Sat, 16 Jul 2005 18:24:15 +0800 Follow is my sql statement: - SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 -- follows are explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' -- This sql statement performance is too bad.Please help me to optimize it . thanks! Your original query, reformatted only: SELECT SUM(ol_qty) sumolqty , ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN ( SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000 ) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 If I try to describe your query, this is how it reads to me: You are want to get for each order, how many individual items comprises that order. You only want to see the top 50 item counts from within the 1 most recent orders ignoring those line items where the ol_i_id = 5000 but including those where the o_c_id is equal to that of an order that has a line item where ol_i_id = 5000. For starters, you could pre-compute the lowest o_id and use that value from a variable instead of getting it during execution (yes it will be a scalar and only computed once any way just bear with me...) SELECT @min_o_id := max(o_id)-1 from orders; SELECT SUM(ol_qty) sumolqty , ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id @min_o_id AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN ( SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id @min_o_id AND order_line.ol_i_id = 5000 ) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 Just doing that eliminates two subqueries, simplifying your total query execution plan. If I rewrite the query to use explicit JOINs and eliminated
Fw: table export problem
- Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 19, 2005 10:08 PM Subject: table export problem Hi all, I am trying to create a table on the remote server from a table I created on my local sever but it never seems to work CREATE TABLE `sheet1` ( `id` int(10) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `fname` varchar(255) NOT NULL default '', `sname` varchar(255) default NULL, `job_title` varchar(255) default NULL, `organisation` varchar(255) default NULL, `email` varchar(255) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `postcode` varchar(255) default NULL, `office_tel` varchar(255) default NULL, `mobile` varchar(255) default NULL, `fax` varchar(255) default NULL, `web` varchar(255) default NULL, `add_info` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ; There seems to be a problem with the last line (this is exported from my local server). I am just learning about mySql as I go so have no real clue about CHARSET and ENGINE (which I believe may be the problem) This is the 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=303' at line 18 and this is what the manual says (not very helpful) a.. Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d Any help will be appreciated. R.
table export problem
Hi all, I am trying to create a table on the remote server from a table I created on my local sever but it never seems to work CREATE TABLE `sheet1` ( `id` int(10) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `fname` varchar(255) NOT NULL default '', `sname` varchar(255) default NULL, `job_title` varchar(255) default NULL, `organisation` varchar(255) default NULL, `email` varchar(255) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `postcode` varchar(255) default NULL, `office_tel` varchar(255) default NULL, `mobile` varchar(255) default NULL, `fax` varchar(255) default NULL, `web` varchar(255) default NULL, `add_info` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ; There seems to be a problem with the last line (this is exported from my local server). I am just learning about mySql as I go so have no real clue about CHARSET and ENGINE (which I believe may be the problem) This is the 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=303' at line 18 and this is what the manual says (not very helpful) a.. Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d Any help will be appreciated. R.
Re: Fw: table export problem
I have been able to create your table in my mysql 4.1.12 I think your remote server is in mysql 4.0 series [EMAIL PROTECTED] wrote: - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 19, 2005 10:08 PM Subject: table export problem Hi all, I am trying to create a table on the remote server from a table I created on my local sever but it never seems to work CREATE TABLE `sheet1` ( `id` int(10) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `fname` varchar(255) NOT NULL default '', `sname` varchar(255) default NULL, `job_title` varchar(255) default NULL, `organisation` varchar(255) default NULL, `email` varchar(255) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `postcode` varchar(255) default NULL, `office_tel` varchar(255) default NULL, `mobile` varchar(255) default NULL, `fax` varchar(255) default NULL, `web` varchar(255) default NULL, `add_info` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ; There seems to be a problem with the last line (this is exported from my local server). I am just learning about mySql as I go so have no real clue about CHARSET and ENGINE (which I believe may be the problem) This is the 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=303' at line 18 and this is what the manual says (not very helpful) a.. Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d Any help will be appreciated. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLDump - Command line password
Hi, I have setup cronjobs to take daily backups of my db using mysqldump. But the problem is, mysqldump requires the password to be passed via command line, which means anyone on the same machine can take a peek at my password using top, ps -ef, etc. Is there a way of avoiding this, i.e. making it read the password from some file, etc? Or, is there any other alternative I can use? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLDump - Command line password
have you looked at using a my.cnf file? eMac:~ hcir$ mysqldump test /temp/test.sql eMac:~ hcir$ ls -l /temp/test.sql -rw-r--r-- 1 hcir staff 78893008 Jul 19 16:47 /temp/test.sql contents of ~/.my.cnf [client] user= username password= password # actual username and password of course are not 'username' and 'password' On Jul 19, 2005, at 3:40 PM, Cabbar Duzayak wrote: Hi, I have setup cronjobs to take daily backups of my db using mysqldump. But the problem is, mysqldump requires the password to be passed via command line, which means anyone on the same machine can take a peek at my password using top, ps -ef, etc. Is there a way of avoiding this, i.e. making it read the password from some file, etc? Or, is there any other alternative I can use?
Won't launch after uninstall and install on OS X
Hi all, After deleting and reinstalling mysql on my Powebook G4 12, it starts and then quits immediate. I had to reinstall because my users seemed to not be working on. These are the steps I am taking after reinstalling and the errors I get: cd /usr/local/mysql sudo echo sudo ./bin/mysqld_safe dts-2:/usr/local/mysql walking$ Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/dts-2.local.pid 050719 20:40:30 mysqld ended And that's it... Any idea what's going on? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Won't launch after uninstall and install on OS X
[EMAIL PROTECTED] wrote: Hi all, After deleting and reinstalling mysql on my Powebook G4 12, it starts and then quits immediate. I had to reinstall because my users seemed to not be working on. These are the steps I am taking after reinstalling and the errors I get: cd /usr/local/mysql sudo echo sudo ./bin/mysqld_safe dts-2:/usr/local/mysql walking$ Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/dts-2.local.pid 050719 20:40:30 mysqld ended And that's it... Any idea what's going on? Thanks. What does the error log (probably /usr/local/mysql/data/dts-2.local.err) say? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Won't launch after uninstall and install on OS X
Michael, here are some snippets from the error log that look relevant, from three times I tried to start: 050719 20:29:41 mysqld started 050719 20:29:46 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 050719 20:29:58 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 050719 20:29:58 mysqld ended 050719 20:33:07 mysqld started 050719 20:33:07 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050719 20:33:08 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050719 20:33:09 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050719 20:33:09 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050719 20:33:12 InnoDB: Started; log sequence number 0 0 050719 20:33:13 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050719 20:33:13 mysqld ended 050719 21:50:42 mysqld started 050719 21:50:42 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 050719 21:50:43 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050719 21:50:43 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050719 21:50:44 InnoDB: Flushing modified pages from the buffer pool... 050719 21:50:44 InnoDB: Started; log sequence number 0 43634 050719 21:50:44 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050719 21:50:44 mysqld ended Thank you. On Jul 20, 2005, at 12:35 AM, Michael Stassen wrote: [EMAIL PROTECTED] wrote: Hi all, After deleting and reinstalling mysql on my Powebook G4 12, it starts and then quits immediate. I had to reinstall because my users seemed to not be working on. These are the steps I am taking after reinstalling and the errors I get: cd /usr/local/mysql sudo echo sudo ./bin/mysqld_safe dts-2:/usr/local/mysql walking$ Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/dts-2.local.pid 050719 20:40:30 mysqld ended And that's it... Any idea what's going on? Thanks. What does the error log (probably /usr/local/mysql/data/ dts-2.local.err) say? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Chris Fonnesbeck wrote: On 7/18/05, Michael Stassen [EMAIL PROTECTED] wrote: Hmmm... In that case, I have more questions. Log in as root and run SHOW VARIABLES LIKE '%pass%'; DESC user password; and post the results. Also, was this a brand new installation, or an upgrade? If an upgrade, from what version? This is a brand new installation, following a clean install of my OS. Sorry, my question was imprecise. I meant to ask if you were using an earlier version before this install. I expect the answer is yes, and you are using the new 4.1 server with the old data. Here are the commands you requested: mysql SHOW VARIABLES LIKE '%pass%'; +---+---+ | Variable_name | Value | +---+---+ | old_passwords | OFF | +---+---+ 1 row in set (0.03 sec) mysql DESC user password; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | Password | varchar(16) | | | | | +--+-+--+-+-+---+ 1 row in set (0.00 sec) Well, there's the problem. The Password column needs to be a varchar(41) to hold the new 41 character password hash used by 4.1. Your Password column is only a varchar(16), which is the old size. I'm guessing you copied in your old data, but haven't run the mysql_fix_privilege_tables script. That should update your Password column to fix this (among other things). Still, the behavior you describe is not what the manual says will happen http://dev.mysql.com/doc/mysql/en/password-hashing.html. It says, If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to widen the Password column. You may have stumbled onto a bug, in which case you should report it http://bugs.mysql.com/. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
