mysqld_multi doesn't support !include or !includedir directives
Hi, I try to use !includedir directive with mysqld_multi for load other option files but nothing is loaded by mysql. In the default my.cnf, I have only the includedir directive for my configuration directory : !includedir /etc/my.cnf.d And in /etc/my.cnf.d/mysql3306.cnf I have : [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = toto password = toto log= /var/log/mysqld_multi.log [mysqld3306] port = 3306 socket = /var/lib/mysql_3306/mysql.sock pid-file = /var/lib/mysql_3306/test.pid datadir = /var/lib/mysql_3306 tmpdir = /tmp user = mysql mysqld = /usr/sbin/mysqld log-error= /var/lib/mysql_3306/test.err But mysqld_multi doesn't not load mysql3306.cnf file. # service mysqld_multi report Reporting MySQL servers No groups to be reported (check your GNRs) Whereas # my_print_defaults --defaults-file=/etc/my.cnf mysqld3306 --port=3306 --socket=/var/lib/mysql_3306/mysql.sock --pid-file=/var/lib/mysql_3306/test.pid --datadir=/var/lib/mysql_3306 --tmpdir=/tmp --user=mysql I found the Bug #29327 dated 2007 but without fix. I use mysqld_multi log file version 2.16 and MySQL 5.6.24 on CentOS 6. Someone as an idea please? Where is my mistake? Regards, Thomas
Re: slave to master
Am Freitag, 29. April 2016, 14:49:57 schrieb william drescher: > On 4/28/2016 5:20 PM, Thomas wrote: > > Hi, > > > > I have setup an master slave replication. > > This works fine. > > I have running an Apache webserver and some other programms accessing the > > master. > > Whats the standard pocedure if master fail? > > > > When the master is repaired and up you need to stop the programs > on the slave from accessing mysql (ie: stop the programs on the > slave), make a database copy of the slave, copy it to the master > and reload the database on the master, and then use the Change > Master to command on the slave to set up the slave to begin > replicating and start slave. > > This is the poor man's failover for low volume systems. > In essence the slave becomes the main database server until you > are ready to restart the master and replication. Hi thanks for all the answers, I only want that when master fails the slave will be the new master and do everythink that the master has before done. So I have more time to repair the master. I have to change the slave to master because I need write access to the mySQL databases. Sure that when mySQL data on the master are damaged I will have same problems on the slave, but for this I have an undependent data saving, not realtime but acceptable. Sure I need in this case more time to repair the whole system. slave to master: Like this: https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html Is this the way to go? thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
slave to master
Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to change character sets in InnoDB as fast as possible
Hi there, I am looking for a way to convert about 40GB of InnoDB tables from latin1 character set to utf8. As true conversion will take ages, I had the idea of just changing the character sets (and preferably collation, too) of the tables without actually converting the data. Conversion could be done manually later. From my side it is ok that the data is wrongly encoded in the tables for the time of manual conversion. The goal is to have the tables up and running as soon as possible. I tried to set the character sets with CHANGE, MODIFY, ALTER, but mysql always seems to convert the tables’ data. I read that character sets will be ignored if you convert to binary character sets, but still changing to this set takes ages. So my question is: Is there a way of changing an InnoDB table's character set and collation without letting mysql converting the data? What is the fastest way of changing the character sets, regardless of the method? If converting the data is the only way to go, I thought about converting several tables at the same time, but still this would require a down time of several hours, which is basically unacceptable. Is there no faster way to go? Thanks a lot for your ideas! Best, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Got It; Thank You; Re: Mysql Bug 04/01/11
Got It; Thank You, Thank You, Thank You On 4/1/2011 11:28 PM, Claudio Nanni wrote: Hi Thomas, Did you run the post install script? http://kae.li/iiikj Claudio On Apr 2, 2011 2:20 AM, Thomas Dineen tdin...@ix.netcom.com mailto:tdin...@ix.netcom.com wrote:
Mysql Bug 04/01/11
Gentlemen: - Keep in mind that I have approximately 50 hours into this Mysql server install and still no results! - Regarding the Sun Freeware package mysql-5.0.67-sol10-x86-local.gz - When installed and started with the following command: /usr/local/mysql/bin/mysqld_safe --user=mysql The following error occurs: 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 1) Please identify where the missing file can be found, keeping in mind that I just installed and have no backup (of the current rev.). 2) If we are installing in /usr/local/mysql where exactly dose this file belong, (exact path please). 3) What exactly do the file ownersiip and permissions have to be? Keep in mind that I have tried several dozen experiments over many late hours with a version host.frm from a older version of mysql with NO success! I have tried putting it in every possible location. Using 777 file permissions. A snapshot of my system: root@Sun# pwd /usr/local/mysql root@Sun# ls bin infoman mysql-test var host.frmlib my.cnf share include libexec mysql sql-bench root@Sun# ls -la host.frm -rwxrwxrwx 1 root root9064 Apr 1 16:39 host.frm root@Sun# ls -la mysql total 22 drwxrwxrwx 2 root root 512 Apr 1 16:34 . drwxr-xr-x 13 bin bin 512 Apr 1 16:39 .. -rwxrwxrwx 1 root root9064 Apr 1 16:34 host.frm - The entire transcript is shown below: 10401 16:39:27 mysqld started 110401 16:39:27 [Warning] option 'max_join_size': unsigned value 18446744073709 551615 adjusted to 4294967295 110401 16:39:27 [Warning] option 'max_join_size': unsigned value 18446744073709 551615 adjusted to 4294967295 110401 16:39:27 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 110401 16:39:27 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 InnoDB: Log scan progressed past the checkpoint lsn 0 36808 110401 16:39:27 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 43655 110401 16:39:27 InnoDB: Starting an apply batch of log records to the database ... InnoDB: Progress in percents: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 5 1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 110401 16:39:27 InnoDB: Started; log sequence number 0 43655 110401 16:39:27 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './my sql/host.frm' (errno: 13) 110401 16:39:27 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './my sql/host.frm' (errno: 13) 110401 16:39:27 [ERROR] Fatal error: Can't open and lock privilege tables: Can' t find file: './mysql/host.frm' (errno: 13) 110401 16:39:27 mysqld ended 110401 16:42:30 mysqld started 110401 16:42:30 [Warning] option 'max_join_size': unsigned value 18446744073709 551615 adjusted to 4294967295 110401 16:42:30 [Warning] option 'max_join_size': unsigned value 18446744073709 551615 adjusted to 4294967295 110401 16:42:30 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 110401 16:42:30 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 InnoDB: Log scan progressed past the checkpoint lsn 0 36808 110401 16:42:30 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 43655 110401 16:42:30 InnoDB: Starting an apply batch of log records to the database ... InnoDB: Progress in percents: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 5 1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 110401 16:42:30 InnoDB: Started; log sequence number 0 43655 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './my sql/host.frm' (errno: 13) 110401 16:42:30 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './my sql/host.frm' (errno: 13) 110401 16:42:30 [ERROR] Fatal error: Can't open and lock privilege tables: Can' t find file: './mysql/host.frm' (errno: 13) 110401 16:42:30 mysqld ended root@Sun# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql-5.0.67-sol10-x86-local Bug
Gentle People: Using the following startup command: /etc/init.d/mysql.server start I get the following error: 110331 18:49:41 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 1) Where do I find or how do I create the file host.frm 2) What is the proper location (Full path please) for host.frm 3) It would be easier to debug this if your error messages included the full path! not something like ./ which provides no help in identifing where a file is required. Thomas Dineen 110331 18:40:10 mysqld started 110331 18:40:10 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 110331 18:40:10 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 InnoDB: Log scan progressed past the checkpoint lsn 0 36808 110331 18:40:10 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 43655 110331 18:40:10 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 7 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 110331 18:40:11 InnoDB: Started; log sequence number 0 43655 110331 18:40:11 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 110331 18:40:11 mysqld ended 110331 18:49:41 mysqld started 110331 18:49:41 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 110331 18:49:41 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 InnoDB: Log scan progressed past the checkpoint lsn 0 36808 110331 18:49:41 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 43655 110331 18:49:41 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 7 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 110331 18:49:41 InnoDB: Started; log sequence number 0 43655 110331 18:49:41 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './mys ql/host.frm' (errno: 13) 110331 18:49:41 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './mys ql/host.frm' (errno: 13) 110331 18:49:41 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 110331 18:49:41 mysqld ended 110331 19:06:48 mysqld started 110331 19:06:48 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 110331 19:06:48 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 InnoDB: Log scan progressed past the checkpoint lsn 0 36808 110331 19:06:48 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 43655 110331 19:06:48 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 7 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 110331 19:06:48 InnoDB: Started; log sequence number 0 43655 110331 19:06:48 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './mys ql/host.frm' (errno: 13) 110331 19:06:48 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find file: './mys ql/host.frm' (errno: 13) 110331 19:06:48 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 110331 19:06:48 mysqld ended 110331 19:19:10 mysqld started 110331 19:19:10 [Warning] option 'max_join_size': unsigned value 184467440737095 51615 adjusted to 4294967295 110331 19:19:10 [Warning] option 'max_join_size
Re: Variable Expansion Using MySQL Client and .sql Files
Simple - brilliant. I've modified to cat a temporary script... 1) cat the file as suggested: cat ~/payload/scripts/create_mysqldb.sql EOF ###--- ### Display the current user ###--- create database puppet; ###--- ### Display all default accounts ###--- USE puppet; ###--- ### Remove anonymous accounts ###--- GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON puppet.* TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'; (variables are expanded) ###--- ### Verify new admin account was added ###--- SELECT user,Host,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Alter_priv from mysql.db; EOF 2) Execute the script 3) Remove the script rm -f ~/payload/scripts/create_mysqldb.sql It is often the simplest things that are most beautiful. Thanks for pulling my head out :D TT On 07/29/2010 03:19 PM, Travis Ard wrote: mysqlEOF GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'; EOF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Automate Install/Configuration of MySQL on Linux
To answer both emails... My first install is Puppet :) 1) I have many clients (schools with Macs/Linux) that could use this package. This is in the works - good call. For the sake of DRP (Disaster Recovery Planning) I automate all of my installs on the 1% off-chance that my backups are partially fouled for some reason. 2) I used the .sql file that you recommended and wow - it's so much easier than expect. Once I had the right key-words (mysql .sql script) Google returned the right pages: 4.5.1.4. Executing SQL Statements from a Text File http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html The problem I have now is variable substitution. But, that's another thread. Thanks guys, TT On 07/27/2010 11:40 PM, Andrés Tello wrote: just place all your sql sentences in a file, setup the database and then use: mysql -uroot -hlocalhost file_with_allsql_you_need.sql or cat file_file_with_allsql_you_need.sql | mysql -u root -hlocalhost and you are done. On Tue, Jul 27, 2010 at 3:56 PM, Todd E Thomas todd_...@ssiresults.com mailto:todd_...@ssiresults.com wrote: I'm looking for automation direction... I've found many packages that sit on top of MySQL. For the purposes of consistency I'd like to automate these installs. I've been able to automate the install and configuration of everything except the mysql part. I'm using CentOS 5.5. Installing/verifying is no big deal. It's the MySQL configuration that's holding me up. Basically I've created an expect script. It works 99% but it's a PITA to finish. Here's what I'd like to accomplish: *Set the default admin password # mysqladmin -u root password 'root-password' *login to mysql mysql mysql -u root -p *Drop the anonymous accounts mysql DELETE FROM mysql.user WHERE user = ''; *Sync all of the root passwords mysql UPDATE mysql.user SET Password = PASSWORD('root-password') WHERE User = 'root'; *Remove the test database: mysql drop database test; In another script I would like to create databases for specific packages. EG: Concrete5, for example needs: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY 'admin-password'; If there is a better way to do this than using expect I would greatly appreciate any pointers in the right direction. Bash is comfortable for me and perl is within reach. I'm not much versed in anything else right now. -- Thanks for the assist, Todd E Thomas It's a frail music knits the world together. -Robert Dana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
Variable Expansion Using MySQL Client and .sql Files
Hey all, I've found many packages that sit on top of MySQL for various clients. For the purposes of consistency I'd like to automate these installs. I've been directed towards using .sql files and they work great. The trouble I'm having now is that I would like to secure the installation but variable expansion isn't clicking for me. My setup is fairly straight-forward: I have a single installer script that calls all other scripts. This is how it works: 1) Source in all global environment variables from a working file: 1_GLOBAL_ENV.sh 2) execute script to create mysql db 3) secure mysql . /root/payload/1_GLOBAL_ENV.sh ... ###--- ### Configure MySQL ###--- set -x mysql -v ${INST_SCRIPTS}/mysqld/secure_mysqld.sql ... mysql -v ${INST_SCRIPTS}/mysqld/create_db.sql ... --- The create_db.sql should be similar to this: Concrete5, for example needs: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'; --- The secure_mysqld.sql script is fairly simple as well: # Display the current user: select user(); # Display all default accounts: SELECT User,Host,password FROM mysql.user; # Remove anonymous accounts: DELETE FROM mysql.user WHERE user = ''; # Display all remaining accounts: SELECT User,Host,password FROM mysql.user; # Sync root passowrds: UPDATE mysql.user SET Password = PASSWORD('$PASSWD_PRIV_ROOT') WHERE User = 'root'; exit --- It all works pretty well. Variable expansion is the problem. For now all of my other scripts substitute $PASSWD_PRIV_ROOT (from my 1_GLOBAL_ENV.sh) for the actual password. The *.sql scripts do not. If anyone can shed some light on this I would appreciate the help. -- Thanks for the assist, Todd E Thomas C: 515.778.6913 It's a frail music knits the world together. -Robert Dana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Automate Install/Configuration of MySQL on Linux
I'm looking for automation direction... I've found many packages that sit on top of MySQL. For the purposes of consistency I'd like to automate these installs. I've been able to automate the install and configuration of everything except the mysql part. I'm using CentOS 5.5. Installing/verifying is no big deal. It's the MySQL configuration that's holding me up. Basically I've created an expect script. It works 99% but it's a PITA to finish. Here's what I'd like to accomplish: *Set the default admin password # mysqladmin -u root password 'root-password' *login to mysql mysql mysql -u root -p *Drop the anonymous accounts mysql DELETE FROM mysql.user WHERE user = ''; *Sync all of the root passwords mysql UPDATE mysql.user SET Password = PASSWORD('root-password') WHERE User = 'root'; *Remove the test database: mysql drop database test; In another script I would like to create databases for specific packages. EG: Concrete5, for example needs: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY 'admin-password'; If there is a better way to do this than using expect I would greatly appreciate any pointers in the right direction. Bash is comfortable for me and perl is within reach. I'm not much versed in anything else right now. -- Thanks for the assist, Todd E Thomas It's a frail music knits the world together. -Robert Dana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: right join troubles
On Sat, 19 Sep 2009, b wrote: I'm trying to select all members who have not registered for an event. I have tables 'members', 'events', and 'events_members', the latter a join table with event_id and member_id columns. The closest I've gotten is with this query: SELECT m.id, m.first_name, m.last_name FROM members AS m RIGHT JOIN events_members AS em ON (em.event_id = 10 AND m.id != em.member_id) ORDER BY m.last_name ASC; This returns an empty set IF there are no records at all in events_members with event_id = 10. But, in that case, I want to receive ALL members. However, if I add a single record with event_id = 10, I then get the expected list of all OTHER members. How can I modify this query so that, when there are 0 registered members for a particular event, I get back all members? Obviously, I could always first check for the existence of the event_id in the join table and, if not found, run the select on the members table. But I doubt that that's the best option. I think that you need two steps: CREATE TABLE events_members_tmp SELECT * FROM events_members WHERE event_id = 10; SELECT m.id, m.first_name, m.last_name FROM members AS m LEFT JOIN events_members_tmp AS em ON m.id = em.member_id WHERE em.member_id IS NULL ORDER BY m.last_name ASC; Having written this it appears that it could work in just one step as well: SELECT m.id, m.first_name, m.last_name FROM members AS m LEFT JOIN events_members AS em ON em.event_id = 10 AND m.id = em.member_id WHERE em.member_id IS NULL ORDER BY m.last_name ASC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Could not start the service MySQL:Error 0
I suffered the same problem, and the following worked for me: http://www.andy.name.my/2009/03/cannot-create-windows-service-for-mysqlerror0/
Re: Re: Could not start the service MySQL:Error 0
Try looking in Control Panel-Administrative Tools- Services and deleting all the MySQL services.
Re: Anyone using LVM for backing up?
Hi Tim, We use LVM snapshots all the time. They are essentially instantaneous with our 90GB innodb database files. A command to generate the snapshot could be: sudo /usr/sbin/lvcreate --snapshot --name mysqlsqlbackup --size 15G / dev/system/data01 Please let me know if you have any questions. -Tom On Jun 22, 2009, at 4:41 PM, Little, Timothy wrote: We have a 20 gig db (that includes the MYIs and MYDs and FRMs). We are wondering how long LVM snapshots take.. in that how long might the DB be read-locked? Do we have to read-lock it and flush tables? Are we talking half a second, ten-seconds, 20 minutes? Currently, when we copy the raw files from one directory to another, it takes about 20 mins and brings the DB to it's proverbial knees. When we copy the files with the db server down, it takes 10 minutes or so. Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=tmcgona...@online-buddies.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: myisamchk buffer_size warnings
On Tue, 16 Jun 2009, Thomas Spahni wrote: Hi I have MySQL 5.0.64 compiled from source. When I run myisamchk on any table I get the following warnings: Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 /etc/my.cnf contains the following: [myisamchk] key_buffer_size=20971520 sort_buffer_size=20971520 read_buffer_size=2097152 write_buffer_size=2097152 What's wrong here? Thomas Spahni Hi all, Replying to myself as the problem is fixed in the meantime (at least for me). It turned out to be bug #33785 described here: http://bugs.mysql.com/bug.php?id=33785 The solution is to fix the source code of myisamchk.c and recompile. The diff is: --- myisamchk.c.original2009-06-18 13:01:55.0 +0200 +++ myisamchk.c 2009-06-18 13:17:34.0 +0200 @@ -295,7 +295,7 @@ { key_buffer_size, OPT_KEY_BUFFER_SIZE, , (gptr*) check_param.use_buffers, (gptr*) check_param.use_buffers, 0, GET_ULONG, REQUIRED_ARG, (long) USE_BUFFER_INIT, (long) MALLOC_OVERHEAD, -(long) ~0L, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0}, +(ulong) ~0, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0}, { key_cache_block_size, OPT_KEY_CACHE_BLOCK_SIZE, , (gptr*) opt_key_cache_block_size, (gptr*) opt_key_cache_block_size, 0, @@ -309,17 +309,17 @@ (gptr*) check_param.read_buffer_length, (gptr*) check_param.read_buffer_length, 0, GET_ULONG, REQUIRED_ARG, (long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD, -(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0}, +(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0}, { write_buffer_size, OPT_WRITE_BUFFER_SIZE, , (gptr*) check_param.write_buffer_length, (gptr*) check_param.write_buffer_length, 0, GET_ULONG, REQUIRED_ARG, (long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD, -(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0}, +(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0}, { sort_buffer_size, OPT_SORT_BUFFER_SIZE, , (gptr*) check_param.sort_buffer_length, (gptr*) check_param.sort_buffer_length, 0, GET_ULONG, REQUIRED_ARG, (long) SORT_BUFFER_INIT, (long) (MIN_SORT_BUFFER + MALLOC_OVERHEAD), -(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0}, +(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0}, { sort_key_blocks, OPT_SORT_KEY_BLOCKS, , (gptr*) check_param.sort_key_blocks, (gptr*) check_param.sort_key_blocks, 0, GET_ULONG, REQUIRED_ARG, Unfortunately MySQL 5.0.64 is packed with SuSE-11.1 and thus a lot of installations will be broken. Some evil things may happen when you have less physical memory than the maximum default values as applied by the broken code. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: myisamchk buffer_size warnings
On Wed, 17 Jun 2009, Johan De Meersman wrote: Aren't those options defined in megabytes ? On Tue, Jun 16, 2009 at 4:59 PM, Thomas Spahni t...@lawbiz.ch wrote: Hi I have MySQL 5.0.64 compiled from source. When I run myisamchk on any table I get the following warnings: Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 /etc/my.cnf contains the following: [myisamchk] key_buffer_size=20971520 sort_buffer_size=20971520 read_buffer_size=2097152 write_buffer_size=2097152 What's wrong here? Thomas Spahni Hi again According to the very fine Manual http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html the values can be given as Bytes or with a suffix of K|M|G. This didn't change anything in my case. It's strange that I see a similar error from mysqld when I run make test. It says: CURRENT_TEST: alias 090617 12:44:21 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 Something must be very wrong here. Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
myisamchk buffer_size warnings
Hi I have MySQL 5.0.64 compiled from source. When I run myisamchk on any table I get the following warnings: Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 /etc/my.cnf contains the following: [myisamchk] key_buffer_size=20971520 sort_buffer_size=20971520 read_buffer_size=2097152 write_buffer_size=2097152 What's wrong here? Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Available parallelism in MySQL community edition 5.1.34?
Hi Mike, The way we do it is installing each mysql instance from a precompiled binary. So if you had an 8 core box, and you wanted to use each of the 8 cores, you could install a mysql binary installation and then make 7 copies of it. We use an internal naming convention like the following: /data01/multi_mysql/mysql_A /data01/multi_mysql/mysql_B /data01/multi_mysql/mysql_C /data01/multi_mysql/mysql_D Create a my.cnf under each of these mysql instance directories. Then all you have to do is create a mysql start up script for each of the instances in /etc/init.d. The startup script needs to the following three variables set. basedir=/data01/multi_mysql/mysql_A datadir=/data01/multi_mysql/mysql_A/data/ export MYSQL_HOME=/data01/multi_mysql/mysql_A/ The datadir variable is contained under the rest of the mysql installation. I usually put the logs under /data01/logs/mysql_A /data01/logs/mysql_B This approach is very simple, and works quite well. We use it instead of the multi_mysql startup/my.cnf approach. The approach works particularly well when you have lots of IO, as you said you did. We use this on large raid array DAS, and it works great. If there is anything unclear about my explanation. Please do let me know if you have any questions. I am happy to help. -Tom On Jun 10, 2009, at 2:19 PM, Mike Spreitzer wrote: If I have a computer with many cores and multiple disks, disjoint filesystems on those disks, and enough I/O bandwidth in the machine to keep the disks busy, can MySQL keep those disks busy if I have it working on different databases at the same time? If so, can one MySQL server process do that, or do I need multiple server processes? If one process is enough, presumably I have to say something clever in my.cnf to make it happen; what would that be? If it can be done with multiple server processes, can I get that from one MySQL installation (and if so, how) or do I need to somehow fool my package management system (RPM on RHEL) into doing multiple installations? Thanks, Mike Spreitzer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
Hi Scott You may use the script below to reload replication if you can ensure that the master db doesn't change during the dump operation. Otherwise you may set a lock on the master manually. Regards, Thomas #!/bin/bash # # replicate-reload # # This is free software. There is no warranty at all. # The program may melt your computer and kill your cat. # Use at your own risk. # # restart new replication of DBASE on localhost; dump from MASTER # # Note: No changes to DBASE may take place on the master during # the dump operation. See comments below. # # Set your values here: DBASE=adbtoreplicate MASTER=host.domain.tld MYUSER=useronlocalhost MYPWD=thisisagoodpassword # Set replication user and password REPLUSER=replicationuser REPLPWD=replicationuserpassword # End of user configuration SPACE=' ' TAB=$(echo -ne \t) MASTER_ALIAS=$(echo $MASTER | sed -e s/\\..*//) MASTER_POS=$(echo FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; | mysql -u $MYUSER -h $MASTER -p$MYPWD $DBASE \ | sed -e /^${MASTER_ALIAS}-bin/ !d) # # Beware: From this point on no changes on the master may be made # until the dump has finished. If this can't be enforced you # have to place a lock manually on the master and release it # once the dump is complete. # MASTER_FILE=$(echo $MASTER_POS | cut -s -d $TAB -f 1) MASTER_LOGPOS=$(echo $MASTER_POS | cut -s -d $TAB -f 2) #echo MASTER_POS=$MASTER_POS echo MASTER_FILE=$MASTER_FILE echo MASTER_LOGPOS=$MASTER_LOGPOS # Get the dump echo Dumping '$DBASE' from $MASTER # # User: set your own dump options here as needed mysqldump -u $MYUSER -h $MASTER -p$MYPWD \ --skip-opt \ --add-drop-table \ --max_allowed_packet=1M \ --character-sets-dir=/usr/share/mysql/charsets \ --skip-set-charset \ --extended-insert --lock-all-tables --quick \ --quote-names --master-data=2 $DBASE \ | sed -e /^SET / d ${DBASE}.sql # # Note: Changes on the master are allowed from here on # echo -e \nCHANGE MASTER TO MASTER_HOST='$MASTER', \ MASTER_USER='$REPLUSER', MASTER_PASSWORD='$REPLPWD', \ MASTER_LOG_FILE='$MASTER_FILE', MASTER_LOG_POS=${MASTER_LOGPOS}; \ ${DBASE}.sync.sql echo STOP SLAVE; | mysql -u $MYUSER -h localhost -p$MYPWD $DBASE # reload dumped database echo Reloading '${DBASE}' on localhost cat ${DBASE}.sql ${DBASE}.sync.sql | mysql \ -u $MYUSER -h localhost -p$MYPWD $DBASE echo Starting slave $(hostname) echo START SLAVE; | mysql \ -u $MYUSER -h localhost -p$MYPWD -E $DBASE sleep 2 echo SHOW SLAVE STATUS; | mysql \ -u $MYUSER -h localhost -p$MYPWD -E $DBASE rm -f ${DBASE}.sql ${DBASE}.sync.sql exit 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thu, 14 May 2009, Ilia KATZ wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia Hi It depends. You may convert the MAC address to a decimal integer and store it as a BIGINT. Use UNSIGNED as well; there are no negative numbers involved. This may gain some speed and saves storage space. The drawback I can see is that these numbers are not human readable, but you may convert back to HEX when retrieving data. And it may break when they start using larger MAC addresses eventually. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Johnny Withers schrieb: Well, I think an update after insert is the only way. Other than perpopulating another table with possibe crc values then usinga join: Select id from testtable Inner join crctable on testtable.id=crctable.id Where crctable.crcval='xxx' Just be sure to index the crcval column. From my understanding, a TRIGGER might do exactly what Thunder needs. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Ciao, Thomas Pundt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Extending stopwords list
Hi I have configured MySQL to ignore stopwords from file /etc/my.stopwords. While playing around with myisam_ftdump I found that my fulltext index contains about a dozen words which are so common that they have a negative weight. Would it be a good idea to include these words in the stopwords file? Will this improve results for users? Any insight is welcome. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I thin'k MySQL will be the 'Oracle Personal Edition'
José I. Merino schrieb: The main question is: Will Oracle permits a cheaper DB in his portfolio with almost the same reliability than his main and expensive DB? It already has, it's called Oracle Express Edition. Ciao, Thomas -- 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 problem
Konrad, AZZOPARDI Konrad schrieb: Hello people, I do not know if this the right listI am migrating a very basic application from an older mysql version 4.1.9-standard to a new mysql version 5.0.45 {RedHat default package}. I have migrated DB data from one to the other and all data seems to be there including the structureMy problem is that I run a query like this : SELECT x.application_name, y.role_name FROM application x, role y JOIN logical_app_role_link l ON x.application_id = l.application_id AND y.role_id = l.role_id WHERE l.logical_id = 15; It works for the old mysql version but for the new mysql version I receive the following error : ERROR 1054 (42S22): Unknown column 'x.application_id' in 'on clause' and I am sure that application_id exists in table application. read the upgrading instructions and pay special attention to http://dev.mysql.com/doc/refman/5.0/en/join.html, Join Processing Changes in MySQL 5.0.12. Best to avoid this issue is to not mix implicit and explicit joins, as Gerald pointed out. Ciao, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: success return from mysql_query() when error return was expected
On Wed, 8 Apr 2009, Pete Wilson wrote: Hi everyone -- I'm a MySQL noob. I have MySQL queries in my C code and I was surprised to find I'm getting a success return from: mysql_query(pmysql, select * from usrs where(usr=\illegal name\); In this table called usrs, usr is the primary key and the engine is myisam. I expected an error return from this query on illegal name, which is not in the table. An error is not returned until I call: prow = mysql_fetch_row(pmysqlres)); which returns NULL, which is great. If I run that same select from the command line, I see: mysql select * from usrs where (usr=illegal name); Empty set (0.00 sec) My question, finally: Is this response to my program call expected and normal for MySQL? I suppose it is, but I just want to make sure that the behavior is OK, that indeed the program call to mysql_query(select ...) must always in these circumstances return success. Thanks! -- Pete Wilson Hi Pete I suspect that illegal name means that this value is not existing in the table. Right? But this query is perfectly correct and should not throw an error. It's like select * from table where 1 = 0; which returns nothing, the correct answer. Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Unprintable characters in regexp
Hi How can I specify 'unprintable' characters is a MySQL regexp ? Query is (example only): SELECT something FROM table WHERE column REGEXP 'Ã\\xA0'; I'm looking for an equivalent of the search part of a sed expression like this: s/Ã\xA0/à/g which means I want to include a character with code hex A0 in the regexp. According to the manual this sytax is not supported for regexp. Any other way to do this? Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: generic remote command/script for monitoring MySQL instance health
On Mon, 9 Mar 2009, Sven wrote: Hi folks I am searching for a generic command to monitor that MySQL instance is up and running. I don't have any know-how about the schema of the DB. kind regards Sven Aluoor Hi What about 'mysqladmin ping' ? Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Finding replicated database
Hi I'm stuck with the following problem: SLAVE has a bunch of databases of which one or two are replicated from MASTER. I'm writing a shell script to be run by an ordinary user on SLAVE. This script should know which of the databases on SLAVE are replicated. Configuration: MASTER has --binlog-do-db rules. These determine what goes to binlog and will be replicated by SLAVE. SLAVE has no --replicate-do-db rules. On SLAVE I can find who the master is (SHOW SLAVE STATUS; will tell) but I can see no way to find what databases are logged by MASTER. User on SLAVE has no access to the replication user password on SLAVE and has no access to MASTER (otherwise mysql -h MASTER -e SHOW MASTER STATUS, would do the trick). Any other way to make the SLAVE tell me what is's replicating? TIA Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL_NO_CACHE
On Wed, 4 Mar 2009, Morten wrote: Hi, I was hoping that using SQL_NO_CACHE would help me bypass the query cache, but judging from the below it doesn't. What can I do to avoid the query cache? Thanks. Morten mysql select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (7.22 sec) mysql select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.45 sec) mysql select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.45 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.43 sec) Hi SQL_NO_CACHE means that the query result is not cached. It does not mean that the cache is not used to answer the query. You may use RESET QUERY CACHE to remove all queries from the cache and then your next query should be slow again. Same effect if you change the table, because this makes all cached queries invalid. But why do you want to do this? Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Consolidation suggestions - some LAMP servers
Hi Götz, On Dienstag, 9. Dezember 2008, Götz Reinicke wrote: | My question is, what do you think about using e.g. one server as | webserver, the other as mysql server ... so the database traffic will go | across the network. So that I have only one service running on one | server and not like now the webserver bundled with the database. | | The most obvious advantage from my point of view: I only have one mysql | server to manage and not three or four. (Likewise the webserver) | | The disadvantage would be the delay accross the network I think. As Steve already pointed out, network performace shouldn't be an issue if you have GigE connectivity; we also do it now for years without a problem. The biggest issue I see is that you probably create another SPOF here. What if the webserver is up running but the database server dies for some reason? I'd strongly suggest to set up database replication including a failover solution (e.g. heartbeat). You could in general do something similar with your webserver and other systems. Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uptimize: join table on if()
On Dienstag, 9. Dezember 2008, Mogens Melander wrote: | I can't figure out why the UNION solution is missing 4 rows. I'll include | the 3 complete statements, so maybe someone smarter than me can figure out | why there's a difference in the result. Without having studied your query, my guess would be: double rows. UNION eliminates those; if you need them, use UNION ALL. Just a guess though... Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing query question, EXPLAIN SELECT ...
Hi, I am pretty new in optimizing tables with index and may need some help. This is my query: EXPLAIN SELECT timestamp FROM Meting_INT_COPY WHERE blockid = '200811252000' ORDER BY timestamp DESC LIMIT 1 If I have an index(blockid), EXPLAIN will return the following information: type possible_keys key rows Extra ref index_blockid index_blockid 2638 Using where; Using filesort If I add an index(blockid,timestamp) EXPLAIN will display the following: type possible_keys key rows Extra ref index_blockid,index_blockid_timestampindex_blockid_timestamp8248 Using where; Using index The index(blockid,timestamp) avoid the filesort + returns the result from index ! (Using where; Using index) But why for the index(blockid) 2638 rows are returned and for a more specific index(blockid,timestamp) 8248 rows are returned ? Thank you for any answer !
Re: Error: Got error 139 from storage engine
On Tue, 4 Nov 2008, Jesse wrote: prompt perror 139 MySQL error code 139: Too big row Seems you are exceeding some limit. Where did you run the perror command from? I tried to run that in the MySQL command line utility and got an error? At any rate, the field in question is a Text field. My understanding is that the limit of a Text field is about 64K, right? I guess it's possible that limit was exceeded, but not very likely. I'll have to do some more checking. Thanks, Jesse Hi Jesse I was running the 'perror' command from the bash command line of a Linux system. A column of type text will allow a maximum of 65'535 characters being stored, but this could be less when a multibyte character set is used. What storage engine type are you using? I found some issues with InnoDB regarding this error. Please check the .err log of mysqld as well. Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: Got error 139 from storage engine
On Mon, 3 Nov 2008, Jesse wrote: I have an ASP.net web application running on a WS03 server using MySQL 5.0.67-community-nt-log. I have a form that allows the customer to use a visual HTML editor to input text that will appear on a web page. So, the text contains HTML tags. When it tries to save the text to the table in the database, I get the error, #HY000Got error 139 from storage engine. Does anyone know what this is and how to fix it? prompt perror 139 MySQL error code 139: Too big row Seems you are exceeding some limit. Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combining Multiple Tables
On Friday, 26. September 2008, Tim Carty wrote: | I would like to combine multiple tables into a single table. I can create a | UNION between tables with similar columns cols A, B, and C from table_1 | with cols A, B, and C from Table_2. What I can¹t figure out is how to add | additionals columns (D, E, F) from table_2 if the columns don¹t exist in | Table_1. select a, b, c, null as d, null as e, null as f from table_1 union select a, b, c, d, e, f from table_2 should do it. Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Statement
Hi, [EMAIL PROTECTED] schrieb: Hi, I have 2 tables as follows: table 1 contains code,order_qty table 2 contains code,stock_qty table 1: code1, 10 code2, 2 code3, 5 table 2: code1, 3 code3, 5 code1, 4 code3, 2 I need to see the following result: code | order_qty| stock_qty code1 | 10 | 7 code2 | 2| 0 The condition is : order_qty sum(stock_qty) and note that if code is not found in table2, stock_qty is 0. Can this be achieved with a single select query? or suggest the best option. mysql create table table1 ( code int, order_qty int); Query OK, 0 rows affected (0.01 sec) mysql create table table2 ( code int, stock_qty int); Query OK, 0 rows affected (0.23 sec) mysql insert into table1 values (1,10),(2,2),(3,5); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql insert into table2 values (1,3),(3,5),(1,4),(3,2); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql select table1.code,order_qty,coalesce(sum(stock_qty),0) from table1 left join table2 using(code) group by table1.code having order_qty coalesce(sum(stock_qty),0); +--+---++ | code | order_qty | coalesce(sum(stock_qty),0) | +--+---++ |1 |10 | 7 | |2 | 2 | 0 | +--+---++ 2 rows in set (0.02 sec) mysql Ciao, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
glabal and session variables
hi list I don't understand what happen in mysql 4.1.22 ! ! ! ! ! (the same in version 5.0) -bash-3.00$ mysql -e show variables like 'wait_timeout%' +---+---+ | Variable_name | Value | +---+---+ | wait_timeout | *30*| +---+---+ -bash-3.00$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55 to server version: 4.1.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show variables like 'wait_timeout%'; +---+---+ | Variable_name | Value | +---+---+ | wait_timeout | *28800 *| +---+---+ 1 row in set (0.00 sec) mysql show global variables like 'wait_timeout%'; +---+---+ | Variable_name | Value | +---+---+ | wait_timeout | *30*| +---+---+ 1 row in set (0.00 sec) mysql
rename database in 4.1
hi list, how can I rename a database with full innodb tables ? The version is 4.1 Thnaks all
update to last 5.0 GA
Hi all, I want to know the main differences between mysql 5.0.41 and MySQL 5.0.51a(last GA release) I read this page : http://dev.mysql.com/doc/refman/5.0/en/mysql-nutshell.html but there is nothing about the developments made by such update... thnaks all...
Does PHP 4.4 work with mySQL 5.0?
Hi. I've got mySQL 4.1.22 installed from sources on Linux --- ./configure --prefix=/usr/local/mysql and PHP 4.4.2 installed from sources --- ./configure --prefix=/usr/local/php --with-mysql=/usr/local/mysql I want to upgrade mySQL from 4.1.22 to 5.0.5 by doing: 1) export DB 2) stop mySQL daemon 3) mv /usr/local/mysql /usr/local/mysq.4 4) install mySQL 5.0.5 (./configure --prefix=/usr/local/mysql) 5) start mySQL daemon 5) import DB Does PHP 4.4 work with mySQL 5.0 or must I upgrade mySQL libraries? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query_cache TimeToLive
Hi all, how mysql manage the query_cache TimeToLive (live) and how can I change it ? Thanks
How to log 'mytop' data
Hi. I've installed 'mytop' on my Linux server and would like to log the data provided in order to store it for future situations. Does anybody know how to perform it? Thank you very much, --Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to detect mytop long-time queries and kill them
Hi. I've been finding some queries by using 'mytop' which takes +200 seconds to be executed. It wastes tons of CPU resources on my server and would like to detect them automatically and kill them. Does anybody have experience with this issue? Thank you very much, --Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL high CPU Load
Hi Using MySQL 4.1.19 on Linux, my server's CPU load is very high. I want to cut it down but I don't know which parameters I might modify to do it. These are my server's data: back log50 basedir /usr/ bdb cache size 8,388,600 bdb home/var/lib/mysql/ bdb log buffer size 32,768 bdb logdir bdb max lock10,000 bdb shared data OFF bdb tmpdir /tmp/ binlog cache size 32,768 bulk insert buffer size 8,388,608 character set clientutf8 (Global value) latin1 character set connectionutf8 (Global value) latin1 character set database latin1 character set results utf8 (Global value) latin1 character set serverlatin1 character set systemutf8 character sets dir /usr/share/mysql/charsets/ collation connectionutf8_general_ci (Global value) latin1_swedish_ci collation database latin1_swedish_ci collation serverlatin1_swedish_ci concurrent insert ON connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit100 delayed insert timeout 300 delayed queue size 1,000 expire logs days0 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 4 ft query expansion limit20 ft stopword file(built-in) group concat max len1,024 have archiveNO have bdbYES have blackhole engine NO have compress YES have crypt YES have csvNO have example engine NO have geometry YES have innodb YES have isam YES have ndbcluster NO have opensslYES have query cacheYES have raid NO have rtree keys YES have symlinkYES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb fast shutdownON innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag0 innodb mirrored log groups 1 innodb open files 300 innodb table locks ON innodb thread concurrency 8 interactive timeout 28,800 join buffer size131,072 key buffer size 8,388,608 key cache age threshold 300 key cache block size1,024 key cache division limit100 language/usr/share/mysql/english/ large files support ON license GPL local infileON locked in memoryOFF log OFF log bin OFF log error log slave updates OFF log slow queriesON log update OFF log warnings1 long query time 5 low priority updatesOFF lower case file system OFF lower case table names 0 max allowed packet 8,387,584 max binlog cache size 4,294,967,295 max binlog size 1,073,741,824 max connect errors 10 max connections 250 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 4,294,967,295 max length for sort data1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max tmp tables 32 max user connections0 max write lock count4,294,967,295 myisam data pointer size4 myisam max extra sort file size 2,147,483,648 myisam max sort file size 2,147,483,647 myisam recover options OFF myisam repair threads 1 myisam sort buffer size 8,388,608 myisam stats method nulls_unequal net buffer length 16,384 net read timeout30 net retry count 10 net write timeout 60 new OFF old passwords ON open files limit1,260 pid file/var/run/mysqld/mysqld.pid port3,306 preload buffer size 32,768 prepared stmt count 0 protocol version10 query alloc block size 8,192 query cache limit 8,388,608 query cache min res unit4,096 query cache size16,777,216 query cache typeON query cache wlock invalidateOFF query prealloc size 8,192 range alloc block size 2,048 read buffer size131,072 read only OFF read rnd buffer size262,144 relay log purge ON relay log space limit 0 rpl recovery rank 0 secure auth OFF server id 0 skip external locking ON skip networking OFF skip show
InnoDB ANALYZE and locks
Hi all, just a simple question : Does the query ANALYZE position reads and/or writes locks ? I read these two pages but I didn't find the answer... http://www.mysql.com/news-and-events/newsletter/2003-04/a000155.html http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html Thanks all
MySQL Performance Analysis tools
Hi. Using MySQL on Linux, I'd like to analyze the performance and know how resources (memory, threads) are used during a period of time. Do you know any tool to carry it out? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SET GLOBAL don't work
Hi all, i have a mysql 4.1.21 and i want to change the wait_timeout variable without restarting mysql. I found that this variable can be changed by using SET GLOBAL wait_timeout=XXX mysql show variables like 'wait%'; +---+---+ | Variable_name | Value | +---+---+ | wait_timeout | 28800 | +---+---+ 1 row in set (0.00 sec) mysql SET GLOBAL wait_timeout=90; Query OK, 0 rows affected (0.00 sec) mysql show variables like 'wait%'; +---+---+ | Variable_name | Value | +---+---+ | wait_timeout | 28800 | +---+---+ 1 row in set (0.00 sec) Can you explain me why this operation don't work ??? ie: I useed the root account... Thanks Thomas Raso
How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Hi. Working on Linux, I've got installed MySQL 3.23.58, but I'm experiencing some problems. For instance, I suffer too many connections error but I cann't set 'max_connections' parameter to a value bigger than 250. I decided to migrate to MySQL 5.0, and I've got some questions: - I installed MySQL from sources on '/usr/local/mysql'. Is it as easy as installing it again on '/usr/local/mysql5'? - what must I back up to migrate data? '/usr/local/mysql/var' directory? - is there any tutorial/webpage to carry out this migration? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Hi Tiago. Thank you very much for your answer. I decided to migrate to MySQL 5.0, and I've got some questions: - I installed MySQL from sources on '/usr/local/mysql'. Is it as easy as installing it again on '/usr/local/mysql5'? Yep. Just change the '--prefix' com './configure' time. ok - what must I back up to migrate data? '/usr/local/mysql/var' directory? - is there any tutorial/webpage to carry out this migration? I would like to suggest that you do one dump on old database, stop the old and restore on newer database. Just copy the datafiles can work on small upgrades (mysql 3 - 4) but I don't know what is the problem of this, I prefer the basic: dump and restore. So you're suggesting to: - install MySQL 5 from sources on '/usr/local/mysql5' without starting it - dump data from MySQL 3 - stop MySQL 3 - start MySQL 5 - restore data in MySQL 5 - delete MySQL 3 is it right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Make sure you use the mysqldump from version 5 and not version 3. Also recheck all your application queries once you have restored the data the list of changes SQL syntax might haunt you. But can I use '/usr/local/mysql5/bin/mysqldump' to dump data of MySQL 3? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication and ibdata file size
Hi all, on a replication architecture, with the same server, the same Mysql version (4.1.21) and the same configuration, the same database. I have a difference between two ibdata file size innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend on the master : -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:36 ibdata6 -rw-rw1 mysqlmysql 22G Oct 30 11:40 ibdata7 on the slave -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata6 -rw-rw1 mysqlmysql 15G Oct 30 11:40 ibdata7 The difference is over 7Go !!! Is there anybody who has got any explanation about this ??? Thanks all
Re: replication and ibdata file size
Thanks a lot for this explanation, yeah we are doing many massive update and insert in our databases. -- Thomas Raso 2007/10/30, Augusto Bott [EMAIL PROTECTED]: One possible explanation (possibly not the only one): if you do a massive update on the master, that transaction would need to create many blocks of versioned data. If you roll that transaction back, those blocks will be freed to be reused, but the datafiles won't shrink. Since that transaction wasn't commited, it won't be written to the binary log, so it won't be executed and rolled back on the slave (that's only true when all tables involved on a transaction are transaction-safe tables). -- Augusto Bott On 10/30/07, Thomas Raso [EMAIL PROTECTED] wrote: Hi all, on a replication architecture, with the same server, the same Mysql version (4.1.21) and the same configuration, the same database. I have a difference between two ibdata file size innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend on the master : -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:36 ibdata6 -rw-rw1 mysqlmysql 22G Oct 30 11:40 ibdata7 on the slave -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata6 -rw-rw1 mysqlmysql 15G Oct 30 11:40 ibdata7 The difference is over 7Go !!! Is there anybody who has got any explanation about this ??? Thanks all
ibdata file size
hi all, i don't understand the size of the ibdata7 -rw-rw1 mysqlmysql2.0G Oct 29 15:18 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 29 15:08 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata6 -rw-rw1 mysqlmysql 22G Oct 29 15:18 ibdata7 the size of the databases is near 8Go. innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend The OS is Linux X 2.4.21-40.ELsmp #1 SMP Thu Feb 2 22:22:39 EST 2006 i686 i686 i386 GNU/Linux 4 x Intel(R) Xeon(R) CPU 5140 @ 2.33GHz with 4Go is anybody has got a documentation about this... Thanks all Thomas Raso
Re: ibdata file size
There is no way whitout stopping mysql ? for information it is a version 4.1 2007/10/29, Dan Nelson [EMAIL PROTECTED]: In the last episode (Oct 29), Thomas Raso said: i don't understand the size of the ibdata7 -rw-rw1 mysqlmysql2.0G Oct 29 15:18 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 29 15:08 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata6 -rw-rw1 mysqlmysql 22G Oct 29 15:18 ibdata7 the size of the databases is near 8Go. innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend The OS is Linux X 2.4.21-40.ELsmp #1 SMP Thu Feb 2 22:22:39 EST 2006 i686 i686 i386 GNU/Linux 4 x Intel(R) Xeon(R) CPU 5140 @ 2.33GHz with 4Go is anybody has got a documentation about this... It means you have (or had at one point in the past) 28GB worth of InnoDB tables created. If you know you have only 8GB in use and want to recover the space used by those ibdata files, you will need to back up all your tables, delete the ibdata files, and restore the tables. I recommend setting innodb_file_per_table=1 so each table gets its own tablespace file. That way, when you delete a table, the space is immediately returned back to the filesystem. http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html -- Dan Nelson [EMAIL PROTECTED]
Re: ibdata file size
ok thanks, this mysql has got a replication slave... On the slave the ibdata has got a size close to 15Go (ie the master's size is 22Go) have you got an idea about this ??? thanks 2007/10/29, Dan Nelson [EMAIL PROTECTED]: In the last episode (Oct 29), Thomas Raso said: 2007/10/29, Dan Nelson [EMAIL PROTECTED]: In the last episode (Oct 29), Thomas Raso said: i don't understand the size of the ibdata7 -rw-rw1 mysqlmysql2.0G Oct 29 15:18 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 29 15:08 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 29 15:17 ibdata6 -rw-rw1 mysqlmysql 22G Oct 29 15:18 ibdata7 the size of the databases is near 8Go. innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend is anybody has got a documentation about this... It means you have (or had at one point in the past) 28GB worth of InnoDB tables created. If you know you have only 8GB in use and want to recover the space used by those ibdata files, you will need to back up all your tables, delete the ibdata files, and restore the tables. I recommend setting innodb_file_per_table=1 so each table gets its own tablespace file. That way, when you delete a table, the space is immediately returned back to the filesystem. http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html There is no way whitout stopping mysql ? for information it is a version 4.1 Nope; the backup/delete/restart/restore procedure is the only way. -- Dan Nelson [EMAIL PROTECTED]
How to change long_query_time with mySql 3.23
Hi. I'm suffering a severe slowness of my server (mySQL 3.23), and want to detect Slow Queries. I installed mySQL on '/usr/local/mysql', and works ok. But if I insert this line into '/etc/my.cnf': - log-slow-queries = /usr/local/mysql/log/slow-queries.log long_query_time = 5 - it won't restart. If I comment the second line, it works ok. Does anybody know how to set the slow queries time? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to change long_query_time with mySql 3.23
I also tried with: --- set-variable=long_query_time=5 -- mySQL starts ok, but all queries within the file are: # Query_time: 0 Lock_time: 0 Rows_sent: 119 Rows_examined: 238 The aren't slow queries, are they? On 10/4/07, thomas Armstrong [EMAIL PROTECTED] wrote: Hi. I'm suffering a severe slowness of my server (mySQL 3.23), and want to detect Slow Queries. I installed mySQL on '/usr/local/mysql', and works ok. But if I insert this line into '/etc/my.cnf': - log-slow-queries = /usr/local/mysql/log/slow-queries.log long_query_time = 5 - it won't restart. If I comment the second line, it works ok. Does anybody know how to set the slow queries time? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to change long_query_time with mySql 3.23
It looks like mySQL is taking ok the long_query_time value by using set-variable=long_query_time=5 If I display mySQL parameters: -- long_query_time current value: 10 -- However, there are tons of lines like this one: -- # [EMAIL PROTECTED]: root[root] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 20 This is not a slow query, is it? On 10/4/07, Jørn Dahl-Stamnes [EMAIL PROTECTED] wrote: On Thursday 04 October 2007 14:06, thomas Armstrong wrote: Hi. I'm suffering a severe slowness of my server (mySQL 3.23), and want to detect Slow Queries. I installed mySQL on '/usr/local/mysql', and works ok. But if I insert this line into '/etc/my.cnf': - log-slow-queries = /usr/local/mysql/log/slow-queries.log long_query_time = 5 - it won't restart. If I comment the second line, it works ok. Try to remove the spaces in the two lines. If my memory is correct, I had the same problem some time ago and it went away when removing the spaces. -- 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]
How to match a UTF-8 field with acute vowels words in BOOLEAN MODE?
Hi. Using mySQL 4.1.22, I'd like to carry out an SQL query to find a string containing acute vowels. mytable: - item1: --- firstname: Antonio --- lastname: Fernández --- comments: he's from Spain My SQL query: -- SELECT id FROM mytable WHERE MATCH(firstname, lastname, comments) AGAINST ('+fernandez' IN BOOLEAN MODE) ORDER BY firstname, lastname If mytable is in latin1, it works fine. But if mytable is in utf8 (it's the case) it doesn't work. How can I change my SQL query to match 'Fernández' in UTF8 within the table? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to find foo within (foo)
Finally I decided to use: -- SELECT id FROM mytable WHERE MATCH(firstname, lastname, comments) AGAINST ('+johnie' IN BOOLEAN MODE) ORDER BY firstname, lastname It works (except with acute vowel words in UTF8). Thank you very much. On 9/20/07, Baron Schwartz [EMAIL PROTECTED] wrote: thomas Armstrong wrote: If you need something more complicated, such as only ignoring (, then you need to get more complicated. You might even need a regular expression. I'm to browse: http://dev.mysql.com/doc/refman/5.0/en/regexp.html http://www.wellho.net/regex/mysql.html You know, you don't have to do fancy regexp matching or even LIKE. There's really no advantage to using those over what looks like it'll be simpler in this case: INSTR(). SELECT INSTR('John (Johnie)', 'Johnie') AS John, INSTR('Peter', 'Johnie') AS Peter; +--+---+ | John | Peter | +--+---+ |7 | 0 | +--+---+ Just say WHERE INSTR(haystack, needle) 0 in your WHERE clause, and you're done. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to match a UTF-8 field with acute vowels words in BOOLEAN MODE?
My ft configuration in /etc/my.conf: -- ft_min_word_len=1 ft_stopword_file='' - On 9/21/07, thomas Armstrong [EMAIL PROTECTED] wrote: Hi. Using mySQL 4.1.22, I'd like to carry out an SQL query to find a string containing acute vowels. mytable: - item1: --- firstname: Antonio --- lastname: Fernández --- comments: he's from Spain My SQL query: -- SELECT id FROM mytable WHERE MATCH(firstname, lastname, comments) AGAINST ('+fernandez' IN BOOLEAN MODE) ORDER BY firstname, lastname If mytable is in latin1, it works fine. But if mytable is in utf8 (it's the case) it doesn't work. How can I change my SQL query to match 'Fernández' in UTF8 within the table? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to match a UTF-8 field with acute vowels words in BOOLEAN MODE?
Hi Edward. Thank you very much for your answer. I tried adding these two lines after DB connection (PHP code): --- mysql_query (SET NAMES utf8;); mysql_query (SET CHARACTER_SET utf8;); -- but it won't work :( The solution you provided could be right because it works, but I don't dare to duplicate all my content. On 9/21/07, Edward Kay [EMAIL PROTECTED] wrote: Hi. Using mySQL 4.1.22, I'd like to carry out an SQL query to find a string containing acute vowels. mytable: - item1: --- firstname: Antonio --- lastname: Fernández --- comments: he's from Spain My SQL query: -- SELECT id FROM mytable WHERE MATCH(firstname, lastname, comments) AGAINST ('+fernandez' IN BOOLEAN MODE) ORDER BY firstname, lastname If mytable is in latin1, it works fine. But if mytable is in utf8 (it's the case) it doesn't work. How can I change my SQL query to match 'Fernández' in UTF8 within the table? If the data in the UTF-8 table consists of UTF-8 characters then it should work fine. If it this is the case but it doesn't work, I suspect the issue is that whatever client you are using to send your query is using a different character set. (I got stuck on this a few months back). The solution for my app was to issue a SET NAMES 'utf8' call when I first connected to ensure everything was using UTF-8. See http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html. Looking at your application, I am guessing you are going to be getting the search terms from a user. This could be annoying for them if they want to find this record, but just provided 'fernandez' (i.e. no accent). I resolved this issue by adding extra columns in my DB containing 'standardised' versions of the 'real' data (e.g. firstname_std). Then, when processing a search, I 'standardise' the user input and match this with the standardised column. If you're interested, this is the PHP code I use to 'standardise': /** * Standardise a string. This converts accented characters to the non-accented equivalents * makes it lowercase and removes any characters that are not [a-z], [0-9] or @. * * These standardised strings are stored in the database alongside their 'normal' counterparts. * Searches are performed by standardising the query and comparing the standardised forms. This * enables us to match regardless of punctation, whitespace, accented chars etc. * * @param string Input string to standardise * @return string Standardised version of the input string */ public static function StandardiseString($strInput) { $strInput = Search::TranslateAccentedChars($strInput); $strInput = strtolower($strInput); return preg_replace('/[EMAIL PROTECTED]/', '', $strInput); } /** * Translate accented characters to their non-accented counterparts * * @param string Input string * @return string String with accented characters replaced */ public static function TranslateAccentedChars($strInput) { $strAccentedChars= ŠŒŽšœžŸ¥µÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýÿ; $strNonAccentedChars = SOZsozYYuAAACDNOOYsaaaconooyy; return strtr($strInput, $strAccentedChars, $strNonAccentedChars); } HTH, Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to find foo within (foo)
Thank you Michael for your answer. On 9/19/07, Michael Dykman [EMAIL PROTECTED] wrote: The whitespace counts... try LIKE '%johnie%' (or better '_johnie_' .. the underscorematches any single character). I want to match '(johnie)' and not 'johnies' or 'aljohnier', what it's the query does with '_'. If I include whitespaces, I match only the word 'johnie'. But I've got some texts like: * (johnie) * johnie-sullivan * johnie.sullivan and I want to find them. I suppose the right way is create another condition: firstname LIKE '%(johnie %' OR '% johnie)%' OR '%(johnie)%' OR '%.johnie %' ... Bear in mind that, with a LIKE variable as the first element in your string, this query will do a full table scan every time. If you get a lot of records in here, that's going to kill you. You're right, but I didn't find another better way. What do you think it's better? - michael On 9/19/07, thomas Armstrong [EMAIL PROTECTED] wrote: Hi. I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. I know I can create another condition within my query: firstname LIKE '(johnie)' OR firstname LIKE '(johnie%' OR firstname LIKE '%johnie)' but I also might consider other characters like ' - * Any suggestion? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to find foo within (foo)
Thank you Chris for your answer. On 9/19/07, Chris Sansom [EMAIL PROTECTED] wrote: Well I'm hardly the world's greatest expert, but I'm curious as to why you're always separating '%' from 'johnie' with a space, because that way it will only find Johnie if he has a space before or after him or both. As commented to Michael, I want to match 'johnie' and not 'johnies' or 'aljohnier'. Hmmm... and why the double parentheses? In fact, why any parentheses at all? You're right. I copiedpasted another more complicated query and didn't remove parentheses. This oughta do it: SELECT friends.id FROM friends WHERE friends.firstname LIKE '%johnie%' ORDER BY friends.firstname LIMIT 0, That should find 'johnie' or 'Johnie' with absolutely any characters before and/or after him. ... and if you want to simplify your queries as much as possible you don't need to specify the table every time unless ambiguities might arise (which they only will if there's more than one table involved), so try: SELECT id FROM friends WHERE firstname LIKE '%johnie%' ORDER BY firstname LIMIT 0, Yes, you're right, but they're more tables in my 'more-complicated' query and I forgot remove them. ... and unless you've really got more than friends that limit clause is redundant too. :-) Yes, you're also right :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to find foo within (foo)
If you need something more complicated, such as only ignoring (, then you need to get more complicated. You might even need a regular expression. I'm to browse: http://dev.mysql.com/doc/refman/5.0/en/regexp.html http://www.wellho.net/regex/mysql.html Thank you very much! Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: thomas Armstrong [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 19, 2007 1:35 PM To: mysql@lists.mysql.com Subject: Query to find foo within (foo) Hi. I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. I know I can create another condition within my query: firstname LIKE '(johnie)' OR firstname LIKE '(johnie%' OR firstname LIKE '%johnie)' but I also might consider other characters like ' - * Any suggestion? -- 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]
Query to find foo within (foo)
Hi. I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. I know I can create another condition within my query: firstname LIKE '(johnie)' OR firstname LIKE '(johnie%' OR firstname LIKE '%johnie)' but I also might consider other characters like ' - * Any suggestion? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replicate_Do_DB double entries
Hi, I have a slave (version 5.0.26) replicating from master (version 4.1.13). The slave's /etc/my.cnf contains the following (just once!): snip # what we should replicate replicate-do-db = bge replicate-do-db = blog replicate-do-db = lawlist replicate-do-db = library replicate-do-db = mandate replicate-do-db = mypal replicate-do-db = polyreg replicate-do-db = ssl replicate-do-db = timon replicate-do-db = vakw replicate-do-db = wikipolyreg /snip Replication is working properly but when I do mysql show slave status; on the slave I see: Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: bge,blog,lawlist,library,mandate,mypal, polyreg,ssl,timon,vakw,wikipolyreg,bge,blog,lawlist,library,mandate, mypal,polyreg,ssl,timon,vakw,wikipolyreg Replicate_Ignore_DB: All replicated databases appear twice. This seems odd to me. What is wrong? Thank you for any help. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unrecognized option `--long-query-time=5'
Hi. Using mySQL 3.23.58, I'm trying to log slow queries and I made: log-slow-queries = /usr/local/mysql/log/slow-queries.log long-query-time = 5 (into my '/etc/my.conf' file) However, I get this error message: unrecognized option `--long-query-time=5' Is this parameter supported in mySQL 3? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unrecognized option `--long-query-time=5'
I also tried with long_query_time = 5 but got the same error :( On 7/12/07, thomas Armstrong [EMAIL PROTECTED] wrote: Hi. Using mySQL 3.23.58, I'm trying to log slow queries and I made: log-slow-queries = /usr/local/mysql/log/slow-queries.log long-query-time = 5 (into my '/etc/my.conf' file) However, I get this error message: unrecognized option `--long-query-time=5' Is this parameter supported in mySQL 3? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Partial char key not used in conjuction with inequality comparison (MySQL5)
Hello list! I've noticed in MySQL 5.0 partial keys on character fields aren't always used. In 4.1 they were. They seem not to be used when using inequality comparison. I'm not sure whether this is a bug or intended (in the latter case I have to work around it to get the speed I got with 4.1 back into 5.0) You can try for yourselves: Setup: CREATE TABLE t (T varchar(16) NOT NULL,KEY (T(1))); INSERT INTO t SET T=test1; INSERT INTO t SET T=test2; INSERT INTO t SET T=test2; Test: EXPLAIN SELECT * FROM t WHERE T=x; Result: key T used EXPLAIN SELECT * FROM t WHERE T!=x; Result: key T _unused_ Adding a key on entire field T works, but that's wasting a lot of space because I only want to test whether a certain field is empty or not. Important note: MySQL4.1 *did* use key T in the second query! Can anybody concur whether this happens for them too? And if so, is this a bug? grtz, Thomas -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: monitoring SQL query response times
Is there a way to monitor SQL query response times? Mysqlperformanceblog has patches for higher granularity query log: http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/ You could always just wrap the query calls in between some time registration of your own. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Hi. Using mySQL 4.1.22 on Linux, I got this error message suddenly this morning (it worked ok yesterday): - [EMAIL PROTECTED] /usr/local/mysql/bin/mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) -- I created a '/root/root.sql' file: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xx'); And killed the process and restarted: [EMAIL PROTECTED] ~]# /usr/local/mysql/bin/mysqld_safe --init-file=/root/root.sql [1] 2494 [EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from /usr/local/mysql/var But I get the same error message :( In my '/usr/local/mysql/var/server.err' file there's no error message: -- 070220 07:03:40 mysqld started 070220 07:03:40 InnoDB: Started; log sequence number 0 3758734 /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.1.22' socket: '/tmp/mysql.sock' port: 3306 Source distribution --- What am I doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Hi. Using mySQL 4.1.22 on Linux, I got this error message suddenly this morning (it worked ok yesterday): - [EMAIL PROTECTED] /usr/local/mysql/bin/mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) -- I created a '/root/root.sql' file: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xx'); And killed the process and restarted: [EMAIL PROTECTED] ~]# /usr/local/mysql/bin/mysqld_safe --init-file=/root/root.sql [1] 2494 [EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from /usr/local/mysql/var But I get the same error message :( In my '/usr/local/mysql/var/server.err' file there's no error message: -- 070220 07:03:40 mysqld started 070220 07:03:40 InnoDB: Started; log sequence number 0 3758734 /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.1.22' socket: '/tmp/mysql.sock' port: 3306 Source distribution --- What am I doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Fixed: []# kill `cat /usr/local/mysql/var/server.pid` []# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables []# /usr/local/mysql/bin/mysql mysql update user set password = password('xxx') where user = 'root' and host='localhost'; On 2/20/07, thomas Armstrong [EMAIL PROTECTED] wrote: Hi. Using mySQL 4.1.22 on Linux, I got this error message suddenly this morning (it worked ok yesterday): - [EMAIL PROTECTED] /usr/local/mysql/bin/mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) -- I created a '/root/root.sql' file: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xx'); And killed the process and restarted: [EMAIL PROTECTED] ~]# /usr/local/mysql/bin/mysqld_safe --init-file=/root/root.sql [1] 2494 [EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from /usr/local/mysql/var But I get the same error message :( In my '/usr/local/mysql/var/server.err' file there's no error message: -- 070220 07:03:40 mysqld started 070220 07:03:40 InnoDB: Started; log sequence number 0 3758734 /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.1.22' socket: '/tmp/mysql.sock' port: 3306 Source distribution --- What am I doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple-table UPDATE unexpected result
Dear listmembers On mysql version 4.1.13 I execute a query of this type: UPDATE a LEFT JOIN b ON a.col = b.col SET a.x = a.x + b.y WHERE b.col IS NOT NULL; I expect that column a.x is updated for every match in the join but this is not the case. Table a is updated for the first match only as in this example: mysql use test; Database changed mysql create table atable ( a int, b int); Query OK, 0 rows affected (0.00 sec) mysql insert into atable values(1,10),(2,10),(3,10),(4,10); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 10 | |3 | 10 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) mysql create table btable (a int, b int); Query OK, 0 rows affected (0.01 sec) mysql insert into btable values(2,5),(3,6),(3,7); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from btable; +--+--+ | a| b| +--+--+ |2 |5 | |3 |6 | |3 |7 | +--+--+ 3 rows in set (0.00 sec) mysql update atable left join btable on atable.a = btable.a set atable.b = atable.b + btable.b where btable.a is not null; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 15 | |3 | 16 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) However, the result I would like to achieve is (manually edited for the purpose of explanation): mysql select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 15 | |3 | 23 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) i.e. row 3 of atable should be updated 2 times, adding 6 and 7, as there are 2 rows in btable where column a is = 3. How can I do this? Any help is apreciated. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
/usr/sbin/mysqld: Shutdown complete when doing mysqldump
Hi. Using mySQL v4.1.9 on Fedora Core 2, I'm suffering a shutdown everyday around 04:30. These are the contents of 'mysqld.log': /usr/sbin/mysqld: ready for connections. Version: '4.1.9-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 070201 4:29:40 [Note] /usr/sbin/mysqld: Normal shutdown 070201 4:29:43 [ERROR] /usr/sbin/mysqld: Forcing close of thread 25822 user: 'root' 070201 4:29:43 [ERROR] /usr/sbin/mysqld: Forcing close of thread 25808 user: 'root' 070201 04:30:39 mysqld started InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. InnoDB: Error in opening ./ibdata1 070201 4:30:42 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 070201 4:30:42 [ERROR] Can't init databases 070201 4:30:42 [ERROR] Aborting 070201 4:30:42 [Note] /usr/sbin/mysqld: Shutdown complete 070201 04:30:42 mysqld ended 070201 04:35:21 mysqld ended At 04:30, my server makes the daily backup, by executing this command: -- /usr/bin/mysqldump -h localhost -u root -pX --all-databases /home/backup/mysql_databases.sql --- Can this command damage the mySQL server? Can I improve this command? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
interesting date/time query issue
I have data that is broken into anything from 30 sec to 15 minute time series (with a DATETIME field). I need to transform all of this into 15 minute data. Does anyone know off the top of their head if there a way I could use GROUP BY to make this happen? Nothing I have tried thus far has worked but it seems as though all of the pieces are there, but there does not appear to be any way to do the comparison in a way that GROUP BY can use it. Otherwise I can write a script to select all of the data and loop over it but you can see why I want to do this in SQL. Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date v. DateTime index performance
If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Front
Cf. http://www.phpcenter.de/phorum/read.php?f=1i=50453t=50453 + http://www.heidisql.com/. cheers, Thomas t.j. loeffler eth zurich institute of geology CAB E 10.4, universitaetsstrasse 6 8092 zurich, switzerland www.erdw.ethz.ch +41 44 632 5696 phone 044 658 3311 Polyphone +41 44 632 1080 fax _ ___ -Original Message- From: PBS Usenet [mailto:[EMAIL PROTECTED] Sent: Thursday, November 09, 2006 9:08 PM To: mysql@lists.mysql.com Subject: MySQL Front Can anyone tell me what's wrong: http://www.mysqlfront.de/ It's my favorite toll now it's gone -- 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: Like inside in clause
On Thu, 14 Sep 2006, Ravi Kumar. wrote: Is there a way to specify wildcard inside values of an in clause. I want to be able to do this: select id, name from tableName where name in ('a%', 'b%', 'c%'); instead of doing select id, name from tableName where name like 'a%' or name like 'b%' or name like 'c%'; no. You have to use LIKE. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport csv file import problem
On Sun, 27 Aug 2006, Jim Seymour wrote: Hi, I download a csv file from Yahoo in this format: ABIAX 20.63 2006-08-3 ACEIX 8.78 2006-08-3 CIGAX 10.08 2006-08-3 FSCTX 22.25 2006-08-3 GGOAX 20.55 2006-08-3 HWLAX 23.3 2006-08-3 HWMAX 28.74 2006-08-3 MLEIX 96.37 2006-08-3 NBPBX 18.98 2006-08-3 PSVIX 32.43 2006-08-3 PTRAX 10.3 2006-08-3 RGACX 30.89 2006-08-3 ^DJI 11242.6 2006-08-3 ^IXIC 2092.34 2006-08-3 ^GSPC 1280.27 2006-08-3 My table for this data is in this format | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 | 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 | Is there a way to get mysqlimport to pull the data from specific column/row to insert into a specified field? Trying to find an easier way than typing all of the data into a text file for import. Thanks, Hi Jim, that needs some preprocessing, but 'sed' is your friend. You could use some shell script doing the work for you: #!/bin/sh BLANK=' ' TAB=' ' WHITESPACE=${BLANK}${TAB} DATE=$(cat mydatafile.csv | sed \ -e 3 !d \ -e s/^[$WHITESPACE]*// \ -e s/.*/''/) DATA=$(cat mydatafile.csv | sed \ -e /[A-Z]/ d \ -e /-/ d \ -e s/^[$WHITESPACE]*// \ -e s/.*/''/ | tr '\012' ',' | sed \ -e s/,*$//) echo INSERT INTO mytable VALUES(${DATE},$DATA); exit 0 # end of shell script The resulting queries can be piped into the mysql client. HTH, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locked Table Issue..
Hello all... And thanks ahead of time for the help... I'm having an issue with a locked table... I have about 27 tables in MySQL Db that i am working with with multiple MS Access front ends.. The problem is one of my tables is locked, but only the first 400 records, the rest is fine... Any idea on how to unlock it??? the other odd thing is that it is only locked in the Access front-end and not if i go in through MySQL Admin.. Many Thanks, Tom T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to delete /tmp/ibE3FYj2 files created by mySQL
Hi. There's a lot of '/tmp/ibE3FYj2' files inside my Linux server, created by mysqld. Does anybody know how to delete them after being used? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP script to simply manage tables
Hi. I've got three tables (users, books and news), and I would like to crete a web interface to manage their data (create items, modify items, delete items). Is there any PHP script to create it automatically (in the same way phpMyAdmin does, but with less functionalities)? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't open file: 'my_table.MYI': Could be the reason of a server reboot?
Hi. My Linux server has just restarted due to memory problems. Browsing mySQL logs, I've found out 5,000 lines like these in less than one hour: -- 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) Can this be the reason of the server reboot? For Linux fans, this is /var/logs/messages message: - Aug 8 1:40:02 www kernel: oom-killer: gfp_mask=0x1d2 Aug 8 1:41:43 www kernel: DMA per-cpu: Aug 8 1:42:33 www kernel: cpu 0 hot: low 2, high 6, batch 1 Aug 8 1:42:55 www kernel: cpu 0 cold: low 0, high 2, batch 1 -- Thank you very much in advance, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHARACTER SET COLLATE NULL error with mySQL 4.0.27
Hola. With mySQL 4.0.27 I'm trying to create this table -- CREATE TABLE `test`.`user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `email` TEXT CHARACTER SET COLLATE NULL , `firstname` TEXT CHARACTER SET COLLATE NOT NULL , ) -- but I get this 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 'CHARACTER SET COLLATE NULL, `firstname` TEXT CHARACTER SET -- Does anybody know which the right way is for this mySQL version? I'm using mySQL-Workbench to design the tables, and that's the code I get? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table 'mysql.host' doesn't exist
Hi. Working with mySQL 3.23.58 on Linux, I get this error when trying to start mysqld: 060716 03:07:21 mysqld started 060716 03:07:21 /usr/local/mysql/libexec/mysqld: Table 'mysql.host' doesn't exist 060716 03:07:21 mysqld ended - This Linux machine has been working ok for months, and it stopped suddenly. I don't've any idea about how to fix it, and I don't find any '/etc/my.conf' file into this machine. The install directory is '/usr/local/mysql'. Thank you very much in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table 'mysql.host' doesn't exist
I created this very-simple '/etc/my.conf': [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/var socket=/tmp/mysql.sock --- But it doesn't still work. '/usr/local/mysql/var' hosts the data, but within 'mysql' subdirectory, there are only two files: -- -rw-rw 1 mysql root0 may 30 2005 func.MYD -rw-rw 1 mysql root 8877 may 30 2005 tables_priv.frm Where are 'host.frm', 'host.MYD' and 'host.MYI'? On 7/16/06, thomas Armstrong [EMAIL PROTECTED] wrote: Hi. Working with mySQL 3.23.58 on Linux, I get this error when trying to start mysqld: 060716 03:07:21 mysqld started 060716 03:07:21 /usr/local/mysql/libexec/mysqld: Table 'mysql.host' doesn't exist 060716 03:07:21 mysqld ended - This Linux machine has been working ok for months, and it stopped suddenly. I don't've any idea about how to fix it, and I don't find any '/etc/my.conf' file into this machine. The install directory is '/usr/local/mysql'. Thank you very much in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
fre 2006-06-23 klockan 01:52 -0400 skrev Michael Stassen: Thomas Lundström wrote: Not sure what you're aming for here and how your data is structured but why not use a join and alias and fetch all info in one select and then solve what you need in your code? Something in the line of: select t2.col2 from_t2, t3.col2 from_t3 from table1 t1, table2 t2, table3 t3 where t1.id = t2.id and t1.id = t3.id and t1.id = 3 Maybe you can do something like that? That may be a start, but you have the wrong condition on t1.id, and you've left out any mention of t1.col1. Also, explicit JOINs are better than implicit (using commas) JOINs. Well of course you are correct, only probably you've missed the intention. The error is the late-in-the-evening substitution of t1.id = 3 where it should read t1.col1 = 3 of course. Yes, explicit JOINS are always better. These ones are though easier to fiddle with! ;-) Regards, Thomas Lundström -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] #0; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
Not sure what you're aming for here and how your data is structured but why not use a join and alias and fetch all info in one select and then solve what you need in your code? Something in the line of: select t2.col2 from_t2, t3.col2 from_t3 from table1 t1, table2 t2, table3 t3 where t1.id = t2.id and t1.id = t3.id and t1.id = 3 Maybe you can do something like that? Regards, Thomas L. ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804: Hi, I'm trying to build a query in using SQL instead of doing it in Perl. I am trying to do something like this : If ((select col1 from table1 where id = 1) == 3) Then Select col2 from table2 where table2.id = 1; Else Select col2 from table3 where table3.id = 1; In Perl I would probably do have to access the DB twice. Select col2 from table1 where if = 1; If (col2 == 3) { Select col2 from table2 where table2.id = 1; } else { Select col2 from table3 where table3.id = 1; } I've read the manual on subqueries but the example don't indicate how I can do a conditional test using a subquery? Am I on the right track or is there another way to do this? Thanks -- Ken e11804 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copy database to a file
I'm running MySQL server 5.0...??? mysql mysqldump --help; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump --help' at line 1 -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 20, 2006 11:32 AM To: mysql@lists.mysql.com Subject: RE: Copy database to a file The command is mysqldump Here's the man file on it: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html -Original Message- From: Karl Larsen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 20, 2006 3:09 PM To: mysql@lists.mysql.com Subject: Copy database to a file I have been reading the Reference and saw how to convert my database tables and stuff to the words that made them and puts it into a file. But now I need it I can't find it. If you know how please send along how or a page(s) in the Reference. Karl -- 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]
Time of entry updates?
Is there any way to find the time of the most recent update for a row?
Question on threads displayed in linux
Recently I noticed a changed behavior in how linux utilities (ps ans pstree) report mysql usage. Previously a ps output would list all active mysql threads, and a pstree would list the number of mysql threads currently running. Now I merely see a pstree output such as: ├─mysqld_safe───mysqld With no number of threads. Was there a change in mysql reporting behavior? This is for MySQL 4.1.19 on CentOS 4.3 and Red Hat ES 3 systems. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INT wants a float/double?
Hi, I am using Java to maintain a MySQL database. I have a table in the database created by: CREATE TABLE track_char ( + id INT UNSIGNED NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id), + name CHAR(40), + posX DOUBLE(10,1), + posY DOUBLE(10,1), + posZ DOUBLE(10,1), + rotX INT(10), + rotY INT(10), + rotZ INT(10)) Now, when I try to update the rotX, rotY, and rotZ fields, I have to put a double into it, ints don't work. But then, when I print out the database, it looks like this: 1 Tom 0.0 0.0 0.0 0 0 0 Which clearly indicates that it is storing the rot fields as ints. I am just wondering why I can't give it an int when I am updating the entry, even though what I am updating is an int?
RE: INT wants a float/double?
Right...the thing is that they are supposed to be INTs, but I can only successfully perform an update query if I use doubles for the fields...? -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 1:59 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] Now, when I try to update the rotX, rotY, and rotZ fields, I have to put a double into it, ints don't work. But then, when I print out the database, it looks like this: Which clearly indicates that it is storing the rot fields as ints. I am just wondering why I can't give it an int when I am updating the entry, even though what I am updating is an int? [/snip] It does not clearly indicate that the rot fields are stored as INTs, far from it. You cannot 'give' an INT to a DOUBLE as that it is incorrect. Anytime you have strongly typed variables you must use them as they are typed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
Here is what it gave me when I did describe: +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | name | char(40) | YES | | NULL|| | posX | double(10,1) | YES | | NULL|| | posY | double(10,1) | YES | | NULL|| | posZ | double(10,1) | YES | | NULL|| | rotX | int(10) | YES | | NULL|| | rotY | int(10) | YES | | NULL|| | rotZ | int(10) | YES | | NULL|| +---+--+--+-+-++ 8 rows in set (0.00 sec) Here is the update statement that works correctly: UPDATE track_char SET rotX = + Double.parseDouble(fields[i + 1]) + , rotY = + Double.parseDouble(fields[i + 2]) + , rotZ = + Double.parseDouble(fields[i + 3]) + WHERE name = 'Tom' where 'fields' is a string array of tokens that I have parsed from a message. I tried doing this: UPDATE track_char SET rotX = + Integer.parseInt(fields[i + 1]) + , rotY = + Int.parseInt(fields[i + 2]) + , rotZ = + Int.parseInt(fields[i + 3]) + WHERE name = 'Tom' but that wouldn't work. And yes, I have made sure that it isn't a problem with the parseInt() method by using hard-coded values...only hard-coded doubles work, hard-coded ints wouldn't. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 2:09 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] Right...the thing is that they are supposed to be INTs, but I can only successfully perform an update query if I use doubles for the fields...? [/snip] Do a describe on the table and make sure that they are INTs, then show us the update statement. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
It seems to work with INTs when I do it from the MySQL commandline. I guess something is weird with the JDBC connector or something... -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 2:26 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] Here is what it gave me when I did describe: +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | name | char(40) | YES | | NULL|| | posX | double(10,1) | YES | | NULL|| | posY | double(10,1) | YES | | NULL|| | posZ | double(10,1) | YES | | NULL|| | rotX | int(10) | YES | | NULL|| | rotY | int(10) | YES | | NULL|| | rotZ | int(10) | YES | | NULL|| +---+--+--+-+-++ 8 rows in set (0.00 sec) Here is the update statement that works correctly: UPDATE track_char SET rotX = + Double.parseDouble(fields[i + 1]) + , rotY = + Double.parseDouble(fields[i + 2]) + , rotZ = + Double.parseDouble(fields[i + 3]) + WHERE name = 'Tom' where 'fields' is a string array of tokens that I have parsed from a message. I tried doing this: UPDATE track_char SET rotX = + Integer.parseInt(fields[i + 1]) + , rotY = + Int.parseInt(fields[i + 2]) + , rotZ = + Int.parseInt(fields[i + 3]) + WHERE name = 'Tom' but that wouldn't work. And yes, I have made sure that it isn't a problem with the parseInt() method by using hard-coded values...only hard-coded doubles work, hard-coded ints wouldn't. [/snip] Do an update straight to the database and show us that without Java code. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
Ahhh! I should have thought of this beforehand. The query I was sending using doubles: UPDATE track_char SET rotX = 0.0, rotY = 0.0, rotZ = 0.0WHERE name = 'Tom' The query I was sending using ints: UPDATE track_char SET rotX = 0, rotY = 0, rotZ = 0WHERE name = 'Tom' There was a missing space between the value for rotZ and the 'WHERE'. I guess when I used a double, it just ignored that and accepted the syntax. Well, thanks for advising me to do the obvious, I now have this working exactly the way it should be! -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 2:38 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] It seems to work with INTs when I do it from the MySQL commandline. I guess something is weird with the JDBC connector or something... [/snip] May I suggest echoing your query out so that you can see what it is trying to insert? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]