Re: Innodb log sequence error - urgent
Thanks, I have the previous ib_log* files on the app server. And every thing on the cnf file was perfect. Only the ib_log file's size was a mismatch. Whats the best work around? Can I copy the log files of the App server to the DB server and change the innodb_log_file_size to 256M and then restart the MySQL server. If I do so will I lose the updates to the database that happened today? So my actual problem is this: I have two sets of ib_logfile* files. To be particular there are a.. ib_logfile0, ib_logfile1, ib_logfile2 on the App server-each 257M (when i did a du -sh). In the my.cnf file of the App server innodb_log_file_size is set to 256M b.. ib_logfile0, ib_logfile1, ib_logfile2 on the DB server -each 5M. These log files were created freshly by the MySQL server as the log files from the App server was not copied to the DB server. In the my.cnf file of the DB server innodb_log_file_size is set to 5M by mistake. All the other settings were same as on the app server. The ibdata1 file is that of the App server. And I get the log sequence errors as shown in my previous post. But everything seems to be working fine. There have been no problems accessing the data. What I can I possibly do to get everything right. How can I correct the log sequence error? Should the log files of App server be in the DB server? Thanks, Ratheesh K J - Original Message - From: Jan Kirchhoff [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, December 11, 2006 1:25 PM Subject: Re: Innodb log sequence error - urgent Ratheesh K J schrieb: Hello all, yesterday we seperated our app server and db server. We moved our 70GB of data from our app server to a new DB server. We installed MySQL 4.1.11 on the DB server. Now the following happened. On the DB server the ibdata1 and all the databases are the old ones (which were copied from the app server). But when Mysql was installed the ib_logfile0 ib_logfile1 and ib_logfile2 were created freshly on the DB serever. Each of these log files were created with 5M size. on the app server these files were 256M in size (innodb_log_file_size = 256M). On the DB server it is (innodb_log_file_size = 5M). Today morning when I checked the error log, there seems to be a lot of error msg flowing in. 061211 11:41:47 InnoDB: Error: page 203046 log sequence number 87 3002891543 InnoDB: is in the future! Current system log sequence number 86 4025048037. InnoDB: Your database may be corrupt. You cannot just copy innodb-databases to other servers without adjusting your my.cnf: Once you created an innodb-database, you cannot change parameters like innodb_log_file_size any more. (this is explained in the manual, you should read the chapter about backing up and restoring innodb-databases) So when you copy the database to the new server, be sure to copy the settings from the my.cnf, too! Jan
First essay - ERROR 1064
Hello ! I'm a beginner with mysql. I just installed the phpMyAdmin 2.9.1.1-Debian-1 / mysql 14.12 Distrib 5.0.30 for a soft (koha), but I have an error when I try to install the base : Creating the MySQL database for Koha... 0 ERROR 1064 (42000) at line 772: 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 'return char(4) default NULL, renewals tinyint(4) default NULL, timestamp tim' at line 9 Can't locate XML/Simple.pm in @INC (@INC contains: /usr/local/koha/intranet/modules /etc/perl /usr/local/lib/perl/5.8.8 /usr/local/share/perl/5.8.8 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl /usr/local/lib/perl/5.8.4 /usr/local/share/perl/5.8.4 .) at /usr/local/koha/intranet/modules/C4/Context.pm line 23. BEGIN failed--compilation aborted at /usr/local/koha/intranet/modules/C4/Context.pm line 23. Compilation failed in require at scripts/updater/updatedatabase line 21. BEGIN failed--compilation aborted at scripts/updater/updatedatabase line 21. Problem updating database... Is the problem from mysql ? Thanks for help, Thibaud. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the db.opt ?
Would like to know what is the archive db.opt. http://www.google.co.uk/search?q=mysql+db.optstart=0ie=utf-8oe=utf-8client=firefox-arls=org.mozilla:en-US:official -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump slows to crawl
Try dropping the indexes first if you can, would save you about half the time and then re-build them after the dump finishes. Obviously you would need to do it at a quite time though when the DB is not being used. Is a binary backup not an option? at 29G is a large text file to write Ade David Sparks wrote: I'm trying to dump some bigger tables without much luck. Anyone have any advice to dump larger tables? mysqldump starts guns blazing, but quickly it isn't doing anything as viewed by strace. After 1 day trying to dump a MyISAM table with 2.7G .MYD and 5.3G .MYI the dumpfile is 270MB compressed and it seems to be dumping 1K per second. After 12 hours trying to dump an InnoDB table with a 29G .ibd, same problem ... data is trickling out. I'm using mysqldump from 5.0.26 dumping a 4.1.21 server. I've tried several incarnations of options, that latest is (-e, -q *should* be enabled by default): mysqldump -e --no-create-db --skip-add-drop-table -q -single-transaction -v database How to speed this up? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump slows to crawl
I never experience any dump that were slow due to the index. The index aren't dumped anyway they will be recreate when you import them back so it shouldn't matter. (And that will cause problem if the db is running) so I wouldn't drop the index on your table if I were you... Your getting a lot of compression ratio 2.7G = 270 Megs, is it possible that your dump is CPU bound ? I have seen this quite often when using bzip2 for example which makes the dump takes very long! You can see that from top when the dump is running. If that's the case you could try gzip which takes much less cpu (but will give a bigger dump size) Also about using the mysqldump 5.0 on a mysql 4.1 server... hmmm not sure about which side effect that may have! I usually use the version that comes with the server... -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Adrian Bruce a écrit : Try dropping the indexes first if you can, would save you about half the time and then re-build them after the dump finishes. Obviously you would need to do it at a quite time though when the DB is not being used. Is a binary backup not an option? at 29G is a large text file to write Ade David Sparks wrote: I'm trying to dump some bigger tables without much luck. Anyone have any advice to dump larger tables? mysqldump starts guns blazing, but quickly it isn't doing anything as viewed by strace. After 1 day trying to dump a MyISAM table with 2.7G .MYD and 5.3G .MYI the dumpfile is 270MB compressed and it seems to be dumping 1K per second. After 12 hours trying to dump an InnoDB table with a 29G .ibd, same problem ... data is trickling out. I'm using mysqldump from 5.0.26 dumping a 4.1.21 server. I've tried several incarnations of options, that latest is (-e, -q *should* be enabled by default): mysqldump -e --no-create-db --skip-add-drop-table -q -single-transaction -v database How to speed this up? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql locking during BLOB upload
Dear MySql, I am finding my version 5.1.6 and 4.1.9 sometimes locking up during the passing of BLOB data. Size between 25 and 250 KB. If I repeat the transaction it will pass. Statistically it occurs about 1 in 30 INSERTs or UPDATEs or a blob. Non-Blob data to the same table will never lock. mysqladmin will show an entry 'Sleep'. The only ever solution is to kill my connection and try again. There is no one table, no one server and no one version of MySql which does this, and the problem is not repeatable. Has anybody experienced this and maybe knows what I can do? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: only update if values different
I agree, you should just update it since the standard operation for MYSQL is to only apply updates if the value is changing. http://dev.mysql.com/doc/refman/5.0/en/update.html If you set a column to the value it currently has, MySQL notices this and does not update it. Ed -Original Message- From: Peter [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 3:42 PM To: Nick Meyer Cc: mysql@lists.mysql.com Subject: Re: only update if values different Hello, with good indeces 100 000 rows is basically nothing. Give it a try. Peter Nick Meyer wrote: What is the best way to UPDATE a row only if values are different? We have a mainframe extract that literally has 100,000 rows and am worried about the performance of just running INSERTs each night. Is there a simple comparison command or would you have to nest a SELECT statement? Thank you, Nick -- 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]
UNIQUE KEY vs NULLs
Hi, I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? Thx ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
old-password issue with mysqldump
Hi everyone
I'm using mysqld with option old-passwords in my server (let's call
it serverA)
and in my application I use UPDATE . SET
USERPWD=PASSWORD('ABCD') to set the users password
now I want to move my database to another server (serverB) and the
mysqld in this new server doesn't run with old-passwords
so I dumped the databases from the first server (serverA) to a file
using mysqldump command and I imported it to the new server (serverB)
with command mysql source database.sql
now no one from my users can access to his account and I think it's
because the old-passwords is not enabled in the new server (serverB)
I can enable old-passwords in the new server but I don't want to do
that (the new password format is more secure and better, right ?).
so is there anyway to convert all the password in my database to the
new password format ?
Thanks
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNIQUE KEY vs NULLs
It is expected behavior, you can make the unique key a primary key instead. This should prevent this situation. Ed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, December 11, 2006 7:42 AM To: mysql@lists.mysql.com Subject: UNIQUE KEY vs NULLs Hi, I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? Thx ImRe -- 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: UNIQUE KEY vs NULLs
This is a feature - a NULL value is an undefined value, therefore two NULL values are not the same. Can be a little confusing but makes sense when you think about it. A UNIQUE index does ensure that non-NULL values are unique; you could specify that your column not accept NULL values. Dan On 12/11/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? Thx ImRe -- 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: UNIQUE KEY vs NULLs
I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) A feature. NULL isn't equal to NULL. If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? If you don't want to have NULL, use a primary key instead of a unique key. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.14 Beta has been released
Dear MySQL users, We are proud to present to you the MySQL Server 5.1.14 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, pay attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.14 Beta release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes from version to version in the MySQL source code through the latest released version of MySQL 5.1, the MySQL 5.1.12-beta. It can also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-14.html NOTE: Embedded is only enabled in the rpm packages, due to problems on other platforms. Functionality added or changed: * Incompatible change: Previously, you could create a user-defined function (UDF) or stored function with the same name as a built-in function, but could not invoke the UDF. Now an error occurs if you try to create such a UDF. The server also now generates a warning if you create a stored function with the same name as a built-in function. It is not considered an error to create a stored function with the same name as a built-in function because you can invoke the function using db_name.func_name() syntax. However, the server now generates a warning in this case. (Bug#22619: http://bugs.mysql.com/22619) See Section 9.2.4, Function Name Resolution, for the rules describing how the server interprets references to different kinds of functions. * NDB Cluster (Replication): ndb_restore now creates the apply_status and schema tables if they do not already exist on the slave cluster. (Bug#14612: http://bugs.mysql.com/14612) * NDB Cluster: Backup messages are now printed to the Cluster log. (Bug#24544: http://bugs.mysql.com/24544) * NDB Cluster: The error message Management server closed connection, when recorded in the MySQL error log, now includes a timestamp indicating when the error took place. (Bug#21519: http://bugs.mysql.com/21519) * NDB Cluster (Disk Data): The output of mysqldump now includes by default all tablespace and logfile group definitions used by any tables or databases that are dumped. (Bug#20839: http://bugs.mysql.com/20839) Note: The working of the --all-tablespaces or -Y option for mysqldump remains unaffected by this change. * Direct and indirect usage of stored routines, user-defined functions, and table references is now prohibited in CREATE EVENT and ALTER EVENT statements. (Bug#22830: http://bugs.mysql.com/22830) See Section 20.2.1, CREATE EVENT Syntax, and Section 20.2.2, ALTER EVENT Syntax, for more specific information. * DROP TRIGGER now supports an IF EXISTS clause. (Bug#23703: http://bugs.mysql.com/23703) Bugs fixed: * NDB Cluster (Replication): If errors occurred during purging of the binary logs, extraneous rows could remain left in the binlog_index table. (Bug#15021: http://bugs.mysql.com/15021) * NDB Cluster (Disk Data): ndb_restore could sometimes fail when attempting to restore Disk Data tables due to data node failure caused by accessing unitialized memory. (Bug#24331: http://bugs.mysql.com/24331) * NDB Cluster (Disk Data): Excessive fragmentation of Disk Data files (including log files and data files) could occur during the course of normal use. (Bug#24143: http://bugs.mysql.com/24143) * NDB Cluster (Disk Data): It was possible to execute a statement for creating a Disk Data table that referred to a nonexistent tablespace, in which case the table was an in-memory NDB table. Such a statement instead now fails with an appropriate error message. (Bug#23576: http://bugs.mysql.com/23576) * NDB Cluster (Disk Data): Under some circumstances, a DELETE from a Disk Data table could cause mysqld to crash. (Bug#23542: http://bugs.mysql.com/23542) * NDB Cluster (Cluster APIs): Using BIT values with any of the comparison methods of the NdbScanFilter class caused the cluster's data nodes to fail. (Bug#24503: http://bugs.mysql.com/24503) * NDB Cluster: A value equal to or greater than the allowed maximum for LongMessageBuffer caused all data nodes to crash. (Bug#22547: http://bugs.mysql.com/22547) * NDB Cluster: The failure of a data node failure during a schema operation could lead to additional node
Re: MySQL 5.1.14 Beta has been released
Mads Martin Joergensen wrote: Dear MySQL users, We are proud to present to you the MySQL Server 5.1.14 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, pay attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.14 Beta release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Hi, in any release that I tried up to 5.1.12 inclusive, almost any access to the information_schema paralyzed the server (bringing it to its knees). This bug was reported long ago and confirmed by many users in bugzilla, but in no changelog that I've read so far it seems to have been fixed. Is there any update on this issue? Any plan to fix it if it's not already resolved? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prefixing fields with table name when joining?
I have three tables (x, y, and z) with the same 3 fields (id, name, number). If I do: SELECT * FROM x, y, z WHERE ... each row of my result will contain 3 id fields, 3 name fields, and 3 number fields. Of course, I can/should do: SELECT x.id AS x_id, x.name AS x_name, x.number AS x_number, y.id AS y_id, y.name AS y_name, y.number AS y_number, z.id AS z_id, z.name AS z_name, z.number AS z_number FROM x, y, z WHERE ... Short of scripting, is there any way to get MySQL to do this? Something like: SELECT * FROM x, y, z PREFIX FIELDS WITH TABLE NAME WHERE ... -- We're just a Bunch Of Regular Guys, a collective group that's trying to understand and assimilate technology. We feel that resistance to new ideas and technology is unwise and ultimately futile. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: old-password issue with mysqldump
On 12/11/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote:
Hi everyone
I'm using mysqld with option old-passwords in my server (let's call
it serverA)
and in my application I use UPDATE . SET
USERPWD=PASSWORD('ABCD') to set the users password
now I want to move my database to another server (serverB) and the
mysqld in this new server doesn't run with old-passwords
so I dumped the databases from the first server (serverA) to a file
using mysqldump command and I imported it to the new server (serverB)
with command mysql source database.sql
now no one from my users can access to his account and I think it's
because the old-passwords is not enabled in the new server (serverB)
I can enable old-passwords in the new server but I don't want to do
that (the new password format is more secure and better, right ?).
so is there anyway to convert all the password in my database to the
new password format ?
Start your new server with the --old-passwords option, login with your
superuser (root or whatever you call it) and change its password to
the new format:
SET PASSWORD FORv 'some_user'@'some_host' = PASSWORD('newpwd');
Then you can logout and restart your server without the
--old-passwords option, and still login with the superuser, and with
this account, you can set all your users passwords to the new format
with a command just like the one above, check:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
And you may find all information about passwords there.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1.14 Beta has been released
how do i find a log file of mysql - Original Message - From: Nico Sabbi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, December 11, 2006 8:37 PM Subject: Re: MySQL 5.1.14 Beta has been released Mads Martin Joergensen wrote: Dear MySQL users, We are proud to present to you the MySQL Server 5.1.14 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, pay attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.14 Beta release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Hi, in any release that I tried up to 5.1.12 inclusive, almost any access to the information_schema paralyzed the server (bringing it to its knees). This bug was reported long ago and confirmed by many users in bugzilla, but in no changelog that I've read so far it seems to have been fixed. Is there any update on this issue? Any plan to fix it if it's not already resolved? Thanks, Nico -- 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]
First essay : error 1064
Hi ! I'm a beginner with mysql. I just installed the phpMyAdmin 2.9.1.1-Debian-1 / mysql 14.12 Distrib 5.0.30 for a soft (koha), but I have an error when I try to install the base : Creating the MySQL database for Koha... 0 ERROR 1064 (42000) at line 772: 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 'return char(4) default NULL, renewals tinyint(4) default NULL, timestamp tim' at line 9 Can't locate XML/Simple.pm in @INC (@INC contains: /usr/local/koha/intranet/modules /etc/perl /usr/local/lib/perl/5.8.8 /usr/local/share/perl/5.8.8 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl /usr/local/lib/perl/5.8.4 /usr/local/share/perl/5.8.4 .) at /usr/local/koha/intranet/modules/C4/Context.pm line 23. BEGIN failed--compilation aborted at /usr/local/koha/intranet/modules/C4/Context.pm line 23. Compilation failed in require at scripts/updater/updatedatabase line 21. BEGIN failed--compilation aborted at scripts/updater/updatedatabase line 21. Problem updating database... Is the problem from mysql ? Thanks for help, Thibaud. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: old-password issue with mysqldump
Hi
Thanks, this is easy and simple if I just have few users :) I have
more than 30,000 users :)
I think there is no way to do this so I have to use old-passwords
option in the new servers :)
Thanks
On 12/11/06, Daniel da Veiga [EMAIL PROTECTED] wrote:
On 12/11/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote:
Hi everyone
I'm using mysqld with option old-passwords in my server (let's call
it serverA)
and in my application I use UPDATE . SET
USERPWD=PASSWORD('ABCD') to set the users password
now I want to move my database to another server (serverB) and the
mysqld in this new server doesn't run with old-passwords
so I dumped the databases from the first server (serverA) to a file
using mysqldump command and I imported it to the new server (serverB)
with command mysql source database.sql
now no one from my users can access to his account and I think it's
because the old-passwords is not enabled in the new server (serverB)
I can enable old-passwords in the new server but I don't want to do
that (the new password format is more secure and better, right ?).
so is there anyway to convert all the password in my database to the
new password format ?
Start your new server with the --old-passwords option, login with your
superuser (root or whatever you call it) and change its password to
the new format:
SET PASSWORD FORv 'some_user'@'some_host' = PASSWORD('newpwd');
Then you can logout and restart your server without the
--old-passwords option, and still login with the superuser, and with
this account, you can set all your users passwords to the new format
with a command just like the one above, check:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
And you may find all information about passwords there.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: old-password issue with mysqldump
Depending on the interface you're using, you can code an on demand
convert function. You can keep your server running with the
--old-passwords option till all your passwords are converted.
So, you keep the --old-passwords option, when a user login, you test
the password to see if its the new or old password format (simply
SELECT the hash from the table and check its lenght, old passwords
have an 8 char lenght, new have 16, AFAIK), if its old, you convert
it. Its transparent for the users and eventually you'll have all your
users converted to the new, more secure and better password format...
You can check once in a while how many passwords are still in the old
format.
Anyway, its just an idea...
On 12/11/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote:
Hi
Thanks, this is easy and simple if I just have few users :) I have
more than 30,000 users :)
I think there is no way to do this so I have to use old-passwords
option in the new servers :)
Thanks
On 12/11/06, Daniel da Veiga [EMAIL PROTECTED] wrote:
On 12/11/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote:
Hi everyone
I'm using mysqld with option old-passwords in my server (let's call
it serverA)
and in my application I use UPDATE . SET
USERPWD=PASSWORD('ABCD') to set the users password
now I want to move my database to another server (serverB) and the
mysqld in this new server doesn't run with old-passwords
so I dumped the databases from the first server (serverA) to a file
using mysqldump command and I imported it to the new server (serverB)
with command mysql source database.sql
now no one from my users can access to his account and I think it's
because the old-passwords is not enabled in the new server (serverB)
I can enable old-passwords in the new server but I don't want to do
that (the new password format is more secure and better, right ?).
so is there anyway to convert all the password in my database to the
new password format ?
Start your new server with the --old-passwords option, login with your
superuser (root or whatever you call it) and change its password to
the new format:
SET PASSWORD FORv 'some_user'@'some_host' = PASSWORD('newpwd');
Then you can logout and restart your server without the
--old-passwords option, and still login with the superuser, and with
this account, you can set all your users passwords to the new format
with a command just like the one above, check:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
And you may find all information about passwords there.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
echo Hello World :)
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump slows to crawl
Mathieu Bruneau wrote: I never experience any dump that were slow due to the index. The index aren't dumped anyway they will be recreate when you import them back so it shouldn't matter. (And that will cause problem if the db is running) so I wouldn't drop the index on your table if I were you... Good point. Your getting a lot of compression ratio 2.7G = 270 Megs Opps I wasn't clear, I killed the dump when it was 10% done. It never would've finished. , is it possible that your dump is CPU bound ? I have seen this quite often when using bzip2 for example which makes the dump takes very long! You can see that from top when the dump is running. If that's the case you could try gzip which takes much less cpu (but will give a bigger dump size) I am using gzip ... the cpu utilization is at 0%. The dump runs on a different server than the DB. Also about using the mysqldump 5.0 on a mysql 4.1 server... hmmm not sure about which side effect that may have! I usually use the version that comes with the server... I guess I could copy the binary and libs to another server to test this. However strace suggests that mysqldump is waiting for the server to send data (its reading the socket). I just checked my latest dump attempt and it has now spent 128077 seconds trying to dump the 29GB table and making almost no progress (1 row every 30 seconds as estimated by strace). I guess the MVCC implementation is pushed to its limits because I can see other queries not finishing in a timely manner. :( Anyone have any other ideas? ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump slows to crawl
I just checked my latest dump attempt and it has now spent 128077 seconds trying to dump the 29GB table and making almost no progress (1 row every 30 seconds as estimated by strace). I guess the MVCC implementation is pushed to its limits because I can see other queries not finishing in a timely manner. :( Anyone have any other ideas? Have you checked your network card? You're pushing a lot of data over the network over a sustained amount of time and a bad NIC may have a problem keeping up. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.14 Release - Change in Cluster System Tables
Hi, The following information is important to all MySQL Cluster 5.1 users, and especially to those using MySQL Cluster Replication. It was not included in the 5.1.14 release announcement, so I'm quoting the relevant update to the 5.1.14 changelog ( http://dev.mysql.com/doc/refman/5.1/en/news-5-1-14.html ) here: [begin] Two major changes have taken place with regard to the MySQL Cluster system tables. These are: 1. Incompatible change: The cluster database is no longer used. The tables formerly found in the cluster database are now in the mysql database, and have been renamed as ndb_binlog_index, ndb_apply_status, and ndb_schema. 2. The mysql.ndb_apply_status and mysql.ndb_schema tables (formerly cluster.apply_status and cluster.schema are now created by ndb_restore in the event that they do not already exist on the slave cluster. (Bug#14612: http://bugs.mysql.com/14612) Note: When upgrading from versions of MySQL previous to 5.1.14 to 5.1.14 or later, mysql_fix_privilege_tables merely creates a new mysql.ndb_binlog_index table, but does not remove the existing cluster database (or, if upgrading from MySQL 5.1.7 or earlier, the existing cluster_replication database), nor any of the tables in it. For more information, see Section 15.10.4, “Replication Schema and Tables”: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-schema.html [end] The fact that news of this non-trivial change for MySQL Cluster 5.1 did not make it into the official 5.1.14 release announcement is entirely my fault, and I hope you will accept my deepest apologies for the omission. cheers, j. -- Jon Stephens - [EMAIL PROTECTED] Technical Writer - MySQL Documentation Team ___ Brisbane, Australia (GMT +10.00) _x_ Bangkok, Thailand (GMT +07.00) ___ Office: +61 (7) 3209 1394 _x_ Office: +66 0 2740 3691 5 ext. #201 Mobile: +61 402 635 784 MySQL AB: www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prefixing fields with table name when joining?
Hi Kelly, The short script of prefixing fields with tablename till 5.0 and beta is not applicable. Hope, this senario would be rare, that too many tables with larger field length, more columns etc. Thanks ViSolve DB Team - Original Message - From: Kelly Jones [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, December 11, 2006 8:39 PM Subject: Prefixing fields with table name when joining? I have three tables (x, y, and z) with the same 3 fields (id, name, number). If I do: SELECT * FROM x, y, z WHERE ... each row of my result will contain 3 id fields, 3 name fields, and 3 number fields. Of course, I can/should do: SELECT x.id AS x_id, x.name AS x_name, x.number AS x_number, y.id AS y_id, y.name AS y_name, y.number AS y_number, z.id AS z_id, z.name AS z_name, z.number AS z_number FROM x, y, z WHERE ... Short of scripting, is there any way to get MySQL to do this? Something like: SELECT * FROM x, y, z PREFIX FIELDS WITH TABLE NAME WHERE ... -- We're just a Bunch Of Regular Guys, a collective group that's trying to understand and assimilate technology. We feel that resistance to new ideas and technology is unwise and ultimately futile. -- 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: UNIQUE KEY vs NULLs
From: Dan Buettner [mailto:[EMAIL PROTECTED] This is a feature - a NULL value is an undefined value, therefore two NULL values are not the same. Can be a little confusing but makes sense when you think about it. A UNIQUE index does ensure that non-NULL values are unique; you could specify that your column not accept NULL values. I was afraid to hear something like this. I'd rather not use some invalid value to mark unknown fields. So I went a bit further, and tried to ensure the uniqueness of the null values with a trigger. CREATE TRIGGER Target_Before_Insert BEFORE INSERT ON Target FOR EACH ROW IF NEW.IMSI IS NULL OR NEW.IMEI IS NULL THEN BEGIN DECLARE c_ INT UNSIGNED; SELECT COUNT(*) INTO c_ FROM Target WHERE IMSI = NEW.IMSI AND IMEI = NEW.IMEI; IF c_ THEN SET NEW.Id = NULL; END IF; END; END IF; Here Id is a non NULL field, so setting it to NULL should trigger an error. But when I run an insert where the trigger body would run, then I get the following error. Table 'Target' was not locked with LOCK TABLES I am pretty sure, I don't use LOCK TABLES at all. What is going on here? How can I get rid of this error? Thx ImRe Dan On 12/11/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? Thx ImRe -- 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]
Why innodb can give the same X gap lock to two transactions?
Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
