AW: creating databases in different folders
You can configure it in the my.cnf file: there you can set the data directory. -Ursprüngliche Nachricht- Von: Foo JH [mailto:jhfoo...@extracktor.com] Gesendet: Dienstag, 2. Juni 2009 05:00 An: mysql@lists.mysql.com Betreff: creating databases in different folders Hi all, I'm using MySQL 5.0 on Windows 2003. Problem background: We use the same server for different applications. All the applications share the same server as the database server. Each application uses their own database. In MSSQL we put each database in the corresponding application folder so that the application root folder contains everything (including the database). Now we're trying to do the same for MySQL as well, but I'm not sure how I can specify that a database should be created in a particular directory (and it's almost always not in C:\Program Files\MySQL Server). Can anyone advise? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=schackenb...@termindoc.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: creating databases in different folders
Benedikt Schackenberg wrote: You can configure it in the my.cnf file: there you can set the data directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: creating databases in different folders
Hi, Benedikt Schackenberg wrote: You can configure it in the my.cnf file: there you can set the data directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? If you don't use InnoDB, then a database IS a folder. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
AW: AW: creating databases in different folders
I think, that will not work with one database daemon. Then you have to install for every database one daemon ;) -Ursprüngliche Nachricht- Von: Foo JH [mailto:jhfoo...@extracktor.com] Gesendet: Dienstag, 2. Juni 2009 11:49 An: schackenb...@termindoc.de Cc: mysql@lists.mysql.com Betreff: Re: AW: creating databases in different folders Benedikt Schackenberg wrote: You can configure it in the my.cnf file: there you can set the data directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=schackenb...@termindoc.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: creating databases in different folders
MySQL represents each database by means of a database directory located within the data directory. You can move a database directory to a location outside the datadirectory and replace it with a symlink. Moving a database directory is very simple. Just follow these steps. (I think you said you were using windows, if you are using *nix give me a shout back, the procedure is different.) 1. Stop the server 2. Move the database directory from the data directory to its new location 3. In the data directory create a file that has a name that is the same as the database name with an extension of .sym This file should contain the full pathname to the database directory location. 4. Restart the server ensuring the --skip-symbolic-links option is not used. Hope this is what you are after. Let me know how much success yoou have with this on Windows. We use symlinking on Linux to distribute load but I have never tried it on Windows. Regards John Daisley MySQL 5 Certified Database Administrator (CMDBA) MySQL 5 Certified Developer (CMDEV) MySQL Certified Associate (CMA) Telephone +44 (0)1283 537111 Mobile +44 (0)7812 451238 Email john.dais...@butterflysystems.co.uk Benedikt Schackenberg wrote: You can configure it in the my.cnf file: there you can set the data directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: creating databases in different folders
You might try and hack something together using NTFS juction points. Unadvisable though, and probably unsupported. Walter On Mon, Jun 1, 2009 at 9:00 PM, Foo JH jhfoo...@extracktor.com wrote: Hi all, I'm using MySQL 5.0 on Windows 2003. Problem background: We use the same server for different applications. All the applications share the same server as the database server. Each application uses their own database. In MSSQL we put each database in the corresponding application folder so that the application root folder contains everything (including the database). Now we're trying to do the same for MySQL as well, but I'm not sure how I can specify that a database should be created in a particular directory (and it's almost always not in C:\Program Files\MySQL Server). Can anyone advise? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- Walter Heck, Consultant @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: creating databases in different folders
John Daisley wrote: MySQL represents each database by means of a database directory located within the data directory. You can move a database directory to a location outside the datadirectory and replace it with a symlink. Thanks for the tip John, esp. the detailed steps to take. It sounds scary (I did some reading and there's some concerns about deleting stuff via Windows Explorer), but I'd give it a shot and update you on the success. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: AW: AW: creating databases in different folders
Guten Tag http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html Das heißt, können wir den Bediener mit a anstellen --Maschinenbordbuchsortierfach Wahl, die eine Position auf einer anderen körperlichen Vorrichtung von der spezifiziert, auf der das Datenverzeichnis liegt. So, die Maschinenbordbücher sind sicher, selbst wenn die Vorrichtung, die das Verzeichnis enthält, verloren ist. arbeitet dieses nicht für Nichtdämon? (raten Sie bitte) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung 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. From: schackenb...@termindoc.de To: jhfoo...@extracktor.com CC: mysql@lists.mysql.com Subject: AW: AW: creating databases in different folders Date: Tue, 2 Jun 2009 12:15:31 +0200 I think, that will not work with one database daemon. Then you have to install for every database one daemon ;) -Ursprüngliche Nachricht- Von: Foo JH [mailto:jhfoo...@extracktor.com] Gesendet: Dienstag, 2. Juni 2009 11:49 An: schackenb...@termindoc.de Cc: mysql@lists.mysql.com Betreff: Re: AW: creating databases in different folders Benedikt Schackenberg wrote: You can configure it in the my.cnf file: there you can set the data directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=schackenb...@termindoc.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows Live™ SkyDrive™: Get 25 GB of free online storage. http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_SD_25GB_062009
Re: AW: AW: creating databases in different folders
From the sound of things, apart from using symbolic links, all MySQL databases MUST reside within the same data root folder. Some further questions: 1. On the *NIX, *BSD platform, do you guys locate the databases in diff folders and link it back to the data root on production systems? 2. If I were to use Innodb, there is NO way to separate the files used by different databases? Benedikt Schackenberg wrote: I think, that will not work with one database daemon. Then you have to install for every database one daemon ;) -Ursprüngliche Nachricht- Von: Foo JH [mailto:jhfoo...@extracktor.com] Gesendet: Dienstag, 2. Juni 2009 11:49 An: schackenb...@termindoc.de Cc: mysql@lists.mysql.com Betreff: Re: AW: creating databases in different folders Benedikt Schackenberg wrote: You can configure it in the my.cnf file: there you can set the data directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: AW: creating databases in different folders
-Original Message- From: John Daisley [mailto:john.dais...@mypostoffice.co.uk] Sent: Tuesday, June 02, 2009 6:30 AM To: Foo JH Cc: schackenb...@termindoc.de; mysql@lists.mysql.com Subject: Re: AW: creating databases in different folders MySQL represents each database by means of a database directory located within the data directory. You can move a database directory to a location outside the datadirectory and replace it with a symlink. Moving a database directory is very simple. Just follow these steps. (I think you said you were using windows, if you are using *nix give me a shout back, the procedure is different.) [JS] So far as I know, Windows supports mount points but not symbolic links. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: AW: creating databases in different folders
-Original Message- From: John Daisley [mailto:john.dais...@mypostoffice.co.uk] Sent: Tuesday, June 02, 2009 6:30 AM To: Foo JH Cc: schackenb...@termindoc.de; mysql@lists.mysql.com Subject: Re: AW: creating databases in different folders MySQL represents each database by means of a database directory located within the data directory. You can move a database directory to a location outside the datadirectory and replace it with a symlink. Moving a database directory is very simple. Just follow these steps. (I think you said you were using windows, if you are using *nix give me a shout back, the procedure is different.) [JS] So far as I know, Windows supports mount points but not symbolic links. You are correct Jerry, Windows does not support symbolic links but for MySQL purposes you can create an .sym file containing the full path to the new location and MySQL will read the file and look in the specified path for the database files. The procedure is covered in the documentation here http://dev.mysql.com/doc/refman/5.0/en/windows-symbolic-links.html __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
AW: AW: creating databases in different folders
Thx john !! i can use this :) -Ursprüngliche Nachricht- Von: John Daisley [mailto:john.dais...@mypostoffice.co.uk] Gesendet: Dienstag, 2. Juni 2009 17:10 An: Jerry Schwartz Cc: john.dais...@butterflysystems.co.uk; 'Foo JH'; schackenb...@termindoc.de; mysql@lists.mysql.com Betreff: RE: AW: creating databases in different folders -Original Message- From: John Daisley [mailto:john.dais...@mypostoffice.co.uk] Sent: Tuesday, June 02, 2009 6:30 AM To: Foo JH Cc: schackenb...@termindoc.de; mysql@lists.mysql.com Subject: Re: AW: creating databases in different folders MySQL represents each database by means of a database directory located within the data directory. You can move a database directory to a location outside the datadirectory and replace it with a symlink. Moving a database directory is very simple. Just follow these steps. (I think you said you were using windows, if you are using *nix give me a shout back, the procedure is different.) [JS] So far as I know, Windows supports mount points but not symbolic links. You are correct Jerry, Windows does not support symbolic links but for MySQL purposes you can create an .sym file containing the full path to the new location and MySQL will read the file and look in the specified path for the database files. The procedure is covered in the documentation here http://dev.mysql.com/doc/refman/5.0/en/windows-symbolic-links.html __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=schackenb...@termindoc.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about query - can this be done?
Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end --- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL University session on June 4: Boosting Performance With MySQL 5.1 Partitioning
Boosting Performance With MySQL 5.1 Partitioning http://forge.mysql.com/wiki/Boosting_Performance_With_MySQL_5.1_Partitioning This Thursday (June 4th, 14:00 UTC), Giuseppe Maxia will give a MySQL University session on Boosting Performance With MySQL 5.1 Partitioning. Giuseppe is leading the Community team at MySQL and has done various MySQL University sessions before. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks: # June 4, 2009: Boosting Performance With MySQL 5.1 Partitioning (Giuseppe Maxia) # June 11, 2009: To be scheduled (original session canceled) # June 18, 2009: No session scheduled # June 15, 2009: MySQL code contributions (Lenz Grimmer) # July 2: Starring Sakila - a data warehouse mini-tutorial (Roland Bouman) # July 9 through September 3: Semester break The schedule is not engraved in stone at this point. Please visit http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the up-to-date list. On that page, you can also find the starting time for many time zones. Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about query - can this be done?
On Tue, Jun 2, 2009 at 11:52 AM, Ray r...@stilltech.net wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 | 4 | t1 | t2 2 | 4 | t3 | t4 3 | 4 | t5 | t6 4 | 5 | t1 | t2 5 | 5 | t3 | t4 becomes id | event_id | start | end | start | end | start | end --- ? | 4 | t1 | t2 | t3 | t4 | t5 | t6 ? | 5 | t1 | t2 | t3 | t4 I think what you are looking for is GROUP_CONCAT. You can just GROUP BY event id, and then process the resulting delimited string on the front end. SELECT event_id, GROUP_CONCAT(start) start_dates, GROUP_CONCAT(end) end_dates FROM events GROUP BY event_id Or even combined start and end dates into a single string and group them. SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id But, if you really want to get it in the column format you indicate, you can make a much more complicated query. Use SUBSTRING_INDEX to split out the parts of the group you need. SELECT event_id, SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 1 ) start1, SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 1 ) end1, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 2 ), ',', -1 ) start2, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 2 ), ',', -1 ) end2, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 3 ), ',', -1 ) start3, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 3 ), ',', -1 ) end3, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 4 ), ',', -1 ) start4, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 4 ), ',', -1 ) end4, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 5 ), ',', -1 ) start5, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 5 ), ',', -1 ) end5 FROM events GROUP BY event_id; I think that will give the format you specified, but I am not recommending you do it this way. Hope that helps. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about query - can this be done?
Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end --- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00
RE: AW: creating databases in different folders
[JS] So far as I know, Windows supports mount points but not symbolic links. You are correct Jerry, Windows does not support symbolic links but for MySQL purposes you can create an .sym file containing the full path to the new location and MySQL will read the file and look in the specified path for the database files. [JS] That's great to know, I've never needed that ability so I never looked into it. I suppose I'll promptly forget it, but you never know what will stick. The procedure is covered in the documentation here http://dev.mysql.com/doc/refman/5.0/en/windows-symbolic-links.html __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql 4.1 vs 5.0
Benching Somebody knows why 4.1 is faster than 5.0 mysql versions Ing. Jaime Fuentes R. 997500459 2421905-2423252 Enviado desde mi BlackBerry de Claro. -Original Message- From: Brent Baisley brentt...@gmail.com Date: Tue, 2 Jun 2009 12:32:39 To: Rayr...@stilltech.net Cc: mysql@lists.mysql.com Subject: Re: Question about query - can this be done? On Tue, Jun 2, 2009 at 11:52 AM, Ray r...@stilltech.net wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 | 4 | t1 | t2 2 | 4 | t3 | t4 3 | 4 | t5 | t6 4 | 5 | t1 | t2 5 | 5 | t3 | t4 becomes id | event_id | start | end | start | end | start | end --- ? | 4 | t1 | t2 | t3 | t4 | t5 | t6 ? | 5 | t1 | t2 | t3 | t4 I think what you are looking for is GROUP_CONCAT. You can just GROUP BY event id, and then process the resulting delimited string on the front end. SELECT event_id, GROUP_CONCAT(start) start_dates, GROUP_CONCAT(end) end_dates FROM events GROUP BY event_id Or even combined start and end dates into a single string and group them. SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id But, if you really want to get it in the column format you indicate, you can make a much more complicated query. Use SUBSTRING_INDEX to split out the parts of the group you need. SELECT event_id, SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 1 ) start1, SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 1 ) end1, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 2 ), ',', -1 ) start2, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 2 ), ',', -1 ) end2, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 3 ), ',', -1 ) start3, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 3 ), ',', -1 ) end3, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 4 ), ',', -1 ) start4, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 4 ), ',', -1 ) end4, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 5 ), ',', -1 ) start5, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 5 ), ',', -1 ) end5 FROM events GROUP BY event_id; I think that will give the format you specified, but I am not recommending you do it this way. Hope that helps. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jfuen...@segursat.com
Replication problems: slave fails to update
I'm trying to resolve a frustrating replication problem with my databases. The master contains a number of schema, only using Innodb tables. Updates happen regularly, usually using bulk inserts of the form INSERT ... ON DUPLICATE UPDATE. Data is mostly numbers. The missing queries contain no non-deterministic functions, no BLOB/TEXT fields, no triggers or procedures, and no variables. The queries are generated by various C programs, connecting through Connector/ODBC. typical table (explain results): 'nodeID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'calculationID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'columnID', 'tinyint(3) unsigned', 'NO', 'PRI', '1', '' 'value', 'double', 'NO', '', '', '' 'lastUpdate', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'on update CURRENT_TIMESTAMP typical bulk insert: INSERT INTO risk.risk_node_tree (nodeID, calculationID, columnID, value, lastUpdate) VALUES (1,2,1,1000,null),... ON DUPLICATE KEY UPDATE value=VALUES(value) Replication is active, and I am able to test it by doing single point modifications through the query browser. However, the bulk inserts seem to vanish: The Master updates, but the slave does not. There are no errors in the log file. SHOW SLAVE STATUS states no problems. I have attempted changing binlog_format, and have received the same results on all three settings. The Master has no settings to ignore any particular schema. Using the query browser, and running the same exact query with the same user, results in the query properly replicating. Both instances are 5.1.34. This is causing me to have to resynchronize the databases every night, which is getting to be something of a chore. Does anyone have any idea what might be happening, or could suggest an avenue of investigation? Any help would be greatly appreciated. Martin -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about query - can this be done?
On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end - -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about query - can this be done?
On June 2, 2009 03:14:36 pm Ray wrote: On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE not sure where this typo came from I meant ON events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray I found a solution, but not sure if it's a good idea. CREATE OR REPLACE VIEW v AS SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id; SELECT * FROM event_details JOIN v ON events.event_id=event_details.event_id Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end --- -- -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 --- - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Question about query - can this be done?
Ray, You can use the results of a query in a join with something like: select tmp.id, t1.id from (some_query_selecting_id) as tmp join t1 on t1.id=tmp.id Hope that helps. Regards, Nathan Sullivan -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Tuesday, June 02, 2009 4:58 PM To: mysql@lists.mysql.com Subject: Re: Question about query - can this be done? On June 2, 2009 03:14:36 pm Ray wrote: On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE not sure where this typo came from I meant ON events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray I found a solution, but not sure if it's a good idea. CREATE OR REPLACE VIEW v AS SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id; SELECT * FROM event_details JOIN v ON events.event_id=event_details.event_id Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end --- -- -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 --- - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[solved]Re: Question about query - can this be done?
On June 2, 2009 04:13:31 pm Nathan Sullivan wrote: Ray, You can use the results of a query in a join with something like: select tmp.id, t1.id from (some_query_selecting_id) as tmp join t1 on t1.id=tmp.id Hope that helps. Regards, Nathan Sullivan Thanks Nathan, I think that completes the picture. Just what I was looking for. Ray -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Tuesday, June 02, 2009 4:58 PM To: mysql@lists.mysql.com Subject: Re: Question about query - can this be done? On June 2, 2009 03:14:36 pm Ray wrote: On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE not sure where this typo came from I meant ON events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray I found a solution, but not sure if it's a good idea. CREATE OR REPLACE VIEW v AS SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id; SELECT * FROM event_details JOIN v ON events.event_id=event_details.event_id Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end - -- -- -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 - -- - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Why doesn't mySQL stop a query when the browser tab is closedL
I just noticed a horrible thing. I have a query (report) that can take 15 minutes or more to generate with mySQL. We have 500 Million rows. This used to be done in real time when we had less rows, but recently we got a big dump of data that shot it up. So, noticing via myTop the query taking so long, I closed my web page tab. The query did NOT go away! WTF? So mysqld continued to peg the CPU at 75% to 135% (yes, top shows that if you have quad cpus. *sigh*) Is there some way to force this to work sanely? Some php.ini or my.cnf file that has a setting to abort queries when the web page has gone away? Not sure which mailing list this belongs on so I'll post to both PHP and mySQL. Although it feels this is a PHP problem as it should know that the Apache thread went away and therefore close the mySQL connection and kill the query. Conversely, mysql should know that it's connection (via PHP) went away and should equally abort. So you're both wrong! :)
Finding the creation date of an existing database in MySQL 5?
Hi all ! Is there any method to find the CREATION DATE of an EXISTING database and tables in MySQL 5.0 or newer versions? Thanks, Uma
Re: Finding the creation date of an existing database in MySQL 5?
Is there any method to find the CREATION DATE of an EXISTING database and tables in MySQL 5.0 or newer versions? information_schema.tables.create_time for tables. PB Uma Bhat wrote: Hi all ! Is there any method to find the CREATION DATE of an EXISTING database and tables in MySQL 5.0 or newer versions? Thanks, Uma No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00
binlog questions
We outputted the bin log using the following command: mysqlbinlog -v --base64-output=DECODE-ROWS oo-mysql1-bin.87 We then looked in this file and found some odd things. For example there is the below insert statement: ### INSERT INTO panel.history ### SET ### @1=-182667600 (4112299696) ### @7=NULL The table structure for history is: CREATE TABLE `history` ( `historyid` int(11) NOT NULL AUTO_INCREMENT, `panelid` int(11) DEFAULT NULL, `projectid` int(11) DEFAULT NULL, `resultid` int(11) DEFAULT NULL, `pulldate` datetime DEFAULT NULL, `resultdate` datetime DEFAULT NULL, `senddate` datetime DEFAULT NULL, `sendcount` int(11) DEFAULT NULL, `bouncecount` int(11) DEFAULT NULL, `link` char(128) DEFAULT NULL, `projectgroupcode` int(11) DEFAULT NULL, `vendorid` int(10) unsigned DEFAULT NULL, `ipaddress` char(15) DEFAULT NULL, `enddate` datetime DEFAULT NULL, `bloodhoundid` int(10) unsigned DEFAULT NULL, `incentive` int(4) unsigned DEFAULT NULL, PRIMARY KEY (`historyid`), KEY `RESULTDATE` (`resultdate`), KEY `PULLDATE` (`pulldate`), KEY `PANELID_PROJECTID` (`panelid`,`projectid`), KEY `PROJECTGROUPCODE` (`projectgroupcode`), KEY `projectid_vendorid` (`projectid`,`vendorid`), KEY `PROJECTIDb` (`projectid`) USING BTREE, KEY `bloodhoundid` (`bloodhoundid`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 So it is trying to put a negative value in for the first column? Then you have stuff like: ### INSERT INTO panel.history ### SET ### @1=1169499418 ### @7=348123-41-35 05:64:02 That looks valid but there is no row in the history table with that historyid so why isn't it there? all in all it seems very inconsistent with the columns it uses and what shows up etc.. any insight would be appreacited 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: Finding the creation date of an existing database in MySQL 5?
Thanks for the response, Peter. Yeah, I am aware of this to find it for the tables. However I require to find the creation time of a database.. Thanks, Uma On 6/3/09, Peter Brawley peter.braw...@earthlink.net wrote: Is there any method to find the CREATION DATE of an EXISTING database and tables in MySQL 5.0 or newer versions? information_schema.tables.create_time for tables. PB Uma Bhat wrote: Hi all ! Is there any method to find the CREATION DATE of an EXISTING database and tables in MySQL 5.0 or newer versions? Thanks, Uma -- No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00
Re: mysql error 2013 Lost connection to MySQL server during query
Per Jessen wrote: It happened agaIn this morning, but slightly different: [snip] thd=0x7fe0140c7e00 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xb, backtrace may not be correct. Bogus stack limit or frame pointer, fp=0xb, stack_bottom=0x4514, thread_stack=262144, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x1355140 = INSERT IGNORE INTO quarantine_archive SELECT * FROM quarantine WHERE state=1 AND domain='example.com' thd-thread_id=1493537 The context is the same as previously, except the query: INSERT IGNORE INTO quarantine_archive SELECT * FROM quarantine WHERE state=1 AND domain='example.com' This is not exactly reproducable, but it is fairly predictable - happens every morning towards 0600 - I have an archive job starting at 0500. For the last three days, the query has been roughly the same, except the 'example.com' varies. Is there nothing I can do to attempt to diagnose crashes such as this? Still no suggestions? /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
