Re: multiple domain names mapped to a single IP
Kaushal There are two ways to approach this: 1.) Turn off name resolution in MySQL and only do the ACL by IP. This is probably best as name resolution can slow the database and cause outright app failure if DNS fails for any reason. 2.) Make absolutely certain the names resolve correctly in DNS... Then see point 1 above. On 3/2/17 7:01 AM, Kaushal Shriyan wrote: Hi, Is there any pros and cons to multiple domain names mapped to a single IP work in MySQL client server setup like in case of httpd webserver there is a concept of VHost having multiple domain names mapped to a single IP? For example :- int-mysqldbserver1.example.com :- 192.168.0.11 int-mysqldbserver2.example.com :- 192.168.0.11 Will there be a issue when i point full qualified domain name in the application which uses mysql client program since both domain names are pointing to the same IP? Any help will be highly appreciable. Regards, Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
Mike, check the datadir (usually /var/lib/mysql). If it's empty, manually execute mysql_install_db. This will place an initial db in place and mysql will start from then on On 02/18/2015 03:11 PM, mike wrote: Cameron Mann cameron.mann at cybera.ca writes: Hi all, I've encountered a problem with MySQL 5.6.23 on CentOS 7.0 and would greatly appreciate any advice on what to do next. Synopsis: 1. Fresh install of CentOS 7.0 using minimal install ISO 2. yum update -y 3. rpm -i http://dev.mysql.com/get/mysql-community-release-el7- 5.noarch.rpm 4. yum install mysql-community-server -y 5. service mysqld start After installing mysql-community-server 5.6.23 on a fresh minimal install of CentOS 7.0 (running in VirtualBox 4.3.20) it will fail to start with the following error: Have you found any resolution for this as I'm having the exact same issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
The mysql_upgrade errors look like the mysql command line client can't be located by the script... some kind of path error induced by a security fix I'm thinking On 02/12/2015 12:37 PM, Cameron Mann wrote: Hi all, I've encountered a problem with MySQL 5.6.23 on CentOS 7.0 and would greatly appreciate any advice on what to do next. Synopsis: 1. Fresh install of CentOS 7.0 using minimal install ISO 2. yum update -y 3. rpm -i http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm 4. yum install mysql-community-server -y 5. service mysqld start After installing mysql-community-server 5.6.23 on a fresh minimal install of CentOS 7.0 (running in VirtualBox 4.3.20) it will fail to start with the following error: $ sudo service mysqld start Redirecting to /bin/systemctl start mysqld.service Job for mysqld.service failed. See 'systemctl status mysqld.service' and 'journalctl -xn' for details. $ systemctl status mysqld.service mysqld.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled) Active: activating (start-post) since Thu 2015-02-12 20:07:08 UTC; 1min 30s ago Process: 5643 ExecStart=/usr/bin/mysqld_safe (code=exited, status=0/SUCCESS) Process: 5632 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 5643 (code=exited, status=0/SUCCESS); : 5644 (mysql-systemd-s) CGroup: /system.slice/mysqld.service └─control ├─5644 /bin/bash /usr/bin/mysql-systemd-start post └─6115 sleep 1 Snippet from mysqld.log: 150212 19:47:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2015-02-12 19:47:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-02-12 19:47:08 1244 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) 2015-02-12 19:47:08 1244 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000) 2015-02-12 19:47:08 1244 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist 2015-02-12 19:47:08 1244 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 2015-02-12 19:47:08 1244 [Note] InnoDB: Using atomics to ref count buffer pool pages 2015-02-12 19:47:08 1244 [Note] InnoDB: The InnoDB memory heap is disabled 2015-02-12 19:47:08 1244 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-02-12 19:47:08 1244 [Note] InnoDB: Memory barrier is not used 2015-02-12 19:47:08 1244 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-02-12 19:47:08 1244 [Note] InnoDB: Using Linux native AIO 2015-02-12 19:47:08 1244 [Note] InnoDB: Using CPU crc32 instructions 2015-02-12 19:47:08 1244 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2015-02-12 19:47:08 1244 [Note] InnoDB: Completed initialization of buffer pool InnoDB: Error: pthread_create returned 13 150212 19:47:08 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended The suggested mysql_upgrade command gives the following output: $ mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed $ mysql_upgrade -uroot --password= Warning: Using a password on the command line interface can be insecure. Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed I've also observed the same behaviour on a CentOS 7.0 image (I believe from http://cloud.centos.org) running in OpenStack under the KVM hypervisor. I have not been able to test in a non-virtualized environment. I've attempted rebooting before and after installation of mysql-community-server to no effect. Previous versions of 5.6.x appear unaffected. Cameron Mann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
Well Mr Harald, I admit... It's not an out of the box behaviour put there by Oracle/MySQL. But it IS a behaviour introduced by distribution packagers and and it's only invoked the first time the db is start is attempted AND there is no basic db in place. mysql_install_db actually won't run without being forced on an installed system. Folks have come to expect it and it IS handy (when I tell MySQL to start, it just works, even if it's never been run before). Isn't that the entire idea behind all of the freedesktop junk... Things just work? Except when they don't. What slobs! On 02/12/2015 03:11 PM, Reindl Harald wrote: Am 13.02.2015 um 00:03 schrieb Bruce Ferrell: If the datadir is empty. you have to execute a different utility mysql_install_db. This will create a correct initial database. Jesus! I HATE systemd. The sysV init script handled this correctly. tell me *one* reason why it is the job of the init-system to check and fire up mysql_install_db at each start? guess what happens when that shell snippet makes a mistake and does that on a existing install frankly my mysql database was installed in 2003 on a windows machine and is the root for every mysql setup from then on Windows, OSX and Linux from MySQL 3.x to MariaDB 5.x up to MariaDB 10.x why would i want to do all the crap about set a sane default password for each and every install when tehre is a template setup? On 02/12/2015 02:06 PM, Cameron Mann wrote: Hi Robert, The value in my.cnf is datadir=/var/lib/mysql. The directory is initially empty, which I believe is normal; there shouldn't be anything in there until mysqld is started for the first time (at least that's the behaviour of 5.6.22, which works fine). After mysqld fails to start, an empty mysql directory is created. Cameron On Thu, Feb 12, 2015 at 2:35 PM, Bob Eby eby...@gmail.com wrote: Hi Cameron, I've seen a similar error running on windows 7. When you look in your datadir= specified in my.ini what is there exactly (hopefully not empty)? Do you have a mysql folder containing plugin.* files at this location? (say .MYI etc) It sounds like your data folders were either not copied to the correct place or not properly configured before starting the server. It's been a while, but I recall there being an install step to getting the correct basic database files into your datadir specified in my.ini -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
It worked before, when you manually had to execute the script... It worked when the script was auto invoked. Now it's silently busted, causing problems for people you think that's OK. Whatever. Moving on On 02/12/2015 03:45 PM, Reindl Harald wrote: Am 13.02.2015 um 00:35 schrieb Bruce Ferrell: Well Mr Harald, I admit... It's not an out of the box behaviour put there by Oracle/MySQL. But it IS a behaviour introduced by distribution packagers and and it's only invoked the first time the db is start is attempted AND there is no basic db in place. mysql_install_db actually won't run without being forced on an installed system. Folks have come to expect it and it IS handy (when I tell MySQL to start, it just works, even if it's never been run before). Isn't that the entire idea behind all of the freedesktop junk... Things just work? Except when they don't. if you setup a *server* you have to configure the *server* if you don't mind to run a simple command don't setup a *server* honestly see all the damage left and right caused by servers (mail, web, adatabase...) by trained monkeys i whish there would be a ton of more barriers to at least require *some thougts* besides fine, i have no clue but it seems to work somehow for whatever reason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
If the datadir is empty. you have to execute a different utility mysql_install_db. This will create a correct initial database. Jesus! I HATE systemd. The sysV init script handled this correctly. On 02/12/2015 02:06 PM, Cameron Mann wrote: Hi Robert, The value in my.cnf is datadir=/var/lib/mysql. The directory is initially empty, which I believe is normal; there shouldn't be anything in there until mysqld is started for the first time (at least that's the behaviour of 5.6.22, which works fine). After mysqld fails to start, an empty mysql directory is created. Cameron On Thu, Feb 12, 2015 at 2:35 PM, Bob Eby eby...@gmail.com wrote: Hi Cameron, I've seen a similar error running on windows 7. When you look in your datadir= specified in my.ini what is there exactly (hopefully not empty)? Do you have a mysql folder containing plugin.* files at this location? (say .MYI etc) It sounds like your data folders were either not copied to the correct place or not properly configured before starting the server. It's been a while, but I recall there being an install step to getting the correct basic database files into your datadir specified in my.ini. Good Luck, Robert Eby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
multilple mysql engines, one set of shared table spaces?
OK, put away the flamethrowers, I KNOW it's dumb. I've been asked for the upteenth time is this possible and if so under what conditions? So I pose the question to the community, is it? Under what conditions? Is it reliable or not? Are there authoritative references to support the answers? Inquiring minds want to know Thanks in advance Bruce Ferrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance Improvements with VIEW
On 07/30/2013 04:13 AM, Manivannan S. wrote: Hi, I've a table with 10 Million records in MySQL with INNODB engine. Using this table I am doing some calculations in STORED PROCEDURE and getting the results. In Stored Procedure I used the base table and trying to process all the records in the table. But it's taking more than 15 Minutes to execute the procedure. When executing the Procedure in the process list I am getting 3 states like 'Sending data', 'Sorting Result' and 'Sending data' again. Then I created one view by using the base table and updated the procedure by replacing that view in the place of a base table, it took only 4 minutes to execute the procedure with a view. When executing the Procedure in the process list I am getting 2 states like 'Sorting Result' and 'Sending data'. The first state of 'Sending data' is not happened with view, It's directly started with 'Sorting Result' state. When I'm referring some MySQL sites and other blogs, I have seen that VIEWS will never improve the performance. But here I see some improvements with a view. I would like to know how VIEW is improving the performance. Regards Manivannan S If you turn on your slow queries logs and activate log queries without indexes, I suspect you'll find your answer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Materialized Views
On 06/23/2013 11:18 AM, Rafael Valenzuela wrote: Hi All, I have a question about the materialized views , i remember in the DBA course my trainer said me. In Mysql doesn't exist this type views like Oracle. But My boss think the opposite. In the new version has this type of view? and the diferences the differences between views Mysql and Oracle? Thanks -- I think you're operating off of old information: http://dev.mysql.com/doc/refman/5.0/en/create-view.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Materialized Views
On 06/23/2013 11:18 AM, Rafael Valenzuela wrote: Hi All, I have a question about the materialized views , i remember in the DBA course my trainer said me. In Mysql doesn't exist this type views like Oracle. But My boss think the opposite. In the new version has this type of view? and the diferences the differences between views Mysql and Oracle? Thanks -- See also this: http://www.fromdual.com/mysql-materialized-views -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Instance tuning
I've long used mysqltuner.pl and have recently heard that it may not be the best tool for the job. what are others using? What experiences have you had with mysqltuner.pl Inquiring minds want to know -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.1: Views, queries, updates and performance issues
Hi all, I've got some semi-general questions on the topics in the title. What I'm looking for is more in the line of theory than query specifics. I am but a poor peasant boy. What I have is an application that makes heavy use of views. If I understand views correctly (and I may not), views are representations of queries themselves. The guy who wrote the app chose to do updates and joins against the views instead of against the underlying tables themselves. I've tuned to meet the gross memory requirements and mysqltuner.pl is saying that 45% of the joins are without indexes. With the slow query logs on and queries_without_indexes, I'm frequently seeing updates that often take more that 2 seconds to complete... Often MUCH longer (how does 157 seconds grab you?). So, with that background, what would you do next and is it possible this use of views, in this way is a significant contributor to the problem? Bruce Ferrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can't run MySQL under Cygwin : connect to server at 'localhost' failed (only when using password)
Bravo Basil! I've been looking at this all day and wondering myself. Now that I've said that.. And now for something terribly evil, is there an strace for cygwin? On 11/18/2011 03:30 PM, Basil Daoust wrote: Maybe this is the wrong place to ask, but why would you want to do this? Mysql has binaries for Windows, just use one of them? On Fri, Nov 18, 2011 at 4:16 PM, Franck Houssen f...@hotmail.com wrote: Hello mysql-list, I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows 7 using Cygwin), I can start and stop the server (only using mysqld.server - mysqladmin fails to connect). I can not connect to the server when I want to use a password (if I don't use any password the connection to the server succeeds). I need client AND server. I followed the on line mysql doc. Some comments about the installation / running process that I would underline :I do NOT use --without-server option in configure (I need the server)I use readline (ccmake configuration) and not libedit : could this be a problem ?I used mysql_install_db with --basedir, --datadir and --skip-name-resolve optionsI use a user dummy (that belongs to the mysql group) : I can not create the mysql user (Windows prevent me from creating a mysql user for a reason I can't figure out : I stopped fighting with Windows. As mentionned in the on-line doc, MySQL should work with any user : dummy is my user - dummy belongs to the group mysql - and the password is dummy)the root user doesn't exist in Cygwin (as far as I understand) : I can't use any mysqlamdin -u root ... as described in the on-line mysql doc When I use mysql or mysqladmin triggering a connection using a password, the connection fails (but succeeds if no password is used). May be someone could find a clue to solve this problem !... Could someone help me ? Thanks Franck Here after is a detailed description of the problem : $ mkgroup -l /etc/group (update groups Windows - Cygwin) $ mkpasswd -l /etc/passwd (update passwords Windows - Cygwin) $ more etc/group | grep mysql (check OK) mysql:S-1-5-21-4028741454-3406211479-1246761672-1004:1004: $ more passwd | grep dummy (check OK) dummy:unused:1000:513:dummy,U-dummy-PC\dummy,S-1-5-21-4028741454-3406211479-1246761672-1000:/home/dummy:/bin/bash $ chgrp -R None /tmp (give read / write access to all users) $ chgrp -R None /var (give read / write access to all users) $ ll (check OK) total 229 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 tmp drwxr-xr-x+ 1 dummy None 0 Aug 17 20:58 var $ chgrp -R mysql /usr/local/mysql (give read / write access to users of mysql group) $ chown -R dummy /usr/local/mysql (give read / write access to users of mysql group) $ ps (check : no server) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 3400 13400 3400 con 1000 11:10:39 /usr/bin/bash 675634006756 5400 con 1000 12:14:46 /usr/bin/ps $ ll /tmp (check : no mysql.sock) total 12 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. drwxrwxrwt+ 1 dummy None 0 Nov 9 18:40 .X11-unix drwxr-xr-x+ 1 dummy None 0 Aug 18 00:00 hsperfdata_dummy -rw-r--r-- 1 dummy None316 Oct 26 09:13 xkb_4Di75h -rw-r--r-- 1 dummy None316 Oct 27 00:08 xkb_4NrKCL -rw-r--r-- 1 dummy None316 Oct 27 00:09 xkb_ThcsMy -rw-r--r-- 1 dummy None316 Oct 27 00:10 xkb_shbOiY $ mysqld --user=dummy (launch server : OK) 14 12:15:54 InnoDB: The InnoDB memory heap is disabled 14 12:15:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins 14 12:15:54 InnoDB: Compressed tables use zlib 1.2.5 14 12:15:54 InnoDB: Initializing buffer pool, size = 128.0M 14 12:15:54 InnoDB: Completed initialization of buffer pool 14 12:15:54 InnoDB: highest supported file format is Barracuda. 14 12:15:54 InnoDB: Waiting for the background threads to start 14 12:15:55 InnoDB: 1.1.8 started; log sequence number 1595675 14 12:15:55 [Note] Event Scheduler: Loaded 0 events 14 12:15:55 [Note] mysqld: ready for connections. Version: '5.5.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution $ ll /tmp (check: mysql.sock is created and can be accessed - read / write permissions) total 13 drwxrwxrwt+ 1 dummy None 0 Nov 14 12:15 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. srwxrwxrwx 1 dummy None 0 Nov 14 12:15 mysql.sock $ ps (check: server launched OK) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 472034004720 6576 con 1000 12:15:53 /usr/local/mysql/bin/mysqld $ mysql -u dummy -p (when I hit return as a password = connection OK) Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql show tables; ERROR 1046
Re: 4 minute slow on select count(*) from table - myisam type
I'd suggest mysqltuner. You can get it by using: wget http://mysqltuner.pl See what suggestions that makes On 10/02/2011 06:44 AM, Joey L wrote: I have having issues with mysql db - I am doing a select count(*) from table -- and it take 3 to 4 min. My table has about 9,000,000 records in it. I have noticed issues on my web pages so that is why i did this test. I have about 4 gig of memory on the server. Is there anything I can do to fix the issue My my.cnf looks like this : # * Fine Tuning # key_buffer = 256M max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 32 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections= 100 table_cache= 1024 thread_concurrency = 20 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 512M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! general_log_file= /var/log/mysql/mysql.log general_log = 1 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin= /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI tinyca. # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
The meaning is: increase max_connections reduce wait_timeout -- 28800 is wait 8 hours before closing out dead connections same for interactive_timeout increase key_buffer_size ( 7.8G) increase join_buffer_size -- This keeps mysql from having to run to disk constantly for keys -- Key buffer size / total MyISAM indexes: 256.0M/7.8G -- You have a key buffer of 256M and 7.8G of keys join_buffer_size ( 128.0K, or always use indexes with joins) Joins performed without indexes: 23576 of 744k queries. -- You probably want to look at the slow query log. Generalize the queries and the do an explain on the query. I have seen instances where a query I thought was using an index wasn't and I had to re-write... with help from this list :-) Thanks gang! increase tmp_table_size ( 16M) increase max_heap_table_size ( 16M) -- When making adjustments, make tmp_table_size/max_heap_table_size equal increase table_cache ( 1k ) -- Table cache hit rate: 7% (1K open / 14K opened) -- Increase table_cache gradually to avoid file descriptor limits All of the aside, you need to let this run for at least 24 hours. I prefer 48 hours. The first line says mysql has only been running 9 hours. You can reset the timeouts interactivly by entering at the mysql prompt: set global wait_timeout=some value You can do the same for the interactive_timeout. Setting these values too low will cause long running queries to abort On 10/02/2011 07:02 PM, Joey L wrote: Variables to adjust: max_connections ( 100) wait_timeout ( 28800) interactive_timeout ( 28800) key_buffer_size ( 7.8G) join_buffer_size ( 128.0K, or always use indexes with joins) tmp_table_size ( 16M) max_heap_table_size ( 16M) table_cache ( 1024) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trying to change wait_timeout
That's the ticket! Thanks On 09/08/2011 06:55 AM, Andrew Moore wrote: Check that you're looking at the variable in the GLOBAL scope not the SESSION scope. SHOW GLOBAL VARIABLE ... Andy On Thu, Sep 8, 2011 at 11:34 AM, Bruce Ferrellbferr...@baywinds.orgwrote: On 09/08/2011 02:56 AM, Johan De Meersman wrote: - Original Message - From: Bruce Ferrellbferr...@baywinds.org** To: mysql@lists.mysql.com Sent: Thursday, 8 September, 2011 3:10:16 AM Subject: trying to change wait_timeout I've read the documentation on MySQL for version 5.1 and it says all I have to do is to place the following: wait_timeout=xxx under [mysqld] That, and restart the service, of course. You *did* think of restarting the service, I trust? :-p That being said, it is also a dynamic variable, so if you didn't restart, prefer not to restart *and* are certain your config file is correct; you can also do set global wait_timeout=xxx to have it take effect immediately for all new sessions. Yes, that means you'll have to disconnect/reconnect to see the change in your own session. Good question to ask. Yes, I did restart mysql. Both before and after show variables like 'wait_time%' returns 28800. Most confusing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=eroomy...@gmail.comhttp://lists.mysql.com/mysql?unsub=eroomy...@gmail.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: trying to change wait_timeout
On 09/08/2011 02:56 AM, Johan De Meersman wrote: - Original Message - From: Bruce Ferrellbferr...@baywinds.org To: mysql@lists.mysql.com Sent: Thursday, 8 September, 2011 3:10:16 AM Subject: trying to change wait_timeout I've read the documentation on MySQL for version 5.1 and it says all I have to do is to place the following: wait_timeout=xxx under [mysqld] That, and restart the service, of course. You *did* think of restarting the service, I trust? :-p That being said, it is also a dynamic variable, so if you didn't restart, prefer not to restart *and* are certain your config file is correct; you can also do set global wait_timeout=xxx to have it take effect immediately for all new sessions. Yes, that means you'll have to disconnect/reconnect to see the change in your own session. Good question to ask. Yes, I did restart mysql. Both before and after show variables like 'wait_time%' returns 28800. Most confusing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
trying to change wait_timeout
Hi all, I've read the documentation on MySQL for version 5.1 and it says all I have to do is to place the following: wait_timeout=xxx under [mysqld] did it and show variable like '%wait%' still show wait_timeout at 28800 as it does when I do a set global wait_timeout=10 What am I missing? Thanks in advance, Bruce Ferrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with Date in Where Clause
On 01/31/2011 12:18 PM, Jørn Dahl-Stamnes wrote:
On Monday 31 January 2011 21:12, Phillip Baker wrote:
Greetings All,
I am looking for a little help in setting a where clause.
I have a dateAdded field that is a DATETIME field.
I am looking to pull records from Midnight to midnight the previous day.
I thought just passing the date (without time) would get it but I keep
getting an empty record set.
So looking for something that works a bit better.
select * from your_table where convert(dateAdded, date)='2011-01-31';
not so good, but it works:
select * from your_table where dateAdded like '2011-01-31%';
OR
select * from your_table where dateAdded between '2011-01-30%' and
'2011-01-31%';
better:
select * from your_table where DATE_SUB('2011-01-31', INTERVAL 1 DAY);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Rewrite SQL to stop table scan
How would you rewrite the following SQL so that is doesn't do a full table scan. It does in fact do a full scan in spite of the time clause. It's been making me nuts for months. select count(*) as count from alerts where (unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) ) and devid = '244'; Thanks in advance, Bruce P.S. I've tried it this way: select count(*) as count from alerts where ((unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) )) and devid = '244'; and explain always says this: +--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-++--+---+--+-+--+-+-+ | 1 | SIMPLE | alerts | ALL | NULL | NULL | NULL |NULL | 2041284 | Using where | ++-++--+---+--+-+--+-+-+ And it's structured this way: | Field| Type| Null | Key | Default | Extra | id| varchar(60)| NO | MUL | | | stamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Rewrite SQL to stop table scan
On 01/14/2011 08:19 AM, Steve Meyers wrote: On 1/14/11 3:52 AM, Bruce Ferrell wrote: select count(*) as count from alerts where (unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) ) and devid = '244'; Bruce - The problem is that the index is useless, because you're running a function on the timestamp. What you want is this: SELECT COUNT(*) AS num FROM alerts WHERE stamp DATE_SUB(NOW(), interval 300 second) AND devid=244; With this query, MySQL will run DATE_SUB() once, and then use the index on stamp (which I assume you have) to narrow down the result set. Steve That did it! Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Mysql tuner is a very useful tool to pull metrics http://blog.mysqltuner.com/ On 09/21/2010 05:48 AM, Jangita wrote: I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Cygwin and DBD::mysql
it seem the Cygwin Perl can't see a client libraries needed to build
DBD::Mysql. Innovative lad that I am I figured I'd just compile my own
from 5.1.44 (current download). Nice thought.
What happens is the build fails as follows:
./configure --prefix=/usr/local/mysql --without-server
[ much configurage ]
make
[ much makage ]
vi.c: In function ‘get_alias_text’:
vi.c:918: error: expected declaration specifiers before ‘__weak_reference’
vi.c:923: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before
‘{’ token
vi.c:953: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before
‘{’ token
vi.c:998: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before
‘{’ token
vi.c:1054: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’
before ‘{’ token
vi.c:1103: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’
before ‘{’ token
vi.c:918: error: parameter name omitted
vi.c:1124: error: expected ‘{’ at end of input
make[2]: *** [vi.o] Error 1
make[2]: Leaving directory
`/usr/local/src/mysql-5.1.44/cmd-line-utils/libedit'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/usr/local/src/mysql-5.1.44/cmd-line-utils'
make: *** [all-recursive] Error 1
Any ideas on how I can get the libraries and headers to build DBD::mysql?
Thanks in advance
Bruce Ferrell
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Req. suitable .cnf file for Server used by 2000 users daily
this error indicates either root doesn't have sufficient privilege or the password entered was bad. try running it this way: ./mysqltuner.pl --user root --pass password Jeetendra Ranjan wrote: Hi, I run the mysqltuner at my server as below and i got error like below. [r...@127 /]# ./mysqltuner.pl MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [!!] Attempted to use login credentials, but they were invalid. On some other server this script is running absolutely fine without any change in mysqltuner.pl. Please guide me how can i run this script Thanks Regards Jeetendra Ranjan - Original Message - From: Bruce Ferrell bferr...@baywinds.org To: jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Thursday, October 15, 2009 12:20 PM Subject: Re: Req. suitable .cnf file for Server used by 2000 users daily Have a look at mysqltuner. It reads the stats from a running mysql instances and makes suggestions for what can be changed http://blog.mysqltuner.com/ Gavin Towey wrote: Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Req. suitable .cnf file for Server used by 2000 users daily
Have a look at mysqltuner. It reads the stats from a running mysql instances and makes suggestions for what can be changed http://blog.mysqltuner.com/ Gavin Towey wrote: Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Odd select question
I seem to recall a SQL select syntax along these lines: SELECT col1, col2 WHERE col1 IN (set) Is this or similar syntax in MySQL or is my dotage coming upon me Thanks in advance, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select Into OUTFILE problem
Thanks all who replied. After I posted I kept looking and found it... Also had folks point it out to me. Your suggestion is what I ended up doing. Bruce Gavin Towey wrote: Hi Bruce, SELECT … INTO OUTFILE always creates the file local to the database server. If you want to dump results where your perl script is running you’ll have to use another method such as receiving the results of the query normally and writing the file in the perl script. Regards, Gavin Towey I have a bit of perl code that ends with an error: $sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC; $sth = $dbh-prepare($sql); $rv = $sth-execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select Into OUTFILE problem
I have a bit of perl code that ends with an error: $sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC; $sth = $dbh-prepare($sql); $rv = $sth-execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Where to get Source Distribution of MySQL Server 5.0 Standard for FreeBSD?
Daniel Kasak wrote: VeeJay wrote: Hi Where one can find Source Distribution of MySQL Server 5.0 Standard for FreeBSD? Not on the website, that's for sure. Have you tried the usual warez sites, p2p networks, etc? Actually the source tarball IS on the mysql download site. -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP DATABASE weirdness
I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP DATABASE weirdness
I get this error: /usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined symbol: zlibCompileFlags Logan, David (SST - Adelaide) wrote: Hi Bruce, Do you have any messages in the MySQL log? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:42 AM To: mysql@lists.mysql.com Subject: DROP DATABASE weirdness I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP DATABASE weirdness
Yes, built with SSL support the build seemed to go OK and sometimes it works. I'm building on SuSE 9.2 using the src.rpm and the included spec file Logan, David (SST - Adelaide) wrote: Hi Bruce, Sounds like you are missing a library or two on the system. Did you compile the server yourself? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:52 AM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: DROP DATABASE weirdness I get this error: /usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined symbol: zlibCompileFlags Logan, David (SST - Adelaide) wrote: Hi Bruce, Do you have any messages in the MySQL log? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:42 AM To: mysql@lists.mysql.com Subject: DROP DATABASE weirdness I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP DATABASE weirdness
I think I found it. The system has zlib 1.1.4 the sources have zlib 1.2.3. Trying a static build now. Anyone wanna bet? :) Bruce Ferrell wrote: Yes, built with SSL support the build seemed to go OK and sometimes it works. I'm building on SuSE 9.2 using the src.rpm and the included spec file Logan, David (SST - Adelaide) wrote: Hi Bruce, Sounds like you are missing a library or two on the system. Did you compile the server yourself? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ **** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia inve nt --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:52 AM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: DROP DATABASE weirdness I get this error: /usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined symbol: zlibCompileFlags Logan, David (SST - Adelaide) wrote: Hi Bruce, Do you have any messages in the MySQL log? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ **** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia inve nt --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:42 AM To: mysql@lists.mysql.com Subject: DROP DATABASE weirdness I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP DATABASE weirdness
Nope, That didn't fix it. I'd uses MySQl provided rpms but they don't seem to have them got glib23 anymore. Suggestions? Bruce Ferrell wrote: I think I found it. The system has zlib 1.1.4 the sources have zlib 1.2.3. Trying a static build now. Anyone wanna bet? :) Bruce Ferrell wrote: Yes, built with SSL support the build seemed to go OK and sometimes it works. I'm building on SuSE 9.2 using the src.rpm and the included spec file Logan, David (SST - Adelaide) wrote: Hi Bruce, Sounds like you are missing a library or two on the system. Did you compile the server yourself? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ **** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia inve nt --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:52 AM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: DROP DATABASE weirdness I get this error: /usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined symbol: zlibCompileFlags Logan, David (SST - Adelaide) wrote: Hi Bruce, Do you have any messages in the MySQL log? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ **** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia inve nt --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:42 AM To: mysql@lists.mysql.com Subject: DROP DATABASE weirdness I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP DATABASE weirdness
Yeah, that is the bug exactly. I can't even get 5.0.27 to start it comes up with the exact same error. Maybe if I get rid of the SSL option but this sucks! Thanks for the help Logan, David (SST - Adelaide) wrote: Hi Bruce, Have a squiz here http://bugs.mysql.com/bug.php?id=16586 sounds suspiciously like your issue. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 11:17 AM To: mysql@lists.mysql.com Cc: Logan, David (SST - Adelaide) Subject: Re: DROP DATABASE weirdness Nope, That didn't fix it. I'd uses MySQl provided rpms but they don't seem to have them got glib23 anymore. Suggestions? Bruce Ferrell wrote: I think I found it. The system has zlib 1.1.4 the sources have zlib 1.2.3. Trying a static build now. Anyone wanna bet? :) Bruce Ferrell wrote: Yes, built with SSL support the build seemed to go OK and sometimes it works. I'm building on SuSE 9.2 using the src.rpm and the included spec file Logan, David (SST - Adelaide) wrote: Hi Bruce, Sounds like you are missing a library or two on the system. Did you compile the server yourself? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ **** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia inve nt --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:52 AM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: DROP DATABASE weirdness I get this error: /usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined symbol: zlibCompileFlags Logan, David (SST - Adelaide) wrote: Hi Bruce, Do you have any messages in the MySQL log? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ **** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia inve nt --- -Original Message- From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 November 2006 9:42 AM To: mysql@lists.mysql.com Subject: DROP DATABASE weirdness I'm running MySQL 5.0.24a and I have a database I need to drop. When I issue the command DROP DATABASE webdb the client seems to just hang. I see the command hit the server in the full log but the database never drops. Anyone have any suggestions? -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing Text File Into mySQL
I did a little shell script to do it. the key was the shell variable IFS: Normally IFS= to make it work right I set it as follows: IFS= Yes, thats a newline between the quotes John Kopanas wrote: I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Thanks for your help :-). -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting 5 to run
look in /var/lib/mysql for the error file. make sure /var/lib/mysql is owned by mysql Karl Larsen wrote: I am trying to get mysql 5 to run on my Fedora Core 4 linux. I installed both server and client RPM files. The info is good and using that I am trying to do the post-install. I ran mysql_install_db as a user and it failed. Then I tried as root and it seemed to work. But one thing it says to do I cannot find. It says: To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system I have no idea where support-files are or where the right place is. When I try to start mysqld_safe it errors out with this: [EMAIL PROTECTED] mysql]# mysqld_safe [1] 6501 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 060820 14:27:48 mysqld ended So I can set up the DB but not start mysqld. Does anyone have an idea what I am doing wrong? I'm just following the info instructions. Karl Larsen -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How come this update does not work??
Carlos Vasquez wrote: How come this doesn't work? Wp_photos.photo = IMG_1234.JPG Pixelpost_pixelpost.headline = /this/path/to/directory/IMG_1234.JPG So I need to just match the latter-bit of the file. update pixelpost_pixelpost,wp_posts,wp_photos set pixelpost_pixelpost.headline=wp_posts.post_title,pixelpost_pixelpost.datetime=wp_posts.post_date_gmt,pixelpost_pixelpost.body=wp_photos.caption where wp_photos.photo like '%pixelpost_pixelpost.headline%' and wp_photos.post_ID=wp_posts.ID; I get zero rows returned, 0 updated, etc. Any ideas would be appriciated? thanks in advanced I'd say the where clause isn't being matched. Try a select with the same where clause and see what you get back -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help starting mysql
Connie, The port you have specified (1020) requires root permissions as does any port 1024 and below. The normal port is 3306. I'm suspecting that your mysql server installation is configured to run as mysql or some user other than root. Change the port to something above 1024 and you should be OK. Bruce Ferrell Logg, Connie A. wrote: I have installed the following rpm's for mysql on my node [EMAIL PROTECTED] mysql]# rpm -qa | grep -i mysql MySQL-client-standard-5.0.15-0.rhel4 MySQL-server-standard-5.0.15-0.rhel4 MySQL-shared-standard-5.0.15-0.rhel4 MySQL-devel-standard-5.0.15-0.rhel4 The node is: [EMAIL PROTECTED] mysql]# uname -a Linux snv1.xxx 2.6.13Fast100 #5 SMP Thu Oct 20 13:42:04 PDT 2005 x86_64 x86_64 x86_64 GNU/Linux And is running red hat version [EMAIL PROTECTED] mysql]# more /etc/redhat-release Red Hat Enterprise Linux WS release 4 (Nahant Update 2) Have I installed the correct rpm? And can you tell me what priv I do not have as root that gives the following message when I try to start mysqld_safe? 051031 16:06:20 mysqld started 051031 16:06:20 InnoDB: Started; log sequence number 0 43655 051031 16:06:20 [ERROR] Can't start server: Bind on TCP/IP port: Permission denied 051031 16:06:20 [ERROR] Do you already have another mysqld server running on port: 1020 ? 051031 16:06:20 [ERROR] Aborting 051031 16:06:20 InnoDB: Starting shutdown... 051031 16:06:22 InnoDB: Shutdown completed; log sequence number 0 43655 051031 16:06:22 [Note] /usr/sbin/mysqld: Shutdown complete 051031 16:06:22 mysqld ended I am not running another mysql server on this machine, and nothing is bound to port 1020. Thanks, Connie Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879 Happiness is found along the way, not at the end of the road, and 'IF' is the middle word in life. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how do I see warnings from bulk loads?
Hi All, I'm bulkloading my instance and I'm getting a return message at the end of the load like this: mysql \. vm_load.txt Query OK, 164 rows affected, 113 warnings (0.01 sec) Records: 164 Duplicates: 0 Warnings: 113 When I load the data line by line, I get no warnings. How can I see what is going wrong? Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how do I see warnings from bulk loads?
Paul DuBois wrote: At 10:11 -0700 10/8/05, Bruce Ferrell wrote: Hi All, I'm bulkloading my instance and I'm getting a return message at the end of the load like this: mysql \. vm_load.txt Query OK, 164 rows affected, 113 warnings (0.01 sec) Records: 164 Duplicates: 0 Warnings: 113 When I load the data line by line, I get no warnings. How can I see what is going wrong? SHOW WARNINGS immediately after your LOAD DATA statement. Requires 4.1 or higher. http://dev.mysql.com/doc/mysql/en/show-warnings.html Thanks that got me where I need to be All hail St Paul!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log file
enable the bin log in mysql. It doesn't log transactions i.e. what it was, what it became, but the update will be logged prathima rao wrote: hi, how to create a log file of the updates done on the data in visual basic or in mysql for a particular record for example i have a purchase order record in which my junior changes say rate without my knowledge how will i know what has been changed p rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: are you receiving this?
Looks like it made it through to here David Smithson wrote: I have sent a couple of emails to the list, but I don't know if they are posting or not, because I haven't received them back from the list, which I assumed I would. Can someone just confirm that this message is indeed being posted? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
build problem on solaris 8
Hi all, I got the following error during the configure phase: configure: WARNING: term.h: present but cannot be compiled configure: WARNING: term.h: check for missing prerequisite headers? configure: WARNING: term.h: see the Autoconf documentation configure: WARNING: term.h: section Present But Cannot Be Compiled configure: WARNING: term.h: proceeding with the preprocessor's result configure: WARNING: term.h: in the future, the compiler will take precedence configure: WARNING: ## -- ## configure: WARNING: ## Report this to the AC_PACKAGE_NAME lists. ## configure: WARNING: ## -- ## configure: WARNING: sys/ptem.h: present but cannot be compiled configure: WARNING: sys/ptem.h: check for missing prerequisite headers? configure: WARNING: sys/ptem.h: see the Autoconf documentation configure: WARNING: sys/ptem.h: section Present But Cannot Be Compiled configure: WARNING: sys/ptem.h: proceeding with the preprocessor's result configure: WARNING: sys/ptem.h: in the future, the compiler will take precedence configure: WARNING: ## -- ## configure: WARNING: ## Report this to the AC_PACKAGE_NAME lists. ## configure: WARNING: ## -- ## I was using gcc from sunfreeware to attempt the build. I'm doiong a source build as the binary from sunfreeware did not include libmysqlclient.10.so -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up directly to tape.
How about mysqldump /dev/nst0 Tucker, Gabriel wrote: Hello All I have been searching the archives and was unable to find an answer. I need the ability to backup MySQL instances directly to a tape device. Currently, I run a mysqldump to disk and have legato pick up the file. As I get to some larger databases, hundred's of gigs, and higher transaction rates, I will need an online solution that goes directly to a tape device. Currently we are using MySQL table types, though this may change. Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Links to myisam database files
Hi all, I'm using 3.23.49a-max-log and as a temporary fix for a disk space problem, I shut mysql down,, moved some database files to different partitions on the system and symlinked them back into the datadir. Everything seems to work ok... So far... Mysql came up with no errors, selects and inserts seem ok, again, no errors in the logs. I know it's an odd way to run a box, but I'm just wondering if there is anything I may have missed doing this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Mysqld Stability and maintanability
It works. It works very well. As Jeremy said, just make sure you are very explicit in your configuration and startup Jeremy Zawodny wrote: On Tue, May 11, 2004 at 09:26:57AM +0700, Winner H Manurung wrote: Dear All, I was an Oracle dba, now my new company want to use Mysql 4.0.18. Does anybody here has experience of running multiple mysqld (i.e. multiple instance on one machine). Is it stable and totally independent to each other? If you configure it properly, yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help:)
you need a smeicolon at the end of the line
Don Matlock wrote:
Thank you very much for the prompt reply.
Yes you were correct, it was the fact that the password had not been
entered at all...I did the
mysql -u root -p
when prompted for the password I just hit enter and was able to get in.
Now I have to figure out why its not accepting the password. I type the
following command as root in mysql:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 3.23.49-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD 'xx'
This is exactly how I typed it in...(just copied and pasted)
When I hit enter with that password...I just get a prompt...no
confirmation the password was accepted or anything.
Did I type in the command for the pass wrong?
Don
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 04, 2004 2:40 AM
To: robert_rowe
Cc: [EMAIL PROTECTED]; Don Matlock
Subject: Re: Help:)
robert_rowe wrote:
Issuing this command:
mysqlSET PASSWORD FOR 'root'@'localhost' = PASSWORD xx
set your password to xx
I'm not so sure. PASSWORD is a function which expects a string. The
correct syntax is
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password')
so I don't believe this worked unless Don is misquoting what he did.
You will need to use
mysql -u root -p xx
This will not work. You may not put a space between the -p and the
password. The space indicates that xx is the db to use. If you
want to provide the password on the command line (not really a good
idea), the syntax is
mysql -u root -pxx
See, no space between the -p and the password.
from the local machine to get access with the root user.
This:
mysql -u root -p
is specifying a blank password.
No, it is not. The -p indicates you want to give a password to
authenticate. Since you didn't provide the password on the command
line, mysql will prompt you for it.
I believe that this:
mysql -u root
will prompt you for the password without echoing it to the screen.
No. This will try to authenticate without a password, which will only
work if the specified user (root, in this case) has no password.
Don,
I expect that when you enter `mysql -u root -p`, you get prompted for a
password and then get an error message. It would help if you would
please post the exact text of the error message. In the meantime, try
your old password (or no password, `mysql -u root`, if root didn't have
one before), in case the SET PASSWORD failed.
Alternatively, take a look at How to Reset a Forgotten Root Password
http://www.mysql.com/doc/en/Resetting_permissions.html in the manual
for the directions on how to use --skip-grant-tables to recover your
root mysql password.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 4.0.17 and 5 on same machine
I'm doing just that right now. I built from source and put mysql4 in /usr/local/mysql and mysql5 in /usr/local/mysql5 with the my.cnf is /usr/l.ocal/mysqlX/var. In the my.cnf file I put the local sockets into /usr/local/mysqlX/tmp and mysql5 is using port 3305 with mysql4 on 3306. The toughtest thing I bumped into was the permissions for the var and tmp directories weren't set correctly by make install. Terry Riley wrote: Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relative performance between innodb and myisam
we have a bit of a debate going on at work. The gist of it is that there is a a performance hit when using innodb tables vs myisam tables. I understand the for a given dataload innodb will be larger, but is there a performance hit as well? If so, as a rough comparison, how much of a hit is it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-establishing nuked log file
flush logs from the mysql command line works Jesse Sheidlower wrote: I recently restarted my MySQL server (4.0.10 in this case) with the general query log enabled, to help out with some debugging and optimization issues. After looking at a batch of these, I then deleted the log file directly, with rm foo.log, assuming that it would be re-generated as soon as the next query came in. It was not. Is there any way to get logging restarted without stopping and restarting the server itself, which is live and which I'd prefer not to interrupt? Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL monitoring tools
I just ran across mtop on freshmeat. Looks kind of nice to me. -- Bruce One day at a time... One second if that's what it takes Gorantla, Aruna wrote: Hi All, Does anybody know if there are any monitoring tools for MySQL database? Thanks, Aruna. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERD
I've not used it yet, but I saw this on freshmeat: Data Architect from the Kompany http://www.thekompany.com/products/dataarchitect/ Won Lee wrote: Anyone know a good ERD program that will also interact with MySQL to write the CREATE scripts? As usual open source would be nice, also running on a win2k workstation. I used to use PowerDesigner. Nice software but too expensive. Won - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: read only message
The message means that the databse files themselves have incorrect permissions for the user mysql is running as. ps -ef | grep mysql will show who mysqld is running as. Then check the owership/permissions of the files to see if they are owned by that user. I think you'll find they are not owned by the same user. Barbara Ferrell wrote: i am using linux redhat 7.2. on my home computer.. i am logged on as root@localhost. i get a message that my table is read only. i thought the root user had all privileges and i cannot figure out how to give myself the privilege to change and alter my database and tables after i just created them.. i also tried to load one line of data into my empty table and it said read only. i tried GRANT ALL PRIVILEGES on *.* to root@localhost. that did not work. i just installed mysql and then created the database and then the tables. i cannot figure out how to get into the Grant tables to give me all the privileges.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bruce One day at a time... One second if that's what it takes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: many similar requests - 1 request
How about a query something like this:
SELECT position, title FROM WHERE position1 IN (1...200) OR position2 IN
(1...200);
Caviats:
1.) I don't do this everyday so that query syntax could be all wet
(probably is in fact). You may have to programaticly generate the IN
clauses to make it work. I don't think elipses are supported.
2.) I may have misunderstood the result set you're trying for.
In anycase, I think you can get ther general idea of what I have in
mind.
boris hajduk wrote:
hi fellow mysql users :)
i'm programming a little application, and i do the following :
for ($i=0 ; $i200 ; $i++)
{
$result=mysql_query(select $i, title from book where position1=$i \
or position2=$i); // this query returns only 1 row
$row=mysql_fetch_row($result);
print(position: $row[0] -- title: $row[1] \n);
}
this generates 200 sql queries... :/
do you see a way to make 1 sql query that would return my 200 rows ?
maybe something like :
select (increment j 0 - 200) , title from book where position1=j \
or position2=j;
note: given a number between 0 and 199, the number is stored in
position1 OR position2 , but never both.
any idea ?
--
Boris Hajduk [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CGI and MySQL
George, At a minimum, you have to setup a user with permissions to access the database. Once you have that done I think it might work [EMAIL PROTECTED] wrote: Hi, I'm currently working on a project in which I am working with CGI scripts for a web based user interface, and MySQL for my database. I'm a student at a University of Waterloo (in Canada). The program is a hotel reservation system (irrelevant) and I'm coding in C and C++. The system is being developed on a Solaris box. , and my problem is that the CGI script is on a different server than the one where my database is one. In fact, my database is located on my Win2k box at home. I noticed that the mysql_real_connect() method in the MySQL C API, has a specification for the IP address, and port. However when I specify the IP address of my box at home and a port 3306 (definitely not in use) and leave the password and user NULL (because I haven't set up a password and user) it tells me it cannot connect to the MySQL server on port 3306. I know the problem isn't that I'm not running the daemon, because I check that already. Is there something I'm missing or is this even possible??? Please reply to the above email address AS WELL AS TO: [EMAIL PROTECTED] I appreciate any assistance you can offer. Thank you George - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bruce One day at a time... One second if that's what it takes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Beginner SQL Question
NOt supported... Yet. Rumor has it sub-selects will be in 4.1 John Morton wrote: Hi, Can someone tell me why this will not work, please SELECT * FROM tbl1 WHERE column =(SELECT * FROM tbl2 WHERE intcolumn = 15); (Table and column names changed to protect the innocent, 15 could be a variable or anything) I thought this was just a common or garden 'Inner Query'. I Have a feeling this has to do with the fact that I have to use different syntax for MySQL. I'm no SQL guru. Could someone please Point me in the right direction. Thanks in anticipation, john John Morton http://www.jdmorton.com Australia (08) 9451 6447 [EMAIL PROTECTED] Where is the wisdom we've lost in knowledge. Where is the knowledge we've lost in information? -T.S. Eliot - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bruce One day at a time... One second if that's what it takes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: possible bug in sum() function
I'm replying to the list because I got so many of these back: Ya know gang... some days it just doesn't pay to get outta bed. Yes, indeed, this is the exact reason my wife doesn't let me do arithmatic :) Sorry and to the guy who replied with no spam please? what can I say, my face is very red. Sinisa Milivojevic wrote: [EMAIL PROTECTED] writes: We keep a timecard database and I was just asked how much time had been charged to a particular project code. No problem says I and did the following: select sum(worktime) from timecard where tcacct=project; The answer came back 182. Then I was asked who had spent time on the project. the query was: select sum(worktime),user from timecard where tcacct=project group by user; the answer was: 8.00user1 14.00 user2 160.00 user 3 Now, where I went to school, this adds up to 184! Suggestions? Sorry, but you have gone to the wrong school. 8 + 14 + 160 = 182 When in doubt, use a calculator to see the correct result. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto startup of mysql in redhat box?
login as root. Use: chkconfig --list mysql to determin if it on, if it's not on issue: chkconfig mysql on. That will make sure that mysql starts at boot time. To start mysql manually use: service mysql start Steve West wrote: I'm trying to figure out how to get mysql to startup automatically after rebooting the server? I have a Redhat 7.0 box and I placed a file to call up the mysql.server auto startup script as follows: /etc/rc.d/init.d/mysql which has the following code: /bin/sh -c 'cd /usr/local/mysql ; ./bin/safe_mysqld --user=mysql ' I also made sure that mysql.server file is executable. Any ideas on how to get the mysql to startup? P.S. I'm still new to linux and would appreciate any help! :) Thanks! SW - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bruce One day at a time... One second if that's what it takes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto startup of mysql in redhat box?
HTH mucho coolness Steve West wrote: Bruce, Thanks for all the help so far! :) It finnally works. SW -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 10:20 PM To: [EMAIL PROTECTED] Subject: Re: auto startup of mysql in redhat box? Actually that's what chkconfig does... It makes the links in /etc/rc.d again be root. then: ln -s /etc/rc.d/init.d/mysql /etc/rc3.d/S99mysql repeat as necessary in the rc[whatever].d directory. I think what's going on with the binary tarball you used is for something like slackware, which doesn't nativly have chkconfig... I've grafted it onto mine :) Steve West wrote: Bruce, I installed mysql-3.23.39-pc-linux-gnu-i686.tar.gz . Even though I'm still a newbie to linux I thought to stay away from the redhat rpm files! :) Is this the correct version I should be using? I ran some tests w/ mysql and it seems everything works fine (i.e. added a test database, some tables, etc). But I just can't seem to get it to automatically startup. The mysql.server autostartup script mentions something about linking mysql.server to /etc/rc3.d/S99mysql and /etc/rc0.d/S01mysql. Do you know how I can go about doing this? Thanks! SW -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 9:48 PM To: [EMAIL PROTECTED] Subject: Re: auto startup of mysql in redhat box? Yeah, it means that the mysql.server file doesn't have the right structure at the top to make chkconfig happy. What version of mysql are you using? The startup script in 3.23.38 is correct. Assuming you built it from a source tarball. You did didn't you? :) Beware of RedHat RPMs (OK, RPMs in general)... They will lull you, 'cause they are mostly right good. Then they will leap upon you and bite you very hard when and where you least expect it. Bruce Steve West wrote: Bruce, I tried what you suggested and I get the following error message on my redhat 7 server: /sbin/chkconfig --list mysql service mysql does not support chkconfig Any ideas what I'm doing wrong? Thanks! SW -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 9:32 PM To: [EMAIL PROTECTED] Cc: MySQL Subject: Re: auto startup of mysql in redhat box? login as root. Use: chkconfig --list mysql to determin if it on, if it's not on issue: chkconfig mysql on. That will make sure that mysql starts at boot time. To start mysql manually use: service mysql start Steve West wrote: I'm trying to figure out how to get mysql to startup automatically after rebooting the server? I have a Redhat 7.0 box and I placed a file to call up the mysql.server auto startup script as follows: /etc/rc.d/init.d/mysql which has the following code: /bin/sh -c 'cd /usr/local/mysql ; ./bin/safe_mysqld --user=mysql ' I also made sure that mysql.server file is executable. Any ideas on how to get the mysql to startup? P.S. I'm still new to linux and would appreciate any help! :) Thanks! SW - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bruce One day at a time... One second if that's what it takes -- Bruce One day at a time... One second if that's what it takes -- Bruce One day at a time... One second if that's what it takes -- Bruce One day at a time... One second if that's what it takes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How I can mysqlhotcopy?
TYou need to obtain and install the perl DBI (database interface) and the DBD (DataBase Driver) for msql/MySQL, then it will work. freelsq wrote: when I use mysqlhotcopy ,it say this: [root@my bin]# ./mysqlhotcopy Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/5.00503/i386-linux /usr/lib/perl5/5.00503 /usr/lib/perl5/site_perl/5.005/i386-linux /usr/lib/perl5/site_perl/5.005 .) at ./mysqlhotcopy line 8. BEGIN failed--compilation aborted at ./mysqlhotcopy line 8. how can I solve this problem? Best Regards LSQ __ === ÐÂÀËÃâ·Ñµç×ÓÓÊÏä (http://mail.sina.com.cn) ÐÂÀËÍø¡ÖØÍƳöÊÖ»ú¶ÌÐŶ¥¼¶ÐÂÎÅ·þÎñ (http://sms.sina.com.cn/topnews) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bruce One day at a time... One second if that's what it takes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
