Re: column being created as varchar() when char() requested.
You need supp_cd to be first in an index. Try ALTER TABLE hpi_supp_agmt ADD INDEX supp_ind (supp_cd); then try to add your foreign key constraint again. Michael Philip Walden wrote: Michael Stassen wrote: Hmmm..., it doesn't seem to be a problem for me in 4.0.17: mysql CREATE TABLE t3 - ( -id CHAR(5) NOT NULL, -description VARCHAR(48) NOT NULL, -PRIMARY KEY (id) - ) TYPE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql DESC t3; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | varchar(5) | | PRI | | | | description | varchar(48) | | | | | +-+-+--+-+-+---+ 2 rows in set (0.01 sec) mysql CREATE TABLE t4 - ( - id INT NOT NULL, - t3_id CHAR(5) NOT NULL, - INDEX t3_ind(t3_id) - ) TYPE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql ALTER TABLE t4 ADD CONSTRAINT FOREIGN KEY (t3_id) REFERENCES t3(id); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 I do get the error you quote (ERROR 1005: Can't create table...) if I leave out the index creation in either table, which is documented in the manual http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html. I can see that supp.supp_cd is a PRIMARY KEY. Did you create the required index on hpi_supp_agmt.supp_cd before you tried to add the foreign key reference? Michael The hpi_supp_agmt.supp_cd is part of a compound primary index. BTW, this is a legacy database I am trying to port. Here is the create for the hpi_supp_agmt: create table hpi_supp_agmt ( div_cd char(4) not null, hpi_no char(15) not null, supp_cd char(5) not null, agmt_no char(8) not null, agmt_owner char(4) not null, agmt_price decimal(16) not null, agmt_uom char(4) not null, agmt_lt integer not null, agmt_exp_dt date not null, updt_user_id smallint not null, updt_dt date not null, create_dt date not null, constraint p1hpisuppagmt primary key (div_cd,hpi_no,supp_cd,agmt_no) ) type = InnoDB; Thanks Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column being created as varchar() when char() requested.
Hi Philip, Hmmm..., it doesn't seem to be a problem for me in 4.0.17: mysql CREATE TABLE t3 - ( -id CHAR(5) NOT NULL, -description VARCHAR(48) NOT NULL, -PRIMARY KEY (id) - ) TYPE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql DESC t3; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | varchar(5) | | PRI | | | | description | varchar(48) | | | | | +-+-+--+-+-+---+ 2 rows in set (0.01 sec) mysql CREATE TABLE t4 - ( - id INT NOT NULL, - t3_id CHAR(5) NOT NULL, - INDEX t3_ind(t3_id) - ) TYPE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql ALTER TABLE t4 ADD CONSTRAINT FOREIGN KEY (t3_id) REFERENCES t3(id); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 I do get the error you quote (ERROR 1005: Can't create table...) if I leave out the index creation in either table, which is documented in the manual http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html. I can see that supp.supp_cd is a PRIMARY KEY. Did you create the required index on hpi_supp_agmt.supp_cd before you tried to add the foreign key reference? Michael The hpi_supp_agmt.supp_cd is part of a compound primary index. BTW, this is a legacy database I am trying to port. Here is the create for the hpi_supp_agmt: create table hpi_supp_agmt ( div_cd char(4) not null, hpi_no char(15) not null, supp_cd char(5) not null, agmt_no char(8) not null, agmt_owner char(4) not null, agmt_price decimal(16) not null, agmt_uom char(4) not null, agmt_lt integer not null, agmt_exp_dt date not null, updt_user_id smallint not null, updt_dt date not null, create_dt date not null, constraint p1hpisuppagmt primary key (div_cd,hpi_no,supp_cd,agmt_no) ) type = InnoDB; What if you create an additional index on the supp_cd column only? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 Stored Procedures are server wide?
Hi all, When creating a stored procedure, what must I do to create a procedure/function in a specific database? I created a function hello like in the example, but afterwards, I cannot create it from another database connection. The column db stays NULL in table procs. Any idea? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bash script to MySql
I need to to the following Select serial from serials; Problem is I need to run this from a script which cron will call once a day, how do you pass a select statement to mysql with user and pass so it can all happen in one go? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL viewer
Hi, Am Dienstag, 3. Februar 2004 22:50 schrieb Alex croes: I'm currently using MyCC as a tool to view and maintain a MySQL-database of mine. I find it quit a good tool, but which tool are you guys using. I have heard about MySQLfront and Navicat. Are this also good tools to maintain the database. What are the pro's and the con's of this viewers? What about PhpMyAdmin? If u use a LAMP System its the best i thinkeasy to setup and even more easy to handle. Bernd -- [Zufallssig 12] Wenn Windows 2000 die Antwort sein soll, wie bescheuert muss bloß die Frage gewesen sein. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Downloading/Exporting delimited text files
Hi, I hope someone can point us in the right direction. We have built up a database of over 2 million part numbers applicable to the horticultural machinery trade. This is held on our website in a MySQL database and subscribers can do all sorts of searches by manufacturer or part number or description, whatever. It all works well having used Dreamweaver to generate the various web pages. We now want to allow them to download a tab delimited text file for importing into their own standard spreadsheets and databases (unlikely to be MySQL). We are struggling with this as there don't appear to be any Macromedia Extensions which will allow us to do this within DreamWeaver. Will we have to use PHP in order to achieve this task? PHPAdmin allows us to do the above but we don't want our subscribers to use PHPAdmin, we want them to just click buttons on a web page. Any comments anyone? Thanks and regards, Bernard Norrie Catalist Solutions Ltd.
Re: Bash script to MySql
Scott Haneda wrote: I need to to the following Select serial from serials; Problem is I need to run this from a script which cron will call once a day, how do you pass a select statement to mysql with user and pass so it can all happen in one go? Something like this should work: #!/bin/bash /path/to/msyql -umack -ptheknife -e use serialDB; select serial from serials; Not tested with cron, but ... HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bash script to MySql
On Wednesday 04 February 2004 05:44, Scott Haneda wrote: I need to to the following Select serial from serials; Problem is I need to run this from a script which cron will call once a day, how do you pass a select statement to mysql with user and pass so it can all happen in one go? mysql -? -u, --user=name User for login if not current user. -p, --password[=name] Password to use when connecting to server. If password is not given it's asked from the tty. -e, --execute=name Execute command and quit. (Output like with --batch). You can also feed a command from a text file using . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Downloading/Exporting delimited text files
hi
it is very simple in php
$query = 'SELECT *
FROM table
WHERE somefield = '.$somevalue.'
ORDER BY somefield
';
$mysql_result = mysql_query($query, $mysql_link);
while($row = mysql_fetch_array($mysql_result))
{
$output .=
\$row[somefield]\\t\$row[some_other_field]\\t\$row[yetnother_fiel
d]\\t ;
}// end while
(not tested)
\t is the tab character
hth
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Catalist Solutions [mailto:[EMAIL PROTECTED]
Sent: 04 February 2004 09:04
To: MySQL Mailing List
Subject: Downloading/Exporting delimited text files
Hi,
I hope someone can point us in the right direction.
We have built up a database of over 2 million part numbers applicable to the
horticultural machinery trade.
This is held on our website in a MySQL database and subscribers can do all
sorts of searches by manufacturer or part number or description, whatever.
It all works well having used Dreamweaver to generate the various web pages.
We now want to allow them to download a tab delimited text file for
importing into their own standard spreadsheets and databases (unlikely to be
MySQL).
We are struggling with this as there don't appear to be any Macromedia
Extensions which will allow us to do this within DreamWeaver.
Will we have to use PHP in order to achieve this task? PHPAdmin allows us to
do the above but we don't want our subscribers to use PHPAdmin, we want them
to just click buttons on a web page.
Any comments anyone?
Thanks and regards,
Bernard Norrie
Catalist Solutions Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 31, 2004, at 1:09 AM, Adam Goldstein wrote: On Jan 30, 2004, at 10:25 AM, Bruce Dembecki wrote: On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it... SNIP SNIP Our switch to innodb was fairly smooth, but one table is unable to be converted due to a FullText Index. I see Innodb has only one drawback :) However, the results so far are very worth it. We are still having some overloads, but, they are certainly not mysql's fault. Apache/php is taking up too much load and memory at a certain point, but the G5 doesn't break a sweat. We still have some configuring to due, as we started with 6 x 2G ibdata files, which mysteriously are only 1G on disk. my.cnf settings below. We also kept some ram in the MyIsam portion of the config for the one remaining (large/important) MyIsam table. Do the settings look kosher? One test of the speed difference has so far registered a 5-10X speed increase (max). These also depend on time of day and filesystem deletes of multiple files for each, there is a backlog of perhaps another million items left to archive that this is working on, so we'll have this script as a working test for a few more days, as we can only run this during low load hours. Before Innodb: START (07:00:00) Done. 2279 archived. STOP (07:50:07): 3005.91sec START (20:00:00) Done. 5603 archived. STOP (20:50:16): 3015.15sec START (22:00:00) Done. 7265 archived. STOP (22:50:04): 3002.85sec After Innodb: START (18:00:00) Done. 16092 archived. STOP (18:50:03): 3002.25sec START (19:00:00) Done. 19683 archived. STOP (19:50:03): 3002.38sec START (22:00:00) Done. 25370 archived. STOP (22:50:04): 3003.6sec Under a simultaneous user/high load situation, would you suggest running with pconnects in php/mysql, and with persistent connections in apache? We have been seeing 300-400 outbound mysql connections from the main app server (via netstat -n -t|grep -c :3306 , which include mostly TIME_WAIT) , 300-450 apache processesoutbound *:80 connections on the primary app server (we are researching/pricing 2-4 frontend 1U servers now.. roughly 2Gram/2Ghz+/gigabit boxes, either P4/Athlon/Athlon64 or Xserves). We are still getting some odd results in stats, such as the same high 'change db' and 'connection' rates. relevant(?) innodb status; Per second averages calculated from the last 53 seconds (now, during low hours. I am not sure how many of these stats would change during high use hours, I will check tomorrow.) 5266530 OS file reads, 2492377 OS file writes, 448439 OS fsyncs 34.68 reads/s, 18790 avg bytes/read, 14.81 writes/s, 1.74 fsyncs/s Ibuf for space 0: size 1, free list len 249, seg size 251, 970319 inserts, 970319 merged recs, 189753 merges Hash table size 4980539, used cells 2737132, node heap has 3893 buffer(s) 9649.16 hash searches/s, 1424.65 non-hash searches/s Total memory allocated 1654471880; in additional pool allocated 2385280 Buffer pool size 76800 Free buffers 124 Database pages 72783 Modified db pages 9798 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 8644115, created 38059, written 2814095 39.87 reads/s, 0.21 creates/s, 17.83 writes/s Buffer pool hit rate 1000 / 1000 Number of rows inserted 2353929, updated 1543175, deleted 1191888, read 547022884 6.98 inserts/s, 0.47 updates/s, 5.94 deletes/s, 17275.54 reads/s relevant status; | Aborted_clients | 3088 | | Aborted_connects | 1 | | Bytes_received | 2788318966 | | Bytes_sent | 1674966066 | | Com_change_db| 5245603| | Com_delete | 1091654| | Com_insert | 1933786| | Com_insert_select| 440592 | | Com_lock_tables | 82167 | | Com_select | 5133100| | Com_unlock_tables| 82172 | | Com_update | 1525300| | Connections | 788173 | | Created_tmp_disk_tables | 350| | Created_tmp_tables | 96399 | | Created_tmp_files| 27 | | Flush_commands | 1 | | Handler_commit | 82157 | | Handler_delete | 0 | | Handler_read_first | 38191 | | Handler_read_key | 1081224301 | | Handler_read_next| 3683264158 | | Handler_read_rnd | 70681449 | | Handler_read_rnd_next| 1174208910 | | Handler_rollback | 729518 | | Handler_update | 55200716 | | Handler_write| 70961992 | |
Re: Bash script to MySql
Hi, Am Mittwoch, 4. Februar 2004 07:19 schrieb Hassan Schroeder: Scott Haneda wrote: I need to to the following Select serial from serials; Problem is I need to run this from a script which cron will call once a day, how do you pass a select statement to mysql with user and pass so it can all happen in one go? 2 possible ways to go: Feed a Textfile with your statement in the db: PATH/mysql db /sql_scripts/sql_statements_as_textfile --password=pass Give command directly In the shell-script: PATH/mysql --skip-column-names -e SELECT table SET blabla db --password=pass Have fun Bernd -- [Zufallssig 10] [Deng] on AO Forum: Good judgement is the result of experience... experience often comes from bad judgement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Monthly reporting
I just hacked out a script that will give me a month at a glance in MySql 3.x, I was thinking, there must be a way to do this in SQL. Table --- Name (varchar 48) Date (timestamp 14) Currently, I select distinct on the name to get all unique names, then I iterate on each name and issue a count() in SQL for each day of the month. It is this ~30 days in script I bet I can get rid of, any suggestions how to do this in MySql in one go and get a report of a month at a glance... -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
Ooops .. I think I have to be more precise about what I mean by replication. In facts the two servers A and B (in slave mode) have to replicate different tables included in the same database and I do not want to manage the replication through my application but only with the standard replication mode of my SQL. Example: Servers A and B managing database my_db and the two tables my_table_1 and my my_table_2. Server B slave of master A and replicating only my_table_1. Server A is in charge of upadating my_table1. The following parameter of my.cnf for server B is = replicate-ignore-table = my_db.my_table_2). Server A slave of master B and replicating only my_table_2. Server B is in charge of upadating my_table2. The following parameter of my.cnf for server A replicate-ignore-table = my_db.my_table_1). How does the configuration file looks like for the two server's ? Anyone has an example ? Thanks in advance. [EMAIL PROTECTED] el.Com To: Pierre Luguern/France/[EMAIL PROTECTED] cc: 03/02/2004 17:35 Subject: Re: Replication Pierre Luguern [EMAIL PROTECTED] wrote on 03/02/2004 15:41:30: Is this configuration possible with MySQL ? Server A is acting as a master server for the A database. Server B is acting as a slave, replicating the A database from server A. Server B is acting as a master server for the B Database. Server A is acting as a slave, replicating the B database from server B. How do the configuration file looks like for the two server's ? What I think you want is circular replication: A replicates B, B replicates A. MySQL can detect when an update has travelled round the full circle, and stops it propagating back to the server which originated it. It is then up to you, at the application level, to route updates for Database A to server A and updates for Database B to server B. If you can ensure this, I think you will get the effect you desire. If, of course, you route updates indiscriminately, things could go wrong. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joined tables still not working
- Original Message - From: Luis Lebron [EMAIL PROTECTED] The data tables look like this: Manhours mid | ProjectId |ChargeNum | Catid | Hours | EmployeeId 1 | 32 |11 | 19 | 80 |200020 2 | 32 |11 | 19 | 24 |23 Tasks TaskID |ChargeNum |Catid |EmployeeId |Hours 1 |11 |19 |200020 |8 2 |11 |19 |200020 |8 5 |11 |19 |23 |12 By looking at the Manhours table I can tell that for ProjectId 32 I have 104 (80+24) hours alloted for Catid 19 By looking at the Tasks table I can tell that 36 hours (8 + 8 + 8 + 12) have been used on this project (same ChargeNum as Manhours table) and Catid 19 I can't see any solution that uses a single query for this. You want to link sumarised info from one table to summarised info from another table. If you need to do it with queries, the only way I see to do this, is to have one query gather info from the Manhours table and store it in a temporary table. Then you can join the temporary table with the Tasks table. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of CHECK TABLE REPAIR TABLE
Gowtham Jayaram [EMAIL PROTECTED] wrote: - Are there any benchmarks that provide information on time taken to run 'CHECK TABLE' and/or 'REPAIR TABLE' on tables of different sizes ? You can run your own benchmarks and find out the statistics you need. - What are the factors that effect the time taken to run 'CHECK TABLE' and/or 'REPAIR TABLE' on a table other than its size? MySQL server parameters, number of indexes, hardware, disk throughput .. - Are there any pointers to optimize the performance of these commands ? Read about myisam_sort_buffer_size, myisam_max_extra_sort_file_size, myisam_repair_threads, myisam_max_sort_file_size options: http://www.mysql.com/doc/en/SHOW_VARIABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
User Defined Function
Dear all How can I make a user defined function (written in C/C++) what returns a row or rowset / result set (like show open tables) for example select udf(1,'par2') what returns C1 | C2 | C3 +--+- 1 | 2 | A 2 | 3 | B 3 | 4 | C Thanks Gerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bash script to MySql
Why not use another language that has mysql hooks built in? For example, from version 4.3.0 PHP comes with SAPI to allow scripting from the command line, ideal for cron style tasks and it's mysql support is very mature. -- Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL viewer
They are mostly windows-only tools! CowBoyNeal uses only mysql console client! jejeje :-) (just a joke!) PHPMyAdmin is a web based tool, very nice SQLyog is a basic, very nice tool also (www.sqlyog.com) DBTools is very very nice, and also support PostgreSQL (www.dbtools.com.br) MySQL-Front is very nice, but 3 seems to be abandoned :( (www.mysql-front-de) I don't really like mysqlcc, seems too fragile to me and basic (sorry folks!) but it seems that mysql guys are working on a new one, that seems very nice, altough they don't have a date of release, it seems that they'll support linux also, let see let see... If you aske me, i would really advice to use the mysql client, vi editor(to edit my.cnf) and the manual, is the best way to really understand and learn mysql, besides helping you become a real guru! jeje (anyway it is'n as difficult as it may seems!) Best Regards PS: would somebody write a real nice and feature complete gtk+ based or java-based admin tool (i know this is to much to ask for, but if it supports postgre also would be very nice. sacrilege) ? :-) On Tue, 2004-02-03 at 17:50, Alex croes wrote: I'm currently using MyCC as a tool to view and maintain a MySQL-database of mine. I find it quit a good tool, but which tool are you guys using. I have heard about MySQLfront and Navicat. Are this also good tools to maintain the database. What are the pro's and the con's of this viewers? Alex -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Re: MySQL viewer
Hi all, They are mostly windows-only tools! CowBoyNeal uses only mysql console client! jejeje :-) (just a joke!) PHPMyAdmin is a web based tool, very nice SQLyog is a basic, very nice tool also (www.sqlyog.com) DBTools is very very nice, and also support PostgreSQL (www.dbtools.com.br) MySQL-Front is very nice, but 3 seems to be abandoned :( (www.mysql-front-de) Just to add another Windows-based tool, but runs fine in Wine or Win4Lin: Database Workbench - www.upscene.com Not free, but not expensive either. It's a newcomer, so if you find anything wrong, be sure to let me know ;-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM Table Corruption
Hassan, By Murphy's law, they WILL get corrupted if you don't have a backup. You need a current backup, or you need an older backup and a way to redo the updates. That said, if you do a FLUSH TABLES after your update, then corruption is unlikely--no more likely than for any other OS file. After an update to a MyISAM table and before doing a FLUSH TABLES, you can easily get corruption on, say, a power failure. (This is observed behavior, despite a claim in the manual that the data are written to disk after the update statement. The data file is incompletely written, so myisamchk doesn't recover it.) From: Hassan Shaikh [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Subject: MyISAM Table Corruption Date: Tue, 3 Feb 2004 12:50:29 +0400 What are the chances of MyISAM tables corruption when the table is update rarely? (Once in a 60+ days). It's basically a lookup table used mainly in SELECT statements. Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Downloading/Exporting delimited text files
On 04-Feb-2004 Peter Lovatt wrote: hi it is very simple in php snip $query = 'SELECT * $query=SELECT flds, you, want FROM ... snip $output .= \$row[somefield]\\t\$row[some_other_field]\\t\$row[yetnothe r_fiel d]\\t ; $output .=implode(\t, $row) .\n; }// end while Header(Content-type: text/tab-separated-values); Header(Content-Disposition: inline; filename=data.tsv); echo $output; snip Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 Stored Procedures are server wide?
Martijn Tonies [EMAIL PROTECTED] wrote: When creating a stored procedure, what must I do to create a procedure/function in a specific database? I created a function hello like in the example, but afterwards, I cannot create it from another database connection. Currently you can't create SP in the particular database. The column db stays NULL in table procs. Any idea? Column 'db' in the table 'proc' isn't used yet. It's for future use. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 Stored Procedures are server wide?
Hi, When creating a stored procedure, what must I do to create a procedure/function in a specific database? I created a function hello like in the example, but afterwards, I cannot create it from another database connection. Currently you can't create SP in the particular database. The column db stays NULL in table procs. Any idea? Column 'db' in the table 'proc' isn't used yet. It's for future use. Thank you for your answer. Can I expect procedures/functions to be database specific in the future? Or will they stay server-wide? IMO, they should be db specific :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bash script to MySql
Why not use another language that has mysql hooks built in? Good suggestion. For example, from version 4.3.0 PHP comes with SAPI to allow scripting from the command line, ideal for cron style tasks and it's mysql support is very mature. Or better yet, Perl. Not only is its mysql support very mature the entire language is! *AND* you don't have to mess with PHP Which, as an administrator of lots of servers over the years, is only seconded in horribleness of headaches by Microsoft stuff. Didn't want to start flames going just wanted to pint out a better alternative. -- Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question
I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie query question...
I have a simple query: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC Results: ++--+---+-++ | id | year | month | day | cat_id | ++--+---+-++ | 25 | 2003 |12 | 7 | 5 | | 44 | 2004 | 2 | 15 | 2 | | 53 | 2004 | 3 | 28 | 5 | | 45 | 2004 | 6 | 6 | 2 | ++--+---+-++ Can anyone point out where I've screwed up the query?? I don't think it should be giving me the first entry. It is a trivial problem, and I can filter the results further with php, but I'd like to do it right, and am trying to learn SQL. JC __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
Pierre Luguern [EMAIL PROTECTED] wrote: Ooops .. I think I have to be more precise about what I mean by replication. In facts the two servers A and B (in slave mode) have to replicate different tables included in the same database and I do not want to manage the replication through my application but only with the standard replication mode of my SQL. Example: Servers A and B managing database my_db and the two tables my_table_1 and my my_table_2. Server B slave of master A and replicating only my_table_1. Server A is in charge of upadating my_table1. The following parameter of my.cnf for server B is = replicate-ignore-table = my_db.my_table_2). Server A slave of master B and replicating only my_table_2. Server B is in charge of upadating my_table2. The following parameter of my.cnf for server A replicate-ignore-table = my_db.my_table_1). How does the configuration file looks like for the two server's ? Anyone has an example ? The solution is in the answer. You knew! :) You can use either of --replicate-ignore-table or --replicate-do-table options. For example: server A: [mysqld] ... master-host= master-user= ... master-connect-retry= replicate-ignore-table=my_db.my_table_1 server B: [mysqld] ... master-host= master-user= ... master-connect-retry= replicate-ignore-table=my_db.my_table_2 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Newbie query question...
From: John Croson [mailto:[EMAIL PROTECTED] I have a simple query: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC Results: ++--+---+-++ | id | year | month | day | cat_id | ++--+---+-++ | 25 | 2003 |12 | 7 | 5 | | 44 | 2004 | 2 | 15 | 2 | | 53 | 2004 | 3 | 28 | 5 | | 45 | 2004 | 6 | 6 | 2 | ++--+---+-++ Can anyone point out where I've screwed up the query?? I don't think it should be giving me the first entry. It is a trivial problem, and I can filter the results further with php, but I'd like to do it right, and am trying to learn SQL. You problem is in that OR. Add a couple parentheses to get: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND (cat_id='2' OR cat_id='5') ^^ AND approved='1' ORDER BY year,month,day ASC Previously, it was going through all the ANDs and then saying oh, /or/ I can grab rows with a catid of 5 (hence the inclusion of that first row). HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 Stored Procedures are server wide?
Martijn Tonies [EMAIL PROTECTED] wrote: Hi, When creating a stored procedure, what must I do to create a procedure/function in a specific database? I created a function hello like in the example, but afterwards, I cannot create it from another database connection. Currently you can't create SP in the particular database. The column db stays NULL in table procs. Any idea? Column 'db' in the table 'proc' isn't used yet. It's for future use. Thank you for your answer. Can I expect procedures/functions to be database specific in the future? Or will they stay server-wide? IMO, they should be db specific :-) Yes, you can. In a future they will be database specific. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Newbie query question...
Hi John, I think you missed on the precedence of AND/OR if you change to AND (cat_id='2' OR cat_id='5' ) it should work as you want it to /Johan John Croson wrote: I have a simple query: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC -- Johan Höök, Facility Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
- Original Message - From: rmck [EMAIL PROTECTED] Are my indexes all gone?? If so how do I recover them! Thanks hehe ;-) MySQL just doesn't know the cardinality of the indexes yet (the cardinality number is a *guess*) Try CHECK TABLE and I think you will see the cardinality numbers appear again. Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
- Original Message - From: rmck [EMAIL PROTECTED] Are my indexes all gone?? If so how do I recover them! Thanks hehe ;-) MySQL just doesn't know the cardinality of the indexes yet (the cardinality number is a *guess*) Try CHECK TABLE and I think you will see the cardinality numbers appear again. Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Did you run an ANALYZE TABLE? Original Message On 2/4/04, 9:33:30 AM, rmck [EMAIL PROTECTED] wrote regarding index question: I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+ -+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ -+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+ -+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+ -+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ -+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+ -+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- 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]
wysiwyg web soft
Hi to all, I'm fed up with Access as a wysiwyg editor for mysql. Generaly, i'm fed up with client that i have to install on each comp. Actualy, i'm looking for an opensource wysiwyg web software. Something like the very good phpMyAdmin but with 'visual' request editor for simple users. If anyone knows such thing. Thanx. fab. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [BUG] SHOW INDEX bug for fulltext indexes in MySQl 4.0.17
Hi! On Jan 23, Dave Rolsky wrote: Here's a recipe: create table foo (foo text, bar text); create fulltext index foo on foo (foo, bar); mysql show index from foo; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | foo | 1 | foo |1 | foo | A | NULL |1 | NULL | YES | FULLTEXT | | | foo | 1 | foo |2 | bar | A | NULL |1 | NULL | YES | FULLTEXT | | +---++--+--+-+---+-+--++--++-+ Sub_part should be NULL for both of these columns. The same thing happens for a single column fulltext index. Fixed. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question part 2
I understand that I need to update the db's cardinality for this table I need speed Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one because with 56179085 records this could take a while... Thanks for the replies Rob -Forwarded Message- From: rmck [EMAIL PROTECTED] Sent: Feb 4, 2004 7:33 AM To: [EMAIL PROTECTED] Subject: index question I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wysiwyg web soft
i'm using this one : http://vsbabu.org/webdev/zopedev/ieeditor.html but it works just on IE5.5 SP1 and above (IE6;) - Original Message - From: fab [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 04, 2004 5:06 PM Subject: wysiwyg web soft Hi to all, I'm fed up with Access as a wysiwyg editor for mysql. Generaly, i'm fed up with client that i have to install on each comp. Actualy, i'm looking for an opensource wysiwyg web software. Something like the very good phpMyAdmin but with 'visual' request editor for simple users. If anyone knows such thing. Thanx. fab. -- 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: mysqld crash on FreeBSD-Alpha (64 Bit)
Hi! On Jan 12, Holm Tiffe wrote: Description: mysqld 4.0.17 crash on FreeBSD 5.1-current-alpha How-To-Repeat: Any acces over IP (not domain socket) crashes mysqld: #/usr/local/bin/mysqladmin: connect to server at 'install' failed error: 'Lost connection to MySQL server during query' syslog: install mysqld[78066]: warning: can't get client address: Bad file descriptor All my tests shows that it crashes in libwrap. If I compile mysqld without libwrap it doesn't crash. Sorry, but it makes it a low-priority issue that I can probably look at later. As a workaround just compile MySQL without libwrap. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql error message
I've installed mysql under Linux and what I do, I always have this number of error message :040204. Please I need your help! Thank u Vinx Accédez au courrier électronique de La Poste : www.laposte.net ; 3615 LAPOSTENET (0,34/mn) ; tél : 08 92 68 13 50 (0,34/mn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Behavior.
Seeking opinions on this. Server is Dual Pentium Xeon 2.8, 6 GB RAM, running RedHat Linux 7.2, MySQL 4.0.17, all installed and tested with no problems. I had a small database for testing purposes, and then dropped it, leaving the default installation databases, mysql and test. If I run top from shell I get the following: 19683 root 9 0 956 956 772 S 0.0 0.0 0:00 mysqld_safe 19716 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19718 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19719 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19720 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19721 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19722 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld I checked the connections to the server and there are absolutely none. No one else is on a shell session except me. This is going on for the last 48 hrs. Is this normal? Do I need to do anything about this? If so, please point me to the right direction. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie query question...
Mike Johnson wrote:
From: John Croson [mailto:[EMAIL PROTECTED]
I have a simple query:
SELECT id,year,month,day,cat_id FROM events
LEFT JOIN calendar_cat ON
events.cat=calendar_cat.cat_id
WHERE year=YEAR(CURDATE())
AND month=MONTH(CURDATE())
AND day=DAYOFMONTH(CURDATE())
AND cat_id='2' OR cat_id='5'
AND approved='1'
ORDER BY year,month,day ASC
Results:
++--+---+-++
| id | year | month | day | cat_id |
++--+---+-++
| 25 | 2003 |12 | 7 | 5 |
| 44 | 2004 | 2 | 15 | 2 |
| 53 | 2004 | 3 | 28 | 5 |
| 45 | 2004 | 6 | 6 | 2 |
++--+---+-++
Can anyone point out where I've screwed up the query??
I don't think it should be giving me the first entry.
It is a trivial problem, and I can filter the results
further with php, but I'd like to do it right, and am
trying to learn SQL.
You problem is in that OR.
Add a couple parentheses to get:
SELECT id,year,month,day,cat_id FROM events
LEFT JOIN calendar_cat ON
events.cat=calendar_cat.cat_id
WHERE year=YEAR(CURDATE())
AND month=MONTH(CURDATE())
AND day=DAYOFMONTH(CURDATE())
AND (cat_id='2' OR cat_id='5')
^^
AND approved='1'
ORDER BY year,month,day ASC
Previously, it was going through all the ANDs and then saying oh,
/or/ I can grab rows with a catid of 5 (hence the inclusion of that
first row).
HTH!
An equivalent way to do this would be to use IN instead of OR, like this:
SELECT id,year,month,day,cat_id FROM events
LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id
WHERE year=YEAR(CURDATE())
AND month=MONTH(CURDATE())
AND day=DAYOFMONTH(CURDATE())
AND cat_id IN('2','5')
AND approved='1'
ORDER BY year,month,day ASC
By the way, are cat_id and approved really strings (CHAR or VARCHAR)?
If they are integers (INT, etc.), you can leave out the quotes on the
comparison values:
SELECT id,year,month,day,cat_id FROM events
LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id
WHERE year=YEAR(CURDATE())
AND month=MONTH(CURDATE())
AND day=DAYOFMONTH(CURDATE())
AND cat_id IN(2,5)
AND approved=1
ORDER BY year,month,day ASC
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql error message
Are you able to start the MySQL server? Is there anything being logged to the hostname.err log? Original Message On 2/3/04, 7:23:50 PM, vincent.gueu [EMAIL PROTECTED] wrote regarding Mysql error message: I've installed mysql under Linux and what I do, I always have this number of error message :040204. Please I need your help! Thank u Vinx Accédez au courrier électronique de La Poste : www.laposte.net ; 3615 LAPOSTENET (0,34/mn) ; tél : 08 92 68 13 50 (0,34/mn) -- 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: Mysql error message
From: vincent.gueu [mailto:[EMAIL PROTECTED] I've installed mysql under Linux and what I do, I always have this number of error message :040204. Please I need your help! Thank u I have no idea what your problem is, but I'd be amused if you had the number 040205 tomorrow (those being today's and tomorrow's dates, respectively). Can you elaborate on the problem? Where do you get this error? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index change moving files to other computer?
Hi folks. I'm in the midst of moving from a debian unstable system to a gentoo system and I'm having some problems getting mysql to give me the same performance. On the new gentoo system (with more hardware) a fairly complex query (a search on a UBBThreads forum) is taking 10+ seconds to complete, while on the debian system it's in the 0.01s range. Before I'm critisized on my distro choice, it appears I've traced part of the problem down with EXPLAIN: (sorry about the width :( ) New but slower system: +---+++-+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+--+-+ | t1| ref| w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex7 | 3 | const | 6607 | Using where; Using filesort | | t2| eq_ref | indx1 | indx1 | 100 | t1.B_Board|1 | | | t3| eq_ref | PRIMARY,indx3 | PRIMARY | 4 | t1.B_PosterId |1 | | +---+++-+-+---+--+-+ Old but faster system: +---+++-+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+---+-+ | t1| range | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex8 | 4 | NULL | 19645 | Using where | | t2| eq_ref | indx1 | indx1 | 100 | t1.B_Board| 1 | | | t3| ref| PRIMARY,indx3 | indx3 | 4 | t1.B_PosterId | 1 | | +---+++-+-+---+---+-+ The big thing here as I've read and understood it is that the gentoo system is Using filesort, which is horribly slow compared to plain old where and indexes. However, I've made no changes to the database files, just copied /var/lib/mysql/database from the old system to the new. The faster box is a 4.0.14 system and the slower is using 4.0.16. Based on my reading of some of the docs on mysql.com using filesort is used when mysql can't use indexes for the order by clause. Would these indexes not be there and still available when moved to the new system? It seems very strange to me. Oh, and I also tried dumping just that database and re-importing it from the (sql) dump file, with the same results. Please help! BTW, specs on the systems: Old: debian unstable running linux 2.4.24 with mysql 4.0.14 XP1800 with 1G ram on two IDE drives with software RAID1 New: gentoo stable, kernels used were 2.4.24, 2.4.25_pre6, and 2.6.1 with and without preempt. Mysql tried 4.0.16 static and dynamic with various cflags and 4.0.17 binaries from mysql.com. my.cnf has been set to the same as o nthe old box, the default config, and the huge, large and medium sample configs, all with the same results. Many thanks. alan -- Alan [EMAIL PROTECTED] - http://arcterex.net There are only 3 real sports: bull-fighting, car racing and mountain climbing. All the others are mere games.-- Hemingway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index change moving files to other computer?
The older system is choosing to use a different index. I would suggest running an analyze on your new tables and see if you can get the newer system to use the same Postsindex8 index. Original Message On 2/4/04, 1:01:46 PM, Alan [EMAIL PROTECTED] wrote regarding index change moving files to other computer?: Hi folks. I'm in the midst of moving from a debian unstable system to a gentoo system and I'm having some problems getting mysql to give me the same performance. On the new gentoo system (with more hardware) a fairly complex query (a search on a UBBThreads forum) is taking 10+ seconds to complete, while on the debian system it's in the 0.01s range. Before I'm critisized on my distro choice, it appears I've traced part of the problem down with EXPLAIN: (sorry about the width :( ) New but slower system: +---++ +-+-+---+--+-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++ +-+-+---+--+-- ---+ | t1| ref| w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex7 | 3 | const | 6607 | Using where; Using filesort | | t2| eq_ref | indx1 | indx1 | 100 | t1.B_Board|1 | | | t3| eq_ref | PRIMARY,indx3 | PRIMARY | 4 | t1.B_PosterId |1 | | +---++ +-+-+---+--+-- ---+ Old but faster system: +---++ +-+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++ +-+-+---+---+-+ | t1| range | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex8 | 4 | NULL | 19645 | Using where | | t2| eq_ref | indx1 | indx1 | 100 | t1.B_Board| 1 | | | t3| ref| PRIMARY,indx3 | indx3 | 4 | t1.B_PosterId | 1 | | +---++ +-+-+---+---+-+ The big thing here as I've read and understood it is that the gentoo system is Using filesort, which is horribly slow compared to plain old where and indexes. However, I've made no changes to the database files, just copied /var/lib/mysql/database from the old system to the new. The faster box is a 4.0.14 system and the slower is using 4.0.16. Based on my reading of some of the docs on mysql.com using filesort is used when mysql can't use indexes for the order by clause. Would these indexes not be there and still available when moved to the new system? It seems very strange to me. Oh, and I also tried dumping just that database and re-importing it from the (sql) dump file, with the same results. Please help! BTW, specs on the systems: Old: debian unstable running linux 2.4.24 with mysql 4.0.14 XP1800 with 1G ram on two IDE drives with software RAID1 New: gentoo stable, kernels used were 2.4.24, 2.4.25_pre6, and 2.6.1 with and without preempt. Mysql tried 4.0.16 static and dynamic with various cflags and 4.0.17 binaries from mysql.com. my.cnf has been set to the same as o nthe old box, the default config, and the huge, large and medium sample configs, all with the same results. Many thanks. alan -- Alan [EMAIL PROTECTED] - http://arcterex.net There are only 3 real sports: bull-fighting, car racing and mountain climbing. All the others are mere games.-- Hemingway -- 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: index change moving files to other computer?
On Wed, Feb 04, 2004 at 07:21:30PM +, [EMAIL PROTECTED] wrote: The older system is choosing to use a different index. I would suggest Any idea why it would choose this? Shouldn't mysql keep using the same indexes? running an analyze on your new tables and see if you can get the newer system to use the same Postsindex8 index. I ran myisamchk -a on this, which according to the documentation is the same. No changes. I did downgrade to 4.0.14 however and happy happy day it's back up to the speed that I was used to! Now I'd love to know why I can't upgrade :) Sorry for being such a lamer n00b, but it's not my DB and mysql has always just worked for me (though I don't use many 600k row tables :) Alan -- Alan [EMAIL PROTECTED] - http://arcterex.net There are only 3 real sports: bull-fighting, car racing and mountain climbing. All the others are mere games.-- Hemingway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 Stored Procedures are server wide?
Hi, When creating a stored procedure, what must I do to create a procedure/function in a specific database? I created a function hello like in the example, but afterwards, I cannot create it from another database connection. Currently you can't create SP in the particular database. The column db stays NULL in table procs. Any idea? Column 'db' in the table 'proc' isn't used yet. It's for future use. Thank you for your answer. Can I expect procedures/functions to be database specific in the future? Or will they stay server-wide? IMO, they should be db specific :-) Yes, you can. In a future they will be database specific. Good. Thanks. Hopefully in 5.0.1 then :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql ECHILD resets on Queries
Ive got a machine running redhat 9 kernel 2.4.20-28.9 and a recent update
from APT seems to have destroyed mysql somehow.
Mysql Version is 3.23.58-1.9
Any query kills the child process of mysqld and it has to respawn. This
does not seem to be the proper behaviour and it is causing many issues for
the php scripts that talk to mysql.
php is version 4.3.3-2 apache is 2.0.47-6
i cant even get a dump of the databases because of the respawning child
process. Ive been searching google and the arcives here for the last 24
hours and I've found nothing of the like..
an strace of the safe_mysqld shows this.
I'm at my witts end here. any insight or way to get a better dump of what
is really killing it?
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xbfffef24, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
pipe([3, 4])= 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2134
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(4)= 0
close(4)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(3)= 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2134
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigaction(SIGINT, {SIG_IGN}, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
8) = 0
close(3)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xb1e4, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
stat64(/bin/rm, {st_mode=S_IFREG|0755, st_size=26780, ...}) = 0
access(/bin/rm, X_OK) = 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2137
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], WNOHANG) = 2137
waitpid(-1, 0xbfffef64, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigaction(SIGINT, {SIG_IGN}, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
stat64(/usr/bin/nohup, {st_mode=S_IFREG|0755, st_size=2129, ...}) = 0
access(/usr/bin/nohup, X_OK) = 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2138
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
waitpid(-1, unfinished ...
--
=
Daniel PowellA+ [EMAIL PROTECTED]
Networkgeek MCP http://networkgeek.org
=
And Trogdor smote the Kerrek, and all was laid to Burnination.
=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
Have you looked in the hostname.err logs for the MySQL server?
Original Message
On 2/4/04, 2:00:53 PM, Daniel Powell [EMAIL PROTECTED] wrote
regarding Mysql ECHILD resets on Queries:
Ive got a machine running redhat 9 kernel 2.4.20-28.9 and a recent update
from APT seems to have destroyed mysql somehow.
Mysql Version is 3.23.58-1.9
Any query kills the child process of mysqld and it has to respawn. This
does not seem to be the proper behaviour and it is causing many issues
for
the php scripts that talk to mysql.
php is version 4.3.3-2 apache is 2.0.47-6
i cant even get a dump of the databases because of the respawning child
process. Ive been searching google and the arcives here for the last 24
hours and I've found nothing of the like..
an strace of the safe_mysqld shows this.
I'm at my witts end here. any insight or way to get a better dump of what
is really killing it?
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xbfffef24, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
pipe([3, 4])= 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2134
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(4)= 0
close(4)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(3)= 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2134
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigaction(SIGINT, {SIG_IGN}, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
8) = 0
close(3)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xb1e4, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
stat64(/bin/rm, {st_mode=S_IFREG|0755, st_size=26780, ...}) = 0
access(/bin/rm, X_OK) = 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2137
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], WNOHANG) = 2137
waitpid(-1, 0xbfffef64, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigaction(SIGINT, {SIG_IGN}, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
stat64(/usr/bin/nohup, {st_mode=S_IFREG|0755, st_size=2129, ...}) = 0
access(/usr/bin/nohup, X_OK) = 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2138
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
waitpid(-1, unfinished ...
--
=
Daniel PowellA+ [EMAIL PROTECTED]
Networkgeek MCP http://networkgeek.org
=
And Trogdor smote the Kerrek, and all was laid to Burnination.
=
--
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:
Is there any documentation of Best Practices/Troubleshooting Guides for Administering MySQL
We are implementing three or four MySql servers (as a start) and I'm writing the Troubleshooting Guide for our operational staff. None of these folks have any MySQL experience (and I'm a newbie myself). I need a pretty basic 'Cheat Sheet' for troubleshooting common production type problems. The staff is all very technical - Senior level Oracle DBAs - I'm going to have to drag them kicking and screaming into the MySQL world :-) Thanks in advance. I'm having fun with this tool, I'm looking forward to see how it does in production. Evelyn
Re: Mysql ECHILD resets on Queries
Where exactly SHOULD these be and is it something I need to enable in
my.cnf
the generic /var/log/mysqld.log has this each time the process dies:
Number of processes running now: 0
040203 22:21:08 mysqld restarted
Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
If you do not want to use transactional InnoDB tables, add a line
skip-innodb
to the [mysqld] section of init parameters in your my.cnf
or my.ini. If you want to use InnoDB tables, add to the [mysqld]
section, for example,
innodb_data_file_path = ibdata1:10M:autoextend
But to get good performance you should adjust for your hardware
the InnoDB startup options listed in section 2 at
http://www.innodb.com/ibman.html
/usr/libexec/mysqld: ready for connections
libgcc_s.so.1 must be installed for pthread_cancel to work
until I enabled skip-innodb and now i get :
Number of processes running now: 0
040204 14:31:18 mysqld restarted
/usr/libexec/mysqld: ready for connections
libgcc_s.so.1 must be installed for pthread_cancel to work
libgcc_s.so.1 is in /lib as a symlink:
[EMAIL PROTECTED] lib]# ls -al libgcc_s*
-rwxr-xr-x1 root root30324 Feb 25 2003 libgcc_s-3.2.2-20030225.so.1
lrwxrwxrwx1 root root 28 Jul 25 2003 libgcc_s.so.1 -
libgcc_s-3.2.2-20030225.so.1
On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote:
Have you looked in the hostname.err logs for the MySQL server?
Original Message
On 2/4/04, 2:00:53 PM, Daniel Powell [EMAIL PROTECTED] wrote
regarding Mysql ECHILD resets on Queries:
Ive got a machine running redhat 9 kernel 2.4.20-28.9 and a recent update
from APT seems to have destroyed mysql somehow.
Mysql Version is 3.23.58-1.9
Any query kills the child process of mysqld and it has to respawn. This
does not seem to be the proper behaviour and it is causing many issues
for
the php scripts that talk to mysql.
php is version 4.3.3-2 apache is 2.0.47-6
i cant even get a dump of the databases because of the respawning child
process. Ive been searching google and the arcives here for the last 24
hours and I've found nothing of the like..
an strace of the safe_mysqld shows this.
I'm at my witts end here. any insight or way to get a better dump of what
is really killing it?
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xbfffef24, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
pipe([3, 4])= 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2134
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(4)= 0
close(4)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(3)= 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2134
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigaction(SIGINT, {SIG_IGN}, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
8) = 0
close(3)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xb1e4, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
stat64(/bin/rm, {st_mode=S_IFREG|0755, st_size=26780, ...}) = 0
access(/bin/rm, X_OK) = 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2137
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], WNOHANG) = 2137
waitpid(-1, 0xbfffef64, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
RE: Is there any documentation of Best Practices/Troubleshooting Guides for Administering MySQL
We are implementing three or four MySql servers (as a start) and I'm writing the Troubleshooting Guide for our operational staff. None of these folks have any MySQL experience (and I'm a newbie myself). I need a pretty basic 'Cheat Sheet' for troubleshooting common production type problems. The staff is all very technical - Senior level Oracle DBAs - I'm going to have to drag them kicking and screaming into the MySQL world :-) Thanks in advance. I'm having fun with this tool, I'm looking forward to see how it does in production. It will do awesome, it always has for me anyway! I'd say the best general guide is the mysql.com website, very informtive and intuitive. HTH DMuey Evelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
The hostname.err log will generally be located in the data directory.
Original Message
On 2/4/04, 2:42:02 PM, Daniel Powell [EMAIL PROTECTED] wrote
regarding Re: Mysql ECHILD resets on Queries:
Where exactly SHOULD these be and is it something I need to enable in
my.cnf
the generic /var/log/mysqld.log has this each time the process dies:
Number of processes running now: 0
040203 22:21:08 mysqld restarted
Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
If you do not want to use transactional InnoDB tables, add a line
skip-innodb
to the [mysqld] section of init parameters in your my.cnf
or my.ini. If you want to use InnoDB tables, add to the [mysqld]
section, for example,
innodb_data_file_path = ibdata1:10M:autoextend
But to get good performance you should adjust for your hardware
the InnoDB startup options listed in section 2 at
http://www.innodb.com/ibman.html
/usr/libexec/mysqld: ready for connections
libgcc_s.so.1 must be installed for pthread_cancel to work
until I enabled skip-innodb and now i get :
Number of processes running now: 0
040204 14:31:18 mysqld restarted
/usr/libexec/mysqld: ready for connections
libgcc_s.so.1 must be installed for pthread_cancel to work
libgcc_s.so.1 is in /lib as a symlink:
[EMAIL PROTECTED] lib]# ls -al libgcc_s*
-rwxr-xr-x1 root root30324 Feb 25 2003
libgcc_s-3.2.2-20030225.so.1
lrwxrwxrwx1 root root 28 Jul 25 2003 libgcc_s.so.1 -
libgcc_s-3.2.2-20030225.so.1
On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote:
Have you looked in the hostname.err logs for the MySQL server?
Original Message
On 2/4/04, 2:00:53 PM, Daniel Powell [EMAIL PROTECTED] wrote
regarding Mysql ECHILD resets on Queries:
Ive got a machine running redhat 9 kernel 2.4.20-28.9 and a recent update
from APT seems to have destroyed mysql somehow.
Mysql Version is 3.23.58-1.9
Any query kills the child process of mysqld and it has to respawn. This
does not seem to be the proper behaviour and it is causing many issues
for
the php scripts that talk to mysql.
php is version 4.3.3-2 apache is 2.0.47-6
i cant even get a dump of the databases because of the respawning child
process. Ive been searching google and the arcives here for the last 24
hours and I've found nothing of the like..
an strace of the safe_mysqld shows this.
I'm at my witts end here. any insight or way to get a better dump of what
is really killing it?
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xbfffef24, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
pipe([3, 4])= 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2134
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(4)= 0
close(4)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [CHLD], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
close(3)= 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [CHLD], 8) = 0
rt_sigaction(SIGINT, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8}, {SIG_IGN},
8) = 0
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2134
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], 0) = 2136
rt_sigprocmask(SIG_SETMASK, [CHLD], NULL, 8) = 0
rt_sigaction(SIGINT, {SIG_IGN}, {0x8075dc0, [], SA_RESTORER, 0x4004b9a8},
8) = 0
close(3)= -1 EBADF (Bad file descriptor)
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, 0xb1e4, WNOHANG)= -1 ECHILD (No child processes)
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
stat64(/bin/rm, {st_mode=S_IFREG|0755, st_size=26780, ...}) = 0
access(/bin/rm, X_OK) = 0
rt_sigprocmask(SIG_BLOCK, [INT CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|0x11,
ignored, ignored, 0x4015c0c8) = 2137
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
waitpid(-1, [WIFEXITED(s) WEXITSTATUS(s) == 0], WNOHANG) = 2137
waitpid(-1, 0xbfffef64, WNOHANG)= -1
OT: Bash script to MySql
Sorry for the OT, I deleted the messages from the perosn who sent me a response offlist. In response to his personal emails in reply to: http://lists.mysql.com/mysql/158945 he said, Perl was a write only language (meaning it is hard to read since careless people may do sloppy code - not me though!) So I wanted to share with him, in a friendly nature of course ;p, this: If you can't read the code, run it through here: http://perltidy.sourceforge.net/ Now you don't have any excuses! :) Ok no more OT posts! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strace of mysqld panic
This goes along with the PIDs
[EMAIL PROTECTED] mysql]# strace -p 20018
select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(4, {sa_family=AF_UNIX, path=@¬@@è4@}, [2]) = 5
fcntl64(4, F_SETFL, O_RDWR) = 0
getsockname(5, {sa_family=AF_UNIX, path=/var/lib/mysql}, [28]) = 0
fcntl64(5, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(5, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(5, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not
supported)
time(NULL) = 1075929158
mmap2(NULL, 69632, PROT_READ|PROT_WRITE|PROT_EXEC,
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x40aab000
mprotect(0x40aab000, 4096, PROT_NONE) = 0
clone(child_stack=0x40abbb08,
flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID|CLONE_DETACHED,
[20022], {entry_number:6, base_addr:0x40a0, limit:1048575,
seg_32bit:1, contents:0, read_exec_only:0, limit_in_pages:1,
seg_not_present:0, useable:1}) = 20022
futex(0x82dfa38, FUTEX_WAKE, 1, ptrace: umoven: Input/output error
{...}PANIC: attached pid 20018 exited
-- =
Daniel PowellA+ [EMAIL PROTECTED]
Networkgeek MCP http://networkgeek.org
=
And Trogdor smote the Kerrek, and all was laid to Burnination.
=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
It appears to be running with safe_mysqld root 7237 1 0 14:26 pts/000:00:01 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf and locate *.err returns nothing. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
If the server is up and running can you log in a perform a show variales from the mysql monitor. From this information you can learn where your logs are being stored. ... Also, what sort of logging are you currently performing? Are you logging just errors , slow queries or everything? ... Original Message On 2/4/04, 3:27:55 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: It appears to be running with safe_mysqld root 7237 1 0 14:26 pts/000:00:01 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf and locate *.err returns nothing. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Starting MySQL 4.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've reinstalled MySQL 4.1. I'm trying to start the server (mysqld_safe), but it fails to start. The log file shows the message: /usr/sbin/mysqld-max: Fatal error: Can't find messagefile '/share/mysql/english/errmsg.sys' The files exists, but the path it's using to find it is wrong; it is actually at /usr/share/mysql/english. Can anyone suggest how to get it to find the file? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAIWTqjeziQOokQnARAkBrAKCt6c3M3AsG605VFWMvcooXhqw05ACeKnEE 999f2G9pTEQi8M4tWkETrLo= =jyv0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
its pretty default. I'd like to enable all logging if possible. What specificly should I enable in my.cnf? On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: If the server is up and running can you log in a perform a show variales from the mysql monitor. From this information you can learn where your logs are being stored. ... Also, what sort of logging are you currently performing? Are you logging just errors , slow queries or everything? ... Original Message On 2/4/04, 3:27:55 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: It appears to be running with safe_mysqld root 7237 1 0 14:26 pts/000:00:01 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf and locate *.err returns nothing. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
All logging will grow very rapidly on a `busy` server. To enable all logging you can add the --log option to the safe_mysqld command. safe_mysqld --log Original Message On 2/4/04, 3:48:46 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: its pretty default. I'd like to enable all logging if possible. What specificly should I enable in my.cnf? On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: If the server is up and running can you log in a perform a show variales from the mysql monitor. From this information you can learn where your logs are being stored. ... Also, what sort of logging are you currently performing? Are you logging just errors , slow queries or everything? ... Original Message On 2/4/04, 3:27:55 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: It appears to be running with safe_mysqld root 7237 1 0 14:26 pts/000:00:01 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf and locate *.err returns nothing. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My SQL Database Synchronization Question
I have two MySQL databases that are networked together. If changes can be made independently to each of the databases - what is the best way to synchronize them? How can I synchronize them in real time? If the network link goes down, can the synchronizations be placed upon a queue until the connection is restored Thank You, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
I may have resolved the crashing issue... I upgraded gcc and libgcc, as well as bintools and cpp for dependancy issues... the child PID no longer crashes... possible that the libgcc was corrupt? im stracing the pid now and it does not exit as before. no more constant respawning. I will enable --log on the init script and see if it shows me anything blatanly evil. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: All logging will grow very rapidly on a `busy` server. To enable all logging you can add the --log option to the safe_mysqld command. safe_mysqld --log Original Message On 2/4/04, 3:48:46 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: its pretty default. I'd like to enable all logging if possible. What specificly should I enable in my.cnf? On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: If the server is up and running can you log in a perform a show variales from the mysql monitor. From this information you can learn where your logs are being stored. ... Also, what sort of logging are you currently performing? Are you logging just errors , slow queries or everything? ... Original Message On 2/4/04, 3:27:55 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: It appears to be running with safe_mysqld root 7237 1 0 14:26 pts/000:00:01 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf and locate *.err returns nothing. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query the data of a fulltext index directly from index?
Hi Sergei! Great news. Thanks very much! :-) Matt - Original Message - From: Sergei Golubchik Sent: Tuesday, February 03, 2004 1:54 PM Subject: Re: query the data of a fulltext index directly from index? Hi! On Feb 02, Matt W wrote: Sergei, Any chance of getting a ft_dump Windows binary in the distribution? :-) Chances are good :) It was added to rpms and binary unix distributions 5 min ago, and it should be added to windows distro too. Note - the new name is myisam_ftdump. Regards, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie query question...
At 09:35 AM 2/4/2004, John Croson wrote: I have a simple query: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC Results: ++--+---+-++ | id | year | month | day | cat_id | ++--+---+-++ | 25 | 2003 |12 | 7 | 5 | | 44 | 2004 | 2 | 15 | 2 | | 53 | 2004 | 3 | 28 | 5 | | 45 | 2004 | 6 | 6 | 2 | ++--+---+-++ Can anyone point out where I've screwed up the query?? I don't think it should be giving me the first entry. It is a trivial problem, and I can filter the results further with php, but I'd like to do it right, and am trying to learn SQL. John, It looks like you're storing the date as 3 separate columns, Year, Month, Day. This is definitely NOT the way to do it. You need to create a Date column and call it something like, Cal_Date (or make it a DateTime if you want the time of the event). Now you can do proper comparisons between 2 dates. Your existing 3 column comparison will break down quickly if the stored day is less than the current day but the stored month and year is greater than the current date. Example: Year=2004, Month=3, Day=1 Your query will fail because Day 1 4 (if today is Feb 4th). You basically want all events that are scheduled for today or in the future. If you get rid of Year, Month, Day columns and replace them with cal_date, you get something much simpler (and your head won't hurt so muchg.): SELECT id, year(cal_date) as Year, month(cal_date) as Month, day(cal_date) as Day, cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE cal_date = CURDATE() AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY cal_date ASC So you use a single column to store the date, in this case cal_date, and use functions like Year(), Month(), Day() to extract the values for your display. There are a pile of date functions that are at your disposal. Your Where clause and sort always refers to the underlying cal_date Date column. For a list of date functions, see http://www.mysql.com/doc/en/Date_and_time_functions.html. 'hope this helps. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql ECHILD resets on Queries
Here is my final analysis of this problem. May it be correct or not, mysql is no longer panicing the child process.. libgcc was either corrupt or incompatible. It was the original version so I lean towards corrupt. Whatever corrupted it, I have no idea what, mysql was PISSED off about it. Whatever call Mysql was making for pthreads and such wasnt hitting the stack pointer right and totally taking a dump. The parent process saw this and restarted the child. this of course pissed off apache and whatever other thread was pointing to that socket/pid for mysql access. Thus all the mysql not responding and such from applications. I'll have to trace back several upgrades before I can pinpoint the exact upgrade that caused the failure. Thanks for everyone's input! On Wed, 4 Feb 2004, Daniel Powell wrote: I may have resolved the crashing issue... I upgraded gcc and libgcc, as well as bintools and cpp for dependancy issues... the child PID no longer crashes... possible that the libgcc was corrupt? im stracing the pid now and it does not exit as before. no more constant respawning. I will enable --log on the init script and see if it shows me anything blatanly evil. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: All logging will grow very rapidly on a `busy` server. To enable all logging you can add the --log option to the safe_mysqld command. safe_mysqld --log Original Message On 2/4/04, 3:48:46 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: its pretty default. I'd like to enable all logging if possible. What specificly should I enable in my.cnf? On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: If the server is up and running can you log in a perform a show variales from the mysql monitor. From this information you can learn where your logs are being stored. ... Also, what sort of logging are you currently performing? Are you logging just errors , slow queries or everything? ... Original Message On 2/4/04, 3:27:55 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: It appears to be running with safe_mysqld root 7237 1 0 14:26 pts/000:00:01 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf and locate *.err returns nothing. On Wed, 4 Feb 2004 [EMAIL PROTECTED] wrote: A locate *.err returns nothing? What happens when you attempt to start MySQL with safe_mysqld? Original Message On 2/4/04, 3:04:54 PM, Daniel Powell [EMAIL PROTECTED] wrote regarding Re: Mysql ECHILD resets on Queries: nothing of the sort. no .err files on the machine nothing named redstone.err or localhost.err. Just baffling. something had mysql hosed and any request or query, even something from mysqladmin, kills it. -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- = Daniel PowellA+ [EMAIL PROTECTED] Networkgeek MCP http://networkgeek.org = And Trogdor smote the Kerrek, and all was laid to Burnination. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Starting MySQL 4.1
Try setting the basedir= parameter in your my.cnf file. Original Message On 2/4/04, 3:32:26 PM, Michael Satterwhite [EMAIL PROTECTED] wrote regarding Starting MySQL 4.1: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've reinstalled MySQL 4.1. I'm trying to start the server (mysqld_safe), but it fails to start. The log file shows the message: /usr/sbin/mysqld-max: Fatal error: Can't find messagefile '/share/mysql/english/errmsg.sys' The files exists, but the path it's using to find it is wrong; it is actually at /usr/share/mysql/english. Can anyone suggest how to get it to find the file? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAIWTqjeziQOokQnARAkBrAKCt6c3M3AsG605VFWMvcooXhqw05ACeKnEE 999f2G9pTEQi8M4tWkETrLo= =jyv0 -END PGP SIGNATURE- -- 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: Newbie query question...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 04 February 2004 16:03, mos wrote: At 09:35 AM 2/4/2004, John Croson wrote: I have a simple query: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC My server is down right now, but I believe your problem is in the OR conditional. Without parenthesis to separate your conditions, the query seems to be asking for a condition that the cat_id is '2' and the stored date is greater than or equal to the current date *OR* the cat_id is '5' and the approved flag is '1'. I'm betting that if you look at the first item, the approved flag is equal to '1'. You might want to try AND (cat_id='2' or cat_id='5') -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAIW8cjeziQOokQnARAiY/AJ9G4qaGLioww3vJ5PfYPN2KOSUdWQCcDac0 29vkek0CgRQM74b1nabARHM= =mfhb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there any documentation of Best Practices/Troubleshooting Guides for Administering MySQL
Thanks, No problem, but please post to the list and not just me so we can all learn and share. Also top posting is bad form, just FYI. I have been perusing the web site, but the manuals don't always give reasons WHY you would run something. For example http://www.mysql.com/doc/en/FLUSH.html the why/what/who: You should use the FLUSH command if you want to clear some of the internal caches MySQL uses. To execute FLUSH, you must have the RELOAD privilege. the flush-tables command. Why would you run it and what does it do? There are several references to this command but I man mysqladmin summed it up for me niceley can't seem to find exactly what it does. Can I do it any time, is it non-destructive etc. If the site and man don't give you the kind of answer you seek then post the specific question to this list. HTH DMuey -Original Message- From: Dan Muey [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 04, 2004 3:33 PM To: Schwartz, Evelyn; [EMAIL PROTECTED] Subject: RE: Is there any documentation of Best Practices/Troubleshooting Guides for Administering MySQL We are implementing three or four MySql servers (as a start) and I'm writing the Troubleshooting Guide for our operational staff. None of these folks have any MySQL experience (and I'm a newbie myself). I need a pretty basic 'Cheat Sheet' for troubleshooting common production type problems. The staff is all very technical - Senior level Oracle DBAs - I'm going to have to drag them kicking and screaming into the MySQL world :-) Thanks in advance. I'm having fun with this tool, I'm looking forward to see how it does in production. It will do awesome, it always has for me anyway! I'd say the best general guide is the mysql.com website, very informtive and intuitive. HTH DMuey Evelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
condense mulitple tables....
I am using 3 different systems... Each have a registration system... I believe they all use md5 to encrypt the password. Is it possible to just put as the value of the password record something like this: dbname.dbtablename So that it will be that value, instead of one that is inserted into the database? Then I can have them all use the same usernames and password for all the systems, instead of having 3 different ones? One is Perl, two are PHP. Thank you. Richard
Replication
Hallo, I am in the process of setting up a simple replication and have a question that I can't figure out from the docs. I have 2 servers, N1 is a test machine and hosts 2 databases live and test. N1 is a slave to N2 which is a production machine and primary host of live. Basically I want to achieve 2 things. 1. To replicate live from N2 to N1. 2. To populate test on N1 by replication from live on N1 The access rights on the test machine to the test database are all for the developers, who have only read rights to live on the same machine and no access at all to live on N2.. Basically I am somewhat confused on how to handle replication of the mysql database and the access rights. Will the access rights from N2 overwrite anything on N1 or can a more fine-grained control be achieved. Or should I exclude some or all of the contents of mysql from the replication and maintain that database manually ??? Thanks for any tips. Regards Mike Anderson / Germany -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there any documentation of Best Practices/Troubleshooting Guides for Administering MySQL
On 4 Feb 2004, at 20:32, Dan Muey wrote: We are implementing three or four MySql servers (as a start) and I'm writing the Troubleshooting Guide for our operational staff. None of these folks have any MySQL experience (and I'm a newbie myself). I need a pretty basic 'Cheat Sheet' for troubleshooting common production type problems. The staff is all very technical - Senior level Oracle DBAs - I'm going to have to drag them kicking and screaming into the MySQL world :-) Thanks in advance. I'm having fun with this tool, I'm looking forward to see how it does in production. It will do awesome, it always has for me anyway! I'd say the best general guide is the mysql.com website, very informtive and intuitive. No, Evelyn's request is a good one. I use MySQL day to day for some very different applications and have little trouble with it. Others coming to it from so-called real database backgrounds try to make it behave like Oracle and it rebels. There are design and code considerations that just make life easier for the programmer and the DBA. As with any database (ask a Sybase DBA!) The mod_perl support mailing list, led by Stas Bekman, produced the mod_perl guide with community support that recently led to an 800+ page O'Reilly book. I'd like to see something like this for MySQL: for those beyond basic web applications and trying to make their lives easier. Um, does this make sense? -- Dave Hodgkinson CTO, Rockit Factory Ltd. http://www.rockitfactory.com/ Web sites for rock bands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column being created as varchar() when char() requested.
Michael Stassen wrote: You need supp_cd to be first in an index. Try ALTER TABLE hpi_supp_agmt ADD INDEX supp_ind (supp_cd); then try to add your foreign key constraint again. Michael Looks like that does not work. BTW this does work (without adding a separate index) in postgres. mysql alter table hpi_supp_agmt add index i1hpisuppagmt (supp_cd); Query OK, 28635 rows affected (4.89 sec) Records: 28635 Duplicates: 0 Warnings: 0 mysql alter table hpi_supp_agmt add constraint f1hpisuppagmt foreign key (hpi_no) references hpi; ERROR 1005 (HY000): Can't create table './gem/#sql-c3f_2.frm' (errno: 150) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How MySQL is handling unicode() some doubts
Hello I am going thru the source code of MySQL client. I am not able to understand in general how MySQL is handling unicode characters. All the function take (char*) as arguments? Is it expecting all the data to be UTF8-encoded and before executing the queries (i was looking into the batch file processing) it is decoding it? I am not sure about it...? Even the normal C API take char* for all their functions. Can somebody tell me how MySQL is handling multi-byte character data? Should I post the same in mysql-internals list? Regards Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Specific Max File Size Allow
Hi, Is it posssible to specific the max file size allowed for mysql database? Thanks, Keng Heng. Chan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bash script to MySql
on 02/04/2004 02:23 AM, Bernd Tannenbaum at [EMAIL PROTECTED] wrote: 2 possible ways to go: Feed a Textfile with your statement in the db: PATH/mysql db /sql_scripts/sql_statements_as_textfile --password=pass Give command directly In the shell-script: PATH/mysql --skip-column-names -e SELECT table SET blabla db --password=pass I need to select into outfile on this one, which poses a problem to me, I can not write the file to any directory as I get a permissions error since mysql is the owner of this file, how would I do this ? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Behavior.
Hi, - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, February 04, 2004 12:12 PM Subject: Server Behavior. Seeking opinions on this. Server is Dual Pentium Xeon 2.8, 6 GB RAM, running RedHat Linux 7.2, MySQL 4.0.17, all installed and tested with no problems. I had a small database for testing purposes, and then dropped it, leaving the default installation databases, mysql and test. If I run top from shell I get the following: 19683 root 9 0 956 956 772 S 0.0 0.0 0:00 mysqld_safe 19716 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19718 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19719 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19720 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19721 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld 19722 mysql 9 0 12960 12M 1612 S 0.0 0.2 0:00 mysqld I checked the connections to the server and there are absolutely none. No one else is on a shell session except me. This is going on for the last 48 hrs. Is this normal? Do I need to do anything about this? Those are just the minimum mysqld threads running (they appear as processes with LinuxThreads). You'd probably see the same thing right after starting the server. :-) BTW, the size of the whole process is 12M in your case; NOT 12M for each thread. Hope that helps. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL autogenerate, update table
Hi David, ALTER TABLE table ADD ListingID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; That will add the column at the beginning of the table (first column). Remove FIRST from the end if you don't want that (it will then go at the end) or replace it with: AFTER some_other_column Also sending this to the General list since it isn't a Windows specific question. :-) Matt - Original Message - From: tooptoosh Sent: Wednesday, February 04, 2004 1:54 PM Subject: mySQL autogenerate, update table Hi all, I have a mySQL table with 75,000 records in it, but the table has no primary key (autogenerate) column in it. I want to add this field column ListingID to the table. How do I do that? Cheers, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query the data of a fulltext index directly from index?
Sergei, Any chance of getting a ft_dump Windows binary in the distribution? :-) Regards, Matt - Original Message - From: Sergei Golubchik Sent: Monday, February 02, 2004 11:33 AM Subject: Re: query the data of a fulltext index directly from index? Hi! On Feb 02, Alexander Bauer wrote: Hello, is there any way to get the fulltext index contents directly? I'm looking for a way to list all indexed words from a column to provide a filter selection. How can I access the index data without walking through all table rows, get the column and tokenize and collect words? Use the ft_dump utility program that comes from MySQL source distribution. Regards, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Faster version of Movie Search
I have a site where members rate movies they've seen. I run a routine to recommend 5 titles based on movies they've given a max 5 rating. It's very slow, and I think a better MySQL query can speed it up. I'm running MySQL 3.23.54. Tables: movies (unique id plus movie info) subcats (movie id, subcategory id) ratings (movie id, user id) Currently: Step 1: // User's top five movie subcategories SELECT COUNT(s.subcategory) AS cnt, s.subcategory FROM ratings r, movies m, movie_subcat s WHERE r.rating = 5 AND r.user_id = $user_id AND r.type = 'movie' AND m.id = r.item AND s.movie = m.id GROUP BY s.subcategory ORDER BY cnt DESC LIMIT 5; // Create a list of subcategory IDs to match Step 2: // Which titles already rated? SELECT item FROM ratings WHERE user_id = $user_id // This is where it slows things down by creating a huge list of ids NOT to match Step 3: SELECT m.id, m.title FROM movies m, movie_subcat s WHERE s.movie = m.id AND m.release NOW() [AND m.id NOT IN (huge list of ids NOT to match)] [AND s.subcategory IN (list of 5 subcats)] GROUP BY m.id ORDER BY RAND() LIMIT 5 Thanks to any takers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Debugging MySql Source in Visual C++
Hi all, I am not part of the official MySql team. I am just a lone developer. I tried debugging MySql source (server) in Visual C++, but it doesn't seem to work. I followed the instructions in the MySql manual and added a few breakpoints at a few random places, but the debugger never stopped there. I would really appreciate if anyone can help..
Re: Debugging MySql Source in Visual C++
At 01:31 5/2/2004, Ananth Raghuraman wrote: Hi, Hi all, I am not part of the official MySql team. I am just a lone developer. I tried debugging MySql source (server) in Visual C++, but it doesn't seem to work. I followed the instructions in the MySql manual and added a few breakpoints at a few random places, but the debugger never stopped there. I would really appreciate if anyone can help.. Most probably the cause for why the debugger doesn't stop in the breakpoint you had configured is because at the start of the server these lines of code aren't called. You need to compile the version debug, press F7 for to compile the whole stuff, then change the path where the server will be linked for the same path you have already the mysql stuff e.g.: c:\mysql this will write the current server. Add as parameters start --console --standalone in the debug tab, then open the file mysqld.cpp, search for the function int main and in the first line create a break point, start the debugger with F5 and using the F11 key do the debug of the rest of the code. The debugger will stop in the point where the server waits for connection. Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index failing on large database - any ideas why?
I have a database with one 17GB table containing about 23 million customer records. The table has about 15 fields. I wanna index five of them to speed up searching. My indexing operation keeps failing with error:127 (record table has crashed). The process fails of different fields at different times; there is no consistencey in when the error occurs... I've tried this process on three seperate systems and had the same results. The last server was a Dual 3GHz Zeon with 6GB Ram and a 15K RPM SCSI RAID array on an LSI RAID Card running RedHat. The server prior to that was a Dual AMD Opteron with 4GB Ram running Windows 200 Server. The server prior to that was a single AMD Thunderbird 1GHz with 2GB Ram running Windows 2000 Pro. All the systems are getting the same error. Can anyone speculate as to what might be going on? Are there some things I should check, such as for certail characters that indexing can't handle within the table data? Are there some mysqld parameters I need to set when working with this much data? Are there compilation flags I should have set? I appreciate any help anyone may be able to provide. Thanks, Devi0s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Debugging MySql Source in Visual C++
Hi, Thanks for the info! I am already able to debug the server startup. I would like it to stop at someother point, perhaps a point where a SELECT statement is executed for example.. Thanks! -Original Message- From: Miguel Angel Solorzano [mailto:[EMAIL PROTECTED] Sent: Thursday, February 05, 2004 11:14 AM To: Ananth Raghuraman; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Debugging MySql Source in Visual C++ At 01:31 5/2/2004, Ananth Raghuraman wrote: Hi, Hi all, I am not part of the official MySql team. I am just a lone developer. I tried debugging MySql source (server) in Visual C++, but it doesn't seem to work. I followed the instructions in the MySql manual and added a few breakpoints at a few random places, but the debugger never stopped there. I would really appreciate if anyone can help.. Most probably the cause for why the debugger doesn't stop in the breakpoint you had configured is because at the start of the server these lines of code aren't called. You need to compile the version debug, press F7 for to compile the whole stuff, then change the path where the server will be linked for the same path you have already the mysql stuff e.g.: c:\mysql this will write the current server. Add as parameters start --console --standalone in the debug tab, then open the file mysqld.cpp, search for the function int main and in the first line create a break point, start the debugger with F5 and using the F11 key do the debug of the rest of the code. The debugger will stop in the point where the server waits for connection. Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column being created as varchar() when char() requested.
Philip Walden wrote: Michael Stassen wrote: You need supp_cd to be first in an index. Try ALTER TABLE hpi_supp_agmt ADD INDEX supp_ind (supp_cd); then try to add your foreign key constraint again. Michael Looks like that does not work. BTW this does work (without adding a separate index) in postgres. mysql alter table hpi_supp_agmt add index i1hpisuppagmt (supp_cd); Query OK, 28635 rows affected (4.89 sec) Records: 28635 Duplicates: 0 Warnings: 0 OK, now table hpi_supp_agmt has supp_cd first in the i1hpisuppagmt index, and table supp has supp_cd first in the p1supp primary key index, so supp_cd should now be a foreign key candidate. mysql alter table hpi_supp_agmt add constraint f1hpisuppagmt foreign key (hpi_no) references hpi; ERROR 1005 (HY000): Can't create table './gem/#sql-c3f_2.frm' (errno: 150) But here you try to add a foreign key constraint using hpi_no, which is not first in any index. And what is hpi? Did you try your original statement? alter table hpi_supp_agmt add constraint foreign key (supp_cd) references supp(supp_cd); I would expect this to work now. At least, it does for me. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Automate Mysql Replication
Hi, I am new to mysql managed to do the mysql replication, that works fine. Now can this whole process be automated. For example: 1) Asume that more that 2 mysql servers can be used for the replication, like A-B-C A--master B,C -- Slaves to A 2) Also in the scenario 1 is it possible to make the B as master C as slave to B incase A goes down? Can this scenario be automated? Any Ideas?? Regards T.Nagendra Prasad Professional Services OSI Technologies __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bash script to MySql
Hi, Am Donnerstag, 5. Februar 2004 03:43 schrieb Scott Haneda: on 02/04/2004 02:23 AM, Bernd Tannenbaum at [EMAIL PROTECTED] wrote: 2 possible ways to go: Feed a Textfile with your statement in the db: PATH/mysql db /sql_scripts/sql_statements_as_textfile --password=pass Give command directly In the shell-script: PATH/mysql --skip-column-names -e SELECT table SET blabla db --password=pass I need to select into outfile on this one, which poses a problem to me, I can not write the file to any directory as I get a permissions error since mysql is the owner of this file, how would I do this ? Dunno if i get you right. You want to write the stuff found by your SELECT into a file on your local machine but the user mysql does not have permission to write anywhere in your system? If thats the problem then you have few options i think. sudo would be a way to go. You could give root-rights to a single command of ur choice that way. Another thing is that you can store the value from the SELECT in a bash-variable if ya like. Like this: var=`PATH/mysql --skip-column-names -e SELECT MAX(id) FROM table db --password=pass`; This way you can keep the value in the shell and write it wherever you have the right to write to. Well, if mysql has no rights at all that won`t help u much i think. So if ya cannot change any rights sudo is the only option i know of but maybe its worth to ask some linux-freaks about it... Good luck, Bernd -- [Zufallssig 1] Windows found - Remove? [Y]es [S]ure [F]ine [O]K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with a SELECT statement across 3 tables
Dominique: Thanks for your suggestions/ideas. After playing with it for a little while longer of banging my head into a brick wall, I realized I was using a left join when I needed a right. You have my table structures pretty much down - here's the final SQL statement that I use to return 1 row per update per server that hasn't been applied: SELECT update_track.update_id, server.server_id, server.os, update_track.bugtraq_id FROM update_track LEFT JOIN server_update ON (update_track.update_id = server_update.update_id) RIGHT JOIN server ON (server_update.server_id = server.server_id) WHERE server.os = update_track.os AND server_update.server_id IS NULL; Which returns something easy to work with ( from a test set of 2 entries in the update_track table) +---+---+--++ | update_id | server_id | os | bugtraq_id | +---+---+--++ | 5 | 5 | Windows 2000 | 0 | | 5 | 7 | Windows 2000 | 0 | | 5 | 8 | Windows 2000 | 0 | | 5 |13 | Windows 2000 | 0 | | 5 |16 | Windows 2000 | 0 | | 5 |19 | Windows 2000 | 0 | | 5 |20 | Windows 2000 | 0 | | 5 |27 | Windows 2000 | 0 | | 5 |28 | Windows 2000 | 0 | | 5 |30 | Windows 2000 | 0 | | 5 |31 | Windows 2000 | 0 | | 5 |32 | Windows 2000 | 0 | | 5 |39 | Windows 2000 | 0 | | 5 |40 | Windows 2000 | 0 | | 5 |44 | Windows 2000 | 0 | | 5 |49 | Windows 2000 | 0 | | 5 |51 | Windows 2000 | 0 | | 4 |53 | RedHat 9 | 0 | | 5 |56 | Windows 2000 | 0 | | 5 | 104 | Windows 2000 | 0 | | 5 | 123 | Windows 2000 | 0 | | 4 | 532 | RedHat 9 | 0 | | 5 | 165 | Windows 2000 | 0 | | 4 | 295 | RedHat 9 | 0 | | 5 | 327 | Windows 2000 | 0 | | 5 | 361 | Windows 2000 | 0 | | 5 | 364 | Windows 2000 | 0 | | 5 | 388 | Windows 2000 | 0 | | 5 | 403 | Windows 2000 | 0 | | 5 | 405 | Windows 2000 | 0 | | 5 | 406 | Windows 2000 | 0 | | 5 | 407 | Windows 2000 | 0 | | 5 | 408 | Windows 2000 | 0 | | 5 | 424 | Windows 2000 | 0 | | 5 | 430 | Windows 2000 | 0 | | 5 | 455 | Windows 2000 | 0 | | 5 | 457 | Windows 2000 | 0 | | 4 | 467 | RedHat 9 | 0 | | 4 | 529 | RedHat 9 | 0 | | 4 | 512 | RedHat 9 | 0 | | 5 | 533 | Windows 2000 | 0 | | 5 | 554 | Windows 2000 | 0 | | 4 | 556 | RedHat 9 | 0 | | 4 | 558 | RedHat 9 | 0 | | 4 | 565 | RedHat 9 | 0 | | 4 | 575 | RedHat 9 | 0 | | 4 | 601 | RedHat 9 | 0 | | 5 | 614 | Windows 2000 | 0 | +---+---+--++ I think I should be able to claim SQL as a second language - you can say so much with it! Brandon Ewing -Original Message- From: Dominique Plante [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 4:43 PM To: 'Brandon Ewing' Subject: RE: Need help with a SELECT statement across 3 tables Brandon: I have been toying with your problem, and unfortunately, I have yet to come up with a good solution, since I am interested in seeing what the solution would be. Maybe you can confirm a few things. Do your table structures look anything like this? Server: | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | server_id | int(11) | | PRI | NULL| auto_increment | | location | varchar(30) | YES | | NULL|| | os| varchar(30) | YES | | NULL|| Server_update: | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | server_id | int(11) | | | 0 | | | update_id | int(11) | | | 0 | | | updateDateTime | timestamp | YES | | NULL| | Update_track: | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | update_id |
