Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a mysqldump question
Hello. Yesterday I try to migrate a database from mysql version is '4.1.5-gamma' to mysql version 3.23.45 using mysqldump: . loki# mysqldump clasificados /usr/backup/clasificados.sql You should remove column and table options. Some of them are new in 4.1 and aren't present in 3.23. Perform a dump using --compatible=mysql323 command line option. If it doesn't help, specify the necessary options manually (like --compatible=no_table_options,no_field_options --skip-opt). See: http://dev.mysql.com/doc/mysql/en/mysqldump.html Migrating from 4.1.x to 3.23.x usually isn't a good idea. Why don't you want to use the latest release (4.1.14 now)? [EMAIL PROTECTED] wrote: Hi all Yesterday I try to migrate a database from mysql version is '4.1.5-gamma' to mysql version 3.23.45 using mysqldump: in the source server I executed: loki# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1955 to server version: 4.1.5-gamma-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use clasificados Database changed mysql show tables; ++ | Tables_in_clasificados | ++ | ad | | advertiser | | advertisers| | banners| | category | | favourites | | payment| | picture| | template | | user | ++ 10 rows in set (0.00 sec) mysql show create table banners; +- +--- ---+ | Table | Create Table | +-+- -+ | banners | CREATE TABLE `banners` ( `bid` int(11) NOT NULL auto_increment, `del` int(11) default NULL, `cid` int(11) default NULL, `btype` int(11) default NULL, `filename` varchar(155) default NULL, `goto_url` varchar(255) default NULL, `status` int(11) default NULL, `hits` int(11) default NULL, `days` int(11) default NULL, `y_day` int(11) default NULL, `clicks` int(11) default NULL, `added` varchar(8) default NULL, `run_for_days` int(11) default NULL, `run_for_hits` int(11) default NULL, `run_for_clicks` int(11) default NULL, `run_in_group` int(11) default NULL, PRIMARY KEY (`bid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-+- -+ 1 row in set (0.00 sec) mysql quit Bye loki# mysqldump clasificados /usr/backup/clasificados.sql loki# ls -la /usr/backup/ total 5588 drwxr-xr-x 2 root wheel 512 Aug 30 16:06 . drwxr-xr-x 19 root wheel 512 Aug 30 16:00 .. -rw-r--r-- 1 root wheel 975599 Aug 30 16:05 clasificados.sql loki# file * clasificados.sql: UTF-8 Unicode text, with very long lines On the target server i eexecuted this: #/usr/local/mysql/bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3816508 to server version: 3.23.45 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database clasificados; Query OK, 1 row affected (0.01 sec) mysql quit Bye # /usr/local/mysql/bin/mysql clasificados clasificados.sql ERROR 1065 at line 7: Query was empty # vi clasificados.sql clasificados.sql Line too long -- MySQL dump 10.7 -- -- Host: localhostDatabase: clasificados -- -- -- Server version 4.1.5-gamma-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; -- -- Table structure for table `ad` -- DROP TABLE IF EXISTS `ad`;
Re: Master Log Files
Hello. Program options can be specified in different ways, check them all. See: http://dev.mysql.com/doc/mysql/en/program-options.html What name does the hostname program reports? Tedy Aulia [EMAIL PROTECTED] wrote: Hi All, I have found that the binlogfiles have changed their name. Initially the files were using the name of the server followed by the number i.e. foo.###. Since two weeks ago the binlog files have changed to be mysql.###. As far as I remember there is no change in my.cnf file so far. I am using MySQL version 3.23.33 Could someone explain why the case I mentioned above has happened? Your help would be appreciated. Cheers, Tedy Aulia -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting Entries
Shaun [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Shaun [EMAIL PROTECTED] wrote on 08/31/2005 04:56:23 PM: [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Shaun [EMAIL PROTECTED] wrote on 08/31/2005 04:04:20 PM: [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Shaun [EMAIL PROTECTED] wrote on 08/31/2005 02:43:33 PM: Hi, This query counts the number of entries in the Bookings table per month, is it possible for this query to return a zero where there are no entries for a particular month? SELECT COUNT(Booking_ID) AS Num_Bookings FROM Bookings WHERE User_ID = 1 AND Work_Type_ID = 12 AND DATE_FORMAT(Booking_Start_Date, %m-%Y) = 04-2005 AND DATE_FORMAT(Booking_Start_Date, %m-%Y) = 06-2005 AND Booking_Type = Booking GROUP BY MONTH(Booking_Start_Date) ORDER BY Booking_Start_Date Thanks for your help. You cannot have missing month values in your query result unless provide values to fill the gaps with. The easiest way to do that is to create a simple lookup table. CREATE TABLE monthList ( id int, name varchar(18) not null ) INSERT monthList (id, name) values (1,'January'),(2,'February'),... fill in the rest...,(12,'December'); Now you can have something to show even if your Bookings data doesn't. Here is how you would use it in your sample query: SELECT m.name, COUNT(b.Booking_ID) AS Num_Bookings FROM monthList m LEFT JOIN Bookings b ON m.id = MONTH(b.Booking_Start_Date) WHERE b.User_ID = 1 AND b.Work_Type_ID = 12 AND b.Booking_Start_Date BETWEEN '2005-04-01' and '2005-06-30 23:59:59' AND b.Booking_Type = Booking GROUP BY m.name ORDER BY m.id; Since I am comparing date values to date values, this should process much faster (especially if Booking_Start_Date is the leftmost column in any index). The short date format works for April 1 because any date constant without a time value is considered to be midnight (00:00:00). I had to include the last second of June 30 so that you would detect bookings that happened on that date (so you searched across the whole day and didn't just stop at midnight at the start of the day). An alternative to using the BETWEEN...AND... comparitor for date ranges is to look for less than the next day. SELECT m.name, COUNT(b.Booking_ID) AS Num_Bookings FROM monthList m LEFT JOIN Bookings b ON m.id = MONTH(b.Booking_Start_Date) WHERE b.User_ID = 1 AND b.Work_Type_ID = 12 AND b.Booking_Start_Date = '2005-04-01' AND b.Booking_Start_Date '2005-07-01' AND b.Booking_Type = Booking GROUP BY m.name ORDER BY m.id; And, just as an example, here is how you would handle the case where you wanted a month-by-month report that crosses over from one year to the next. This should show how many bookings you had in each month for the twelve months starting with April 2004. SELECT m.name as month, YEAR(b.Booking_Start_Date) as year, COUNT(b.Booking_ID) AS Num_Bookings FROM monthList m LEFT JOIN Bookings b ON m.id = MONTH(b.Booking_Start_Date) WHERE b.User_ID = 1 AND b.Work_Type_ID = 12 AND b.Booking_Start_Date = '2004-04-01' AND b.Booking_Start_Date '2005-04-01' AND b.Booking_Type = Booking GROUP BY 1, 2 ORDER BY 1, m.id; (NOTE: I used a MySQL-specific shortcut by using column numbers in the GROUP BY and ORDER BY clauses. A more ANSI way of writing those clauses would have been: GROUP BY YEAR(b.Booking_Start_Date), m.name ORDER BY YEAR(b.Booking_Start_Date), m.id; I think the shortcut method is just a tad easier to read.) HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi Shawn, Thanks for your reply but the query doesnt seem to work for me... mysql SELECT M.Month_Name, - COUNT(B.Booking_ID) AS Num_Bookings - FROM Months M - LEFT JOIN Bookings B - ON M.Month_ID = MONTH(B.Booking_Start_Date) - WHERE B.User_ID = 1799 - AND B.Booking_Start_Date = '2005-01-01' - AND B.Booking_Start_Date '2005-12-01' - AND B.Booking_Type = Booking - GROUP BY M.Month_Name - ORDER BY M.Month_ID; ++--+ | Month_Name | Num_Bookings | ++--+ | May|3 | | June | 22 | | July | 12 | | August | 15 | | September | 13 | | October| 18 | | November |6 | ++--+ 7 rows in set (0.04 sec) mysql Any ideas why this might be happening? It seems to be working just fine.
Re: Bulk Upload.
Hello All, I have a strange situation while doing Bulk upload (using LOAD DATA FILE). I have a database which has 60 records, when I try to insert 1 more records it is taking around 50-55 seconds to complete the task (I feel this is much higher than the normal timings). I notice a strange thing that time taken to upload 1 records reduces to 10 to 12 sec after I run a big query (a query that needs full table scan) on that table. Can someone explain why this happens... I run MySQL 4.1.5 Gamma, on windows 2003 Server OS. Regards, Ramesh Confidentiality Notice: This transmittal is a confidential communication. If you are not the intended recipient, you are hereby notified that you have received this transmittal in error and that any review, dissemination, distribution or copying of this transmittal is strictly prohibited. If you have received this communication in error, please notify this office immediately by reply and immediately delete this message and all of its attachments, if any.
MySQL Subquery bug or am I doing something wrong?
Hi Folks! I have a problem with a subquery using MySQL 4.1.12 (Fedora Core 4). I will include the SHOW CREATE TABLE for the two tables involved. Please, forgive me if I include this information in my original language (Spanish) but I wish to keep the field names as is to avoid any mistake. I don't know if this problem is a MySQL subquery bug or I'm doing something wrong. Anyway, what I'm doing here is pretty straighforward. First, the table information: -- SHOW CREATE TABLE FOR TABLE dominios_propios_completos CREATE TABLE `dominios_propios_completos` ( `criterio` char(100) NOT NULL default '', `idDominio` int(10) unsigned NOT NULL default '0', `idSite` int(10) unsigned default NULL, `fechaCreacion` datetime default NULL, `idIdioma` tinyint(3) unsigned default NULL, `estado` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`criterio`,`idDominio`), KEY `idSite` (`idSite`), KEY `fechaCreacion` (`fechaCreacion`), CONSTRAINT `FK_dominios_propios_completos_1` FOREIGN KEY (`idSite`) REFERENCES `sites` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED -- SHOW CREATE TABLE FOR TABLE sites_criterios CREATE TABLE `sites_criterios` ( `idSite` int(10) unsigned NOT NULL default '0', `criterio` varchar(100) NOT NULL default '', `criterio_pub` varchar(100) default NULL, `idIdioma` int(10) unsigned NOT NULL default '1', KEY `idSite` (`idSite`), KEY `idIdioma` (`idIdioma`), KEY `criterio` (`criterio`), KEY `criterio_pub` (`criterio_pub`), CONSTRAINT `sites_criterios_ibfk_1` FOREIGN KEY (`idSite`) REFERENCES `sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 OK, now for the problem. If you look at the above tables, they are somewhat related via dominios_propios_completos.criterio with sites_criterios.criterio_pub. In fact, what I am doing is trying to make dominios_propios_completos.criterio a constraint for criterio_pub on table sites_criterios. Now, look carefully: mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio = su-turno; +--++ | criterio | idSite | +--++ | su-turno | 4305 | +--++ 1 row in set (0.00 sec) mysql SELECT criterio_pub, idSite - FROM sites_criterios - WHERE criterio_pub = su-turno; Empty set (0.00 sec) In the above queries it's pretty clear the word su-turno exists in table dominios-propios-completos but NOT EXISTS in the table sites_criterios as a value of criterio_pub. The problem is that the following subquery return no results... Why??? mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.05 sec) Why is this? It's obvious (at least to me) that this query must return su-turno at least. It interesting to note that if I further restrict the WHERE clauses, I get the correct output!! Take a look at this, for instance: mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE idSite = 4305 AND - criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - WHERE idSite = 4305 - ); +++ | criterio | idSite | +++ ... cut ... | su-turno | 4305 | +++ 245 rows in set (0.00 sec) I don't know why this last query is working and the former, more general one, isn't. I have tried different combinations, like using DISTINCT at the subquery, etc. I begun thinking this is a bug because I get different results if I modify the WHERE clause in the inner subquery, like this: mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE idSite = 4305 AND - criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.01 sec) Note that this query is THE SAME as the previous one, but with a wider result set inside the subquery. This makes no difference in my queries as we confirmed at least the word su-turno does not exists. Thanks for your patience. If you think I'm doing something wrong please reply. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please I need inputs on Lost connection to MySQL server during query
Hi all, Ive read this link http://dev.mysql.com/doc/mysql/en/gone-away.html; hoping this has something to do with the error... Ive already checked based on the page the list of roots of error to produce gone-away.. w/c i hope leads to Lost connection to MySQL server during query and non of it is present. One thing Ive noticed. When I tried connecting using mysql client from web server to my DB box its takes time before mysql console shows-up after giving the password. Client and Server are connected through a LAN. Based on ping an average of .1 ms reply.. tia, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT alternative
Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. Thanks! -- Good Luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Subquery bug or am I doing something wrong?
Hi, mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.05 sec) Do you have NULL values in sites_criterios.criterio_pub ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT alternative
Vladimir B. Tsarkov wrote: Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. Thanks! take a look at the code of adodb http://adodb.sourceforge.net and to it's implementation of: SelectLimit($sql,$numrows=-1,$offset=-1,$inputarr=false) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Subquery bug or am I doing something wrong?
Hi Pooly! mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.05 sec) Do you have NULL values in sites_criterios.criterio_pub ? Yes Pooly, certainly it had. Thanks for your comment!! This was the problem, again thank you very much. I am now very ashamed as this is a newbie mistake. I was fooled because I was pretty sure I had no NULL values, but you know... I'm not the only one inserting values into the table. :-) Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT alternative
2005/9/1, Vladimir B. Tsarkov [EMAIL PROTECTED]: Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. FTFM : For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. but if you want to be fully compatible with many others RDBMS, go for something like adodb, or some others DB interface in PHP Thanks! -- Good Luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please I need inputs on Lost connection to MySQL server during query
Hi, what is the error in your error log ? Does your server crash for every query it makes ? Or only some ? Are you using the official binary from MySQL or the one used for you distro vendor ? Are you using UDF ? ... We need more inputs ;-) 2005/9/1, JM [EMAIL PROTECTED]: Hi all, Ive read this link http://dev.mysql.com/doc/mysql/en/gone-away.html; hoping this has something to do with the error... Ive already checked based on the page the list of roots of error to produce gone-away.. w/c i hope leads to Lost connection to MySQL server during query and non of it is present. One thing Ive noticed. When I tried connecting using mysql client from web server to my DB box its takes time before mysql console shows-up after giving the password. Client and Server are connected through a LAN. Based on ping an average of .1 ms reply.. tia, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade from 4.0.12 to 4.0.25
Hi List I look up on the online manual for directions for this kind of upgrade but i found nothing. Is there something I have to do extra or the upgrade so smoth? Osvaldo Sommer -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005
Re: Index update process 20+ hrs
It does not seem fine to me, mysql seems to really get slow once you break about the 3 gig myd size, or 1.5 gig myi size not sure why, I've changed every setting, but nothing seems to help Matt Clyde Lewis wrote: Matt, Looking at how the database if currently configured, do you have any suggestions or does it all seem fine. CL matt_lists wrote: Get used to it, I've got similar tables, mine have less columns, but more records, my biggest takes a week to restore a mysqldump Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 4.0.12 to 4.0.25
Osvaldo Sommer wrote: Hi List I look up on the online manual for directions for this kind of upgrade but i found nothing. Is there something I have to do extra or the upgrade so smoth? Osvaldo Sommer See http://lists.mysql.com/mysql/186726 -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. It's not the client. That's how InnoDB works. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). I'll make no comments on how Oracle works, but what you seem to be describing is effectively what happens with AUTOCOMMIT on in MySQL. In general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for example) to behave in exactly the same way will usually get you in trouble. David Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting Entries
Shaun [EMAIL PROTECTED] wrote on 09/01/2005 05:38:01 AM: big snip -- see earlier posts in this thread for details The corrected query should read: SELECT m.name as month, YEAR(b.Booking_Start_Date) as year, COUNT(b.Booking_ID) AS Num_Bookings FROM monthList m LEFT JOIN Bookings b ON m.id = MONTH(b.Booking_Start_Date) AND b.User_ID = 1 AND b.Work_Type_ID = 12 AND b.Booking_Start_Date = '2004-04-01' AND b.Booking_Start_Date '2005-04-01' AND b.Booking_Type = Booking GROUP BY 1, 2 ORDER BY 1, m.id; I am so very sorry! Modify my other examples in the same way and they should work too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks Shawn :) Hi Shawn, This is all working fine except where a booking spans more than one day i.e. a holiday. I have a column called Booking_End_Date, is there a way to count all the days used where a booking spans more than one day? Thanks for your help. That's a different question, isn't it. Your first question asked to count all of the bookings where the starting date was within a certain date range. Your second question might be rephrased as: Tell me how many days were booked within a certain date range. See the difference between the two functions? One is a simple count of how many rows are in the table, the other is the sum of bookable days. What functions we can use to make that calculation easier depend on what version MySQL you are running. What version MySQL are you running? Is this a simple date span calculation or do we need a list of holidays so that if a holiday occurs within the span covered by Booking_Start_Date to Booking_End_Date it won't count as a booked day? Do you count weekends too? Covering the cases of where a booking starts in one month and ends in another can also get complicated (oh, it's do-able but it's not as simple as you might think). The more exceptions you want to add into this formula, the more convoluted it becomes and the more processing required to reach the answer you want. Plus, there is definitely more than one solution so if one way is too slow or cumbersome or complex, try another. One of the ways to simplify this calculation is to not enter bookings across date spans but enter them into the table as a booking for each day. That way you just don't enter a booking for holidays and weekends. Since each booking has the same customer_id or event_id (or both) you can tell instantly how many days were booked for any event or customer within any month. In this suggestion, complex SQL wasn't the solution but changing the way you store the data was. If you are locked into your current storage plan, then you are left with complex SQL processing to get at your data. One way to do the multiple-date-entries-per-booking method is to create a new table, `bookingdates`, that stores the id of the booking and the date for the booking. Bookings that span multiple dates would have multiple entries. CREATE TABLE bookingdates ( booking_id int not null , bookingdate datetime not null , UNIQUE (booking_ID, bookingdate) , key(bookingdate, booking_ID) ); The indexes are called covering indexes and will basically allow you to load the entire table into memory twice. Once sorted by booking_ID, the other sorted by bookingdate. Because the index contains all of the data you could want from a query, the datafile will not need to be touched to do the actual retrieval. One less random seek and read on the disk means much faster performance to your application. Just make sure that you do not create entries where bookings do not happen (like holidays and weekends). This table, properly filled in, makes not just your first query but also your second query trivial to answer. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Please I need inputs on Lost connection to MySQL server during query
JM [EMAIL PROTECTED] wrote on 09/01/2005 06:01:57 AM: Hi all, Ive read this link http://dev.mysql.com/doc/mysql/en/gone-away. html hoping this has something to do with the error... Ive already checked based on the page the list of roots of error to produce gone-away.. w/c i hope leads to Lost connection to MySQL server during query and non of it is present. One thing Ive noticed. When I tried connecting using mysql client from web server to my DB box its takes time before mysql console shows-up after giving the password. Client and Server are connected through a LAN. Based on ping an average of .1 ms reply.. tia, OK, we have the error message (good). We know what you have looked at to solve the error (also good). What we don't have is what you did to create the error in the first place. Please describe what you are doing to create the error, the data you are doing it with, and the tools involved in both ends of the process. Then we can understand your problem. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: LIMIT alternative
Vladimir B. Tsarkov [EMAIL PROTECTED] wrote on 09/01/2005 06:16:12 AM: Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. Thanks! -- Good Luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html Not that I know if. MySQL uses limit, MS SQL Server uses top, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Counting Entries
[EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Shaun [EMAIL PROTECTED] wrote on 09/01/2005 05:38:01 AM: big snip -- see earlier posts in this thread for details The corrected query should read: SELECT m.name as month, YEAR(b.Booking_Start_Date) as year, COUNT(b.Booking_ID) AS Num_Bookings FROM monthList m LEFT JOIN Bookings b ON m.id = MONTH(b.Booking_Start_Date) AND b.User_ID = 1 AND b.Work_Type_ID = 12 AND b.Booking_Start_Date = '2004-04-01' AND b.Booking_Start_Date '2005-04-01' AND b.Booking_Type = Booking GROUP BY 1, 2 ORDER BY 1, m.id; I am so very sorry! Modify my other examples in the same way and they should work too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks Shawn :) Hi Shawn, This is all working fine except where a booking spans more than one day i.e. a holiday. I have a column called Booking_End_Date, is there a way to count all the days used where a booking spans more than one day? Thanks for your help. That's a different question, isn't it. Your first question asked to count all of the bookings where the starting date was within a certain date range. Your second question might be rephrased as: Tell me how many days were booked within a certain date range. See the difference between the two functions? One is a simple count of how many rows are in the table, the other is the sum of bookable days. What functions we can use to make that calculation easier depend on what version MySQL you are running. What version MySQL are you running? Is this a simple date span calculation or do we need a list of holidays so that if a holiday occurs within the span covered by Booking_Start_Date to Booking_End_Date it won't count as a booked day? Do you count weekends too? Covering the cases of where a booking starts in one month and ends in another can also get complicated (oh, it's do-able but it's not as simple as you might think). The more exceptions you want to add into this formula, the more convoluted it becomes and the more processing required to reach the answer you want. Plus, there is definitely more than one solution so if one way is too slow or cumbersome or complex, try another. One of the ways to simplify this calculation is to not enter bookings across date spans but enter them into the table as a booking for each day. That way you just don't enter a booking for holidays and weekends. Since each booking has the same customer_id or event_id (or both) you can tell instantly how many days were booked for any event or customer within any month. In this suggestion, complex SQL wasn't the solution but changing the way you store the data was. If you are locked into your current storage plan, then you are left with complex SQL processing to get at your data. One way to do the multiple-date-entries-per-booking method is to create a new table, `bookingdates`, that stores the id of the booking and the date for the booking. Bookings that span multiple dates would have multiple entries. CREATE TABLE bookingdates ( booking_id int not null , bookingdate datetime not null , UNIQUE (booking_ID, bookingdate) , key(bookingdate, booking_ID) ); The indexes are called covering indexes and will basically allow you to load the entire table into memory twice. Once sorted by booking_ID, the other sorted by bookingdate. Because the index contains all of the data you could want from a query, the datafile will not need to be touched to do the actual retrieval. One less random seek and read on the disk means much faster performance to your application. Just make sure that you do not create entries where bookings do not happen (like holidays and weekends). This table, properly filled in, makes not just your first query but also your second query trivial to answer. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi Shawn, I am using 4.1.9. The system has been up and running for 3 years now so I am 'fairly' locked in but if your suggestion will make things easier in the future then its worth the effort. However we already keep unavailability in the bookings table so the we can identify time off i.e. holiday sickness etc. Bookings can never be made at the weekend and that is handled at the application layer although a booking could span a weekend - this could be rectified. Unavailability descriptions are help in a seperate table. The reason I started this was because I was asked to produce a report detailing the capacity worked by staff i.e. ( (days worked + days unavailable) / days available in month(i.e. working days) ) * 100 = capacity worked I have emailed a copy to you so you can see what I mean - hope you don't mind! Thanks for your help. -- MySQL General Mailing List For list
database problem
__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: cannot create database Description: I have already set the path to c:\mysql\bin and would like to create a database. When i enter the command creta database feedback; it comes out this error 'create' is not recognized as an internal or external command, operable program or batch file How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Cannot create database Submitter-Id: [EMAIL PROTECTED] Originator: Diana Organization: Student from Multimedia Universuty MySQL support: licence Severity: non-critical Priority: medium | high Category: mysql client Class: sw-bug doc-bug change-request support Release:mysql-3.23.38 Exectutable: [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt] Environment: machine description System:XP Compiler: VC++ 6.0 Architecture: i -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Upgrade from 4.0.12 to 4.0.25
Thanks, but that link is for 4.1.x and not for 4.0.x Osvaldo Sommer -Original Message- From: Nuno Pereira [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 7:38 AM To: Osvaldo Sommer Cc: mysql@lists.mysql.com Subject: Re: Upgrade from 4.0.12 to 4.0.25 Osvaldo Sommer wrote: Hi List I look up on the online manual for directions for this kind of upgrade but i found nothing. Is there something I have to do extra or the upgrade so smoth? Osvaldo Sommer See http://lists.mysql.com/mysql/186726 -- Nuno Pereira -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DIV-function?
Martijn Tonies wrote: I wonder how I'll get a div-function in SQL? I dunno, maybe by looking in the manual? From [1]: Division: mysql SELECT 3/5; - 0.60 [1] http://dev.mysql.com/doc/mysql/en/arithmetic-functions.html Gustav is looking for the DIV-function. But it is there |DIV| Integer division. Similar to |FLOOR()| but safe with |BIGINT| values. He never said he wanted integer division... he just said I want to divide t[w]o columns and make a new column based on the result. Maybe Gustav can say if this is what he wants, but it seems that it is. Ehm, no. He specifically asked for a DIV function, not a division. :-) See above. With regards, Martijn Tonies -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database problem
dEeZAcK SweETtY wrote: __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: cannot create database Description: I have already set the path to c:\mysql\bin and would like to create a database. When i enter the command creta database feedback; it comes out this error 'create' is not recognized as an internal or external command, operable program or batch file How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Cannot create database Submitter-Id: [EMAIL PROTECTED] Originator: Diana Organization: Student from Multimedia Universuty MySQL support: licence Severity: non-critical Priority: medium | high Category: mysql client Class: sw-bug doc-bug change-request support Release: mysql-3.23.38 Exectutable: [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt] Environment: machine description System:XP Compiler: VC++ 6.0 Architecture: i You are trying to run mysql client commands from the Windows/Dos command line. But: 1. Delete the spam. If it looks like spam why read it? 2. Do not send attachments. Many people do not read attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql embedded on palm?
MySQL on Palm. It's possible? Anyone have just tried? Any suggestion will be appreciated. Paolo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database problem
dEeZAcK SweETtY [EMAIL PROTECTED] wrote on 09/01/2005 10:27:28 AM: __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: cannot create database Description: I have already set the path to c:\mysql\bin and would like to create a database. When i enter the command creta database feedback; it comes out this error 'create' is not recognized as an internal or external command, operable program or batch file How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Cannot create database Submitter-Id: [EMAIL PROTECTED] Originator: Diana Organization: Student from Multimedia Universuty MySQL support: licence Severity: non-critical Priority: medium | high Category: mysql client Class: sw-bug doc-bug change-request support Release: mysql-3.23.38 Exectutable: [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt] Environment: machine description System:XP Compiler: VC++ 6.0 Architecture: i This is not a bug but a training issue. Please, read the fine manual (RTFM). Not only does it tell you, step-by-step, how to set up a MySQL installation on XP but actually walks you step-by-step through some of the more common tasks of using it. Installation: http://dev.mysql.com/doc/mysql/en/windows-installation.html Basic tutorial: http://dev.mysql.com/doc/mysql/en/tutorial.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Connections with bad DNS cause lockups
Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/dns.html You may want to start mysqld with the --skip-name-resolve option. Hi, Thanks for the reply. I did see that page before, but I guess my bigger question is why if the DNS is broken/slow, why does the entire server come to a COMPLETE halt, no commands can be done via either TCP *OR* the socket. If it just errored, that session took forever, whatever... I could understand. The problem is that when it gets probed, it COMPLETELY offlines (DOS) the server. And just *1* connection! Just also seems difficult to keep proper documentation if we are using IPs and not complete hostnames. Thanks, Tuc Tuc at T-B-O-H [EMAIL PROTECTED] wrote: Hi, We seem to be running into a problem with our installation that we don't understand. We are running mysql-server-4.0.25 from the ports collection on a FreeBSD 5.3-RELEASE-p10 machine. Its tcpwrapper'd to only allow from our /24, and a single machine outside the /24. At times, all of a sudden the server seems to freeze. It appears that we've narrowed it down to an issue with people attacking the server that come from a site that has a bad reverse DNS setup. Has anyone else seen this, or knows how to stop it? Thanks, Tuc -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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: Connections with bad DNS cause lockups
(please, either top-post or bottom-post but don't mix it up) One way to bypass a broken DNS server is to create complete HOSTS files on your servers. That way you can keep using your hostnames but avoid the problems of actual DNS server negotiations going sour as all hostname to IP address translations are handled locally. This is especially useful for resolving internal names for resources that rarely change addresses (like servers and most users). Then, the only names that pose a risk would be those not on the list. It's not a perfect solution but it may keep you going until MySQL can figure out something better to deal with misbehaving DNS servers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tuc at T-B-O-H [EMAIL PROTECTED] wrote on 09/01/2005 10:56:24 AM: Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/dns.html You may want to start mysqld with the --skip-name-resolve option. Hi, Thanks for the reply. I did see that page before, but I guess my bigger question is why if the DNS is broken/slow, why does the entire server come to a COMPLETE halt, no commands can be done via either TCP *OR* the socket. If it just errored, that session took forever, whatever... I could understand. The problem is that when it gets probed, it COMPLETELY offlines (DOS) the server. And just *1* connection! Just also seems difficult to keep proper documentation if we are using IPs and not complete hostnames. Thanks, Tuc Tuc at T-B-O-H [EMAIL PROTECTED] wrote: Hi, We seem to be running into a problem with our installation that we don't understand. We are running mysql-server-4.0.25 from the ports collection on a FreeBSD 5.3-RELEASE-p10 machine. Its tcpwrapper'd to only allow from our /24, and a single machine outside the /24. At times, all of a sudden the server seems to freeze. It appears that we've narrowed it down to an issue with people attacking the server that come from a site that has a bad reverse DNS setup. Has anyone else seen this, or knows how to stop it? Thanks, Tuc -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com
Re: Connections with bad DNS cause lockups
Hi, (Lets not get into top/bottom/mixed post discussions. :) ) I'm not sure why putting in hosts would make a difference. Doesn't the --skip-name-resolve bypass any sort of name resolution, be it /etc/hosts or resolver? Or are you telling me to change nsswitch.conf from hosts: files dns to just hosts: files? Would be a SLIM possibility, if this wasn't a machine that didn't only do MySQL and other things in my TCPWrappers (/etc/hosts.allow) did partial domain matching for clients. Thanks, Tuc (please, either top-post or bottom-post but don't mix it up) One way to bypass a broken DNS server is to create complete HOSTS files on your servers. That way you can keep using your hostnames but avoid the problems of actual DNS server negotiations going sour as all hostname to IP address translations are handled locally. This is especially useful for resolving internal names for resources that rarely change addresses (like servers and most users). Then, the only names that pose a risk would be those not on the list. It's not a perfect solution but it may keep you going until MySQL can figure out something better to deal with misbehaving DNS servers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tuc at T-B-O-H [EMAIL PROTECTED] wrote on 09/01/2005 10:56:24 AM: Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/dns.html You may want to start mysqld with the --skip-name-resolve option. Hi, Thanks for the reply. I did see that page before, but I guess my bigger question is why if the DNS is broken/slow, why does the entire server come to a COMPLETE halt, no commands can be done via either TCP *OR* the socket. If it just errored, that session took forever, whatever... I could understand. The problem is that when it gets probed, it COMPLETELY offlines (DOS) the server. And just *1* connection! Just also seems difficult to keep proper documentation if we are using IPs and not complete hostnames. Thanks, Tuc Tuc at T-B-O-H [EMAIL PROTECTED] wrote: Hi, We seem to be running into a problem with our installation that we don't understand. We are running mysql-server-4.0.25 from the ports collection on a FreeBSD 5.3-RELEASE-p10 machine. Its tcpwrapper'd to only allow from our /24, and a single machine outside the /24. At times, all of a sudden the server seems to freeze. It appears that we've narrowed it down to an issue with people attacking the server that come from a site that has a bad reverse DNS setup. Has anyone else seen this, or knows how to stop it? Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connections with bad DNS cause lockups
I don't know how your OS does it or what you need to do to use it, but if I have a file called HOSTS in my (c:\winnt\system32\drivers\etc\ directory (each OS has a similar location for this file)) that contains a list of hostname - IP address pairs then whenever I attempt to do a hostname resolution, my IP stack will use that file *first* before attempting to contact a DNS server. If it finds the hostname in the HOSTS file then it never calls a DNS server. I am suggesting that you populate a hosts file suitable to resolve the hostnames to ip addresses for your user base. That way you should be able to remove --skip-name-resolve (allowing MySQL to do hostname-based security) and not run into the issue of a DNS server becoming flaky unless someone with a hostname NOT in the list tries to login. However, if your users are always getting new IP addresses (some places are like that) then this workaround won't work for you. It may not work at all but I thought it was worth a shot. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tuc at T-B-O-H [EMAIL PROTECTED] wrote on 09/01/2005 11:24:24 AM: Hi, (Lets not get into top/bottom/mixed post discussions. :) ) I'm not sure why putting in hosts would make a difference. Doesn't the --skip-name-resolve bypass any sort of name resolution, be it /etc/hosts or resolver? Or are you telling me to change nsswitch.conf from hosts: files dns to just hosts: files? Would be a SLIM possibility, if this wasn't a machine that didn't only do MySQL and other things in my TCPWrappers (/etc/hosts.allow) did partial domain matching for clients. Thanks, Tuc (please, either top-post or bottom-post but don't mix it up) One way to bypass a broken DNS server is to create complete HOSTS files on your servers. That way you can keep using your hostnames but avoid the problems of actual DNS server negotiations going sour as all hostname to IP address translations are handled locally. This is especially useful for resolving internal names for resources that rarely change addresses (like servers and most users). Then, the only names that pose a risk would be those not on the list. It's not a perfect solution but it may keep you going until MySQL can figure out something better to deal with misbehaving DNS servers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tuc at T-B-O-H [EMAIL PROTECTED] wrote on 09/01/2005 10:56:24 AM: Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/dns.html You may want to start mysqld with the --skip-name-resolve option. Hi, Thanks for the reply. I did see that page before, but I guess my bigger question is why if the DNS is broken/slow, why does the entire server come to a COMPLETE halt, no commands can be done via either TCP *OR* the socket. If it just errored, that session took forever, whatever... I could understand. The problem is that when it gets probed, it COMPLETELY offlines (DOS) the server. And just *1* connection! Just also seems difficult to keep proper documentation if we are using IPs and not complete hostnames. Thanks, Tuc Tuc at T-B-O-H [EMAIL PROTECTED] wrote: Hi, We seem to be running into a problem with our installation that we don't understand. We are running mysql-server-4.0.25 from the ports collection on a FreeBSD 5.3-RELEASE-p10 machine. Its tcpwrapper'd to only allow from our /24, and a single machine outside the /24. At times, all of a sudden the server seems to freeze. It appears that we've narrowed it down to an issue with people attacking the server that come from a site that has a bad reverse DNS setup. Has anyone else seen this, or knows how to stop it? Thanks, Tuc
install_driver (mysql) failed
We are running mysql 4.1.13 on solaris 9 box. Everything was fine until someone accidentally deleted the mysql directory where the application was installed. I had to retrieve it from our backup tape. I then stopped and started mysqld daemon. Please note that we had installed mysql with the binary tar file. Since we restored the mysql application directory form our backup we are getting the following error with our databases that interfaces with perl in the cgi-bin. Software error: install_driver(mysql) failed: Can't load '/usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: perl: fatal: libmysqlclient.so.10: open failed: No such file or directory at /usr/local/lib/perl5/5.8.0/sun4-solaris/DynaLoader.pm line 229. at (eval 4) line 3 Compilation failed in require at (eval 4) line 3. Perhaps a required shared library or dll isn't installed where expected at /PATH/PATH/PATH/cgi-bin/filename.cgi line 55 I would appreciate some help as soon as possible. Thank you. URR.
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
I believe you - I'm just a but surprised. I guess I had a singular view of how a session should work based on Oracle. I would have expected that until you execute SQL that requires a commit or a rollback, you wouldn't be in a transaction. Unfortunately, if you have connections that are read and write, and one connection ends up being used for SELECTs only (just bad luck) , it's going to have an out-date view of the database. To me, a transaction is something you commit or rollback. You can't commit or rollback a SELECT unless you've done a locking-read. I guess Oracle is just smarter about it, only starting a transaction behind the scenes if you've actually done something that warrants a transaction. David Michael Stassen wrote: David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. It's not the client. That's how InnoDB works. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). I'll make no comments on how Oracle works, but what you seem to be describing is effectively what happens with AUTOCOMMIT on in MySQL. In general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for example) to behave in exactly the same way will usually get you in trouble. David Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
If you are NOT in autocommit mode, your connection (or the server, it doesn't matter which) starts a transaction *when you issue your first command*. Every command you issue on that connection is in that initial transaction until you EXPLICITLY commit or rollback (or do something else that commits or rolls-back your transactions like ALTER TABLE) . At that point a new transaction is automatically started when you issue your next command. If I remember correctly, closing a connection with a pending transaction defaults to a ROLLBACK. That way if a transaction is left incomplete due to communications failure, you maintain a consistent database. If autocommit is enabled (SET autocommit=1) then each command executes within it's own mini-transaction (one little, tight transaction wrapped around each statement). Each SELECT can see what every other INSERT, UPDATE, or DELETE has done (assuming their transactions are committed) because it is not already inside a pending transaction. This is the default mode for user interaction for nearly every database product I have used. With autocommit active, you are required to explicitly issue a START TRANSACTION if you want a transaction that includes several commands. Are you sure that's not how Oracle operates, too? I ask because MS SQL acts the same as MySQL when it comes to autocommits Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM: I believe you - I'm just a but surprised. I guess I had a singular view of how a session should work based on Oracle. I would have expected that until you execute SQL that requires a commit or a rollback, you wouldn't be in a transaction. Unfortunately, if you have connections that are read and write, and one connection ends up being used for SELECTs only (just bad luck) , it's going to have an out-date view of the database. To me, a transaction is something you commit or rollback. You can't commit or rollback a SELECT unless you've done a locking-read. I guess Oracle is just smarter about it, only starting a transaction behind the scenes if you've actually done something that warrants a transaction. David Michael Stassen wrote: David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or- rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you
Install error on fedora Core3
Hey all, Trying to upgrade MySQL from 3.23 to 4.0.16 on a new fedora core3 system. I keep getting the following when I run the servers-standard rpm... Preparing...### [100%] 1:MySQL-server-standard ### [100%] 050901 17:33:34 [ERROR] Can't create interrupt-thread (error 13, errno: 13) Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/sbin/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! Starting MySQL...[FAILED] Anyone have this problem and know how to fix it??? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
Yah, I tested in SQL*Plus - one window could see inserts, updates and
deletes that had been committed in another window (in which a commit or
rollback had not been issued). I ran the test again - delete data from a
table in one window and commit the change, and a select in the other
window displays the results.
Note that SQL*Plus by default does not auto-commit, but the key elements
of the test are the same. Data committed in one session is visible in
another session once committed.
In Oracle/SQL*Plus, data committed in session A will show up in Session
B if Session B has an open transaction. Here's the example (using
session A and B).
Session A:
insert into temp_table (col1) values ('a');
Session B:
insert into temp_table (col1) values ('b');
At this point, neither is committed, and neither session can see what's
the other has done (the left hand doesn't know what the right is doing,
so to speak).
Session A:
commit;
Session B:
SQL select * from temp_table;
C
-
b
a
Session B has an open transaction, yet can see the data that was
committed in another transaction. It's view of the data is, Show me all
the data that has been committed to the database at the point where I
started my query, plus all changes that I've made yet not committed or
rolled back.
Oracle runs in READ COMMITTED (the above), while INNODB runs in
REPEATABLE READ. Big difference. And I (stupidly) assumed they ran as
the same transaction isolation level.
Learn something new every day.
David
[EMAIL PROTECTED] wrote:
If you are NOT in autocommit mode, your connection (or the server, it
doesn't matter which) starts a transaction *when you issue your first
command*. Every command you issue on that connection is in that
initial transaction until you EXPLICITLY commit or rollback (or do
something else that commits or rolls-back your transactions like ALTER
TABLE) . At that point a new transaction is automatically started when
you issue your next command. If I remember correctly, closing a
connection with a pending transaction defaults to a ROLLBACK. That way
if a transaction is left incomplete due to communications failure, you
maintain a consistent database.
If autocommit is enabled (SET autocommit=1) then each command
executes within it's own mini-transaction (one little, tight
transaction wrapped around each statement). Each SELECT can see what
every other INSERT, UPDATE, or DELETE has done (assuming their
transactions are committed) because it is not already inside a pending
transaction. This is the default mode for user interaction for nearly
every database product I have used. With autocommit active, you are
required to explicitly issue a START TRANSACTION if you want a
transaction that includes several commands.
Are you sure that's not how Oracle operates, too? I ask because MS SQL
acts the same as MySQL when it comes to autocommits
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM:
I believe you - I'm just a but surprised. I guess I had a singular view
of how a session should work based on Oracle. I would have expected
that
until you execute SQL that requires a commit or a rollback, you
wouldn't
be in a transaction. Unfortunately, if you have connections that are
read and write, and one connection ends up being used for SELECTs only
(just bad luck) , it's going to have an out-date view of the database.
To me, a transaction is something you commit or rollback. You can't
commit or rollback a SELECT unless you've done a locking-read. I guess
Oracle is just smarter about it, only starting a transaction behind the
scenes if you've actually done something that warrants a transaction.
David
Michael Stassen wrote:
David Griffiths wrote:
No, with the default transaction isolation level, REPEATABLE READ,
that's how it is supposed to work. You've started a transaction in
Window B, so Window B is immune to changes made in Window A until
you
finish the transaction in Window B. See the manual for details
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;
I haven't explicitly started any transactions in Window B - it's
select-only (with autocommit set to 0). Are you saying that even
though
transactions have happend and been committed in Window A, I won't be
able
to see those transactions in Window B?
The key word is explicitly. You have implicitly started a
transaction with your first SELECT, precisely because you turned
AUTOCOMMIT off. That transaction continues until you COMMIT or
ROLLBACK (or perform an action that implicitly commits
http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-
rollback.html).
That's the point of setting AUTOCOMMIT to off. If you only want to
start transactions explicitly (with START TRANSACTION or BEGIN), then
you need to leave AUTOCOMMIT on. See the manual for details
Mysql to Oracle migration
Does anyone know of a straght forward approach to migrate a mysql(4.1.11) Schema to Oracle(9i release 2). Also, please provide any best practices. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql x86 64bit?
Is it just me or are there no RPMS for x86 64bit? Does this mean I am stuck using the 32bit version? This machine has 16gigs of ram and the 32bit version won't be able to make use of all of it. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql x86 64bit?
Mayuran Yogarajah wrote: Is it just me or are there no RPMS for x86 64bit? Does this mean I am stuck using the 32bit version? This machine has 16gigs of ram and the 32bit version won't be able to make use of all of it. thanks, M Sorry Ignore this, Intel EM64T rpm works just fine. M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql to Oracle migration
You need to talk to Oracle, or look on an Oracle mailing list. People here are more concerned about migrating from Oracle to MySQL, rather than the other way around. There are probably lots of commercial tools out there that will do it (and compared to your Oracle licensing costs, they are probably relatively cheap). David Clyde Lewis wrote: Does anyone know of a straght forward approach to migrate a mysql(4.1.11) Schema to Oracle(9i release 2). Also, please provide any best practices. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Populate values in an Excel sheet from MySQL
Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to optimize fulltext selection?
Hello, I have 200-300 kb slow log daily with fulltext queries only :-( All queries using fulltext indexes. I use huge mysql cofig (huge.cfg). What can I change in the mysql configuration for better performance? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. -Original Message- From: Nick Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 2:23 PM To: mysql@lists.mysql.com Subject: Populate values in an Excel sheet from MySQL Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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: Populate values in an Excel sheet from MySQL
Nick Jones wrote: Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Yes, you can do it with odbc in excel. Create and ODBC connection using the MySQL driver. (Office XP/Excel XP): Data --Import External Data -- New Database Query -- (select your odbc connection) -- Setup the query (add cols) -Next- select a col. to select the data based upon (if any) -Next- Select a sort col and by (if any) -Next- Select Return Data to Microsoft Excel -Finish- Remember, Excel has a hard limit on the number of rows, (it is not a database.) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql to Oracle migration
Look at http://www.ispirer.com It is free and very good tool to do it. I did it from Oracle to MySQL, but you can do it from Mysql to oracle. Good luck! -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 2:17 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: Mysql to Oracle migration You need to talk to Oracle, or look on an Oracle mailing list. People here are more concerned about migrating from Oracle to MySQL, rather than the other way around. There are probably lots of commercial tools out there that will do it (and compared to your Oracle licensing costs, they are probably relatively cheap). David Clyde Lewis wrote: Does anyone know of a straght forward approach to migrate a mysql(4.1.11) Schema to Oracle(9i release 2). Also, please provide any best practices. Thanks in advance. -- 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: could insert...select have problem with corrupt table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Today mysql 4.1.8 has a segmentation fault (signal 11), and crashed. In the process one table was left corrupted, when it started back up. An application continued to call insert...select on the corrupted table as it wasn't realized this happened. So, in the replication log on the slave we get this error: 050901 10:50:59 [ERROR] Slave: Error 'Duplicate entry '2925542' for key 1' on query. Default database: 'mydb'. Query: 'INSERT INTO target_table(start,badge,finish,lid,itemtype,rid)SELECT c.start, c.badge, 1125586211, i.lid, i.itemtype,12576 FROM corrupt_table c, good_table i WHERE i.rid=12576 AND c.rid=12576', Error_code: 1062 Could this duplicate record have been caused by my doing an insert...select from a corrupted table to a good table? This is on Solaris 9, btw. Thank you for any clarification. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDF1JEikQgpVn8xrARAmaBAJ4jDyW+RdjLBh+tW21PnYR8XRVajwCfbWCV uQcsbwMY9HKRAa7srzk9TpY= =FLGE -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT alternative
Vladimir, Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site ... No, there's not a 'super SQL' which all SQL engines understand. To hide the details of LIMIT | TOP from your apps, you can write a PHP funcion which applies or updates the appropriate LIMIT | TOP clause in a given query, depending on what the DBMS is. If you are writing against MySQL 5.0.2 or later, you might alternatively write MSSQL and MySQL stored procs for that task. PB - Vladimir B. Tsarkov wrote: Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. Thanks! No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.16/83 - Release Date: 8/26/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
--- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Nick Jones [EMAIL PROTECTED] wrote on 09/01/2005 03:18:39 PM: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick The ODBC query is SO simple and flexible once you get through the darn wizard just once. I think it will save you a lot of headaches in the long run. Another alternative is to run your query through the MySQL client with the HTML output flag turned on and Tee your output to some temp file. Excel (at least the modern versions) are HTML aware and will convert the TABLE, TR, TD, etc. tags into cells automagically. Another alternative: I have also changed the Screen Buffer settings of my CMD window so that it no longer wraps at 80 columns. I tend to use something like 1024 x 2048 but it can get much bigger if you need it to. It allows me to catch a whole lot of CLI output before I hit the limits of the buffer. Highlight and copy your query results into Word and replace all | characters with tabs. I had to use Word as you can't enter the tab character into the replace with field in Notepad. -OR- paste into Notepad and save it off as a temp file. Then import that temp file into Excel as | delimited data. Of course if your actual data contains the | character, some of your rows will be wider by a column or two However, the easiest is still the Import External Data wizard via ODBC. If you stick with the PHP solution, remember that Excel will process any tab-delimited file into columns and rows as well as it can do anything else. If you are really gonzo, you can actually produce a fully formatted sheet so long as you conform to the HTML+XML format that Excel uses when you click Save as HTML. That save format IS thoroughly documented in the KB (I know I found it recently). Options. Way too many options ;-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Populate values in an Excel sheet from MySQL
Nick Jones wrote: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs How about this for a novel aproach... Use the CSV storage engine that MySQL provides in the source distribution? It is very easy to use, and there is no headaches. -- Partha Dutta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connections with bad DNS cause lockups
Hi, I'm running FreeBSD 5.4 on the system in question. It was my understanding on newer Unixes that things like a call to gethostbyname(3) would cause it to go against the nsdispatch(3) in libc and determine what method to perform its various lookups. Right now its set to read nsswitch.conf and that has hosts: files dns. That, to me, means it'll go against /etc/hosts first, then do lookups against bind. The problem I have switching to just hosts: files is that this system does alot of other services besides a database, that depend on being able to do reliable forward and reverse DNS queries. I understand you then want me to put all the names into my /etc/hosts, but doing so doesn't buy me what I need for all the rest of the services on the box. I also have TCPWrappers running depending on domain names out of my control, so it would mean that every time they added/deleted a host, I would have to be told of it. I think /etc/hosts would also do forward DNS only, while TCPWrappers also wants reverse DNS (PTR). While in concept the suggestion is a work around, I think it then breaks every thing around it. Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Here's a little heads-up, as you will need some XML coding to make it look right. PLEASE NOTE: I'm an ASP programmer, so PHP is a little foreign to me. You will need to do some conversion. Here is the way to do it: 1) Define your recordset and connection strings. 2) Write PHP strings to add ContentType (or similar call) = application/vnd.ms-excel 3) (optional) Write PHP echo strings for adding XML tags. (You can find these at http://msdn.microsoft.com, although I'll be damned if I can find them now. Ask for them and I will get them from my other computer and send them on.) 4) In the body of your webpage, put the following snippet: body table width=100% thead tr tdCol 1/td tdCol 2/td ... tdCol end/td /tr /thead tbody ?PHP Open the records, pull the set 'do the loop movefirst while not eof ? tr td? PHP DATA 1 ?/td td? PHP DATA 2 ?/td ... td? PHP DATA end ?/td /tr ?PHP Next in loop Wend Close ? /tbody /table /body /html I hope this helps a little! J.R. PS - Shawn FYI, if you copy and paste a tab into the replace with in notepad, you can do it that way... -Original Message- From: Nick Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 3:19 PM To: mysql@lists.mysql.com Subject: RE: Populate values in an Excel sheet from MySQL --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- 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: Populate values in an Excel sheet from MySQL
--- Partha Dutta [EMAIL PROTECTED] wrote: Nick Jones wrote: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs How about this for a novel aproach... Use the CSV storage engine that MySQL provides in the source distribution? It is very easy to use, and there is no headaches. -- Partha Dutta Ok, here's what we're doing in a nutshell: Every day we fill out forms with backup times and tapes for various computer systems. Three forms a day with 70-100 fields each with all the same data. One Excel sheet and two word documents that end up as PDFs later, all of this is very time consuming. I'm creating a web app in PHP that lets you fill out one form, then click a button and it creates the PDFs for you. I've gotten that far, and now I'm trying to get it to create the Excel sheet too with just one click (fill in your desired date, click submit, and up pops your PDFs and spreadsheet. Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql to Oracle migration
Clyde, Does anyone know of a straght forward approach to migrate a mysql(4.1.11) Schema to Oracle(9i release 2). Also, please provide any best practices. Check out our database development IDE (running on Windows): Database Workbench - works with Oracle, MySQL, Firebird, InterBase and MS SQL Server. Includes a Schema Migration tool. www.upscene.com With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored function
I'm running MySQL 4.0.23-standard, and trying to create a stored function. Am I doing something wrong, or can I not create a stored function in 4.0.23? What I want is a stored function to take an int from a column and alter it (mask off the low 16-bits). SQL-query : CREATE FUNCTION hello( s CHAR( 20 ) ) RETURNS CHAR( 50 ) RETURN CONCAT( 'Hello, ', s, '!' ) MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( s CHAR( 20 ) ) RETURNS CHAR( 50 ) RETURN CONCAT( 'Hello, Thanks DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database
Start your day with Yahoo! - make it your home page From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: cannot create database Description: How to get the promt mysql? Usually i will follow the steps at the command prompt: 1.cd.. 2.cd mysql\bin.. How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Cannot create database Submitter-Id: [EMAIL PROTECTED] Originator: Diana Organization: Student from Multimedia Universuty MySQL support: licence Severity: non-critical Priority: medium | high Category: mysql client Class: sw-bug doc-bug change-request support Release:mysql-3.23.38 Exectutable: [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt] Environment: machine description System:XP Compiler: VC++ 6.0 Architecture: i -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Master Log Files
Thanks for your reply Gleb, I understand that we can setup the binlogfile name in 'my.cnf' file, but I am sure that there is no change in the 'my.cnf'. The hostname program reports the machine name correctly i.e. foo. Cheers, Tedy -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, 1 September 2005 1:47 PM To: mysql@lists.mysql.com Subject: Re: Master Log Files Hello. Program options can be specified in different ways, check them all. See: http://dev.mysql.com/doc/mysql/en/program-options.html What name does the hostname program reports? Tedy Aulia [EMAIL PROTECTED] wrote: Hi All, I have found that the binlogfiles have changed their name. Initially the files were using the name of the server followed by the number i.e. foo.###. Since two weeks ago the binlog files have changed to be mysql.###. As far as I remember there is no change in my.cnf file so far. I am using MySQL version 3.23.33 Could someone explain why the case I mentioned above has happened? Your help would be appreciated. Cheers, Tedy Aulia -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master Log Files
Thanks for your reply Gleb, I understand that we can setup the binlogfile name in 'my.cnf' file, but I am sure that there is no change in the 'my.cnf'. The hostname program reports the machine name correctly i.e. foo. Cheers, Tedy -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, 1 September 2005 1:47 PM To: mysql@lists.mysql.com Subject: Re: Master Log Files Hello. Program options can be specified in different ways, check them all. See: http://dev.mysql.com/doc/mysql/en/program-options.html What name does the hostname program reports? Tedy Aulia [EMAIL PROTECTED] wrote: Hi All, I have found that the binlogfiles have changed their name. Initially the files were using the name of the server followed by the number i.e. foo.###. Since two weeks ago the binlog files have changed to be mysql.###. As far as I remember there is no change in my.cnf file so far. I am using MySQL version 3.23.33 Could someone explain why the case I mentioned above has happened? Your help would be appreciated. Cheers, Tedy Aulia -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with revoke all
Mysql 4.0.18-standard revoke all on *.* from 'deleteatanytime'@'localhost'; mysql revoke all on *.* from 'deleteatanytime'@'localhost'; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) mysql revoke all on *.* from [EMAIL PROTECTED]; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I am ssh'd int the box, so I am localhost, I am root, and it just does not want to let me remove access privs, in this case, all I have is select privs. For the longest time I just delete the user and recreate the permissions, but I would like to figure out how to make this work. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cleaning out old users
Its pretty simple for me to audit my databases and see what ones are old, and can be dropped or whatever, is there some way to get a list of users that are not hooked into any databases, so I can delete those, since they are no longer needed? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
