Re: Updating from 4 to 5
Yeah, I'm agree with michael. For migration of DB from one major version to another, must use the below approach. by the using mysqlupgrade, might be we can loss some data. regards, Nilnandan Michael Dykman wrote: There are binary differences between v4.x and 5.x (5.1.x recommended).. The cleanest approach would be to do mysqldump on the old database and inject that into your new server. - michael dykman On Tue, Jun 29, 2010 at 4:04 PM, Grant Peel gp...@thenetnow.com wrote: nilnandan, I meant to say Mysqlupgrade :-) Further, I can use the old mysql tables, (the mysql gant tables, user/passwd/host tables etc) and the mysqlupgrade will update them as necessary? -Grant - Original Message - From: Nilnandan Joshi To: Grant Peel Cc: mysql@lists.mysql.com Sent: Tuesday, June 29, 2010 4:00 AM Subject: Re: Updating from 4 to 5 Grant, I think, you should run mysqlupgrade after copying old data in new servers. Regards, nilnandan Grant Peel wrote: Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
Re: Mysql error causing database to not accept threads
Hi!
Machiel Richards wrote:
[[...]]
We received an error on a MySQL database this morning which
caused it to be unavailable for connections.
Error:
ERROR 1135 (0): Can't create a new thread (errno 11); if
you are not out of available memory, you can consult the manual for a
possible OS-dependent bug
[[...]]
You don't say anything about the MySQL version you are running, or your
platform.
I hope this here will help you, though:
| u...@h:~ fgrep 11 /usr/include/asm-generic/errno-base.h
| #define EAGAIN 11 /* Try again */
| u...@h:~ man pthread_create
| PTHREAD_CREATE(3P) POSIX Programmer's Manual
PTHREAD_CREATE(3P)
|
|
|
| NAME
|pthread_create - thread creation
|
| [[...]]
|
| ERRORS
|The pthread_create() function shall fail if:
|
|EAGAIN The system lacked the necessary resources to create
another thread, or the system-imposed
| limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|
| [[...]]
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Mysql error causing database to not accept threads
Hi Joerg
Thank you very much for the response.
I found an answer this morning (about 15 minutes ago) where the
server's Thread cache was used up and the amount of max connections exceeded
causing the same error.
We have not yet been able to establish the source of the amount of
connections though.
Machiel Richards
MySQL DBA
Relational Database Consulting
-Original Message-
From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com]
Sent: 30 June 2010 10:53 AM
To: mysql@lists.mysql.com
Cc: Machiel Richards
Subject: Re: Mysql error causing database to not accept threads
Hi!
Machiel Richards wrote:
[[...]]
We received an error on a MySQL database this morning
which
caused it to be unavailable for connections.
Error:
ERROR 1135 (0): Can't create a new thread (errno 11);
if
you are not out of available memory, you can consult the manual for a
possible OS-dependent bug
[[...]]
You don't say anything about the MySQL version you are running, or your
platform.
I hope this here will help you, though:
| u...@h:~ fgrep 11 /usr/include/asm-generic/errno-base.h
| #define EAGAIN 11 /* Try again */
| u...@h:~ man pthread_create
| PTHREAD_CREATE(3P) POSIX Programmer's Manual
PTHREAD_CREATE(3P)
|
|
|
| NAME
|pthread_create - thread creation
|
| [[...]]
|
| ERRORS
|The pthread_create() function shall fail if:
|
|EAGAIN The system lacked the necessary resources to create
another thread, or the system-imposed
| limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|
| [[...]]
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Determine connection origins
Good day all We are trying to find out where the current connections on our Mysql database originates from. We are receiving 4000+ connections at the moment where this was usually only about half this. Is there a way of determining where the connections originates from ? i.e - website, internal, import scripts, etc... Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo
Re: MySQL Replication
Hi Just one other question. With regards the replication in MySQL 5.1 - does it it replication the whole row of data or just the field in which the data has been changed for the current record ? Thanks Neil 2010/6/24 Jaime Crespo Rincón jcre...@warp.es 2010/6/24 Tompkins Neil neil.tompk...@googlemail.com: Hi Regarding two-way replication what do you mean by very controlled environment ? What things do I need to consider ? Control at application level that you are not going to insert/update/delete the same record on the two servers. Even if MySQL gives some support to handle this (auto-increment-offset, replicate-ignore-table), you should mostly handle it at business logic (application server) layer, not in the MySQL database. Alternatively, as Johan pointed, have a look at the semi-synchronous replication. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es
Re: MySQL Replication
2010/6/30 Tompkins Neil neil.tompk...@googlemail.com: Hi Just one other question. With regards the replication in MySQL 5.1 - does it it replication the whole row of data or just the field in which the data has been changed for the current record ? MySQL 5.1 supports two replication formats: row and statement-based. Please, have a look at the manual page: http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Reserving threads for root user
Good day all Sorry one more question. I have seen many questions about this on the web but no resolution yet. When MySQL runs out of threads, you are unable to stop / restart the database. Is there a way to reserve threads for the root user / database restarts? Current version running: 5.0.72sp1-enterprise-gpl-log Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo
Re: Determine connection origins
Good day all We are trying to find out where the current connections on our Mysql database originates from. We are receiving 4000+ connections at the moment where this was usually only about half this. Is there a way of determining where the connections originates from ? i.e - website, internal, import scripts, etc... show processlist; but I dont think you can determine if it is from webserver etc - dont think it matters to mysql really. it will provide user/host/query/ID ... Also I am sure you can find some third party tools doing the same. -- bEsT rEgArDs| Confidence is what you have before you tomasz dereszynski | understand the problem. -- Woody Allen | Spes confisa Deo| In theory, theory and practice are much numquam confusa recedit | the same. In practice they are very | different. -- Albert Einstein -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reserving threads for root user
On 6/30/2010 6:56 AM, Machiel Richards wrote: Good day all Sorry one more question. I have seen many questions about this on the web but no resolution yet. When MySQL runs out of threads, you are unable to stop / restart the database. Is there a way to reserve threads for the root user / database restarts? Yes. Don't give normal users the SUPER privilege. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_super Your applications and non-administrative users should be using accounts with the least privileges necessary to to their jobs. That way the extra connection allocated to the SUPER user accounts will not be consumed with non-administrative activities. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
INSERT with auto increment
Hi All,
In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...
For instance, with a two column table I could do INSERT INTO TABLE1
VALUES('stuff')
I'm having trouble doing the same thing in mySQL...
In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.
The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?
Need some guidance
Thanks!
Dave
Re: INSERT with auto increment
generally, it is:
INSERT INTO TABLE1 (fieldname [ , fieldname]* ) VALUES (value[, value]*)
If you don't list the columns, it assumes you are inserting all of them, so:
INSERT INTO TABLE1 (mycolumn ) VALUES ('stuff')
This will also work
INSERT INTO TABLE1 VALUES (0, 'stuff')
the auto-increment will engage on an insert of 0
- michael dykman
On Wed, Jun 30, 2010 at 1:30 PM, David Stoltz dsto...@shh.org wrote:
Hi All,
In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...
For instance, with a two column table I could do INSERT INTO TABLE1
VALUES('stuff')
I'm having trouble doing the same thing in mySQL...
In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.
The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?
Need some guidance
Thanks!
Dave
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT with auto increment
You can choose between:
INSERT INTO TABLE1 VALUES (null,'stuff')
or
INSERT INTO TABLE1 (stuffField) VALUES ('stuff')
--
João Cândido de Souza Neto
David Stoltz dsto...@shh.org escreveu na mensagem
news:487e7d0857fe094590bf2dc33fe3e1080a102...@shhs-mail.shh.org...
Hi All,
In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...
For instance, with a two column table I could do INSERT INTO TABLE1
VALUES('stuff')
I'm having trouble doing the same thing in mySQL...
In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.
The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?
Need some guidance
Thanks!
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT with auto increment
From: David Stoltz dsto...@shh.org In mySQL, if I expressly give it a value, like INSERT INTO TABLE1 VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I don't have a matching number of columns. Use NULL for the autoinsert column. I made it a rule to forbear all direct contradictions to the sentiments of others, and all positive assertion of my own. I even forbade myself the use of every word or expression in the language that imported a fixed opinion, such as certainly, undoubtedly, etc. I adopted instead of them I conceive, I apprehend, or I imagine a thing to be so or so; or so it appears to me at present. When another asserted something that I thought an error, I denied myself the pleasure of contradicting him abruptly, and of showing him immediately some absurdity in his proposition. In answering I began by observing that in certain cases or circumstances his opinion would be right, but in the present case there appeared or seemed to me some difference, etc. I soon found the advantage of this change in my manner; the conversations I engaged in went on more pleasantly. The modest way in which I proposed my opinions procured them a readier reception and less contradiction. I had less mortification when I was found to be in the wrong, and I more easily prevailed with others to give up their mistakes and join with me when I happened to be in the right. -- Benjamin Franklin Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
