Re: Relational query question

[Divesh Kamra]

It better to LEFT join rather then NOT IN




On Wed, Sep 30, 2015 at 6:00 PM, Mogens Melander 
wrote:

> Maybe not the most optimal, but (probably) the most simple:
>
> SELECT * FROM fruit
> where id not in (select fruit from purchase
> where customer=1);
>
> 1, 'Apples'
> 3, 'Oranges'
>
>
> On 2015-09-30 00:01, Richard Reina wrote:
>
>> If I have three simple tables:
>>
>> mysql> select * from customer;
>> +++
>> | ID | NAME   |
>> +++
>> |  1 | Joey   |
>> |  2 | Mike   |
>> |  3 | Kellie |
>> +++
>> 3 rows in set (0.00 sec)
>>
>> mysql> select * from fruit;
>> ++-+
>> | ID | NAME|
>> ++-+
>> |  1 | Apples  |
>> |  2 | Grapes  |
>> |  3 | Oranges |
>> |  4 | Kiwis   |
>> ++-+
>> 4 rows in set (0.00 sec)
>>
>> mysql> select * from purchases;
>> ++-+--+
>> | ID | CUST_ID | FRUIT_ID |
>> ++-+--+
>> |  2 |  3 |   2   |
>> |  3 |  1 |   4   |
>> |  4 |  1 |   2   |
>> |  5 |  2 |   1   |
>> ++-+--+
>>
>> I am having trouble understanding a relational query. How can I select
>> those fruits that Joey has not purchased?
>>
>
> --
> Mogens
> +66 8701 33224
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: INTO OUTFILE ERROR...

[Divesh Kamra]

Check permission of user 'teal1dwd_teal' 

DK Sent from Phone

 On 19-Sep-2014, at 8:13 pm, Don Wieland d...@pointmade.net wrote:
 
 Hi gang,
 
 Trying to generate a CSV file using mySQL and I keep getting this error 
 below. The target folder has full permissions to write (777). I am not 
 certain why this is happening. Any answers? Thanks!
 
 
 Database_Class error: DATABASE_ERROR: Access denied for user 
 'teal1dwd_teal'@'localhost' (using password: YES) IN [SELECT u.user_id, 
 u.first_name AS u_first_name, 
 u.last_name AS u_last_name,
 c.client_id AS c_client_id,
 c.first_name AS c_first_name,
 c.middle_name AS c_middle_name,
 c.last_name AS c_last_name,
 c.address AS c_address,
 c.city AS c_city,
 c.state AS c_state,
 c.zip AS c_zip,
 c.dob AS dob_ymd,
 c.phone_home AS c_phone_home,
 c.phone_cell AS c_phone_cell,
 c.phone_work AS c_phone_work,
 c.email AS c_email,
 c.other_contacts AS c_other_contacts,
 count(*) as apt_qty
 
 FROM tl_appt apt 
 
 JOIN tl_clients c on c.client_id = apt.client_id 
 JOIN tl_rooms r on r.room_id = apt.room_id
 JOIN tl_users u on u.user_id = apt.user_id
 
 WHERE 
 
 apt.time_start between '1388552400' and '1420088399' 
 and r.location_id = '1'
 
 GROUP BY u.user_id, c.client_id 
 having count(*)  1
 
 ORDER BY u.first_name, u.last_name, c.last_name, c.first_name
 INTO OUTFILE '/tmp/1_2031305738.csv' 
 FIELDS TERMINATED BY ','
 ENCLOSED BY '']
 
 
 Don Wieland
 d...@pointmade.net
 http://www.pointmade.net
 https://www.facebook.com/pointmade.band
 
 
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: 1045 Error accessing mysql db

[Divesh Kamra]

check weather there any space in localhost
'localhost
'

Also check permission for 'foo' user though command

select * from mysql.user where 'foo';




On Thu, Aug 21, 2014 at 6:53 AM, Augori aug...@gmail.com wrote:

 After an operating system change (CentOS 5 to CentOS 6), my Python script
 could no longer connect with mysql database.  I get the following error...

 mysql_exceptions.OperationalError: (1045, Access denied for user 'foo'@
 'localhost
 ' (using password: YES))

 I have granted all on all databases to this user.
 I attempted to run mysql_upgrade.

 I am stymied because according to another guy, he is successfully accessing
 the same database with the same credentials as I am (but in a PHP script).

 Can any of you suggest an explanation for this?

 Thanks in advance!

Re: error 29, file not found (errcode: 13)

[Divesh Kamra]

Sol :- 
Change file owner to mysql.mysql 
  OR
Change folder and file mode to 777


DK Sent from Phone

 On 23-Jun-2014, at 8:22 pm, thufir hawat.thu...@gmail.com wrote:
 
 Apparently this error is because MySQL can't read my home directory?  Fair 
 enough, but I don't quite follow.  Where would be a good location for the CSV 
 file, then?
 
   thufir@dur:~$
   thufir@dur:~$ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 62
   Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)
   Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights
   reserved.
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input
   statement.
   mysql
   mysql LOAD DATA  INFILE '/home/thufir/make_year_model.csv' INTO
   TABLE vehicles.vehicles FIELDS TERMINATED BY ',' LINES TERMINATED BY
   '\n';
   ERROR 29 (HY000): File '/home/thufir/make_year_model.csv' not found
   (Errcode: 13)
   mysql
   mysql quit
   Bye
   thufir@dur:~$
   thufir@dur:~$ cat /home/thufir/make_year_model.csv
   make1,model1,2012,604,buy now
   make2,model2,2013,780,need to sell
   make3,model3,2001,780,cheap
   thufir@dur:~$
 
 
 
 thanks,
 
 Thufir
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: access problem for a particular table

[Divesh Kamra]

Hi 
Run mysql_upgrade command at OS shell 

DK Sent from Phone

 On 28-May-2014, at 1:40 pm, Lentes, Bernd 
 bernd.len...@helmholtz-muenchen.de wrote:
 
 Hi,
 
 we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't 
 access one particular table.
 Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command 
 denied to user 'backup'@'localhost' for table 'cond_instances' when using 
 LOCK TABLES.
 
 root has these rights:
 
 ++
 | Grants for root@localhost   
   
|
 ++
 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 
 '*948BD740C15428999D549B9632F3C432415E93A4' WITH GRANT OPTION |
 | GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'root'@'localhost' 
 WITH GRANT OPTION|
 | GRANT SELECT ON `vectordb%`.* TO 'root'@'localhost' 
   
  |
 | GRANT USAGE ON `performance_schema`.`cond_instances` TO 'root'@'localhost' 
 WITH GRANT OPTION |
 | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
|
 ++
 
 backup has these rights:
 
 ++
 | Grants for backup@localhost 
|
 ++
 | GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost' 
 IDENTIFIED BY PASSWORD '*1827DC630AAEB1E997DB2B212CC94EFD9C431555' |
 | GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'backup'@'localhost' 
|
 | GRANT SELECT ON `vectordb%`.* TO 'backup'@'localhost'   
|
 ++
 
 If I try to specify (as root) exactly the select and lock tables right to 
 user backup, I get the following error:
 mysql grant select, lock tables on performance_schema.cond_instances to 
 'backup'@'localhost';
 ERROR 1142 (42000): SELECT,GRANT,LOC command denied to user 
 'root'@'localhost' for table 'cond_instances'
 
 What I understood is that the usage right for root on 
 performance_schema.cond_instances means no rights.
 Trying to revoke seems to work:
 mysql revoke usage on performance_schema.cond_instances from 
 'root'@'localhost';
 Query OK, 0 rows affected (0.00 sec)
 
 But the usage right remains, it does not disappear.
 How can I grant these rights to user backup ?
 
 Thanks for any hint.
 
 Bernd
 
 
 --
 Bernd Lentes
 
 Systemadministration
 Institut für Entwicklungsgenetik
 Gebäude 35.34 - Raum 208
 HelmholtzZentrum münchen
 bernd.len...@helmholtz-muenchen.de
 phone: +49 89 3187 1241
 fax:   +49 89 3187 2294
 http://www.helmholtz-muenchen.de/idg
 
 Die Freiheit wird nicht durch weniger Freiheit verteidigt
 
 
 
 Helmholtz Zentrum München
 Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
 Ingolstädter Landstr. 1
 85764 Neuherberg
 www.helmholtz-muenchen.de
 Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
 Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen
 Registergericht: Amtsgericht München HRB 6466
 USt-IdNr: DE 129521671
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: mysql Access denied error

[Divesh Kamra]

Edward , use following way

mysql -uroot -p -h{host ip }

else  give grant  privileges to localhost



On Mon, May 5, 2014 at 7:47 AM, EdwardKing zhan...@neusoft.com wrote:

 I use mysql to create a database and grant rights to a user
 hadooptest,then I use hadooptest to login mysql and use the database,but it
 failed. Why raise error after I grant rights? How to solve it? Thanks.

 My operation is follows:

 [hadoop@master ~]$ mysql -h localhost -u root -p
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 3
 Server version: 5.5.37-log MySQL Community Server (GPL)

 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights
 reserved.

 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

 Type 'help;' or '\h' for help. Type '\c' to clear the current input
 statement.

 mysql create database hadooptest;
 mysql grant all on hadooptest.* to 'hadoopuser'@'%' identified by
 '123456';
 Query OK, 0 rows affected (0.00 sec)

 mysql flush priviledges;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
 that corresponds to your MySQL server version for the right syntax to use
 near 'priviledges' at line 1
 mysql flush privileges;
 Query OK, 0 rows affected (0.00 sec)

 mysql quit;
 Bye

 Then I use hadoopuser  with password 123456
 [hadoop@master ~]$ mysql -u hadoopuser -p
 Enter password:
 ERROR 1045 (28000): Access denied for user 'hadoopuser'@'localhost'
 (using password: YES)



 ---
 Confidentiality Notice: The information contained in this e-mail and any
 accompanying attachment(s)
 is intended only for the use of the intended recipient and may be
 confidential and/or privileged of
 Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader
 of this communication is
 not the intended recipient, unauthorized use, forwarding, printing,
  storing, disclosure or copying
 is strictly prohibited, and may be unlawful.If you have received this
 communication in error,please
 immediately notify the sender by return e-mail, and delete the original
 message and all copies from
 your system. Thank you.

 ---

Re: database perfomance worries

[Divesh Kamra]

Andy , increase innodb_log_file size to 400mb

DK Sent from Phone

On 03-Jul-2013, at 23:39, Rick James rja...@yahoo-inc.com wrote:

 Set innodb_buffer_pool_size to 70% of _available_ ram.  That may be 11G on 
 your 16GB machine, unless you have a lot of other bulky stuff there.  Do 
 _not_ make it so large that it leads to swapping.  Swapping is much worse on 
 performance than shrinking the buffer_pool.
 
 36 seconds for a single-row UPDATE using the PRIMARY KEY -- Something else 
 _must_ have been interfering.  DELETE was suggested; ALTER is another 
 possibility.  Even with a totally cold cache, that UPDATE should have taken 
 much less than one second.  I suspect the problem will not recur.
 
 KEY `status` (`status`),
 That index will probably never be used, due to low cardinality.  Either DROP 
 it, or make it 'compound'.
 
 `flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
 Consider the SET datatype.
 
 5.6 has some performance improvements, but not related to this query.
 
 Please have the slowlog turned on.  There could be extra, useful, info in it.
 
 
 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, July 02, 2013 7:28 PM
 To: Singer Wang
 Cc: Andy Wallace; mysql list
 Subject: Re: database perfomance worries
 
 
 We are on a quest to improve the overall performance of our database.
 It's
 generally
 working pretty well, but we periodically get big slowdowns for no
 apparent
 reason. A
 prime example today - in the command line interface to the DB, I
 tried to update one record, and got:
 
   mysql update agent set number_of_emails = 5 where acnt =
 'AR287416';
   Query OK, 1 row affected (36.35 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
 
 36 seconds to update one table? The primary key is `acnt`. If I run
 the same (basic)
 
 Check if there is some DELETE running for the selected table.
 
 If there is a DELETE involving whole table it might be locking up
 database.
 
 Look into mysql-slow.log
 
 Try to optimize your application queries with EXPLAIN.
 
 
 [!!] InnoDB data size / buffer pool: 7.8G/5.5G Variables to adjust:
 innodb_buffer_pool_size (= 7G)
 
 2 GB innodb_buffer_pool is a joke for a dataset of 33 GB
 
 that leads in permanently I/O on mixed load at the chances are high
 that there are times where nothing needed to operate is in the
 buffer_pool and on concurrent load mysqld ends in repeatly swap data in
 and out of the pool
 
 at least all repeatly accessed tables should fit permanently in the
 buffer
 
 it depends on the load and how much data you're acquiring.
 
 if you have 33GB in total, but only using few same tables in total size of
 less than 2GB at the same time it would work just fine.
 
 for example I have 136GB of data, but my buffer is only about 10Gb, but
 most of the queries work just fine (I'm using it for mostly read-only
 things).
 
 but ofc, you need to check your system usage, if mysqld swaps its a bad
 thing and most likely you need to either upgrade your hardware or consider
 checking your data architecture (i.e. use LIMIT for quieries, add more
 indexes, split large tables for a smaller ones which you really update or
 store large data in mongodb etc).
 
 
 
 command again a few seconds later, I get:
 
   mysql update agent set number_of_emails = 15 where acnt =
 'AR287416';
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
 
 Why would we be getting such huge variations? We're running Solaris
 10 on i386, with
 4 processors and 16GB of memory, MySQL 5.1.46-log. We are working
 out a plan to upgrade to MySQL 5.6, but I certainly don't want to
 depend on that upgrade to solve all performance problems.
 
 CREATE TABLE `agent`
 (
 `acnt` varchar(20) NOT NULL,
 `passwd` varchar(20) NOT NULL,
 `package` char(2) DEFAULT NULL,
 `data_template` varchar(20) DEFAULT 'NULL',
 `default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
 `status` enum('A','T','P','C','D','X','**S') NOT NULL
 `flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
 `aliases` varchar(4000) NOT NULL DEFAULT '',
 `offices` varchar(4000) NOT NULL DEFAULT '',
 `license_no` varchar(40) NOT NULL DEFAULT '',
 `agent_code` varchar(20) DEFAULT NULL,
 `office_code` varchar(20) DEFAULT NULL,
 `parent_acnt` varchar(20) DEFAULT NULL,
 `number_of_agentlinks` int(11) DEFAULT NULL,
 `number_of_emails` int(11) DEFAULT NULL,
 `fname` varchar(30) DEFAULT NULL,
 `lname` varchar(30) DEFAULT NULL,
 
 whole bunch of other fields
 
 PRIMARY KEY (`acnt`),
 KEY `parent_acnt` (`parent_acnt`),
 KEY `status` (`status`),
 KEY `email` (`email`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
 --
 Andy Wallace
 iHOUSEweb, Inc.
 awall...@ihouseweb.com
 (866) 645-7700 ext 219
 --
 Sometimes it pays to stay in bed on Monday, rather than spending
 the
 rest
 of the week debugging Monday's code.
 - Christopher Thompson
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:

Re: space gone after MyISAM REPAIR TABLE

[Divesh Kamra]

You can use disable Key before loading data  use enable Key  after loading 

Command :- alter table {table. Name } disable key;

Same enabling 

DK Sent from Phone

On 27-Jun-2013, at 4:57, nixofortune nixofort...@gmail.com wrote:

 This is my table:
 CREATE TABLE `ga_monthly_keyword_visits` (
  `site_id` int(11) DEFAULT NULL,
  `index_date` int(11) DEFAULT NULL,
  `index_month` int(11) NOT NULL,
  `index_year` int(11) NOT NULL,
  `keyword` varchar(128) DEFAULT NULL,
  `source` varchar(30) DEFAULT NULL,
  `visits` int(11) DEFAULT NULL,
  `bounced_visits` int(11) DEFAULT NULL,
  `transactions` int(11) DEFAULT NULL,
  `revenue` float(10,2) DEFAULT NULL,
  `value_per_click` float(10,2) DEFAULT NULL,
  `conversions` int(11) DEFAULT NULL,
  `goal_value` float(10,2) DEFAULT NULL,
  KEY `idx_bounced_visits` (`site_id`,`index_date`),
  KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
 
 Was changed into:
 
 CREATE TABLE `ga_monthly_keyword_visits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) DEFAULT NULL,
  `index_date` int(11) DEFAULT NULL,
  `index_month` int(11) NOT NULL,
  `index_year` int(11) NOT NULL,
  `keyword` varchar(128) DEFAULT NULL,
  `source` varchar(30) DEFAULT NULL,
  `visits` int(11) DEFAULT NULL,
  `bounced_visits` int(11) DEFAULT NULL,
  `transactions` int(11) DEFAULT NULL,
  `revenue` float(10,2) DEFAULT NULL,
  `value_per_click` float(10,2) DEFAULT NULL,
  `conversions` int(11) DEFAULT NULL,
  `goal_value` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
  KEY `idx_bounced_visits` (`site_id`,`index_date`),
  KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 I tried Converting like this:
 
   CREATE TABLE new LIKE old;
   ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement 
 or a 'natural' compound PK), ENGINE=InnoDB;
   INSERT INTO new SELECT site_id, ..., goal_value FROM old;
   ALTER TABLE new ADD INDEX (...);
 
 With only difference The original MyISAM table crashed and I took it from 
 backup, loading by LOAD DATA INFILE.
 The problem, yes It loaded much quicker into Database 4H 16M to be precise, 
 but ALTER TABLE new ADD INDEX (...);
 Put server into meditation mode. High I/O Wait rendered box unusable. I had 
 to interrupt the ALTER ADD KEY process after 5H of struggle.
 
 Now importing with Keys in place. It takes longer, much longer but at least 
 the server is working and customers do not complaint.
 Schema design is awful, agree. I try to understand the process so will 
 redesign it soon, but any suggestions are welcome.
 I' not a MySQL super guru so will be glad for hear your sorts, guys.
 Thanks
 
 
 On 27/06/13 00:04, Rick James wrote:
 (`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`,
  
 `bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`);
 May we see the SHOW CREATE TABLE?  Some of this smells bad.
 * It is almost always bad to split day/month/year into multiple fields.
 * Often a fact table, which this sounds like, should not have extra 
 indexes.
 * Is each datatype as small as is practical?
 * Are any of the fields VARCHAR, and could be 'normalized'?
 
 I would expect this to the fastest way to convert (assuming you have the 
 disk space):
CREATE TABLE new LIKE old;
ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement 
 or a 'natural' compound PK), ENGINE=InnoDB;
INSERT INTO new SELECT site_id, ..., goal_value FROM old;
ALTER TABLE new ADD INDEX (...);
 
 What version of MySQL are you running?  Newer versions do the ALTER TABLE 
 faster (online??), and may require you to do one add at a time.
 
 Another issue...
 If the data in `old` is in the same order as the PRIMARY KEY of `new`, then 
 INSERT..SELECT will run fast.  (No need to jump around to find where to put 
 each row.)
 Case 1:  You are adding an AUTO_INC -- it will be in the 'right' order.
 Case 2:  The new PK is approximately the order of the insertions into `old` 
 -- probably run fast.  (However, I do not see a likely natural PK that would 
 allow this
 
 INSERT ... SELECT...ORDER BY (new PK) -- This would make the INSERT part 
 fast, but the SELECT part would be slow.  (You can't win)
 
 Your task is all about disk hits.  By understanding what MySQL has to do, 
 you can 'predict' whether a plan will be slow or slower.
 
 Back to the secondary indexes...
 What are the SELECTs that will benefit from them?  (Sometimes discussing 
 this can lead to fewer/better INDEXes.  Often it leads to suggesting Summary 
 Table(s).)
 
 
 -Original Message-
 From: Johan De Meersman [mailto:vegiv...@tuxera.be]
 Sent: Wednesday, June 26, 2013 11:46 AM
 To: li...@netrogenic.com; Jay Ess; mysql@lists.mysql.com
 Subject: Re: space gone after MyISAM REPAIR TABLE
 
 You can't actually move innodb tables around until 5.6 where you have
 transpotable 

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Divesh Kamra]

Hi Reindi


Thanks for solution .

Can u share complete steps ? 

R's
DK

On 20-Feb-2013, at 2:50, Reindl Harald h.rei...@thelounge.net wrote:

 surely
 
 * use mysql_upgrade -u root -p after EACH update
 * upgrade regulary
 
 we went from MySQL 3.x to 5.5.30 until know without
 any dump and here are around 5000 tables
 
 Am 19.02.2013 22:12, schrieb Divesh Kamra:
 Is there any better way for grade MySQL version without taking backup with 
 mysqldump
 
 Or if there any tool for this 
 
 R's
 DK
 
 On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote:
 
 Am 16.02.2013 09:42, schrieb Manuel Arostegui:
 2013/2/15 Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net
 
   our database is 400 GB, mysqldump is 600MB was not a typo and you
   honestly believed that you can import this dump to somewhat?
 
   WTF - as admin you should be able to see if the things in front
   of you are theoretically possible before your start any action
   and 1:400 is impossible, specially because mysql-dumps are
   ALWAYS WAY LARGER then the databasses because they contain
   sql-statements and not only data
 
 That's not completely true. If you have a poor maintained database or just 
 tables with lot of writes and deletes
 and you don't periodically optimize it - you can end up with lot of blank 
 spaces in your tables which will use _a
 lot_ of space. If you do a du or whatever to measure your database 
 size...you can get really confused.
 mysqldump obviously doesn't backup blank spaces and once you get rid of 
 them, your database will use much less space.
 
 ok, normally i expect there is a admin and doing his job
 especially for large datasets
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Divesh Kamra]

Hi all


Is there any better way for grade MySQL version without taking backup with 
mysqldump

Or if there any tool for this 

R's
DK

On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote:

 
 
 Am 16.02.2013 09:42, schrieb Manuel Arostegui:
 2013/2/15 Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net
 
our database is 400 GB, mysqldump is 600MB was not a typo and you
honestly believed that you can import this dump to somewhat?
 
WTF - as admin you should be able to see if the things in front
of you are theoretically possible before your start any action
and 1:400 is impossible, specially because mysql-dumps are
ALWAYS WAY LARGER then the databasses because they contain
sql-statements and not only data
 
 That's not completely true. If you have a poor maintained database or just 
 tables with lot of writes and deletes
 and you don't periodically optimize it - you can end up with lot of blank 
 spaces in your tables which will use _a
 lot_ of space. If you do a du or whatever to measure your database 
 size...you can get really confused.
 mysqldump obviously doesn't backup blank spaces and once you get rid of 
 them, your database will use much less space.
 
 ok, normally i expect there is a admin and doing his job
 especially for large datasets
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication problem

[divesh kamra]

HI

share detail

   Slave_IO_Running: Yes/No
  Slave_SQL_Running: Yes/No
 Last_IO_Errno: 0
  Last_IO_Error:
   Last_SQL_Errno: 0
   Last_SQL_Error:



On Tue, Dec 4, 2012 at 5:59 AM, Rick James rja...@yahoo-inc.com wrote:

 SHOW SLAVE STATUS\G
 SHOW MASTER STATUS;
 What directory are the binlogs in?


  -Original Message-
  From: Michael Dykman [mailto:mdyk...@gmail.com]
  Sent: Monday, December 03, 2012 8:53 AM
  To: trimur...@tulassi.com
  Cc: mysql@lists.mysql.com
  Subject: Re: replication problem
 
  Trimurthy,
 
  you will have to describe the method you are using to setup
  replication.  The error message seems plain but an observer could not
  reasonably guess what caused it without more information.
 
   - michael dykman
 
 
  On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com
  wrote:
   hi list,   i am trying to set up replication but i couldn't
   complete because of the following error Could not find first log file
   name in binary log index file can any one please help me.
  
  
  
  
  
  
  
 Normal
 0
  
  
  
  
 false
 false
 false
  
 EN-US
 X-NONE
 AR-SA
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
   Thanks  Kind Regards,
  
TRIMURTHY
 
 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL Replication Error

[divesh kamra]

Hi

first check this thought application end

or

There is another way

slave-skip-errors=1062 --- in my.cnf and restart mysql


On Sat, Dec 1, 2012 at 4:30 AM, Reindl Harald h.rei...@thelounge.netwrote:



 Am 30.11.2012 23:52, schrieb Rick James:
  Possible causes:
  * Someone is writing to the Slave

 that is why the option read-only exists for my.cnf

  * The Slave was not in synch with the Master.
  * Schemas are different between Master and Slave

 should not happen if the slave is properly cloned
 as a binary copy of the stopped master and secured
 with read-only

Re: Basic SELECT help

[divesh kamra]

Hi

Is there is performance issue from this query on more then 5-10 million data





On Fri, Nov 23, 2012 at 11:17 AM, Mogens Melander mog...@fumlersoft.dkwrote:

 Ok, to make up for my bad joke, here's the answer
 to the original question.

 DROP TABLE IF EXISTS `test`.`atest`;
 CREATE TABLE  `test`.`atest` (
   `id` int(10) unsigned NOT NULL,
   `type` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 insert into atest(id,type)
 values(1000,5)
 ,(1001,5)
 ,(1002,2)
 ,(1001,2)
 ,(1003,2)
 ,(1005,2)
 ,(1006,1);

 SELECT DISTINCT id
 FROM atest
 WHERE `type` = 2 OR `type` = 5
 GROUP BY id
 HAVING count(DISTINCT `type`) = 2;

 On Thu, November 22, 2012 22:16, Michael Dykman wrote:
  Mogens,
 
  Platform could not be less relevant to a question of MySql syntax.
  The techniques we have been discussing have been available to every
  version of MySql post v3.23 and the class/job function he is applying
  it to is neither relevant to the problem nor any of our business,
  unless he volunteers to share it. Excepting only the working
  assumption that he is using a MySql version released in this century,
  I don't know how this would have informed my analysis or response.
 
   - michael dykman
 
  On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander mog...@fumlersoft.dk
  wrote:
 
  On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
  Basically I only what to return the IDs that have both types.
 
 
  And that's exactly what below statement will return.
 
  You forgot to include what platform you are on,
  which version of MySQL you are running and
  what class you are attending.
 
  All necessary information to provide a sufficient help.
 
 
  On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
  marek.gutow...@gmail.comwrote:
 
  SELECT DISTINCT id FROM table WHERE type IN ('2','5')
 
  should work
 
 
  On 22 November 2012 14:30, Neil Tompkins
  neil.tompk...@googlemail.comwrote:
 
  Hi,
 
  I'm struggling with what I think is a basic select but can't think
  how
  to
  do it : My data is
 
  id,type
 
  1000,5
  1001,5
  1002,2
  1001,2
  1003,2
  1005,2
  1006,1
 
  From this I what to get a distinct list of id where the type equals 2
  and
  5
 
  Any ideas ?
 
  Neil
 
 
 
 
  --
  This message has been scanned for viruses and
  dangerous content by MailScanner, and is
  believed to be clean.
 
 
 
 
  --
  Mogens Melander
  +66 8701 33224
 
  --
  This message has been scanned for viruses and
  dangerous content by MailScanner, and is
  believed to be clean.
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 
  --
  This message has been scanned for viruses and
  dangerous content by MailScanner, and is
  believed to be clean.
 
 


 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Assistance with replication

[divesh kamra]

Hi Machiel


use following steps

1) make binary log into readable format through mysqlbinlog
 mysqlbinlog -u{user} -p{password}  log.log(this any text
file)

2) read text file check next position just after position where replication
is stuck

3) run the *change master* command on both slave with that position



This will help to run replication





On Mon, Nov 12, 2012 at 11:33 PM, Igor Shevtsov nixofort...@gmail.comwrote:

 Hi Michael,
 Is your binary log running on the slaves?
 I have the same issue when forgotten enabling log_bin on SLAVE host
 Thanks,
 Igor




 On 12/11/12 13:04, Machiel Richards - Gmail wrote:

 Hi Guys

 We need some assistance with a master slave setup we are
 experiencing some issues with.

 We have one master with 2 slaves replicating from it.

 Both the slaves stopped working this morning in the early
 hours at exactly the same bin_log file and position.

 We have since restarted the slave servers and when you run a
 show slave status the seconds behind master stays 0.


 Upon closer investigation, the Relay_Master_Log_File and
 Exec_Master_Log_Pos does not change, although all other values are
 changing.

 No errors are being logged, and there is enough disk space on
 the server so it can't be space related.



 Can anyone suggest some things we can check or do in order to
 get replication sorted and to avoid future issues like this?


 regards
 Machiel



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql