mysql update
I have the following update procedure that update mySQL DB over the internet between source Linux Centos (local machine on my net behind a DMZ with real IP A.B.C.D) and target Linux fedora (web server www.myweb.com) every day on a specific time 18:00 through a crontab on my source linux server server(source) ---DMZ---ASA---Router-InternetHostingCompany---Myweb(target) [r...@source]# mysql -u updatex -p -h www.myweb.com test < sample.SQL [r...@source]$ mysql -u updatex -p -h www.myweb.com test < sample.SQL Enter password: * CURTIME() 19:41:44 CURTIME() 19:50:09 [r...@source]$ mysql -u updatex -p -h www.myweb.com test < sample.SQL Enter password:* CURTIME() 08:26:08 CURTIME() 08:26:34 I did the above procedure multiple times in different times in the day. the duration of this procedure takes from 22sec to 10min see above, before a while it was running constant with duration of 30sec. I checked with my ISP, hosting company and network nothing been changed from the structure/configuration. [r...@source]# lsof -i -P | grep 3306 mysqld 3806 mysql 11u IPv4 10926 TCP *:3306 (LISTEN) mysql 15150user3u IPv4 297528 TCP 192.168.10.5:8376->www.myweb.com:3306 (ESTABLISHED) [r...@target]# netstat -a |grep mysql tcp0 0 *:mysql *:* LISTEN tcp0 0 www.myweb.:mysql A.B.C.D:8366 TIME_WAIT tcp0 11 www.myweb.:mysql A.B.C.D:8372 ESTABLISHED also i attached tcp connection between the nodes as above from source and target, can any one help why i have this behavior and how can i fix the delay, thinking doing QoS or clean up and remoteexcution at that time ... Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Record old passwords ?
As an auditor once told me, "If you can do your job, then I'm not doing my job." 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Best way to synchronize two database schemas
Exactly what Johan said. I keep structure like so: develo...@mypse /var/www/dart2/UPDATES $ ll -rw-rw-rw- 1 developer developer 551097 2009-12-22 23:16 airports_city_country.sql drwxrwxrwx 2 developer developer 4096 2010-01-21 04:51 CVS -rw-rw-rw- 1 developer developer 3063 2009-07-15 01:40 fix_airports.php -rw-r--r-- 1 developer developer 23414 2010-01-21 03:52 ps_access_to_mysql.sql -rw-rw-rw- 1 developer developer 12259 2010-01-06 05:22 UPDATES.sql Any and all changes to DB schema are in the UPDATES/UPDATES.sql file and each are commented with the date of the change, who did it, and why. This file/dir is part of your repository, so as each developer checks out, they would run the appropriate part of the script as well. I've not yet found a good (and safe) way to automate this process. /* 2009-06-01 [dv] fix the privileges for various users as they were all whacked out * http://dev.mysql.com/doc/refman/5.0/en/grant.html */ REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'foo'@'10.10.10.%'; ... If you're using Subversion, you may find my "Subversion Flagged Update" script helpful... http://daevid.com/content/examples/snippets.php I also can vouche for SQLYog. Aside from being the absolute BEST mySQL GUI I've ever used, it has a feature to create the schema differences between two live databases. I've used it before to get a DEV and PROD server in sync, so that I could then implement the above methodology. > -Original Message- > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On > Behalf Of Johan De Meersman > Sent: Thursday, January 21, 2010 10:35 AM > To: Price, Randall > Cc: mysql@lists.mysql.com > Subject: Re: Best way to synchronize two database schemas > > The best way is to keep track of all individual changes to your > staging environment, including fire-and-forget style scripts; and > apply those to your production environment as needed. This is part of > the process of change management, and generally a very good idea :-) > > Lacking that, there are several tools that can generate a differential > script to do exactly this. I don't really use them, but I seem to > remember that SQLyog and some expensive but excellent Quest tool could > do it. > > On 1/21/10, Price, Randall wrote: > > I have a two databases, one in a production environment > (let's call it > > db_prod) and the other in a testing environments (Let's > call it db_test). > > > > What is the best way to synchronize the database schemas? > db_test has had a > > few indexes and constraints added to several tables and I > need to generate a > > MySQL script to apply these changes to db_prod. So > basically I want to dump > > the schemas of the two database, compare, and generate the > necessary script > > to apply to db_prod. > > > > Thanks, > > Randall Price > > > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > -- 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 and unicode weirdness
On 1/21/10 10:27 AM, "John Campbell" wrote: > I want to find rows that contain a word that matches a term, accent > insensitive: I am using utf8-general collation everywhere. > > attempt 1: > SELECT * FROM t WHERE txt LIKE '%que%' > Matches que qué, but also matches 'queue' > > attempt 1.5: > SELECT * FROM t WHERE txt LIKE '% que %' OR LIKE 'que %' OR LIKE '% que'; > Almost, but misses "que!" or 'que...' > > attempt2: > SELECT * FROM t WHERE txt REGEXP '[[:<:]]que[[:>:]]' > Matches que, not queue, but doesn't match qué. > > attempt3 > SELECT * FROM t WHERE txt REGEXP > '[[:<:]]q[uùúûüũūŭůűųǔǖǘǚǜ][eèéêëēĕėęě][[:>:]]' > Matches que, queue, qué. (I have no idea why this matches queue, but > the Regex behavior is bizarre with unicode.) > > Does anyone know why the final regex acts weird? "Warning "The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal." -- Mysql 11.4.2 > It there a good solution? doesn't look like it. Sphinxsearch might work nicely for you (it does for me) but that may not be an option for you. i generated a Sphinxsearch charset_table config that mimics utf8_general_ci collation. -- 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 and unicode weirdness
On Jan 21, 2010, at 9:27 AM, John Campbell wrote: > I want to find rows that contain a word that matches a term, accent > insensitive: I am using utf8-general collation everywhere. > > attempt 1: > SELECT * FROM t WHERE txt LIKE '%que%' > Matches que qué, but also matches 'queue' > > attempt 1.5: > SELECT * FROM t WHERE txt LIKE '% que %' OR LIKE 'que %' OR LIKE '% que'; > Almost, but misses "que!" or 'que...' > > attempt2: > SELECT * FROM t WHERE txt REGEXP '[[:<:]]que[[:>:]]' > Matches que, not queue, but doesn't match qué. > > attempt3 > SELECT * FROM t WHERE txt REGEXP > '[[:<:]]q[uùúûüũūŭůűųǔǖǘǚǜ][eèéêëēĕėęě][[:>:]]' > Matches que, queue, qué. (I have no idea why this matches queue, but > the Regex behavior is bizarre with unicode.) > > Does anyone know why the final regex acts weird? It there a good solution? http://dev.mysql.com/doc/refman/5.1/en/regexp.html: Warning The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to synchronize two database schemas
The best way is to keep track of all individual changes to your staging environment, including fire-and-forget style scripts; and apply those to your production environment as needed. This is part of the process of change management, and generally a very good idea :-) Lacking that, there are several tools that can generate a differential script to do exactly this. I don't really use them, but I seem to remember that SQLyog and some expensive but excellent Quest tool could do it. On 1/21/10, Price, Randall wrote: > I have a two databases, one in a production environment (let's call it > db_prod) and the other in a testing environments (Let's call it db_test). > > What is the best way to synchronize the database schemas? db_test has had a > few indexes and constraints added to several tables and I need to generate a > MySQL script to apply these changes to db_prod. So basically I want to dump > the schemas of the two database, compare, and generate the necessary script > to apply to db_prod. > > Thanks, > Randall Price > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best way to synchronize two database schemas
I have a two databases, one in a production environment (let's call it db_prod) and the other in a testing environments (Let's call it db_test). What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to generate a MySQL script to apply these changes to db_prod. So basically I want to dump the schemas of the two database, compare, and generate the necessary script to apply to db_prod. Thanks, Randall Price
Re: Selecting, Inserting and Deleting data
At 06:15 AM 1/21/2010, Krishna Chandra Prajapati wrote: Hi Abhishek. insert, select and delete are in the same proportion. 1. Inserted data into a table A by user. 2. Selecting data from table A inserting data to table B after applying some rules(update). 3. Deleting data from table A. 4. Selecting data from table B using some conditions (SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for sending sms. 5. Deleting the selected data from table B. With the above scenario, i am not able to user concurrent connections. Other wise it will send duplicate sms. Thanks, Kishna Kishna, If the table A and B are not doing Updates, then there is a solution using MyISAM tables. :-) 1) Start by optimizing the tables to remove any deleted rows (holes) in the table. Call this table "A". Do the same for Table "B". 2) Create a third table "DA" that contains one column, the Rcd_Id of the rows in table A that needs deleting. You can create table "DB" with the rcd_id of the deleted rows from table B. Of course tables DA and DB have an index on this Rcd_Id column. 3) When you start the DA and DB tables are empty and optimized (no holes) 4) Instead of deleting rows from table A, you add its Rcd_id to table DA. Same with table B and DB. 5) Your Select statements on table A and table B will do a Left Join to DA and DB respectively as in: select ... from A left join DA on A.Rcd_Id=DA.Rcd_Id where DA.Rcd_Id is NULL or select ... from B left join DB on B.Rcd_Id=DB.Rcd_Id where DB.Rcd_Id is NULL Since DA.Rcd_Id and Db.Rcd_id are indexed, this will be quite fast. Make them a memory table if you like. Now what makes this work is MyISAM tables will NOT issue a lock when inserting rows on an optimized table! This goes for both table A and DA. Once a day you will delete the deleted rows from Table A using DA. You may be able to reduce this time by taking advantage of the ability to rename multiple tables at one time. See http://dev.mysql.com/doc/refman/5.0/en/rename-table.html so you ... ... can create a duplicate empty tables using: create table DUPA like A; insert into DUPA select * from A left join DA on A.Rcd_Id=DA.Rcd_Id where DA.Rcd_Id is null; create table DUPDA like DA; create table DUPB like B;insert into DUPB select * from A left join DA on B.Rcd_Id=DB.Rcd_Id where DB.Rcd_Id is null; create table DUPDB like DB; drop table if exists DupA, DupDA, DupB, DupDB, OldA, OldB; Now in one statement execute: rename table A to OldA, DUPA to A, DA to OldDA, DupDA to DA, B to OldB, DUPB to B, DB to OldDB, DupDB to DB; This is atomic so all tables get renamed at once using one lock. Now you have to copy the few rows that were inserted into A after DupA was created and before the tables were renamed; set @MaxRcdId := select max(Rcd_Id) from A; Insert into A select * from OldA where Rcd_Id>@MaxRcdId; Now you have to copy the few rows that were inserted into B after DupB was created and before the tables were renamed; set @MaxRcdId := select max(Rcd_Id) from B; Insert into B select * from OldB where Rcd_Id>@MaxRcdId; This theoretically should work. I've done this from the top of my head so there may be syntax errors. This should get you on the right road. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
REGEXP and unicode weirdness
I want to find rows that contain a word that matches a term, accent insensitive: I am using utf8-general collation everywhere. attempt 1: SELECT * FROM t WHERE txt LIKE '%que%' Matches que qué, but also matches 'queue' attempt 1.5: SELECT * FROM t WHERE txt LIKE '% que %' OR LIKE 'que %' OR LIKE '% que'; Almost, but misses "que!" or 'que...' attempt2: SELECT * FROM t WHERE txt REGEXP '[[:<:]]que[[:>:]]' Matches que, not queue, but doesn't match qué. attempt3 SELECT * FROM t WHERE txt REGEXP '[[:<:]]q[uùúûüũūŭůűųǔǖǘǚǜ][eèéêëēĕėęě][[:>:]]' Matches que, queue, qué. (I have no idea why this matches queue, but the Regex behavior is bizarre with unicode.) Does anyone know why the final regex acts weird? It there a good solution? Thanks in advance, John Campbell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quick help with Insert
that's exactly how i do it. On 1/19/10 3:57 PM, "Intell! Soft" wrote: > Thanks! - I found a Insert Into on your tip: > > Insert Into Lieferanten (Lieferant) > select distinct > a.lieferant > from artikelstamm a > left join lieferanten b on > a.lieferant = b.lieferant > where > b.lieferant is null > > > > > -Ursprüngliche Nachricht- > Von: Carlos Proal [mailto:carlos.pr...@gmail.com] > Bereitgestellt: Dienstag, 19. Jänner 2010 20:15 > Bereitgestellt in: gmane.comp.db.mysql.general > Unterhaltung: Quick help with Insert > Betreff: Re: Quick help with Insert > > > Hi !! > > You need a left join and then an insert. > Please read: http://dev.mysql.com/doc/refman/5.1/en/join.html or google > for tutorials on left join > > And tell me if you have further questions > > Carlos > > > On 1/19/2010 1:00 PM, Intell! Soft wrote: >> Hey >> Not really quick ;> - But nobody knows an answer? >> >> THX >> >> >> -Ursprüngliche Nachricht- >> Von: Intell! Soft [mailto:intellis...@fachoptiker.net] >> Bereitgestellt: Donnerstag, 14. Jänner 2010 17:40 >> Bereitgestellt in: gmane.comp.db.mysql.general >> Unterhaltung: Quick help with Insert >> Betreff: Quick help with Insert >> >> Hey >> >> I would need quick help with an Insert statement >> So, I have two tables Table A& Table B >> So, in Table A I have a field called customerID >> The same field I do have in Table B >> So, I want to find out, which customerID from Table A is NOT in Table > B >> and fill the customerID's which are not present in Table B. >> >> Understood? - Hope so >> >> THX ! >> >> >> >> >> >> >> >> >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 1/19/2010 7:49 AM, Mark Goodge wrote: On 19/01/2010 14:44, Tompkins Neil wrote: Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Store the date/time that the password was changed, and as well as not alllowing one within the past four passwords you can also disallow one that was last used within the past N days, for whatever value of N you prefer. Mark Keep in mind that if you do this you may be setting yourself up for other security risks (people writing down passwords, etc). If a security measure gets in the way of the right people's ability to access the environment, they will find a way to circumvent it--and screw over your pci compliance in the process. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SCALING INSERT
Krishna Chandra Prajapati wrote: Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna The manual is your friend. It doesn't hurt to consult it. INSERT ... http://dev.mysql.com/doc/refman/5.1/en/insert.html LOAD DATA INFILE ... http://dev.mysql.com/doc/refman/5.1/en/load-data.html And we even have both general and specific suggestions on improving INSERT performance in our Optimization chapter: http://dev.mysql.com/doc/refman/5.1/en/optimization.html Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Selecting, Inserting and Deleting data
Krishna Chandra Prajapati wrote: Hi List, I am working for a messaging company, sending sms to enterprise customers. In a mysql table data is being continuously inserted by user. Most of the time we have 5 to 10 millions of data in this table. Table name : alt_send_sms engine myisam From this table, i need to select data based on below parameter. Send some where else and then delete the selected data. selection and deletion part is done in bulk. SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20 delete from alt_send_sms where sql_id in () sql_id is a unique bigint column with auto_increment. Since the selection and deletion is done in bulk. Therefore, i cannot run many similar concurrent queries. As duplicate messages will be send. What can be the solution for this ? Any response is highly appreciated. Thanks, Krishna The main thing you want to achieve is to isolate these rows from other queries, correct? One easy way around this is to "tag" each row with a unique value. UPDATE SET tag_column= WHERE conditions> AND tag_column=0 The last part, "AND tag_column=0" is the part that ensures that only untagged columns that meet your condition are tagged with your unique identifier. You can repeat this tagging process on several tables (using the same value) to build a set of related values for your processing needs. After you have set your values, you have identified an entire set of data that you want to manipulate. When you are done processing the SELECT, you can very easily drop just those rows by DELETE FROM WHERE tag_column= Or you can return those rows into the general pool of data by simply clearing the flag UPDATE SET tag_column = 0 WHERE tag_column = value>; Some ideas for unique values: * the thread number from within your application. * the connection number for your MySQL client * a UUID value * a hashed value of a combination of pseudo-random values (IP address, time, someone's name, a random number, etc.) . * some sequential number you track in another table. All you really need is a number statistically improbable to repeat between any two of your clients. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LOAD BALANCER
Hi list, Out of the below load balancer which is the best one. Is there any other load balancer available. 1 mysql proxy (Still alpha) 2 ultramonkey. 3 haproxy Thanks, Krishna
Re: Selecting, Inserting and Deleting data
On Thu, Jan 21, 2010 at 7:15 AM, Krishna Chandra Prajapati wrote: > 1. Inserted data into a table A by user. > 2. Selecting data from table A inserting data to table B after applying some > rules(update). > 3. Deleting data from table A. > 4. Selecting data from table B using some conditions (SELECT sql_id, momt, > sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, > sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id = > 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for > sending sms. > 5. Deleting the selected data from table B. > > With the above scenario, i am not able to user concurrent connections. Other > wise it will send duplicate sms. I see, you want to lock on steps 4 and 5 to prevent concurrent access to the same records. You can use SELECT FOR UPDATE (if you're using InnoDB) or just lock the whole table. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SCALING INSERT
Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna
Re: Selecting, Inserting and Deleting data
Hi Abhishek. insert, select and delete are in the same proportion. 1. Inserted data into a table A by user. 2. Selecting data from table A inserting data to table B after applying some rules(update). 3. Deleting data from table A. 4. Selecting data from table B using some conditions (SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for sending sms. 5. Deleting the selected data from table B. With the above scenario, i am not able to user concurrent connections. Other wise it will send duplicate sms. Thanks, Kishna On Thu, Jan 21, 2010 at 5:28 PM, Abhishek Singh wrote: > > > On Thu, Jan 21, 2010 at 5:18 PM, Suresh Kuna wrote: > >> Innodb contains multi-version property, so it can handle more concurrent >> queries from user connections. >> >> >> On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati < >> prajapat...@gmail.com> wrote: >> >> > Hi Suresh, >> > >> > my question is how i can run concurrent connection with the above work >> > load. >> > >> > Thanks, >> > Krishna >> > >> > >> > On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna > >wrote: >> > >> >> Hi Krishna, >> >> As table is using MyISAM engine and it acquires a table level lock, the >> >> queries will be executed one after one . >> >> By converting it into Innodb as it acquires a row level lock, doing a >> >> select and delete based on primary key will be faster and the >> concurrency >> >> increases. >> >> >> >> -- >> >> Thanks >> >> Suresh Kuna >> >> MySQL DBA >> >> >> >> >> >> >> >> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < >> >> prajapat...@gmail.com> wrote: >> >> >> >>> Hi List, >> >>> >> >>> I am working for a messaging company, sending sms to enterprise >> >>> customers. >> >>> >> >>> In a mysql table data is being continuously inserted by user. Most of >> the >> >>> time we have 5 to 10 millions of data in this table. >> >>> >> >>> Table name : alt_send_sms engine myisam >> >>> >> >>> From this table, i need to select data based on below parameter. Send >> >>> some >> >>> where else and then delete the selected data. >> >>> >> >>> selection and deletion part is done in bulk. >> >>> >> >>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, >> smsc_id, >> >>> service, account, id, sms_type, mclass, mwi, coding, compress FROM >> >>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT >> >>> 0,20 >> >>> >> >>> delete from alt_send_sms where sql_id in >> >>> () >> >>> >> >>> sql_id is a unique bigint column with auto_increment. >> >>> >> >>> Since the selection and deletion is done in bulk. Therefore, i cannot >> run >> >>> many similar concurrent queries. As duplicate messages will be send. >> What >> >>> can be the solution for this ? >> >>> >> >>> Any response is highly appreciated. >> >>> >> >>> Thanks, >> >>> Krishna >> >>> >> >> >> >> >> >> >> > >> >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> > > > > Hi Krishna, > > Can you please tell me what kind query you mostly run is it select or > insert? > > -- > Abhishek Kumar Singh > >
Re: Selecting, Inserting and Deleting data
On Thu, Jan 21, 2010 at 5:18 PM, Suresh Kuna wrote: > Innodb contains multi-version property, so it can handle more concurrent > queries from user connections. > > > On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati < > prajapat...@gmail.com> wrote: > > > Hi Suresh, > > > > my question is how i can run concurrent connection with the above work > > load. > > > > Thanks, > > Krishna > > > > > > On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna >wrote: > > > >> Hi Krishna, > >> As table is using MyISAM engine and it acquires a table level lock, the > >> queries will be executed one after one . > >> By converting it into Innodb as it acquires a row level lock, doing a > >> select and delete based on primary key will be faster and the > concurrency > >> increases. > >> > >> -- > >> Thanks > >> Suresh Kuna > >> MySQL DBA > >> > >> > >> > >> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < > >> prajapat...@gmail.com> wrote: > >> > >>> Hi List, > >>> > >>> I am working for a messaging company, sending sms to enterprise > >>> customers. > >>> > >>> In a mysql table data is being continuously inserted by user. Most of > the > >>> time we have 5 to 10 millions of data in this table. > >>> > >>> Table name : alt_send_sms engine myisam > >>> > >>> From this table, i need to select data based on below parameter. Send > >>> some > >>> where else and then delete the selected data. > >>> > >>> selection and deletion part is done in bulk. > >>> > >>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, > >>> service, account, id, sms_type, mclass, mwi, coding, compress FROM > >>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT > >>> 0,20 > >>> > >>> delete from alt_send_sms where sql_id in > >>> () > >>> > >>> sql_id is a unique bigint column with auto_increment. > >>> > >>> Since the selection and deletion is done in bulk. Therefore, i cannot > run > >>> many similar concurrent queries. As duplicate messages will be send. > What > >>> can be the solution for this ? > >>> > >>> Any response is highly appreciated. > >>> > >>> Thanks, > >>> Krishna > >>> > >> > >> > >> > > > > > -- > Thanks > Suresh Kuna > MySQL DBA > Hi Krishna, Can you please tell me what kind query you mostly run is it select or insert? -- Abhishek Kumar Singh
Re: Selecting, Inserting and Deleting data
Innodb contains multi-version property, so it can handle more concurrent queries from user connections. On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi Suresh, > > my question is how i can run concurrent connection with the above work > load. > > Thanks, > Krishna > > > On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna wrote: > >> Hi Krishna, >> As table is using MyISAM engine and it acquires a table level lock, the >> queries will be executed one after one . >> By converting it into Innodb as it acquires a row level lock, doing a >> select and delete based on primary key will be faster and the concurrency >> increases. >> >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >> >> >> >> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < >> prajapat...@gmail.com> wrote: >> >>> Hi List, >>> >>> I am working for a messaging company, sending sms to enterprise >>> customers. >>> >>> In a mysql table data is being continuously inserted by user. Most of the >>> time we have 5 to 10 millions of data in this table. >>> >>> Table name : alt_send_sms engine myisam >>> >>> From this table, i need to select data based on below parameter. Send >>> some >>> where else and then delete the selected data. >>> >>> selection and deletion part is done in bulk. >>> >>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, >>> service, account, id, sms_type, mclass, mwi, coding, compress FROM >>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT >>> 0,20 >>> >>> delete from alt_send_sms where sql_id in >>> () >>> >>> sql_id is a unique bigint column with auto_increment. >>> >>> Since the selection and deletion is done in bulk. Therefore, i cannot run >>> many similar concurrent queries. As duplicate messages will be send. What >>> can be the solution for this ? >>> >>> Any response is highly appreciated. >>> >>> Thanks, >>> Krishna >>> >> >> >> > -- Thanks Suresh Kuna MySQL DBA
Re: Record old passwords ?
On 21/01/2010 11:07, Lucio Chiappetti wrote: On Tue, 19 Jan 2010, Tompkins Neil wrote: I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Probably if your users do that, it means they (rightfully) consider A DAMN NUISANCE the fact to be compelled to change password. Abandon the idea. I share their feeling about forcing this change of passwords, and cannot see almost no real life application (unless perhaps one is a spy) which really require this degree of security ! The real life application most commonly encountered where this is necessary is where your organisation wishes to process credit card or other financial data, and needs to be certified as PCI compliant by the banks and card companies in order to be able to process payments via their systems. One of the requirements of PCI compliance is that any login which has access to financial data must have the password changed regularly, with restrictions on reusing recent passwords. Now, you may well argue that the PCI requirements are wrong in this respect, and if so then a lot of people may well agree with you :-) However, unless you are a huge multinational and able to negotiate your own terms with the banks, disagreeing with the requirements doesn't alter the need to comply with them - at least, not if you want to be able to use their payment APIs. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Selecting, Inserting and Deleting data
Hi Suresh, my question is how i can run concurrent connection with the above work load. Thanks, Krishna On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna wrote: > Hi Krishna, > As table is using MyISAM engine and it acquires a table level lock, the > queries will be executed one after one . > By converting it into Innodb as it acquires a row level lock, doing a > select and delete based on primary key will be faster and the concurrency > increases. > > -- > Thanks > Suresh Kuna > MySQL DBA > > > > On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < > prajapat...@gmail.com> wrote: > >> Hi List, >> >> I am working for a messaging company, sending sms to enterprise customers. >> >> In a mysql table data is being continuously inserted by user. Most of the >> time we have 5 to 10 millions of data in this table. >> >> Table name : alt_send_sms engine myisam >> >> From this table, i need to select data based on below parameter. Send some >> where else and then delete the selected data. >> >> selection and deletion part is done in bulk. >> >> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, >> service, account, id, sms_type, mclass, mwi, coding, compress FROM >> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT >> 0,20 >> >> delete from alt_send_sms where sql_id in >> () >> >> sql_id is a unique bigint column with auto_increment. >> >> Since the selection and deletion is done in bulk. Therefore, i cannot run >> many similar concurrent queries. As duplicate messages will be send. What >> can be the solution for this ? >> >> Any response is highly appreciated. >> >> Thanks, >> Krishna >> > > >
Re: Selecting, Inserting and Deleting data
Hi Krishna, As table is using MyISAM engine and it acquires a table level lock, the queries will be executed one after one . By converting it into Innodb as it acquires a row level lock, doing a select and delete based on primary key will be faster and the concurrency increases. -- Thanks Suresh Kuna MySQL DBA On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi List, > > I am working for a messaging company, sending sms to enterprise customers. > > In a mysql table data is being continuously inserted by user. Most of the > time we have 5 to 10 millions of data in this table. > > Table name : alt_send_sms engine myisam > > From this table, i need to select data based on below parameter. Send some > where else and then delete the selected data. > > selection and deletion part is done in bulk. > > SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, > service, account, id, sms_type, mclass, mwi, coding, compress FROM > alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20 > > delete from alt_send_sms where sql_id in > () > > sql_id is a unique bigint column with auto_increment. > > Since the selection and deletion is done in bulk. Therefore, i cannot run > many similar concurrent queries. As duplicate messages will be send. What > can be the solution for this ? > > Any response is highly appreciated. > > Thanks, > Krishna >
Re: Record old passwords ?
On Tue, 19 Jan 2010, Tompkins Neil wrote: I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Probably if your users do that, it means they (rightfully) consider A DAMN NUISANCE the fact to be compelled to change password. Abandon the idea. I share their feeling about forcing this change of passwords, and cannot see almost no real life application (unless perhaps one is a spy) which really require this degree of security ! -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Selecting, Inserting and Deleting data
Hi List, I am working for a messaging company, sending sms to enterprise customers. In a mysql table data is being continuously inserted by user. Most of the time we have 5 to 10 millions of data in this table. Table name : alt_send_sms engine myisam >From this table, i need to select data based on below parameter. Send some where else and then delete the selected data. selection and deletion part is done in bulk. SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20 delete from alt_send_sms where sql_id in () sql_id is a unique bigint column with auto_increment. Since the selection and deletion is done in bulk. Therefore, i cannot run many similar concurrent queries. As duplicate messages will be send. What can be the solution for this ? Any response is highly appreciated. Thanks, Krishna
Re: parameter being overwritten
Jerome Macaranas schrieb: > im trying to setup mysql slave but the things is it wont start because of > this errror: > > --> ERROR 1200 (HY000): The server is not configured as slave; fix in config > file or with CHANGE MASTER TO > > after some testing.. i saw the server-id = 0 > through > show variables like 'server_id' > > > went to check server-id parameter in /etc/my.cnf > grep server-id /etc/my.cnf > #server-id = 2 > server-id = 2 > -- its good.. > > ls -l ~/my.cnf -- file not found.. > > print_defaults mysqld result > --server-id=2 > -- its good > > but again > show variables like server_id is showing "0" > what i had to do is set global parameter in mysqld cli w/c is not a good > thing.. > > > additional info: > > Default options are read from the following files in the given order: > /etc/my.cnf ~/.my.cnf /etc/my.cnf > > > is there anyway to trace why server-id = 0? > To make sure that mysql is actualy reading the file you may use strace (see man strace) and look if the my.cnf is realy read. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
