RE: monitor multiple mysql servers with no 3306 access
Hi, Try MRTG, there are a couple of very good scripts available for monitoring mysql, it can monitor locally for you without requiring TCP access to the DB. This allows you to monitor (not on a real time basis though) multiple databases and provide some nice pictures to spot errors quickly. There are many options available. Regards http://www.google.com.au/search?hl=enq=%2Bmrtg+%2BmysqlbtnG=Google+Search; meta= --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Bing Du [mailto:[EMAIL PROTECTED] Sent: Wednesday, 14 March 2007 6:31 AM To: mysql@lists.mysql.com Subject: Re: monitor multiple mysql servers with no 3306 access On 3/13/07, Bing Du [EMAIL PROTECTED] wrote: Hi, Seems like there are quite many MySQL monitoring tools on the Internet. The one I tried today was MONyog http://www.webyog.com/en/. I've also tried a couple of others before, including phpmysqladmin as I was suggested. They require you provide mysql server name and port number. In our situation, we have three or four separate MySQL servers running. Each has 'skip-networking' configured. So mysql don't accept requests coming from outside via network. Each mysql server also runs as web server. 'Localhost' is used for communication between web applications and database server. In this case, how should I monitor our multiple mysql servers remotely? Would anybody advise? Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Probably you cant access it from network (remote access), but if you do some script do interconnect them.. maybe you can get this working :D Or if you prefer you can enable the tcp/ip socket on a diferent port for each instances of mysql running in your box.. :) -- -- Thiago LPS C.E.S.A.R - Administrador de Sistemas msn: [EMAIL PROTECTED] 0xx 81 8735 2591 -- Thanks for the reply. I don't think we want to open any port for MySQL connections. Is it possible to install some local stats collector on each MySQL server and my central monitor polls each stats collector? So I don't need to directly connect to mysql. The goal is make minimum impact on database services. That's just my rough idea. Before I delve into writing home grown scripts, anybody aware of any such server-client based monitoring tools or have a better idea? Thanks, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Year - Field type
Why not keep the date of birth as a standard date field and extract the fields you need using the DATE_FORMAT function? eg. DATE_FORMAT(date_of_birth, '%Y') mysql \u test Database changed mysql create table test_dates (a int, mydate date); Query OK, 0 rows affected (0.29 sec) mysql describe test_dates; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | a | int(11) | YES | | NULL| | | mydate | date| YES | | NULL| | ++-+--+-+-+---+ 2 rows in set (0.03 sec) mysql insert into test_dates SET a=1, mydate=1887-10-12; Query OK, 1 row affected (0.10 sec) mysql select * from test_dates; +--++ | a| mydate | +--++ |1 | 1887-10-12 | +--++ 1 row in set (0.00 sec) mysql insert into test_dates SET a=1, mydate=1987-10-12; Query OK, 1 row affected (0.10 sec) mysql select * from test_dates order by mydate; +--++ | a| mydate | +--++ |1 | 1887-10-12 | |1 | 1987-10-12 | +--++ 2 rows in set (0.00 sec) mysql select DATE_FORMAT(mydate,'%Y') as year FROM test_dates ORDER BY year; +--+ | year | +--+ | 1887 | | 1987 | +--+ 2 rows in set (0.00 sec) mysql Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 February 2007 9:59 AM To: 'Mike Blezien'; mysql@lists.mysql.com Subject: RE: Year - Field type Mickalo I gave that a go now I have another issue I can only enter years 1900 onwards so when I enter 1887 it changes the value to 0 John B -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 23:10 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Year - Field type have you try using the datatype YEAR for you table field/column ? Mickalo - Original Message - From: John Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 19, 2007 11:45 AM Subject: Year - Field type Hi Using mysql4 Sure this is an easy one a field in my dbase is year of birth, its always a 4 digit number, for some reason Im failing to sort by the field in my results, it was originally a varchar field so I updated it to int but still no luck. Pointers appreciated. Regards John Berman -- 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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.2/692 - Release Date: 18/02/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Safe DB Distribution
Have you checked out http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html, you should be able to en/decrypt your data via these functions thereby rendering any data on the disk totally useless unless the process comes through your mysql server. You will have to configure mysql with openssl support though, I'm sure you would be able to find a package somewhere that has that available. You can then use mysql security to only allow access to the webserver user so the php package is the only one that can access this. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Suhas Pharkute [mailto:[EMAIL PROTECTED] Sent: Tuesday, 30 January 2007 1:55 PM To: Ales Zoulek Cc: mysql@lists.mysql.com Subject: Re: Safe DB Distribution Yep, I do understand that, but when I give DB to someone, I do not want them to see the database. I want them to access the database thr' the php application. It is very simple, I don't want any one to see my DB in open way. But they can access it thr' my app. I agree with about file system, and that will work on Linux but unfortunately I am working on Windows(98/NT/XP) any ideas!!! Thx Suhas On 1/29/07, Ales Zoulek [EMAIL PROTECTED] wrote: What is it exactly that you need to achieve? PHP script doesnt read a MySQL data files, MySQL server does. You can configure your OS and filesystem rights, so that only MySQL server can read them. Ales. -- Forwarded message -- From: Suhas Pharkute [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Mon, 29 Jan 2007 11:48:27 -0700 Subject: Safe DB Distribution HI, I want to distribute the MySQL DB to different user with an PHP web app. My biggest problem is MySQL data files. Is there anyway by which I can configure the MySQL so that only PHP script can read it and if you happen to copy the Data files and try to use it, that will be useless. I know, this is been in discussion before, but if I can get any other idea to it, I would appreciate it Thank you in advance, Suhas -- -- Ales Zoulek +420 604 332 515 ICQ: 82647256 http://www.al3x.cz/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Suhas Pharkute, PhD CEO - Syna Intelligence, LLP V. 208 830 8915 (C) E. [EMAIL PROTECTED],.com W. http://synaintel.com smime.p7s Description: S/MIME cryptographic signature
RE: Replication Problem?
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
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: DROP DATABASE weirdness
Hi Bruce, Do you have any messages in the MySQL log? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:42 AM To: mysql@lists.mysql.com Subject: DROP DATABASE weirdness I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- 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: DROP DATABASE weirdness
Hi Bruce,
Sounds like you are missing a library or two on the system. Did you
compile the server yourself?
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, 30 November 2006 9:52 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness
I get this error:
/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined
symbol: zlibCompileFlags
Logan, David (SST - Adelaide) wrote:
Hi Bruce,
Do you have any messages in the MySQL log?
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, 30 November 2006 9:42 AM
To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness
I'm running MySQL 5.0.24a and I have a database I need to drop. When
I
issue the command DROP DATABASE webdb the client seems to just hang.
I
see the command hit the server in the full log but the database never
drops. Anyone have any suggestions?
--
One day at a time, one second if that's what it takes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP DATABASE weirdness
Hi Bruce,
Have a squiz here http://bugs.mysql.com/bug.php?id=16586 sounds
suspiciously like your issue.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, 30 November 2006 11:17 AM
To: mysql@lists.mysql.com
Cc: Logan, David (SST - Adelaide)
Subject: Re: DROP DATABASE weirdness
Nope, That didn't fix it. I'd uses MySQl provided rpms but they don't
seem to have them got glib23 anymore.
Suggestions?
Bruce Ferrell wrote:
I think I found it. The system has zlib 1.1.4 the sources have zlib
1.2.3. Trying a static build now. Anyone wanna bet? :)
Bruce Ferrell wrote:
Yes, built with SSL support the build seemed to go OK and sometimes
it
works. I'm building on SuSE 9.2 using the src.rpm and the included
spec file
Logan, David (SST - Adelaide) wrote:
Hi Bruce,
Sounds like you are missing a library or two on the system. Did you
compile the server yourself?
Regards
---
** _/ ** David Logan *** _/
*** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **** _/
Postal:
148 Frome Street,
_/ ** Adelaide SA 5001
Australia inve
nt
---
-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday,
30
November 2006 9:52 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness
I get this error:
/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max:
undefined symbol: zlibCompileFlags
Logan, David (SST - Adelaide) wrote:
Hi Bruce,
Do you have any messages in the MySQL log?
Regards
---
** _/ ** David Logan *** _/
*** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **** _/
Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia inve
nt
---
-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday,
30 November 2006 9:42 AM
To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness
I'm running MySQL 5.0.24a and I have a database I need to drop.
When
I
issue the command DROP DATABASE webdb the client seems to just
hang.
I
see the command hit the server in the full log but the database
never drops. Anyone have any suggestions?
--
One day at a time, one second if that's what it takes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Urgent please..
Try http://www.php.net/manual/en/install.windows.php --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Renish [mailto:[EMAIL PROTECTED] Sent: Thursday, 16 November 2006 1:44 PM To: mysql@lists.mysql.com Subject: Urgent please.. All, Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 I want to use PHP as sever side scripting. Cheers, Renish. -- 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: Urgent please..
Hi Renish, Perhaps these might be a more appropriate forum and also get you the result you need. This being a database list (I'm sure there are php experts on here) is probably not quite the right place to be asking. One of the php lists may produce a more relevant response. http://www.php.net/mailing-lists.php Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Renish [mailto:[EMAIL PROTECTED] Sent: Thursday, 16 November 2006 2:38 PM To: Joshua J. Kugler Cc: mysql@lists.mysql.com Subject: Re: Urgent please.. Ocourse i read the manual . I was unable to do so..ie the reason I asked...I also knew this location mysql@lists.mysql.com - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Renish [EMAIL PROTECTED] Sent: Thursday, November 16, 2006 11:20 AM Subject: Re: Urgent please.. On Wednesday 15 November 2006 18:14, Renish wrote: Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 Please read the documentation before asking questions such as these. Thanks. http://www.php.net/manual/en/install.windows.php j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- 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: can I recover a database from db files?
Hi Patrick, The error says a permissions problem. Did you change the ownership/group to mysql:mysql, to the mentioned files, after you restored them? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Patrick Aljord [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 November 2006 9:40 AM To: mysql@lists.mysql.com Subject: can I recover a database from db files? Hey all, I host my app on a friend server who make backup every night, well yesterday he installed another distro so I asked him for my db backup and it turns out the only backup he did was the whole hard drive. So he just sent me a tarball of my database directory containing: ads_categories.MYD,ads_categories.MYI,ads.frm,ads.MYD,ads.MYI,categories .frm,categories.MYD,categories.MYI,db.opt,regions.frm,regions.MYD,region s.MYI. I tried to create a database called mydb on my computer (debian etch) and just copied all the files to the place /var/lib/mysql/mydb/ then when I try to do a select * from categories, I get: ERROR 1017 (HY000): Can't find file: './mydb/categories.frm' (errno: 13) is there any way to get my db back? thanx in advance Pat -- 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: mysql-bin.00000X
Hi Tanner, These are the binary logs. Look in your my.cnf file or check your system variable ( do this ) mysql show variables like '%log%'; and see if you have log-bin enabled. These are generally used for recovery from the last full backup (similar to logical/redo logs in other technologies) and are also necessary for replication. You can delete them using the PURGE MASTER LOGS statement (documented here : http://dev.mysql.com/doc/refman/4.1/en/replication-master-sql.html) Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Tanner Postert [mailto:[EMAIL PROTECTED] Sent: Thursday, 31 August 2006 10:02 AM To: mysql@lists.mysql.com Subject: mysql-bin.0X there are ton of these files in my mysql data directory on fedora core 5 they are about a GB a piece. it appears that they are snapshots or some kind of log file. what is creating these files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1
Hi Folks, Does anybody have any idea when 5.1 may come to General Release? I am particularly interested in MySQL Cluster as I have several databases (around 50) totalling 26Gb and would like to consider moving to this version because of the Cluster Disk Data Storage and the fact the current hardware doesn't have the RAM requirements to hold these databases. I plan to store the data on a SAN and was wondering if anybody had any experience with this as yet? I have current thoughts along the lines of 2 x Pentium 4 1Ghz, 1Gb RAM as management nodes 4 x Pentium 4 1Ghz, 1Gb RAM as SQL nodes 2 x Pentium 4 1Ghz (x 8 cpus), 8Gb RAM as data nodes The databases are currently running, replicating and serving around 800 queries per second utilising a 100M network. Thanks --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent ---
REPAIR TABLE and mysqlcheck
Hi Folks, Does anybody know if it is possible to restrict access to these commands on an individual basis? I have a need to exclude users, who would otherwise have most privileges, and was wondering if there was a particular privilege that I could revoke. If not, does anybody have a technique that may provide for this? Thanks --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent ---
RE: Managing big mysqldump files
Hi Anil, Why not pipe the mysqldump direct into gzip? eg: mysqldump etc ... | gzip -c Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Anil [mailto:[EMAIL PROTECTED] Sent: Saturday, 19 August 2006 8:03 PM To: mysql@lists.mysql.com Subject: Managing big mysqldump files Hi List, We are facing a problem of managing mysqldump out put file which is currently of size 80 GB and it is growing daily by 2 - 3 GB, but we have a linux partition of only 90 GB.. Our backup process is first generate the mysqldump file of total database and then compress the dump file and remove the dump file. Is there any way to get compressed dump file instead of generating dump file and then compressing it later. Any ideas or suggestions please Thanks Anil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with updating table
Sounds like you will have to change the structure of your table. I would add an auto-increment column at the beginning of the row and use that as the primary key. You can still select on the horse, course etc. like so SELECT * FROM horse_and_courses WHERE selection criteria ORDER BY new_primary_key DESC LIMIT 1; This would return you the most recent record. The auto_increment column would be updated automatically during the import. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: wizard007 [mailto:[EMAIL PROTECTED] Sent: Friday, 11 August 2006 5:30 PM To: mysql@lists.mysql.com Subject: Problem with updating table I have a site with horse racing results and the results table consists of: Date, Time, Course, Horse, Odds, Result, Profit I have about 6 months results in there and obviously there are no unique fields. Each morning I imprt a new spreadsheet with the days racing selections without the odds and result field filled in. As the day goes on I use a page to update the results but when I update a race with horse that has run in the past it updates the new record and the old record as well. I now have 2 identical records as the data updates has overwritten the old record for the horses race before. Any ideas!?!?!?!? -- View this message in context: http://www.nabble.com/Problem-with-updating-table-tf2089249.html#a575833 9 Sent from the MySQL - General forum at Nabble.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: cell colors PHP/MySQL
http://www.php.net there are many code examples here. That is a probably more appropriate place for such a question Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brian E Boothe [mailto:[EMAIL PROTECTED] Sent: Thursday, 10 August 2006 4:55 AM To: mysql@lists.mysql.com Subject: cell colors PHP/MySQL i'm needing assistance in table Cell color thru MySQL i have a project were im listing a projects in a table either Complete / Active / Pending / now im needing these refrances cells to change Color whenver its lists them out on a page, [Complete] = green[Active] = Blue[Pending] = yellow Can someone Give me refrences or Code in PHP to how to accomplish this thanks -- 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: MySQL Replication Binary Logs - How Long to Keep?
Hi Warren, I would generally keep the binary log until you have done a full backup. Once that is done, all the transactions that are contained in those logs are now committed and saved in your database backup. All binary logs may be deleted that were created before that backup took place. The main reason for this is that it allows you to restore from your last backup and then roll forward all transactions (I use the term to describe anything modifying the database and logged in the binary log) until the database is back up to date. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Warren Crigger [mailto:[EMAIL PROTECTED] Sent: Thursday, 10 August 2006 9:52 AM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: MySQL Replication Binary Logs - How Long to Keep? I've just recently set up MySQL replication amongst two servers so I'm not too familiar with it. I was cleaning up my /var filesystem and found the binary data below being stored in /var/lib/mysql, taking up 1.5gb. I did a little reading on mysql.org docs. My interpretation was that you can have it replicate every so often, then you can purge these after that happens..however, my replication is instantaneous. I can insert a record on the master and then go select it on the slave immediately. Is there any reason to keep this data? It's replicated to the 2nd server, in addition to dumps of the more important databases nightly, and dumps of the entire server weekly, which go to different physical drives and then ultimately off-site. I like redudancy obviously, however, this seems pretty useless to me. If it is of use, is there a way I can put this on a different filesystem, keeping the databases themselves within /var/lib/mysql? Thanks, Warren [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# ls -lart |grep repl -rw-rw1 mysqlmysql 3088434 Jun 11 04:02 repl.001 -rw-rw1 mysqlmysql 107 Jun 11 04:02 repl.002 -rw-rw1 mysqlmysql188387006 Jun 18 04:02 repl.003 -rw-rw1 mysqlmysql 107 Jun 18 04:02 repl.004 -rw-rw1 mysqlmysql 107 Jun 25 04:02 repl.006 -rw-rw1 mysqlmysql156749380 Jun 25 04:02 repl.005 -rw-rw1 mysqlmysql153489679 Jul 2 04:02 repl.007 -rw-rw1 mysqlmysql 107 Jul 2 04:02 repl.008 -rw-rw1 mysqlmysql 107 Jul 9 04:02 repl.010 -rw-rw1 mysqlmysql140922795 Jul 9 04:02 repl.009 -rw-rw1 mysqlmysql58638790 Jul 11 17:30 repl.011 -rw-rw1 mysqlmysql 38410 Jul 11 17:46 repl.012 -rw-rw1 mysqlmysql 5927431 Jul 12 09:00 repl.013 -rw-rw1 mysqlmysql80007235 Jul 16 04:02 repl.014 -rw-rw1 mysqlmysql 107 Jul 16 04:02 repl.015 -rw-rw1 mysqlmysql 107 Jul 23 04:02 repl.017 -rw-rw1 mysqlmysql155468996 Jul 23 04:02 repl.016 -rw-rw1 mysqlmysql 107 Jul 30 04:02 repl.019 -rw-rw1 mysqlmysql155806419 Jul 30 04:02 repl.018 -rw-rw1 mysqlmysql 107 Aug 6 04:02 repl.021 -rw-rw1 mysqlmysql159420166 Aug 6 04:02 repl.020 -rw-rw1 mysqlmysql 6366383 Aug 6 13:33 repl.022 -rw-rw1 mysqlmysql 1138297 Aug 6 17:36 repl.023 -rw-rw1 mysqlmysql 264 Aug 6 17:40 repl.index -rw-rw1 mysqlmysql43014905 Aug 9 00:03 repl.024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replication maybe?
Hi Brian, Try having a good read here : http://dev.mysql.com/doc/refman/5.0/en/replication.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brian E Boothe [mailto:[EMAIL PROTECTED] Sent: Monday, 7 August 2006 8:47 AM To: mysql@lists.mysql.com Subject: replication maybe? hey i have a question ; if i have a MySQL database on our local intranet server, but i wanna be able to replicate that database to our internet site thats also a MySQL database so whenver something changes on our intranet it also changes on our Internet Database,,how do i do this ??? -- 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: I have 972 vmware-bin.000XXX files!
Hi Daevid, There are the binary logs. Yes they are used for replication or you can use them much the same as a logical log exists in some other technologies. You can find a good description of them at the mysql manual website at : http://dev.mysql.com/doc/refman/5.0/en/binary-log.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Friday, 4 August 2006 12:43 PM To: mysql@lists.mysql.com Subject: I have 972 vmware-bin.000XXX files! I use a VMware for LAMP development work. I just looked in my /var/lib/mysql dir and there are 972 of these vmware-bin.01 ... vmware-bin.000972 files! Yipes! Do I need them? Can I delete them? How do I prevent them from being created all the time? Can't they all just go into one vmware-bin file (as in append, like a .log file does)? I saw this post, but it doesn't answer any questions: http://forums.mysql.com/read.php?28,74385,74385 Googling for 'mysql bin.01' seems to indicate these are related to replication -- I'm not using replication. ÐÆ5ÏÐ P.S. I found this link: http://miniprep.caltech.edu/~remote/blog/?p=99 Which says to run reset master; -- which did get rid of them for now, but my questions above are still valid. -- 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: select between date
Hi, Try here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Penduga Arus [mailto:[EMAIL PROTECTED] Sent: Monday, 31 July 2006 9:03 PM To: mysql@lists.mysql.com Subject: select between date I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- 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: Mysqlcheck issues
The trouble with myisamchk is that it requires the server to be offline. This may not be suitable. Do you have a bad area on the disk? The easiest way would be to stop the server briefly, rename the index thus keeping it occupying the potentially bad part of the disk and recreate the index. This would at least take the disk out of the equation if the problem re-occurs. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Dilipkumar [mailto:[EMAIL PROTECTED] Sent: Friday, 28 July 2006 1:07 PM To: Dirk Bremer; mysql@lists.mysql.com Subject: Re: Mysqlcheck issues Hi, Instead of mysqlcheck you can use myisamckh to recover the data's. As myisamchk -r -o *.MY* Thanks Regards Dilipkumar - Original Message - From: Dirk Bremer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 27, 2006 8:32 PM Subject: Mysqlcheck issues I am using MySQL server 4.1.10 on Windows 2003 Server with MyISAM tables. I have an issue where occasionally an index (MYI) file becomes corrupted. I do not know why this occurs. To combat this issue, I tried running the following command every half-hour: mysqlcheck -Aamov --auto-repair --use-frm This command runs on the host. For some reason, when this command executes, the MySQL service aborts and the MYI for the main table is corrupted. I must then restart the service and repair the affected table. I am at a loss here. I would really like to use the --auto-repair option, but don't understand what is causing the service to abort. Your thoughts? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.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: undefined reference to `mysqlpp::Connection::Connection(bool)'
Hi Ali, Your problem is as it states /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status The linker, ld, does not know where to find the library libmysqlclient.so. You need to tell the linker using either ldconfig (man ldconfig) or LD_LIBRARY_PATH variable. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: ali asghar torabi parizy [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 6:54 PM To: mysql@lists.mysql.com Subject: undefined reference to `mysqlpp::Connection::Connection(bool)' Hi, I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i want to connect to MySQL through C. When I am compiling the program, I am getting the following error. #gcc -o custom1 -lmysqlclient -lz custom1.cpp /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostreamchar, std::char_traitschar std::operator std::char_traitschar (std::basic_ostreamchar, std::char_traitschar , char const*)'... ... ... ... ... ...std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, std::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_stringchar, std::char_traitschar, std::allocatorchar ::~basic_string()' /tmp/ccPQEF5p.o:(.gnu.linkonce.r._ZTIN7mysqlpp8BadQueryE[typeinfo for mysqlpp::BadQuery]+0x0): undefined reference to `vtable for __cxxabiv1::__si_class_type_info' /tmp/ccPQEF5p.o:(.eh_frame+0x12): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status ### my code is standard code in mysql++ examples directory please help me. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: undefined reference to `mysqlpp::Connection::Connection(bool)'
Hi Ali, I'll repeat what I've already said, it is the ld command that is having the problem. If you don't know what the ld command is or does, you need to look elsewhere for the answer as this is beyond the scope of this list. As I have previously mentioned, try $ man ldconfig Your problem is as it states /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient -- here is the problem collect2: ld returned 1 exit status The linker, ld, does not know where to find the library libmysqlclient.so. You need to tell the linker using either ldconfig (man ldconfig) or LD_LIBRARY_PATH variable. eg. export LD_LIBRARY_PATH=path to mysqlclient library Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: ali asghar torabi parizy [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 7:29 PM To: mysql@lists.mysql.com Subject: RE: undefined reference to `mysqlpp::Connection::Connection(bool)' Hi.thanks logan. I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i trying too many pathes in gcc cammand but that error continued. I am getting the following error. # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp #gcc -o custom1 -lmysqlclient -L /usr/local/mysqlpp/include/mysql++ custom1.cpp gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ -L /usr/local/mysqlpp/include/mysql++ custom1.cpp for all statements following error continued:( # /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostream std::char_traits std::operator (std::basic_ostream , char const*)'... ... ... ... ... ...std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_string, std::allocator ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_string, std::allocator ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_string, std::allocator ::~basic_string()' /tmp/ccPQEF5p.o:(.gnu.linkonce.r._ZTIN7mysqlpp8BadQueryE[typeinfo for mysqlpp::BadQuery]+0x0): undefined reference to `vtable for __cxxabiv1::__si_class_type_info' /tmp/ccPQEF5p.o:(.eh_frame+0x12): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status ### my code is standard code in mysql++ examples directory please help me. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:28 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- -- - - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- - - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives:
RE: Replication Problem
Hi Brett, Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 11:06 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert:
RE: Replication Problem
Hi Brett, I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 9:19 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: Replication Problem
Another suggestion, look for other files on the system ending in .MYD or put a query through that you know has updated a particular table/database and do a find looking for a file that has been updated in the last 5 minutes. See which ones have. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 10:18 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: Replication Problem
Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: Replication Problem
Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: How to copy a large innodb table
Hi Dominic, Why not just use an OPTIMIZE TABLE ? This will map to an ALTER table command for an InnoDB table which will free the now unused space. From the manual at http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. and from http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html 14.2.14.3. Defragmenting a Table snip snip It can speed up index scans if you periodically perform a null ALTER TABLE operation: ALTER TABLE tbl_name ENGINE=INNODB That causes MySQL to rebuild the table. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file. snip snip Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Monday, 17 July 2006 9:58 PM To: mysql@lists.mysql.com Subject: How to copy a large innodb table Recently I deleted ~200.000.000 rows out of a history table. Still there are 20.000.000 rows in the table. So now I want to clear some discspace by copying the table, dropping the old one and renaming the copy afterwards. Is there another (faster) way to do that and how does one copy such a large table? Normally I would do create table new like table old; insert into new select * from old; But this runs into this error: The total number of locks exceeds the lock table size. So I wrote a shell script which creates the table and copies like this: insert into new select * from old limit 100 offset 0 insert into new select * from old limit 100 offset 100 insert into new select * from old limit 100 offset 200 and so on. But I'm not sure if this will produce an exact copy of my table and on top of that the seventeenth loop fails with the same error mentioned above. Any help would be appreciated. Regards Dominik -- 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: Using MySQL as backend
Try http://us2.php.net/manual/en/ref.mysql.php --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 July 2006 1:54 PM To: sharvan kumar Cc: mysql@lists.mysql.com Subject: Re: Using MySQL as backend sharvan kumar wrote: Dear Sir/Madam I am working on a project where I am to use PHP as front end and MYSQL as backend.MySQL stand alone is working properly,but it is not connecting with PHP.Please tell me solution. Connect it. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- 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: Split a Delimited String in SQL ( PROCEDURE split_string )
Hi, There are a couple of errors in the way it is defined, firstly 1) There should be a ; after the DROP PROCEDURE statement 2) use a delimiter //, this stops the mysql client trying to interpret the other ; as the end of the command. It then takes everything until the next // as belonging to the PROCEDURE. 3) it also needs a ; after the final END 4) Will object if you don't have a database selected already as it is trying to create a temporary table. eg. DROP PROCEDURE IF EXISTS split_string; delimiter // CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END; // Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: listsql listsql [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June 2006 8:36 PM To: mysql@lists.mysql.com Subject: Split a Delimited String in SQL ( PROCEDURE split_string ) Hi all, I was trying this: http://forge.mysql.com/snippets/view.php?id=4 That is supposed to emulate a split() in mysql. Could anyone make it work ? I've been trying without luck. I 'm getting strange errors when trying to create this procedure. _ DROP PROCEDURE IF EXISTS split_string CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END __-- Best regards, MARTIN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aborting a greedy querry from the command line
Hi Scott, mysqladmin can issue a kill from the command line. You will have to issue a mysqladmin -u etc. etc. processlist to get the process id from mysql and then you can issue a mysqladmin -u etc. etc. kill mysql process id Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 June 2006 8:32 AM To: mysql@lists.mysql.com Subject: Aborting a greedy querry from the command line Sometimes I will issue something, slip of the fingers, and mysql will chew on it for hours, the best I can do is control-z to get out of it and log back in. But, I just learned these processes are still running, and I have to go in and kill them by hand. Is there any easy way to abort from the command line and have it really stop that query? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aborting a greedy querry from the command line
Hi Chris, I've noticed that a Ctrl-C will also leave the query running (5.0.22 - Linux) and I've had to use the kill from mysqladmin or mysql client to get rid of it. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 June 2006 8:37 AM To: mysql@lists.mysql.com Subject: Re: Aborting a greedy querry from the command line On Monday 19 June 2006 04:02 pm, Scott Haneda wrote: Sometimes I will issue something, slip of the fingers, and mysql will chew on it for hours, the best I can do is control-z to get out of it and log back in. But, I just learned these processes are still running, and I have to go in and kill them by hand. CTRL+Z simply puts a process in sleep mode so it can be woken up later on. CTRL+C will kill the MySQL process (thus killing the query). While I don't like that method, it's the only one I've found so far. Is there any easy way to abort from the command line and have it really stop that query? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- Chris White PHP Programmer/DB Overclocker Interfuel -- 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: MySQL (GPL License)
Hi Michael, I believe you can purchase a commercial license taking away the GPL provisions from your software if you do not wish to GPL your own software. You can enquire on the MySQL website. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Michael Louie Loria [mailto:[EMAIL PROTECTED] Sent: Thursday, 8 June 2006 9:45 AM To: mysql@lists.mysql.com Subject: MySQL (GPL License) Hello, I would like to inquire about the GPL License used by MySQL. Here's our scenario We developed our owned software needed by our operations using MySQL community edition under Windows platform. GPL says that we should distribute/share the source code. But I think it isn't even of interest or beneficial to others because it was done on the company specs. If we were required to distribute/share our source code. What distribution methods can be used? like uploading the source code in a site? or when someone walks in and asks for the source code, we should share it to them. Thanks, I just need to have some clarifications about the GPL Mic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql GUI
Hi Chris, I would agree, I have found it useful as well. It does have a few limitations (well the versions I've used) but once it is up and running, it works fine. eg. It doesn't help with replication, amongst a few others. I use it internally within an intranet so there isn't much of a security issue. Perhaps that was the intent? I'm a bit confused by the responses also. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: Wednesday, 31 May 2006 6:32 PM To: MySQL List Subject: Re: MySql GUI OK, so I suggested phpMyAdmin. Dan Trainor said: I highly suggest staying away from PHPMyAdmin. and, even more helpfully, 'Anthony' wrote: !!! ! :-\ Fine - from this I gather phpMyAdmin is perhaps not a Good Thing. Now, given that I'm a relative newbie to all this, can someone please tell me why in slightly more coherent terms? So far, I've found it very useful. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If you have to ask what jazz is, you'll never know. -- Louis Armstrong -- 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: Take my name off your list !!
Hi Michael, Have you tried the link at the bottom of every email? If not then have you looked here? http://lists.mysql.com/faq.php Shouting at everybody who chooses to subscribe (which you have at some stage) doesn't help Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Michael Price [mailto:[EMAIL PROTECTED] Sent: Wednesday, 31 May 2006 9:53 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Take my name off your list !! Original Message Follows From: Neeraj [EMAIL PROTECTED] To: 'Harish TM' [EMAIL PROTECTED],'sheeri kritzer' [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: RE: Problem in using LOAD DATA LOCAL INFILE Date: Wed, 31 May 2006 17:20:39 +0530 Hi harish Thanks for reply By executing this query for this table I am not getting any error.. But I am getting wrong result. I have tried many combinations for LOAD DATA LOCAL INFILE but I am getting the results in two ways... 1. all the data getting loaded in first columns of first row. Or 2. data for all columns getting loaded in first column only and getting error line now data for other columns Hope you will find the solution Cheers.:) Neeraj Black Bits -Original Message- From: Harish TM [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 30, 2006 10:23 PM To: sheeri kritzer Cc: Neeraj; mysql@lists.mysql.com Subject: Re: Problem in using LOAD DATA LOCAL INFILE I went through a lot of trouble with Load data local infile... There are a lot of things regarding Local that MySql does not allow due to security reasons. As mentioned before this you could give us the error... that might help alternatively try out mysqlimport http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html Its similar but a lot easier to use. However its a program so embedding it in code is not efficient.. hope this helps harish On 5/30/06, sheeri kritzer [EMAIL PROTECTED] wrote: What's the error you are getting? What MySQL version are you using? On 5/26/06, Neeraj [EMAIL PROTECTED] wrote: Hi all I have a table with following structure CREATE TABLE `dsc` ( `ch_code` varchar(255) default NULL, `name` varchar(255) default NULL, `inof` blob ) ENGINE=InnoDB DEFAULT CHARSET=latin1; And I have give a text file with following format Ü ¢ Ch.Code :02Ü MEATÜ 1 COUNTRY: JORDAN 2 COUNTRY: SAUDI ARABIA Ü ¢ Ch.Code :03Ü FISHÜ 1 COUNTRY: JORDAN Ü ¢ Ch.Code :04Ü FISHÜ 1 COUNTRY: JORDAN 2 COUNTRY: SAUDI ARABIA ... I am trying to import the text file into table. I have tried various ways but unable to find the solution. the code I am trying to import the file is as follows LOAD DATA LOCAL INFILE 'd:\\data3.txt' INTO TABLE dsc FIELDS TERMINATED BY 'Ü\n' LINES TERMINATED BY '¢\n' (ch_code, name, info); Can any one help me in loading this file... Regards Neeraj Black Bits -- 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] -- 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: i'm at a complete loss?
Hi Daniel,
Sounds like there are a few records missing out of the mysql tables. Did
you run mysql_install_db?
http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 30 May 2006 9:53 AM
To: David Griffiths
Cc: mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?
When I log in using mysql which by the way is the only user that I can
log
into with I get
snip
mysql USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database
'mysql'
/snip
I have to log in to using mysql -u mysql and it drops me into the mysql
command line. It seems as though the user mysql has absolutely no
privileges to do any thing and I cant log in as root. So I have no idea
what
I did wrong but this is the third time I installed MySQL41 and I don't
think
reinstalling would do any thing different.
Thanks go out to every one for trying to help me, but like I said I have
no
idea as to whats wrong.
On 5/29/06, David Griffiths [EMAIL PROTECTED] wrote:
Log into the server, and type, use mysql; without the quotes.
Look at the user table - that defines what user can connect to the
database, the ip addresses they can use, and the password
they must provide.
For example, you could enter,
INSERT INTO USER (host, user, password)
values ('127.0.0.1', 'mysql', password(mysql));
and
INSERT INTO USER (host, user, password)
values ('localhost', 'mysql', password(mysql));
Don't forget to do a flush privileges; afterwards (again, no
quotes);
The mysql schema is thoroughly (but dryly) documented @
http://dev.mysql.com
David
Daniel McQuay wrote:
Thanks Greg, I did try that here is what happened.
boxster# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: NO)
boxster#
So I really just don't know what to do from here. I'm up for any
ideas
if
any one has 'em.
On 5/29/06, Greg Maruszeczka [EMAIL PROTECTED] wrote:
On Sun, 28 May 2006 20:17:53 -0400
Daniel McQuay [EMAIL PROTECTED] wrote:
Hello list,
I just installed MySQL on my FreeBSD box here at home and am
having
a
few problems with privileges. I can connect to mysql using; mysql
-u
mysql and there is no password needed. However, when I try to
connect
to the server using root; mysql -u root I get an error;
snip
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
(using
password: NO)
/snip
so reading that it appears that I need a password so i try; mysql
-u
root -p it prompts me for my root pass and when I put it in it
does
that same thing above but with (using password: YES).
I went to the the MySQL web site and read 2.10.3 Securing the
Initial
MySQL Accounts and tried following along with that but with no
luck.
When checking google for help I read a lot about the initial
installation. Something about /usr/local/bin/mysql_install_db
will
install a privilege table.
I installed mysql using this guide here
http://raybdbomb.com/p/mysql-install-on-freebsd.html and every
thing
seemed to go well but like I said I keep getting this error. Is
there
something else I should do? Any help on this would be MUCH
appreciated.
Hi,
Wasn't clear to me in reading your post that you did this so here
goes:
Did you actually set a root password for mysql using a GRANT
statement after logging in with the default BLANK password?
mysql -u root -p [then just hit enter]
HTH,
G
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Daniel McQuay
[EMAIL PROTECTED]
boxster.homelinux.org
814.825.0847
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: i'm at a complete loss?
Hi Daniel,
Is this actually the instance that you have just installed? I know that
sounds trite but if it is running during the installation maybe it
hasn't been able to start the new server and you are still trying to
connect to the old one.
Kill the server currently running, restart it and try again.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 30 May 2006 10:20 AM
Cc: David Griffiths; mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?
Yeah when I ran it the first time I get this message:
snip
boxster# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables
To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password
'new-password'
See the manual for more instructions.
NOTE: If you are upgrading from a MySQL = 3.22.10 you should run
the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!
You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe
You can test the MySQL daemon with the benchmarks in the 'sql-bench'
directory:
cd sql-bench ; perl run-all-tests
Please report any problems with the /usr/local/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
boxster#
/snip
I then follow the directions on setting a password for root but I get
basically the same error message.
snip
boxster# /usr/local/bin/mysqladmin -u root password ''
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
boxster#
/snip
Even though I get an error message I tried running cd /usr/local ;
/usr/local/bin/mysqld_safe but I then get this error:
snip
boxster# cd /usr/local ; /usr/local/bin/mysqld_safe
[1] 33209
boxster# Starting mysqld daemon with databases from /usr/local/var
STOPPING server from pid file /usr/local/var/boxster.mydomain.com.pid
060529 20:45:47 mysqld ended
[1]Done ( cd /usr/local;
/usr/local/bin/mysqld_safe )
boxster#
/snip
I went to that directory and read the error log and it says I have an
instance of mysqld running, which I do. ps auxw | grep myslqd show it
there
running.
Thanks again!
On 5/29/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
Hi Daniel,
Sounds like there are a few records missing out of the mysql tables.
Did
you run mysql_install_db?
http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 30 May 2006 9:53 AM
To: David Griffiths
Cc: mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?
When I log in using mysql which by the way is the only user that I can
log
into with I get
snip
mysql USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database
'mysql'
/snip
I have to log in to using mysql -u mysql and it drops me into the
mysql
command line. It seems as though the user mysql has absolutely no
privileges to do any thing and I cant log in as root. So I have no
idea
what
I did wrong but this is the third time I installed MySQL41 and I don't
RE: i'm at a complete loss?
Ah, this is a little different. I know there are a couple of OS's (redhat AFAIK) that place the mysql socket in a different location http://dev.mysql.com/doc/refman/4.1/en/problems-with-mysql-sock.html Your socket has probably been placed in a spot where the server is not expecting it. You may need to update the my.cnf files to reflect the correct location or add a --socket option to the server when it starts. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- From: Daniel McQuay [mailto:[EMAIL PROTECTED] Sent: Tuesday, 30 May 2006 10:39 AM To: Logan, David (SST - Adelaide) Cc: David Griffiths; mysql@lists.mysql.com Subject: Re: i'm at a complete loss? Nah, that's not the case my friend. I made sure that when I installed mysql that the old one was completely removed. But, I do think I figured something out. I was reading the MySQL documentation and it said that if I was having problems with mysql_install_db that I should move the mysql directory, the one with all my information, to mysql.old and try the mysql_install_db. I did that and every thins seemed to go just fine. Now my problem is with this: snip boxster# /usr/local/bin/mysqladmin -u root password 'flNT3b4c' /usr/local/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! /snip Have you ever seen that error before? Now I cant connect at all. :( On 5/29/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote: Hi Daniel, Is this actually the instance that you have just installed? I know that sounds trite but if it is running during the installation maybe it hasn't been able to start the new server and you are still trying to connect to the old one. Kill the server currently running, restart it and try again. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Daniel McQuay [mailto: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Tuesday, 30 May 2006 10:20 AM Cc: David Griffiths; mysql@lists.mysql.com Subject: Re: i'm at a complete loss? Yeah when I ran it the first time I get this message: snip boxster# /usr/local/bin/mysql_install_db --user=mysql Installing all prepared tables Fill help tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/bin/mysqladmin -u root password 'new-password' /usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd /usr/local ; /usr/local/bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/local/bin/mysqlbug script
RE: SPAMs
Hi Jørn, I myself haven't had too many spams, I get a few anyway from various places my email address is placed. I would suggest try unsubscribing using this alias and just re-subscribe with your new (known only to you) alias. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jrn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Wednesday, 24 May 2006 9:01 PM To: mysql@lists.mysql.com Subject: SPAMs Seems like someone has got their hand of my e-mail alias which I use for this list only. It started last friday and I get about 10-15 messages every day. Does someone else has this problem? What's the procedure to change my subscribtion e-mail addresse? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- 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: InnoDB problems under 5.1.9
Hi Ben, Try doing SHOW ENGINES; and see what it says. It should say InnoDB is supported, if not then it hasn't compiled in. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 May 2006 4:57 PM To: mysql@lists.mysql.com Subject: InnoDB problems under 5.1.9 Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben -- 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: InnoDB problems under 5.1.9
Hi Ben,
I thought the InnoDB engine was included without having to set a
./configure option. On my latest build (admittedly a 5.0.18 one) InnoDB
was enabled without setting any ./configure option.
My options were
hambone /usr/dev/src/mysql-5.0.18 $ cat ../../build_scripts/mysql-5.0.18
#!/bin/bash
./configure \
--prefix=/usr/local/mysql-5.0.18 \
--enable-thread-safe-client \
--with-unix-socket-path=/tmp \
--with-openssl \
--with-example-storage-engine \
--with-archive-storage-engine \
--with-csv-storage-engine \
--with-blackhole-storage-engine \
--with-ndbcluster \
--with-ndb-test \
--with-ndb-port=3510 \
--with-ndb-port-base=3710 \
--with-federated-storage-engine
This worked fine. If I get time later, I may give it a punt with your
version. Sorry I can't be of more help.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 23 May 2006 5:50 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: InnoDB problems under 5.1.9
Logan, David (SST - Adelaide) wrote:
Hi Ben,
Try doing
SHOW ENGINES;
Here: (pertinent cols only)
++--+--+-++
| Engine | Support | Transactions | XA | Savepoints |
++--+--+-++
| CSV| YES | NO | NO | NO |
| MEMORY | YES | NO | NO | NO |
| MRG_MYISAM | YES | NO | NO | NO |
| InnoDB | DISABLED | YES | YES | YES|
| BLACKHOLE | YES | NO | NO | NO |
| MyISAM | DEFAULT | NO | NO | NO |
| BerkeleyDB | DISABLED | YES | NO | YES|
| ARCHIVE| YES | NO | NO | NO |
++--+--+-++
Ok, now I believe this does mean I have compiled the InnoDB.
Just to save me trawling though the manual, can you tell me how I should
enable InnoDB?
Thanks for the info!
Ben
and see what it says. It should say InnoDB is supported, if not then
it
hasn't compiled in.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 23 May 2006 4:57 PM
To: mysql@lists.mysql.com
Subject: InnoDB problems under 5.1.9
Dear MySQL,
I've installed 5.1.9 from source on a SUSE 10 box. But I can't get
InnoDB tables respected.
I have used the correct compilation flag (--with-innodb).
SHOW VARIABLES; lists all the usual innodb variables.
The innodb table space has been created in ~/var/ibdata1.
But if I enter:
CREATE TABLE a (
a int NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
SHOW CREATE TABLE a;
CREATE TABLE `a` (
`a` int(10) NOT NULL PRIMARY KEY
) ENGINE=MyISAM
As you can see, an InnoDB has become an MyISAM and will be stored in
~/var/test/a.*
I am using the large table .cnf file. Everything else is much as
default.
Can anybody help me?
Regards,
Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: than you for your answer but...
There is no reason why you can't use single quotes around the HTML. You don't appear to have any single quotes with the HTML so this should work fine. Everything, including the double quotes, should be then stored in your database. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 10:03 AM To: Steve Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: than you for your answer but... Steve wrote: thank you for the clue of double quoting the html when inserting into the database but I still get the error. the problem seems to be that I have quotes INSIDE the html code as well (links and pix) so, i still get this error. Database Selected: candlecatalog Query: insert into Products (ProdID,ProdName,ProdType,ProdDesc,Price,Pic,Buy) Values (VOALM,Almond Votive,Votive,Our Almond scented Candles are melon colored,1.25,,a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.the melt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/ca rt.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html; img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/image s/bu y_button_f.gif border=0 / /a) Results Error 1064: 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 'href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_hhttp://www.c cnow .com/cgi-local/cart.cgi?meltingpot_VOALM_h Your problem is not with MySQL but with the programming language you're using. It probably has a facility to do this. PHP, as another poster has noted, has the function mysql_real_escape_string(). But you are not using PHP. What language are you using? Do you have a manual for it? Does it have a section on interfacing with MySQL? On escaping quotes? the code i want to insert is: a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.the melt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/ca rt.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html; img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/image s/bu y_button_f.gif border=0 / /a Since you surround this with double quotes and then include more double quotes inside it, perhaps you are not a native speaker of a language that uses for quotes. In the ASCII character set (and on most keyboards), the beginning quotation mark and ending quotation mark are identical. This makes for difficulties when trying to enclose quotation marks within the larger quote. You can use the single quote ' inside double quotes or you can escape the quotes (by preceding them with an escape character: the backslash character in the case of MySQL). Hope that helps. --J PS Try to keep your messages in the same thread by replying to another message in that thread and not changing the subject. -- 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: Installing DBD::mysql - problems
Hi Connie, Looks like you have hit http://bugs.mysql.com/bug.php?id=18091 (which hasn't been fixed yet) potential solutions are documented there. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Logg, Connie A. [mailto:[EMAIL PROTECTED] Sent: Thursday, 11 May 2006 5:47 AM To: mysql@lists.mysql.com Subject: Installing DBD::mysql - problems I am trying to install DBD::mysql on the following node: [EMAIL PROTECTED] cal]# uname -a Linux snv1 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux [EMAIL PROTECTED] cal]# more /etc/redhat-release Red Hat Enterprise Linux WS release 4 (Nahant Update 3) [EMAIL PROTECTED] cal]# I have the following rpms installed [EMAIL PROTECTED] cal]# rpm -qa | grep -i mysql MySQL-devel-standard-5.0.20a-0.rhel4 MySQL-client-standard-5.0.20a-0.rhel4 MySQL-shared-standard-5.0.20a-0.rhel4 MySQL-server-standard-5.0.20a-0.rhel4 And DBI installed ok. [EMAIL PROTECTED] utils]$ ./seeperlmods Archive::Tar -- 1.29 CPAN -- 1.87 Compress::Zlib -- 1.41 Cwd -- 3.17 DBD::Multiplex -- 1.98 DBI -- 1.50 -- DBI::Shell -- 11.93 Data::ShowTable -- undef Digest::MD5 -- 2.36 IO::Tee -- 0.64 IO::Zlib -- 1.04 Net::Daemon -- 0.39 Net::Telnet -- 3.03 Perl -- 5.8.5 RPC::PlServer -- 0.2018 Storable -- 2.15 Term::ReadKey -- 2.30 Term::ReadLine -- 1.01 Text::Reform -- 1.11 When I try to install the DBD::mysql I get errors: [EMAIL PROTECTED] DBD-mysql-3.0002_5]# /usr/bin/perl Makefile.PL I will use the following settings for compiling and testing: cflags(mysql_config) = -I/usr/include/mysql -g -pipe -m64 embedded (mysql_config) = libs (mysql_config) = -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ps-protocol (default ) = 1 ssl (guessed ) = 0 testdb(default ) = test testhost (default ) = testpassword (default ) = testsocket(default ) = testuser (default ) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ / usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 741 Using DBI 1.50 (for perl 5.008005 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.5/ x86_64-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::mysql probably ok as it picked one When I run make: [EMAIL PROTECTED] DBD-mysql-3.0002_5]# make gcc -c -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -g -pipe - m64 -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr /local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\ 3.0002_5\ -DXS_VERSION=\3.0002_5\ -fPIC -I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE dbdimp. c gcc -c -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -g -pipe - m64 -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr /local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\ 3.0002_5\ -DXS_VERSION=\3.0002_5\ -fPIC -I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE mysql.c Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so /usr/bin/perl myld gcc -shared dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so -L/usr/lib64/mysql -l mysqlclient -lz -lcrypt -lnsl -lm /usr/bin/ld: /usr/lib64/mysql/libmysqlclient.a(libmysql.o): relocation R_X86_64_32 against `a local symbol' ca n not be used when making a shared object; recompile with -fPIC /usr/lib64/mysql/libmysqlclient.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1 [EMAIL PROTECTED] DBD-mysql-3.0002_5]# I tried using /usr/bin/perl Makefile.PL --cflags fPIC And doing the make afterword, but that failed miserably with many many errors snip dbdimp.c:3017:
RE: Backups with MySQL/InnoDB
Hi Greg, Maybe similar features to that of bacula (my current backup software of choice for my wifes business servers). This is a very comprehensive open source solution that has many of the features requested below. eg. multiple servers, pooling, aging etc. It is a good example of what my own requirements would be. Is the intention to have a MySQL type plugin? eg. will it have an api that will be open to other backup solutions being able to utilise what will be written? It would be nice to be able to utilise a standard XBSA solution giving access to the database from any one of the major enterprise backup solutions, eg. Legato Networker, Veritas Netbackup, HP Dataprotector etc. etc. This would allow an enormously simple and straightforward integration into many of the existing corporate solutions that exist around the world. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Duzenbury, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, 9 May 2006 6:39 AM To: mysql@lists.mysql.com Subject: RE: Backups with MySQL/InnoDB -Original Message- From: Daniel da Veiga [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 1:55 PM To: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On 5/8/06, David Hillman [EMAIL PROTECTED] wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). -- David Hillman LiveText, Inc 1.866.LiveText x235 In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. It would then be really sweet to be able to say 'restore a full backup of x database as of April 2, 2005 at 8:42 am' and have it create a new instance on a user defined port, then restore the closest previous full, then apply the binlogs up to the correct point in time. Thanks. Regards, Rich -- 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: updates during database dump
Hi Luke, Like most backup systems, there is no window unless you are locking the table/database. If the record has been read and passed out to mysqldump, any further updates will not be included until the next backup. The only way to ensure you get complete referential integrity is to stop any applications and lock all users out of the database. This is highly impractical in many cases so a hot backup is the only solution. A common solution is to have the database on a mirrored volume, drop the apps, break the mirror, restart the apps and backup from the unused half of the mirror. Rejoin the mirror at the end of the backup. If you are using InnoDB, Innobase have a hot backup product which (I believe) works well. Other than that, I don't know if there is any product currently for the MyISAM engine but I believe there are noises afoot from MySQL in this regard. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Monday, 8 May 2006 8:32 AM To: MySQL List Subject: updates during database dump Hi. When mysql is doing a dump, do the updates that happen during the dump get included in the dump. I have a dump that starts at 11pm and goes for 2 hours. If someone updates data at say 11:45pm, does that update get included in the dump? When does the window, on what gets included in a dump, close? Thanks. Kind regards. -- Luke Vanderfluit. Analyst/Programmer. Internode Systems Pty. Ltd. -- 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: How to repair a table,
Hi Payne, This should have all the info you need, there is also another command myisamchk which requires the server to be down while it repairs. mysqlcheck does not. http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Payne [mailto:[EMAIL PROTECTED] Sent: Monday, 8 May 2006 1:45 PM To: mysql@lists.mysql.com Subject: How to repair a table, hi, I got a table where the myi isn't able to re be read. I tried to run myisam but it give an error about the index. Do I need to drop the table? Can it be repaired? Payne -- 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: How to move the MySQL data directory?
Hi Yves,
You could also have changed the directory in the global /etc/my.cnf file
by setting
datadir=/path/to/mysql/data
This is pretty simple and works a lot easier than hacking the init
scripts.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Yves Goergen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 3 May 2006 8:01 PM
To: paul rivers
Cc: mysql@lists.mysql.com
Subject: Re: How to move the MySQL data directory?
On 03.05.2006 01:21 (+0100), paul rivers wrote:
Specify the data dir in the local my.cnf and be sure your instance
uses it
by starting it with the --defaults-file parameter set to that
instance's
local copy.
Okay, since hacking seems to be required anyway, I hacked it the
straight-forward and least-change way. I already had datadir=... changed
in the init script to the correct location. Now I also insert some
variables corrections in bin/mysqld_safe:
# here are the lines where ledir is set totally wrong...
MY_BASEDIR_VERSION=`pwd`
ledir=${MY_BASEDIR_VERSION}/bin
DATADIR=`pwd | sed -r s;/usr/local/;/var/;`/data
defaults=--defaults-file=${DATADIR}/my.cnf
# user=... and so on
This does the job pretty well for MySQL 4.0. Need to do it with every
upgrade, but I think I can automate it.
MySQL 5.0 required a less invasive hack though. I saw that setting
datadir= in the proposed init script is for nothing at the very
beginning since it's overwritten again right below. So moving that line
further down helped. Then the mysqld_safe call in the 'start' section
required an additional parameter --defaults-file=$datadir/my.cnf to make
it read my socket name, IP port etc.
Now both servers are up and running fine again, side by side, with the
*entire* data directory moved somewhere else, saving me from handling
that with every upgrade. Thanks for your help, I thought it could be
done an easy way but it seems nobody has thought about doing that
before. At least I don't have the impression, from reading the scripts.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
http://beta.unclassified.de - My web laboratory.
--
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: How to move the MySQL data directory?
Apologies, I didn't read your initial posting properly. Perhaps a glance
at this http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would
provide you the facilities that you require for using multiple servers.
This work well and enables you to manage the multiple global
configuration files required.
Regards
Hi Yves,
You could also have changed the directory in the global /etc/my.cnf file
by setting
datadir=/path/to/mysql/data
This is pretty simple and works a lot easier than hacking the init
scripts.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Yves Goergen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 3 May 2006 8:01 PM
To: paul rivers
Cc: mysql@lists.mysql.com
Subject: Re: How to move the MySQL data directory?
On 03.05.2006 01:21 (+0100), paul rivers wrote:
Specify the data dir in the local my.cnf and be sure your instance
uses it
by starting it with the --defaults-file parameter set to that
instance's
local copy.
Okay, since hacking seems to be required anyway, I hacked it the
straight-forward and least-change way. I already had datadir=... changed
in the init script to the correct location. Now I also insert some
variables corrections in bin/mysqld_safe:
# here are the lines where ledir is set totally wrong...
MY_BASEDIR_VERSION=`pwd`
ledir=${MY_BASEDIR_VERSION}/bin
DATADIR=`pwd | sed -r s;/usr/local/;/var/;`/data
defaults=--defaults-file=${DATADIR}/my.cnf
# user=... and so on
This does the job pretty well for MySQL 4.0. Need to do it with every
upgrade, but I think I can automate it.
MySQL 5.0 required a less invasive hack though. I saw that setting
datadir= in the proposed init script is for nothing at the very
beginning since it's overwritten again right below. So moving that line
further down helped. Then the mysqld_safe call in the 'start' section
required an additional parameter --defaults-file=$datadir/my.cnf to make
it read my socket name, IP port etc.
Now both servers are up and running fine again, side by side, with the
*entire* data directory moved somewhere else, saving me from handling
that with every upgrade. Thanks for your help, I thought it could be
done an easy way but it seems nobody has thought about doing that
before. At least I don't have the impression, from reading the scripts.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
http://beta.unclassified.de - My web laboratory.
--
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: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
You could further explain the lats and longs being slightly off by the use of a different datum. There are many many datums utilised by different geographical/geological authorities. This difference could become quite large dependent upon the datum used. As gmail user as noted, negative = West and South, positive = North and East. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 April 2006 11:17 AM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) Daevid Vincent wrote: My confusion is that I have some formulas to plug in these values, but it seems to me that if I use the wrong set of data, my zipcodes will be wrong too. I also don't understand why there is even such a difference. I can understand a few decimal points being different, but I don't understand how they are positive and negative, when it's supposed to be based upon the equator and the prime meridian. Hasn't that already been explained here? Sign is entirely a matter of convenience and convention. PB -Original Message- From: Gmail User [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 6:03 AM To: mysql@lists.mysql.com Subject: RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there).
RE: problem with coalesce function
Hi Pedro, What version of MySQL are you running? From the manual COALESCE(value,...) Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. mysql SELECT COALESCE(NULL,1); - 1 mysql SELECT COALESCE(NULL,NULL,NULL); - NULL COALESCE() was added in MySQL 3.23.3. -- Are you running at least that? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 7:47 PM To: mysql@lists.mysql.com Subject: problem with coalesce function When I use coalesce function I receive this error: FUNCTION sirius.coalesce does not exist. sirius is the name of db. Can someone help me with this error Thanks, Pedro ___ O SAPO já está livre de vírus com a Panda Software, fique você também! Clique em: http://antivirus.sapo.pt -- 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: Reply / Return Address of this List
It is rather a shame if the method of reply is all that it takes to turn you off the list. Personally the content of the list is far more important to me. There are forums if you want a web interface. Having been through this argument on another list recently, I can say there is no pleasing all of the people so we might as well get used to variety. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jason Teagle [mailto:[EMAIL PROTECTED] Sent: Tuesday, 18 April 2006 10:09 PM To: mysql@lists.mysql.com Subject: RE: Reply / Return Address of this List The battle has been fought before - and the list administrator has given his reasons why he has not made the requested change. The way the list currently behaves is not an accident or omission, but a deliberate decision. I do not recall the grounds for that decision - maybe RFCs or the behaviour of certain email clients (which probably does not include your own), or maybe the fact that an individual reply is often very difficult if the default is group reply but no the other way round. But I would have thought that 99% of replies should go to the list, and personal replies are the exception - as has been pointed out here, and many times on other lists, a public answer benefits many people. However, if the subject has been addressed and the decision made, then there's just no point in this topic. I guess those of us that don't like it, or don't like people inadvertently posting personal replies thanks to that decision, should simply find another list. -- Jason Teagle [EMAIL PROTECTED] -- 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: Importing raw MySQL files
Hi Chris, Looks like you may have to either upgrade your current server to the version the Arch Linux was running or install a second temp server of that version, export the databases and then import them into the ver 3.2 server. Personally, I'd look closely at upgrading if at all possible. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Christopher Beale [mailto:[EMAIL PROTECTED] Sent: Monday, 17 April 2006 7:46 PM To: mysql@lists.mysql.com Subject: Importing raw MySQL files Hi everyone. I used to run mysql on a local server here, which has some databases which are very important to me on it. The server was running mysql on Arch Linux, but unfortunatly this crashed, I was not able to rebuild the operating system but I was able to recover the raw database files off of the hard disk. Is there a way of importing these to my MySQL 3.2 server? (I beleive that Arch Linux was running 5.0). I have tried simply placing them in the mysql database folder but I get errors such as Incorrect information in file: './my0007/ee_pm.frm... when I try and perform any operations in PhpMyAdmin. Any help would be appreciated as I do not want to have to rebuilt the databases from scratch/ Cheers Chris -- 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: ~ How to install 3 instances of mysql~
Hi Abdul,
As I only use MyISAM, I can only reinforce Keith Roberts mentioned,
these are three separate instances and will require three separate
memory spaces.
Have you looked here?
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html and
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
I could also recommend the book by Jeremy Zawodny, High Performance
MySQL. This has a fair bit of information which would be helpful to you.
Chapter 6, Server Tuning, is on the mysql website here
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
Sorry, that is about all the help I can be as I am no InnoDB expert.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Monday, 17 April 2006 12:35 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: ~ How to install 3 instances of mysql~
Hi Logan,
Thanks for the links. But how am i suppose to allocate my physical
memory for the 3 mysql instances. Pls guide me on the innodb memory
settings. My total RAM memory is 1 GB.
Thanks in advance,
Abdul.
On Sat, 2006-04-15 at 23:35 +1000, Logan, David (SST - Adelaide) wrote:
Hi,
Also a read of
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html and
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would
probably
be quite helpful. The second gives you a good idea on how to setup the
my.cnf file for multiple servers. The concepts are the same for
version
4.1
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Saturday, 15 April 2006 10:45 PM
To: mysql@lists.mysql.com
Subject: Re: ~ How to install 3 instances of mysql~
On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote:
To: [EMAIL PROTECTED]
From: Mohammed Abdul Azeem [EMAIL PROTECTED]
Subject: Re: ~ How to install 3 instances of mysql~
Hello Keith,
Thank you very much for your guidence.
This is my existing my.cnf file
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_cache = 8
query_cache_size = 32M
log_bin_trust_routine_creators = 1
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
#specify the storage engine
default-storage-engine = InnoDB
#specify the table type
default-table-type = InnoDB
#enable the full query log
log
#Print warnings to error log file
log_warnings
#specify max connections
max_connections = 30
#specify max user connections
max_user_connections = 12
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /mysql-system/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql-system/mysql/data/
innodb_log_arch_dir = /mysql-system/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5242880
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100
The variables key_buffer = 384M, innodb_buffer_pool_size = 384M,
innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M
are set for 1 instance.
My RAM memory is 1GB. Can i have the same values for the above
RE: ~ How to install 3 instances of mysql~
Hi, Also a read of http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html and http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would probably be quite helpful. The second gives you a good idea on how to setup the my.cnf file for multiple servers. The concepts are the same for version 4.1 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, 15 April 2006 10:45 PM To: mysql@lists.mysql.com Subject: Re: ~ How to install 3 instances of mysql~ On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote: To: [EMAIL PROTECTED] From: Mohammed Abdul Azeem [EMAIL PROTECTED] Subject: Re: ~ How to install 3 instances of mysql~ Hello Keith, Thank you very much for your guidence. This is my existing my.cnf file [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M thread_cache = 8 query_cache_size = 32M log_bin_trust_routine_creators = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #specify the storage engine default-storage-engine = InnoDB #specify the table type default-table-type = InnoDB #enable the full query log log #Print warnings to error log file log_warnings #specify max connections max_connections = 30 #specify max user connections max_user_connections = 12 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /mysql-system/mysql/data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /mysql-system/mysql/data/ innodb_log_arch_dir = /mysql-system/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5242880 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M are set for 1 instance. My RAM memory is 1GB. Can i have the same values for the above variables set for all the 3 instances ? if yes, will that not exceed the available RAM memory. Please help me set the correct values for these variables for each instance. Well, if you are running multiple instances of mysqld concurrently, then I guess you will have to share the system resorces as well, such as memory and file handles. Note that if you are using qps to monitor processes, for each mysqld you have running, the memory usage will increase appropriately. I only use MyISAM tables the moment, so I cannot help you with your InnoDB table settings. Kind Regards Keith Roberts -- 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: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory
Hi Tom, Are you using the --quick option? --quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. From the manual at page http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Tom Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, 14 March 2006 10:27 PM To: mysql@lists.mysql.com Subject: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory Hi Trying to dump a complete db where 1 of the tables contains about 88 million rows - When the dump runs on the command line the following error is thrown mysqldump: Out of memory (Needed 3543176 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Is it possible to give more memory to the client when running on the command line? thanks -- 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: mysql.sock gone
Do you have any cron jobs that clear the /tmp directory during the day? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Anton Krall [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 March 2006 7:38 AM To: mysql@lists.mysql.com Subject: mysql.sock gone Guys. I just started having a problem, Im running mysql-standard-4.1.12-pc-linux-gnu-i686 binaries under Fedora Core 3 and Ive been having problem where during some parts of the day, /tmp/mysql.oskc goes away, I can still see mysql running when I do a ps ax but when I try to connect to it, it says it cant connect thru mysql.sock What could be the cause that would make mysql.sock dissapear? Thx for any help you can provide -- 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: Checking for good update
Perhaps reading the manual would be a good start at
http://us3.php.net/manual/en/function.mysql-num-rows.php
It clearly states
Retrieves the number of rows from a result set. This command is only
valid for SELECT statements. To retrieve the number of rows affected by
a INSERT, UPDATE, or DELETE query, use mysql_affected_rows().
You are using a function not valid for an UPDATE
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: fbsd_user [mailto:[EMAIL PROTECTED]
Sent: Monday, 13 March 2006 8:00 AM
To: Mysql
Subject: Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
49
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
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: mysql_query gives Resource id #3 error
Hi,
This is more a question of how to use php, so perhaps this page may be
of more use http://us2.php.net/manual/en/function.mysql-query.php
It gives a good explanation there of how the SELECT statement will
return the resource number on success, if you wish to access the data
returned, you need to use one of several other functions eg.
mysql_fetch_array etc. In other words, your query worked fine, you just
haven't accessed the data returned yet.
http://www.php.net is a valuable resource on how to use this fine
language.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Pat Adams [mailto:[EMAIL PROTECTED]
Sent: Sunday, 12 March 2006 4:45 PM
To: Mysql
Subject: Re: mysql_query gives Resource id #3 error
On Sat, 2006-03-11 at 12:53 -0500, fbsd_user wrote:
$sql = SELECT logon_id
FROM members
WHERE logon_id = '$logonid' AND logon_pw =
'$logonpw';
$result = mysql_query($sql) or die('Query failed. ' .
mysql_error());
print $result; shows Resource id #3
Where can I find meaning for what this means?
And why does mysql_error() not contain the description of this
error?
And why was the 'or die' condition not taken?
Try print($result[0]) or print($result['logon_id']); $result is a handle
to the result set, not something you can print. It's the same thing as
if you tried to print out the return value of mysql_connect, which
should return a resource id. There wasn't an error with the query, so
the or die shouldn't execute, and mysql_error should return null.
--
Pat Adams
Digital Darkness Promotions
Check out the Dallas Music Wiki http://digitaldarkness.com/tiki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php and mysql
If you still have issues after that, then read
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: JC [mailto:[EMAIL PROTECTED]
Sent: Friday, 10 March 2006 6:42 AM
To: Mary Adel
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: php and mysql
On Thu, 9 Mar 2006, Mary Adel wrote:
Thanks for al your help and i di that and now i have another error
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)
my code is as follows:
?php
print hi;
mysql_connect('localhost', 'root','') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if u can help in this i ll appreciate that a lot
On Thu, 2006-03-09 at 13:37 -0500, fbsd_user wrote:
You need a login id and password unless this is test DB added under
ID root
You have to use the same login id as the one you created the
db/table with.
mysql_connect('localhost', 'Login id', 'pw') or die(mysql_error())
mysql_connect('localhost', 'root') or die(mysql_error())
-Original Message-
From: Mary Adel [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 3:06 PM
To: mysql@lists.mysql.com
Subject: php and mysql
I have a severe problem that php5 cannot connect to mysql and i
don't
know why
also i am using linux
here is my peice of code
:?php
print hi;
mysql_connect('localhost', '', '') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if their is any configuration please tell me
thanks,
mary
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
hi mary,
take a look at
http://www.stanton-finley.net/fedora_core_5_installation_notes.html#MySQ
L.
basically, you need to make sure that your mysql server accepts local
connection.
if you login to your mysql server:
mysql -u root (if no password for root) or
mysql -u root -p (if there is pw for root)
and then:
select user,host,password from mysql.user;
if you don't see an entry for root localhost, then you need to add it
in.
hope that help.
JC
--
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: Movable Type + OSXServer + MySQL issues.
Hi, Looks like you don't have the DBI installed, doesn't appear to be anything to do with the installation of MySQL. The first line of the message states it cannot find DBI.pm in the include path. Try installing that from CPAN and retry the installation. You may have to set your perl paths (man perlrun , look for PERL5LIB) to allow perl to find the pm files. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: m i l e s [mailto:[EMAIL PROTECTED] Sent: Saturday, 4 March 2006 8:18 AM To: MySQL Subject: Movable Type + OSXServer + MySQL issues. Hi, Im having a rather nasty time installing MT on my G5XServe. IM trying to use MySQL but Im getting a rather NASTY error Stock Install of MySQL that came with the G5. ++ Got an error: Unsupported driver MT::ObjectDriver::DBI::mysql: Can't locate DBI.pm in @INC (@INC contains: /Volumes/webserver/ ~shoreweddings.com/blog/extlib lib /System/Library/Perl/5.8.6/darwin- thread-multi-2level /System/Library/Perl/5.8.6 /Library/Perl/5.8.6/ darwin-thread-multi-2level /Library/Perl/5.8.6 /Library/Perl /Network/ Library/Perl/5.8.6/darwin-thread-multi-2level /Network/Library/Perl/ 5.8.6 /Network/Library/Perl /System/Library/Perl/Extras/5.8.6/darwin- thread-multi-2level /System/Library/Perl/Extras/5.8.6 /Library/Perl/ 5.8.1 .) at lib/MT/ObjectDriver/DBI.pm line 10. BEGIN failed--compilation aborted at lib/MT/ObjectDriver/DBI.pm line 10. Compilation failed in require at lib/MT/ObjectDriver/DBI/mysql.pm line 10. BEGIN failed--compilation aborted at lib/MT/ObjectDriver/DBI/mysql.pm line 10. Compilation failed in require at (eval 6) line 1. BEGIN failed--compilation aborted at (eval 6) line 1. ++ Anyone seen this before ? And how best to resolve it ? M i l e s. -- 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: email to db
Hi Mark, You will need to setup a processing script on your mailer that will do that for you. Your ISP maybe able to set this up for you. Most mailers use the aliases file for that. A quick google found this as an example, yes it is talking to Oracle but the principles are exactly the same. http://philip.greenspun.com/doc/email-handler Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: Saturday, 25 February 2006 1:19 PM To: mysql@lists.mysql.com Subject: email to db Can anyone tell me if it is possible to send an email from outlook to a DB so it updates a record. I have a php tipping script hosted with a mysql DB but would like users to email their tips instead of loggin on the site. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Hi Rithish, I'm all out of ideas with this one, sorry I can't be of more help. Perhaps Mr Tuuri or others with more nouse than myself can help. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 5:02 PM To: Logan, David (SST - Adelaide); MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hello David. Thanks for the prompt response. The permissions were the first thing that I checked when I got the error. In fact, I even tried giving 777 permissions on the .ibd files. No results. Regards, Rithish. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 10:42 AM To: Rithish Saralaya; MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Hi Michael, Have you considered a Master-Master (or more if required) replication setup for achieving what you mentioned below? That would certainly provide the I'll show you mine if you show me yours scenario. I can recommend the High Performance MySQL book by Jeremy Zawodny as it has an example of the setup required. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Friday, 17 February 2006 7:14 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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: Hi, newbie question on a select statement
Hi Ariel, It works fine for me localhost.testshow create table mytest\G *** 1. row *** Table: mytest Create Table: CREATE TABLE `mytest` ( `id` int(11) NOT NULL auto_increment, `test_col` varchar(10) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.test 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.testselect * from mytest order by test_col; ++--+ | id | test_col | ++--+ | 1 | 1| | 10 | 10 | | 12 | 11 | | 2 | 2| | 11 | 20 | | 3 | 3| | 4 | 4| | 5 | 5| | 6 | 6| | 7 | 7| | 8 | 8| | 9 | 9| ++--+ 12 rows in set (0.01 sec) 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.testselect * from mytest order by CAST(test_col as unsigned); ++--+ | id | test_col | ++--+ | 1 | 1| | 2 | 2| | 3 | 3| | 4 | 4| | 5 | 5| | 6 | 6| | 7 | 7| | 8 | 8| | 9 | 9| | 10 | 10 | | 12 | 11 | | 11 | 20 | ++--+ 12 rows in set (0.00 sec) Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] Sent: Friday, 17 February 2006 11:29 AM To: mysql@lists.mysql.com Subject: Hi, newbie question on a select statement I searched the other lists and couldn't find one that was more appropiate for this question; if there is, please tell me so :) I am monitoring networking equipment and so far I'm saving all my data in a MySQL database (hence an email to this list). I'm using only VARCHARs because the SNMP agent returns only strings and I didn't find enough a reason for converting the different types of answers, since most string comparations are donde correctly; I received both text and numbers from the monitoring tasks and wanted to keep it simple. However when I execute this select statement I am getting this problem: select info_oficina,valorSNMP from ultimas_respuestas_snmp where columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10; info_oficina valorSNMP CSF Desamparados error Periferica Palmares 4 CSF San Pedro4 Sucursal Guapiles4 Periferica Pentagono San Pablo 30 Periferica Tibas 3 Periferica Buenos Aires 3 Sucursal Turrialba 3 Ventanilla Florencia 3 CSF del Sur (Ciudad Neilly) 3 Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, etc. The summary question is: given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL to order it as if they were integer values, so that instead of 99,98,97,96,95,94,93,92,91,90,9,89 I'd have 99,98,97,96,95,94,93,92,91,90,89,88 Apparently, CAST() does not have an effect (but feel free to prove me wrong) Thanks! Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installation Issue
It looks more like you haven't run the mysql_install_db script. From the manual : http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html If necessary, run the mysql_install_db program to set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server. You'll need to do this if you used a distribution type for which the installation procedure doesn't run the program for you. Typically, mysql_install_db needs to be run only the first time you install MySQL, so you can skip this step if you are upgrading an existing installation, However, mysql_install_db does not overwrite any existing privilege tables, so it should be safe to run in any circumstances. To initialize the grant tables, use one of the following commands, depending on whether mysql_install_db is located in the bin or scripts directory: shell bin/mysql_install_db --user=mysql shell scripts/mysql_install_db --user=mysql snip snip mysql_install_db creates several tables in the mysql database, including user, db, host, tables_priv, columns_priv, and func, as well as others. See Section 5.8, The MySQL Access Privilege System, for a complete listing and description of these tables. snip snip If you have trouble with mysql_install_db at this point, see Section 2.9.2.1, Problems Running mysql_install_db. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ravi Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 February 2006 7:27 AM To: Imran Chaudhry Cc: Peter M. Groen; mysql@lists.mysql.com Subject: Re: Installation Issue Imran, I noticed couple of permissions were not correct.I changed mysql.mysql. Still Ihave been getting following errors. 060214 15:53:05 mysqld started 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 060214 15:53:06 mysqld ended Please advice. thanks Imran Chaudhry [EMAIL PROTECTED] wrote: On 2/14/06, Ravi Kumar wrote: Starting mysql with root.I tried withn mysql user account also but still same error. thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert and Update together
Hi Andre, I don't believe 4.1 is able to do this. Version 5.0.18 (latest) has the ability to use triggers to do exactly what you describe. You may have to consider an upgrade to achieve this functionality or do something in php. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Andre Matos [mailto:[EMAIL PROTECTED] Sent: Sunday, 12 February 2006 2:23 PM To: George Law; mysql@lists.mysql.com Subject: Re: Insert and Update together No George. I took a look there before sent this email to the mysql list. My case it is not a duplicate record. What I want is that if the update in one table happen, it will be proceed by an insert in another table like a log of changes. Andre On 2/11/06 12:48 PM, George Law [EMAIL PROTECTED] wrote: Andre, I tried this a couple weeks ago... I think you want the on duplicate option for the INSERT query. depends what version you have... I think this was introduced in mysql 4.1 insert into values () on duplicate key update set x=2,y=5; unfortunately, the server I was testing this on was running 4.0.x so I couldn't use it. http://dev.mysql.com/doc/refman/5.0/en/insert.html - Original Message - From: Andre Matos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, February 11, 2006 10:54 AM Subject: Insert and Update together Hi List, I would like to know if it is possible to combine Insert and Update in one SQL instruction. This is what I want to do: I have two tables: one where I will perform and Update replacing m0 by scr. If MySQL find a m0, it will need to perform an insert into a log table including the information updated. I am trying to avoid writing a php4 program to do this. I am using MySQL 4.1 Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- 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: Surviving MySQL crash
That sounds an even better idea! Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Foo Ji-Haw [mailto:[EMAIL PROTECTED] Sent: Friday, 10 February 2006 6:57 PM To: Dan Nelson Cc: David Logan; Heikki Tuuri; mysql@lists.mysql.com; Logan, David (SST - Adelaide) Subject: Re: Surviving MySQL crash 15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES' This would run a FLUSH TABLES once every hour at 15 minutes past. If you are using Windows, sorry I'm not sure how to do it there. Easier to just set flush_time=900 in my.cnf :) Thanks guys. Will give it a shot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Failed to upgrade from 4.1.16 to 5.0.18
Hi suomi, From my understanding (and I have just performed it myself about 2 hours ago on one of my servers), I stopped the server, took a backup of the data directories, built the new server and put it in place (same as your rpm step), started the server, ran the fix script as /usr/local/mysql/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql --user=root --password=xx --verbose It ran ok. I stopped and started the server and away it went with no problems. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:47 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi Dave i did so, but probably in the wrong moment. i got Access denied when i run the script. you mean that this should have been the very first step after the upgrade. suomi Logan, David (SST - Adelaide) wrote: Hi, Did you run the bin/mysql_fix_privilege_tables script? This is documented in the upgrade procedures on the website under the installation chapter. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:04 PM To: mysql@lists.mysql.com Subject: Failed to upgrade from 4.1.16 to 5.0.18 Hi listers 1. i did the upgrad as i usually did so far: /etc/init.d/mysql stop rpm -U MySQL-server-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-client-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm from then on: no chance to enter the database via whatever interface: always Access denied for user on host ... 2. i checked to see, whether the mysql userid had sufficient access privileges to all database files: all access was ok. i also checked the error log myhost.mydomain.tld.err: no hint why access was denied. 3. i then went back to 4.1.16, entered the database (no problem) and for the root user created the long password and intentionally did a flush privileges. 4. then i re-did the above (under 1) upgrade steps again to the same event: Access denied. 5. i then took the procedure described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html while in the procedure, when i wanted to enter the flush privileges, again access denied. 6. i checked to see, whether there is some upgrade procedure to follow, where privilege tables are upgraded (as from 3 to 4), and i found none. i read the README even more carefully: nothing found. 7. back to 4.1.16 which essential step did i omitt? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Failed to upgrade from 4.1.16 to 5.0.18
Have you tried it without a password? eg. just --user root Are you able to connect with the mysql client? Sorry, I don't know a lot about rpm and how it works as I work on Solaris and use the .tar.gz versions. Hopefully somebody with familiarity with rpm can help out here? I'd be interested to know what is happening. I have noticed the message about setting root password, maybe it thinks this is a new install? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 10:16 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi again, it just does not work. follows the entire dialog: [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-server-standard-5.0.18-0.rhel3.i386.rpm Giving mysqld a couple of seconds to exit nicely PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h myhost.mydom.com password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting MySQL[ OK ] [EMAIL PROTECTED] /home/myuser/software netstat -ln |grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-client-standard-5.0.18-0.rhel3.i386.rpm [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm /sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty, not checked. /sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty, not checked. [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm [EMAIL PROTECTED] /home/myuser/software which mysql_fix_privilege_tables /usr/bin/mysql_fix_privilege_tables [EMAIL PROTECTED] /home/myuser/software mysql_fix_privilege_tables --user root --password=pp --verbose This script updates all the mysql privilege tables to be usable by MySQL 4.0 and above. This is needed if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION, stored procedures, or more secure passwords in 4.1 You can safely ignore all 'Duplicate column' and 'Unknown column' errors because these just mean that your tables are already up to date. This script is safe to run even if your tables are already up to date! ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Got a failure from command: cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --password=pp --database=mysql Please check the above output and try again. If you get an 'Access denied' error, you should run this script again and give the MySQL root user password as an argument with the --password= option [EMAIL PROTECTED] /home/myuser/software suomi Logan, David (SST - Adelaide) wrote: Hi suomi, From my understanding (and I have just performed it myself about 2 hours ago on one of my servers), I stopped the server, took a backup of the data directories, built the new server and put it in place (same as your rpm step), started the server, ran the fix script as /usr/local/mysql/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql --user=root --password=xx --verbose It ran ok. I stopped and started the server and away it went with no problems. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665
RE: Surviving MySQL crash
Hi, If they are MyISAM tables, you can run a FLUSH TABLES statement at a periodic interval. A good reference is here http://dev.mysql.com/doc/refman/5.0/en/flush.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Foo Ji-Haw [mailto:[EMAIL PROTECTED] Sent: Friday, 10 February 2006 3:45 PM To: mysql@lists.mysql.com Subject: Surviving MySQL crash Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- 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: Failed to upgrade from 4.1.16 to 5.0.18
Hi, Did you run the bin/mysql_fix_privilege_tables script? This is documented in the upgrade procedures on the website under the installation chapter. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:04 PM To: mysql@lists.mysql.com Subject: Failed to upgrade from 4.1.16 to 5.0.18 Hi listers 1. i did the upgrad as i usually did so far: /etc/init.d/mysql stop rpm -U MySQL-server-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-client-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm from then on: no chance to enter the database via whatever interface: always Access denied for user on host ... 2. i checked to see, whether the mysql userid had sufficient access privileges to all database files: all access was ok. i also checked the error log myhost.mydomain.tld.err: no hint why access was denied. 3. i then went back to 4.1.16, entered the database (no problem) and for the root user created the long password and intentionally did a flush privileges. 4. then i re-did the above (under 1) upgrade steps again to the same event: Access denied. 5. i then took the procedure described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html while in the procedure, when i wanted to enter the flush privileges, again access denied. 6. i checked to see, whether there is some upgrade procedure to follow, where privilege tables are upgraded (as from 3 to 4), and i found none. i read the README even more carefully: nothing found. 7. back to 4.1.16 which essential step did i omitt? suomi -- 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: How to login to MYSQL as root without knowing the passward
Hi Sol, I think you can change the startup properties from the services screen. If you right click on the MySQL service, you can set startup parameters at the bottom of the first screen. You could set the --skip-grant-tables there. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: sol beach [mailto:[EMAIL PROTECTED] Sent: Tuesday, 31 January 2006 12:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: How to login to MYSQL as root without knowing the passward Sounds reasonable, but how do I shutdown down MYSQL so I can restart with --skip-grant-tables option? On 1/30/06, Dan Trainor [EMAIL PROTECTED] wrote: sol beach wrote: I've been asked to assist the folks who own the data in the database, but folks who used to maintain it are no longer with the company. This installation resides on a Windows 2003 server to which I have local admin rights. What is the most painless way to get root access to this database? I am more than willing to RTFM, if anyone will point me at which FM to read. TIA Hi - I've always used the startup option of '--skip-grant-tables'. This may or may not be the correct way to do so, but we'll see what kind of feedback we get. I do this, then update the Password field in the mysql.user table. HTH -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dump only data and Database
Hi Luiz, If however you do not wish to create the indexes while inserting data, you can use the following option for the mysqldump --disable-keys, -K For each table, surround the INSERT statements with /*!4 ALTER TABLE tbl_name DISABLE KEYS */; and /*!4 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective for MyISAM tables only. All the indexes will be created at the end. This would speed up your restore considerably. This will only work on version 4.0 and above. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Luiz Rafael Culik Guimaraes [mailto:[EMAIL PROTECTED] Sent: Tuesday, 24 January 2006 11:40 AM To: MYSQL List; Paul DuBois Subject: Re: Dump only data and Database Paul Dear Friends What are the best options to dump an entire database on linux (with creation of databases and tables) with out dumping the index creation sentences What is an index creation sentence? Do you mean that you want the dump to include the CREATE TABLE statements, but for those statements not to contain the index definitions? If so, there's no option for that. yes, exactly this, and thanks for the answer Regards Luiz -- 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: error connecting to db.
Hi Doug, It is probably failing because you changed the ip address. The security system in MySQL is based around where a user is coming from. There is good documentation that is available here http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html I've referred to the current manual for ver. 5.0 as I'm not sure what you are using. You can easily refer to an earlier version of the manual via the http://dev.mysql.com/doc/ page. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, 17 January 2006 1:07 PM To: mysql@lists.mysql.com Subject: error connecting to db. I have a php application that connects with MySQL using: $SvrConn = @mysql_connect($MySQLServer, $MySQLUser, $MySQLPassword) where: $MySQLServer='127.0.0.1' $MySQLUser='dbuser' MySQLPassword='dbpasswd' and I can login using the above values. However connect fails: mysql select host,user from user; +---+-+ | host | user| +---+-+ | localhost | dbuser | +---+-+ The application worked until I changed the IP of the host. Where else might I look? There is no cnf file. thanks for any help. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- 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: error connecting to db.
Hi Doug, Can you connect using mysql -u $MySQLUser -p $MySQLPassword -h $MySQLServer? Without the -h option, the mysql client will connect via localhost and also via a unix socket rather than tcp/ip, by testing it as mentioned above, this will ensure it will actually connect to that host with that user/password combination. Please ensure that the $MySQLServer is exactly the same as in the php script. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, 17 January 2006 2:55 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: RE: error connecting to db. Thanks for the link I am glad the site has added manuals for multiple versions. I think I have access and db privileges set properly. I can (in effect) login to the server from the host command line using: mysql -u $MySQLUser -p $MySQLPassword I showed the user table on the off chance that 127.0.0.1 does not map to local host and someone would clue me in to that. I have tried setting the host for the user to the IP and to 127.0.0.1. Neither works. I think the problem probably lies with the mysql install but I do not know where to look. I am a PHP novice but AFAIK @mysql_connect should be like accessing mysql from the command line. On Tue, 17 Jan 2006, Logan, David (SST - Adelaide) wrote: Hi Doug, It is probably failing because you changed the ip address. The security system in MySQL is based around where a user is coming from. There is good documentation that is available here http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html I've referred to the current manual for ver. 5.0 as I'm not sure what you are using. You can easily refer to an earlier version of the manual via the http://dev.mysql.com/doc/ page. Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, 17 January 2006 1:07 PM To: mysql@lists.mysql.com Subject: error connecting to db. I have a php application that connects with MySQL using: $SvrConn = @mysql_connect($MySQLServer, $MySQLUser, $MySQLPassword) _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql database capacity
Hi Vishwas These and many other answers can be found here : http://dev.mysql.com/doc/refman/5.0/en/what-is.html There is no practical limitation on the row size apart from the number of fields x the size of these fields. eg. 1000 fields of varchar(255) or larger will take up that space. I don't know your table description so can't say. I have referenced the version 5.0 manual as I'm not sure of your version. How long to search a record in 1 billion rows? That is very much dependent on how you are searching, which index you are using etc. It could take as little as .01 of a second if you use the primary key to access the record directly. For the number of records, it is dependent on the size of the record. Unfortunately there is very little information in your email to be able to make a judgement. I would suggest you see the limitations in the url above and divide your record size into that. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, 9 January 2006 8:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication
Hi Jason, Most other peoples responses are excellent as usual, however might I suggest getting a copy of High Performance MySQL by Jeremy Zawodny (O'Reilly publishers). This covers the exact scenario you are talking about. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Saturday, 7 January 2006 9:32 AM To: mysql@lists.mysql.com Subject: MySQL Replication I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Thank You, Jason Williard -- 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: remotely show databases
Hi Anthony, If you are accessing the db from perl, just execute a show databases from there using the DBI interface. There is quite an amount of doco about this and other features at cpan.org. You could also look at the source for phpMyadmin as this has to get the database names to display them. The advantage of the DBI interface is that with a tiny bit of work, it will probably work with Postgresql as well. You could also give SELECT permission to a specific user on all databases that is not passworded and this should allow you to list the databases without having to send a password. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Tuesday, 10 January 2006 7:40 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: remotely show databases 'SHOW DATABASES;' | mysql -u foo -h bar.com this works, I haven't tested it with other databases though. On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote: Hi Anthony, I am not sure if you have an installation of MySQL on your local server. If you do then you can try to use something like below to execute your SHOW DATABASES Local_server[path to your mysql/bin directory]/mysql --host=your_remote_host --user=your_user --password=your_password -e SHOW DATABASE Make sure that [EMAIL PROTECTED] has appropriate rights on your_remote_host (server) Best, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Sunday, January 08, 2006 4:03 PM To: mysql@lists.mysql.com Subject: remotely show databases I know I can login via ssh and run $mysqlshow But I would then have to parse the outputted text, is there an easier way (I'm using Perl locally here). The pitfall of running it locally is that you DO have to password protect your database user since it's an outside connection to run SHOW DATABASES; I tried $man mysqlshow, but didn't see any easy way of simply returning a \n seperated list of databases. Any suggestions? I also need to do this for postgresql if anyone else knows of a standalone app that dumps the databases for a specific user. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: remotely show databases
True, however you could run the perl script locally via another
mechanism and allow it to return the database info in the format that
you desire, or run it using the mysql -e 'show databases' -u
local_user_that_doesn't_need_a_password
The other option is to use an ssh tunnel to the server in question. I
use that quite a bit. Redirect a port say, 1, to 3306 locally. That
would be ok if you can ssh to it, it will also secure the communications
and allow you to extract the information that you require.
You only then need to open the ssh port on the remote machine. From my
reading of you emails, that seems to be ok to do.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 9:33 AM
To: Logan, David (SST - Adelaide)
Cc: Mikhail Berman; mysql@lists.mysql.com
Subject: Re: remotely show databases
On 1/9/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
Hi Anthony,
If you are accessing the db from perl, just execute a show databases
from there using the DBI interface. There is quite an amount of doco
about this and other features at cpan.org. You could also look at the
source for phpMyadmin as this has to get the database names to display
them.
The advantage of the DBI interface is that with a tiny bit of work, it
will probably work with Postgresql as well.
You could also give SELECT permission to a specific user on all
databases that is not passworded and this should allow you to list the
databases without having to send a password.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 7:40 AM
To: Mikhail Berman
Cc: mysql@lists.mysql.com
Subject: Re: remotely show databases
'SHOW DATABASES;' | mysql -u foo -h bar.com
this works, I haven't tested it with other databases though.
On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote:
Hi Anthony,
I am not sure if you have an installation of MySQL on your local
server.
If you do then you can try to use something like below to execute
your
SHOW DATABASES
Local_server[path to your mysql/bin directory]/mysql
--host=your_remote_host --user=your_user --password=your_password -e
SHOW DATABASE
Make sure that [EMAIL PROTECTED] has appropriate rights on
your_remote_host (server)
Best,
Mikhail Berman
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Sunday, January 08, 2006 4:03 PM
To: mysql@lists.mysql.com
Subject: remotely show databases
I know I can login via ssh and run $mysqlshow
But I would then have to parse the outputted text, is there an
easier
way (I'm using Perl locally here).
The pitfall of running it locally is that you DO have to password
protect your database user since it's an outside connection to run
SHOW
DATABASES;
I tried $man mysqlshow, but didn't see any easy way of simply
returning
a \n seperated list of databases.
Any suggestions?
I also need to do this for postgresql if anyone else knows of a
standalone app that dumps the databases for a specific user.
Actually, for security reasons, the command has to be executed on the
remote
ssh server that has network access to the mysql server. Going across the
Internet with DBI; may not be feasible unless you open up the access
host.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing mysqld.sock
Hi Jon, The socket is created on demand. The error message said the server failed to start, not that your socket has disappeared ipso facto the socket will not have been created. That is why you can't connect, your server is not running. You need to check the system and mysql logs as mentioned in the message and relay what the error messages are in there. That will give you a good idea as to the possible cause of it failing to start. We need to identify why the server didn't start before going to such drastic lengths as re-installing. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED] Sent: Saturday, 7 January 2006 7:58 PM To: Logan, David (SST - Adelaide) Subject: RE: missing mysqld.sock Tried everything on that page and nothing help. As I've stated the mysqld.sock file is no longer on the system. This was working prior to me trying to remove the older version of mysql. Should I just bite the bullet and do a apt-get purge mysql-server and do a reinstall? Jon Logan, David (SST - Adelaide) [EMAIL PROTECTED] 3:45:59 pm 7/01/2006 Hi Jon, This reference will give you all the info you need. http://dev.mysql.com/doc/refman/4.1/en/can-not-connect-to-server.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED] Sent: Saturday, 7 January 2006 5:59 PM To: mysql@lists.mysql.com Subject: missing mysqld.sock I've installed mysql 4 and noticed that both mysql 4.0 and 4.1 was on the system. In my attempt to remove the older version I may have done away with my mysqld.sock. When I try to access mysql i'm getting the following message: debOS:~# /etc/init.d/mysql start Starting MySQL database server: mysqld...failed. Please take a look at the syslog. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing mysqld.sock
Hi Jon, This reference will give you all the info you need. http://dev.mysql.com/doc/refman/4.1/en/can-not-connect-to-server.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED] Sent: Saturday, 7 January 2006 5:59 PM To: mysql@lists.mysql.com Subject: missing mysqld.sock I've installed mysql 4 and noticed that both mysql 4.0 and 4.1 was on the system. In my attempt to remove the older version I may have done away with my mysqld.sock. When I try to access mysql i'm getting the following message: debOS:~# /etc/init.d/mysql start Starting MySQL database server: mysqld...failed. Please take a look at the syslog. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump: Got errno 32 on write ?
Hi, Are you running mysqldump through into a pipe eg: into tar or similar? I mention this because : test1=perror 32 System error: 32 = Broken pipe Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Friday, 6 January 2006 2:17 PM To: mysql@lists.mysql.com Subject: mysqldump: Got errno 32 on write ? mysqldump: Got errno 32 on write -- any ideas? I think it may be mysqldump is not run as user, because dump dir is 0700?? -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: an backup syntax error
Hi, The error message (errno: 13) indicates a permissions problem. Check the permissions of the user that is running the backups or, alternatively, check the permissions that are set for this file. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: wangxu [mailto:[EMAIL PROTECTED] Sent: Thursday, 5 January 2006 1:29 PM To: mysql@lists.mysql.com Subject: an backup syntax error Follow is my preform and result: - mysql backup table ht_detail to '/'; +++--+-- + | Table | Op | Msg_type | Msg_text | +++--+-- + | test.ht_detail | backup | error| Failed copying .frm file (errno: 13) | | test.ht_detail | backup | status | Operation failed | +++--+-- + 2 rows in set, 1 warning (0.00 sec) - How to solve it? I use 5.0.16. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: logging issue
Morning Jon, You will not be too successful in using MySQL logging to trace this. You need to be able to connect to the server at the very least before this would help. I don't know much about MailScanner but I would be checking a couple of things, 1) do you have a firewall in place? is the port (generally 3306) open? and 2) is there a configuration file for MailScanner that has been set up correctly? This sounds more like a network issue rather than a MySQL problem. Are you able to ping 192.168.10.4 from the MailScanner box? Can you telnet to it? You could use tcpdump or similar to check the network conversations. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED] Sent: Tuesday, 3 January 2006 9:03 PM To: mysql@lists.mysql.com Subject: logging issue System: Red hat 7.2 My SQL modules: MySQL-devel-4.0.13-0 php-mysql-4.1.2-7.2.6 MySQL-shared-3.23.55-1 MySQL-server-4.0.13-0 MySQL-client-4.0.13-0 CAMysql-9.0-220 Msql-Mysql-DBI-perl-bin-1.1823-1 I would like to log or turn on the facility to do a verbose logging to troubleshoot an issue I'm having with a program on another (mail) server trying to access the mysql server. From the mail server I can issue the following: mysql -h 192.168.10.4 -u mailwatch -p and enter the password and it connects. Yet from within the initial program it does not work and in the mail logs it has: Jan 3 18:19:31 mail MailScanner[11376]: Unable to initialise database connection: Can't connect to MySQL server on '192.168.10.4' (110) I've ask the mailscanner list, the mailwatch list and the postfix list and we are not getting anywhere. Thanks -- 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: File Permissions On Database Files
Hi Hal, You might like to try setting the umask for the mysql user. I would think the server would abide by the OS rules for permission settings. You may be able to set the UMASK_DIR environment variable (as described here http://dev.mysql.com/doc/refman/5.0/en/environment-variables.html). Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Hal Vaughan [mailto:[EMAIL PROTECTED] Sent: Tuesday, 3 January 2006 7:54 AM To: mysql@lists.mysql.com Subject: File Permissions On Database Files My MySQL databases are stored in a directory that is with the rest of my project. I am using rsync to back up the entire project, including the database files. I'd much rather do it this way than to create files with mysqldump. Whenever MySQL creates a new database, the file is owned by the user mysql, and has limited read and write permissions (this is on Linux, btw). It would be a HUGE help if I could make sure every time MySQL creates any files, it creates them with at least group if not all read access for backup purposes. Is there a simple way to do this? Thanks! Hal -- 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: Re: How to share databases on dual-boot machines?
Hi James,
Check the whole /mnt filesystem hasn't been mounted read-only.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of James Brown
Sent: Friday, 30 December 2005 2:30 AM
To: mysql@lists.mysql.com
Subject: Re: How to share databases on dual-boot machines?
Thanks for the advice David. I opted for your method 2 and symlinked to
my database files which now reside on a FAT32 partition.
Unfortunately, I've hit a slight issue which I'd like to run past you,
but I do think this could turn out to be a FAT32 problem rather than a
MySQL one.
Basically, the following error appears when I try to run an UPDATE on
the relocated database:
WordPress database error: [Table 'wp_options' is read only]
I've tried 'chmod 777 *' on all the wordpress database files, but
still couldn't get universal write permissions. 'chown mysql *' also
failed with an error.
An ls -al on the database files now shows this:
# 'jamebrow' is my username
# mysql is running as user 'mysql'
-rwxr-xr-x 1 jamebrow 9063 2006-01-06 13:03 wp_options.frm*
-rwxr-xr-x 1 jamebrow 24 2006-01-07 02:22 wp_options.MYD*
-rwxr-xr-x 1 jamebrow 8192 2005-12-28 15:28 wp_options.MYI*
The symlink looks like this:
lrwxrwxrwx 1 mysql 25 2005-12-29 13:40 wordpress -
/mnt/docs/mysql/wordpress/
Does anyone have any ideas?
Many thanks,
James.
Logan, David (SST - Adelaide) wrote:
Hi James,
There are a couple of options (from the Linux side of things) :
1) set the datadir either in the start options of safe_mysqld (or
mysqld_safe I can never remember) or set it in the options file
(my.cnf
or a .my.cnf in your home directory) see
http://dev.mysql.com/doc/refman/5.0/en/option-files.html
2) move it to /mnt/wordpress and use a symbolic link from
/var/lib/mysql
eg. mv /var/lib/mysql/wordpress /mnt ; ln -s /mnt/wordpress
/var/lib/mysql/wordpress see
http://dev.mysql.com/doc/refman/4.1/en/symbolic-links.html
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of James Brown
Sent: Thursday, 29 December 2005 12:54 PM
To: mysql@lists.mysql.com
Subject: How to share databases on dual-boot machines?
All,
I'm new to MySQL (using v4.1) and have a dual boot machine with
Windows
and Debian Linux (Etch).
My goal is to share a database named wordpress between Linux and
Windows by storing it on a FAT32 mount (I realise this limits me to
4GB
in total).
From what I can see at the moment, the wordpress tables live under
/var/lib/mysql/wordpress.
Numerous searches have revealed how to backup data, but I haven't seen
any clues on how to permanently reloate a database to a different
filepath (I'd like to move it to /mnt/wordpress). Could anyone
enlighten
me please?
Thanks in advance,
James.
--
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: How to share databases on dual-boot machines?
Hi James, There are a couple of options (from the Linux side of things) : 1) set the datadir either in the start options of safe_mysqld (or mysqld_safe I can never remember) or set it in the options file (my.cnf or a .my.cnf in your home directory) see http://dev.mysql.com/doc/refman/5.0/en/option-files.html 2) move it to /mnt/wordpress and use a symbolic link from /var/lib/mysql eg. mv /var/lib/mysql/wordpress /mnt ; ln -s /mnt/wordpress /var/lib/mysql/wordpress see http://dev.mysql.com/doc/refman/4.1/en/symbolic-links.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of James Brown Sent: Thursday, 29 December 2005 12:54 PM To: mysql@lists.mysql.com Subject: How to share databases on dual-boot machines? All, I'm new to MySQL (using v4.1) and have a dual boot machine with Windows and Debian Linux (Etch). My goal is to share a database named wordpress between Linux and Windows by storing it on a FAT32 mount (I realise this limits me to 4GB in total). From what I can see at the moment, the wordpress tables live under /var/lib/mysql/wordpress. Numerous searches have revealed how to backup data, but I haven't seen any clues on how to permanently reloate a database to a different filepath (I'd like to move it to /mnt/wordpress). Could anyone enlighten me please? Thanks in advance, James. -- 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: Are primary keys essential?
Hi James, Not AFAIK, one can create tables without specifying a PK and there is no objection. Data is stored quite happily and you should be able to use your FK's to access other data. It must be voluntary because the ALTER TABLE DML statement has DROP PRIMARY KEY drops the primary index. Note: In older versions of MySQL, if no primary index existed, then DROP PRIMARY KEY would drop the first UNIQUE index in the table. This is not the case in MySQL 5.0, where trying to use DROP PRIMARY KEY on a table with no primary key will give rise to an error. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 12:45 PM To: mysql@lists.mysql.com Subject: Re: Are primary keys essential? In hindsight my thread title was misleading - sorry. Should have been are primary keys _always_ essential?. JH -- 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: Need Help Connecting
Hi Mark, Have you checked to see if you any aliases set? It might be using that instead of the mysql command. May well be worth checking your path to ensure you aren't picking up a script called mysql or something similar. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:02 PM To: MYSQL List Subject: Need Help Connecting I have the following setup - mysql 4.0.24 running on Debian Linux stable. I set up a user 'mark' with a password. When I log into my Linux box as user 'mark', I cannot connect to mysql - I get this funny error message: [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p mysql: unknown option '--user mark' [EMAIL PROTECTED]:~$ If I su to another user, I can login as mark to mysql [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 128 to server version: 4.0.24_Debian-10sarge1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I don't get it - why can't I use the '-u mark' option with mysql when I am logged into my own Linux user account? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- 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: Need Help Connecting
Try typing at the command prompt $ aliases $ which mysql $ echo $PATH The first one will list any aliases that have been setup. The second will tell you the directory the system thinks it is getting mysql from and the third will list your PATH environment variable. If you see an alias named mysql, that may well be your culprit. If you installed from an rpm (I don't know much about the debian apt-get thingy) you should find the mysql command in /usr/bin Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:21 PM To: mysql@lists.mysql.com Cc: Logan, David (SST - Adelaide) Subject: Re: Need Help Connecting David, How do I do that? Thanks! Mark On Wednesday 21 December 2005 11:37 pm, Logan, David (SST - Adelaide) wrote: Hi Mark, Have you checked to see if you any aliases set? It might be using that instead of the mysql command. May well be worth checking your path to ensure you aren't picking up a script called mysql or something similar. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:02 PM To: MYSQL List Subject: Need Help Connecting I have the following setup - mysql 4.0.24 running on Debian Linux stable. I set up a user 'mark' with a password. When I log into my Linux box as user 'mark', I cannot connect to mysql - I get this funny error message: [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p mysql: unknown option '--user mark' [EMAIL PROTECTED]:~$ If I su to another user, I can login as mark to mysql [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 128 to server version: 4.0.24_Debian-10sarge1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I don't get it - why can't I use the '-u mark' option with mysql when I am logged into my own Linux user account? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help Connecting
What happens when you type $ /usr/bin/mysql -h localhost -u mark -p Does this work? I'm off home now so won't be able to check your reply for about 2 hours. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:36 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Need Help Connecting David, This is what I got: [EMAIL PROTECTED]:~$ aliases bash: aliases: command not found [EMAIL PROTECTED]:~$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:~$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:~$ I don't have a command 'aliases', but the other tests seem to say all I have is mysql running as mysql. When I am logged in as 'emily' I get: [EMAIL PROTECTED]:/home/mark$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games [EMAIL PROTECTED]:/home/mark$ Any other thoughts? The error message from mysql when I try to log in is strange. Why all the spaces? mysql: unknown option '--user mark' Thanks! Mark On Wednesday 21 December 2005 11:55 pm, Logan, David (SST - Adelaide) wrote: Try typing at the command prompt $ aliases $ which mysql $ echo $PATH The first one will list any aliases that have been setup. The second will tell you the directory the system thinks it is getting mysql from and the third will list your PATH environment variable. If you see an alias named mysql, that may well be your culprit. If you installed from an rpm (I don't know much about the debian apt-get thingy) you should find the mysql command in /usr/bin Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:21 PM To: mysql@lists.mysql.com Cc: Logan, David (SST - Adelaide) Subject: Re: Need Help Connecting David, How do I do that? Thanks! Mark On Wednesday 21 December 2005 11:37 pm, Logan, David (SST - Adelaide) wrote: Hi Mark, Have you checked to see if you any aliases set? It might be using that instead of the mysql command. May well be worth checking your path to ensure you aren't picking up a script called mysql or something similar. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:02 PM To: MYSQL List Subject: Need Help Connecting I have the following setup - mysql 4.0.24 running on Debian Linux stable. I set up a user 'mark' with a password. When I log into my Linux box as user 'mark', I cannot connect to mysql - I get this funny error message: [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p mysql: unknown option '--user mark' [EMAIL PROTECTED]:~$ If I su to another user, I can login as mark to mysql [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 128 to server version: 4.0.24_Debian-10sarge1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I don't get it - why can't I use the '-u mark' option with mysql when I am logged into my own Linux user account? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow sorting
Hi Marcus, Do you have the output of the EXPLAIN? I've found the number of rows processed to be very instructional. If you could get this number down, it may be well worth putting an index on the account column and re-running the EXPLAIN to see how many rows are being processed. I recently put an index on a table that was doing a filesort around 50 times for each web page. It was processing 58800 records each sort x 50 made for a very slow web page. I put an index in place, it still does the filesort but is only processing 154 rows each time and this is probably being done in memory as it is so tiny. Would be worth a punt. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Marcus Bointon [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 December 2005 9:55 PM To: mysql@lists.mysql.com Subject: Slow sorting I have a table that contains around 400,000 simple names. It's displaying a subset of them (perhaps 5,000) them a page at a time in a web interface, sorted by name, so I have a query like this: SELECT * FROM names WHERE account=123 ORDER BY lastname, firstname LIMIT 0,30 That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? A DBA friend recommended using clustered indexes - does MySQL have such things? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- 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: How to Square a number?
Why not then use user variables? eg. select @a:=((col1+col2+col3)/col4), @b:=((col5+col6+col7)/col8), pow(@a,2), pow(@b,2) ... table1 Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 December 2005 7:40 AM To: Elmar von Muralt Cc: mysql@lists.mysql.com Subject: Re: How to Square a number? At 02:32 PM 12/19/2005, Elmar von Muralt wrote: Mike, How about: temp1 = [Expression] temp2 = temp1 * temp1 This should be quick enough, assuming tempN are cast as integers Elmar Nope, not quite.g The expression is an expression based on columns in the table, as in: select ((col1+col2+col3)/col4), ((col5+col6+col7)/col8) ... table1 So it looks like I'll have to use Power(num,exp) after all to square these expressions. Mike mos wrote: At 08:30 AM 12/19/2005, Peter Brawley wrote: Yes, it's a bit more complicated than that. I'm using an expression so I don't want to have to repeat the whole expression all over again. What's wrong with CAST( POWER( expr, 2 ) AS SIGNED )? PB -- PB, Yes it looks like I'll have to use Power() after all. From a programming standpoint, I thought using a floating point function like Power on an integer was inefficient and makes it harder to read the exoression. But I'll give it a try. Thanks. I'm still not sure why the ** operator is not supported because it would reduce the amount of code considerably (I have to square quite a few numbers). Oh well. :( Mike mos wrote: At 05:43 PM 12/18/2005, James Harvard wrote: Maybe I'm missing something, but can't you just multiply the number by itself? select (3 * 3); select (int_col * int_col) as squared from table_name; James H James, Yes, it's a bit more complicated than that. I'm using an expression so I don't want to have to repeat the whole expression all over again. Mike At 5:36 pm -0600 18/12/05, mos wrote: How do I square a number in MySQL 4.1? I thought it would be something simple like: select 3**2 but that produces a syntax error. I can use Pow(3,2) but that produces a float. Is there a Square function? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.1/206 - Release Date: 12/16/2005 -- 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: How to Square a number?
Hi Mike, If you only want the squares displayed, you could then do select pow(((col1+col2+col3)/col4),2), pow(((col5+col6+col7)/col8),2) ... table1 Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 December 2005 9:34 AM To: Logan, David (SST - Adelaide); Elmar von Muralt Cc: mysql@lists.mysql.com Subject: RE: How to Square a number? At 03:30 PM 12/19/2005, Logan, David (SST - Adelaide) wrote: Why not then use user variables? eg. select @a:=((col1+col2+col3)/col4), @b:=((col5+col6+col7)/col8), pow(@a,2), pow(@b,2) ... table1 Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia David, That looks interesting. The only drawback of course is it creates more temporary variable columns for @a,@b etc. that gets displayed. Of course it could be simplified even more with a simple @[EMAIL PROTECTED] etc.. Thanks for the suggestion. I'll put it to work. :-) Mike +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 December 2005 7:40 AM To: Elmar von Muralt Cc: mysql@lists.mysql.com Subject: Re: How to Square a number? At 02:32 PM 12/19/2005, Elmar von Muralt wrote: Mike, How about: temp1 = [Expression] temp2 = temp1 * temp1 This should be quick enough, assuming tempN are cast as integers Elmar Nope, not quite.g The expression is an expression based on columns in the table, as in: select ((col1+col2+col3)/col4), ((col5+col6+col7)/col8) ... table1 So it looks like I'll have to use Power(num,exp) after all to square these expressions. Mike mos wrote: At 08:30 AM 12/19/2005, Peter Brawley wrote: Yes, it's a bit more complicated than that. I'm using an expression so I don't want to have to repeat the whole expression all over again. What's wrong with CAST( POWER( expr, 2 ) AS SIGNED )? PB -- PB, Yes it looks like I'll have to use Power() after all. From a programming standpoint, I thought using a floating point function like Power on an integer was inefficient and makes it harder to read the exoression. But I'll give it a try. Thanks. I'm still not sure why the ** operator is not supported because it would reduce the amount of code considerably (I have to square quite a few numbers). Oh well. :( Mike mos wrote: At 05:43 PM 12/18/2005, James Harvard wrote: Maybe I'm missing something, but can't you just multiply the number by itself? select (3 * 3); select (int_col * int_col) as squared from table_name; James H James, Yes, it's a bit more complicated than that. I'm using an expression so I don't want to have to repeat the whole expression all over again. Mike At 5:36 pm -0600 18/12/05, mos wrote: How do I square a number in MySQL 4.1? I thought it would be something simple like: select 3**2 but that produces a syntax error. I can use Pow(3,2) but that produces a float. Is there a Square function? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.1/206 - Release Date: 12/16/2005 -- 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: [OT] SPAM
Hi Stephen, Unfortunately this is not a moderated list. Basically anybody can join, send an email and then vanish. Nothing much to be done about it. I would however note, this is only about the third time in a couple of years listening that I've seen anything. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Stephen Cook [mailto:[EMAIL PROTECTED] Sent: Friday, 16 December 2005 11:05 AM To: Daniel Kasak Cc: mysql@lists.mysql.com Subject: Re: [OT] SPAM I got one from mysql@lists.mysql.com, and one from [EMAIL PROTECTED] And my filters remove anything that isn't from one of the mailing lists I subscribe to. Daniel Kasak wrote: Stephen Cook wrote: What's the deal with SPAM on the list? I don't see any. Maybe my spam filter is better than yours? What exactly are you asking anyway? -- 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: LOAD DATA INFILE Syntax
Hi Elliot,
It is in the docs, just a little bit further down the page 8-)
LOCAL works only if your server and your client both have been enabled
to allow it. For example, if mysqld was started with --local-infile=0,
then LOCAL does not work. See Section 5.6.4, Security Issues with LOAD
DATA LOCAL.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Elliot Kleiman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 13 December 2005 8:12 AM
To: mysql@lists.mysql.com
Subject: LOAD DATA INFILE Syntax
Hi mysql-list,
I just installed,
++
| version() |
++
| 5.0.16-log |
++
Here is what I am testing out: (simple table and data)
% echo 'a b c d' testfile
mysql CREATE TABLE `test` (
- `fe` VARCHAR( 2 ),
- `fi` VARCHAR( 2 ),
- `fo` VARCHAR( 2 ),
- `fum` VARCHAR( 2 )
- );
Query OK, 0 rows affected (0.00 sec)
mysql load data local infile './test' into table test;
ERROR 1148 (42000): The used command is not allowed with this MySQL
version
Q: What am I doing wrong(missing)?
{ I did not see it in the documentation online }
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Thanks, Elliot
P.S.
The same command works fine in previous versions of MySQL.
__
WizardsWorks Cluster
http://www.wizardsworks.org/
--
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]
