help on creating missing ids
Hi all, I have a table shares that consists of company id, shareholder id, shareholder name. Company id and shareholder id are decimal digits; shareholder name is text. Due to some missings of my initial data, I'd like to create a unique id for the shareholder ids which are blank/null in the imported version and are the same for every shareholder that appears into couple of companies. So far I've got to: create table shares (company id not null, shareholder id not null, shareholder name not null) PRIMARY KEY ( shareholder name) Then I'd like to update the missing shareholder id with autoincrement and group them by shareholder name, but I'm not sure how to do it. I'm thinking of something like update autoincrement id where id is null group by shareholder name. Any straws/pointers are welcome. Thanks in advance. -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525
Re: non-blocking connect and EAGAIN
Hi, Dmitriy, Vladimir!
On 19 Sep 2007, at 07:40, Vladimir Shebordaev wrote:
Hi, Dmitriy,
would you please specify when you get those reconnects?
The Linux connect() system call on non-blocking AF_UNIX sockets
should return immediately with EAGAIN when the peer's backlog queue
is full.
Vladimir's right here. The Linux kernel doesn't normally send errno
EINPROGRESS, but it does send EAGAIN for this case:
if (skb_queue_len(other-sk_receive_queue)
other-sk_max_ack_backlog) {
err = -EAGAIN;
if (!timeo)
goto out_unlock;
timeo = unix_wait_for_peer(other, timeo);
err = sock_intr_errno(timeo);
if (signal_pending(current))
goto out;
sock_put(other);
goto restart;
}
Notably, the BSDs don't send EAGAIN, as far as I can tell.
Otherwise connect() will block until there is some room available
on receiving end. MySQL client intention is to literally follow
that system call when there is no timeout option explicitly
specified (see the comments in my_connect() right above the lines
you've cited). So, what you get looks like intended behavior from
both kernel and MySQL side.
Agreed, for the most part. (I don't know that the kernel sends
EAGAIN /only/ for no-timeout/non-blocking connect()ion attempts. I
didn't dig wider than the above.)
The Linux kernel truly couldn't accept the connect() syscall, and
this is a valid problem. The library code behaves correctly because
the library /should/ pass errors from the kernel up to the client.
This specific case isn't one I think we considered, but client code
should handle all errors the OS could generate; the library shouldn't
insulate the client from the kernel, but it should from the server.
Please check out the MySQL 5.0 trouble shooting page at http://
dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html.
You've probably got your server crashed or stalled due to some real
bug. If so, you should try to reproduce it and file a bug report.
But please upgrade to decent MySQL version first of all.
It could be a crashed server that's causing the problem, I suppose.
More likely, if it's not, please keep us included if there's another
bottleneck in connecting that you find.
- chad
Dmitriy MiksIr wrote:
Hello!
I got a lot of mysql errors Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock' (11).
I trace one of this error and see, what non-blocking connect
return EAGAIN. See:
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
connect(3, {sa_family=AF_FILE, path=/var/lib/mysql/mysql.sock},
110) = -1 EAGAIN (Resource temporarily unavailable)
Mysql's connect do not detect this error:
if ((res != 0) (s_err != EINPROGRESS))
{
errno= s_err; /* Restore it */
return(-1);
}
Is this kernel bug (Linux 2.6.16-std26-smp-alt1)?... which return
EAGAIN instead of EINPROGRESS, or some other troubles can force
EAGAIN on unix socket connect?
--
Chad Miller, Software Developer [EMAIL PROTECTED]
MySQL Inc., www.mysql.com
Orlando, Florida, USA13-20z, UTC-0400
Office: +1 408 213 6740 sip:[EMAIL PROTECTED]
PGP.sig
Description: This is a digitally signed message part
privileges
Hi everyone, Just getting into database stuff a little bit, and wanted to double check something with you guys. I have a database, which has 2 tables, I have created a user using this syntax: grant select, update, delete on dbname.table to 'me'@'localhost' identified by 'mypass'; then I also added access to another table: grant select on dbname.othertable to 'me'@'localhost' identified by 'mypass'; the other table is used to store local accounts to a online database, and my php script uses SELECT to check to see if they are listed and if so grant them access. No one is accessing the database directly except for me, and I would like to keep it that way. Given the user that I created, is there anything else I need to worry about? I only want local requests to be able to interface with the database. I don't need Joe Blow for indiana to have direct access :) Oh, and do I specifically have to disallow certain privileges if all I want them to be able to do is delete, select or update records? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
Re: Linking tables
Kevin, To link articles keywords wouldn't you want a table like this? CREATE table articles_keywords( AK_id int auto_increment PRIMARY KEY, AK_article_id int NOT NULL, AK_keyword_id int NOT NULL ); I don't understand why you want the sort of 'dynamic table' you describe. PB - Kevin Waterson wrote: Hi all, having a spot of bother with a 'keywords' table. I have a table of articles with an article_id and a table of categories with a category_id, all is well. These are linked via a article_link table so that an article can be a member of multiple categories. I then have a table of 'keywords' that will relate to each article. I wish to be able to SELECT all articles associated with the keywords. No issues so far. Next, I wish to be able to dynamically add a table to the database, eg: a 'cats' table. If a cat has a keyword of 'tiddles' associated with it. I would then like to be able to SELECT all related articles, that is, all records in the articles table, with the keyword 'tiddles'. MySQL 5 and the db layout looks like this so far. thanks for looking Kevin CREATE table articles ( article_id int(9) NOT NULL auto_increment, user_id int(9) default NULL, article_category_id int(11) NOT NULL, article_title varchar(50) NOT NULL, article_subject varchar(50) NOT NULL, article_blurb varchar(250) default NULL, article_body text, article_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, article_publish_date timestamp NOT NULL default '-00-00 00:00:00', article_orig_author varchar(50) NOT NULL, article_image longblob, PRIMARY KEY (article_id) ); CREATE table article_categories ( article_category_id int(11) NOT NULL auto_increment, article_category_name varchar(20) NOT NULL, article_category_left_node int(11) NOT NULL, arcitle_category_right_node int(11) NOT NULL, PRIMARY KEY (article_category_id) ); CREATE table article_links( article_link_id int(11) NOT NULL auto_increment, article_id int(11) NOT NULL, article_category int(11) NOT NULL, PRIMARY KEY (article_link_id) ); CREATE table keywords( keyword_id int(11) NOT NULL auto_increment, keyword_name char(20) NOT NULL, PRIMARY KEY (keyword_id) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple Regex Question
It's been a few years since I did any regex queries, but I swear I used to be able to do something like: SELECT fieldname FROM tablename WHERE field RLIKE '(^|\|)2(\||$)'; And that would find '2', with an optional '|' at the beginning, or at the beginning of the line, and an optional '|' or end of the line. So, it would match the following: 2|3 1|2|3 1|2 But not 1|20|3 Can someone give me a little guidance on this? I'm pulling my hair out on what should be a simple thing The database is currently in 4.1 but will soon be going to 5.0, so, ideally the solution will work in both... Thanks.
Slave Misbehavin'
Howdy, I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 5 to our system that currently has one master and one slave, slave1, running 4.0.24, and somehow slave2 somehow ends up with too many records in many of the 30 tables in the database. Steps taken: 1. Stopped new records from being inserted into the master, and confirmed with count(*)'s that both master and slave1 were in a static state. 2. Stopped mysqld and commented out in my.cnf the master connection parameters (user, host, password, port) on slave2. 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql data directory on slave2. 4. Deleted all .MYD, .MYI, and .frm files from the replication database directory on slave2. 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2. 6. Restarted mysqld on slave2 (now not running as a slave). 7. Confirmed that record counts were consistent across master, slave1 and slave2. 8. Stopped mysqld on slave2, uncommented master connection parameters in my.cnf, and restarted mysqld. 9. Got log file and log position parameters with 'show master status' on the master. 10. Ran 'Change master to... with all fields filled in. 11. Ran 'slave start' on slave2. 12. Rechecked record counts on slave2, and they were too large and out of sync with slave1 and master. I poked around in the data on slave2 and found a number of records had been duplicated, and that accounted for the higher record counts. After starting the application that inserts data into the master, I determined that new records are being inserted correctly into slave2. Seriously out of ideas here. Thanks, David
Re: Slave Misbehavin'
Hi, [EMAIL PROTECTED] wrote: Howdy, I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 5 to our system that currently has one master and one slave, slave1, running 4.0.24, and somehow slave2 somehow ends up with too many records in many of the 30 tables in the database. Steps taken: 1. Stopped new records from being inserted into the master, and confirmed with count(*)'s that both master and slave1 were in a static state. 2. Stopped mysqld and commented out in my.cnf the master connection parameters (user, host, password, port) on slave2. 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql data directory on slave2. 4. Deleted all .MYD, .MYI, and .frm files from the replication database directory on slave2. 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2. And in the meantime, slave1's data is being changed because the master is sending it replication events, no? You need to run STOP SLAVE on slave1 before rsyncing it. After STOP SLAVE, run SHOW SLAVE STATUS and record the output, then rsync, then START SLAVE on slave1 again. 6. Restarted mysqld on slave2 (now not running as a slave). 7. Confirmed that record counts were consistent across master, slave1 and slave2. 8. Stopped mysqld on slave2, uncommented master connection parameters in my.cnf, and restarted mysqld. 9. Got log file and log position parameters with 'show master status' on the master. TOO LATE. The horse has left the barn and you're closing the door behind it! You should instead get the replication coordinates from slave1 with SHOW SLAVE STATUS during step 5. You're cloning slave2 from slave1, so slave2 tells the truth, not the master, which has done a whole bunch of work while you were going through these steps. 10. Ran 'Change master to... with all fields filled in. 11. Ran 'slave start' on slave2. 12. Rechecked record counts on slave2, and they were too large and out of sync with slave1 and master. I poked around in the data on slave2 and found a number of records had been duplicated, and that accounted for the higher record counts. After starting the application that inserts data into the master, I determined that new records are being inserted correctly into slave2. Seriously out of ideas here. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote: Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. I just thought of a difference. If you are using LOCK TABLES and UNLOCK TABLES, these interact differently with COMMIT and ROLLBACK. More info: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Misbehavin'
Hi, [EMAIL PROTECTED] wrote: Howdy, I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 5 to our system that currently has one master and one slave, slave1, running 4.0.24, and somehow slave2 somehow ends up with too many records in many of the 30 tables in the database. Steps taken: 1. Stopped new records from being inserted into the master, and confirmed with count(*)'s that both master and slave1 were in a static state. 2. Stopped mysqld and commented out in my.cnf the master connection parameters (user, host, password, port) on slave2. 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql data directory on slave2. 4. Deleted all .MYD, .MYI, and .frm files from the replication database directory on slave2. 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2. - And in the meantime, slave1's data is being changed because the master - is sending it replication events, no? You need to run STOP SLAVE on - slave1 before rsyncing it. After STOP SLAVE, run SHOW SLAVE STATUS and - record the output, then rsync, then START SLAVE on slave1 again. I don't think so. I stopped all activities on the master (step 1), and slave1 therefore shouldn't have any changes made to it. I should have noted that only inserts are done on the master - no updates or deletes. 6. Restarted mysqld on slave2 (now not running as a slave). 7. Confirmed that record counts were consistent across master, slave1 and slave2. 8. Stopped mysqld on slave2, uncommented master connection parameters in my.cnf, and restarted mysqld. 9. Got log file and log position parameters with 'show master status' on the master. - TOO LATE. The horse has left the barn and you're closing the door - behind it! You should instead get the replication coordinates from - slave1 with SHOW SLAVE STATUS during step 5. You're cloning slave2 from - slave1, so slave2 tells the truth, not the master, which has done a - whole bunch of work while you were going through these steps. No, slave1 can't do any work except as directed by the master, which has had all activities stopped on it. 10. Ran 'Change master to... with all fields filled in. 11. Ran 'slave start' on slave2. 12. Rechecked record counts on slave2, and they were too large and out of sync with slave1 and master. I poked around in the data on slave2 and found a number of records had been duplicated, and that accounted for the higher record counts. After starting the application that inserts data into the master, I determined that new records are being inserted correctly into slave2. Seriously out of ideas here. Thanks, David
Query to find foo within (foo)
Hi. I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. I know I can create another condition within my query: firstname LIKE '(johnie)' OR firstname LIKE '(johnie%' OR firstname LIKE '%johnie)' but I also might consider other characters like ' - * Any suggestion? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to find foo within (foo)
At 19:34 +0200 19/9/07, thomas Armstrong wrote: I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. Well I'm hardly the world's greatest expert, but I'm curious as to why you're always separating '%' from 'johnie' with a space, because that way it will only find Johnie if he has a space before or after him or both. Hmmm... and why the double parentheses? In fact, why any parentheses at all? This oughta do it: SELECT friends.id FROM friends WHERE friends.firstname LIKE '%johnie%' ORDER BY friends.firstname LIMIT 0, That should find 'johnie' or 'Johnie' with absolutely any characters before and/or after him. ... and if you want to simplify your queries as much as possible you don't need to specify the table every time unless ambiguities might arise (which they only will if there's more than one table involved), so try: SELECT id FROM friends WHERE firstname LIKE '%johnie%' ORDER BY firstname LIMIT 0, ... and unless you've really got more than friends that limit clause is redundant too. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query to find foo within (foo)
You are putting a space between johnie and the % wildcards. That space is not ignored, it is part of the pattern. LIKE %johnie% will find every occurrence of johnie no matter what surrounds it. If you need something more complicated, such as only ignoring (, then you need to get more complicated. You might even need a regular expression. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: thomas Armstrong [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 19, 2007 1:35 PM To: mysql@lists.mysql.com Subject: Query to find foo within (foo) Hi. I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. I know I can create another condition within my query: firstname LIKE '(johnie)' OR firstname LIKE '(johnie%' OR firstname LIKE '%johnie)' but I also might consider other characters like ' - * Any suggestion? -- 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: Slave Misbehavin'
Maybe I missed this in the text below, but are you trying to daisy chain the slaves (master - slave 1 - slave 2) or have multiple slaves connecting to one master? Is slave 1 configured with log-slave-updates? Regards, Scott On Wed, 2007-09-19 at 12:31 -0500, [EMAIL PROTECTED] wrote: Hi, [EMAIL PROTECTED] wrote: Howdy, I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 5 to our system that currently has one master and one slave, slave1, running 4.0.24, and somehow slave2 somehow ends up with too many records in many of the 30 tables in the database. Steps taken: 1. Stopped new records from being inserted into the master, and confirmed with count(*)'s that both master and slave1 were in a static state. 2. Stopped mysqld and commented out in my.cnf the master connection parameters (user, host, password, port) on slave2. 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql data directory on slave2. 4. Deleted all .MYD, .MYI, and .frm files from the replication database directory on slave2. 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2. - And in the meantime, slave1's data is being changed because the master - is sending it replication events, no? You need to run STOP SLAVE on - slave1 before rsyncing it. After STOP SLAVE, run SHOW SLAVE STATUS and - record the output, then rsync, then START SLAVE on slave1 again. I don't think so. I stopped all activities on the master (step 1), and slave1 therefore shouldn't have any changes made to it. I should have noted that only inserts are done on the master - no updates or deletes. 6. Restarted mysqld on slave2 (now not running as a slave). 7. Confirmed that record counts were consistent across master, slave1 and slave2. 8. Stopped mysqld on slave2, uncommented master connection parameters in my.cnf, and restarted mysqld. 9. Got log file and log position parameters with 'show master status' on the master. - TOO LATE. The horse has left the barn and you're closing the door - behind it! You should instead get the replication coordinates from - slave1 with SHOW SLAVE STATUS during step 5. You're cloning slave2 from - slave1, so slave2 tells the truth, not the master, which has done a - whole bunch of work while you were going through these steps. No, slave1 can't do any work except as directed by the master, which has had all activities stopped on it. 10. Ran 'Change master to... with all fields filled in. 11. Ran 'slave start' on slave2. 12. Rechecked record counts on slave2, and they were too large and out of sync with slave1 and master. I poked around in the data on slave2 and found a number of records had been duplicated, and that accounted for the higher record counts. After starting the application that inserts data into the master, I determined that new records are being inserted correctly into slave2. Seriously out of ideas here. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X binary installer
Looking for the OS X 4.1 binary package installer, I can not seem to locate this on the site, I just find a source style, and a tar style. I am pretty sure in the past, I was able to have a double clickable installer, and it had a preference pane for starting and stopping the service. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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]
