Fwd: Undelivered Mail Returned to Sender
- Weitergeleitete Nachricht von Mail Delivery System [EMAIL PROTECTED] - Datum: Tue, 18 Jan 2005 08:09:14 +0100 (CET) Von: Mail Delivery System [EMAIL PROTECTED] Antwort an: Mail Delivery System [EMAIL PROTECTED] Betreff: Undelivered Mail Returned to Sender An: [EMAIL PROTECTED] This is the mailinout01.osnanet.de program at host mailinout01.osnanet.de. I'm sorry to have to inform you that the message returned below could not be delivered to one or more destinations. For further assistance, please send mail to postmaster If you do so, please include this problem report. You can delete your own text from the message returned below. The mailinout01.osnanet.de program [EMAIL PROTECTED]: Name service error for name=list.mysql.com type=A: Host not found - Ende der weitergeleiteten Nachricht - - gesendet durch osnatel-Webmailer http://www.osnatel.de Reporting-MTA: dns; mailinout01.osnanet.de Arrival-Date: Tue, 18 Jan 2005 08:09:14 +0100 (CET) Final-Recipient: rfc822; mysql@list.mysql.com Action: failed Status: 5.0.0 Diagnostic-Code: X-mailinout01-osnanet-de; Name service error for name=list.mysql.com type=A: Host not found ---BeginMessage--- Hallo verehrtes Mysql-Team, ich habe folgendes Problem... Seit Tagen versuche ich eine Quelldistribution von Mysql-4.0.23a mit der Pfadangabe --prefix=/usr/local/mysql zu installieren. Ich bin gerade dabei unter SuSE 9.0, Raid1, BS update nicht durchgeführt, ein Postfix, Cyrus-sasl, imap, aufzusetzen. Postfix erwartet aber mit Mysql kompilliert zu werden. Ich hatte schon die SuSE .rpm installation mehrmals enfernt. Ich erhalten nach dem make install einen recuriven Fehler! Hier der Fehler: ___ /usr/bin/install: `bench-count-distinct' and `/usr/local/mysql/sql-bench/bench-c make[2]: *** [install-benchSCRIPTS] Error 1 make[2]: Leaving directory `/usr/local/mysql-4.0.23a/sql-bench' make[1]: *** [install-am] Error 2 make[1]: Leaving directory `/usr/local/mysql-4.0.23a/sql-bench' make: *** [install-recursive] Error 1 mail:/usr/local/mysql-4.0.23a # ___ -Mysqlbug-Report- ___ SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.23a (Source distribution) C compiler:gcc (GCC) 3.3.1 (SuSE Linux) C++ compiler: g++ (GCC) 3.3.1 (SuSE Linux) Environment: machine, os, target, libraries (multiple lines) System: Linux mail 2.4.21-99-athlon #1 Wed Sep 24 13:34:32 UTC 2003 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/local/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir\ =/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada --dis\ able-checking --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zli\ b --enable-shared --enable-__cxa_atexit i586-suse-linux Thread model: posix gcc version 3.3.1 (SuSE Linux) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: -00-:---F1 mysqlbug (Fundamental)--L1--Top machine, os, target, libraries (multiple lines) System: Linux mail 2.4.21-99-athlon #1 Wed Sep 24 13:34:32 UTC 2003 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/local/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir\ =/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada --dis\ able-checking --enable-libgcj
RE: backup of database
-Original Message- From: N. Kavithashree [mailto:[EMAIL PROTECTED] Sent: 18 January 2005 05:35 To: mysql@lists.mysql.com Subject: backup of database hello, how to take a backup of database? i have a database with name1 now i want to move all the tables into another database? how to do? Could be mysqldump that you need: http://dev.mysql.com/doc/mysql/en/mysqldump.html examples: mysqldump [--no-data] name1 | mysql name2 mysqldump [--no-data] name1 name1.sql HTH Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
get field name
how do i get field name from this table Time Stat1 Stat2 Stat3 0905 1 1 1 0906 1 0 1 i want to get the field name Stat1 Stat 3 from the 0906 row which have 1 as value I expect the result to be STAT1 STAT3 Select xxx from table_time where xxx=1 and Time=0906; can someone help me? -- Chenri J Taman Palem Lestari B18 - 19A (021) 926 68651 - Esia jadi bisa SMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: restoring database
snip i have written a bat file in which the data databackup has been taken in sql it runs automatically every 1 hour and sends a automatically through email now at the other end i have to manually restore the database can any one give me the codes to restore the database automatically in vb or as a bat file the databack up say for a database test is test.sql I am assuming you used mysqldump to create your test.sql mysql [-u user] [-ppassword] new_db_name test.sql If new_db_name already contains data within test.sql you may need to use something like --add-drop-table when you create test.sql. mysqldump --add-drop-table my_db test.sql HTH Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: glibc thread_stack
On Mon, Nov 29, 2004 at 11:50:20AM -0700, Sasha Pachev wrote: Hi, Hello, For a few servers with 1000 concurrent connections, I've compiled glibc 2.3.3 with a patched STACK_SIZE in linuxthreads/descr.h : #ifndef STACK_SIZE #define STACK_SIZE (128 * 1024) #endif instead of #define STACK_SIZE (2 * 1024 * 1024) This by the hints on the Documentation @ mysql.com But, now I notice: 041125 15:15:34 mysqld started 041125 15:15:34 Warning: Asked for 196608 thread stack, but got 126976 041125 15:15:35 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Is the 128K STACK_SIZE I've built glibc with too little, and should I update it to 256K ? Originally, MySQL team believed 128K was sufficient. Then some time later it was discovered that some DNS resolving routines required a 192K stack in some cases. So for safety reasons, mysqld was modified to request at least 192K stack. In your case, your modification to glibc makes it impossible for it to have a 192K stack, which is why you are getting the message. However, if you run mysqld with --skip-name-resolve (you will need to update your priv tables to use numeric addresses), glibc DNS routines are never called, so 128K should be sufficient. I see :) With MySQL 4.1.8 and higher, I need to use a stack_size of at least 256K, else MySQL segfaults (signal 11) when started. This is with gcc-3.3.5 and glibc-2.3.3. Maybe something you guys should look into this some more, since it really boosts performance :) Also, I have one other question regarding this: When building with --with-other-libc, the build automatically changes to a static build. But, when using OpenSSL, it _must be_ a dynamic build, according to the configure-script. Does anyone have a clue on howto built with a custom glibc, but also have OpenSSL-support ? I really need it :) Thanks ! Kind regards, -- WideXS http://www.widexs.nl Wouter de Jong System-Administrator Tel +31 (0)23 5698070 Fax +31 (0)23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final yes, do it confirmation transfer them to the true tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the next ID in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark user rollback or similar - if that is permissible in the application. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with Virus checkers
My product has at is centre a Windows PC whose sole purpose is to run MySQL plus my middleware layer. However, it installed on a site with a large amount of heterogeneous IT department and an active IT department managing the whole corporate IT structure. This IT department insists that, if it is a Windows PC with any connection to the corporate network, it *must* run a virus checker. However, it appears that the virus checker (McAffee, as it happens, but I think the problem may be general) feels a need to check the MySQL data files every time they change. As the system load is bean ramped up, more and more time is being spent in the virus checker. The quick solution is to tell the virus checker to ignore the whole mysql\data directory. This solves the problem, but leaves the IT department nervous because something is not being checked. I cannot see how a virus could infect via the data directory, but I am no virus expert. It is also my view that a machine with no actual humans using it (no email, no web), with all unnecessary services disabled and which is behind a good firewall should be pretty well protected and should not need a virus checker. Am I right in this? Have other people had this sort of problem, and how did they cope with it? To my regret, the reply switch to *nix is unacceptable to my management. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW COLUMNS Syntax Using 3.23.54 Please!
Hi, I am trying to get all field names from my table that begin with letter X or Y, however the following statement and many variations I have tried produce an error: SHOW COLUMNS FROM TABLE LIKE X% OR LIKE Y% Any help here would be much appreciated :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Andre, I would recommend a table for recovering id's that are lost due to rollback. Before you actually rollback, take the generated ID and push it into this table. Then change the way you acquire id's on insert. You will want to check to see if this table has an ID before you auto_increment the table you are inserting the record into. This should be a little less resource intensive than to put all data into temporary tables. Clint From: Joerg Bruehe [EMAIL PROTECTED] To: mysql@lists.mysql.com CC: Andre Matos [EMAIL PROTECTED], Paul DuBois [EMAIL PROTECTED] Subject: Re: Rollback and INSERT_ID() or LAST_INSERT_ID() Date: Tue, 18 Jan 2005 11:08:40 +0100 Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final yes, do it confirmation transfer them to the true tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the next ID in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark user rollback or similar - if that is permissible in the application. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- 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: I'd like to know SQL query for table description.
Try this query - show table status from YourDatabaseName; ninjajs wrote: Hi. ALL i have a database in MySQL. A middleware request to MySQL DB server. and then, result is table description. ex) dbms_type,table_name,column_name,data_type,data_size,constraint_type What SQL query can I get this information ? thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW COLUMNS Syntax Using 3.23.54 Please!
shaun thornburgh wrote: I am trying to get all field names from my table that begin with letter X or Y, however the following statement and many variations I have tried produce an error: SHOW COLUMNS FROM TABLE LIKE X% OR LIKE Y% According to the manual, the pattern for SHOW COLUMNS is: SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] There is no WHERE clause or expression evaluation here, to achieve what you want you must issue the statement twice: SHOW COLUMNS FROM tbl_name LIKE X%; SHOW COLUMNS FROM tbl_name LIKE Y%; URL: http://dev.mysql.com/doc/mysql/en/SHOW_COLUMNS.html You didn't ask, but a shorter way to write this is using the DESCRIBE command, which can be abbreviated to DESC: DESC tbl_name X%; DESC tbl_name Y%; URL: http://dev.mysql.com/doc/mysql/en/DESCRIBE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trouble with Virus checkers
Hi Alec, Of course it is an acceptable solution to tell your virus checker to ignore the data files. Another application than the database engine itself should never manipulate the data files of any database engine. And yes, you are right... :-) Running the database server separate from the pc net is a good idea. Put a firewall in between (maybe controlled by your IT-department so they can sleep at night?) and make sure it only allows database traffic. Then you have a solution in the right line of thinking by my book. Regard, Thomas Thomas Lundström, mailto:[EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: den 18 januari 2005 11:45 To: mysql@lists.mysql.com Subject: Trouble with Virus checkers My product has at is centre a Windows PC whose sole purpose is to run MySQL plus my middleware layer. However, it installed on a site with a large amount of heterogeneous IT department and an active IT department managing the whole corporate IT structure. This IT department insists that, if it is a Windows PC with any connection to the corporate network, it *must* run a virus checker. However, it appears that the virus checker (McAffee, as it happens, but I think the problem may be general) feels a need to check the MySQL data files every time they change. As the system load is bean ramped up, more and more time is being spent in the virus checker. The quick solution is to tell the virus checker to ignore the whole mysql\data directory. This solves the problem, but leaves the IT department nervous because something is not being checked. I cannot see how a virus could infect via the data directory, but I am no virus expert. It is also my view that a machine with no actual humans using it (no email, no web), with all unnecessary services disabled and which is behind a good firewall should be pretty well protected and should not need a virus checker. Am I right in this? Have other people had this sort of problem, and how did they cope with it? To my regret, the reply switch to *nix is unacceptable to my management. Alec -- 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: I'd like to know SQL query for table description.
[snip] and then, result is table description. ex) dbms_type,table_name,column_name,data_type,data_size,constraint_type What SQL query can I get this information ? [/snip] DESCRIBE `tablename`; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get field name
Chenri wrote: how do i get field name from this table Time Stat1 Stat2 Stat3 0905 1 1 1 0906 1 0 1 i want to get the field name Stat1 Stat 3 from the 0906 row which have 1 as value I expect the result to be STAT1 STAT3 This is not easily done, if I understand you correctly. In general, a select statement can list the content of the columns and do calculations based on this content, but you are asking about the _name_ of the column. The name of the column is considered meta data, it is not part of the data the rdbms is managing. Select xxx from table_time where xxx=1 and Time=0906; You have two rows in your example table, and you want two rows in the result. This normally means you want one result row for each row in the table, but that is not the case here? It seems you want one row for each column with a specified value (1)? I suppose you could do something like this, if your version of mysql supports UNION (version 4.0.x and later): SELECT 'STAT1' FROM tab WHERE Stat1=1 and Time='0906' UNION SELECT 'STAT2' FROM tab WHERE Stat2=1 and Time='0906' UNION SELECT 'STAT3' FROM tab WHERE Stat3=1 and Time='0906' can someone help me? You should normalize your data. URL: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Maybe if you explain what task you are trying to solve, someone could suggest a solution? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I ... SQL question
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: SELECT DISTINCT place FROM a ; place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Using derived tables, this would be something like SELECT DISTINCT a.place FROM ( SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1) AS d JOIN a ON a.name = d.name AND a.year = d.year; I would like to be compatible with 3.23.xx. The pre-4.1 workaround for derived tables are temporary tables: CREATE TEMPORARY TABLE tmp AS SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; SELECT DISTINCT a.place FROM tmp d JOIN a ON a.name = d.name AND a.year = d.year; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Losing database information
[EMAIL PROTECTED] wrote: ALL, I have an issue where MySQL has lost all of the table information for an existing database, I do not know of anything that has changed and there was no maintenance being done in MySQL. Below you will find the error message as I receive it from MySQL. ANY ideas or suggestions on how to recover this database intact will be GREATLY appreciated. The .FRM files are intact and all permissions have been checked against a database that I CAN still access, which tells me that the problem is the database somehow and not MySQL as a whole. Try check table and repair table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error - #1251
Thanks - I got it working. On Jan 18, 2005, at 12:35 AM, Mattias J wrote: At 2005-01-18 04:53, Craig Hoffman wrote: I upgrade to 4.1.9 last night and I keep getting this error in PhpMyAdmin after I changed the root password (yes I changed both pw's). Does anyone have a solution on how to fix this? I'm running a Mac 10.3.x #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client If you try searching for 1251 authentication on mysql.com, the first hit is this page, which will provide the answer http://dev.mysql.com/doc/mysql/en/Old_client.html -- 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: Ask for input during SQL script execution
[EMAIL PROTECTED] wrote: All, Can someone point me in the right direction ? I am trying to right sql scripts for queries that will prompt for information. Here is the gist of it, I have a sql script that will query a database however I need the script to ask the user for IP address, and other information that will be different each time this script is run, is there a way to have the script take input from the user? Chris Hood If you are writing scripts, you are writing them in some scripting language, and you will have to use the input facilities of that language. Perl, PHP, sh etc all have the facilities you need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I ... SQL question
[EMAIL PROTECTED] wrote on 01/17/2005 06:45:22 PM: Hi there: I have a How do I... SQL question regarding selecting distinct values from a field not included in an aggregated query when LIMIT is in effect, illustrated by the following example: Table a contains the names of individuals, the places they have visited and the year in which they were visited. Let's see who has visited where and when: SELECT * FROM a; name place year -- --- -- kimnorth 2004 kimsouth 2003 kimsouth 2003 bobwest2004 bobwest2004 bobwest2003 joesouth 2004 joesouth 2005 suewest2004 bobeast2003 joeeast2004 joeeast2004 suesouth 2004 bobnorth 2004 bobnorth 2005 Summarize data by number of places visited by year: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC; count name year --- -- -- 3 bob2004 3 joe2004 2 bob2003 2 kim2003 2 sue2004 1 bob2005 1 kim2004 1 joe2005 Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; Put the results of the LIMITed query into a temporary table and re-query. CREATE TEMPORARY TABLE tmpStep1 ( freq int , name varchar(25) , year int ); INSERT tmpStep1 (freq, name, year) SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; select distinct a.place from tmpStep1 ts1 INNER JOIN a on a.name = ts1.name; Then you should get the list: place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Any help appreciated. I would like to be compatible with 3.23.xx. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Basically, if you need to treat a set of results as source data, your best option is to make a table (temporary or permanent) out of your results. Then when you are through, cleanup for the next time. DROP TEMPORARY TABLE tmpStep1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Replication Slave I/O Thread won't start on 4.1.8
One thing I left out is that we are running 4.1.8-Max-log not 4.1.8-standard-log Could that have anything to do with it? Thanks for any insight, Frank On Mon, 17 Jan 2005 15:55:06 -0500, Frank Febbraro [EMAIL PROTECTED] wrote: Hey all, I have setup replication in the past on 4.0.x servers so I figured I knew what I was doing...silly me. I reread all of the docs and best I can tell I followed them sufficiently. When I start my slave, the SQL Thread starts, but the I/O thread never starts, and thus the Master Thread never starts. There are no error messages in any logs, or the show slave status screen. Below are my relevant configurations. I am very sorry if I have left anything significant out. Thank you for any help, Frank MASTER CONFIG ~~ [EMAIL PROTECTED] cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=100 log-bin binlog-do-db=internal binlog-ignore-db=mysql [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~~ mysql show master status\G *** 1. row *** File: server2-bin.04 Position: 874 Binlog_Do_DB: internal Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) ~~ SLAVE CONFIG ~~ [EMAIL PROTECTED] cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=200 replicate-do-db=internal [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~~ [EMAIL PROTECTED] cat /var/lib/mysql/master.info 14 server2-bin.03 79 cms.internal.org slave slavepass 3306 60 0 ~~ mysql show slave status \G *** 1. row *** Slave_IO_State: Master_Host: cms.internal.org Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: server2-bin.03 Read_Master_Log_Pos: 79 Relay_Log_File: server1-relay-bin.01 Relay_Log_Pos: 4 Relay_Master_Log_File: server2-bin.03 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: internal Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.8 and storing east characters
Hi Daniel, I apologise on my ignorance on the matter of Romanian being a Cyrillic, my English is bad enough, any comment I make about languages should be ignored ;-) On the matter of Japanese symbols I'm puzzled; something must be altering the character code somehow on the way to being inserted into the database. Does anyone have any experience in dealing with inserting Japanese symbols in MySQL 4.1 via PHP5? Thanks again, - Martin -Original Message- From: Daniel BODEA [mailto:[EMAIL PROTECTED] Sent: 17 January 2005 23:20 To: Martin Gallagher Subject: Re: MySQL 4.1.8 and storing east characters Hi Martin, I beg to differ on your statement that Romanian is a Cyrillic language. I'm replying only to you because this issue is not related to the discussion list in any way and there's no point in me saying this out loud. It is up to you to correct this statement that will remain in the public archives if you so wish to. Romania is one of the most latin countries in the world and it is in fact a latin island surrounded by all the other eastern european countries which have all either slavic or cyrillic based languages. The romanian language has a full latin alphabet with only 3 letters being responsible for it not being represented by the most standard character set, the ISO Latin 1. Its character set is however the ISO Latin 2 though the language can be written using the first set without any problems of representation or understanding. I hope this aparté can shed a little more light on this matter even though it doesn't help you much with your original predicament ;-) Cheers, Daniel - Original Message - From: Martin Gallagher [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 17, 2005 5:15 PM Subject: RE: MySQL 4.1.8 and storing east characters Japanese AND Cyrillic based languages such as Romania is still messing up. - The Greek and Cyrillic looked ok to me - Unfortunately they are not, some characters are also being given the wrong UTF-8 char code, resulting in the dreaded question marks :-(. - The web browser doesn't have a Japanese font - This is definitely not the problem. All character sets that are available to me on Windows are installed, along with the language specific fonts. Does anybody have experience in using libxml to insert UTF-8 data in 4.1.x? Cheers, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what permissions are necessary in 4.1.x to allow a user to change their own password
Gleb Paharenko wrote:
Hello.
Use SET PASSWORD=password('youpassword'). On 4.1.9 such queries works fine:
mysql show grants for current_user();
++
| Grants for [EMAIL PROTECTED]
|
++
| GRANT USAGE ON *.* TO 'vano'@'%' IDENTIFIED BY PASSWORD '6067079d6665cd0e' |
++
1 row in set (0.00 sec)
mysql set password =password('v');
Query OK, 0 rows affected (0.00 sec)
As said at:
http://dev.mysql.com/doc/mysql/en/SET_PASSWORD.html
syntax as your can use only clients with access to mysql database.
Jason Joines [EMAIL PROTECTED] wrote:
In 4.0.x and 3.23.x a user with these permissions:
GRANT USAGE ON *.* TO 'bogus'@'%' IDENTIFIED BY PASSWORD '7f8933111c70fb1d'
could still change their own password with:
set password for bogus = password('bogus');
After upgrading to 4.1.7 my users are getting this error:
ERROR 1044 (42000): Access denied for user 'bogus'@'%' to database 'mysql'
I did run mysql_fix_privilege_tables after the upgrade but then had to
change to using old_passwords because of application problems. The
upgrade seemed to change the above grants to:
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'bogus'@'%'
IDENTIFIED BY PASSWORD '7f8933111c70fb1d'.
However, users still receive this error when trying to change their
password with either set of grants. Any ideas?
Thanks,
Jason Joines
=
Thanks for the information. That was it. I'd read that document but
guess I didn't pay enought attention to notice the difference.
Jason
===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Slave I/O Thread won't start on 4.1.8
- make sure log-bin is enabled on both master and slave (looks like it is not present in the slave config) - check replication account permissions on the master. I don't remember details, but you can find required permissions in the docs on mysql web site, or doc file in mysql installation directory. -Original Message- From: Frank Febbraro [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 9:41 AM To: mysql@lists.mysql.com Subject: Re: Replication Slave I/O Thread won't start on 4.1.8 One thing I left out is that we are running 4.1.8-Max-log not 4.1.8-standard-log Could that have anything to do with it? Thanks for any insight, Frank On Mon, 17 Jan 2005 15:55:06 -0500, Frank Febbraro [EMAIL PROTECTED] wrote: Hey all, I have setup replication in the past on 4.0.x servers so I figured I knew what I was doing...silly me. I reread all of the docs and best I can tell I followed them sufficiently. When I start my slave, the SQL Thread starts, but the I/O thread never starts, and thus the Master Thread never starts. There are no error messages in any logs, or the show slave status screen. Below are my relevant configurations. I am very sorry if I have left anything significant out. Thank you for any help, Frank MASTER CONFIG ~~ [EMAIL PROTECTED] cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=100 log-bin binlog-do-db=internal binlog-ignore-db=mysql [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~~ mysql show master status\G *** 1. row *** File: server2-bin.04 Position: 874 Binlog_Do_DB: internal Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) ~~ SLAVE CONFIG ~~ [EMAIL PROTECTED] cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=200 replicate-do-db=internal [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~~ [EMAIL PROTECTED] cat /var/lib/mysql/master.info 14 server2-bin.03 79 cms.internal.org slave slavepass 3306 60 0 ~~ mysql show slave status \G *** 1. row *** Slave_IO_State: Master_Host: cms.internal.org Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: server2-bin.03 Read_Master_Log_Pos: 79 Relay_Log_File: server1-relay-bin.01 Relay_Log_Pos: 4 Relay_Master_Log_File: server2-bin.03 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: internal Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create view not working on mysql 4.1.8
I believe CREATE VIEW came in with 5.01, not 4.1.x. PB sirisha gnvg wrote: we are working on mysql 4.1.8 and windows XP platform. This version supports views.We created a view like this mysqluse sample mysqlcreate view v1 as select * from sam2; sam2 is a table We got an error errror 1024(42000):you have an error in your sql syntax; we checked the manual but the syntax is as written above.we also saw error 1024 in error notepad but we could not get any further details. please explain the fault in above statement Yahoo! India Matrimony: Find your life partneronline. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lost data
Hello. I've run a safe recover on a table after I had to recreate the index file: If you had to recreate the index file after upgrading, this could be a character set issue. I mean that data is ok, but you see it after converting to another character set. myisamchk in this case doesn't have any relation to the problem. Give us the information about version of MySQL and operating system. J S [EMAIL PROTECTED] wrote: Hi, I've run a safe recover on a table after I had to recreate the index file: # myisamchk --tmpdir=/proxydb/mysql/tmp --safe-recover internet_usage - recovering (with keycache) MyISAM-table 'internet_usage' Data records: 290804216 Data records: 519541696 # After that I ran a query on the table, but the data seems to be corrupted. I tried flushing the tables but still got bad data despite the checks below which show everything looks fine: mysql check table internet_usage; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | proxy_logs.internet_usage | check | status | OK | +---+---+--+--+ 1 row in set (35 min 34.97 sec) # myisamchk internet_usage Checking MyISAM file: internet_usage Data records: 519541696 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 # myisamchk -dvv internet_usage MyISAM file: internet_usage Record format: Fixed length Character set: latin1 (8) File-version:1 Creation time: 2004-10-27 16:49:48 Recover time:2005-01-16 7:14:48 Status: checked Data records:519541696 Deleted blocks: 0 Datafile parts: 519541696 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length: 16625334272 Keyfile length:5006235648 Max datafile length: 137438953470 Max keyfile length: 4398046510079 Recordlength: 32 table description: Key Start Len Index Type Rec/key Root Bloc 1 2 4 multip. unsigned long 0445844480 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 4 3 6 4 4 104 5 144 6 182 1 2 7 204 1 4 8 244 1 8 9 281 10291 11301 12312 # ls -l internet* -rw-rw 1 mysqlmysql16625334272 Jan 16 07:14 internet_usage.MYD -rw-rw 1 mysqlmysql5006235648 Jan 17 09:05 internet_usage.MYI -rw-rw 1 mysqlmysql 8856 Jul 09 2004 internet_usage.frm mysql show create table internet_usage \g | internet_usage | CREATE TABLE `internet_usage` ( `uid` int(10) unsigned NOT NULL default '0', `time` timestamp(14) NOT NULL, `ip` int(10) unsigned NOT NULL default '0', `urlid` int(10) unsigned NOT NULL default '0', `timetaken` smallint(5) unsigned default '0', `cs_size` int(10) unsigned default '0', `sc_size` int(10) unsigned default '0', `method_ID` tinyint(3) unsigned NOT NULL default '0', `action_ID` tinyint(3) unsigned NOT NULL default '0', `virus_ID` tinyint(3) unsigned NOT NULL default '0', `useragent_ID` smallint(5) unsigned NOT NULL default '0', KEY `uid` (`uid`) ) TYPE=MyISAM MAX_ROWS=4294967295 | 1 row in set (0.00 sec) mysql Could anyone give me some advice on what else I can try to recover the data please? Thanks, JS. -- 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: backup of database
Hello. how to take a backup of database? See: http://dev.mysql.com/doc/mysql/en/Backup.html N. Kavithashree [EMAIL PROTECTED] wrote: hello, how to take a backup of database? i have a database with name1 now i want to move all the tables into another database? how to do? kavi -- 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: 'TYPE = InnoDB'
Hello. You can change the default storage engine by using the --default-storage-engine or --default-table-type server startup option, or by setting the storage_engine or table_type system variable. Paul Wallace [EMAIL PROTECTED] wrote: Hi, I need to obtain FK data from my DB. I am designing the database using DBDesigner. Using the said tool, do I have to export my schema as SQL and manually append the table type (TYPE =3D InnoDB) at the end of = the table definition? For each table?! For those of you who are using DBDesigner with MySQL, is it, and if so how is it possible to use the Database Synchronisation function so that the resulting tables are of type InnoDB? As it is, the tables are created fine, but I can not access them to get the FK data, as they are of type MyISAM. Thanks and regards Paul.=20 -- 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: I'd like to know SQL query for table description.
Hello. See: http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html ninjajs [EMAIL PROTECTED] wrote: Hi. ALL i have a database in MySQL. A middleware request to MySQL DB server. and then, result is table description. ex) dbms_type,table_name,column_name,data_type,data_size,constraint_type What SQL query can I get this information ? thank you. -- 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: Replication Slave I/O Thread won't start on 4.1.8
- make sure log-bin is enabled on both master and slave (looks like it is not present in the slave config) Why is log-bin needed on the slave? I thought the master logs changes and the slave reads those changes and updates it's copy. Why should the slave also log changes it is making? There was no mention of this in the docs. - check replication account permissions on the master. I don't remember details, but you can find required permissions in the docs on mysql web site, or doc file in mysql installation directory. Turns out when my hosting provider installed MySQL 4.1, it was actually an install over a 3.x data directory. The Priv tables were never updated, so I never had the appropriate REPLICATION SLAVE Priv, so the thread never started. It never did say why it did not start though. Threads are now running, but data is not getting replicated. Need to figure that out now. thanks again. Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt about Performance
Hello, Trying to keep the database and the application fast, I´d like to clearify my mind about it. 1) Is a SELECT DISTINCT over 5,000 records a weight query? (Supposing it has about 20 different option the the DISTINCT key). 2) Is SELECT ORDER BY RAND() over 1,500 records a weight query? I need to put these two queries in the first page of our site. So, I´ve been worried if it can slow down our site in the pics. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
processes and threads question
I used to run mysql 3.x on Redhat, and would on any given day have anywhere from 5 to 30 mysqld process/threads going depending on traffic. I upgraded to mysql 4.0.17 on a dedicated redhat box, and now it only ever shows 1 mysqld process/thread that uses max cpu and consumes a good deal of memory. This is all fine, but I'd like to understand what has changed and why I'm not seeing multiple threads like I used to under 3.x? Any insight is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processes and threads question
Did you upgrade to a 2.6.x linux kernel as well? Threads get hidden in a normal ps aux command starting in 2.6 and show as a single process. If this is the case do ps aux -L and you will see the threads as well. On Tue, 2005-01-18 at 13:15 -0500, Eben Goodman wrote: I used to run mysql 3.x on Redhat, and would on any given day have anywhere from 5 to 30 mysqld process/threads going depending on traffic. I upgraded to mysql 4.0.17 on a dedicated redhat box, and now it only ever shows 1 mysqld process/thread that uses max cpu and consumes a good deal of memory. This is all fine, but I'd like to understand what has changed and why I'm not seeing multiple threads like I used to under 3.x? Any insight is appreciated. -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processes and threads question
I appear to be running kernel 2.4.x ps -ax only shows one mysqld thread John McCaskey wrote: Did you upgrade to a 2.6.x linux kernel as well? Threads get hidden in a normal ps aux command starting in 2.6 and show as a single process. If this is the case do ps aux -L and you will see the threads as well. On Tue, 2005-01-18 at 13:15 -0500, Eben Goodman wrote: I used to run mysql 3.x on Redhat, and would on any given day have anywhere from 5 to 30 mysqld process/threads going depending on traffic. I upgraded to mysql 4.0.17 on a dedicated redhat box, and now it only ever shows 1 mysqld process/thread that uses max cpu and consumes a good deal of memory. This is all fine, but I'd like to understand what has changed and why I'm not seeing multiple threads like I used to under 3.x? Any insight is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about Performance
Greg, Thank you very much for your help. An index the full size of the distinct key is probably very quick especially if it only has 20 distinct values. OTOH, if you already know you have a small number of distinct values, could you just store them normalized in a different table? Actually, it is. It has some tables: features = - id - description groups = - id - description products == - id - description - group_id - feature_id And I´ll use a SELECT like this: SELECT DISTINCT features.description FROM products LEFT JOIN features ON (products.feature_id = features.id) WHERE products.group_id = $var_group AND products.features_id 0 The table products should have a million of records, but the filtered query should goes over a thousand records (filtered by group_id) and return about 20 distinct lines. It´s my situation but I don´t know how heavy such query is for the database and how viable such query is. Thanks in advance, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Slave I/O Thread won't start on 4.1.8
- make sure log-bin is enabled on both master and slave (looks like it is not present in the slave config) Why is log-bin needed on the slave? I thought the master logs changes and the slave reads those changes and updates it's copy. Why should the slave also log changes it is making? There was no mention of this in the docs. You are right, log-bin is not required on a slave. I have it in my setup with log-slave-updates on for a chained replication where slave works as master as well. - check replication account permissions on the master. I don't remember details, but you can find required permissions in the docs on mysql web site, or doc file in mysql installation directory. Turns out when my hosting provider installed MySQL 4.1, it was actually an install over a 3.x data directory. The Priv tables were never updated, so I never had the appropriate REPLICATION SLAVE Priv, so the thread never started. It never did say why it did not start though. Check this one out http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html for priv table upgrade. Also you slave account may need more permissions if you want to use LOAD * FROM MASTER commands. Threads are now running, but data is not getting replicated. Need to figure that out now. Below are settings I have for both slave and master. Maybe you can try them in your setup. default-character-set=utf8 # time zone has to be the same on master and slave for correct replication (from 4.1) default-time-zone=GMT log_warnings log_slow_queries log-bin log-slave-updates slave_compressed_protocol=1 # skip illegal collation error slave-skip-errors=1267 thanks again. Frank Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Logging Data: Should I use MyIsam or InnoDB?
I have a curious issue here, maybe someone can help. I have a single process that inserts data into tables that contain purely logging information. This table is then searched by our Care department to troubleshoot issues. I am looking for the best way to store this data, and the structure on the backend. There are 50 million inserts into table LOG a day. The primary index on the table is seconds from 1971. I only need to keep 60 days worth of data, and the table is only used for read purposes. This is my design criteria, but my problem is how to delete old data without crashing the log writer that is atteched to the table. I would prefer to use MyIsam, since it is simple data, and as such it is much faster than an untuned InnoDB table. But what would I do when it is time to delete data? The delete would lock the table, hence freeze the application, and I can not have that. I thought of using a combo of InnoDB and Merge tables, where the LOG table is InnoDB, and the LOG_ARCH tables are Merge. The application would know to read from both, and I can just migrate data from the InnoDB table to the Merge tables, and then delete from the LOG table and not affect the app. since the delete is running against an InnoDB table. I would use truncate table, but there will always be a certain amount of time that will elapse between copying data from the live version to the archive, hence the need for a selective delete on the original. If anyone with experience with large logging apps can chime in here, I would be most appreciative. Regards, Thomas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about Performance
Ronan Lucio wrote: Hello, Trying to keep the database and the application fast, I´d like to clearify my mind about it. Ronan: I assume you mean has serious performance impact when you say weight. If this is not what you meant, please correct/clarify. 1) Is a SELECT DISTINCT over 5,000 records a weight query? (Supposing it has about 20 different option the the DISTINCT key). This query will most likely result in a creation of a temporary table with 20 columns and a key over all of them that will have no more than 5000 records, and will take 5000 attempted inserts to populate. Assuming that your WHERE clause is ok, this query should take no more than 3 seconds or so on modern hardware. However, this could be bad if you are doing this frequently and there is other activity going on. On the other hand, the query cache could save you. If it does not, consider creating and maintaining a summary table. 2) Is SELECT ORDER BY RAND() over 1,500 records a weight query? Does the table have only 1,500 records, and is it going to stay that way? Are you selecting only a few reasonably sized columns? If yes, unless you are Yahoo or Google, you'll do fine on modern hardware - this query under those curcumstances should take the order of maginitude of 0.01 s. However, if you have more records in the table, and the WHERE clause is not optimized, things could get bad, and this time the query cache does not save you. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indizes fr groe Datenbank
Hi Jigal, Jigal van Hemert schrieb: From: Andreas Brandl ich stehe gerade vor dem Problem, dass ich eine große Datenbank (ca. 2 Mio. Datensätze) optimieren muss. Since this list is in English I'll answer you in English, so the others can join the fun! Well, I didnt notice the list is in English :) So, thank you for translating :) You have to optimize a large database (2 Million records). 'bid' is primary field. There are a few x-id fields which are queried from time tot time, a few varchars which are rarely queried and a few informative fields. How do you optimally define the indexes? Well, first of all I would consider the table type. For this type of database the MyISAM and InnoDB table types can be used. The differences in short are: - MyISAM is fast in small tables - MyISAM is fast when you have very little inserts/updates and many selects - InnoDB is faster when you have about the same number of inserts/updates and selects. - InnoDB supports transactions - MyISAM supports full text indexes More details can be found in the MySQL documentation, but these were the main differences. Your choice does not really influence the way you make indexes, but it may/will influence the overall performance. Warning: InnoDB seems very slow with small datasets, but in high concurrency situations (about equal amounts of reads and writes) MyISAM will get slower when the number of records increases, while InnoDB will be roughly constant in speed. Ok, Im using myISAM because of much reading work, only little writing at all... [...] Oh, please read the manual on the MySQL site. It contains a couple of articles on query optimization, etc. I promise to do! Regards, Jigal. Thank you very much for the extensive answer. I'll try your proposals. Regards, Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging Data: Should I use MyIsam or InnoDB?
Hi Thomas, I have a curious issue here, maybe someone can help. I have a single process that inserts data into tables that contain purely logging information. This table is then searched by our Care department to troubleshoot issues. I am looking for the best way to store this data, and the structure on the backend. There are 50 million inserts into table LOG a day. The primary index on the table is seconds from 1971. I only need to keep 60 days worth of data, and the table is only used for read purposes. This is my design criteria, but my problem is how to delete old data without crashing the log writer that is atteched to the table. OK, how about this: Use MyISAM and MERGE tables. Keep one table per day. E.g.: log_2005_01_15 log_2005_01_16 log_2005_01_17 log_2005_01_18 etc. Use MERGE tables to give you the 60 day (and perhaps e.g. 14 day, 30 day, 7 day, etc.) read views that you need, like so: CREATE TABLE log_view_7day ( ... ) TYPE=MERGE UNION=( log_2005_01_12, log_2005_01_13, log_2005_01_14, log_2005_01_15, log_2005_01_16, log_2005_01_17, log_2005_01_18 ); Create another MERGE table for today using INSERT_METHOD: CREATE TABLE log_view_today ( ... ) TYPE=MERGE INSERT_METHOD=FIRST UNION=( log_2005_01_18 ); You can then do all of your inserts from the log writer into the today table, and do your reads against the various MERGEs. Every day at exactly midnight, you would use ALTER TABLE (which is atomic) to redefine the UNION of the MERGE definition of the various tables. When you want to delete your old data, it's simply a matter of doing an ALTER TABLE to remove them from the MERGE, and using DROP TABLE to drop the log__mm_dd table after that. Also note that you could compress the old data and leave it live (but read only) in case you ever need it. myisampack can help you with that. I hope that helps! 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]
allowMultQueries for Connector-J breaks replication from master.
Not sure which list this should go to ... seems like a bad bug so I sent it to the java and mysql lists to prevent it from hitting anyone else. (I also searched bugs.mysql.com which doesn't seem to show anything). We're playing with the allowMultiQueries feature in Connector-J 3.1.7 which allows you to run multiple queries in one executeStatement... this can reduce the time for 1000 queries from 1000ms down to about 1ms which for batch updates it MUCH faster. (its also faster than PreparedStatement batch updates by about 1000x as each stmt in the batch takes 1ms). In my tests I ran these against a single master but it looks like these will break replication. One of our engineers was playing with using this on our master and the query immediately broke all of our slaves: Last_Error: Error '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 '; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 1061 WHERE USER_FEED.USE' at line 1' on query. Default database: 'ksa'. Query: 'UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 5239 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61025 AND USER_FEED.SUBJECT_ID = 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 1061 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61026 AND USER_FEED.SUBJECT_ID = 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 4255 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61027 AND USER_FEED.SUBJECT_ID = 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 400 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61028 AND USER_FEED.SUBJECT_ID = 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 1007 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61261 AND USER_FEED.SUBJECT_ID = Skip_Counter: 0 He ran the statement once with about 200 UPDATEs in the multiquery. It seems like the master wrote these as 200 FULL statements (not split into individual statements) so when the slave replayed the transaction it broke. The issue seems to be that while the SQL is executed correctly its not written to the binary log correctly and thus breaks all the masters. Any thoughts here? Obviously we can't move to allowMultiQueries since this seems like a fatal bug. Thanks! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about Performance
Ronan Lucio [EMAIL PROTECTED] wrote on 01/18/2005 01:13:10 PM: Hello, Trying to keep the database and the application fast, I´d like to clearify my mind about it. 1) Is a SELECT DISTINCT over 5,000 records a weight query? (Supposing it has about 20 different option the the DISTINCT key). 2) Is SELECT ORDER BY RAND() over 1,500 records a weight query? I need to put these two queries in the first page of our site. So, I´ve been worried if it can slow down our site in the pics. Thanks, Ronan Depending on the size of the data, you may be better off caching your 5000 rows and 1500 rows in arrays on your web server. You would only need to refill the array if your source data changed. Since you should also control the code that updates the source data of the arrays, you can have it refill your server-cached arrays as soon as it finishes making its changes (inserts, updates, or deletes) to the source data. I can't tell you exactly which commands/objects/techniques to use to make static, global instances of those arrays (so that every user-specific thread sees the same objects) as you never said what your web server/language was. For example: if you were using an IIS/ASP server, I would tell you to store the arrays (NOT the recordsets! convert the recordsets to scalar data first) in the Application object (NOT the Session object). I recommend this because you say this is going to be served on the first page of your site. Everyone is going to need this data at least once. By keeping those lists as arrays in the web server's memory then randomly picking from the arrays, you will get blazing performance. Update the arrays only when the source data changes. That way you can save your SQL cycles for other, less predictable queries. I have reduced the response times on some websites to 20% or less than their original time by using this technique (even with query caching enabled! The time savings is not just in avoiding query processing but also due to eliminating network lag and data transfer time). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Logging Data: Should I use MyIsam or InnoDB?
Jeremy, Thanks, this is what I was originally thinking of, but how I am getting rid of the data in log_view_today? OR, are you saying that log_view_today is a merge table for only the current day? That table def is defined every night? Then I would go about dropping everything in whatever fashion I need. When I recreate the merge table for just the current day, don't I have to drop the merge table, or it just gets recreated automatically. I am not sure why you reference atomic on ALTER TABLE . . . , if there is a log writer attached to that table, won't I have to wait for a lock? What do you mean by atomic? I understand the term atomic transaction, just not sure of your context to this example. Thanks for the idea, I was already in this neck of the woods, but the MERGE table just for today, I was not sure about that. My problems in the past deal with the locking of the table by the logwriter, hence the need to truncate the table. But while it is truncating, the table hung, hence the need for InnoDB. Thomas. -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 2:28 PM To: Thomas Lekai Cc: mysql@lists.mysql.com Subject: Re: Logging Data: Should I use MyIsam or InnoDB? Hi Thomas, I have a curious issue here, maybe someone can help. I have a single process that inserts data into tables that contain purely logging information. This table is then searched by our Care department to troubleshoot issues. I am looking for the best way to store this data, and the structure on the backend. There are 50 million inserts into table LOG a day. The primary index on the table is seconds from 1971. I only need to keep 60 days worth of data, and the table is only used for read purposes. This is my design criteria, but my problem is how to delete old data without crashing the log writer that is atteched to the table. OK, how about this: Use MyISAM and MERGE tables. Keep one table per day. E.g.: log_2005_01_15 log_2005_01_16 log_2005_01_17 log_2005_01_18 etc. Use MERGE tables to give you the 60 day (and perhaps e.g. 14 day, 30 day, 7 day, etc.) read views that you need, like so: CREATE TABLE log_view_7day ( ... ) TYPE=MERGE UNION=( log_2005_01_12, log_2005_01_13, log_2005_01_14, log_2005_01_15, log_2005_01_16, log_2005_01_17, log_2005_01_18 ); Create another MERGE table for today using INSERT_METHOD: CREATE TABLE log_view_today ( ... ) TYPE=MERGE INSERT_METHOD=FIRST UNION=( log_2005_01_18 ); You can then do all of your inserts from the log writer into the today table, and do your reads against the various MERGEs. Every day at exactly midnight, you would use ALTER TABLE (which is atomic) to redefine the UNION of the MERGE definition of the various tables. When you want to delete your old data, it's simply a matter of doing an ALTER TABLE to remove them from the MERGE, and using DROP TABLE to drop the log__mm_dd table after that. Also note that you could compress the old data and leave it live (but read only) in case you ever need it. myisampack can help you with that. I hope that helps! 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]
Re: Doubt about Performance
Sasha, I assume you mean has serious performance impact when you say weight. If this is not what you meant, please correct/clarify. Yes, you´re right. 1) Is a SELECT DISTINCT over 5,000 records a weight query? (Supposing it has about 20 different option the the DISTINCT key). This query will most likely result in a creation of a temporary table with 20 columns and a key over all of them that will have no more than 5000 records, and will take 5000 attempted inserts to populate. Assuming that your WHERE clause is ok, this query should take no more than 3 seconds or so on modern hardware. However, this could be bad if you are doing this frequently and there is other activity going on. On the other hand, the query cache could save you. If it does not, consider creating and maintaining a summary table. Hmmm, I wanted to say the SELECT DISTINCT should return about 20 lines. The table should have about a million records, but WHERE clause should filter it to up to 5,000 and such query should be in the site´s home... :-/ Perhaps work with summaries would be a better choice. 2) Is SELECT ORDER BY RAND() over 1,500 records a weight query? Does the table have only 1,500 records, and is it going to stay that way? Are you selecting only a few reasonably sized columns? If yes, unless you are Yahoo or Google, you'll do fine on modern hardware - this query under those curcumstances should take the order of maginitude of 0.01 s. However, if you have more records in the table, and the WHERE clause is not optimized, things could get bad, and this time the query cache does not save you. The table should have about a million records, but WHERE clause should filter it to up to 5,000 and such query should be in the site´s home. I don´t know if it can put the site in performance troubles or if it´s paranoia of mine. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: allowMultQueries for Connector-J breaks replication from master.
Kevin A. Burton wrote: Not sure which list this should go to ... seems like a bad bug so I sent it to the java and mysql lists to prevent it from hitting anyone else. (I also searched bugs.mysql.com which doesn't seem to show anything). Actually... one more note The last time I tested this was with MySQL 4.0.12... it worked fine. This bug was only raised in MySQL 4.1.7 Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN with a ndbcluster engine
I am having an issue with a JOIN on two tables that are in a two node cluster. What happens is that the columns in the joined table are all null. students left outer join members on students.student_key = members.student_key I should get something like this: +--+---+--- +-+ | students.student_key | students.student_name | members.class_key | members.student_key | +--+---+--- +-+ |5 | Grace | NULL | NULL | |3 | Susie | NULL | NULL | |1 | Bill | 3 | 1 | |1 | Bill | 1 | 1 | |4 | Jean | 3 | 4 | |2 | John | 3 | 2 | |2 | John | 2 | 2 | |2 | John | 1 | 2 | +--+---+--- +-+ But I get something like this: +--+---+--- +-+ | students.student_key | students.student_name | members.class_key | members.student_key | +--+---+--- +-+ |5 | Grace | NULL | NULL | |3 | Susie | NULL | NULL | |1 | Bill | NULL | NULL | |1 | Bill | NULL | NULL | |4 | Jean | NULL | NULL | |2 | John | NULL | NULL | |2 | John | NULL | NULL | |2 | John | NULL | NULL | +--+---+--- +-+ These are not the real tables, the real tables are around 2000 rows I don't have this problem on a much smaller scale (see above) or in a InnoDB database with 2000 rows. Yes, the columns are set to NOT NULL. I am using mysql 4.1.7. Anyone seen this? Thanks kib -- Klaus Berkling Systems Administrator DynEd International, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: allowMultQueries for Connector-J breaks replication from master.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kevin A. Burton wrote: Kevin A. Burton wrote: Not sure which list this should go to ... seems like a bad bug so I sent it to the java and mysql lists to prevent it from hitting anyone else. (I also searched bugs.mysql.com which doesn't seem to show anything). Actually... one more note The last time I tested this was with MySQL 4.0.12... it worked fine. This bug was only raised in MySQL 4.1.7 Kevin Kevin, 4.0.12 doesn't have the feature, so you should've gotten a 'syntax error' when you tried it ;) -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 408 213 6557 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB7XEFtvXNTca6JD8RArKlAJ9GpoS/rENvxUus1aq20NHZ3VoN/ACfXTC6 ihs/9oEBXTpYsC32JwxtFZU= =p/Dj -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging Data: Should I use MyIsam or InnoDB?
Hi Thomas, Thanks, this is what I was originally thinking of, but how I am getting rid of the data in log_view_today? OR, are you saying that log_view_today is a merge table for only the current day? That table def is defined every night? Then I would go about dropping everything in whatever fashion I need. Basically, at midnight you would do a sequence like this: CREATE TABLE log_2005_01_19 ( ... ); ALTER TABLE log_view_today UNION=(log_2005_01_19); ALTER TABLE log_view_7day UNION=(..., log_2005_01_19); etc. etc. You could actually create the tables beforehand, and only do the ALTER TABLEs themselves at midnight. Note that this procedure has a race condition in that, depending on when the table actually switches over, you may have some records on either side of the split that don't belong. You can always move those back manually with something like: INSERT INTO log_2005_01_18 SELECT * from log_2005_01_19 WHERE datefield 2005-01-19 00:00:00; DELETE FROM log_2005_01_19 WHERE datefield 2005-01-19 00:00:00; or INSERT INTO log_2005_01_19 SELECT * from log_2005_01_18 WHERE datefield = 2005-01-19 00:00:00; DELETE FROM log_2005_01_18 WHERE datefield = 2005-01-19 00:00:00; In some cases it might be easier to do the switch always e.g. 5 seconds before midnight, so that any records falling on the wrong side of the edge will always be on the same side. That makes things easier sometimes. When I recreate the merge table for just the current day, don't I have to drop the merge table, or it just gets recreated automatically. I am not sure why you reference atomic on ALTER TABLE . . . , if there is a log writer attached to that table, won't I have to wait for a lock? What do you mean by atomic? I understand the term atomic transaction, just not sure of your context to this example. What I mean is, you can use ALTER TABLE to change the definition (e.g. which tables it contains) of the MERGE table. This happens atomically (no INSERTs will error, and no records could conceivably be split by the sudden change). Does that all make sense? 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]
RE: Logging Data: Should I use MyIsam or InnoDB?
Jeremy, Thanks a million, this makes perfect sense, I will test this out asap. In theory it sounds like the plan, I just need to see if it will work here. I really appreciate the help . . . Regards, Thomas. -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 3:30 PM To: Thomas Lekai Cc: mysql@lists.mysql.com Subject: Re: Logging Data: Should I use MyIsam or InnoDB? Hi Thomas, Thanks, this is what I was originally thinking of, but how I am getting rid of the data in log_view_today? OR, are you saying that log_view_today is a merge table for only the current day? That table def is defined every night? Then I would go about dropping everything in whatever fashion I need. Basically, at midnight you would do a sequence like this: CREATE TABLE log_2005_01_19 ( ... ); ALTER TABLE log_view_today UNION=(log_2005_01_19); ALTER TABLE log_view_7day UNION=(..., log_2005_01_19); etc. etc. You could actually create the tables beforehand, and only do the ALTER TABLEs themselves at midnight. Note that this procedure has a race condition in that, depending on when the table actually switches over, you may have some records on either side of the split that don't belong. You can always move those back manually with something like: INSERT INTO log_2005_01_18 SELECT * from log_2005_01_19 WHERE datefield 2005-01-19 00:00:00; DELETE FROM log_2005_01_19 WHERE datefield 2005-01-19 00:00:00; or INSERT INTO log_2005_01_19 SELECT * from log_2005_01_18 WHERE datefield = 2005-01-19 00:00:00; DELETE FROM log_2005_01_18 WHERE datefield = 2005-01-19 00:00:00; In some cases it might be easier to do the switch always e.g. 5 seconds before midnight, so that any records falling on the wrong side of the edge will always be on the same side. That makes things easier sometimes. When I recreate the merge table for just the current day, don't I have to drop the merge table, or it just gets recreated automatically. I am not sure why you reference atomic on ALTER TABLE . . . , if there is a log writer attached to that table, won't I have to wait for a lock? What do you mean by atomic? I understand the term atomic transaction, just not sure of your context to this example. What I mean is, you can use ALTER TABLE to change the definition (e.g. which tables it contains) of the MERGE table. This happens atomically (no INSERTs will error, and no records could conceivably be split by the sudden change). Does that all make sense? 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]
Re: Doubt about Performance
Ronan Lucio [EMAIL PROTECTED] wrote on 01/18/2005 02:53:39 PM: Shawn, Your tips are realy good but I can´t use cache neither for queries nor for pages because our site serves hundreds of clients, each one with your own code make a different query, returning different rows. It would be too many queries to be cached. I know I didn´t say it in the previous message. Thank you for the help, Ronan You would run your queries against your cached data using your web site's application code. You can create additional arrays to act as indexes against the data so that you will not need to do a full array scan every time. Load your data into your arrays in the order of the customer parameter, then you have already isolated each customer's data to a contiguous portion of the data array. Trust me, if you do it right (sorted and indexed data + fast lookup routine), it should be 10-20 times faster than trying to read through the same data from the database each and every time ([array search + array seek + looped scan] instead of [SQL parsing + query processing + net lag + data transfer time]). I do not recommend doing this to every page on your site, only to those pages that handle the highest traffic and only for data that doesn't change quickly (on the order of several changes per hour or per day, not several changes per second). For rapidly changing data, data you don't need often, or unpredictable queries, read the data from the database. It saves you no time to take the effort to cache that kind of data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
bind-address question
Hi everyone, I would like to bind mysqld to more than one IP address (localhost and my private unrouted interface). Is there a way to get --bind-address to bind to more than one IP? I have been unsucessfull when trying comma/space delimited arguments to bind-address, etc. Thanks, -Cere -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Like statement help
Hi There, I have web form where a user can search certain fields and then have them displayed aggregated. For example, find all the routes I climbed with partner A in area(s) ALL (% - wildcard) between date1 and date2 -- so on. See below for the complete query. I'm using pull down menu's and when a the user does not select something the default is ALL or option value='%' ALL /option for all. Then all the other options are listed. Should I be using the % as a wildcard? I would like it to work if one, two, three... or all fields are selected. Obviously, the more options you select the more detailed your search becomes and vice versa. Any thoughts on what could be wrong with my query? Any help would be appreciated. Thanks - Craig Here is my query query = SELECT routes.*, users.email, users.fname, users.lname, users.user_id, ranking.* FROM routes, users, ranking WHERE email='$email' AND area LIKE '%$area%' AND partner LIKE '%$partner%' AND id BETWEEN '$rating1' AND '$rating2' AND additional_rating IS NOT NULL LIKE '%$additional_rating%' AND pitchs LIKE '%$pitchs%' AND `type` LIKE '%$type%' AND style LIKE '%$style%' AND stars LIKE '%$stars%' AND fall LIKE '%$fall%' AND popular LIKE '%$popular%' AND date_climbed BETWEEN '$date_climbed1' AND '$date_climbed2' AND routes.rating = ranking.rating AND routes.user_id = users.user_id GROUP BY route_count ORDER BY area, date_climbed DESC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like statement help
Craig Hoffman [EMAIL PROTECTED] wrote on 01/18/2005 04:17:30 PM:
Hi There,
I have web form where a user can search certain fields and then have
them displayed aggregated. For example, find all the routes I climbed
with partner A in area(s) ALL (% - wildcard) between date1 and date2 --
so on. See below for the complete query.
I'm using pull down menu's and when a the user does not select
something the default is ALL or option value='%' ALL /option for
all. Then all the other options are listed. Should I be using the
% as a wildcard? I would like it to work if one, two, three... or all
fields are selected. Obviously, the more options you select the more
detailed your search becomes and vice versa. Any thoughts on what
could be wrong with my query? Any help would be appreciated.
Thanks - Craig
Here is my query
query = SELECT routes.*, users.email, users.fname, users.lname,
users.user_id, ranking.* FROM routes, users, ranking WHERE
email='$email' AND area LIKE '%$area%'
AND partner LIKE '%$partner%'
AND id BETWEEN '$rating1' AND '$rating2'
AND additional_rating IS NOT NULL LIKE '%$additional_rating%'
AND pitchs LIKE '%$pitchs%'
AND `type` LIKE '%$type%'
AND style LIKE '%$style%'
AND stars LIKE '%$stars%'
AND fall LIKE '%$fall%'
AND popular LIKE '%$popular%'
AND date_climbed BETWEEN '$date_climbed1' AND '$date_climbed2'
AND routes.rating = ranking.rating
AND routes.user_id = users.user_id
GROUP BY route_count ORDER BY area, date_climbed DESC;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rather than construct the query all at once, as you are doing, I would
suggest you conditionally check for each search condition then add those
conditions to the WHERE statement if they exist. Also, if you don't give
the user the opportunity to enter their own values (as in your Area SELECT
box) use an = not a LIKE, you will have a better chance of using an index
if you do.
I am VERY noob with PHP (if this even is PHP) so please forgive my
horribly wrong syntax :)
$query = SELECT routes.*, users.email, users.fname, users.lname,
users.user_id, ranking.* FROM routes, users, ranking WHERE
email='$email';
if ($area '') {
$query += AND area='$area';
}
if ($partner '') {
$query += AND partner LIKE '$partner';
}
... the other tests for your other search fields ...
$query += ORDER BY route_count, area, date_climbed DESC;
That way you only query on the fields the user enters and not every value
every time. This way, too, the user can decide if they want an exact
match, or some kind of partial match as LIKE will default to = if there
isn't a % in the comparator.
Also, you only need a GROUP BY if you are going to do some kind of
aggregation (sum, avg, max, min, etc...) but you weren't in this query so
I moved that term to the ORDER BY clause.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
getting error with mysql_fix_privilege_tables
When I run mysql_fix_privilege_tables it tells me to ignore a lot of errors. But it doesn't tell me about this error: ERROR 1061 at line 5: Duplicate key name 'Grantor' Can I ignore this? What is it from? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Listing all connected users?
How do I see who is currently connected to MYSQL from where they originate? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Listing all connected users?
Issue SHOW [FULL] PROCESSLIST as the super user -Original Message- From: sol beach [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 3:01 PM To: mysql@lists.mysql.com Subject: Listing all connected users? How do I see who is currently connected to MYSQL from where they originate? -- 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: Listing all connected users?
sol beach wrote: How do I see who is currently connected to MYSQL from where they originate? Use the SHOW PROCESSLIST command: URL: http://dev.mysql.com/doc/mysql/en/SHOW_PROCESSLIST.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication fails to resume after master crash
Master: mysqld 3.23.58 on FreeBSD 4.10-R Runs two masters via mysqld_multi Rotation #1: 10x mysqld 4.0.22 on FreeBSD 4.9-R, 4.10-R 1x mysqld 4.1.8a on FreeBSD 4.10-R Rotation #2 2x mysqld 4.0.22 on FreeBSD 4.9-R We have had the following happen twice in the past two weeks: The server the master runs on crashes and reboots; both master servers start up fine again by itself with an incrementing binlog position. For both rotations, all slaves running 4.0.22 reported running fine (both replication threads) but binlog position was not incrementing. No errors were reported in any of the error logs. The fix both times was to issue SLAVE STOP ; SLAVE START ;. The 4.1 slave has been unaffected in both instances. Is this a known bug? 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]
I seem to have lost a table somehow :-(
I have a Movable Type weblog at http://www.technofile.org/AverageJoe/ I am using MySQL to store the data for this weblog; mysql Ver 14.7 Distrib 4.1.7, for unknown-freebsd4.7 (i386) when I go to the MT Main Menu page, the weblog is not listed as existing. When I go to the mysql database directory, several pertinent .MYD files are empty; this concerns me. (massive understatment). -rw-rw 1 mysql mysql 672 Nov 6 20:13 mt_author.MYD -rw-rw 1 mysql mysql 4096 Nov 13 12:16 mt_author.MYI -rw-rw 1 mysql mysql 9350 Nov 6 20:13 mt_author.frm -rw-rw 1 mysql mysql 0 Nov 6 20:13 mt_blog.MYD -rw-rw 1 mysql mysql 1024 Nov 6 20:13 mt_blog.MYI -rw-rw 1 mysql mysql 11844 Nov 6 20:13 mt_blog.frm -rw-rw 1 mysql mysql 0 Nov 6 20:13 mt_category.MYD -rw-rw 1 mysql mysql 1024 Nov 6 20:13 mt_category.MYI -rw-rw 1 mysql mysql 8970 Nov 6 20:13 mt_category.frm -rw-rw 1 mysql mysql 0 Nov 6 20:13 mt_entry.MYD -rw-rw 1 mysql mysql 1024 Nov 6 20:13 mt_entry.MYI -rw-rw 1 mysql mysql 9678 Nov 6 20:13 mt_entry.frm myisamchk certainly doesn't give me warm fuzzies... % myisamchk mt_blog.MYI Checking MyISAM file: mt_blog.MYI Data records: 0 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check record links The weblog data is still out there (i.e. the database provides a backup and datastore for published .html files; MT isn't, thank goodness, completely dynamic in nature). I'm waiting in hopes that the MT tech support has a suggestion for rebuilding the database from published files. Is there some way I can fix this problem at the MySQL end? The .frm files have data. Does that mean anything? Can I use that? -- Vicki Brown ZZZJourneyman Sourceror: SF Bay Area, CAzz |\ _,,,---,,_ Scripts Philtres http://www.cfcl.com zz /,`.-'`'-. ;-;;,_Code, Doc, Process, QA http://cfcl.com/vlb |,4- ) )-,_. ,\ ( `'-'Perl, Unix, Mac OS X, WWW '---''(_/--' `-'\_) ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I seem to have lost a table somehow :-(
Somehow your table was zero'ed out, i.e. TRUNCATE / DROP-CREATE. MYD - data of the table MYI - index of the table Frm - TABLE definition I hope you have a backup. -Original Message- From: Vicki Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 3:15 PM To: mysql@lists.mysql.com Subject: I seem to have lost a table somehow :-( I have a Movable Type weblog at http://www.technofile.org/AverageJoe/ I am using MySQL to store the data for this weblog; mysql Ver 14.7 Distrib 4.1.7, for unknown-freebsd4.7 (i386) when I go to the MT Main Menu page, the weblog is not listed as existing. When I go to the mysql database directory, several pertinent .MYD files are empty; this concerns me. (massive understatment). -rw-rw 1 mysql mysql 672 Nov 6 20:13 mt_author.MYD -rw-rw 1 mysql mysql 4096 Nov 13 12:16 mt_author.MYI -rw-rw 1 mysql mysql 9350 Nov 6 20:13 mt_author.frm -rw-rw 1 mysql mysql 0 Nov 6 20:13 mt_blog.MYD -rw-rw 1 mysql mysql 1024 Nov 6 20:13 mt_blog.MYI -rw-rw 1 mysql mysql 11844 Nov 6 20:13 mt_blog.frm -rw-rw 1 mysql mysql 0 Nov 6 20:13 mt_category.MYD -rw-rw 1 mysql mysql 1024 Nov 6 20:13 mt_category.MYI -rw-rw 1 mysql mysql 8970 Nov 6 20:13 mt_category.frm -rw-rw 1 mysql mysql 0 Nov 6 20:13 mt_entry.MYD -rw-rw 1 mysql mysql 1024 Nov 6 20:13 mt_entry.MYI -rw-rw 1 mysql mysql 9678 Nov 6 20:13 mt_entry.frm myisamchk certainly doesn't give me warm fuzzies... % myisamchk mt_blog.MYI Checking MyISAM file: mt_blog.MYI Data records: 0 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check record links The weblog data is still out there (i.e. the database provides a backup and datastore for published .html files; MT isn't, thank goodness, completely dynamic in nature). I'm waiting in hopes that the MT tech support has a suggestion for rebuilding the database from published files. Is there some way I can fix this problem at the MySQL end? The .frm files have data. Does that mean anything? Can I use that? -- Vicki Brown ZZZJourneyman Sourceror: SF Bay Area, CAzz |\ _,,,---,,_ Scripts Philtres http://www.cfcl.com zz /,`.-'`'-. ;-;;,_Code, Doc, Process, QA http://cfcl.com/vlb |,4- ) )-,_. ,\ ( `'-'Perl, Unix, Mac OS X, WWW '---''(_/--' `-'\_) ___ -- 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: Doubt about Performance
-Original Message- From: Ronan Lucio [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 10:13 AM To: mysql@lists.mysql.com Subject: Doubt about Performance Hello, Trying to keep the database and the application fast, I´d like to clearify my mind about it. 1) Is a SELECT DISTINCT over 5,000 records a weight query? (Supposing it has about 20 different option the the DISTINCT key). This is not bad, it's a mysql function that uses a KEY if a key exist. 2) Is SELECT ORDER BY RAND() over 1,500 records a weight query? I need to put these two queries in the first page of our site. So, I´ve been worried if it can slow down our site in the pics. DO SELECT ORDER BY RAND() LIMIT 1500 Such that means much less then 1500 on the order of 10. This is a rather expensive operation and should be used with care. I've gotten around this by generating a random number in my app and trying to match it to a known id by making multiple selects. This was less intensive then ORDER BY RAND. -- DVP Thanks, Ronan -- 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: allowMultQueries for Connector-J breaks replication from master.
Mark Matthews wrote: Kevin, 4.0.12 doesn't have the feature, so you should've gotten a 'syntax error' when you tried it ;) Hm.. Maybe you're right... Now that you mention it I did test it against 4.1.7. -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
Re: How do I ... SQL question
Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; Put the results of the LIMITed query into a temporary table and re-query. CREATE TEMPORARY TABLE tmpStep1 ( freq int , name varchar(25) , year int ); INSERT tmpStep1 (freq, name, year) SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; select distinct a.place from tmpStep1 ts1 INNER JOIN a on a.name = ts1.name; Then you should get the list: place --- south west east Thanks to all who responded. The inner join does what I wanted. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setup question on mysql under Linux
I'm having trouble getting myslqd to run as a user other than root when it is started by mysqld_safe. I folowed the steps in the manual but mysqld continues to run as root, mysqld_safe runs as the changed user. I check my.conf and the user statement is in the mysqld section as outlined. If I manually start mysqld directly it runs as the changed user. so its something in mysqld thats changing it but I cannot find out what. I have a little programming knowledge but I'm no master. Can anyone point me in the right direction? Thanks in advance!!! -- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ? MySql Server on Suse Linux XP Workstations?
Hi Eric, Thanks for you reply. You said this PC Setup works. Can ask you to be my correspondence on this subject? If that is okay with you! Regards, Teddy At 04:29 PM 18/01/2005, Eric Bergen wrote: On Tue, 18 Jan 2005 07:51:18 +1100, Teddy Apostol [EMAIL PROTECTED] wrote: Hi Lists, Question: Who has opinion or experience installing a MySql version 4 or 5 into a Suse Linux Enterprise Server version 9 as a server and ... the clients workstations are Windows XP Profesional? Does it work? Yes. How? Very well. Any references to read? The vanilla binary install guide should give you enough hints to either get it going from a YaST install or to install the vanilla MySQL binaries. http://dev.mysql.com/doc/mysql/en/Installing.html Thanks, Teddy -- 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]
Regarding replication
Hi, Is replication of database is possible in MySQL 4.0.21?. If not from which version it is available?. Could any one of you please provide some helpful information about how to do the replication?. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
