Re: subquery error with no result
Hi, I found the solution for this issue. I separate into 2 queries and put the result onto an array, and do PHP array_intersect_assoc to match. Much more simpler, fast, about 200 thousands record matched in 10 seconds. Many thanks for your helps. Regards, Willy On Thu, 2008-05-22 at 07:40 -0700, Rob Wultsch wrote: > Sub queries suck in mysql. It is probably worth while to post the results of: > EXPLAIN SELECT msgdata > FROM sent_sms > WHERE momt = 'MT' > AND binfo IN (SELECT >binfo >FROM sent_sms >WHERE momt = 'DLR') > > Are binfo and momt indexed? If not, they probably should be. A > replacement for the sub query should be something like: > SELECT s1.msgdata > FROM sent_sms AS s1 > INNER JOIN sent_sms AS s1 ON s1.binfo =s2.binfo > WHERE s1.momt = 'MT' > > > > On Thu, May 22, 2008 at 2:05 AM, sangprabv <[EMAIL PROTECTED]> wrote: > > Hi, > > Thanks for the reply. The query seems doing something but after minutes > > of waiting it still return no result. > > > > Regards, > > > > > > Willy > > On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote: > >> Would this work for you? > >> > >> SELECT msgdata > >> FROM sent_sms > >> WHERE momt = 'MT' > >> AND binfo IN (SELECT > >> binfo > >> FROM sent_sms > >> WHERE momt = 'DLR') > >> > >> > >> David > >> > >> > >> On 5/21/08 10:30 PM, "sangprabv" <[EMAIL PROTECTED]> wrote: > >> > >> > Hi, > >> > I tried to look for records from a table with this query: > >> > SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT > >> > binfo FROM sent_sms WHERE momt = 'DLR' ) > >> > But MySQL returns this error: > >> > #1242 - Subquery returns more than 1 row > >> > I tried also with ANY, IN, EXISTS. > >> > And modified the query into: > >> > SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') > >> > AS t1 > >> > WHERE momt = 'MT'. But none works. > >> > What I want to view is, all records which has momt = 'MT' and binfo from > >> > the same table where has momt = 'DLR' and has the same binfo. TIA > >> > > >> > Regards, > >> > > >> > > >> > Willy > >> > > >> > >> > > -- > > [sangprabv] > > http://www.sangprabv.web.id > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master-Slave replication error: Last_Errno: 1146
On Thu, May 22, 2008 at 5:36 PM, Salah Nait-Mouloud < [EMAIL PROTECTED]> wrote: > Hi all. > > I have 2 MySQL servers. > One master and one slave. > In order to add new slave server, and because i can't stop the master one, > i > have tried this: > > http://forums.mysql.com/read.php?26,99846,102058 > > " > *You don't have to modify the other slaves configuration. What you need to > do is obtain a snapshot of the data on those slaves with their current > position relative to the master. The easiest way to do this is to stop > mysql > on one of the slaves and copy it's data directory (except for the bin logs) > and my.cnf to the new slave. Then start the old slave so it doesn't get too > far behind in replication. On the new slave change the server-id in my.cnf > so it's unique from the other servers. Add skip-slave to my.cnf. Start > mysql, login and run show slave status\G Then run the following command > substituting the $values from the show slave status > > change master to master_log_file='$Relay_master_log_file', > master_log_pos=$Exec_master_log_pos; > > Then start the new slave; > > Once replication catches up it's good to go :)* > " > > The issue is when i do "START SLAVE;" i obtain this error: > > Slave_IO_Running: Yes > Slave_SQL_Running: No >Replicate_Do_DB: >Replicate_Ignore_DB: > Replicate_Do_Table: > Replicate_Ignore_Table: >Replicate_Wild_Do_Table: > Replicate_Wild_Ignore_Table: > Last_Errno: 1146 > Last_Error: Error 'Table 'XX.y' doesn't exist' > on query. Default database: ''. Query: 'INSERT INTO >y ' > > MySQL Server version: version 5.0.32 . > > Any idea ? It looks like you started at a position that after an INSERT. So your position is wrong. > > Many Thanks. > -- > Salah NAIT-MOULOUD > Echovox - www.echovox.com > m-Boost - www.m-boost.com >
Master-Slave replication error: Last_Errno: 1146
Hi all. I have 2 MySQL servers. One master and one slave. In order to add new slave server, and because i can't stop the master one, i have tried this: http://forums.mysql.com/read.php?26,99846,102058 " *You don't have to modify the other slaves configuration. What you need to do is obtain a snapshot of the data on those slaves with their current position relative to the master. The easiest way to do this is to stop mysql on one of the slaves and copy it's data directory (except for the bin logs) and my.cnf to the new slave. Then start the old slave so it doesn't get too far behind in replication. On the new slave change the server-id in my.cnf so it's unique from the other servers. Add skip-slave to my.cnf. Start mysql, login and run show slave status\G Then run the following command substituting the $values from the show slave status change master to master_log_file='$Relay_master_log_file', master_log_pos=$Exec_master_log_pos; Then start the new slave; Once replication catches up it's good to go :)* " The issue is when i do "START SLAVE;" i obtain this error: Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1146 Last_Error: Error 'Table 'XX.y' doesn't exist' on query. Default database: ''. Query: 'INSERT INTO y ' MySQL Server version: version 5.0.32 . Any idea ? Many Thanks. -- Salah NAIT-MOULOUD Echovox - www.echovox.com m-Boost - www.m-boost.com
Re: master master replication - hostname change - slaves fails
On Thu, May 22, 2008 at 10:00 AM, Tom Brown <[EMAIL PROTECTED]> wrote: > > >> I don't think you need to flush your bin logs. >> >> CHANGE MASTER TO MASTER HOST = 'hostname'; >> Look at this for more info at >> http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html >> There is a lot more you can add to the statement. >> >> >> >> >> > > thanks but this gave me a very similar error - I forget now as i > reinstalled from scratch. Don't jump the gun to reinstall so fast, your last spot was easier to fix. > > > i now get this error > > 080522 13:49:34 [ERROR] Slave: Error 'Table 'db' already exists' on query. > Default database: 'mysql' > > which to me is odd as i have the following in my my.cnf > > binlog-ignore-db=mysql > > so i would have thought it would not try and replicate that db ? Although i > did not need that in my previous configuration. > > Any thoughts ? > > Well if your master ever fails and you make the slave your new mater you will be missing your mysql database. User mostly will be missing, which might not be a big thing in your situation. Are there any other logs before and after? Mike
Re: Restarting slave after interruption
On Thu, May 22, 2008 at 10:41 AM, François Beausoleil <[EMAIL PROTECTED]>
wrote:
> Hi all!
> I must be stupid or something. I can't find what my problem is.
>
> I searched this list, and did find a couple of hits, but nothing that
> seemed fully relevant. This one in particular was interesting:
> http://lists.mysql.com/mysql/212863
>
> I have a single master (server-id=1) and a single slave (server-id=2).
> Replication was correctly setup, and I was doing backups from the slave. To
> test recovery, I terminated the slave server (I'm on EC2), and I now wish to
> start a new one.
>
If your taking a dump of the master open two windows. Don't close either
In the first lock all tables with FLUSH ALL TABLES WITH READ LOCK; & master
status;
Then in the second window procedure with mysqldump.
>
> First, I dump my master using this:
>
> mysqldump --master-data --flush-logs --extended-insert --single-transaction
> -u root -papassword mydb >thedump.sql
>
> Then, I copy the dump to the slave and load the dump using:
>
> mysql -u root -papassword mydb
> Next, I login to the slave server using the mysql command line client and
> issue the following commands:
>
> CHANGE MASTER TO MASTER_HOST='10.252.155.80',
>
> MASTER_USER='root',
>
> MASTER_PASSWORD='apassword',
>
> MASTER_LOG_FILE='mysql-bin.54',
>
> MASTER_LOG_POS=98;
>
> I read the log file and position from the dump (--master-data),
> specifically, this line:
>
> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.54', MASTER_LOG_POS=98;
>
> Then, I issue a START SLAVE on the slave, and SHOW SLAVE STATUS\G
>
> Here's the output:
>
> *** 1. row ***
> Slave_IO_State: Waiting for master to send event
>Master_Host: 10.252.155.80
>Master_User: root
>Master_Port: 3306
> Connect_Retry: 60
>Master_Log_File: mysql-bin.54
>Read_Master_Log_Pos: 39727978
> Relay_Log_File: mysqld-relay-bin.02
> Relay_Log_Pos: 1381
> Relay_Master_Log_File: mysql-bin.54
> Slave_IO_Running: Yes
> Slave_SQL_Running: No
>Replicate_Do_DB:
>Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
>Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table:
> Last_Errno: 1062
> Last_Error: Error 'Duplicate entry '479084' for key
This is a Duplicate primary key. Usually means the position error.
>
> 1' on query. Default database: 'mydb'. Query: 'INSERT INTO assets
> (`content_type`, `email_id`, `size`, `thumbnail`, `updated_at`,
> `account_id`, `archive_id`, `title`, `type`, `folder_id`,
> `description`, `filename`, `height`, `owner_id`, `parent_id`,
> `created_at`, `width`) VALUES('image/jpeg', NULL, 6571, 'small',
> '2008-05-21 23:24:10', 125, NULL, NULL, NULL, NULL, NULL,
> '0101100102000104022008052003d78e21fe087d9623000b5c-21_small.jpg',
> 180, NULL, 479082, '2008-05-21 23:24:10', 240)'
> Skip_Counter: 0
>Exec_Master_Log_Pos: 1244
>Relay_Log_Space: 39728115
>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
>
> Anybody can tell me what I'm doing wrong ? Am I dumping my master DB using
> the right combination of options ?
>
> Thanks !
> François Beausoleil
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Re: Triggering an action every 24 hours
Dan Nelson schrieb: In the last episode (May 22), Samuel Vogel said: Hey, I am wondering how I can trigger an Action every 24 hours inside of mysql. A cron job would also do the job, but it won't work on shared hosts and it's just another point of failure. So is there any way to run a specific SQL command every 24 hours inside of mysql? I'm using MySQL 5.0.51! MySQL 5.1 added an event scheduler that would do what you need, but there's nothing built into 5.0. You'll have to make do with cron jobs, or check out upgrading to 5.1. http://dev.mysql.com/doc/refman/5.1/en/events.html Okay. I guess I'll have to stick with cronjobs then! Thanks, Samy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load data infile
LOAD DATA LOCAL INFILE http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://www.mysql.com/news-and-events/newsletter/2002-05/a12.html -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Thursday, May 22, 2008 2:24 PM To: mysql@lists.mysql.com Subject: Load data infile Hi, I would like to know if I can use the Load data infile to update a table on the server from a workstation? I tried it but was unsuccessful. Is there any other way to do this from a workstation? Thanks. Regards, Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Triggering an action every 24 hours
No. You need to use MySQL 5.1 as that is now a standard feature. Or, if all your functionality is 100% MySQL, just run a stored procedure in an infinite loop and check every 60 seconds for DELIMITER $$ DROP PROCEDURE IF EXISTS `rolando`.`runjob` $$ CREATE PROCEDURE `rolando`.`runjob` (scheduled_hour TINYINT,scheduled_minute TINYINT) BEGIN DECLARE nw DATETIME; DECLARE x,hh,mm,ss,time_to_run TINYINT; SELECT NOW() INTO nw; SELECT HOUR(nw),MINUTE(nw),SECOND(nw) INTO hh,mm,ss; WHILE 1=1 DO SET time_to_run = 0; IF hh = scheduled_hour AND mm = scheduled_minute AND ss = 0 THEN SET time_to_run = 1; END IF; WHILE time_to_run = 0 DO SET x = SLEEP(1); SELECT NOW() INTO nw; SELECT HOUR(nw),MINUTE(nw),SECOND(nw) INTO hh,mm,ss; IF hh = scheduled_hour AND mm = scheduled_minute AND ss = 0 THEN SET time_to_run = 1; END IF; END WHILE; -- Now that You reached the scheduled time -- Run Your SQL Code Here -- On completion, the job will restart SET x = SLEEP(57); END WHILE; END $$ DELIMITER ; -Original Message- From: Samuel Vogel [mailto:[EMAIL PROTECTED] Sent: Thursday, May 22, 2008 2:28 PM To: MYSQL General List Subject: Triggering an action every 24 hours Hey, I am wondering how I can trigger an Action every 24 hours inside of mysql. A cron job would also do the job, but it won't work on shared hosts and it's just another point of failure. So is there any way to run a specific SQL command every 24 hours inside of mysql? I'm using MySQL 5.0.51! Regards, Samy -- 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]
Load data infile
Hi, I would like to know if I can use the Load data infile to update a table on the server from a workstation? I tried it but was unsuccessful. Is there any other way to do this from a workstation? Thanks. Regards, Velen
Re: Triggering an action every 24 hours
In the last episode (May 22), Samuel Vogel said: > Hey, > > I am wondering how I can trigger an Action every 24 hours inside of mysql. > A cron job would also do the job, but it won't work on shared hosts and > it's just another point of failure. > > So is there any way to run a specific SQL command every 24 hours inside of > mysql? > I'm using MySQL 5.0.51! MySQL 5.1 added an event scheduler that would do what you need, but there's nothing built into 5.0. You'll have to make do with cron jobs, or check out upgrading to 5.1. http://dev.mysql.com/doc/refman/5.1/en/events.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Triggering an action every 24 hours
Hey, I am wondering how I can trigger an Action every 24 hours inside of mysql. A cron job would also do the job, but it won't work on shared hosts and it's just another point of failure. So is there any way to run a specific SQL command every 24 hours inside of mysql? I'm using MySQL 5.0.51! Regards, Samy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP & ORDER BY Question
David, What I am trying to limit this query to is the top 100 details ordered by SUM(Volume) DESC for each unique LongDescription For some solutions see 'Within-group quotas (Top N per group)' at http://www.artfulsoftware.com/infotree/queries.php PB - David Perron wrote: Hello MySQL Users- I am pretty sure this is a simple question and I am over thinking how to solve the problem, so I am hoping the community can help. I am selecting a pretty straightforward aggregation from a single stats table with the following format: SELECT Description LongDescription Detail SUM(Volume) FROM StatsTable GROUP BY Description LongDescription Detail What I am trying to limit this query to is the top 100 details ordered by SUM(Volume) DESC for each unique LongDescription This is what I am trying now but its not quite correct, it simply returns 100 of the top details. SELECT Description LongDescription Detail SUM(Volume) FROM StatsTable GROUP BY Description LongDescription Detail ORDER BY SUM(Volume) DESC LIMIT 100 What I believe would work is a function in MySQL that is equivalent to the CUBE function in Oracle. Any direction would be greatly appreciated! David No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.24.0/1460 - Release Date: 5/22/2008 7:06 AM
RE: indexes and speeds
There is a huge difference !!! When You Load a Table with a Primary Key, the Primary get built automatically. Not even ALTER TABLE DISABLE KEYS has an effect since it disables non-unique indexes. Hence, loading the table is a one-pass operation. In contrast, loading a table with two non-unique indexes in the way you specified has the following effect: Your first statement: create index index_name1 on table_name (/|index_col_name|/1); This will make a temp table, copy the data from your table to the temp table, then build index_name1 on the temp table. Finally it renames the temp table to your table. Your second statement: create index index_name2 on table_name (/|index_col_name|/2); This will make a temp table, copy the data from your table to the temp table, then build index_name1 AND index_name2 on the temp table. Finally, it renames the temp table to your table. It copies the whole table twice. Notice, it build indexes thrice (3 times) not twice. Here is a chart that shows how many index builds would occur it you built indexes one at a time: IndexesTemp Table Loads Index Builds --- 1 1 1 2 2 3 3 3 4 4 4 10 5 5 15 6 6 21 7 7 28 8 8 36 9 9 45 10 10 55 11 11 66 12 12 78 13 13 91 14 14105 15 15120 16 16136 17 17153 N N N(N+1)/2 Building indexes one at a time, and non-unique indexes especially, is very inefficient. If you have non-unique indexes in your table, disable the keys first. Here is an example: CREATE TABLE t1 ( A INT NOT NULL PRIMARY KEY, B INT, C INT ); ALTER TABLE t1 ADD INDEX index_name1 (B); ALTER TABLE t1 ADD INDEX index_name2 (C); ALTER TABLE t1 DISABLE KEYS; -- Shuts off non-unique indexes -- Primary Key is Loaded ALTER TABLE t1 ENABLE KEYS; -- Non-unique indexes are loaded linearly This is how mysqldumps are reloaded. Just do a mysqldump of a small table and look at the code it generates for any one table. Here is a sample mysqldump: -- -- Table structure for table `contact` -- DROP TABLE IF EXISTS `contact`; CREATE TABLE `contact` ( `sno` int(11) NOT NULL auto_increment, `Name` varchar(50) default NULL, `mobile` varchar(20) default NULL, `email` varchar(100) default NULL, `companyname` varchar(100) default NULL, `newsletterflag` tinyint(4) default NULL, `smsflag` tinyint(4) default NULL, `createdatetime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`sno`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- -- Dumping data for table `contact` -- LOCK TABLES `contact` WRITE; /*!4 ALTER TABLE `contact` DISABLE KEYS */; INSERT INTO `contact` VALUES (1,'[EMAIL PROTECTED]','6421510248','Srinivasa Rao Munagala','THL INDIA ',1,NULL,'2007-11-08 21:48:36'); /*!4 ALTER TABLE `contact` ENABLE KEYS */; UNLOCK TABLES; Your best bet is the disable keys, load the data, and enable keys. Here is a direct quotation from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. While the non-unique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them -Original Message- From: kalin m [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 5:55 PM To: mysql@lists.mysql.com Subject: indexes and speeds hi all... just wondering what is the performance difference between: PRIMARY KEY [/|index_type|/] (/|index_col_name|/1,/|index_col_name|/2) at the time of the table creation or create index index_name1 on table_name (/|index_col_name|/1); create index index_name2 on table_name (/|index_col_name|/2); after the table has been made? the question i guess is: is there performance advantage to have a primary key defined on two (or more)
Restarting slave after interruption
Hi all!
I must be stupid or something. I can't find what my problem is.
I searched this list, and did find a couple of hits, but nothing that
seemed fully relevant. This one in particular was interesting: http://lists.mysql.com/mysql/212863
I have a single master (server-id=1) and a single slave (server-id=2).
Replication was correctly setup, and I was doing backups from the
slave. To test recovery, I terminated the slave server (I'm on EC2),
and I now wish to start a new one.
First, I dump my master using this:
mysqldump --master-data --flush-logs --extended-insert --single-
transaction -u root -papassword mydb >thedump.sql
Then, I copy the dump to the slave and load the dump using:
mysql -u root -papassword mydb Next, I login to the slave server using the mysql command line client
and issue the following commands:
CHANGE MASTER TO MASTER_HOST='10.252.155.80',
MASTER_USER='root',
MASTER_PASSWORD='apassword',
MASTER_LOG_FILE='mysql-bin.54',
MASTER_LOG_POS=98;
I read the log file and position from the dump (--master-data),
specifically, this line:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.54', MASTER_LOG_POS=98;
Then, I issue a START SLAVE on the slave, and SHOW SLAVE STATUS\G
Here's the output:
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 10.252.155.80
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.54
Read_Master_Log_Pos: 39727978
Relay_Log_File: mysqld-relay-bin.02
Relay_Log_Pos: 1381
Relay_Master_Log_File: mysql-bin.54
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '479084' for key
1' on query. Default database: 'mydb'. Query: 'INSERT INTO assets
(`content_type`, `email_id`, `size`, `thumbnail`, `updated_at`,
`account_id`, `archive_id`, `title`, `type`, `folder_id`,
`description`, `filename`, `height`, `owner_id`, `parent_id`,
`created_at`, `width`) VALUES('image/jpeg', NULL, 6571, 'small',
'2008-05-21 23:24:10', 125, NULL, NULL, NULL, NULL, NULL,
'0101100102000104022008052003d78e21fe087d9623000b5c-21_small.jpg',
180, NULL, 479082, '2008-05-21 23:24:10', 240)'
Skip_Counter: 0
Exec_Master_Log_Pos: 1244
Relay_Log_Space: 39728115
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
Anybody can tell me what I'm doing wrong ? Am I dumping my master DB
using the right combination of options ?
Thanks !
François Beausoleil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for a Mysql Guru/DBA
Kevin Hunter wrote: At 1:43p -0400 on Wed, 21 May 2008, bruce wrote: The basic goal of the project is to be able to track the sites that I'm visiting via a Firefox extension. I want to be able to implement something like the "breadcrumbs" extension, but I want to be able to go a lot further. If this is for a one-per-person kind thing (i.e. only a single Firefox installation will use a single DB instance at any time), MySQL may be overkill. You may want to look towards something smaller and embedded, like SQLite[1]. It'll be much less overhead, in both installation for users and memory overhead for your extension. In fact, Firefox 3.0 already includes SQLite for the smart url bar they've got going on. If you're interested, and you're reasonably good at mysql, and devising database structures/schema then let's talk! You will still want to talk to someone about getting the right schema in place, however. Kevin [1] http://www.sqlite.org/ Hey Kevin, One thought on that, there are a lot of existing WAMP installs out there. :) But overall I think you are probably right. This might be worth a look http://www.freebyte.com/programming/database/ Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery error with no result
Sub queries suck in mysql. It is probably worth while to post the results of: EXPLAIN SELECT msgdata FROM sent_sms WHERE momt = 'MT' AND binfo IN (SELECT binfo FROM sent_sms WHERE momt = 'DLR') Are binfo and momt indexed? If not, they probably should be. A replacement for the sub query should be something like: SELECT s1.msgdata FROM sent_sms AS s1 INNER JOIN sent_sms AS s1 ON s1.binfo =s2.binfo WHERE s1.momt = 'MT' On Thu, May 22, 2008 at 2:05 AM, sangprabv <[EMAIL PROTECTED]> wrote: > Hi, > Thanks for the reply. The query seems doing something but after minutes > of waiting it still return no result. > > Regards, > > > Willy > On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote: >> Would this work for you? >> >> SELECT msgdata >> FROM sent_sms >> WHERE momt = 'MT' >> AND binfo IN (SELECT >> binfo >> FROM sent_sms >> WHERE momt = 'DLR') >> >> >> David >> >> >> On 5/21/08 10:30 PM, "sangprabv" <[EMAIL PROTECTED]> wrote: >> >> > Hi, >> > I tried to look for records from a table with this query: >> > SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT >> > binfo FROM sent_sms WHERE momt = 'DLR' ) >> > But MySQL returns this error: >> > #1242 - Subquery returns more than 1 row >> > I tried also with ANY, IN, EXISTS. >> > And modified the query into: >> > SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS >> > t1 >> > WHERE momt = 'MT'. But none works. >> > What I want to view is, all records which has momt = 'MT' and binfo from >> > the same table where has momt = 'DLR' and has the same binfo. TIA >> > >> > Regards, >> > >> > >> > Willy >> > >> >> > -- > [sangprabv] > http://www.sangprabv.web.id > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for a Mysql Guru/DBA
At 1:43p -0400 on Wed, 21 May 2008, bruce wrote: > The basic goal of the project is to be able to track the sites that I'm > visiting via a Firefox extension. I want to be able to implement something > like the "breadcrumbs" extension, but I want to be able to go a lot further. If this is for a one-per-person kind thing (i.e. only a single Firefox installation will use a single DB instance at any time), MySQL may be overkill. You may want to look towards something smaller and embedded, like SQLite[1]. It'll be much less overhead, in both installation for users and memory overhead for your extension. In fact, Firefox 3.0 already includes SQLite for the smart url bar they've got going on. > If you're interested, and you're reasonably good at mysql, and devising > database structures/schema then let's talk! You will still want to talk to someone about getting the right schema in place, however. Kevin [1] http://www.sqlite.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial operators - DISTANCE
Hi, I am developing a geo-spatial application that requires the DISTANCE function. We have been using 5.1.23-GIS beta, but have noted that it appears not to be in the 5.1.24 beta or the 6.0 alpha release. I also can find no documentation as to when this will be merged into the main release or go into production. Any hints or clues would be much appreciated. -- Best regards, jona. Just don't create a file called -rf. :-) --- Larry Wall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: master master replication - hostname change - slaves fails
On Thu, May 22, 2008 at 9:20 AM, Tom Brown <[EMAIL PROTECTED]> wrote: > > Hi >> >> I had master-master working fin in dev - i move them to prd now and so >> change the hostnames, on starting i see this error >> >> 080522 11:53:40 mysqld started >> 080522 11:53:40 InnoDB: Started; log sequence number 0 213274351 >> 080522 11:53:40 [ERROR] Failed to open the relay log >> './devnagios01-relay-bin.03' (relay_log_pos 949012) >> 080522 11:53:40 [ERROR] Could not find target log during relay log >> initialization >> 080522 11:53:40 [ERROR] Failed to initialize the master info structure >> >> in mysql data directory i see this >> >> -rw-rw 1 mysql mysql 949012 May 22 11:40 devnagios01-relay-bin.03 >> -rw-rw 1 mysql mysql 34 May 19 16:08 devnagios01-relay-bin.index >> -rw-rw 1 mysql mysql 4 May 22 11:53 prdnagios01-relay-bin.01 >> -rw-rw 1 mysql mysql 4 May 22 11:54 prdnagios01-relay-bin.02 >> -rw-rw 1 mysql mysql 68 May 22 11:54 prdnagios01-relay-bin.index >> >> is there a way to 'flush' this or simalar so i can start the slaves again? >> >> > hmm actually on one of the db's i see this > > mysql> show slave status\G > *** 1. row *** >Slave_IO_State: > Master_Host: 192.168.12.225 > > thats the OLD dev IP and not the prd one - even though i have updated the > master host IP in the my.cnf file. How can i change this value? > I don't think you need to flush your bin logs. CHANGE MASTER TO MASTER HOST = 'hostname'; Look at this for more info at http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html There is a lot more you can add to the statement. > > thanks > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: master master replication - hostname change - slaves fails
Hi I had master-master working fin in dev - i move them to prd now and so change the hostnames, on starting i see this error 080522 11:53:40 mysqld started 080522 11:53:40 InnoDB: Started; log sequence number 0 213274351 080522 11:53:40 [ERROR] Failed to open the relay log './devnagios01-relay-bin.03' (relay_log_pos 949012) 080522 11:53:40 [ERROR] Could not find target log during relay log initialization 080522 11:53:40 [ERROR] Failed to initialize the master info structure in mysql data directory i see this -rw-rw 1 mysql mysql 949012 May 22 11:40 devnagios01-relay-bin.03 -rw-rw 1 mysql mysql 34 May 19 16:08 devnagios01-relay-bin.index -rw-rw 1 mysql mysql 4 May 22 11:53 prdnagios01-relay-bin.01 -rw-rw 1 mysql mysql 4 May 22 11:54 prdnagios01-relay-bin.02 -rw-rw 1 mysql mysql 68 May 22 11:54 prdnagios01-relay-bin.index is there a way to 'flush' this or simalar so i can start the slaves again? hmm actually on one of the db's i see this mysql> show slave status\G *** 1. row *** Slave_IO_State: Master_Host: 192.168.12.225 thats the OLD dev IP and not the prd one - even though i have updated the master host IP in the my.cnf file. How can i change this value? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master master replication - hostname change - slaves fails
Hi I had master-master working fin in dev - i move them to prd now and so change the hostnames, on starting i see this error 080522 11:53:40 mysqld started 080522 11:53:40 InnoDB: Started; log sequence number 0 213274351 080522 11:53:40 [ERROR] Failed to open the relay log './devnagios01-relay-bin.03' (relay_log_pos 949012) 080522 11:53:40 [ERROR] Could not find target log during relay log initialization 080522 11:53:40 [ERROR] Failed to initialize the master info structure in mysql data directory i see this -rw-rw 1 mysql mysql 949012 May 22 11:40 devnagios01-relay-bin.03 -rw-rw 1 mysql mysql 34 May 19 16:08 devnagios01-relay-bin.index -rw-rw 1 mysql mysql 4 May 22 11:53 prdnagios01-relay-bin.01 -rw-rw 1 mysql mysql 4 May 22 11:54 prdnagios01-relay-bin.02 -rw-rw 1 mysql mysql 68 May 22 11:54 prdnagios01-relay-bin.index is there a way to 'flush' this or simalar so i can start the slaves again? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: integer
If i am not using zerofill. Then is there any different between int(10) and int On Thu, May 22, 2008 at 2:23 PM, Glyn Astill <[EMAIL PROTECTED]> wrote: > int(10) will pad with zeros on return, i.e. if value = 59 > > int(10) 59 > int59 > > - Original Message > > From: Krishna Chandra Prajapati <[EMAIL PROTECTED]> > > To: MYSQL General List > > Sent: Thursday, 22 May, 2008 9:34:55 AM > > Subject: integer > > > > Hi, > > > > I would to know the difference between the two > > > > int(10) and int (Used in create table ) Which one is better and why. > > > > Regards, > > -- > > Krishna Chandra Prajapati > > MySQL DBA, > > > > __ > Sent from Yahoo! Mail. > A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html > -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Re: subquery error with no result
Hi, Thanks for the reply. The query seems doing something but after minutes of waiting it still return no result. Regards, Willy On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote: > Would this work for you? > > SELECT msgdata > FROM sent_sms > WHERE momt = 'MT' > AND binfo IN (SELECT > binfo > FROM sent_sms > WHERE momt = 'DLR') > > > David > > > On 5/21/08 10:30 PM, "sangprabv" <[EMAIL PROTECTED]> wrote: > > > Hi, > > I tried to look for records from a table with this query: > > SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT > > binfo FROM sent_sms WHERE momt = 'DLR' ) > > But MySQL returns this error: > > #1242 - Subquery returns more than 1 row > > I tried also with ANY, IN, EXISTS. > > And modified the query into: > > SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1 > > WHERE momt = 'MT'. But none works. > > What I want to view is, all records which has momt = 'MT' and binfo from > > the same table where has momt = 'DLR' and has the same binfo. TIA > > > > Regards, > > > > > > Willy > > > > -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: integer
what kind of datatype it is int(50) parent_id| int(50) is that big int? Saravanan --- On Thu, 5/22/08, Norbert Tretkowski <[EMAIL PROTECTED]> wrote: > From: Norbert Tretkowski <[EMAIL PROTECTED]> > Subject: Re: integer > To: mysql@lists.mysql.com > Date: Thursday, May 22, 2008, 3:34 PM > Am 22.05.2008, 14:04 +0530 schrieb Krishna Chandra > Prajapati: > > I would to know the difference between the two > > > > int(10) and int (Used in create table ) > > MySQL has an excellent documentation, which of course also > answers your > question: > > http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html > > Norbert > > > -- > 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: integer
Am 22.05.2008, 14:04 +0530 schrieb Krishna Chandra Prajapati: > I would to know the difference between the two > > int(10) and int (Used in create table ) MySQL has an excellent documentation, which of course also answers your question: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html Norbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: integer
If you didn't define zerofill int is equal to int(11) if the actual variable is 59,then all display 59. On Thu, May 22, 2008 at 4:53 PM, Glyn Astill <[EMAIL PROTECTED]> wrote: > int(10) will pad with zeros on return, i.e. if value = 59 > > int(10) 59 > int59 > > - Original Message > > From: Krishna Chandra Prajapati <[EMAIL PROTECTED]> > > To: MYSQL General List > > Sent: Thursday, 22 May, 2008 9:34:55 AM > > Subject: integer > > > > Hi, > > > > I would to know the difference between the two > > > > int(10) and int (Used in create table ) Which one is better and why. > > > > Regards, > > -- > > Krishna Chandra Prajapati > > MySQL DBA, > > > > __ > Sent from Yahoo! Mail. > A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: integer
int(10) will pad with zeros on return, i.e. if value = 59 int(10) 59 int59 - Original Message > From: Krishna Chandra Prajapati <[EMAIL PROTECTED]> > To: MYSQL General List > Sent: Thursday, 22 May, 2008 9:34:55 AM > Subject: integer > > Hi, > > I would to know the difference between the two > > int(10) and int (Used in create table ) Which one is better and why. > > Regards, > -- > Krishna Chandra Prajapati > MySQL DBA, __ Sent from Yahoo! Mail. A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
integer
Hi, I would to know the difference between the two int(10) and int (Used in create table ) Which one is better and why. Regards, -- Krishna Chandra Prajapati MySQL DBA,
