mysql 5.6.10 won't start
Hi, I'm running mysql 5.6.10 on Fedora. when I try and boot mysql I get this: # /etc/init.d/mysql start Starting MySQL.The server quit without updating PID file (/[FAILED]xt/mysql/veritian.pid). Any ideas? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
query running very slow, need a little help
Hi, I'm running this query: mysql SELECT email FROM promoters where id NOT IN (SELECT promoter_id FROM credits WHERE success = 1 ) and active = 1; Empty set (31.89 sec) its returning an empty set and take over 30 seconds to return. mysql describe promoters; +---+--+--+-+---++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---++ | id| int(11) unsigned | NO | PRI | NULL | auto_increment | | company_name | varchar(40) | YES | | NULL || | first_name| varchar(40) | YES | | NULL || | last_name | varchar(40) | YES | | NULL || | address | varchar(40) | YES | | NULL || | zip | varchar(10) | YES | | NULL || | city | varchar(40) | YES | | NULL || | country | varchar(40) | YES | | NULL || | phone | varchar(20) | YES | | NULL || | email | varchar(100) | YES | UNI | NULL || | website | varchar(100) | YES | | NULL || | payments_id | varchar(10) | YES | MUL | NULL || | password | varchar(100) | YES | | NULL || | active| tinyint(1) | YES | MUL | NULL || | activation_key| varchar(50) | YES | | NULL || | new_email | varchar(100) | YES | | NULL || | new_email_activation_key | varchar(50) | YES | | NULL || | registered| timestamp| YES | | CURRENT_TIMESTAMP || | referral | int(10) unsigned | YES | | NULL || | whitelabel_beginner_modus | tinyint(1) | YES | | 1 || +---+--+--+-+---++ 20 rows in set (0.00 sec) mysql describe credits; ++-+--+-+---++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+---++ | id | int(11) unsigned| NO | PRI | NULL | auto_increment | | type | tinyint(1) unsigned | NO | | NULL || | credits| int(11) | YES | | NULL || | success| tinyint(1) | YES | MUL | NULL || | profit | float | NO | | NULL || | price | float | NO | | NULL || | date | timestamp | NO | MUL | CURRENT_TIMESTAMP || | user_id| int(11) unsigned| NO | | NULL || | promoter_id| int(10) unsigned| YES | MUL | NULL || | referrer | varchar(10) | YES | | NULL || | domain_id | int(11) unsigned| NO | | NULL || | string | varchar(100)| YES | | NULL || | client_info| varchar(200)| YES | | NULL || | promoter_paid | tinyint(1) | YES | | NULL || | status | tinyint(4) | YES | | NULL || | seconds| int(11) | YES | | NULL || | transaction_id | varchar(16) | YES | | NULL || ++-+--+-+---++ 17 rows in set (0.00 sec) Any ideas as to why the wuery is taking so long?? With kind regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Optimize query help.
Dear all, I have a query that takes a rather long time and was wondering if there is anyway to optimize it. Normally we removing duplicate records by phone number. This query takes about a second and it really slows down the process when we are importing several 1000 records a day. Here is the query: SELECT count(id) c FROM leads WHERE (phone_home = '(770) 512-8990' OR phone_work = '(770) 512-8990' OR phone_other = '(770) 512-8990' OR phone_mobile = '(770) 512-8990' OR phone_fax = '(770) 512-8990') AND date_entered DATE_SUB(NOW(),INTERVAL 45 DAY) AND deleted != '1'; This is the describe: DESCRIBE SELECT count( id ) c FROM leads WHERE ( phone_home = '(770) 512-8990' OR phone_work = '(770) 512-8990' OR phone_other = '(770) 512-8990' OR phone_mobile = '(770) 512-8990' OR phone_fax = '(770) 512-8990') AND date_entered DATE_SUB( NOW( ) , INTERVAL 45 DAY ) AND deleted != '1' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE leads ALL idx_del_user,phone_home,phone_mobile,phone_work,phone_other,phone_fax,date_entered NULL NULL NULL 636433 Using where Any thoughts? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slave using SSL?
Hey can anyone tell me is my slave is utilizing SSL? I am unsure why the Master SSL Cipher is not listed under show slave status. Here's some info: mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: host Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.38 Read_Master_Log_Pos: 74709312 Relay_Log_File: slave-relay.000577 Relay_Log_Pos: 8579562 Relay_Master_Log_File: mysql-bin.38 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: crm Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 74709312 Relay_Log_Space: 8579562 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem Master_SSL_CA_Path: /etc/mysql/ssl/ Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/client-key.pem Seconds_Behind_Master: 0 1 row in set (0.00 sec) mysql SHOW VARIABLES LIKE 'have_ssl'; +---+---+ | Variable_name | Value | +---+---+ | have_ssl | YES | +---+---+ 1 row in set (0.01 sec) mysql SHOW STATUS LIKE 'Ssl_cipher'; +---++ | Variable_name | Value | +---++ | Ssl_cipher| DHE-RSA-AES256-SHA | +---++ 1 row in set (0.00 sec) Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL replication SSL
Dear All, I'm trying to get SSL connections for all mysql slave and masters. I have one box that will not use SSL for some reason. When I start this slave it can not connect because it's not using SSL. show slave status\G *** 1. row *** Slave_IO_State: Connecting to master Master_Host: myhost Master_User: rep_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.95 Read_Master_Log_Pos: 1095 Relay_Log_File: slave-relay.04 Relay_Log_Pos: 98 Relay_Master_Log_File: mysql-bin.95 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: crm Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1095 Relay_Log_Space: 98 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No --- Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Here is a portion of my.cnf. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 max_connections=500 max_connect_errors = 0 # replication server-id = 1 replicate-same-server-id = 0 auto-increment-increment =3 auto-increment-offset =1 master-host = myHost master-user = rep_user master-password = rep_passwd master-connect-retry = 60 replicate-do-db = crm log-bin= /var/log/mysql/mysql-bin.log binlog-do-db = crm relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M # end replication # SSL for replication ssl ssl-key=/etc/mysql/ssl/server-key.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-capath=/etc/mysql/ssl/ ssl-cipher=DHE-RSA-AES256-SHA [client] ssl port = 3306 socket = /var/lib/mysql/mysql.sock ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-cipher=DHE-RSA-AES256-SHA Anyone see any issues with this? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Verify replication slave is using SSL
Dear All, I just want to verify my rep slave is using ssl. When I run this command mysql \s I get: mysql \s -- mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 5 Current database: Current user: r...@localhost SSL:Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.0.77-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 9 min 48 sec And I can see the Cipher. But when I run a show slave status I don't see a cipher key: mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: my.sql.host Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.36 Read_Master_Log_Pos: 186677 Relay_Log_File: slave-relay.74 Relay_Log_Pos: 186814 Relay_Master_Log_File: mysql-bin.36 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: sugarcrm Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 186677 Relay_Log_Space: 186814 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem Master_SSL_CA_Path: /etc/mysql/ssl/ Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/client-key.pem Seconds_Behind_Master: 0 1 row in set (0.00 sec) Can any tell me if SSL is functioning or not please? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: REGEXP help Finding phone numbers (nnn) nnn-nnnn format SOLVED
This seems to do it:
SELECT phone_work FROM leads WHERE phone_work REGEXP '[(]{1}([0-9]){3}[)]{1}[
]?([^0-1]){1}([0-9]){2}[ ]?[-]?[ ]?([0-9]){4}'
- Original Message
From: Paul Nowosielski [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, December 3, 2008 2:39:54 PM
Subject: REGEXP help Finding phone numbers (nnn) nnn- format
Hi,
Please, can anyone lend a hand in helping pullout
phone numbers from the DB that only match
the format (nnn) nnn- ?
SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?'
I've been trying to lick this for hours now with
no avail.
Thank you,
Paul
--
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]
REGEXP help Finding phone numbers (nnn) nnn-nnnn format
Hi, Please, can anyone lend a hand in helping pullout phone numbers from the DB that only match the format (nnn) nnn- ? SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?' I've been trying to lick this for hours now with no avail. Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
regular expressions matching only numeric characters in order
Dear All, I'm trying to create a regular expression query to match phone numbers in a database field. My issue is this , the numbers have no set standard for input in the db. So the number in the db could be in multiple formats. EX: 333.333. (333)333- 333-333- 33 So I am wanting to find matches and ignore any non-numeric values and need some help with the expression. The query would be something like: SELECT tel FROM leads WHERE do_not_call = '1' AND tel REGEXP 'the expression' The number I am looking for will already have all the non numerics stripped and will be in the format of nn. Any help would be appreciated. Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: regular expressions matching only numeric characters in order
Hi, Seems to work! But the number is a zero you have to add an extra escape: (\\0+\3+\\0+\9+) or you get ERROR 1139 (42000): Got error 'parentheses not balanced' from regexp Thank you!! Paul - Original Message From: Bartis, Robert M (Bob) [EMAIL PROTECTED] To: Paul Nowosielski [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, December 1, 2008 6:42:19 PM Subject: RE: regular expressions matching only numeric characters in order Hi I am a bit of novice at Regexp, but I believe this will work for you (\d+\d+\d+).*(\d+\d+\d+).*(\d+\d+\d+\d+) Robert M. Bartis EMAIL: [EMAIL PROTECTED] -Original Message- From: Paul Nowosielski [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 3:32 AM To: mysql@lists.mysql.com Subject: regular expressions matching only numeric characters in order Dear All, I'm trying to create a regular expression query to match phone numbers in a database field. My issue is this , the numbers have no set standard for input in the db. So the number in the db could be in multiple formats. EX: 333.333. (333)333- 333-333- 33 So I am wanting to find matches and ignore any non-numeric values and need some help with the expression. The query would be something like: SELECT tel FROM leads WHERE do_not_call = '1' AND tel REGEXP 'the expression' The number I am looking for will already have all the non numerics stripped and will be in the format of nn. Any help would be appreciated. Thank you, Paul -- 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]
sub query help
Dear All,
I am interested in performing a sub query that removes duplicate records from
a temporary table prior to pushing the data to the main table.
I am not sure if it is possible and thought I would ask prior to the endeavor.
I currently use php to perform this operation but is really bogs down the db.
Here is the code example i am using now to perform this function:
// select the name from the dedup row
$sql = select name,id from dedup;
$ret = run_query($sql);
// loop through and check if the name exists in the lead table
while($ddRow=mysql_fetch_assoc($ret)){
$sql2 = select name,id from leads where name LIKE '$ddupRow[name]';
$ret2 = run_query($sql2);
// if rows returned is greater than 0 delete from the dedup table
if(myqsl_num_rows( $ret2 ) 0){
$del_sql = DELETE FROM dedup WHERE id = '$ddRow[id]';
run_query($del_sql);
// else insert the new record
}else{
$iQ=INSERT into leads (name) VALUES ( '$ddupRow[name]');
; run_query($iQ);
}
}
I am wondering if it is possible to do this with one query without the double
hit to the DB?
Or ,if possible, perform one large query and remove the duplicates in one blow?
Any thought or suggestions would be very much appreciated.
Kind Regards,
Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pulling Percentages From Query
Dear All, I'm trying to pull percentages from a query and really don't understand how to initiate this. Here is the low down: I have a list of venues with the number of acts who performed catoragized by genre. For example: venue_id: 23 blues: 117 rock: 34 world_music:57 ( and so on, there are 24 genres listed.) So my question would be how can I write a query to spit out a result set like: venue_id:24 blues:34(%) rock:12(%) world music:3(%) comedy: 0(%) Is this possible? here is the table structure: venue_idint(6) PRI 0 Actor int(6) 0 Adult Contemporary int(6) 0 Alternative int(6) 0 Blues int(6) 0 Children's Entertainmentint(6) 0 Christian int(6) 0 Classic Rockint(6) 0 Classical int(6) 0 Comedy int(6) 0 Country int(6) 0 Dance int(6) 0 Folkint(6) 0 Holiday int(6) 0 Jazzint(6) 0 Latin int(6) 0 Lecturers Speakersint(6) 0 Magicianint(6) 0 Reggae int(6) 0 Rockint(6) 0 Sports/Athleticsint(6) 0 Urban int(6) 0 Variety int(6) 0 World Music int(6) 0 Any help would really be appreciated! Best, -- Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching through an alphabetical range
Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching through an alphabetical range
I have the solution: SELECT DISTINCT email_address FROM mailing_list WHERE last_name = 'm' AND last_name = 'z' Thank you, -- Paul Nowosielski Webmaster On Friday 21 July 2006 11:03, Paul Nowosielski wrote: Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching through an alphabetical range
Its working fine for me: mysql SELECT DISTINCT email_address FROM mailing_list WHERE marketing_list LIKE 'Y' AND last_name = 'm' AND last_name = 'z'; 26371 rows in set (1.00 sec) Thank You, -- Paul Nowosielski Webmaster On Friday 21 July 2006 12:46, Tim Lucia wrote: Not quite... mysql select 'zebra' = 'm' AND 'zebra' = 'z'; +---+ | 'zebra' = 'm' AND 'zebra' = 'z' | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select 'seal' = 'm' AND 'seal' = 'z'; +-+ | 'seal' = 'm' AND 'seal' = 'z' | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql 'zz' will probably do the trick, though. Tim -Original Message- From: Paul Nowosielski [mailto:[EMAIL PROTECTED] Sent: Friday, July 21, 2006 2:10 PM To: mysql@lists.mysql.com Subject: Re: Searching through an alphabetical range I have the solution: SELECT DISTINCT email_address FROM mailing_list WHERE last_name = 'm' AND last_name = 'z' Thank you, -- Paul Nowosielski Webmaster On Friday 21 July 2006 11:03, Paul Nowosielski wrote: Dear All, I need to write a query that searches last names between the ranges of m through z. Is there a way to do this in the query? Thank You, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left Join Help
Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Paul Nowosielski Webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help SOLVED
Thank you all so much for your help, here is my solution: (I'm sure I can do a little more optimization) SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td JOIN tbl_ARTST as art ON (art.PKEY = artd.artist_id) JOIN artist_tourdate artd ON (artd.artist_id = art.PKEY) JOIN tbl_VENUES tv ON (td.venue_id = tv.ID) LEFT JOIN tbl_VENUE_CAPACITY tvc ON (tvc.VENUE_ID = tv.ID) LEFT JOIN tbl_VENUE_AGE_XREF tvax ON (tvax.VENUE_ID = tv.ID) LEFT JOIN tbl_VENUE_AGES tvage ON (tvage.PKEY = tvax.VENUE_ID) LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) 45929 rows in set (3 min 11.75 sec) Best Regards, -- Paul Nowosielski Webmaster On Friday 23 June 2006 14:10, Gerald L. Clark wrote: I ammend my previous post. Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage Use INNER_JOIN and use ON clauses. tvc, tvax, and tvage are not joined at all, producing Cartesian Products. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) This on condition does not include the table being joined. WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+ -++---+--+ | table | type | possible_keys | key | key_len | | ref| rows | Extra| +++---+-+ -++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | | NULL | 4 | Using index; Distinct| +++---+-+ -++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system
Join help
Dear All, I'm working on a database that has a lot of inconsistencies. I have a large query that pulls artist and venue information. My problem is this: I'm trying to create a data feed that lists artists tour dates, and the relation venue information corresponding to that tour date. Unfortunately the data is inconsistent. For example I have an artist thats on tour at a specific venue. I am supposed to also give the venue format information but some venues do not have this relation. Therefore these artist tour dates would not be list in the query return. Is there a join method that can ignore the fact that there is no venue information and just populate the fields as blank? Here is my query so far: select DISTINCT (t.td_id),ta.NAME as artist,ta.PKEY as artist_id, hta.NAME as headliner, DATE_FORMAT(t.td_date, '%b %e %Y') as start_date, tv.ADDR1, tv.ADDR2, tv.NAME as venue, tv.CITY, tv.STATE, tv.COUNTRY,tv.ID as venID,ta.WEB as artlink,tv.WEBSITE as venweb,vcp.SIZE as vensize,vtp.TYPE as ventype, vage.TYPE as venage ,tv.TICKETAGNCY1 as ticket1,tv.TICKETAGNCY2 as ticket2 from tbl_VENUE_TYPE vtp, tbl_VENUE_CAPACITY vcp, tourdates t, tbl_VENUES tv, tbl_ARTST ta, artist_tourdate at, tbl_ARTST hta, artist_tourdate hat,tbl_VENUE_TYPE_XREF tvtx,tbl_VENUE_AGE_XREF vax, tbl_VENUE_AGES vage where t.td_id = at.td_id and at.artist_id = ta.PKEY and t.venue_id = tv.ID and hat.headliner=1 and t.td_id = hat.td_id and hat.artist_id = hta.PKEY and vcp.VENUE_ID = tv.ID and tv.id =tvtx.VENUE_ID AND tvtx.TYPE_ID = vtp.PKEY and vax.VENUE_ID =tv.ID AND vax.AGE_ID = vage.PKEY AND UNIX_TIMESTAMP(t.td_date) $time GROUP BY t.td_id Thanks for the help! -- Paul Nowosielski -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup and recovery problems
Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and recovery problems
On Thursday 25 May 2006 12:09, you wrote: Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? Ok this is very strange. I checked the permissions on the data directory and they where set to 660(rw rw X) and owned by mysql. I became the mysql user and listed the data/mysql directory: [EMAIL PROTECTED]:/usr/local/src/mysql_current/data ll mysql/ and I got: /bin/ls: mysql/procs_priv.MYD: Permission denied /bin/ls: mysql/procs_priv.MYI: Permission denied /bin/ls: mysql/procs_priv.frm: Permission denied /bin/ls: mysql/help_keyword.MYD: Permission denied /bin/ls: mysql/help_keyword.MYI: Permission denied /bin/ls: mysql/help_keyword.frm: Permission denied /bin/ls: mysql/func.MYD: Permission denied /bin/ls: mysql/func.MYI: Permission denied /bin/ls: mysql/func.frm: Permission denied /bin/ls: mysql/columns_priv.MYD: Permission denied /bin/ls: mysql/columns_priv.MYI: Permission denied /bin/ls: mysql/columns_priv.frm: Permission denied /bin/ls: mysql/tables_priv.MYD: Permission denied /bin/ls: mysql/tables_priv.MYI: Permission denied /bin/ls: mysql/tables_priv.frm: Permission denied /bin/ls: mysql/help_topic.MYD: Permission denied /bin/ls: mysql/help_topic.MYI: Permission denied /bin/ls: mysql/help_topic.frm: Permission denied /bin/ls: mysql/time_zone_transition_type.MYD: Permission denied /bin/ls: mysql/time_zone_transition_type.MYI: Permission denied /bin/ls: mysql/time_zone_transition_type.frm: Permission denied /bin/ls: mysql/time_zone_leap_second.MYD: Permission denied /bin/ls: mysql/time_zone_leap_second.MYI: Permission denied /bin/ls: mysql/time_zone_leap_second.frm: Permission denied /bin/ls: mysql/db.MYD: Permission denied /bin/ls: mysql/db.MYI: Permission denied /bin/ls: mysql/db.frm: Permission denied /bin/ls: mysql/host.MYD: Permission denied /bin/ls: mysql/host.MYI: Permission denied /bin/ls: mysql/host.frm: Permission denied /bin/ls: mysql/help_relation.MYD: Permission denied /bin/ls: mysql/help_relation.MYI: Permission denied /bin/ls: mysql/help_relation.frm: Permission denied /bin/ls: mysql/time_zone.MYD: Permission denied /bin/ls: mysql/time_zone.MYI: Permission denied /bin/ls: mysql/time_zone.frm: Permission denied /bin/ls: mysql/proc.MYD: Permission denied /bin/ls: mysql/proc.MYI: Permission denied /bin/ls: mysql/proc.frm: Permission denied /bin/ls: mysql/user.MYD: Permission denied /bin/ls: mysql/user.MYI: Permission denied /bin/ls: mysql
Re: Backup and recovery problems
Gerald, Thank you that worked. now I'm receiving this error: dev:/tmp # /usr/local/mysql/bin/mysqldump -u root -p -h 192.168.45.7 --force --all-databases all.sql Enter password: /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `help_category` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `help_keyword` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `help_topic` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `proc` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `procs_priv` (Can't open file: 'procs_priv.MYI'. (errno: 138)) /usr/local/mysql/bin/mysqldump: Can't get CREATE TABLE for table `time_zone_name` (File '/usr/local/src/mysql_current/share/mysql/charsets/?.conf' not found (Errcode: 2)) Any ideas? Thank you, -- Paul Nowosielski Webmaster office: 303.440.0666 ext 219 cel: 303.827.4257 On Thursday 25 May 2006 13:24, gerald_clark wrote: Paul Nowosielski wrote: On Thursday 25 May 2006 12:09, you wrote: Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? Ok this is very strange. I checked the permissions on the data directory and they where set to 660(rw rw X) and owned by mysql. Which is wrong. You need 770 or you have no execute (search) privilege on the directory. I became the mysql user and listed the data/mysql directory: [EMAIL PROTECTED]:/usr/local/src/mysql_current/data ll mysql/ and I got: /bin/ls: mysql/procs_priv.MYD: Permission denied /bin/ls: mysql/procs_priv.MYI: Permission denied /bin/ls: mysql/procs_priv.frm: Permission denied /bin/ls: mysql/help_keyword.MYD: Permission denied /bin/ls: mysql/help_keyword.MYI: Permission denied /bin/ls: mysql/help_keyword.frm: Permission denied /bin/ls: mysql/func.MYD: Permission denied /bin/ls: mysql/func.MYI: Permission denied /bin/ls: mysql/func.frm: Permission denied /bin/ls: mysql/columns_priv.MYD: Permission denied /bin/ls: mysql/columns_priv.MYI: Permission denied /bin/ls: mysql/columns_priv.frm: Permission denied
Problem using IN statement MySQL 5
Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- Paul Nowosielski Webmaster 2401 Broadway St Boulder, Co 80304 Tel: 303.440.0666 ext:219 Cell: 303.827.4257 www.celebrityaccess.com www.protouronline.com www.boxofficenetwork.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
