Re: Estimate mysqldump size
Ronan McGlue writes:
> Hi Olivier,
>
> On 28/11/2018 8:00 pm, Olivier wrote:
>> Hello,
>>
>> Is there a way that gives an estimate of the size of a mysqldump such a
>> way that it would always be larger than the real size?
>>
>> So far, I have found:
>>
>> mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
>>information_schema.tables WHERE table_schema NOT IN
>>('information_schema','performance_schema','mysql');
>>
>> but the result may be smaller than the real size.
>
> In the above example, you also need to account for index_length, eg
>
> mysql> select round(SUM(data_length+index_length)/POWER(1024,2),1)
> Total_MB,round(SUM(data_length)/POWER(1024,2),1)
> data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB FROM
> information_schema.tables where TABLE_SCHEMA not in (
> "information_schema", "performance_schema", "mysql") ;
> +--+-+--+
> | Total_MB | data_MB | index_MB |
> +--+-+--+
> | 4546.0 | 4093.7 | 452.2 |
> +--+-+--+
> 1 row in set (0.00 sec)
Thanks.
> However, this doesn't 100% map to OS file size ( if using innodb file
> per table ) and will likely never be 100% accurate to what the OS
> reports, due to fragmentation etc.
>
>>
>> I am writting a program that takes the result of mysqldump and pipe it
>> in a tar file.
>
> A typical global mysqldump ( ie taken with -A ) will be a single file.
> Why are you then wanting to pipe this to a tar archive?
The tar file will be part of Amanda backup. On a full backup, it should
have the mysqldump and on incremental backups it should have the binary
logs.
Having everything in a tar file makes it very consistent and easy to
deal with in case of catastrophic failure (like everything is lost
except the tape, the backup can still be extracted by hand on a live
CD/single user system as it is all tar).
Amanda will also take care of the compression.
> Its also common for mysqldump to be compressed via a pipe due to the
> nature of the output file created ( eg text files compress *very* well )
> , to then be sent across the network , eg via ssh
>
> mysqldump -u.. -p -A | gzip > schema.sql.gz
>
>
> Aside from your stated goal of piping to tar, if we can step back a
> level briefly - what are you trying to achieve here?
A plugin for Amanda. I think a commercial solution exist, I don't need
anything very fancy, so I am trying to come up with my own solution.
Best regards,
Olivier
>
>> Tar file format has the size in the header, before the
>> data and if the size of the dump is bigger than the size declared in the
>> header, tar does not like that (if the size of the dump is smaller than
>> the actual size, it can be padded with spaces).
>>
>> So, the estimate must be larger than the actual dump, how to acheive
>> that?
>
> It wont be anything other than an estimate , however it should still be
> reasonably close if you arent doing a *lot* of dml on it.
>
> You could artificially inflate the expected size by ,eg multiplying by
> 1.1x or 1.2x , however there will always be an edge case table which
> will be greater still..
>
>
> Regards
>
> Ronan McGlue
>
> MySQL Support
>
>
>
>>
>> Thanks in advance,
>>
>> Olivier
>>
>>
>
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Estimate mysqldump size
Ronan McGlue writes:
> Hi Olivier,
>
> On 28/11/2018 8:00 pm, Olivier wrote:
>> Hello,
>>
>> Is there a way that gives an estimate of the size of a mysqldump such a
>> way that it would always be larger than the real size?
>>
>> So far, I have found:
>>
>> mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
>>information_schema.tables WHERE table_schema NOT IN
>>('information_schema','performance_schema','mysql');
>>
>> but the result may be smaller than the real size.
>
> In the above example, you also need to account for index_length, eg
But I thought I had read that indexes are not saved by a myslqdump, but
recreated on a restore?
Thanks in advance,
Olivier
>
> mysql> select round(SUM(data_length+index_length)/POWER(1024,2),1)
> Total_MB,round(SUM(data_length)/POWER(1024,2),1)
> data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB FROM
> information_schema.tables where TABLE_SCHEMA not in (
> "information_schema", "performance_schema", "mysql") ;
> +--+-+--+
> | Total_MB | data_MB | index_MB |
> +--+-+--+
> | 4546.0 | 4093.7 | 452.2 |
> +--+-+--+
> 1 row in set (0.00 sec)
>
> However, this doesn't 100% map to OS file size ( if using innodb file
> per table ) and will likely never be 100% accurate to what the OS
> reports, due to fragmentation etc.
>
>>
>> I am writting a program that takes the result of mysqldump and pipe it
>> in a tar file.
>
> A typical global mysqldump ( ie taken with -A ) will be a single file.
> Why are you then wanting to pipe this to a tar archive?
>
> Its also common for mysqldump to be compressed via a pipe due to the
> nature of the output file created ( eg text files compress *very* well )
> , to then be sent across the network , eg via ssh
>
> mysqldump -u.. -p -A | gzip > schema.sql.gz
>
>
> Aside from your stated goal of piping to tar, if we can step back a
> level briefly - what are you trying to achieve here?
>
>> Tar file format has the size in the header, before the
>> data and if the size of the dump is bigger than the size declared in the
>> header, tar does not like that (if the size of the dump is smaller than
>> the actual size, it can be padded with spaces).
>>
>> So, the estimate must be larger than the actual dump, how to acheive
>> that?
>
> It wont be anything other than an estimate , however it should still be
> reasonably close if you arent doing a *lot* of dml on it.
>
> You could artificially inflate the expected size by ,eg multiplying by
> 1.1x or 1.2x , however there will always be an edge case table which
> will be greater still..
>
>
> Regards
>
> Ronan McGlue
>
> MySQL Support
>
>
>
>>
>> Thanks in advance,
>>
>> Olivier
>>
>>
>
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Estimate mysqldump size
Hello,
Is there a way that gives an estimate of the size of a mysqldump such a
way that it would always be larger than the real size?
So far, I have found:
mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql');
but the result may be smaller than the real size.
I am writting a program that takes the result of mysqldump and pipe it
in a tar file. Tar file format has the size in the header, before the
data and if the size of the dump is bigger than the size declared in the
header, tar does not like that (if the size of the dump is smaller than
the actual size, it can be padded with spaces).
So, the estimate must be larger than the actual dump, how to acheive
that?
Thanks in advance,
Olivier
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Fwd: Enabled memcached Plugin on mysql
-- Forwarded message -- From: Olivier Morel <olivierm...@gmail.com> Date: 2016-03-09 13:35 GMT+01:00 Subject: Enabled memcached Plugin on mysql To: mysql@lists.mysql.com i've installed mysql5.6 on debian 8 , i've also enabled daemon_memcached. After enabled memcached i restart mysql , but when i type netstat -tnpl i didn't see mysql listen on port 11211 ?? tcp0 0 127.0.0.1:6011 0.0.0.0:* LISTEN 1599/1 tcp0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2577/mysqld tcp0 0 0.0.0.0:22 0.0.0.0:* LISTEN 506/sshd tcp0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1455/0 tcp6 0 0 ::1:6011 :::* LISTEN 1599/1 tcp6 0 0 :::22 :::* LISTEN 506/sshd tcp6 0 0 ::1:6010 :::* LISTEN 1455/0 mysql-apt-config0.6.0-1 mysql-client 5.6.29-1debian8 mysql-common 5.6.29-1debian8 mysql-community-client5.6.29-1debian8 mysql-community-server5.6.29-1debian8 mysql-server 5.6.29-1debian8 mysql-server- 5.55.5.46-0+deb8u1 mysql-server- 5.65.6.28-1 My question is do i need to install the application memcache ? -- Cordialement Olivier Morel
Re: does anyone else have problems sending mails to this list ?
Bernd, > <mysql@lists.mysql.com>: host lists-mx.mysql.com[137.254.60.71] said: 552 spam > score exceeded threshold (#5.6.1) (in reply to end of DATA command) > > Any ideas ? Whatever provider you are using to send mail has been blacklisted? It happens with some ISP who are not diligent enough to remove the spammers from their networks, legitimate customers get penalized. Best regards, Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: AW: Select one valuebut not the other
Axel, Simply translated: select * from table t1 where t1.data_value=1 AND not exists(select * from table t2 where t2.data_value=2 and t2.item_number = t1.item_number) Yes, but with t1 and t2 the same table. best regards, Olivier Axel Diehl __ GIP Exyr GmbH Hechtsheimer Str. 35-37 | 55131 Mainz Tel: +49 (0) 6131 / 80124 - 46 | Fax: +49 (0) 6131 / 80124 - 24 E-Mail: axel.di...@gip.com | Web: www.gip.com http://www.gip.com/ Geschäftsführer: Dr. Bernd Reifenhäuser, Dr. Alexander Ebbes Handelsregister: HRB 6870 - Amtsgericht Mainz -Ursprüngliche Nachricht- Von: Olivier Nicole [mailto:olivier.nic...@cs.ait.ac.th] Gesendet: Mittwoch, 29. April 2015 07:21 An: mog...@fumlersoft.dk Cc: mysql@lists.mysql.com Betreff: Re: Select one valuebut not the other SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- 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: Select one valuebut not the other
Thank you, SELECT * FROM test WHERE item_number in (SELECT item_number FROM test where data_value=1) AND item_number not in (SELECT item_number FROM test where data_value = 2); That did it. Olivier On Wed, April 29, 2015 07:20, Olivier Nicole wrote: SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- 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: Select one valuebut not the other
Lucio, I have a table where each record is made of one item_number and one data_value. You do not have any other column ? In particular you do not have any unique key record identifier ? All my tables have a column with a record sequence number seq int NOT NULL AUTO_INCREMENT which is also a key KEY auxiliary(seq). This is useful a posteriori to locate particular records. I do, but that was irrelevant to my question, as it is only counting the records, it carries no information. What is the command to select all the records where an item_number has the data 1 but not the data 2? 1) by select you mean display at the terminal using the mysql line mode client, or locate all affected records for further work ? I meant SELECT command, so a display I guess (but that would be the same select in Perl). 2) am I getting it correctly that you want to locate all the cases where a given item_number (any) has JUST ONE occurrence in the table ? In the line mode client this can be easily done with an additional table, which can be a temporary table. My idea was to do it in one single command, without using additional table. I ended up with something along the line of: select handle, text_value from metadatavalue, handle where item_id in (select item_id from metadatavalue where metadata_field_id=64) and item_id not in (select item_id from metadatavalue where metadata_field_id=27) and metadata_field_id=64 and handle.resource_id=item_id and resource_type_id=2 order by item_id; Maybe not the fastest nor the nicest, but as I need to run it only once, it is enought. Thank you, Olivier Consider e.g. the following table (it has two columns, no seq column, and nothing else ... actually it is a table of seq pointers in two other tables) select * from north33w1t7_ | north33 | w1t7 | +-+--+ | 21 |1 | | 21 |2 | | 24 | 20 | create temporary table temp1 select north33,count(*) as c from north33w1t7_ group by north33 order by north33; temp1 will contain something like this | north33 | c | +-+---+ | 21 | 2 | | 24 | 1 | so it will tell you that item 21 has 2 counteparts, while item 24 has 1 counterpart. If you want to select (display) all cases in the main table with 1 counterpart do select north33w1t7_.* from temp1 join north33w1t7_ on temp1.north33=north33w1t7_.north33 where c=1 : | north33 | w1t7 | +-+--+ | 24 | 20 | | 200013 | 93 | A different story would be if you want always to extract ONE record from the main table, the single one if c=1, and the FIRST one if c1. What you define first it is up to you (the smallest data_value, the highest data_value, a condition on other columns). Here in general I use a trick which involves one or two temporary tables and a variable. I initialize the variable to zero (or a value which is not represented in the table, which shall be ordered on the columns as you need. Then I test whether the item_number is the same as the variable, if not I declare it to be first, then reset the variable in the same select statement. set @x:=0; select north33w1t7_.*, if(@xnorth33w1t7_.north33,'FIRST','no') as flag, @x:=north33w1t7_.north33 from temp1 join north33w1t7_ on temp1.north33=north33w1t7_.north33 where c1 order by north33,w1t | north33 | w1t7 | flag | @x:=north33w1t7_.north33 | +-+--+---+--+ | 21 |1 | FIRST | 21 | | 21 |2 | no| 21 | | 22 |8 | FIRST | 22 | | 22 |9 | no| 22 | I can then save this select to a temporary table, and take my pick where flag='FIRST'. of course you can also do without the join with temp1 if you want either the single or the first (i.e. c=1 and c1) select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from north33w1t7_ order by north33,w1t7 | north33 | w1t7 | flag | @x:=north33 | +-+--+---+-+ | 21 |1 | FIRST | 21 | | 21 |2 | no| 21 | | 22 |8 | FIRST | 22 | | 22 |9 | no| 22 | | 24 | 20 | FIRST | 24 | -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html Do not like Firefox =29 ? Get Pale Moon ! http://www.palemoon.org -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Select one valuebut not the other
Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Select one valuebut not the other
SELECT * FROM table WHERE item_number=1; Sorry if my question was not clear: what I am looking for is: SELECT * FROM table WHERE data_value=1 AND there is not any reccord with the same item_number and data_value=2 Olivier On Wed, April 29, 2015 06:30, Olivier Nicole wrote: Hi, I am sure that it is feasible with MySQl, and I am sure that's a newbie question, but my SQL skills are limited... I have a table where each record is made of one item_number and one data_value. If one item has several values, there are several records with the same item_number and a different data_value. What is the command to select all the records where an item_number has the data 1 but not the data 2? Thanks in advance, Olivier -- 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: Help with REGEXP
Paul, You could look for a tool called The Regex Coach. While it is mainly for Windows, it runs very well in vine. I fijd it highly useful to debug regexps. Best regards, Olivier -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL error to syslog
HI, I am running MySQL 5.5.31 on FreeBSD 9.2. I have a web server with a miss-configured service that generates faulty connections. After a while, MySQl blocks any connection from the web server. At some stage, I had set-up a script that would browse syslog log and look for a string like Host 'xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' and would then issue a mysqladmin flush-hosts. But after an upgrade, MySQl stopped reporting to syslog. MySQL process is: databaseroot: ps auwwx | grep mysql mysql 81063 0.0 0.1 9852 1172 ?? Is3Dec14 0:00.18 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/database/mysql/my.cnf --user=mysql --datadir=/database/mysql --pid-file=/database/mysql/database.cs.ait.ac.th.pid --syslog mysql 81386 0.0 2.0 314712 42180 ?? I 3Dec14 8:19.75 /usr/local/libexec/mysqld --defaults-extra-file=/database/mysql/my.cnf --basedir=/usr/local --datadir=/database/mysql --plugin-dir=/usr/local/lib/mysql/plugin --user=mysql --pid-file=/database/mysql/database.cs.ait.ac.th.pid --socket=/tmp/mysql.sock --port=3306 Any help will be greatly appreciated. TIA, Olivier -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Error#: 2002
I have about 60 websites based on mysql and php. Suddenly they have all gone blank, just white pages. The files are still on the server and I can see the tables in all the databases via myphpadmin interfact. I'm not getting any response from hosting gods yet. When I try to connect to server via Dreamweaver, the error message is: MySQL Error#: 2002 Can't connect to local MySQL server through socket '/var/lib/myswl/mysql.sock'(111). I have one site that uses a different IP number that that site is ok. My static sites, ie, no database inclusion, are ok. Any ideas what to look for, most appreciated. Regards, Patrice Olivier-Wilson 828-628-0500 http://Biz-comm.com b...@biz-comm.com Everything will be alright in the end, so if it is not alright, it is not yet the end. - Quote from movie: The Best Exotic Marigold Hotel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
List archive?
I have a question that this list solved a year ago, and I can't remember what the solution was. Does this list have an archive? (Sorry if it is on one of the links to the list, but I don't have any on hand at the moment.) -- Patrice Olivier-Wilson http://biz-comm.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: List archive?
On 12/14/10 12:51 PM, Patrice Olivier-Wilson wrote: I have a question that this list solved a year ago, and I can't remember what the solution was. Does this list have an archive? (Sorry if it is on one of the links to the list, but I don't have any on hand at the moment.) see it on the bottom of my post... thanks and sorry for noise -- Patrice Olivier-Wilson http://biz-comm.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: Not to show until a certain date
On 9/28/10 8:33 PM, Chris W wrote: SELECT * FROM announcements WHERE announcements_expiredate CURDATE() AND announcements_postdate = CURDATE() ORDER BY announcements_expiredate ASC Thank you! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Not to show until a certain date
Hi... beginner here. Working on a php page and using this $query_announcements = SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() ORDER BY announcements_expiredate ASC ; Client now wants the announcement NOT to show until a specific date. I have an announcements_postdate in the table. Just not sure what the WHERE should be to not show until that date. Thanks much, -- Patrice Olivier-Wilson http://biz-comm.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: Not to show until a certain date
On 9/28/10 10:52 AM, Patrice Olivier-Wilson wrote: Hi... beginner here. Working on a php page and using this $query_announcements = SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() ORDER BY announcements_expiredate ASC ; Client now wants the announcement NOT to show until a specific date. I have an announcements_postdate in the table. Just not sure what the WHERE should be to not show until that date. Thanks much, Figured it out SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() AND announcements.announcements_postdateCURDATE() ORDER BY announcements_expiredate ASC thx -- Patrice Olivier-Wilson http://biz-comm.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: newbie question database tables
Thanks all... I got this to work! Much appreciated.. And thanks for patience with a newbie! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
newbie question database tables
I'm working on a database that needs to do a few things and getting brain freeze on one part. Scenario: I want to compile a db of articles with these tables: Categories Topics Users Categories cat_ID | cat_name Topics top_ID | top_name | top_content | cat_ID Users user_ID | user_name | top_ID or user_ID | user_name | top_ID | top_IDb | top_IDc etc (output to web page using php) But I need to show which users are using which topics, and I can add top_ID to the user file, which is fine if they are only using one topic. I could add 5 different topic to each user, but then I couldn't expand later. Reverse is true if I add user_ID to the Topics. So, need an idea how to solve this so it doesn't matter how many new users I keep adding, I can still see who is using the topics. As I said, a newbie question. Thanks much. -- Patrice Olivier-Wilson http://biz-comm.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: newbie question database tables
On 9/10/10 12:31 PM, Jerry Schwartz wrote: Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Thank you! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Importing table contents
I have 2 databases, different domains. Both have a table named 'tips'... both have different contents in the table. Using phpMyAdmin for GUI. I want to export databaseA tips as sql (done) then import content into databaseB tips. But when I run that operation, the databaseB says that there is already a table named tips in databaseB. Yep, know that... I want to bring in the contents...not make a new table. Any help, most appreciated Thanks as always -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Importing table contents
Gary Smith wrote: Patrice Olivier-Wilson wrote: I have 2 databases, different domains. Both have a table named 'tips'... both have different contents in the table. Using phpMyAdmin for GUI. I want to export databaseA tips as sql (done) then import content into databaseB tips. But when I run that operation, the databaseB says that there is already a table named tips in databaseB. Yep, know that... I want to bring in the contents...not make a new table. Any help, most appreciated When you export, PHPMyAdmin has the option to add drop table. This will drop the existing table structure and create a new one as it was when it was exported. Is this what you're after? Gary I have data I need to keep in both db just trying to merge. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Importing table contents
Gary Smith wrote: Patrice Olivier-Wilson wrote: I have data I need to keep in both db just trying to merge. There's two ways around this: First is to not export the structure (uncheck structure). The second is to export with if not exists. This should (IIRC) do a create table if not exists, so it'll do what you're wanting to do. Do you have any primary keys/auto increment columns that are going to overlap or anything like that? Cheers, Gary Gave it a try got this: MySQL said: #1062 - Duplicate entry '1' for key 1 -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Importing table contents
Gary Smith wrote: Patrice Olivier-Wilson wrote: Gave it a try got this: MySQL said: #1062 - Duplicate entry '1' for key 1 Yeah, that's what I was saying about in my previous mail. It looks like you've got a primary key on one of your columns, and you're attempting to insert data into it with a duplicate primary key (ie what the error message says). The easiest way to get around this one would be to write a query that pulls all of the columns apart from the primary key, and then replace that field with '' or somesuch. For instance, let's say you've got a schema of the following: table1(primarykey,column2,column3,column4,column5) primarykey is obviously a primary key. You'd do something along the lines of select '',column2,column3,column4,column5 from table1; Then export that resultset to an SQL file. Anyone else aware of an easier way to do this? I've got into some bad habits over the years, but I'm not aware of another way to do what Patrice is trying to do. Cheers, Gary If I export both db tables into csv, combine and then import back, that should do it, methinks... just create a new table called tips2, merge the 2 into one... -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
inserting csv - solved, but more to the puzzle
In case anyone might find this of the least interest, probably not, but I always hope to add to discussion just as part of the thank you for help. Further work with same issues found that a file received from a PC based client, if opened in PC environment, didn't have the same problems (so far) as if opened in a Mac environment. The next file I had to work with in this particular project, I opened in PC, then uploaded using phpMyadmin with no issues. Same client, same type of file. So, maybe there is a PC/Mac thing happening to cause the line 17, missing commas in csv? Just a thought. If anyone has any insight, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: inserting csv - solved, but more to the puzzle
Thank you for the confirmation, Andy. I appreciate your patience with such a newbie who is just trying to learn. Andy Wallace wrote: I've run into similar situations w/regard to Mac vs PC CSV files, it usually has to do with the EOL character sequence. Macs use LF (chr(10)), while PCs use CRLF (chr(13)chr(10)). andy Patrice Olivier-Wilson wrote: In case anyone might find this of the least interest, probably not, but I always hope to add to discussion just as part of the thank you for help. Further work with same issues found that a file received from a PC based client, if opened in PC environment, didn't have the same problems (so far) as if opened in a Mac environment. The next file I had to work with in this particular project, I opened in PC, then uploaded using phpMyadmin with no issues. Same client, same type of file. So, maybe there is a PC/Mac thing happening to cause the line 17, missing commas in csv? Just a thought. If anyone has any insight, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Inserting csv
Newbie question, please. I have a csv file of 950 records, 20 fields. I used this converter http://csv2sql.evandavey.com/ and copied/pasted insert code into SQL in phpMyAdmin and got this error SQL query: INSERT INTO membership( `members_ID` , `updated` , `notes` , `preferred_mail_street` , `preferred_mail_csz` , `first_name` , `last_name` , `street` , `city` , `state` , `zip` , `location_code` , `property` , `camp_street` , `camp_city` , `camp_zip` , `member_year` , `director` , `email` , `camp_phone` , `20` , `21` , `22` , `23` ) VALUES ( '', '', '', '', '', 'xxx', '', '102 Summer St', 'Dover-Foxcroft', 'xx', '04426', 'B', 'M15_L1_S20', 'Mill Brook', 'Bowerbank', '', '', '', '', '', '', '', '', '' ); MySQL said: #1054 - Unknown column '20' in 'field list' Actually the first time, it was column 21, so to trouble shoot, I removed that column from csv file and field name in phpMyAdmin. camp_phone should be the last field but `20` , `21` , `22` , `23` ) is there too... Any assistance most appreciated! I checked the file and didn't see any odd entries after the camp_phone field. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
insertng csv - solved
A HUGE thank you to Michael and John. There were a couple of things going on but I do not know why things got the way they were can only assume the client files had some issue. I pulled the file into text wrangler and removed , `20` , `21` , `22` , `23` etc. Then counted the number of extra commas for the lines, did a find replace and removed them. If there hadn't been so many, that trick would have been nearly impossible though. Then, on a hunch, I removed clients column content for how they had entered dates: 1/11/09 type of entry. Just blanked it out. Tried to import and it stopped at line 17 (again). BUT progress! So, went back to the converter and entered into SQL on phpmyadmn interface... viola! success. Thank you both so much for your help. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
csv import issues - good solution found
A week or so ago, I was seeking a solution for breaking lines for importing csv to phpmyadmin interface Found a slick solution: http://csv2sql.evandavey.com/ create table in database, upload the csv file to above page, copy/paste the resulting code into SQL field for the database (not the table) -- viola! :-) Just thought I'd share in case anyone else might need it... -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Newbie question: importing cvs settings - followup
Thanks again for assistance. FYI, I did track this thread down http://ask.metafilter.com/57007/Missing-commas-in-CSV-file (exerpt: Maybe there is a space or something in the 14th column of the first 15 rows. posted by. on February 14, 2007 It's a bug in Excel (not something you did wrong.) posted by . February 14, 2007 ) -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Newbie question: importing cvs settings
Jerry Schwartz wrote: [JS] This is just a shot in the dark, but Excel can be rather surprising when it puts out a CSV file. Depending upon the data, and exactly how you've specified the export, it can put double-quotes in unexpected places. If you leave out the 17th line of data what happens? 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 Thanks Jerry, Gavin and John: Sorry for not an immediate response to all of your suggestions. Other demands were pulling at me since I first asked for assistance. I opened the .csv file with Text Wrangler, and the commas are missing at about line 17 portfolio_ID,portfolio_sort_ID,portfolio_title,portfolio_bodycopy,portfolio_image,portfolio_before ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg ,1,Kitchens,,123.jpg ,1,Kitchens,,123.jpg So not sure why that is happening. I'm on a Mac, using Excel 2008. But at least you all have helped me find what it is doing, so now, I can at least pull into a txt file and make corrections manually. My thanks to all of you for your help and patience. (above represents an empty portfolio_ID, a filled in sort_ID, title, empty bodycopy, image, empty before) Thank you. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Newbie question: importing cvs settings
Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie question: importing cvs settings
Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.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: Newbie question: importing cvs settings
Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you John wrote: I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
problem with table design
Hello everybody I'm designing a database for our new Application and have some problems with the following: We have a table Jobs in which we store all kind of Jobs. Looks like this: tbl_jobs - job_id, integer, name, varchar, description, varchar easy so far :) The problem is we also want to keep track which of the Jobs are related or almost the same. Example: If we have the following jobs: 1 painter 2 auxiliary worker painter 3 plasterer 4 auxiliary worker plasterer 5 electrician 6 auxiliary worker electrician There will be 2 logical groups: first: 1, 2, 3, 4 second: 5, 6 If I query for plasterer I should get the following result: - plasterer - painter - auxiliary worker painter - auxiliary worker plasterer If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician What is the easiest way to design this? I thought about this the whole morning but couldn't get a solution. I hope somebody on this list can point me in the right direction. Regards, Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: problem with table design
On Apr 5, 2005 3:15 PM, Martijn Tonies [EMAIL PROTECTED] wrote: Hi, I'm designing a database for our new Application and have some problems with the following: We have a table Jobs in which we store all kind of Jobs. Looks like this: tbl_jobs - job_id, integer, name, varchar, description, varchar easy so far :) The problem is we also want to keep track which of the Jobs are related or almost the same. Example: If we have the following jobs: 1 painter 2 auxiliary worker painter 3 plasterer 4 auxiliary worker plasterer 5 electrician 6 auxiliary worker electrician There will be 2 logical groups: first: 1, 2, 3, 4 second: 5, 6 If I query for plasterer I should get the following result: - plasterer - painter - auxiliary worker painter - auxiliary worker plasterer If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician What is the easiest way to design this? I thought about this the whole morning but couldn't get a solution. I hope somebody on this list can point me in the right direction. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Thanks for your reply. I see this could be a possible solution. But isn't it possible to solve this problem somehow without having to create an additional job_group table? Regards, Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shell execution of mysql query
On Wed, 23 Feb 2005, Nupur Jain wrote: Hi Nupur, I am executing a mysql query through shell and expecting to see a return of SQL execution. mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile rc=$? Here rc is always 0 and so are $opFile entries. $queryFile contains exactly the same query as listed below. the following egrep (or grep -e) should do the trick: mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile egrep -qv Empty set (0.00 sec) $opFile rc=$? Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permission Denied for INSTALL-BINARY
On Wed, 9 Feb 2005, Mark Sargent wrote: Hi All, using Fedora2 and trying to install from a .tar file. Extracted to this dir, mysql-standard-4.1.9-pc-linux-gnu-i686 where I see the INSTALL-BINARY file. Using this cmd, ./INSTALL-BINARY gives the following error, [EMAIL PROTECTED] mysql-standard-4.1.9-pc-linux-gnu-i686]# ./INSTALL-BINARY -bash: ./INSTALL-BINARY: Permission denied What am I doing wrong here.? I'm rather new to Linux too. Cheers. Mark Sargent. Hi Mark, the file INSTALL-BINARY is a text file that includes documentation you have to read to know how you should proceed to install the package. (with more INSTALL-BINARY, for example, quit with q). so read this and follow the instructions ;-) Olivier PS: note that if you have a RedHat based system, like SuSE, Fedora, Mandrake, that use the RPM (Redhat Package Manager), you should download the RPM package instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Qcache and read data from master
Hi, I noticed the following behaviour on mysql 4.1.8 and two servers; to setup replication, I did the required grant on the master, and choose to use LOAD DATA FROM MASTER on the slave. As we monitor the Qcache usage on the master, it happend that Qcache was reset to 0 Queries during this operation. I would like to know if this behaviour is expected, and would be interrested if someone can explain me why this is necessary ? Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1189 (08S01): Net error reading from master
Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
Hi, As no one answered to the question below, I'm wondering wether it's a FAQ ... or a bug for which I should fill a report .. My network is ok between the two machines, I can ssh from one to another and any traffic can flow. But this error prevents me to start replication at all.. Olivier On Tue, 8 Feb 2005, Olivier Kaloudoff wrote: Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
On Tue, 8 Feb 2005, Bastian Balthazar Bux wrote: Just a shot in the dark do you have skip-networking or bind-address uncommented in your my.cnf ? Reading better what you write I suppose the answer is yes ;) can you connect from one server to the other using the replication user ? no skip-networking or bind to localhost activated. The two servers are clearly speaking to each other, as the first try was refused with : db0 does not allow from 192.168.0.177, and after the grant, I got the error 1189.. (lsof -i has open socket to *:mysql) Additionnaly, I can see clearly that db0 receives the command, as the Query Cache is completely wiped out to 0 Queries when load data from master is issued So there does not seem like a network problem to me :) another idea ? Olivier Kaloudoff ha scritto: Hi, As no one answered to the question below, I'm wondering wether it's a FAQ ... or a bug for which I should fill a report .. My network is ok between the two machines, I can ssh from one to another and any traffic can flow. But this error prevents me to start replication at all.. Olivier On Tue, 8 Feb 2005, Olivier Kaloudoff wrote: Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- 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: server performance
Hello, I use a mysql server on red hat 9 linux box with PIII 733 Mhz cpu and 256 MB memory. On this machine we use an adserver with a mysql backend. All click and view logs are kept in mysql tables. The number of the advertisements are increasing and so do the server's overhead. Some times I can not connect the box via ssh because of the overhead. First, I want to ask that the overhead will be over or not if I upgrade the hardware? Will hardware upgrade solve the problem? If another solution is possible, what is it? I'm new to mysql and also database stuff. So you want some server logs or status query results? Which are the most important infos for finding the problem and finding a way to solution. Could you please help me? Thanks in advance.. Hello Ender, a good way to start is probably to turn on slow query logging, for example adding the following option to the command line of mysql: --log-slow-queries[=file_name] then watch file_name with tail -f file_name, and you should see the bad queries come up. next, do an EXPLAIN on each query, to see if it's using indexes or not, and put indexes where you can. Olivier http://dev.mysql.com/doc/mysql/en/slow-query-log.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump and read lock
Hi all, Looking for a way to automate backup I was thinking of scheduling mysqldump on a daily base. It want to use the read lock, but I am not really sure about the consequences of this. Does putting a read lock on the files can cause loss of data? It is no problem pausing the flow of incoming data (invoices and other documents that are put in a monitored directory). But what about the data coming from the application that accesses the database? Say a user changes preferences or creates a new query, which are saved in the database. Is that data lost or buffered by MySql? Or does the application have to provide functionality to anticipate his situation? I know, I could bring down the whole system (webserver, application services and MySql), but with the read lock, the system would be 'down' (querying still possible) for only half an hour and it keeps running. The platform is Win2K. Regards, Olivier
RE: Cannot GRANT REPLICATION SLAVE
I had the same stuff going on for 4.0.7g on windows. Olivier Hi, I have this weird things happens. kaspia:/var/lib/mysql# mysql --version mysql Ver 12.22 Distrib 4.0.21, for pc-linux-gnu (i386) mysql show grants for \root\@\localhost\; +-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +-+ mysql grant replication slave on *.* to \backup\@\192.168.1.32\; Query OK, 0 rows affected (0.00 sec) mysql show grants for \backup\@\192.168.1.32\; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT USAGE ON *.* TO 'backup'@'192.168.1.32' | +---+ I can grant another previleges, but not replication slave and replication clients. How do I fix this? Thank you in advance. --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reg Backup
On Mon, 4 Oct 2004 [EMAIL PROTECTED] wrote: Hi, Is there any suitable command in MySQL 4.0 with out InnoDB for taking the backup. Please suggest. Thanks, Narasimha Hi Narasimha, when posting a question to a mailing list, please don't ask it many times, or the users might say hey, this guy sucks, we heard his question already. If you need quick answers to your questions, as well as real support, the MySQL guys can sell you some. Additionnaly, this list is archived, so your question might have been already answered, so browse the mailing list archives first before asking. Regards, Olivier Kaloudoff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT Gmail
Transcend Development wrote: -- From: Transcend Development[SMTP:[EMAIL PROTECTED] Sent: Sunday, August 29, 2004 6:30:01 PM To: [EMAIL PROTECTED] Subject: RE: OT Gmail Auto forwarded by a Rule As I have received many more than 3 requests I thought I should post this: You can get invitations to gmail for about 99 cents on ebay! That's where I got mine. I then sold a couple, but it's too much hassle for too little money! Anyway I hope some of you can get yours there, as I have not received any more quote from Google. Regards, I have 2 invitations left. The first two who send me a message win :-) Regards, Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem after transfer from 4.0.x to 3.23.x
Hello everybody I have the following problem: I've created a Website for a Customer by using MySQL 4.0.x Now I need to transfer the whole stuff to my Customer's ISP. The ISP is using MySQL 3.23.x Now I have the following Query which doesn't work on the 3.23 Server: SELECT hotelstammdaten.id_PK , hotelstammdaten.hotelname , hotelstammdaten.name , hotelstammdaten.vorname , hotelstammdaten.strasse , hotelstammdaten.plz , hotelstammdaten.ort , hotelstammdaten.bundesland , hotelstammdaten.land FROM hotelstammdaten INNER JOIN relation_hotelthema ON hotelstammdaten.id_PK = relation_hotelthema.hotelid_fk INNER JOIN hotelthema ON relation_hotelthema.hotelthemaid_fk = hotelthema.id_PK INNER JOIN relation_hotelausstattung ON hotelstammdaten.id_PK = relation_hotelausstattung.hotelid_fk INNER JOIN hotelausstattung ON relation_hotelausstattung.hotelausstattungid_fk = hotelausstattung.id_PK INNER JOIN relation_hoteldienstleistung ON hotelstammdaten.id_PK = relation_hoteldienstleistung.hotelid_fk INNER JOIN hoteldienstleistung ON relation_hoteldienstleistung.hoteldienstleistungid_fk = hoteldienstleistung.id_PK INNER JOIN relation_hotelfreizeit ON hotelstammdaten.id_PK = relation_hotelfreizeit.hotelid_fk INNER JOIN hotelfreizeit ON relation_hotelfreizeit.hotelfreizeitid_fk = hotelfreizeit.id_PK WHERE hotelstammdaten.status = 'aktiv' AND hotelstammdaten.sterne_fk = '3' AND hotelstammdaten.ort like 'Bern' AND (hotelstammdaten.bundesland LIKE 'Oberösterreich' ) AND relation_hotelthema.hotelthemaid_fk = '2' AND relation_hotelausstattung.hotelausstattungid_fk IN (50, 96) AND relation_hoteldienstleistung.hoteldienstleistungid_fk IN (42) AND relation_hotelfreizeit.hotelfreizeitid_fk IN (5, 34) GROUP BY hotelstammdaten.id_PK HAVING count(DISTINCT relation_hotelausstattung.hotelausstattungid_fk) = 2 AND count(DISTINCT relation_hoteldienstleistung.hoteldienstleistungid_fk) = 1 AND count(DISTINCT relation_hotelfreizeit.hotelfreizeitid_fk) = 2 The Error Message I get is: Unknown column 'relation_hotelausstattung.hotelausstattungid_fk' in 'having clause' I can't figure out what is wrong. Can anyone put me in the right direction ? Regards Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: recommended books for web app.
It's not one from your list but I can recommend you this one: http://www.oreilly.com/catalog/webdbapps2/index.html Gives you a nice overview about PHP/MySQL and a nice case study which explains how to create a Online Winestore. Regards Olivier -Ursprüngliche Nachricht- Von: Peter Brawley [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 12. August 2004 15:23 An: Kerry Frater; MySQL List Betreff: Re: recommended books for web app. Welling Thomson is terrific. - Original Message - From: Kerry Frater To: MySQL List Sent: Thursday, August 12, 2004 8:09 AM Subject: recommended books for web app. I am looking to port an app from an existing web environment to MySQL. The requirement is relatively easy. The Tables are read only and the data is to be only accessed via login password. The login will give a limited view of records based on a master/detail table relationship. I need to be aware of securing the database and have been told by others that I should look to use PHP. I know my local bookstore has the following publications (based on asking about MySQL PHP) Beginning PHP, Apache MySQL Web Development published by Wrox PHP MySQL written by Larry Ullmen PHP MySQL Web Development written by Luke Welling Laura Thomsan Has anyone seen these books and possibly recommend one of them? Thanks Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: cannot add mysql user on debian sarge
I think he's trying to create the user mysql on the system. If so try if you can add any other user on your system. If this fails, it's a problem of your system. Try asking the question on a debian list. Maybe they can help you better. Regards O.Salzgeber -Ursprüngliche Nachricht- Von: Victor Pendleton [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 4. August 2004 15:28 An: 'Levi Campbell '; 'mysql ' Betreff: RE: cannot add mysql user on debian sarge Are you attempting to add a user in the MySQL database or the mysql user on the system? -Original Message- From: Levi Campbell To: mysql Sent: 8/4/04 8:12 AM Subject: cannot add mysql user on debian sarge On debian sarge, I'm trying to add a user for MySQL but every time I try, I get the following error /usr/bin/perl: relocation error: /usr/perl5/locale/gettext/gettext.so: unrecognized symbol: Perl_gthr_key_ptr What do I need to do? -- 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]
Enum or Int
Hello all I have a table with a field which needs to hold a yes/no or 1/0 value. Which field type is best for this ? Should i create a ENUM field with yes or no values or is it better to create an Int field for this ? What would you recommend ? Best regards Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query w multiple tables and expressions
Hello everyone I hope this is the right list for my question if not please let me know. First I've got to say that I'm not an experienced query-builder so maybe the answer to my problem is very easy. But I have no idea how I could solve this problem and all my research (Internet and Forums) did not help me :( I am working on a hotel database. Every hotel has some general data like name, address,... and can provide one or many services. And a service can be for 0 or many hotels. So we have a many to many link. A hotel can also provide one or many spare time activity's and this activity's can be for 0 or many hotels. So another many to many link. I've created a link-table for activity's and services in which i link the hotels to the category by ID. So here are my tables: - stammdaten (id_PK, name, description, address, location...) - services (id_PK, servicesname) - relation_services (hotelid, serviceid) - sparetime (id_PK, sparetimename) - relation_sparetime (hotelid, sparetimeid) I need to create a search Query to find all the hotels which have specific services and sparetime activities. For example: Search for hotels where location is Bern and the hotel has sparetime activity 2 and 5 AND services 5 and 3 and 7 So only hotels which match all the expressions should appear as results. I've tried it with the following Query but this doesn't work as it should. * SELECT * FROM stammdaten INNER JOIN relation_services ON stammdaten.id_PK = relation_services.hotelid INNER JOIN relation_sparetime ON stammdaten.id_PK = relation_sparetime.hotelid WHERE stammdaten.location LIKE 'Bern' AND relation_services.serviceid IN (5, 3, 7 ) AND relation_sparetime.sparetimeid IN (2, 5) AND GROUP BY stammdaten.name ORDER BY stammdaten.name * I'm trying to get this working for more than a week now but couldn't find a solution. Maybe this isn't possible in a single query ? But I have no idea how to get this working in another way. Hope somebody here can help me with this. O.Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: query w multiple tables and expressions
Thank you very much. Works perfectly now. -Ursprüngliche Nachricht- Von: Harald Fuchs [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 15. Juli 2004 13:58 An: [EMAIL PROTECTED] Betreff: Re: query w multiple tables and expressions This gives all hotels having at least one of the required services/sparetimes, i.e. an OR condition. For AND you need SELECT stammdaten.id_PK FROM stammdaten INNER JOIN relation_services ON stammdaten.id_PK = relation_services.hotelid INNER JOIN relation_sparetime ON stammdaten.id_PK = relation_sparetime.hotelid WHERE stammdaten.location = 'Bern' AND relation_services.serviceid IN (5, 3, 7) AND relation_sparetime.sparetimeid IN (2, 5) GROUP BY stammdaten.id_PK HAVING count(DISTINCT relation_services.serviceid) = 3 AND count(DISTINCT relation_sparetime.sparetimeid) = 2 ORDER BY stammdaten.id_PK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: MySql++ compilation error on Aix with gcc3.2.1 (default argument given]
I tried to integrate, MySql++-1.7.9 with mysql-max-3.23.55-ibm-aix4.3.2.0-powerpc. I compiled with gcc3.2.1, and i passed the 2 patches for gcc3.0 and gcc3.2, on Aix4.3.2 How-To-Repeat: My configure contains: setenv CC /opt/gcc-3.2.1/usr/local/bin/gcc setenv CXX /opt/gcc-3.2.1/usr/local/bin/g++ setenv CFLAGS '-Wno-deprecated' setenv CXXFLAGS '-Wno-deprecated' setenv MAKE /opt/bin/make ./configure --srcdir=/opt/mysql++-1.7.9.src/mysql++-1.7.9 --with-mysql-include=/opt/mysql/include --prefix=/opt/mysql++-1.7.9_gcc3.2 And at compilation time i obtain this error: connection.cc:26: default argument given for parameter 6 of ` MysqlConnection::MysqlConnection(const char*, const char*, const char*, const char*, unsigned int, char = 0, unsigned int = 60, bool = true, const char* = , unsigned int = 0)' Could you give me an advice, to correct this error. Thanks, Olivier -- * * * Olivier HARDOUIN [TRANSICIEL - TECHNOLOGIES] * * * * sgl: 8is - Bur: M077 - Tel: 05 62 14 34 87 * * * ---end of your message--- MySQL Development Team -- * * * Olivier HARDOUIN [TRANSICIEL - TECHNOLOGIES] * * * * sgl: 8is - Bur: M077 - Tel: 05 62 14 34 87 * * * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
config fails
[localhost:/usr/local/mysql-3.23.51] root# ./configure
--prefix=/usr/local/mysql
--with-unix-socket-path=/usr/local/mysql/run/mysql_socket
--with-mysqld-user=mysql --with-comment --with-debug
checking build system type... powerpc-apple-darwin5.5
checking host system type... powerpc-apple-darwin5.5
checking target system type... powerpc-apple-darwin5.5
checking for a BSD compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... yes
checking for working aclocal... missing
checking for working autoconf... found
checking for working automake... missing
checking for working autoheader... found
checking for working makeinfo... missing
checking whether to enable maintainer-specific portions of Makefiles... no
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... (cached) yes
checking for mawk... no
checking for gawk... no
checking for nawk... no
checking for awk... awk
checking for gcc... no
checking for cc... cc
checking for C compiler default output... configure: error: C compiler
cannot create executables
[localhost:/usr/local/mysql-3.23.51] root#
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL under cygwin : mysql_install_db does not create host.frm
Dear all, I've installed Cygwin and CAMP and I'm now looking for guidelines to build/install mysql under cygwin and XPpro. Sorry for that question but I tried during severals days with no luck. I'm using the scripts provided by Gerrit P. Haase in http://sources.redhat.com/ml/cygwin/2001-12/msg00025.html with mysql-3.23.49.tar.gz At the end of the installation process, I get the following error when starting mysql deamon : /src/mysql/mysql-3.23.49 Starting mysqld daemon with databases from /var/mysql/data 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) 020503 07:15:49 mysqld ended This error has been already discussed in several mailing lists, the solution was to chown /var/mysql/data with user mysql, but it does not work here. I used the following procedure : - add user mysql and group cyg_mysql in XP (I log with username 'Administrateur') - add user and group to cygwin files with mkpasswd -l /etc/passwd mkgroup -l /etc/group - installation in /scr/mysql - mysql.build prep = ok - patch -p0 ../mysql.patch = ok - mysql.build conf = ok - mysql.build build stops with errors in make info : make[1]: Leaving directory `/src/mysql/mysql-3.23.49/bdb' make: *** [info-recursive] Error 1 - mysql.build install = ok - mysql.build initdb = ok, but ./scripts/mysql_install_db says : Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ERROR: 1033 Incorrect information in file: './mysql/db.frm' ... 020503 8:16:00 /usr/libexec/mysqld: Shutdown Complete - mysql.build startdb = stops with 'mysqld ended' : cat /var/mysql/data/EXBRAYAT.err /src/mysql/mysql-3.23.49 Starting mysqld daemon with databases from /var/mysql/data 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) 020503 07:15:49 mysqld ended Of course, there is no file in /var/mysql/data/mysql/ ... when I cp /dev/null to mysql/host.frm the deamon can access the file and the error become : 020503 8:28:58 /usr/libexec/mysqld: Incorrect information in file: './mysql/host.frm' 020503 08:28:58 mysqld ended So, the installation process did not create the mysql database. but why ? Is there a patch to apply in ./scripts/mysql_install_db ? Fortunately, I have also installed mysql under XP, and when I copy the XP/mySQL databases to the CygWin/mySQL ones, everything (camp mysqld) is working perfectly, except it is a mess with usernames ... Mr Haase, could you help ? :-) thanks, Olivier. - Olivier Delrieu [EMAIL PROTECTED] Phone : +33 1 44 89 77 59 : +33 1 44 89 77 50 (secretariat) Mobile : +33 6 64 63 00 15 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL under cygwin : mysql_install_db does not create host.frm
sorry, no effect, the deamon could not be started anyway as there is no mysql database ... moreover, mysql_install_db lauch : /usr/libexec/mysqld --bootstrap --skip-grant-tables --basedir=/usr --datadir=/var/mysql/data --skip-innodb --skip-gemini --skip-bdb sql... and the sql statement include only reference to 'root' user in autorisation table. may be that is the problem ? olivier -Message d'origine- De : TP R Murthy [mailto:[EMAIL PROTECTED]] Envoye : ven. 3 mai 2002 12:28 A : Olivier Delrieu Cc : [EMAIL PROTECTED] Objet : Re: mySQL under cygwin : mysql_install_db does not create host.frm hi, where are you running the server from? you might wanna try running from the base dir ./bin/safe_mysqld i used to have the same problem. on a linux machine though. regards, On Fri, 3 May 2002, Olivier Delrieu wrote: Dear all, I've installed Cygwin and CAMP and I'm now looking for guidelines to build/install mysql under cygwin and XPpro. Sorry for that question but I tried during severals days with no luck. I'm using the scripts provided by Gerrit P. Haase in http://sources.redhat.com/ml/cygwin/2001-12/msg00025.html with mysql-3.23.49.tar.gz At the end of the installation process, I get the following error when starting mysql deamon : /src/mysql/mysql-3.23.49 Starting mysqld daemon with databases from /var/mysql/data 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) 020503 07:15:49 mysqld ended This error has been already discussed in several mailing lists, the solution was to chown /var/mysql/data with user mysql, but it does not work here. I used the following procedure : - add user mysql and group cyg_mysql in XP (I log with username 'Administrateur') - add user and group to cygwin files with mkpasswd -l /etc/passwd mkgroup -l /etc/group - installation in /scr/mysql - mysql.build prep = ok - patch -p0 ../mysql.patch = ok - mysql.build conf = ok - mysql.build build stops with errors in make info : make[1]: Leaving directory `/src/mysql/mysql-3.23.49/bdb' make: *** [info-recursive] Error 1 - mysql.build install = ok - mysql.build initdb = ok, but ./scripts/mysql_install_db says : Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ERROR: 1033 Incorrect information in file: './mysql/db.frm' ... 020503 8:16:00 /usr/libexec/mysqld: Shutdown Complete - mysql.build startdb = stops with 'mysqld ended' : cat /var/mysql/data/EXBRAYAT.err /src/mysql/mysql-3.23.49 Starting mysqld daemon with databases from /var/mysql/data 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) 020503 07:15:49 mysqld ended Of course, there is no file in /var/mysql/data/mysql/ ... when I cp /dev/null to mysql/host.frm the deamon can access the file and the error become : 020503 8:28:58 /usr/libexec/mysqld: Incorrect information in file: './mysql/host.frm' 020503 08:28:58 mysqld ended So, the installation process did not create the mysql database. but why ? Is there a patch to apply in ./scripts/mysql_install_db ? Fortunately, I have also installed mysql under XP, and when I copy the XP/mySQL databases to the CygWin/mySQL ones, everything (camp mysqld) is working perfectly, except it is a mess with usernames ... Mr Haase, could you help ? :-) thanks, Olivier. - Olivier Delrieu [EMAIL PROTECTED] Phone : +33 1 44 89 77 59 : +33 1 44 89 77 50 (secretariat) Mobile : +33 6 64 63 00 15 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- _ TP R Murthy (Systems Analyst) Cerulean Information Technology Pvt. Ltd. #24, floor#2, 5th Main, 5th Block, Koramangala, Bangalore-560 095. Phone: 299-1886, 299-1897 web:http://www.ceruleaninfotech.com _ Life is a Dream. Dont Wake up. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie question about InnoDB
hi, I've downloaded mysql-max-nt v.3.23.49, have setup InnoDB startup support. I am now in the process of creating tables. When these are standard tables no problem, eg: create table test (id integer not null, primary key(id)) type=InnoDB; works fine ! I have problems when I have tables with foreign keys. Here is my sql script: CREATE TABLE answer ( id_answer INTEGER NOT NULL, text_answer VARCHAR (255), id_evaluation INTEGER NOT NULL, id_question INTEGER NOT NULL, scale_answer INTEGER, PRIMARY KEY(id_answer), INDEX evaluation_ind (id_evaluation), INDEX question_ind (id_question), FOREIGN KEY (id_evaluation) REFERENCES evaluation (id_evaluation), FOREIGN KEY (id_question) REFERENCES question (id_question) ) type=InnoDB; There I end up with an error 1005 errno 150 I've seen in the doc that my foreign key syntax might be wrong, but what is wrong ?? Note the same script works fine without type=InnoDB. Any idea ?? Olivier - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: Misc Question
Hello Tim Graves, Thank you for your e-mail. Bug report *** slight error in directions for binary install on Solaris 2.8 sparc -R chown on soft links changes only the owner of the link. Security feature of Solaris. Suggest you change dir mysql to mysql-VERSION... in the installation instructions. User gets a mysqld ended and an error log in ./data/ saying can't create files... shell chown -R root /usr/local/mysql shell chown -R mysql /usr/local/mysql/var shell chgrp -R mysql /usr/local/mysql I post your bug report on our [EMAIL PROTECTED] Regards, Olivier -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Olivier Beutels / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Account Manager /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com GSM: +358 50 571 0528 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Misc Question [University of Alberta, Canada]
Dear Roman Eisner, Hello. I'm not sure where to submit bugs, so I'll do it here. It's not that major, but I thought I'd let you know The normal place to report bugs and problems is [EMAIL PROTECTED] In mysql, on Linux, I have a table, say TempTable. So, I type Drop table Temp and hit Tab, then it autocompletes for me, and the statement becomes Drop table TempTable Ok. So, after I drop this table, if I type Drop table temp and hit Tab, then it autocompletes again to Drop table TempTable. This is kind of annoying. this table is gone, so I dont want it autocompleted to that table. Best Regards, Olivier Beutels, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Olivier Beutels / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Account Manager /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com GSM: +358 50 571 0528 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
limits to volume handled by MySQL
Hi there We are switching to a MySQL 3.23.38 on a PIII 933 MHz machine running with a FreeBSD and a web Apache server 1.3.19 . The hosting company has just argued MySQL cannot handle more than 1Go in database files opened per session. Is it true ? If so how to go over that 1Go limit ? ( :-) yes i know scratch the project .. seriously ?) Cheers Bernard Clist Bernard-Olivier Clist Administrateur du site Internet de l'OGE Site Web : http://www.geometre-expert.fr/ Mél : [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: GROUPING
Hola Rodrigo, I suggest that you use a temporary table: CREATE tmp ( INT id, TIME max); INSERT INTO tmp SELECT mytable.id, MAX(mytable.start) FROM mytable GROUP BY mytable.id; and then join it to your original table: SELECT M.* FROM mytable M, tmp T WHERE M.id=T.id AND M.start=T.max; I didn't try it, but that's what I would try. Un saludo, Olivier - Original Message - From: Rodrigo Gonzalez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 4:56 PM Subject: GROUPING Hi, I have to do a query but i don't know how to do this... I have this table id int start time stop time ... There are 2 to 4 rows for each id I have to select one row for each id where start is the max value between all rows with this id If you have an idea please let me know Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOIN optimization
Hi, I'm rather new to SQL (well, I had a one-year course at the university many years ago, but they didn't teach us anything practical). I did a few very simple data bases, but now I'm moving to something more serious with joins and 'group by'. The idea is to create a search engine for a battery distributor, specifying various characteristics like * who makes it (only one per battery) * special characteristics (various) * tipical applications (various) To simplify, I have the following tables: Battery: * batID (primary key) * makID * descr Maker: * makID (primary key) * mak Special: * speID (primary key) * special Bat_Spe: * batID * speID (both in primary key) Tipical: * tipID (primary key) * tipical Bat_Tip: * batID * tipID (primary key) First I want to make a list of batteries with all the carateristics they have: SELECT M.mak, B.descr, S.special, T.tipical FROM Battery B, Maker M, Special S, Bat_Spe BS, Tipical T, Bat_Tip BT WHERE M.makID=B.makID AND B.batID=BS.batID AND BS.speID=S.speID AND B.batID=BT.batID AND BT.tipID=T.tipID QUESTION: Is there a better way of writing this join ? Now I want to make a list of batteries which have caracteristics: * Special: speA * Tipical: tipA or tipB Además I want to sort the result such that if a battery has both tipA _and_ tipB, it comes first. I am thinking of doing it with temporary tables: tmp_spe: * batID (primary key) * count tmp_tip: * batID (primary key) * count INSERT into tmp_spe SELECT batID, count(speID) FROM Bat_Spe WHERE (speID='speA') GROUP BY batID QUESTION: Is there any difference between * SELECT batID, count(speID) * SELECT batID, count(batID) * SELECT batID, count(*) ? In this case, the three work and return the same result. INSERT into tmp_tip SELECT batID, count(tipID) FROM Bat_Tip WHERE (tipID='tipA' OR tipID='tipB') GROUP BY batID SELECT B.batID, (TS.count+TT.count) FROM Battery B, tmp_spe TS, tmp_tip TT WHERE B.batID=TS.batID AND B.batID=TT.batID ORDER BY 2 DESC This returns all the batteries which have all the characteristics I want, ordered as I want. QUESTION: Well, how does it sound ? Is there a better way to do it ? QUESTION: Now, I would need to get, for each battery matched, a list of all the characteristics it has. That is, if battery with batID='bat1' has tipical applications 'tipA', 'tipB' and 'tipC', can I retrieve in one shot all the batteries matched, together with the characteristics each one has, _or_ do I have to go through more temporary tables, _or_ is it better to make one query for each battery I have Well, I know that's a bit long, but it would greatly help to orientate me on how to continue... Thanks in advance for any help or hint, Olivier
RE: JOIN optimization
Hi Bob,
Many thanks for your answer, I was afraid that
due to the length of the mail, it would scare
everybody...
Battery:
* batID (primary key)
* makID
* descr
Maker:
* makID (primary key)
* mak
A battery can have only one manufacturer, so you might as well add
the mak column to Battery and remove Maker.
Well, the idea was not repeating the name of the maker,
just have its ID.
Special:
* speID (primary key)
* special
Bat_Spe:
* batID
* speID (both in primary key)
Tipical:
Do you mean Type?
Well, in fact I mean Typical (typical application of the battery)
* tipID (primary key)
* tipical
Bat_Tip:
* batID
* tipID (primary key)
In fact it is like for join table Bat_Spe:
* tipID (both in primary key)
QUESTION:
Is there any difference between
* SELECT batID, count(speID)
* SELECT batID, count(batID)
* SELECT batID, count(*) ?
In this case, the three work and return the same result.
In the future, if you decide to change the WHERE clause to search for
two or more speIDs (e.g. WHERE speID = 'speA' OR speID = 'speB'),
then the counts will be different. Decide what you actually want to
count; speIDs, batteries, or rows. Even if the WHERE clause doesn't
change, what you count gives you a clue as to what the statement
does. Suppose you want to count rows and you use count(batID). Six
months from now when you've forgotten how this works, the use of
count(batID) will make it more difficult to figure out that you are
counting rows.
I think I was confused because each couple (speID, batID)
is unique. That's why the 3 forms are equivalent here.
For the last question, (list of characteristics for each
battery matched) I decided to make a query for each
class of characteristics ('spe', 'tip') since I wanted to
do some additional treatments.
Thanks again Bob, I feel more confident now.
Olivier
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem installing binary on AIX 4.3.1.0
From: [EMAIL PROTECTED] Date: Thu, 12 Apr 2001 17:12:06 +0200 To: [EMAIL PROTECTED] Hi there, Although I am using AIX4.3.1, I tried to install the binary version (which is built for AIX4.3.3). Most of the time, it does not make any difference, but when I run: scripts/mysql_install_db I get: exec(): 0509-036 Cannot load program ./bin/my_print_defaults because of the following errors: 0509-150 Dependent module libz.a(shr.o) could not be loaded. 0509-022 Cannot load module libz.a(shr.o). 0509-026 System error: A file or directory in the path name does not exist. WARNING: The host 'centaure' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables exec(): 0509-036 Cannot load program ./bin/mysqld because of the following errors: 0509-150 Dependent module libz.a(shr.o) could not be loaded. 0509-022 Cannot load module libz.a(shr.o). 0509-026 System error: A file or directory in the path name does not exist. Installation of grant tables failed! Obviously, there is an archive file missing (library). Any advice for that ? Building from the source distribution on an AIX machine is not that straightforward, so I would prefer to use the binary distribution. Any advice appreciated, Olivier. MySQL support: none Synopsis: Problem installing binary on AIX 4.3.1.0 Severity: critical Priority: medium Category: mysql Class: support Release: mysql-3.23.36 (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: AIX centaure 3 4 002004917500 Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/rs6000-ibm-aix4.3.1.0/2.7.2/specs gcc version 2.7.2 Compilation info: CC='gcc' CFLAGS='-O3 -mcpu=powerpc -Wa,-many ' CXX='c++' CXXFLAGS='-O3 -mcpu=powerpc -Wa,-many -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 bin bin 19 Oct 08 1971 /lib/libc.a - /usr/ccs/lib/libc.a lrwxrwxrwx 1 bin bin 19 Oct 08 1971 /usr/lib/libc.a - /usr/ccs/lib/libc.a Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --disable-shared Perl: This is perl, version 5.005_03 built for aix Olivier Hislaire MSG International Email: [EMAIL PROTECTED] 97 Avenue de Tervuren Phone: +32 (0)2 735.91.59 Ext. 55 1040 Brussels Fax: +32 (0)2 732.12.19 Belgium http://www.msg-i.com
Mysql_install_db
To whom it may concern: When running, the /scripts/mysql_install_db, it starts off with preparing the tables, and then it says it is Installing the tables. That is all it does, it says the process is running and everything. Does it take a real long time for it to install the tables? If so how long? We are running this on a Alpha 1000a digital Unix 5.1. Thanks
