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
question?
hey. within php (or any other language) is there a way to create the mysql sql, and execute the sql, where the process can wait until the network connection for the mysql command/process is actually valid? IE (phpesque) $pdo=new pdo() sql = select * from foo where a=:a $s=$pdo-prepare($sql) $s-bind(a,$one) $s-execute() The issue we're seeing, is that the network (it's all wifi) is really bad.. and the app machine, might not have a connection to the db box. We're wondering if there's a way to simply have mysql/php detect when a valid connection is there, and then proceed. We've thought about the try/catch process, any others? Thanks -- 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
mysql - uppoer limit for doing simultaneous red/writes..
Hi. Considering a system, where I have a centralized Mysql setup. I'm not sure exactly what this should be called, single box, cluster, etc... But I'm looking to have a system of a a bunch of boxes, whihc run apps that will access (read/write) to the different dbs/tbls on the mysql setup. I'm trying to get a feel for just what mysql can handle in this situation. Can a mysql setup handle 50K-100K simultaneous connections (reads/writes) to the same db.. Any docs that discuss this that I can take a look at. My setup would consist of a couple of queues, that get accessed by ~100 machines to pull of the data, each machine would then run a number of different apps that would in turn hit back to the master mysql setup/db process.. So, I'm trying to determine if mysql can actually handle this kind of scenario. Thanks -- 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 guru??
Hi. Got a major pain that I'm trying to solve using mysql. Trying to handle a hierarchical tree structure, where I have a parent/child structure that grows as data is added to the system. The process needs to continuously determine if the overall tree, and all the associated nodes/leafs have completed so not only is the tree growing, but data for the given node/leaf is also changing, The system is comprised of a parent app which spawns descendant apps that in turn can spawn descendant apps, and so on.. The system is represented in mysql as a parent/child tree, where each spawned app has an ID, as well as a status for the completion status of the app. I'm trying to find someone I can talk to regarding this, so I can get clarity on how this can be implemented. The process needs to be able to: -update the tree tbl with updated data from the running apps -update the tbl with new nodes/leafs as the spawned apps are created -quickly return 0/1 if the descendants of a node have been complete I've created a few different tbl defs, and played with a few different approaches, but haven't got this right yet. I've looked at a number of different articles covering hierarchical, adjacency models, closures, etc... **The nested soln isn't applicable to the project, as the data/tree tbl is continually growing, which would require a complete rebuilding of the nested tbls, which would impose a computational/time hit on the process. I can provide the sample tbl defs/data that I'm using, as well as more data on what I'm trying to accomplish. So, if you're skilled in this area, let's talk. Thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Parent/Child - Linked List
Running mysql(5.5) /linux/ php/python Got a situation with a test env, where I'm dealing with a number of spawned processes, that might also spawn processes, so I have a tree where I'm looking to determine when the spawned processes have completed. To manage this cluster/tree of processes, I'm considering using a tree kind of DB representation: The tables would be: ParentChildTBL ParentID, int ChildID int ItemStatusTBL Name, varchar ID, int Status int ItemStatusTBL.ID -- ParentID/ChildID Tree Graph: top(1) | _ | | | itemA(2) itemB(3) itemC(4) | -- | | | itemD(5) itemE(6) itemF(7) pseudo tbl representation ItemStatusTBL top, 1, 0 itemA, 2, 0 itemB, 3, 0 itemC, 4, 0 itemD, 5, 0 itemE, 6, 0 itemF, 7, 0 ParentChildTBL ,1 1,2 1,3 1,4 2,5 2,6 2,7 I've got a test app that spawns off child processes, where each process then updates the status of the corresponding given item upon completion. So the status in the itemStatusTBl will change from 0 to 1. I'm trying to determine how to efficiently be able to determine when the children of a given top/root node in the parentChildTBL are complete, ie, have the status set to '1'. (There could be multiple top level/root nodes, each with their own independent set of children) I could try to simply look at all the children each time I examine the tbl, but that might result in a lot of recursive function/processing in order to get to all the levels... I've looked at various articles, but not sure which is the best approach to this kind of issue. A complete run is determined by: when all children of the top level/root node != '0' Is this a self join,left join situation? A sample query/pointers would be helpful Thoughts/Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Parent/Child - Linked List
On Mon, Jan 16, 2012 at 1:52 PM, bruce badoug...@gmail.com wrote: Hey Authur. Should have been more clear. I've looked over a number of sites. And with the exception of the the articles that talk about using the Nested List approach, nowhere did I find data on how to get a complete list of the child descendants of a given 'root'/top item from the parent/child TBL. Chunks of code/pointers would be seriously useful. Thanks On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fuller fuller.art...@gmail.com wrote: See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Parent/Child - Linked List
hi Peter. Sorry.. Been looking at this for awhile. In the sample data/tbl I provided, it has two top level root/parents. Ie, I have two entries that don't have a parentID. I use 0 to be null. The items are (0,1), and (0,8). The (0,1) item, has a number of descendants. The (0,8) only has a single descendant. For my app, I'm going to have a number of top level items, and they're each going to have a number of descendants, where I don't know the number of descendant rows, or the number of actual descendants. But either way, once I get the descendant list, I still need some way of linking the childID of the descendant to the linked ID of the statusTBL so I can get the status of the childID/app. And like I said, I'm not quite sure how to proceed in an efficient manner on this. Thanks On Mon, Jan 16, 2012 at 4:08 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 1/16/2012 2:08 PM, bruce wrote: Hi Peter. Not a mysql guru... so I've never used stored procedures/sub-queries.. But it sort of makes sense. What I'm really trying to get is to be able to take a test table like below LOCK TABLES `parentChildTBL` WRITE; /*!4 ALTER TABLE `parentChildTBL` DISABLE KEYS */; INSERT INTO `parentChildTBL` VALUES (0,1,1), (1,2,2), (1,3,3), (1,4,4), (2,5,5), (2,6,6), (2,7,7), (0,8,8), (8,9,9); UNLOCK TABLES; and to be able to generate the child/descendant list of the top two/2 items (1,8) I don't understand top two(1,8). In general a non-procedural query of n recursion levels requires n-1 joins. If the number of recursive references is unknown beforehand, the only way to query the tree is via a stored procedure. PB - if I only have a single top level item.. and can do a left join.. but I'm not sure how to accomplish this with two top items, unless I take a look at the approach you provided. I'm looking at being able to compare a 'status' from a linked tbl, that links on the childID... thanks On Mon, Jan 16, 2012 at 2:33 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 1/16/2012 12:53 PM, bruce wrote: On Mon, Jan 16, 2012 at 1:52 PM, brucebadoug...@gmail.com wrote: Hey Authur. Should have been more clear. I've looked over a number of sites. And with the exception of the the articles that talk about using the Nested List approach, nowhere did I find data on how to get a complete list of the child descendants of a given 'root'/top item from the parent/child TBL. Look again, eg listings 7 through 7d in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - Chunks of code/pointers would be seriously useful. Thanks On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fullerfuller.art...@gmail.com wrote: See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr -- 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
deleting the node, and child nodes/leafs for a db/tbl..
Hi. I've got a situation where I have a couple of tables. The relationship between the tables is one of parent/child. I'm trying to figure out the best approach to being able to delete the associated children in the child tbls, of a given parentID in the parentTBL... I've checked into various sites/articles on the 'net.. but i'm not sure how to accomplish this without getting into recursion... I'm using python/php as the interface language to the test tbls.. Any pointers/articles/test code (code/schema) would be helpful... Thanks -- 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
query question...
hi. i've got a situation, where i'm trying to figure out how to select an item from tblA that may/maynot be in tblB. if the item is only in tblA, i can easilty get a list of the items select * from tblA if the item is in tblA but not linked to tblB, i can get the items as well select * from tblA where id not in (select id from tblB); but i have no idea how to combine the two selects.. i need to combine them, as the app can create tblA for a given item, and then later on create the data in tblB, with thblA.id = tblB.aid. thoughts/pointers on this would be appreciated. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
a possible group issue???
Hi...
I have the following...
mysql INSERT INTO ParseScriptTBL VALUES
- ('auburnCourse.py',40,1,1),
- ('auburnFaculty.py',40,2,2),
- ('uofl.py',2,1,3),
- ('uky.py',3,1,4),
- ('ufl.py',4,1,5)
- ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+--+---+--+++
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+--+---+--+++
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+--+---+--+++
2 rows in set (0.00 sec)
i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have only
one, and some will have both...
i've tried to do the query, and added a group by CollegeID' with no luck..
so how can i combine the two rows to get a single row??
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: a possible group issue???
hi martin...
thanks for the reply.. but that still generates two separate rows as well...
-Original Message-
From: Martin Gainty [mailto:mgai...@hotmail.com]
Sent: Friday, June 12, 2009 12:04 PM
To: bruce Douglas
Subject: RE: a possible group issue???
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40
GROUP BY CollegeID WITH ROLLUP
;
http://dev.mysql.com/doc/refman/6.0/en/group-by-modifiers.html
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.
From: bedoug...@earthlink.net
To: mysql@lists.mysql.com
Subject: a possible group issue???
Date: Fri, 12 Jun 2009 11:36:35 -0700
Hi...
I have the following...
mysql INSERT INTO ParseScriptTBL VALUES
- ('auburnCourse.py',40,1,1),
- ('auburnFaculty.py',40,2,2),
- ('uofl.py',2,1,3),
- ('uky.py',3,1,4),
- ('ufl.py',4,1,5)
- ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+--+---+--+++
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+--+---+--+++
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+--+---+--+++
2 rows in set (0.00 sec)
i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have
only
one, and some will have both...
i've tried to do the query, and added a group by CollegeID' with no
luck..
so how can i combine the two rows to get a single row??
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
Insert movie times and more without leaving Hotmail®. See how.
--
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: Resetting MySQL Root Password
jason... did a fresh mysql install.. simply type foo mysql and mysq will start, assuming you have the mysq daemon started. to start the mysql daemon, /etc/init.d/service/mysql start (check it first, but it's something like the above..) -Original Message- From: Jason Todd Slack-Moehrle [mailto:mailingli...@mailnewsrss.com] Sent: Monday, April 27, 2009 9:46 AM To: mysql@lists.mysql.com Subject: Resetting MySQL Root Password Hi All, CentOS 5.3 I installed MySQL Server via yum and started it. I tried entering: mysqladmin -u root password yourrootsqlpassword mysqladmin -h server1.example.com -u root password yourrootsqlpassword But I get: r...@server1 ~]# /usr/bin/mysqladmin -u root -h localhost password mypassword /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' How can I reset this and allow Root access, otherwise nobody has access! Thanks, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bedoug...@earthlink.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to design book db
hi wm. take this for what it's worth! i think you're trying to solve an issue that's already been solved. why don't you do some quick/dirty research on companies that either issue/manage isbn numbers, or companies who sell solutions for online/brick-mortar bookstores. explain that you're thiking of setting up a system (ie, might buy their product/services/solutions) but that you want to know more about the underlying database/schema regarding your issues... you'll be surprised at how much you can get from this kind of approach!!! in your case, you aren't looking to design a system that's going to be that different from what's already out there make the calls. and if you don't get any information from this process, what have you lost. on the other hand, you might find a resource who gives you everything you're looking for, and more! also, at some point, you're going to want to have a sample book database to test your db schema/app on have fun!! -Original Message- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Tuesday, January 06, 2009 1:38 PM To: mysql@lists.mysql.com Subject: Re: how to design book db On Tue, January 6, 2009 13:30, PJ wrote: My comments, questions explanations inserted below mos wrote: At 09:55 AM 1/6/2009, you wrote: -Original Message- From: c...@l-i-e.com [mailto:c...@l-i-e.com] Sent: Tuesday, January 06, 2009 9:47 AM To: mysql@lists.mysql.com Subject: RE: how to design book db Just theories here: The same book re-issued by another publisher might have a different ISBN. A book with an insert (e.g., CDROM) may have a different ISBN, but be the same for some purposes. And mistakes can be made... Ultimately, I suspect that the uniqueness of ISBN to what normal folks call the same book is not as clear as one would hope. [JS] I'm really glad I was able to eavesdrop on this conversation. I had no idea the ISBN issue was so murky. For better or worse, most of my publishers don't use ISBNs; many of them don't even assign product numbers. I guess the only way around it is to assign your own unique key. Not only do I have 2 ISBNs for a few books, but there is also the problem of books in foreign languages (French, Italian, German, Spanish) - my boss (my daughter who owns http:// www.ptahhotep.com ) tells me that foreign editions do have different ISBN numbers but not to worry, they are diffeerent but they are unique and would not conflict with the US numbers. BTW, it might be worth while for PJ to look at how Amazon stores its data. I don't think you can find an easier to use database for searching on books. It looks like they store 2 ISBN numbers per book. It also appears they use fulltext indexing on a lot of fields so the user can search on anything. I'll look at the Amazon stuff. Thanks for the suggestion. But this is where I get a little bit muddled: I have a problem (probably just understanding how things work) with categories. Since there are many categories (somewhere like 40+), how do I handle that? Would it be best to set up foreign keys for a categories table? But then there is the problem of fulltext indexing... it only works with MyISAM but foregn keys only work with InnoDB? So do I use MyISAM, a categories field, and just use 1 table for books with fulltext indexing on description, title, author, and categories tables? And what about fulltext indexing? Do I really need that? I thought it would be sufficient to search the fields for words or phrases (in the case of categories which often will be like second intermetiate period). In effect, what I expect to be doing is to use php to format the pages dynamically using the search functions of MySQL - so the categories would be on a static page with javascript dropdown fields for the different categories which would poinnt to a file that would do the required search and php would then set up the page to display the retrieved info. You could have a category table, a table to relate categories to categories and another table to relate books to categories. That's the general solution since books could be in multiple categories. I did a system like that for a bookstore but they didn't want to put the books into categories and just wanted to search. If you are using a procedural language you can use that instead of foreign keys to enforce consistency then you can us MyISAM tables. Also check out http://www.abebooks.com/ re: ISBN numbers. If this application is for a used bookstore then you're going to have to allow books without ISBN's because books from 40 years ago of course don't have ISBN's. No, this is not for a used bookstore... only for the ptahhotep site which is only a bibliography of books on Ancient Egypt. :-) - so you're right, there are old books in there. PJ Also see http://en.wikipedia.org/wiki/Isbn. Mike -- MySQL General Mailing List For list archives:
RE: how to design book db
hey phil... are you sure that a book can have multiple ISBN numbers. I was under the impression that a book/version has a single ISBN number. care to share where you have derived your understanding... i believe bowkers/new jersy is responsible for allocating ISBN blocks for US authors/publishers... thanks -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Monday, January 05, 2009 3:06 PM To: mos Cc: mysql@lists.mysql.com Subject: Re: how to design book db mos wrote: At 08:17 AM 12/29/2008, you wrote: I am rather fresh to MySQL and am trying to fix and update a website - modifying from just plain html to css, php and MySQL. I'm working on FreeBSD 7.0, MySQL 5.1,30, PHP5.28 Apache 2.2.11. I need figure out how to set up (design) a database of books which gets rather complicated since I must implement searches of the database based on key words including categories, ISBN numbers, authors, dates, etc. etc. The problem is how to deal with duplication of the data - In other words, a book may have not only several authors, but also several ISBN numbers, fall under several categories, different dates (year of publication), several publishers I probably haven't yet seen all of the variables. I certainly do not want to enter the same book many times with just one of each different variable. I suppose that one way to do it is to enter one row with a lot of columns to store all the the different variables; a search would probably be simpler this way if the search criteria are limited to 1 word. Or would it? I rather do think that the search should be limited to 1 word anyway. :-) If the search would be for a category, for instance, would it make sense to use a column for category with an input of keywords for the different categories?; rather than a column for each category or another table of categories? Multiple publication years could probably be different row entries since there would not be more than 2 or would be a different publisher, language, or country. I really with to K.I.S.S this undertaking and would appreciate any help or suggestions. If it helps, you can see the site as it is at present http://www.ptahhotep.com - but since it is rather messed up at the moment, it is best viewed with IE. Some of the links and jscripts don't work on FireFox. TIA, PJ You can of course simplify things by putting the alternate ISBN number in the description of the book and put a full text index on it. Same with alternate authors etc.. It would be a catch all for items that you don't have columns for. How do I do that? I think the best line of attack is to work from an existing model. Why re-invent the wheel? You're right... I appreciate the suggestion and the links... There are a few bookstore/library schemas here: http://www.databaseanswers.org/data_models/ http://www.edumax.com/oracle-basics-06-normalization-and-sample-schema-creat ion-normalization-and-sample-schema-creation.html Mike Sorry for the long holiday delay in continuing... I checked the links below I think they will help ... but there are some things that are not clear in my mind: 1. I understand what the abbreviations PK and FK (primary key and foreign key) are but what is PF? (primary field??? - this is in the link http://www.databaseanswers.org/data_models/ uner Libraries and books 2. How can I deal with a primary key for books? ISBN would be great, except for the fact that it was only implemented at a certain date and books published before that date do not have an ISBN number. 3. And what about books that were written by several authors? 4. What do I need to fix in the tables below? +---+ | Tables_in_biblane | +---+ | authors | | books | | books_by_author | | books_by_category | | categories| +---+ mysql DESCRIBE authors; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | author_id | tinyint(4) | NO | PRI | NULL| | | auth_first_name | varchar(15) | NO | | NULL| | | auth_last_name | varchar(32) | NO | | NULL| | +-+-+--+-+-+---+ mysql DESCRIBE books; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | tinyint(11) | NO | PRI | NULL| auto_increment | | title | varchar(60) | NO | | NULL|| | auth_name | char(28)| NO | | NULL|| | auth_first | char(12)| NO | | NULL|| | yr | year(4) | YES | | NULL|| | lang | char(7) | YES | | NULL|
RE: how to design book db
right... forgot about that! -Original Message- From: Paul Wilson [mailto:hoo...@staff.iinet.net.au] Sent: Monday, January 05, 2009 5:59 PM To: 'bruce'; 'PJ'; 'mos' Cc: mysql@lists.mysql.com Subject: RE: how to design book db Recent books that I've looked at have 2 ISBN's - one the older 10 digit, and also the newer 13 digit version. Both printed on the same book (both on the back cover at the bottom and inside). Of course, a hard cover will have a different ISBN again. Hooker -- If ignorance is bliss, politicians should be orgasmic! -Original Message- From: bruce [mailto:bedoug...@earthlink.net] Sent: Tuesday, January 06, 2009 8:52 AM To: 'PJ'; 'mos' Cc: mysql@lists.mysql.com Subject: RE: how to design book db hey phil... are you sure that a book can have multiple ISBN numbers. I was under the impression that a book/version has a single ISBN number. care to share where you have derived your understanding... i believe bowkers/new jersy is responsible for allocating ISBN blocks for US authors/publishers... thanks -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Monday, January 05, 2009 3:06 PM To: mos Cc: mysql@lists.mysql.com Subject: Re: how to design book db mos wrote: At 08:17 AM 12/29/2008, you wrote: I am rather fresh to MySQL and am trying to fix and update a website - modifying from just plain html to css, php and MySQL. I'm working on FreeBSD 7.0, MySQL 5.1,30, PHP5.28 Apache 2.2.11. I need figure out how to set up (design) a database of books which gets rather complicated since I must implement searches of the database based on key words including categories, ISBN numbers, authors, dates, etc. etc. The problem is how to deal with duplication of the data - In other words, a book may have not only several authors, but also several ISBN numbers, fall under several categories, different dates (year of publication), several publishers I probably haven't yet seen all of the variables. I certainly do not want to enter the same book many times with just one of each different variable. I suppose that one way to do it is to enter one row with a lot of columns to store all the the different variables; a search would probably be simpler this way if the search criteria are limited to 1 word. Or would it? I rather do think that the search should be limited to 1 word anyway. :-) If the search would be for a category, for instance, would it make sense to use a column for category with an input of keywords for the different categories?; rather than a column for each category or another table of categories? Multiple publication years could probably be different row entries since there would not be more than 2 or would be a different publisher, language, or country. I really with to K.I.S.S this undertaking and would appreciate any help or suggestions. If it helps, you can see the site as it is at present http://www.ptahhotep.com - but since it is rather messed up at the moment, it is best viewed with IE. Some of the links and jscripts don't work on FireFox. TIA, PJ You can of course simplify things by putting the alternate ISBN number in the description of the book and put a full text index on it. Same with alternate authors etc.. It would be a catch all for items that you don't have columns for. How do I do that? I think the best line of attack is to work from an existing model. Why re-invent the wheel? You're right... I appreciate the suggestion and the links... There are a few bookstore/library schemas here: http://www.databaseanswers.org/data_models/ http://www.edumax.com/oracle-basics-06-normalization-and-sample-schema-creat ion-normalization-and-sample-schema-creation.html Mike Sorry for the long holiday delay in continuing... I checked the links below I think they will help ... but there are some things that are not clear in my mind: 1. I understand what the abbreviations PK and FK (primary key and foreign key) are but what is PF? (primary field??? - this is in the link http://www.databaseanswers.org/data_models/ uner Libraries and books 2. How can I deal with a primary key for books? ISBN would be great, except for the fact that it was only implemented at a certain date and books published before that date do not have an ISBN number. 3. And what about books that were written by several authors? 4. What do I need to fix in the tables below? +---+ | Tables_in_biblane | +---+ | authors | | books | | books_by_author | | books_by_category | | categories| +---+ mysql DESCRIBE authors; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | author_id | tinyint(4) | NO | PRI | NULL| | | auth_first_name | varchar(15) | NO | | NULL
trying to figure out unique/distinct situation...
hey... got a quick situation where i'm trying to figure out how to do a unique/distinct so i get two (2) rows for the 'faculty_id'... so.. how can i get just two unique/distinct rows based on the faculty_id thanks mysql select distinct f1_status.faculty_id, c1.cname, course1.cname, f1_status.userID, c1.id, f1_status.id - from c1 - join d1 - on c1.id=d1.cID - join course1 - on d1.id=course1.dID - join f1_status - on course1.fID=f1_status.faculty_id - where c1.id=1 and d1.id=1; ++---+-++++ | faculty_id | cname | cname | userID | id | id | ++---+-++++ | 1 | usc | math-101| 1 | 1 | 1 | | 2 | usc | math-202| 2 | 1 | 2 | | 1 | usc | physics-101 | 1 | 1 | 1 | ++---+-++++ 3 rows in set (0.00 sec) == mysql select c1.cname, course1.cname, f1_status.faculty_id, f1_status.userID, c1.id, f1_status.id - from c1 - join d1 - on c1.id=d1.cID - join course1 - on d1.id=course1.dID - join f1_status - on course1.fID=f1_status.faculty_id - where c1.id=1 and d1.id=1; +---+-+++++ | cname | cname | faculty_id | userID | id | id | +---+-+++++ | usc | math-101| 1 | 1 | 1 | 1 | | usc | math-202| 2 | 2 | 1 | 2 | | usc | physics-101 | 1 | 1 | 1 | 1 | +---+-+++++ 3 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sql/group question --
Hi.. I have the following query.. missing something based on 'net searches. but how do i create a group to allow me to get a single row/count based on the 'd1_status.dept_id' i can't figure out exactly where in the query to have the count(*) xxx group by abc... the few different approaches i've tried have led to errs... any pointers/comments will be appreciated. thanks mysql select * - from course1 - join d1 - on course1.dID=d1.id - join c1 - on c1.id=d1.cID - join d1_status - on d1_status.dept_id=d1.id - where course1.id=1 - and c1.id=1; +--+--+-++---+-++---+-++ +-+++ | cname| dID | fID | id | dname | cID | id | cname | stateID | id | userID | dept_id | status | id | +--+--+-++---+-++---+-++ +-+++ | math-101 |1 | 1 | 1 | math | 1 | 1 | usc | 1 | 1 | 1 | 1 | 1 | 1 | | math-101 |1 | 1 | 1 | math | 1 | 1 | usc | 1 | 1 | 2 | 1 | 1 | 4 | +--+--+-++---+-++---+-++ +-+++ 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Guru Needed!!
Hi. I've got a situation where I need to reach out/talk to a mysql guru every now and then. For the most part, the questions are probably 5-10 minutes for the right person, but they might take me hours/days to cobble together a good solution. (I'm not a mysql guru!!) As an example, I have a situation now where I've been trying to figure out a solution for a day now... I'm looking for someone that I can talk to periodically if I have questions. I'm willing to drop something in a paypal acct for this function. Posting to the email list, or the IRC chat isn't always expedient for my needs. Thanks -bruce -- 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 figure out how to create the right query...
Hi. I have the following test db/tbl setup. stateTBL +--+ | stateName| | stateID |+ +--+ V V V collegeTBL V +--+ V | collegeName | V | stateID |+ | collegeID|+ +--+ V V V deptTBLV +--+ V | deptName | V | collegeID|+ | deptID |+ +--+ V V V courseTBL V +--+ V | courseName | V | deptID |+ | courseID |+ +--+ V V V facultyTBL V +--+ V | facultyName | V | courseID |+ | ID | +--+ userTBL +--+ | userName | | itemID | | itemType | | assignedDate | | userID | +--+ - itemTBL +--+ | type | | itemID | +--+ itemTBL denotes a College, Dept, Course, Faculty (could just as easily have had the separate cols in the userTBL, for each of the items.. this would have resulted in empty fields, as the app would have had a single item per row in the userTBL..) typeitemID college 1 dept 2 course3 faculty 4 -- the userTBL is used to track each item the user is responsible for. an item could be a College, Dept, Course, or Faculty. my intended app will allow the user to select a given item (college, dept, course, faculty) and to store this data. when a user is displaying a list of items, i'd like to have my app determine if the child of the item has been selected by the user. so. if a user has selected stanford, i'd like to be able to have a query that returns 'true' for any item, that has a 'child' (leaf) already selected by the user. (this should work for any level of childTBL) so if a user has selected a course, from the stanford, physics dept, physics 101, i'd like to have a query that returns 'true' if the user selects 'stanford' from the list of colleges... i'm hoping that this makes sense!! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL und dual cores
hi... a sa short test, how would one demonstrate this from the cli.., using the mysql cmd interface?? thanks -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 7:59 AM To: Marten Lehmann Cc: mysql@lists.mysql.com Subject: Re: MySQL und dual cores In the last episode (Oct 17), Marten Lehmann said: we are using MySQL 4.1 and 5 on AMD dual core processors, but I can only see one mysqld process on each machine. Since a process is always tied to a certain processor, mysqld doesn't seem to make use of the second core. As far as I know multiple threads of one process would be visible as different processes using the ps command. Is mysqld really not using more than one processor core? Or if it does, then how can I verify it? Each thread of a threaded process can run on a different CPU. Try connecting to mysql over two sessions and run SELECT BENCHMARK(100,1+1); on both. If you switch to top you should see mysqld go to 200% CPU. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL und dual cores
sorry.. my bad, i was looking for the exact sequence of cmds that i could type in, in order to test this!!... sorry for the confusion.. thanks -Original Message- From: ?? [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 8:26 AM To: bruce Cc: Dan Nelson; Marten Lehmann; mysql@lists.mysql.com Subject: Re: MySQL und dual cores 2008/10/17 bruce [EMAIL PROTECTED] hi... a sa short test, how would one demonstrate this from the cli.., using the mysql cmd interface?? Right, you also can use it in other editor,such as mysql-front editor or sql server thanks -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 7:59 AM To: Marten Lehmann Cc: mysql@lists.mysql.com Subject: Re: MySQL und dual cores In the last episode (Oct 17), Marten Lehmann said: we are using MySQL 4.1 and 5 on AMD dual core processors, but I can only see one mysqld process on each machine. Since a process is always tied to a certain processor, mysqld doesn't seem to make use of the second core. As far as I know multiple threads of one process would be visible as different processes using the ps command. Is mysqld really not using more than one processor core? Or if it does, then how can I verify it? Each thread of a threaded process can run on a different CPU. Try connecting to mysql over two sessions and run SELECT BENCHMARK(100,1+1); on both. If you switch to top you should see mysqld go to 200% CPU. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL und dual cores
hi ron. forgive me, so running mulitple instances of mysql (the client) will bounce between dual processors??? h... i'll check it out. never really thought about how to test this, thanks.. -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 8:41 AM To: bruce Cc: 'Marten Lehmann'; mysql@lists.mysql.com Subject: Re: MySQL und dual cores In the last episode (Oct 17), bruce said: From: Dan Nelson In the last episode (Oct 17), Marten Lehmann said: we are using MySQL 4.1 and 5 on AMD dual core processors, but I can only see one mysqld process on each machine. Since a process is always tied to a certain processor, mysqld doesn't seem to make use of the second core. As far as I know multiple threads of one process would be visible as different processes using the ps command. Is mysqld really not using more than one processor core? Or if it does, then how can I verify it? Each thread of a threaded process can run on a different CPU. Try connecting to mysql over two sessions and run SELECT BENCHMARK(100,1+1); on both. If you switch to top you should see mysqld go to 200% CPU. a sa short test, how would one demonstrate this from the cli.., using the mysql cmd interface?? I thought that's exactly what I explained how to do :) Run the mysql command in two terminal windows (or screen sessions, or vtys, or whatever you prefer) and top in a third. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL und dual cores
hey martin... thanks, and this is different.. someone else replied that alll i had to do, was to start two sessions of mysql in different xterm windows, and i'd be using both processesors. and that didn't sound right.. -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 10:12 AM To: bruce; 'Dan Nelson' Cc: 'Marten Lehmann'; mysql@lists.mysql.com Subject: RE: MySQL und dual cores need to specify processor with 'taskset' windows specific hacky workaround http://blogs.techrepublic.com.com/programming-and-development/?p=691 Under linux you can set the task processor affinity with taskset http://www.cyberciti.biz/tips/setting-processor-affinity-certain-task-or-pro cess.html Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: MySQL und dual cores Date: Fri, 17 Oct 2008 09:27:28 -0700 hi ron. forgive me, so running mulitple instances of mysql (the client) will bounce between dual processors??? h... i'll check it out. never really thought about how to test this, thanks.. -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2008 8:41 AM To: bruce Cc: 'Marten Lehmann'; mysql@lists.mysql.com Subject: Re: MySQL und dual cores In the last episode (Oct 17), bruce said: From: Dan Nelson In the last episode (Oct 17), Marten Lehmann said: we are using MySQL 4.1 and 5 on AMD dual core processors, but I can only see one mysqld process on each machine. Since a process is always tied to a certain processor, mysqld doesn't seem to make use of the second core. As far as I know multiple threads of one process would be visible as different processes using the ps command. Is mysqld really not using more than one processor core? Or if it does, then how can I verify it? Each thread of a threaded process can run on a different CPU. Try connecting to mysql over two sessions and run SELECT BENCHMARK(100,1+1); on both. If you switch to top you should see mysqld go to 200% CPU. a sa short test, how would one demonstrate this from the cli.., using the mysql cmd interface?? I thought that's exactly what I explained how to do :) Run the mysql command in two terminal windows (or screen sessions, or vtys, or whatever you prefer) and top in a third. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] You live life beyond your PC. So now Windows goes beyond your PC. See how -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql - load data file question..
Hi..
I've got an issue with doing a Load data file cmd..
my test text tbl has a column named company name i'm trying to figure out
how to use the load data file cmd, to be able to extract the company name
col...
when i do:
load data file '/foo/test.csv' into table abc.test (company name);
load data file '/foo/test.csv' into table abc.test ('company name');
i get errs for both of the above...
i can get this to work if i have a column with a single name...
can't find any pointers via google..
any pointers/thoughts/etc...
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large import into MYISAM - performance problems
You could load the data into several smaller tables and combine them into a merged table which would have no real effect on the schema. Ade Simon Collins wrote: I'm loading the data through the command below mysql -f -u root -p enwiki enwiki.sql The version is MySQL 5.0.51a-community I've disabled the primary key, so there are no indexes. The CPU has 2 cores and 2 Gigs memory. The import fell over overnight with a table full error as it hit 1T (I think this may be a file system problem). As it's not importing before anymore show status isn't going to provide any interesting info however, I did notice that mysql was not consuming much CPU time ~ 10%. I wouldn't like to split the data up into separate tables as it would change the schema and I'm not in charge of the schema design - just the DBA at the backend. Cheers Simon mos wrote: Simon, As someone else mentioned, how are you loading the data? Can you post the SQL? You have an Id field, so is that not the primary key? If so, the slowdown could be maintaining the index. If so, add up to 30% of your available ram to your key_bufer_size in your my.cnf file and restart the server. How much RAM do you have on your machine and how many CPU's do you have? What version of MySQL are you using? Also can you post your Show Status output after it has started to slow down? How much CPU is being used after the import slows down? Now from what you've said, it looks like you are using this table as a lookup table, so if it just has an id and a blob field, you probably return the blob field for a given id, correct? If it were up to me, I would break the data into more manageable tables. If you have 100 million rows, then I'd break it into 10x10 million row tables. Table_1 would have id's from 1 to 9,999,999, and table_2 with id's from 10 million to 10,999,999 etc. Your lookup would call a stored procedure which determines which table to use based on the Id it was given. If you really had to search all the tables you can then use a Merge table based on those 10 tables. I use Merge tables quite a bit and the performance is quite good. Mike At 11:42 AM 6/4/2008, you wrote: Dear all, I'm presently trying to import the full wikipedia dump for one of our research users. Unsurprisingly it's a massive import file (2.7T) Most of the data is importing into a single MyISAM table which has an id field and a blob field. There are no constraints / indexes on this table. We're using an XFS filesystem. The import starts of quickly but gets increasingly slower as it progresses, starting off at about 60 G per hour but now the MyISAM table is ~1TB it's slowed to a load of about 5G per hour. At this rate the import will not finish for a considerable time, if at all. Can anyone suggest to me why this is happening and if there's a way to improve performance. If there's a more suitable list to discuss this, please let me know. Regards Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for a Mysql Guru/DBA
Hi... Got a small personal project that I'm considering, and I realize that I need a mysql database guru/dba to talk to to figure out the best approach to implementing a database for my needs of the project. The basic goal of the project is to be able to track the sites that I'm visiting via a Firefox extension. I want to be able to implement something like the breadcrumbs extension, but I want to be able to go a lot further. If you're interested, and you're reasonably good at mysql, and devising database structures/schema then let's talk! Given that this is my own personal project, I've got a small amount of $$$ for your time! Also, I apologize in advance if this is an inappropriate post for the list. And if the list isn't the right place, let me know of a better place to post!! Thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory usage - mysql tuning!!
Hi.. Fairly new to mysql, in particular tuning. I have a test mysql db, on a test server. I've got a test app that runs on multiple servers, with each test app, firing/accessing data from the central db server. the central server is on a 2GHz, 1GMem, 100G system. MySQL is the basic app. the remote/test apps are doing basic selects/inserts, with a few basic select.. group/order by. the db schema appears to be pretty straight forward, with primary/unique fields. keep in mind, i'm not a dba!!! the my.cnf file is pretty basic. there has been a modification for the key_buffer_table entry... my issue, is that when i examine the central mysql (show processlist) i see a number of connections (~10) with the majority being in a sleep status.. However, when i then check the OS, using top, i see that mysql is running, consuming ~ 80-90% of the cpu cycles... so, i'm trying to figure out how to diagnose/solve this issue. any pointers, comments, suggestions will be greatly appreciated. this instance of mysql, is 5.x, and is running on a virtual rhel5 os, under vmware... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copying of tbls...
Hi... I have a situation where I want to periodically copy new rows from a source database/tables on a remote server. Using a cron process, I'm looking to periodically copy the new tbl/rows into the dest database on my local server. i don't want to get into implementing slave/master replication, which would solve the issue. and while it's easy enough to simply copy all the tbls/rows, this would be overkill!! i'm looking for some soln that would allow me to copy thr latest newly inserted tbls/rows since i last copied. there's no timestamp field for the tbls, so i can't trigger off of time in the tbls/rows... any thoughts/comments on this... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Name-based virtual servers?
ryan... mysql has the ability to set access rights to a given database/tables for given users. so it's easy to create a database, and to only allow a given set of users access to the database. in fact, if you lock it down enough, only the user with permissions will know the db exists... unless they have access to the physical filesystem, and then it's a simple matter of examining the dir or the files.. (assuming myisam).. regards... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 10:55 AM To: mysql@lists.mysql.com Subject: Re: Name-based virtual servers? Okay, I've convinced myself that this can't be done. In the http world, it can be done only because http/1.1 includes the hostname in the request. If mysql doesn't do that, there's no way to handle all the cases. So I'll either have to settle for people remembering their own socket files, or ... I would settle for a single shared database in which users could create databases and drop their own databases but not other users'. They'll have to live with namespace collisions. Is that possible to do with mysql's permissions? Thanks. --Ryan On Thu, 20 Dec 2007 [EMAIL PROTECTED] wrote: Hi. I'd like to set up something like apache's name-based virtual hosting. I read the docs for mysqlmanager. It told me how to set it up to run multiple instances of mysql on one machine, where each instance had its own port number and socket file. I'd like to have my users connect to their own servers instead, and not have to remember a port number. Like: mysql -h mysql.username.domain.com -u username -p and then they'd be pointed at their appropriate instance. Anybody know how I can do that? Thanks. --Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger question..
hi.
using mysql 5.0.27 and playing with triggers.
a simple db:
create table foo{
aa varchar (10),
bb int auto_increment,
cc varchar (10),
} innondb
i'm trying to figure out how to create a trigger, such that if the user does
an insert into foo (cc) value (www);
the table will concat the www with the 'id' value to produce:
foo
aabb cc
www-1 1 www
i've been reviewing triggers, and various examples, and for the life of me,
i can't figure out how to modify a field of the row or the item i'm
triggering off of...
any thoughts/comments/pointers would be helpful!!
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: trigger question..
hi joe...
thanks, and what you provided works... but you changed the tbl def...
you're using an int, and you're passing the value to the tbl.
in my situation, i'm using an auto_increment, and i can't seem to use the
create before... as the auto_increment wouldn't be set prior to the
row being created...
any more pointers/thoughts/...
thanks
-Original Message-
From: joe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 7:33 PM
To: 'bruce'; 'mysql list'
Subject: RE: trigger question..
create table foo
(aa varchar(20),
id integer,
cc varchar(20));
delimiter |
create trigger foo_ins before insert on foo
for each row
begin
set new.aa = concat(new.cc,'-',new.id);
end; |
delimiter ;
insert into foo (cc,id) values ('www',1);
select * from foo;
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 7:59 PM
To: 'mysql list'
Subject: trigger question..
hi.
using mysql 5.0.27 and playing with triggers.
a simple db:
create table foo{
aa varchar (10),
bb int auto_increment,
cc varchar (10),
} innondb
i'm trying to figure out how to create a trigger, such that if the user does
an insert into foo (cc) value (www); the table will concat the www with
the 'id' value to produce:
foo
aabb cc
www-1 1 www
i've been reviewing triggers, and various examples, and for the life of me,
i can't figure out how to modify a field of the row or the item i'm
triggering off of...
any thoughts/comments/pointers would be helpful!!
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.17 - Release Date: 12/6/2007 12:00
AM
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.17 - Release Date: 12/6/2007 12:00
AM
--
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]
trigger question...
hi... i have the following test sql/schema. i'm trying to create a trigger that would allow an item in tbl2 to be updated, based upon values from the tbl that's being inserted into, and the value in a 2nd tbl.element. the sql/schema: /* test schema for stratalight file project # # b douglas # #3 creates the database, tbls for the project # the tbl contains the file information for the various systems # drives/users in thew stratalight system/environment # # # # */ drop database if exists jfrank; create database jfrank; use jfrank; /* basic data tbl */ DROP TABLE IF EXISTS masterTestResultStartValTBL; CREATE TABLE masterTestResultStartValTBL ( serverStartVal int(20) not null default '0', serverSepVal int(20) not null default '0', prodVal int(10) not null default '0' ) TYPE=MyISAM DEFAULT CHARSET=latin1; /* result startTBL */ DROP TABLE IF EXISTS masterTestResultStartTBL; CREATE TABLE masterTestResultStartTBL ( hostID int(5) not null default '0', testResultVal int(20) not null default '0', id int(10) NOT NULL auto_increment, PRIMARY KEY (id) ) TYPE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS masterHostTBL; CREATE TABLE masterHostTBL ( host varchar(50) default '', id int(15) NOT NULL auto_increment, PRIMARY KEY (id) ) TYPE=MyISAM DEFAULT CHARSET=latin1; --set @q = masterTestResultStartValTBL.serverStartVal; --set @w = masterTestResultStartValTBL.serverSepVal; delimiter | create trigger mfgtst after insert on masterHostTBL for each row begin set @tmp = 55; insert into masterTestResultStartTBL set hostID = NEW.id, testResultVal = 88; /* set @q = masterTestResultStartValTBL.serverStartVal; set @w = masterTestResultStartValTBL.serverSepVal; */ /* set hostID = NEW.id, testResultVal = 88; */ /* (NEW.id-1)*masterTestResultStartValTBL.serverSepVal; */ end; | delimiter ; -- i can't seem to figure out where/when/how to get the values in the masterTestResultStartValTBL to be used... -- set @q = masterTestResultStartValTBL.serverStartVal; -- set @w = masterTestResultStartValTBL.serverSepVal; when i import the sql... i get an invalid table in the field.. any thoughts/pointers/questions would be appreciated. basically, the app inserts a value in tbl1. i'd like the trigger to be able to then use a value in tbl2, and then compute a final value, that would then be inserted into tbl3. thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log-bin
hi... i'm trying to run mysql on a fedora core 5, system to allow the log bin files to be created. i have the following my.cnf file: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock max_connections=3 #log-error = /var/log/mysql_err.log #set-variable = log=/var/log/mysql_query.log log-bin = /var/log/mysql/mysql_bin.log #set-variable = log-slow-queries=/var/log/mysql_slow.log # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql #basedir=/var/lib basedir=/usr [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- when i run /etc/init.d/mysql start, it dies, and i get an error stating that the mysql_bin.index isn't found: (the mysqld.log file) 071208 11:09:25 mysqld started /usr/libexec/mysqld: File '/var/log/mysql/mysql_bin.index' not found (Errcode: 13) 071208 11:09:25 [ERROR] Aborting if i comment out the log-bin line in the my.cnf file, mysql starts/runs, but i don't get the log bin files. if i run mysqld-safe --log-bin, it apparently creates the log bin files... i can't find the error that i see above when i search the 'net... basically, i'm looking to be able to test using the log bin files for backup purposes. any thought/ideas... thanks -tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: log-bin
arrggg.. hi martin. nope, i tried to add the line to my my.cnf file. in fact, i also copied the bin log files from the /var/lib/... to the /var/log/mysql dir i now have in the my.cnf file log-bin = /var/log/mysql/mysql_bin.log log-bin-index = /var/log/mysql/mysql_bin.index i have the following in the /var/log/mysql dir --- mysql-bin.01 mysql-bin.03 mysql-bin.05 mysql_bin.index mysql-bin.02 mysql-bin.04 mysql-bin.06 --- i get the same err as before... again, if i simply only have the log-bin in the my.cnf, allowing mysql to write things to the default locations then everything starts as expected... thanks -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, December 08, 2007 1:49 PM To: bruce Subject: Re: log-bin Bruce- when log-bin is enabled log-bin = /var/log/mysql/mysql_bin.log specify log-bin-index log-bin-index[=file_name] http://blog.robinz.info/archives/2006/04/13/fixedmysqld-file-mysql-binindex- not-found-errcode-13/ M- - Original Message - From: bruce [EMAIL PROTECTED] To: 'mysql list' mysql@lists.mysql.com Sent: Saturday, December 08, 2007 3:44 PM Subject: log-bin hi... i'm trying to run mysql on a fedora core 5, system to allow the log bin files to be created. i have the following my.cnf file: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock max_connections=3 #log-error = /var/log/mysql_err.log #set-variable = log=/var/log/mysql_query.log log-bin = /var/log/mysql/mysql_bin.log #set-variable = log-slow-queries=/var/log/mysql_slow.log # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql #basedir=/var/lib basedir=/usr [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- when i run /etc/init.d/mysql start, it dies, and i get an error stating that the mysql_bin.index isn't found: (the mysqld.log file) 071208 11:09:25 mysqld started /usr/libexec/mysqld: File '/var/log/mysql/mysql_bin.index' not found (Errcode: 13) 071208 11:09:25 [ERROR] Aborting if i comment out the log-bin line in the my.cnf file, mysql starts/runs, but i don't get the log bin files. if i run mysqld-safe --log-bin, it apparently creates the log bin files... i can't find the error that i see above when i search the 'net... basically, i'm looking to be able to test using the log bin files for backup purposes. any thought/ideas... thanks -tom -- 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: log-bin
oh... btw, also made sure the permissions are set on the /var/log/msql dir, and the underlying files, as well as that the owner/group are 'mysql'... thanks -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Saturday, December 08, 2007 5:39 PM To: 'mysql list' Subject: RE: log-bin arrggg.. hi martin. nope, i tried to add the line to my my.cnf file. in fact, i also copied the bin log files from the /var/lib/... to the /var/log/mysql dir i now have in the my.cnf file log-bin = /var/log/mysql/mysql_bin.log log-bin-index = /var/log/mysql/mysql_bin.index i have the following in the /var/log/mysql dir --- mysql-bin.01 mysql-bin.03 mysql-bin.05 mysql_bin.index mysql-bin.02 mysql-bin.04 mysql-bin.06 --- i get the same err as before... again, if i simply only have the log-bin in the my.cnf, allowing mysql to write things to the default locations then everything starts as expected... thanks -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, December 08, 2007 1:49 PM To: bruce Subject: Re: log-bin Bruce- when log-bin is enabled log-bin = /var/log/mysql/mysql_bin.log specify log-bin-index log-bin-index[=file_name] http://blog.robinz.info/archives/2006/04/13/fixedmysqld-file-mysql-binindex- not-found-errcode-13/ M- - Original Message - From: bruce [EMAIL PROTECTED] To: 'mysql list' mysql@lists.mysql.com Sent: Saturday, December 08, 2007 3:44 PM Subject: log-bin hi... i'm trying to run mysql on a fedora core 5, system to allow the log bin files to be created. i have the following my.cnf file: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock max_connections=3 #log-error = /var/log/mysql_err.log #set-variable = log=/var/log/mysql_query.log log-bin = /var/log/mysql/mysql_bin.log #set-variable = log-slow-queries=/var/log/mysql_slow.log # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql #basedir=/var/lib basedir=/usr [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- when i run /etc/init.d/mysql start, it dies, and i get an error stating that the mysql_bin.index isn't found: (the mysqld.log file) 071208 11:09:25 mysqld started /usr/libexec/mysqld: File '/var/log/mysql/mysql_bin.index' not found (Errcode: 13) 071208 11:09:25 [ERROR] Aborting if i comment out the log-bin line in the my.cnf file, mysql starts/runs, but i don't get the log bin files. if i run mysqld-safe --log-bin, it apparently creates the log bin files... i can't find the error that i see above when i search the 'net... basically, i'm looking to be able to test using the log bin files for backup purposes. any thought/ideas... thanks -tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql replication....
hi... a quick question that i haven't found an answer to. i can use replicate-do-db=foo in a my.cnf file for replication, to replicate the master foo db on the slave. but this requires that i use/have a my.cnf set on the slave. is there a way to dynamically set this attribute/parameter within mysql on the fly. i thought it would be possible via change master to but didn't find the cmd when looking through the mysql information. basically, i'm going to have multiple databases, on multiple systems, that i'm going to be replicating to a single system. so, for each master server, i'd like to be able to set the databases that i'm going to replicate... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql replication....
ok... you guys have convinced me!! my.cnf it is! so, one more question. is there an attribute i can use to run/restart mysql using a given my.cnf file... i can simply have a number of separate my.cnf files, and point to them when i run/restart mysql.. /etc/init.d/mysqld --??? myown.cnf is there an option/attribute for this. thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Baron Schwartz Sent: Tuesday, November 27, 2007 6:50 AM To: bruce Cc: B. Keith Murphy; mysql list Subject: Re: mysql replication You can only do that in the my.cnf file. On Nov 27, 2007 9:50 AM, bruce [EMAIL PROTECTED] wrote: hi keith... i recognize you can't do multiple masters to a single slave with mysql's replication. but you can setup separate mysql slave dbs that are independent, and that yo can then iteratively walk through each slave/master, one at a time, and then do the sync/update for each one... this essentially gets you the slave/master replication for each server, replicated to the slave db on the system. the result is a bunch of different slave dbs, instead of a single db... however, that didn't get me my answer to my question... so, how can you do a replicate-do-db from within the mysql cmd??? in fact, even if i only had a single master, but multiple dbs, i'd still like to know this, given that i might not want to use the my.cnf file... thanks -Original Message- From: B. Keith Murphy [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 6:31 AM To: bruce; 'mysql list' Subject: Re: mysql replication bruce wrote: hi... a quick question that i haven't found an answer to. i can use replicate-do-db=foo in a my.cnf file for replication, to replicate the master foo db on the slave. but this requires that i use/have a my.cnf set on the slave. is there a way to dynamically set this attribute/parameter within mysql on the fly. i thought it would be possible via change master to but didn't find the cmd when looking through the mysql information. basically, i'm going to have multiple databases, on multiple systems, that i'm going to be replicating to a single system. so, for each master server, i'd like to be able to set the databases that i'm going to replicate... thanks Can't do that currently in MySQL. It is called multi-master replication. You can do multi-slave replication which replicates from one master to multiple slaves, but not the other way around. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql replication....
hi keith... i recognize you can't do multiple masters to a single slave with mysql's replication. but you can setup separate mysql slave dbs that are independent, and that yo can then iteratively walk through each slave/master, one at a time, and then do the sync/update for each one... this essentially gets you the slave/master replication for each server, replicated to the slave db on the system. the result is a bunch of different slave dbs, instead of a single db... however, that didn't get me my answer to my question... so, how can you do a replicate-do-db from within the mysql cmd??? in fact, even if i only had a single master, but multiple dbs, i'd still like to know this, given that i might not want to use the my.cnf file... thanks -Original Message- From: B. Keith Murphy [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 6:31 AM To: bruce; 'mysql list' Subject: Re: mysql replication bruce wrote: hi... a quick question that i haven't found an answer to. i can use replicate-do-db=foo in a my.cnf file for replication, to replicate the master foo db on the slave. but this requires that i use/have a my.cnf set on the slave. is there a way to dynamically set this attribute/parameter within mysql on the fly. i thought it would be possible via change master to but didn't find the cmd when looking through the mysql information. basically, i'm going to have multiple databases, on multiple systems, that i'm going to be replicating to a single system. so, for each master server, i'd like to be able to set the databases that i'm going to replicate... thanks Can't do that currently in MySQL. It is called multi-master replication. You can do multi-slave replication which replicates from one master to multiple slaves, but not the other way around. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show slave staus
hi.. simple question!! mysql show slave status returns a number of fields, with information on the status of the slave. is there a way to only return the field(s) i'm interested in... i thought that i had figured this out, but i can't recall, and it's been awhile since i've played with this! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: show slave staus
hi alex.. thanks for the reply. but i thought i had seen a way in mysql, to specify the given field that i want. the show slave status cmd obviously displays a list of fields, so i'm pretty sure that there should be a way of just displaying the targeted field, without having to parse using grep... thanks -Original Message- From: Alex Arul Lurthu [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 7:19 PM To: bruce; mysql list Subject: Re: show slave staus You can set pager command to grep out unwanted fields. On 11/28/07, bruce [EMAIL PROTECTED] wrote: hi.. simple question!! mysql show slave status returns a number of fields, with information on the status of the slave. is there a way to only return the field(s) i'm interested in... i thought that i had figured this out, but i can't recall, and it's been awhile since i've played with this! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sent from Gmail for mobile | mobile.google.com Thanks Alex http://alexlurthu.wordpress.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master/slave replication - errors!!
hi.. doing a simple test of master/slave replication, using mysql. i have two test systems: master - foo (192.168.10.13) slave - cat (192.168.20.20) on both machines, i created a testmasterdb. on the master, i populated the tbl within the db with some test data. there are no tbls in the slave, only the create database... for the master, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-bin=/var/log/mysql/mysql-bin.log binlog-do-db=testmasterdb server-id=11 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid for the slave, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #replication - slave server server_id=2 master_host=mfgtest3.stratalight.com master_user=slave master_password=slave master_connect_retry=60 replicate-do-db=testmasterdb [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid on the master, (logged in as root) i granted replication privileges to my test user (slave/slave). on the slave mysql, i then tried to do a load data from master and got the following error: Error running query on master: Access denied; you need the RELOAD privilege for this operation do i need to run the load data cmd from the slave, when i'm logged in as user slave, i would think that being root would allow me to issue the cmd? do i have to have the physical ipaddress or the master in the my.cnf file? (i would think i could have the fqdn, given that ips change -dhcp) any thoughts/ideas/comments... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL - master/slave replation question
Hi... If I have a master/slave setup, I can do a mysqlshow slave status\G and get information on the overall status of the slave. Is there a way to break out this information without having to parse the output? In other words, are there other cmds that might provide the different pieces of information in a way that won't require me to parse it? Basically, I'm trying to figure out the best approach to being able to automatically look at a mySQL/Slave and determine i it's connected to the master, and if it's relatively up to date, relative to the master. Haven't really seen anything on different sites that speak to this issue. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL - Transaction/Commit Question/Issue
Hi... I'm considering the following issue: need to copy in db1 tbl_1 - tbl_2 and in db2 cat - dog so i need to perform copies of both tbls in the two databases. and i need them to both succeed, or to both be rolled back. the copies are in two separate databases. any thoughts on this... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger/Locking question--
Hi... I'm trying to get me head around a possible situation involving locks/triggers. Suppose I have two tables: FooTBL CatTBL in FooTBL, I have a trigger that operates such that whenever a new row is added to FooTBL, it's immeadiately copied to CatTBL. I'm trying to understand what happens if I do an operation with CatTBL, while FooTBL is trying to write to CatTBL because of the trigger. If CatTBL is in use, does the trigger on FooTBL not get implemented? How does locking CatTBL play a role in this? My basic need is to reliably be able to ensure that everytime a row is added to FooTBL, that it gets copied to CatTBL. At the same time, if I'm doing some query to CatTBL (read/write/delete/update/etc...) that I don't cause an issue with FooTBL or it's trigger(s). Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Triggers/Innodb/Locking
Hi... As I understand mysql, Innodb provides for row level locking, as opposed to myIsam which does tbl level locking. Is this correct? If I am correct, if I have a trigger on a Innodb tbl (foo), such that the trigger then copies a row to another innodb tbl (cat), foo should only do a lock of the row when it's being copied, right? Also, I can then do whatever operations I want to on cat and it want affect, or be affected by foo, unless both cat and foo are doing operations that would result in dealing with the same row on cat, where there could be row level locking/conflicts... Is this more or less how mysql innodb/triggers/locking works? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL - Replication (Master/Slave) Question
Hi... I have a number of servers that I want to treat as Master Servers or the purpose of Replication.. I'd like to have each of the Master, have the Slave DB on the same machine. Ie, a Slave server, might have 10 different Slave Databases/config files, with each of the SlaveDB tied back to the Master Server/DB... However, in looking through various docs, I can only see how to setup a single Slave connection in the my.cnf file. I can't see how to setup multiple Slave connections in the Slave Server, to allow it to handle multiple Masters... So, my basic question is how/what do I need to do? Can I have multiple my.cnf files.. Should everything be placed in a single my.cnf file? Any thoughts/pointers/comments would be helpful!! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL - Replication (Master/Slave) Question
hi... in very basic terms/pics... i have : masterServer1 masterDB1 my.cnf masterServer2 masterDB2 my.cnf . . . masterServerN masterDBN my.cnf this gets me N masterServers, each with it's own my.cnf file, as well as it's own masterDB. I'd like to be able to have the master servers have a slave. Under normal situations (per docs i've seen) the slave would be on a machine, with a slaveDB that matches the masterDB in terms of TBLs, and it's own my.conf to handle the interface between the slave/master. In my situation, I'd like to be able to simply have all the slave DBs, and my.conf information on the same box. Given that I can easily have multiple DBs on a mySQL app, my question appears to come down to how to handle the my.cnf information. I don't see how I can handle multiple my.cnf files that are separate, so is there a way to have all the information for the various slave DBs in the same my.cnf file. Or would I essentially have to have multiple instances of mySQL running, and use a different my.cnf for each instance, which would be a pain!! Or am I tilting at windmills here If this is at all possible, can I get/see a sample my.cnf file illustrating how this can be handled thanks -Original Message- From: Dan Rogart [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 14, 2007 1:07 PM To: Mike Johnson; Baron Schwartz; bruce Cc: mysql list Subject: Re: MySQL - Replication (Master/Slave) Question On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote: Correction to a couple of replies I've seen -- a slave server can have more than one master, but not to the same database. That is, Slave reads Database1 and Database3 from Master1 and also reads Database2 from Master2. You may actually be able to get down to the table level, but I'd have to check on that. Not likely, though. As for how to set it all up, don't ask me. I just enjoy the results. :) (apologies if you get a dupe, Baron -- I accidentally hit reply, not reply-to-all) I would be very interested in hearing more about how you set this up, because as far as I know it's impossible for a slave to have more than one master at any given time. Are you using some kind of time based rotation that changes the master info on the slave periodically or something? -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL - Replication (Master/Slave) Question
Update/Clarification: It's apparent that you need a master-slaveDB, and that I can have multiple slaveDBs on the slave server. But can I setup all the slaveDBs on a single machine. Thanks hi... in very basic terms/pics... i have : masterServer1 masterDB1 my.cnf masterServer2 masterDB2 my.cnf . . . masterServerN masterDBN my.cnf this gets me N masterServers, each with it's own my.cnf file, as well as it's own masterDB. I'd like to be able to have the master servers have a slave. Under normal situations (per docs i've seen) the slave would be on a machine, with a slaveDB that matches the masterDB in terms of TBLs, and it's own my.conf to handle the interface between the slave/master. In my situation, I'd like to be able to simply have all the slave DBs, and my.conf information on the same box. Given that I can easily have multiple DBs on a mySQL app, my question appears to come down to how to handle the my.cnf information. I don't see how I can handle multiple my.cnf files that are separate, so is there a way to have all the information for the various slave DBs in the same my.cnf file. Or would I essentially have to have multiple instances of mySQL running, and use a different my.cnf for each instance, which would be a pain!! Or am I tilting at windmills here If this is at all possible, can I get/see a sample my.cnf file illustrating how this can be handled thanks -Original Message- From: Dan Rogart [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 14, 2007 1:07 PM To: Mike Johnson; Baron Schwartz; bruce Cc: mysql list Subject: Re: MySQL - Replication (Master/Slave) Question On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote: Correction to a couple of replies I've seen -- a slave server can have more than one master, but not to the same database. That is, Slave reads Database1 and Database3 from Master1 and also reads Database2 from Master2. You may actually be able to get down to the table level, but I'd have to check on that. Not likely, though. As for how to set it all up, don't ask me. I just enjoy the results. :) (apologies if you get a dupe, Baron -- I accidentally hit reply, not reply-to-all) I would be very interested in hearing more about how you set this up, because as far as I know it's impossible for a slave to have more than one master at any given time. Are you using some kind of time based rotation that changes the master info on the slave periodically or something? -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger/cron process questions...
Hi. I'm considering a situation where I have a number of child/client servers, each of which are running local apps that feed into a local mysql db/tbl. In order to manage the data, I want to copy all the mysql db/tbl data from the chil/client systems, to a single central/master db. I do not want to simply have the local apps write directly to the central db for a number of reasons. The approach I need, is to write local, and then copy this information from the local mysql, to the central/parent mysql/db on a separate machine. I've considered Replication (Master/Slave) but then realized that you can't have a slave, with multiple masters. In my case, each of the child systems, would be considered to be Masters, with the central machines being the slave. So it appears that the mysql replication isn't suitable. I'm considering simply using cron processes on the child machines, where the cron app would simply fir on a periodic basis, and write any new data from the child db to the central system (assuming the network/central machine is up/running). This kind of process is simple, full proof, and pretty straightfoward to implement. In researching, I've come across articles discussing triggers, and I'm wondering if triggers might prove usefful or this issue. Is it possible to have a periodic trigger, IE a trigger that gets fired based on time. I could have a cron process that updates a tbl on a periodic basis, and a trigger on that tbl. When that trigger fires, it could then update/insert the local data into the remote/central db/tbl. Thoughts/Comments/Pointers/Etc.. .would be helpful. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication - Master/Slave Issue...
Hi... I have a situation where I'm looking at multiple child servers, each of which has it's own mysql/DB/Tbls... I want to be able to have all the information that exists in these tbls, to be captured, and copied to a single parent TBL on a separate machine. So, I'm trying to figure out what's the best way/approach of implementing this. I've been looking at the mysql slave/master replication process, but I'm not sure if this will work, as it appears that you can only have a single slave, for a given master. In my situation, I'm going to have multiple masters. If I were going to implement a master/slave replication process, how could the different masters, be written to a single mysql instance that operates as the slave for all the masters. This seems to violate what I've been reading. Here's my basic scenario: Child Srvr1 Child Srvr2 . . Parent Server . . Child SrvrN Each Child has it's own mysql app, with it's own mysql DB/Tbls... How can I capture/store all the child information and store it in a central (slave) server... Is there a way of making a single slave server accommodate multiple masters?? Thoughts/Comments/Approaches/Pointers will be helpful!! Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication - Master/Slave Issue...
hey baron... in thinking about this... i'm inclined to imagine some kind of process where the client communicates with the parent system, via a cron process. issues that have to be solved relate to ensuring that the client system is able to update the parent system at all times, even in the event that a client system is unable to connect with the network there also has to be a solution to tracking what client systems are on the network, and which of the systems are reporting their information to the parent system. cron processes on the client system could be used to implement processes that would update the parent system with the local client information. thoughts/comments... -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, November 05, 2007 9:55 AM To: bruce Cc: mysql@lists.mysql.com Subject: Re: Replication - Master/Slave Issue... Hi, bruce wrote: Hi... I have a situation where I'm looking at multiple child servers, each of which has it's own mysql/DB/Tbls... I want to be able to have all the information that exists in these tbls, to be captured, and copied to a single parent TBL on a separate machine. So, I'm trying to figure out what's the best way/approach of implementing this. I've been looking at the mysql slave/master replication process, but I'm not sure if this will work, as it appears that you can only have a single slave, for a given master. In my situation, I'm going to have multiple masters. If I were going to implement a master/slave replication process, how could the different masters, be written to a single mysql instance that operates as the slave for all the masters. This seems to violate what I've been reading. Here's my basic scenario: Child Srvr1 Child Srvr2 . . Parent Server . . Child SrvrN Each Child has it's own mysql app, with it's own mysql DB/Tbls... How can I capture/store all the child information and store it in a central (slave) server... Is there a way of making a single slave server accommodate multiple You are correct that a slave can have only one master. The only possible workaround I can think of is to write a process that connects to each master in turn. I have been tossing around this idea as a tool for MySQL toolkit for a while. I think Peter Zaitsev et al at Percona also have a similar tool, which you might contact them about. Or you could roll your own. Other options include terrible icky hacks like Federated tables :-) Baron -- 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: Query question.
you need to group the result sets by date, look at the manual link below: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html Richard Reina wrote: I have a database table paycheck like this. empno, date, gross, fed_with 1234 2007-09-01 1153.85 108.26 1323 2007-09-01 461.54 83.08 1289 2007-09-01 1153.85 94.41 1234 2007-09-15 1153.85 108.26 1323 2007-09-15 491.94 87.18 1289 2007-09-15 1153.8594.41 I can easily do a query like this select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE=2007-09-01; But then I have to do a query for each pay date in the pay period. Accordingly, what would be really useful on a day like today would be to be able to do a query like the following: select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE IS distinct; Does anyone know how to do this? Thanks for the help. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question
there should be no space between function name and () i.e. it should be group_concat(hosts.name) (unless you have the sql mode IGNORE_SPACE set) Andrey Dmitriev wrote: I knew I’ve seen this error before ☺ Thanks a lot. -andrey From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 1:55 AM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 Try lose the space after group_concat. PB Andrey Dmitriev wrote: Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql select service_names.name as 'Service', - group_concat (hosts.name) - from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names - where - hosts.host_id=services.host_id - and service_names.servicename_id=services.servicename_id - group by service_name.name - - - ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seeking a MySQL DB Guru for Project/Partner!!
Hi. We're creating a startup team for creating web based apps/services. The goal is to create business that's initially Sweat Equity based. For those of you not familiar with the term, this means that there will not be any initial compensation/salary, just the thrill of working with some really good people on the team, as we attempt to build a successful business! There will also be equity sharing. Our basic skills cover the web design/developer roles, the marketing/sales roles, and the Linux admin roles. The team is currently 5-6 people in the US/Europe. We need you to be able to fulfill the DB Guru role! The role of the DB Guru will be to make sure that the apps that tie into the database are doing so in an efficient manner, and that we have the ability to scale. You'll be involved with dealing with redundancy issues, efficiency issues, rollover, hardware/software, etc... You should have a background in being able to make MySQL 'sing' with regards to web based implementations. If you're looking to be part of a team, and you're entrepreneurial in nature, we'd like to talk to you. If you aren't the right person, but you know someone who might be, we ask that you please pass this along! Thanks for your time on this. Bruce Douglas [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pointers about replication
Hi Multi-master replication is safely possible with MySQL 5.0 when they introduced auto_increment_increment and auto_increment_offset variables. Before this it was possible to run into problems with auto increment columns generating non-unique numbers between servers. Try the following link for more info: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html hope this helps Adrian Cabbar Duzayak wrote: Hi All, Would it be possible to provide some advanced pointers (articles/books/tutorials/sites) for learning more about replication? I am particularly interested in master-to-master replication (not even sure if this is possible with mysql) and/or real-world usage scenarios/examples as to how much load it can handle, how reliable it is, etc? Any help is appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication - master/slave, distributed db questions...
hi... i have a project and i'm trying to figure out the best approach to architect a solution to resolve the issues i'm facing. i'm open to whatever might be the 'best' solution. keep in mind, this is a 'project' that's my own, kind of a garage function!! i'm creating a distributed web parsing/crawling app. it will consist of a number of nodes in the network whose function is to crawl a site, extract information from the site, and to return the information to the db/tbls for the app. in an effort to speed this whole process, i'm gearing up to being able to have 100s of crawling apps running in a simultaneous manner. this would obviously swamp out a single instance of mysql given the limit of the open connections that you can have. i've started to look at the idea of having a mysql instance on each crawling node within the network. this would allow me to have a kind of round robin approach, so that each crawling/parsing script could write to whatever 'local' mysql db that it finds. this kind of makes sense. i can then import/pull the information from the local dbs to the master db. however, i'm also running into a situation where i might need to delete/flush data written to a local db/tbl by one of the crawling apps in the even the app fails. in this case, i'd essentially have to search each of the 'local' mysql dbs in order to do the flush/delete, as i wouldn't know which db the crawling app that i've killed had been writing to... which is a less than elegant solution. i've looked at docs that talk about master/slave replication/etc... so.. i'm open to a discussion on the potential solutions to this kind of scenario. keep in mind, i'm not a mysql dba/guru., just trying to solve this issue. thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max connections question
hi... i can modify the max_connections param in the my.cnf file... does mysql provide a way for me to essentially guarantee that a given process/user can always access the db, so the process/user doesn't get the 'max connection' error... i thought i had seen something regarding this awhile back, but i can't seem to find any information on it now.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple instances of mysql on a given server
hi... i'm trying to determine if it's safe, or if there are pitfalls to running simultaneous copies of mysql on a given server. assume that the instances are completely separate, dirs/ports/etc... can't really determine from the various information sources on the net. -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql - connections question
hi shawn... mysql permits you to set/modify the number of simultaneous connections via the my.cnf file my assumption is that this is for all the databases for a mysql instance. is this correct? also, what issues might one run into if you have multiple copies of mysql running on a single server? i'm looking at possibly needing multiple mysql instances running so that i can handle the databases, and the connections that i'm going to be dealing with. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group by/select issue..
hi... i have the following test tbl dog name char statusint idint test data dog name status id tom 1 1 tom 2 2 sue 1 3 tom 3 4 sue 2 5 bob 1 6 i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' i'd also like to be able to get a single row for 'bob' if i wanted the 'name' (group) that do not have a status=2. i'm not sure how to craft the select using the group by/distinct, and i couldn't find examples via google to solve this... once i get my hands around this, i can apply it to a test tbl of 2000-3000 rows... thanks bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be created to return the universityID (groups) that don't have an actionID=3... when i tried, SELECT DISTINCT universityID FROM SvnTBL WHERE actionID != 3 i got the same as if i did: SELECT DISTINCT universityID FROM SvnTBL; thanks.. -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:32 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com Subject: Re: group by/select issue.. Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
thanks for the derived tbl approach. it solved my 1st problem/issue. the final query that i used is: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; this works, in that i get the unique universityID data... i now have two additional questions... 1) in the SvnTBL, how can i also get the actionID value? if i attempt to do something like: select distinct s1.universityID, s1.actionID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ONs1.universityID=s2.universityID where s2.universityID is null group by universityID; the query eventually returns with what appears to be the correct information. i get a distinct universityID/actionID, but the query takes ~65 secs to run... the tbl only has ~2900 rows... 2) also, if i want to do a join with another tbl, where i also want to have the select pull information from the joined tbl, is there a 'better' way to handle this... the 2nd tbl is: mysql describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(75) | NO | UNI | NULL|| | svn_dir_name | varchar(50) | NO | | NULL|| | city | varchar(20) | YES | | NULL|| | stateVAL | varchar(5) | NO | | NULL|| | userID| int(10) | NO | | 0 || | ID| int(10) | NO | PRI | NULL| auto_increment | | parsefilename | varchar(50) | NO | | NULL|| | statusID | int(1) | NO | | 1 || +---+-+--+-+-++ 8 rows in set (0.01 sec) the join would take place on SvnTBL.universityID=universityTBL.ID thanks for helping me to see what's going on... my initial approach is to simply do the unique select on only the SvnTBL, and then have an iterative loop through the resulting data, where i then query the universityTBL each time... however, this results in the app having to hit the db a number of times... thoughts/comments/ thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 10:01 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com; 'Peter Bradley' Subject: RE: group by/select issue.. Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query
query question...
hi... continuing with my test (i was able to get the correct information earlier.. thanks to all who helped!) i have the following test tbl/information: dog name char statusint _date timestamp idint test data dog name status _dateid tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 using a query similar: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID,_date from SvnTBL where actionID =5) as s2 ON s1.universityID=s2.universityID where s2.universityID is null and s1.universityID='1000' group by s1.universityID; i can get the unique 'name's that don't have a given actionID. however, i'm now trying to figure out how to craft a query to get the unique 'name' (group) for when the tbl has an actionID=3 with a _date later than the _date of the actionID=4 i've played with the inner 'select' but can't quite seem to get what i'm looking for... any thoughts/comments.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
db/query question...
hi... a further test... the following test tbl/information: dog name char statusint _date timestamp idint test data dog name status _dateid tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 if i do a regular group, i can get (for tom) tom 1 01/20/07 1 tom 2 01/21/07 2 tom 3 01/22/07 4 tom 4 01/23/07 7 sue 1 01/20/07 3 sue 2 01/21/07 5 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... any thoughts/comments.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
db/query question...
hi... a further test... the following test tbl/information: dog name char statusint _date timestamp idint test data dog name status _dateid tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 if i do a regular group, i can get (for tom) tom 1 01/20/07 1 tom 2 01/21/07 2 tom 3 01/22/07 4 tom 4 01/23/07 7 sue 1 01/20/07 3 sue 2 01/21/07 5 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... i imagine that there's a way to accomplish this using subselects. any thoughts/comments.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql question regarding distinct/group by...
hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: socket error
you might also need to check that the my.cnf file is configured to reference the sock file. additionally, you should check to make sure the mysql app/daemon is even running. (this has bit me a few times when i've been tired!!) sample my.cnf file.. [EMAIL PROTECTED] ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = max_connections=3 #set-variable = log-error=/var/log/mysql_err.log #set-variable = log=/var/log/mysql_query.log ##set-variable = log-bin=/var/log/mysql_bin.log #set-variable = log-slow-queries=/var/log/mysql_slow.log # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql #basedir=/var/lib basedir=/usr [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -Original Message- From: Lemuel Formacil [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 6:25 AM To: mysql@lists.mysql.com Subject: Re: socket error On Thursday 21 December 2006 02:25, eng.waleed wrote: hi I have this error on mysql engine Could not connect: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) is there any suggestion my version is 3.23.58 BR I think it's either you don't have read permissions to the socket file '/var/lib/mysql/mysql.sock', or it doesn't exist. Can you check? Lemuel -- 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: socket error
do you know if the mysql daemon is running.. ?? when you do: ps -aux | grep mysql, what do you see. .do you see mysqld? -Original Message- From: eng.waleed [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 8:16 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: socket error the file mysql.sock does not exist what I have to do? - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 21, 2006 4:35 PM Subject: RE: socket error you might also need to check that the my.cnf file is configured to reference the sock file. additionally, you should check to make sure the mysql app/daemon is even running. (this has bit me a few times when i've been tired!!) sample my.cnf file.. [EMAIL PROTECTED] ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = max_connections=3 #set-variable = log-error=/var/log/mysql_err.log #set-variable = log=/var/log/mysql_query.log ##set-variable = log-bin=/var/log/mysql_bin.log #set-variable = log-slow-queries=/var/log/mysql_slow.log # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql #basedir=/var/lib basedir=/usr [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -Original Message- From: Lemuel Formacil [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 6:25 AM To: mysql@lists.mysql.com Subject: Re: socket error On Thursday 21 December 2006 02:25, eng.waleed wrote: hi I have this error on mysql engine Could not connect: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) is there any suggestion my version is 3.23.58 BR I think it's either you don't have read permissions to the socket file '/var/lib/mysql/mysql.sock', or it doesn't exist. Can you check? Lemuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting just the first 2 values
add LIMIT 2 to the end of the statement goose wrote: Hi All, I have the following SQL statement SELECT channel.channel_name, program_title, start_time, finish_time FROM program, channel WHERE program.channel_id='3' AND program.channel_id=channel.channel_id UNION SELECT channel.channel_name, program_title, start_time, finish_time FROM program, channel WHERE program.channel_id='2' AND program.channel_id=channel.channel_id; This produces this: http://pastebin.ca/283519 However how do I tell it to select JUST the first 2 entries for each channel?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: mysqldump slows to crawl
Try dropping the indexes first if you can, would save you about half the time and then re-build them after the dump finishes. Obviously you would need to do it at a quite time though when the DB is not being used. Is a binary backup not an option? at 29G is a large text file to write Ade David Sparks wrote: I'm trying to dump some bigger tables without much luck. Anyone have any advice to dump larger tables? mysqldump starts guns blazing, but quickly it isn't doing anything as viewed by strace. After 1 day trying to dump a MyISAM table with 2.7G .MYD and 5.3G .MYI the dumpfile is 270MB compressed and it seems to be dumping 1K per second. After 12 hours trying to dump an InnoDB table with a 29G .ibd, same problem ... data is trickling out. I'm using mysqldump from 5.0.26 dumping a 4.1.21 server. I've tried several incarnations of options, that latest is (-e, -q *should* be enabled by default): mysqldump -e --no-create-db --skip-add-drop-table -q -single-transaction -v database How to speed this up? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
