Unable to restart after crash
Hi, My mysql server crashed last night, and when it rebooted, was unable to restart. Here is the error log: Jan 13 00:12:54 localhost mysqld_safe[1324]: started Jan 13 00:12:55 localhost mysqld[1327]: 080113 0:12:55 InnoDB: Database was not shut down normally! Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Starting crash recovery. Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Reading tablespace information from the .ibd files... Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Restoring possible half-written data pages from the doublewrite Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: buffer... Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 InnoDB: Starting log scan based on checkpoint at Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: log sequence number 0 111349. Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: Doing recovery: scanned up to log sequence number 0 111349 Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: Last MySQL binlog file position 0 3587, file name /var/log/mysql/mysql-bin.000489 Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 InnoDB: Started; log sequence number 0 111349 Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Recovering after a crash using /var/log/mysql/mysql-bin Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Starting crash recovery... Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Crash recovery finished. Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Do you already have another mysqld server running on port: 3306 ? Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Aborting Jan 13 00:12:57 localhost mysqld[1327]: Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 InnoDB: Starting shutdown... Jan 13 00:12:59 localhost mysqld[1327]: 080113 0:12:59 InnoDB: Shutdown completed; log sequence number 0 111349 Jan 13 00:12:59 localhost mysqld[1327]: 080113 0:12:59 [Note] /usr/sbin/mysqld: Shutdown complete Jan 13 00:12:59 localhost mysqld[1327]: Jan 13 00:12:59 localhost mysqld_safe[1374]: ended And since then I am unable to start it. mysqld_safe aborts with: Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[3150]: started STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[3164]: ended And error file: Jan 13 06:03:06 localhost mysqld_safe[1318]: started Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 InnoDB: Started; log sequence number 0 111349 Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Do you already have another mysqld server running on port: 3306 ? Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Aborting Jan 13 06:03:08 localhost mysqld[1321]: Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 InnoDB: Starting shutdown... Jan 13 06:03:11 localhost mysqld[1321]: 080113 6:03:11 InnoDB: Shutdown completed; log sequence number 0 111349 Jan 13 06:03:11 localhost mysqld[1321]: 080113 6:03:11 [Note] /usr/sbin/mysqld: Shutdown complete Jan 13 06:03:11 localhost mysqld[1321]: Jan 13 06:03:11 localhost mysqld_safe[1368]: ended Nothing is running on port 3306, telnet gets connection refused. No mysql processes are running. Does anyone have any ideas what might be wrong? Thanks ROSCO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
adress stored as an array
Hi,
I have an address stored as a serialized array
address[0] = my street;
address[1] = Edinburgh;
address[2] = Scotland;
This is what I have. I want if it is possible to extract all the UNIQUE
towns from my database.
This is what the on of the entires looks like in my database.
a:7:{s:8:building;s:0:;s:7:company;s:0:;s:5:line1;s:12:Leyland
Road;s:5:line2;s:0:;s:5:line3;s:0:;s:4:town;s:8:Bathgate;s:6:county;s:12:West
Lothian;}
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ordering dates
$result= mysql_query(SELECT date_format(date, '%d/%m/%Y') as date, title, id, display FROM news ORDER BY date DESC ); I have the query above the problem is oders them like so 30/05/2007 29/07/2007 25/0/2007 The order is taken by the first number. Is there any way to order them properly without a timestamp? Ta, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting everyting from 2 non-identical tables.
I have two non-identical tables. They are pretty similar except a few fields. I want to select everything from both for example table1 id name age table2 id name height I want id name height age even if it returns null values. select * from table1, table2 seems to give repeat rows for some reason. Thanks, R.
Re: selecting everyting from 2 non-identical tables.
My frist post was not worded correctly. I cannot join two tables as all the rows are unique. men id height name age fav team 1 - 176cm - John - 25-lakers 2 - 180cm - Rob - 40-yankies women id height name age no_of_children 3 - 166cm - mary - 22 - 2 4 - 175cm - betty - 48 - 4 I want to display all the attributes of all the people even if they are null my final table should be 1 - 176cm - John - 25-null-lakers 2 - 180cm - Rob - 40-null-yankies 3 - 166cm - mary - 22 - 2-null 4 - 175cm - betty - 48 - 4-null Ross - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 19, 2007 12:58 PM Subject: RE: selecting everyting from 2 non-identical tables. [snip] I have two non-identical tables. They are pretty similar except a few fields. I want to select everything from both for example table1 id name age table2 id name height I want id name height age even if it returns null values. select * from table1, table2 seems to give repeat rows for some reason. [/snip] Use a left outer join, assuming that 'name' is the same in both; SELECT t1.id, t1.name, t1.age, t2.height FROM table1 t1 LEFT OUTER JOIN table2 t2 ON(t1.name = t2.name) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting second last row
I have a table of publications. -- Table structure for news -- CREATE TABLE `news` ( `id` int(100) NOT NULL auto_increment, `content` longblob, `title` varchar(100) default NULL, `date` date default NULL, `display` varchar(10) default 'no', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- the column display = 'yes'; if the article is to be dispalyed on the homepage. I only have 2 yes columns equal to yes at any one time. SELECT * FROM news WHERE display='yes' ORDER BY id desc limit 1 Think this would get the lastest article that is to be displayed but how do I get the second one. R.
using tinymce and inserting the contents in a database
Hi, I am using tinymce to save news articles with very basic html styling in a database. This works well upto a point but when I get over a certain number of characters and then the 'you have an error in your SQL syntax' error. I am saving in as a LONGBLOB so it should not be a data type mismatch. Any ideas? R.
selecting the last and second last id's
My primary id is an auto incrementing table and I want to return the highest and second highest id. Any ideas?
a function to convert a uk date to and from mysql date
Hi,
My UK dates are this format DD/MM/ I want it reversed and then the
seperator changed so it becomes -MM-DD
I use this PHP at the moment
$available_from = implode('/', array_reverse(explode('-', $available_from)));
Ta,
R.
adding 3 values
Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R.
Re: adding 3 values
Ok, I have this so far $query = SELECT * FROM properties where single_rooms+double_rooms+twin_rooms10 and rent 100; This is fine but what I really want to do it this $query = SELECT * FROM properties WHERE single_rooms+double_rooms+twin_rooms10 AND single_rooms+double_rooms+twin_rooms10 AND rent 100; This is starting to get messy. Can I set up an alias for the total? I tried this without success. $query = SELECT *, single_rooms+double_rooms+twin_rooms AS total FROM properties WHERE total 2 AND total 10 R. - Original Message - From: Edward Kay [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, May 10, 2007 10:17 AM Subject: RE: adding 3 values -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 10:08 To: mysql@lists.mysql.com Subject: adding 3 values Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R. SELECT SUM(single_rooms, double_rooms, twin_rooms) from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF(SUM(single_rooms, double_rooms, twin_rooms) 10,1,0) from TABLE; Sorry, brain was switched off when I wrote that. It should be: SELECT single_rooms+double_rooms+twin_rooms from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF((single_rooms+double_rooms+twin_rooms) 10,1,0) from TABLE; Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
I have a table of properties that is linked to a table f images with a one
property to many images relationship. I have manged this with nested queries
but want to try and do it on one line. My current query
$query = SELECT * FROM images, properties WHERE images.property_id =
properties.property_id;
As you can see from the query this returns a row for every image so if a
property has 3 images associated with it it will be returned 3 times.
Thanks,
CREATE TABLE `images` (
`id` int(10) unsigned NOT NULL auto_increment,
`property_id` varchar(10) default NULL,
`name` varchar(30) NOT NULL default '',
`type` varchar(30) NOT NULL default '',
`size` int(11) NOT NULL default '0',
`position` int(10) unsigned NOT NULL default '0',
`title` varchar(100) NOT NULL,
`img_url` varchar(200) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=264 ;
CREATE TABLE `properties` (
`property_id` varchar(20) NOT NULL,
`postcode` varchar(20) default NULL,
`address` varchar(200) default NULL,
`short_desc` varchar(500) default NULL,
`long_desc` varchar(500) default NULL,
`latitude` double(100,20) default NULL,
`longitude` double(100,20) default NULL,
`rent` varchar(50) default NULL,
`available_from` date default NULL,
`rent_type` varchar(255) default NULL,
`double_rooms` int(2) default NULL,
`single_rooms` int(2) default NULL,
`twin_rooms` int(2) default NULL,
`additional_rooms` varchar(500) default 'on',
`features` varchar(500) default NULL,
`status` enum('off','on') default 'on',
PRIMARY KEY (`property_id`)
)
t: 0131 553 3935 | m:07816 996 930 | [EMAIL PROTECTED] |
http://www:blue-fly.co.uk
Re: Query problem
No I want all the properties only one regardless of how many images are attached to them. Think I need a distinct in there somewhere, - Original Message - From: Jon Ribbens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 09, 2007 6:56 PM Subject: Re: Query problem On Wed, May 09, 2007 at 07:14:38PM +0200, Martijn Tonies wrote: I have a table of properties that is linked to a table f images with a one property to many images relationship. I have manged this with nested queries but want to try and do it on one line. My current query $query = SELECT * FROM images, properties WHERE images.property_id = properties.property_id; As you can see from the query this returns a row for every image so if a property has 3 images associated with it it will be returned 3 times. What exactly is your question? I think he somehow wants to return each property once only but still have every image returned in the result. -- 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: Replication performance questions
On Fri, Mar 02, 2007 at 12:57:25AM -0800, Gary W. Smith wrote: Pdns? Close! bind-dlz Anyway, did you enable the slow query logging? That still might give you an idea if something is running slow. But I also forgot to ask earlier, what is running slow, the inserts or the selects during the inserts? I've been trying to determine if there's a way to enable slow query logging at runtime. I've made the changes to the config file, but restarting the database for this stuff is a little dicey and with the stakes being DNS, I'd rather be conservative! set long_query_time=1; doesnt' seem to have created the slow query file, so I'm guessing this means a restart? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication performance questions
Hi guys, We have a MySQL database that we replicate to about a dozen clients and we hope to be increasing that number to about 15-20. The database has two tables. One is negligably small and changes maybe once a month. The second is about 1.3 million rows and grows at the rate of about 800-1000 rows per day. Both use MyISAM. We've recently been seeing some performance issues with our application that manages inserts and updates to the master database. I have some questions on this front: 1) Does increasing the number of replication slaves increase query latency on the master? We're considering tiering the replication if it might help - replicate the master to two slaves, each of which replicates to ten clients. 2) Is there a chance that the insert latency is coming from the fact that the table is growing so long? At a certain point, even with indexes, I imagine that the engine is going to have to do some linear searching. Thanks for any input! -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication performance questions
On Thu, Mar 01, 2007 at 03:46:56PM -0800, Gary W. Smith wrote: 1) Does increasing the number of replication slaves increase query latency on the master? We're considering tiering the replication if it might help - replicate the master to two slaves, each of which replicates to ten clients. The slaves should only be pulling from the log file, not querying the master data directly. But yes, I guess I could cause an additional load on the server if there are many many slaves. But with 10,000 updates a day (that is 8 per minute, this shouldn't be much of a load at all. This makes a lot of sense to me, and I kind of suspected the additional slaves wouldn't really add much load. The updates are typically small so it not like there's much data to wait for. 2) Is there a chance that the insert latency is coming from the fact that the table is growing so long? At a certain point, even with indexes, I imagine that the engine is going to have to do some linear searching. You mentioned updates, but what about querying the data. Do you run a lot of queries against the data on the master server? We have a database with 50M rows in it and we have a complicated replication strategy for the reader just so we can take 99% of the load off the master. We have a slave'd database just to run reports from (actually we have a load balanced cluster of them). The master received inserts about 20 records/s We have a similar setup with our database that's 1/50 the size ::-) The master is there only to service updates from the application and to push them to the slaves. All reads happen locally on each slave node. Slaves never perform updates. Also, what type of database are you using? INNODB? MyISAM? If you are running MyISAM then things can get slow on updates. The table is MyISAM. I searched on google a bit for info on slow updates with MyISAM and didn't really hit it on the nose. Can I ask you to elaborate? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication performance questions
On Thu, Mar 01, 2007 at 08:29:09PM -0800, Gary W. Smith wrote:
The table is MyISAM. I searched on google a bit for info on slow
updates with MyISAM and didn't really hit it on the nose. Can I ask
you to elaborate?
In /etc/my.cnf try adding:
long_query_time = 1
log-slow-queries=/var/lib/mysql/mysql-slow.log
Let me make the problem a step more frustrating ::-). All of the
queries and updates to this database are extremely simple and very
predictable. The database is a backend for DNS.
Inserts are of the form (updates are analogous):
insert into dns_records (zone, host, data, ... )
values ('domain.com', 'www', '1.2.3.4', ... );
Queries are of the form:
select ttl, type, mx_priority, case
when lower(type)='txt' then
concat('\', data, '\')
when lower(type) = 'soa' then
concat_ws(' ', data, resp_person, serial, refresh, retry,
expire, minimum)
else data end from dns_records where
zone = 'domain.com' and host = 'www';
We've fixed a few data formatting issues that made the select queries
slow under certain circumstances, but we're still running into
occasional performance problems running the inserts/updates.
There are no joins, subqueries, transactions, or any of the usual muck
that complicates a performance issue.
--
Ross Vandegrift
[EMAIL PROTECTED]
The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell.
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql problem
I have a database online and have a strange mysql problem. When I connect remotely from my desktop (same browse_database.php page) I get over 1000 results with the query below but when I run the same page on the remoted server this value is almost halved to 520. any ideas what is hapening? $total_rows = mysql_num_rows(mysql_query(SELECT * FROM $table_name)); It just seems to ignore them on the remote one as I am in under ross and ross2 and only one when I search for me. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions on BDB storage engine
Hello everyone, I have a few questions on the use of the BDB engine with MySQL. If I use the BDB storage engine for a table, is it safe for me to access the BerkeleyDB file while MySQL may possibly writing to it? The database commits are nothing more than simple inserts, updates, and deletes. The access to the BDB file would be strictly read-only. There are no transactions anywhere. Is it possible replicate a table that is using the BDB engine? Many thanks in advance! -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
returning unique value
I have two tables galleries which contains the number and name of the photo
galleries and 'thumnails' the images that are conenected to the galleries.
I am trying to create a 'pick a gallery' screen where it selects all the
galleries and then output the first thumbnail image associated with that
gallery. The probroblem is only returning one unique image.
--
-- Table structure for table `galleries`
--
CREATE TABLE `galleries` (
`id` int(11) NOT NULL auto_increment,
`display` tinyint(4) NOT NULL default '0',
`galleryorder` int(11) NOT NULL default '0',
`title` mediumtext NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
--
-- Dumping data for table `galleries`
--
INSERT INTO `galleries` VALUES (7, 1, 1, 'my gallery1', 'my gallery1');
INSERT INTO `galleries` VALUES (8, 0, 1, 'gallery2', 'my gallery2');
--
--
-- Table structure for table `thumbnails`
--
CREATE TABLE `thumbnails` (
`id` int(4) NOT NULL auto_increment,
`gallery` int(4) NOT NULL default '0',
`display` tinyint(4) NOT NULL default '0',
`photoorder` int(4) NOT NULL default '0',
`caption` varchar(80) NOT NULL default '',
`description` varchar(200) default NULL,
`bin_data` longblob,
`filename` varchar(50) default NULL,
`filesize` varchar(50) default NULL,
`filetype` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=513 DEFAULT CHARSET=latin1 AUTO_INCREMENT=513 ;
This is what I have so far
? $dbquery = SELECT id FROM galleries;
$result = mysql_query($dbquery);
while($row=mysql_fetch_array($result))
{
echo $id=$row['id'];
$dbquery2 = SELECT * FROM thumbnails where gallery=$id;
$result2 = mysql_query($dbquery2);
while($myimage=mysql_fetch_array($result2)){
echo $myimage['caption'];
}
}
Fw: returning unique value
I have atable which contains the number and name of the photo galleries and
'thumnails' the images that are conenected to the galleries.
I am trying to create a 'pick a gallery' screen where it selects all the
galleries and then output the first thumbnail image associated with that
gallery. The problem I have is returning one unique image.
--
-- Table structure for table `galleries`
--
CREATE TABLE `galleries` (
`id` int(11) NOT NULL auto_increment,
`display` tinyint(4) NOT NULL default '0',
`galleryorder` int(11) NOT NULL default '0',
`title` mediumtext NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
--
-- Dumping data for table `galleries`
--
INSERT INTO `galleries` VALUES (7, 1, 1, 'my gallery1', 'my gallery1');
INSERT INTO `galleries` VALUES (8, 0, 1, 'gallery2', 'my gallery2');
--
--
-- Table structure for table `thumbnails`
--
CREATE TABLE `thumbnails` (
`id` int(4) NOT NULL auto_increment,
`gallery` int(4) NOT NULL default '0',
`display` tinyint(4) NOT NULL default '0',
`photoorder` int(4) NOT NULL default '0',
`caption` varchar(80) NOT NULL default '',
`description` varchar(200) default NULL,
`bin_data` longblob,
`filename` varchar(50) default NULL,
`filesize` varchar(50) default NULL,
`filetype` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=513 DEFAULT CHARSET=latin1 AUTO_INCREMENT=513 ;
This is what I have so far
? $dbquery = SELECT id FROM galleries;
$result = mysql_query($dbquery);
while($row=mysql_fetch_array($result))
{
echo $id=$row['id'];
$dbquery2 = SELECT * FROM thumbnails where gallery=$id;
$result2 = mysql_query($dbquery2);
while($myimage=mysql_fetch_array($result2)){
echo $myimage['caption'];
}
}
Re: displaying a sing thumbnail
$query = SELECT distinct gallery FROM thumbnails; that only returns the numbers 7 8. I need the all the info from the rows - id, binary data etcsomething like (although it doesn't work) $query = SELECT * FROM DISTINCT gallery FROM thumbnails; any ideas? - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, August 26, 2006 6:05 PM Subject: Re: displaying a sing thumbnail [EMAIL PROTECTED] wrote: I have a database of images, http://www.thethistlehouse.com/db.jpg What I want to do is select ONLY ONE image to display as a the image link for that gallery. As you can see galleries are numbered dynamcially but galleries can also be added and deleted so the galleries no's I have now (7, 8) will change. I have the code to display the thubnail but am stuck with the query. I want to use mysql and php to (i) determine how many unique galleries there are. To list the galleries: SELECT DISTINCT gallery FROM yourpicturetable; To count them: SELECT COUNT(DISTINCT(gallery)) FROM yourpicturetable; (ii) Retrieve display a single thumbnail from each gallery to act as the link to that gallery That should be easy, but first you must tell us how you determine which picture in each gallery is the desired thumbnail. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
displaying a sing thumbnail
I have a database of images, http://www.thethistlehouse.com/db.jpg What I want to do is select ONLY ONE image to display as a the image link for that gallery. As you can see galleries are numbered dynamcially but galleries can also be added and deleted so the galleries no's I have now (7, 8) will change. I have the code to display the thubnail but am stuck with the query. I want to use mysql and php to (i) determine how many unique galleries there are. (ii) Retrieve display a single thumbnail from each gallery to act as the link to that gallery Ross
returning username/pass from 2 tables
How do I check two tables is it? Username and userpass are submitted through a from and are unique $sql = SELECT username, userpass FROM mytable, mytable2 WHERE username = '$username' AND userpass = '$userpass'; Ta, Ross
maximum files size for longblob - what is bigger?
what us the maximum filesize for longblobs in kb? Is there anything bigger? Ross
Getting the previous months documents
I have a database that stores documents relating to meetings. They have all the
usual stuff agenda, minutes etc. There are 3 paper types for each date agenda
(1 only), minutes (1 only), and a bunch of general documents titled 'papers'.
I need to display all the documents for a specific date which is easy but I
also need to retrieve the minutes for the PREVIOUS meeting which is proving
more difficult. The documents are stored by date in the format dd/mm/yy. If
someone wants the documents from 02/05/06 how do I find the minutes for the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default 'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
Re: Getting the previous months documents
My sql is not so great. The dates are entered through a dropdown box in the
format dd/mm/ (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
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]
sorting with php/mysql
This is my database...I will use the item_id for the order but what if I
want to change item_id 3 to item id 1? How can I push all the items down one
place? How can I delete any gaps when items are deleted. Say I delete item 2
how can I delted the gap and 'promote item_id 3 to item_id 2
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default 'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
`item_id` int(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
download link to file
I have a pdfs/documents saved in a field called 'content' on my db and I want to create an active hyperlink so users can download. any ideas how I can achive this. I am sure it is simple but cannot find an easy example posted. thanks, Ross
mysql query browser- editing resultsets
Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested;
Re: mysql query browser- editing resultsets
The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query browser- editing resultsets
I think the most likely case is the table is read-only. How do I change this? Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:46 PM Subject: RE: mysql query browser- editing resultsets Then, as the documentation states, you cannot edit the fields in that particular query. Either the table is read-only, you have multiple tables (like a join) in your SQL string, or you have functions (max(),min(),count()) in your string. Check out those items again, and if you still can't edit it, post your SQL query and your CREATE TABLE statement so that we (the list) can test this out for you. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:42 AM To: J.R. Bullington; mysql@lists.mysql.com Subject: Re: mysql query browser- editing resultsets The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
exporting a mysql database via mysql query browser
I am a phpmyadmin user and have never really used mysql query browser before. I have a database sitting on my localhost and I want to export the whole thing via mysql query browser to the host. What is the easiest way to do it? Ross
getting unique results
SELECT `Service type` FROM `service_providers` How do I get only unique results from this query. There are many services called 'cafe' for example but I only want one. R.
adding a field and settign the value
I am using phpmyadmin and I want to add a new field and then set the defualt value to all the values to a. How do I do a mass insert and not just create a big empty table row? The filed is called AREA it is a varchar of LENGTH 5 it should be NOT NULL and have a default value of a This is fine when I create the field and add new entries but the old ones have a nothing in them. Thanks, Ross
Re: setting up phpmyadmin problem
I did. The server is windows could this be the problem? The config.inc.php is in the main phpmyadmin folder not in the config folder. Is this ok? Ross - Original Message - From: Dominik Klein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 10, 2006 11:25 AM Subject: Re: setting up phpmyadmin problem [EMAIL PROTECTED] schrieb: http://www.blue-fly.co.uk/screen.jpg I cannot seem to add a server..anyone shed any light on it? Just edit config.inc.php It has good documentation comments, so it should not be a problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem
I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem
I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross
Optimization suggestions when only using Innodb
I have read the suggestions for optimization for innodb however I'm curious if anyone can help me understand which buffers are common (thus used by innodb action) and which are specific to myiasm. The server has 2GB of ram but runs multiple services. I have a number of different types of connections also. Some are repeated queries to static tables and others are dynamic read write to large tables. Thanks in advance! Ross Anderson mysql 4.0.25 linux-2.6.14 key_buffer = 128M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 16M thread_cache = 8 #query_cache_type = ON query_cache_size= 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 innodb_buffer_pool_size = 768M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_status_file=0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting expiry date
I have a php/mysql database with articles. What I need is a php sctipt or some mysql that will compare the current day with the 'expiry date' entered by the user and if the there is a difference then drop it from the database.. I am fairly new to mysql so do not know if there is a way to automatically drop records when they expire. I retrieve the date (called time) and format it like this... $query= "SELECT DATE_FORMAT(time, '%d.%m.%Y') AS time, article, id FROM news ORDER BY id DESC"; The expiry date is entered through a _javascript_ widget and is in the format dd/mm/. thanks, Ross www.blue-fly.co.uk | [EMAIL PROTECTED] | 07816 996 930 | 0131 553 3935
Re: Migrating MySQL users
On Mon, Sep 05, 2005 at 12:41:50PM +0300, Gleb Paharenko wrote: Hello. Have you run 'FLUSH PRIVILEGES'? Doh! I got everything else find, but forgot something stupid. Murphy's law, I suppose. Thanks for the prompt! Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating MySQL users
On Mon, Sep 05, 2005 at 11:00:43AM -0700, Karam Chand wrote: First all it is not recommended at all to dump and import user information. Why not? They are just entries in a database, like any other. How would you propose I move a large batch of users to another database server? Secondly the user authentication system has changed between 4.0 and 4.1 so old passwords from 4.0 wont work. Its given in the docs. Re-read my email: I'm moving from 4.1 - 4.1. Re-read the docs: 4.1 can authenticate against both hash formats. I just forgot to flush privileges, that's all! -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating MySQL users
Hello everyone, I'm working on migrating a number of users to a different database server. mysqldump makes this quite easy, but I've run into a surprising issue with the new server. I imported things like this: # mysqldump --skip-add-drop --all-databases | mysql -h newbox -u root -p Somehow this didn't get the mysql.user table correctly. So I dumped that one seperately like this: # mysqldump --skip-add-drop --skip-extended mysql user | mysql -h newbox -u root -p mysql Now I have all the users in the new mysql.user table. But when I try to login, the same credentials do not work. Both DB servers are 4.1 servers, one happens to be Debian and the other RedHat. Both servers have the wide password field and use old_password in my.cnf. Any reason why these moved accounts are unable to authenticate? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating MySQL users
On Sun, Sep 04, 2005 at 10:08:32PM -0400, [EMAIL PROTECTED] wrote:
Now I have all the users in the new mysql.user table. But when I try
to login, the same credentials do not work.
A bit more info on this. This interesting error happens with GRANT:
mysql grant all on testing.* to testing@'%' identified by 'somepassword';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql select host, user from mysql.user where user='testing';
+--+-+
| host | user|
+--+-+
| %| testing |
+--+-+
1 row in set (0.01 sec)
But a new user works fine:
mysql grant all on testing.* to buttface@'%' identified by 'somepassword';
Query OK, 0 rows affected (0.00 sec)
I can update the old user's password:
mysql update mysql.user set password=password('somepassword') where
user='testing';
Query OK, 1 row affected (0.00 sec)
But still cannot login:
# mysql -u testing -psomepassword testing
ERROR 1045 (28000): Access denied for user 'testing'@'localhost' (using
password : YES)
Thanks a ton for any suggestions!
--
Ross Vandegrift
[EMAIL PROTECTED]
The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell.
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp and php
Hi, I have a row in myslq database called time and is just a simple timestamp column When I echo it out echo $row['time']; echo $row['content']; I get the following 2005-08-30 13:50.05 this is the text content Now I am not worried about the time but I would like to know how to (i) sort the returned rows in order (latest first) (ii) be able to extract the individual parts of the date and display them in UK format (ddmm) 30.08.2005 this is the text from 30th of August 27.08.2005 this is the text from 27th of August 27.08.2005 this is the text from 23rd of August thanks, R.
newbee question
Using mysql with php I have a database that has a unique value for the user id. when people get delete off the list I want the unique numbers to compact down so. user id 1 user id 4 user id 5 user id 7 user id 9 user id 10 becomes user id 1 user id 2 user id 3 user id 4 user id 5 user id 6 If this is not possible how can I find the first entry in the database and last entry with php/mysql? R.
Re: MySQL and HIPAA Compliance?
On Fri, Aug 05, 2005 at 01:29:19PM -0400, J.R. Bullington wrote: The way that data is stored is not at the issue. It's the way that data is collected that is at the heart of the RDBMS part of HIPAA. I once interned for a major vendor of HIPAA-compliant hospital IT solutions, doing software quality assurance on their internal products. I was responsible for testing two of the most HIPAA-relevant pieces of inftastructure in the whole architechture: 1) User authentication: HIPPA compliance requires that someone be explicity authorized to view data before they can view it. We had a reasonably sophisticated token system that was implemented for every API. In addition to a function's parameters, it also needed it's security token. That security token was based on the user that had logged in and what they were attempting to view. Security tokens would be assigned to users and stored in an isolated database (we had Oracle, MS SQL, and DB2 modules, though there's no reason MySQL couldn't be supported). A security administrator would use a tool to assign the proper permissions to their users. This would literally define what APIs the user had access to (similar to Java's security policy, but with a database backend and even finer grained control). 2) Audit logging: Every time an authenticated user retrieved some piece of data, we had to create an audit record that could prove they did it. This is the Accountability part of HIPAA. An auditing administrator would configure the auditing properties, which gets stored in another isolated database. Anytime an authenticated user does something that the auditing administrator has decided is significant - boom, a record gets written to record the fact. You'll notice that in both cases, the HIPPA-ness is implemented on a higher level than the actual database. Like JR said, as long as the server is secured, there's no problem with that implementation. Having said that, we all laughed at the idea of running our software in a production environment on anything but a DB2 mainframe. These two pieces are quite a heavy load on the databases - just imagine how many audit records can be generated at a large busy hospital! We had a test load that could easily bog down our database servers. Of course it was contrived, and our testing budget prevented us from having serious hardware to test with, but it always boggled my mind to think of all the audit records that would be stored somewhere (and probably never looked at ::-). Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. That's a clever idea! But didn't you have to store personal information at somepoint? I guess you could do a clever encoding scheme to map a name and address to a very large integer, but that seems... not much better. How did you handle that issue? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: table export problem
- Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 19, 2005 10:08 PM Subject: table export problem Hi all, I am trying to create a table on the remote server from a table I created on my local sever but it never seems to work CREATE TABLE `sheet1` ( `id` int(10) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `fname` varchar(255) NOT NULL default '', `sname` varchar(255) default NULL, `job_title` varchar(255) default NULL, `organisation` varchar(255) default NULL, `email` varchar(255) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `postcode` varchar(255) default NULL, `office_tel` varchar(255) default NULL, `mobile` varchar(255) default NULL, `fax` varchar(255) default NULL, `web` varchar(255) default NULL, `add_info` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ; There seems to be a problem with the last line (this is exported from my local server). I am just learning about mySql as I go so have no real clue about CHARSET and ENGINE (which I believe may be the problem) This is the error 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=303' at line 18 and this is what the manual says (not very helpful) a.. Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d Any help will be appreciated. R.
table export problem
Hi all, I am trying to create a table on the remote server from a table I created on my local sever but it never seems to work CREATE TABLE `sheet1` ( `id` int(10) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `fname` varchar(255) NOT NULL default '', `sname` varchar(255) default NULL, `job_title` varchar(255) default NULL, `organisation` varchar(255) default NULL, `email` varchar(255) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `postcode` varchar(255) default NULL, `office_tel` varchar(255) default NULL, `mobile` varchar(255) default NULL, `fax` varchar(255) default NULL, `web` varchar(255) default NULL, `add_info` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ; There seems to be a problem with the last line (this is exported from my local server). I am just learning about mySql as I go so have no real clue about CHARSET and ENGINE (which I believe may be the problem) This is the error 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=303' at line 18 and this is what the manual says (not very helpful) a.. Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d Any help will be appreciated. R.
Re: error: 'Can't create a new thread (errno 12).
On Mon, Jul 18, 2005 at 10:33:49AM +0300, Gleb Paharenko wrote: [EMAIL PROTECTED] gleb]$ perror 12 OS error code 12: Cannot allocate memory Very often it is ulimits problem. Check if your server has enough memory as well. The cause of problem sometime might be unofficial binaries. If nothing helps, switch to binaries from MySQL AB. Yea, at first I thought it was an out-of-memory condition, and so I did typical things like lower maximum connections. This helped, but the machine is no longer running out of memory. Or if it is, it's running out of memory without displaying any symptoms of it (nothing is OOM killed by the kernel, other processes don't fail, free reports allocatable memory, etc). I just wanted to see what the OS-dependent bug thing is about - it sounds like it is referring to a particular bug that is described in the manual. I haven't tried MySQL AB binaries - I am using the stock Debian builds. Since it refers to general errors, I may persue it with them as well. Thanks! -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error: 'Can't create a new thread (errno 12).
| | Handler_update | 160159 | | Handler_write| 60991 | | Key_blocks_used | 249380 | | Key_read_requests| 61619597 | | Key_reads| 291069 | | Key_write_requests | 144783 | | Key_writes | 141414 | | Max_used_connections | 15 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 64 | | Open_files | 81 | | Open_streams | 0 | | Opened_tables| 675| | Questions| 14290311 | | Qcache_queries_in_cache | 12829 | | Qcache_inserts | 5903341| | Qcache_hits | 6000658| | Qcache_lowmem_prunes | 0 | | Qcache_not_cached| 42714 | | Qcache_free_memory | 8610680| | Qcache_free_blocks | 480| | Qcache_total_blocks | 26186 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 72569 | | Select_range_check | 0 | | Select_scan | 332366 | | Slave_open_temp_tables | 0 | | Slave_running| OFF| | Slow_launch_threads | 0 | | Slow_queries | 8 | | Sort_merge_passes| 0 | | Sort_range | 70697 | | Sort_rows| 5116790| | Sort_scan| 120| | Table_locks_immediate| 6908344| | Table_locks_waited | 258110 | | Threads_cached | 0 | | Threads_created | 687128 | | Threads_connected| 5 | | Threads_running | 2 | | Uptime | 64124 | +--++ -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comments on hot databases migration
I'm looking for comments/suggestions on the best method to migrate multiple databases from one machine to another. It has been requested that both machines be placed into production and that a slow migration occur of services and databases over a period of time. I realize that mysql doesn't support synchronization per sey but has anyone done real time migration in small blocks. I have multiple services accessing the same databases. Although not my choice, they want to leave some services running on the old machine and writing to one db while re-writing code/moving other services. My inclination is to deny this request but I'd like to pool the community for comments. I'm very comfortable shutting down one db, migrating a whole db, re-point services and start the new db. I am just digging for a creative solution. I like creative suggestions/challenges. Thanks in advance, Ross Anderson __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
InnoDB race condition inserting into a table with a unique constraint
MySQL 4.1.8 I have an InnoDB table with a unique constraint: CREATE TABLE TEST ( ID bigint NOT NULL auto_increment, NAME varchar(100) NOT NULL, VALUE varchar(100), PRIMARY KEY (ID), UNIQUE KEY IX_NAME (NAME)) ENGINE=InnoDB Given a particular unique name, I need to either find the existing record and return the value or create a new record. I am having problems working out how to avoid a race condition though. If two sessions both issue the query: SELECT * FROM TEST WHERE NAME='uniquename' then both could get no row returned and hence try and create a new record (one of which would fail with a unique key violation). Is it possible to get MySQL to lock the table/index so that when two sessions run the above query and no rows are found, the second select will block until the first transaction has completed? Is there an alternative way of structuring the problem that would make this concurrently safe? Thanks, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling mysql and mysql crashing
I had to install a source version of mysql and one of the install docs strongly suggests using CXXFLAGS=03 -felide-constructors -fno-exceptions -fno-rtti because of some instances of mysql crashing. When I did that I noticed in the compile a line that said it wasn't supported by gcc (3.3.3). Im using CXX=gcc. 1) Has anyone had instances if it crashing without those parameters? I can't have mysql crashing but if the gcc indicates it doesn't support it, do those flags get built in? Also when I included it in the build, my statement about --localstatedir=/usr/local/mysql/data was ignored because it never built the data directory. 2) I just built it with --prefix and --localstatedir. Has anyone had problems with this? 3) If I want to recompile mysql, do I also have to recompile all apache, php and mod_ssl? Thanks for any suggestions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting records using the 'LIMIT' clause
Hi all, I have positively identified the row I want to delete using: 'SELECT * FROM table LIMIT 10,1' This has returned 1 record and I now want to DELETE the record. How do I identify this record in my DELETE statement? (using 'DELETE FROM table LIMIT 10,1' does not work) NOTE : I can't identify it using it's key fields as the table has no primary key. Help greatly appreciated. Regards ... Ross . Ross Honniball JCU Bookshop Cairns Supervisor . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] . There are no problems. Only solutions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I determine the row number or key when table has no key fields
eg. say a table is created using: create table fred (f1 char(10), f2 int) Then it has neither keys nor an AUTO_INCREMENT field. Let's say 1000,000 records are then inserted into table fred. I then say 'select * from fred' and loop through results writing to a web page. I stop writing to the web page after say 20 records. The user hits 'next page'. I want to say 'select * from fred where ?field? ?value? Where ?field? and ?value? are what I want to know. Surely there is some kind of 'record number' or something available in mySQL for me to : 1. Retrieve and save 2. Query against I'm new to this mailing list. Apologies if I am asking this question of an inappropriate email address. Regards ... Ross . Ross Honniball JCU Bookshop Cairns Supervisor . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] . There are no problems. Only solutions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies Same problem --Plz giv the solution !!
-Original Message- From: rajesh k [mailto:[EMAIL PROTECTED] Sent: 23 March 2004 14:36 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Newbie - dependencies Same problem --Plz giv the solution !! Robb + ( ME TOO )- still in jail :-( Plz get me out of the jail !!! Thanx, bii --- Hi bii, I got the fix from Alex Greg: You have a previous version of MySQL installed, from the mysql.com RPM's. To get rid of it, do: rpm -e 'MySQL*' first, then: rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm Hope this helps, if not Alex is your man Robb - I'm free :0) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? Robb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
Hi Victor, I tried that initially and thought I had installed everything - RH Enterprise has the option in the GUI to install mysql - what it doesn't tell you is that it is the client and not the server. When you (eventually) find this out and try to install the server, problems occur because the server should be installed before the client (I think). So you then uninstall both server and client, then re-install the server. If sucessful, you still get problems when you try to re-install the client as the GUI cannot find the server's headers and so will not install client software. So you go and find the rpm and try to install it yourself and get the dependencies problem. I think you get an idea of my frustration, and I have not even got to the part of trying to use mysql yet! Still, I will continue on...:-) Thanks for your input. Robb -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 March 2004 13:31 To: Robert Ross Cc: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi! anyway, why dont you use the graphic install utility redhat uses to install mysql?? it will resolve the dependencies for you =) PS: i think it's called redhat-config-packages Best Regards! On Thu, 2004-03-11 at 09:05, Robert Ross wrote: Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
You have a previous version of MySQL installed, from the mysql.com RPM's. To get rid of it, do: rpm -e 'MySQL*' first, then: rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm I tried 'locate' but could not find either file, so then tried 'rpm -e' of both When you use rpm -e, you give it a package name (e.g. mysql-3.23.58-1.i386 or mysql) not a file name. -- Alex -- Genius mate, It worked! I had tried the whole package name when I used '-e' but it did not seem to make much difference. However, using your wildcard idea seems to have worked. A pause then the command line again. Put it your multi rpm line and it kicked in and loaded beautifully. Seems to be working, so now I can get horribly confused with setting up the database. Steep learning curve, but really interesting. Thanks again for you help. Robb (I'm free:-)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 3.23 to 4.0 Problem
Im trying to upgrade 3.23 on my redhat 9 machine. I have one lone perl-dbd-mysql libmysqlclient dependency that is throwing me off. when i try to upgrade it wont go. so i try to uninstall that perl rpm and it says its mysql 3.23 needs it and when i try to uninstall mysql 3.23 it says the perl dbd needs it. how do i get around this catch? __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Confusion in Linux
I was able to successfully install MySQL server 4 under windows, but am a little confused about doing it through linux. My main confusion is where the install goes? Im using the rpm, and from my personal directory where i downloaded the rpm to, i issue a rpm -i MySQL-server-...rpm command and it goes through its deal. The very first thing I notice is it says is to execute /usr/bin/mysqladmin -u root... to set the passwords. Well there are about a dozen mysql scripts in that directory but no mysqladmin, so immediately Im thinking i have a problem or did something wrong. Next it says the default directory for the install is /usr/local/mysql unless i specified otherwise. I did not and that directory does not exist. so what happened? i downloaded the rpm off the mysql site and all i did was run it. didnt touch anything else. it installs the mysql service fine, and it puts all its db files in the /var/lib directory like it says it will, but it sure seems like im missing some other critical files? __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Confusion in Linux
/ukrainian -rwxr-xr-x1 rootroot13600 Feb 12 09:02 /usr/share/mysql/ukrainian/errmsg.sys -rwxr-xr-x1 rootroot14090 Feb 12 09:02 /usr/share/mysql/ukrainian/errmsg.txt [EMAIL PROTECTED] root]# --- heres a list of the files by running rpm -qlp package and dont find mysqladmin or some other important files. did something go wrong during the install? ive tried uninstalling/reinstalling many times. using the find command on mysqladmin yields no results. --- walt [EMAIL PROTECTED] wrote: Ross, rpm -qlp package_name.rpm will list all the files in an rpm for you. You may need to install the client rpm as well. walt Ross O wrote: I was able to successfully install MySQL server 4 under windows, but am a little confused about doing it through linux. My main confusion is where the install goes? Im using the rpm, and from my personal directory where i downloaded the rpm to, i issue a rpm -i MySQL-server-...rpm command and it goes through its deal. The very first thing I notice is it says is to execute /usr/bin/mysqladmin -u root... to set the passwords. Well there are about a dozen mysql scripts in that directory but no mysqladmin, so immediately Im thinking i have a problem or did something wrong. Next it says the default directory for the install is /usr/local/mysql unless i specified otherwise. I did not and that directory does not exist. so what happened? i downloaded the rpm off the mysql site and all i did was run it. didnt touch anything else. it installs the mysql service fine, and it puts all its db files in the /var/lib directory like it says it will, but it sure seems like im missing some other critical files? __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- 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] __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Question - RedHat 9.0
For RedHat, just download the rpms and install it, it's far much simpler... Nicolas - Original Message - From: Ryan Sinnwell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 1:21 PM Subject: Installation Question - RedHat 9.0 I am trying to setup Jabberd2 as a test for our IT users and have run into a few issues. Here is a link to the instructions I'm following: http://www.jabberdoc.org/app_mysql.html I know there are things missing because after I complete the make install in step A.4.6, it goes right on to have me change the root password, but mysqld isn't even running at this point. Could someone that has installed mySQL many times give these a look and see what steps they have left out that I will need for RedHat 9.0? One other step that doesn't appear to be correct is A.4.9 because /usr/local/var/mysql/ does not even exist after the install as they describe it. Also, if I want mysqld to start every time the computer is booted, and use the mysql user that I created per these instructions, is this what I would add to rc.local: /usr/local/libexec/mysqld --user=mysql Is rc.local the correct file to put this in or is there a better place? Any help that is provided will be much appreciated. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb in production
Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Darwin (Mac OS X 10.2) grant problem
2. Grants aren't working. Almost for each db we have the grant I did is something like : grant all on db.* to 'user'@'%.domain.com' identified by 'password'; So, I end up with a user with no global privilege in the user table, and a entry in the db table with all privs. User table : '%.domain.com', user, pass, N for the rest, exept Lock_tables_priv is Y. Db table : '%.domain.com', db, user, Y for the rest. When users connect from the web server (another machine), they can connect to the mysql server, but they can't do anything. We get : select command denied to user: '[EMAIL PROTECTED]' for table 'table' Have one of these users connect and issue this statement: SELECT CURRENT_USER(); If the username part of the result value is empty (nothing before the '@' character), you probably have a problem of these users actually being authenticated using an anonymous-user account. Dump those accounts by connecting to the server as root and issuing these statements: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; Then have the user try again. I get something like : [EMAIL PROTECTED], which fits the user in the user and db table. I suppose it's right... Also, I want to point out that those exacts same privs where working right on my intel box... Any new hints on this ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Darwin (Mac OS X 10.2) grant problem
Hi ! Our normal server is on RedHat 7.3 / mysql-max 4.0.14. The server just crached, I had to recover on another hardware, a Mac OS X 10.2, wih mysql-max 4.0.15. The restore went well, but I have some problems : 1. All tables/database are now lower case. This not a major issue, as the dbs are still running ok. I'll handle it later when the intel server is back up again. 2. Grants aren't working. Almost for each db we have the grant I did is something like : grant all on db.* to 'user'@'%.domain.com' identified by 'password'; So, I end up with a user with no global privilege in the user table, and a entry in the db table with all privs. User table : '%.domain.com', user, pass, N for the rest, exept Lock_tables_priv is Y. Db table : '%.domain.com', db, user, Y for the rest. When users connect from the web server (another machine), they can connect to the mysql server, but they can't do anything. We get : select command denied to user: '[EMAIL PROTECTED]' for table 'table' If we connect as root, the select is working properly. It's not the host either, I tried with % as hosts, and it didn't work. Any hints ? Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Darwin (Mac OS X 10.2) grant problem
2. Grants aren't working. Almost for each db we have the grant I did is something like : grant all on db.* to 'user'@'%.domain.com' identified by 'password'; So, I end up with a user with no global privilege in the user table, and a entry in the db table with all privs. User table : '%.domain.com', user, pass, N for the rest, exept Lock_tables_priv is Y. Db table : '%.domain.com', db, user, Y for the rest. When users connect from the web server (another machine), they can connect to the mysql server, but they can't do anything. We get : select command denied to user: '[EMAIL PROTECTED]' for table 'table' Have one of these users connect and issue this statement: SELECT CURRENT_USER(); If the username part of the result value is empty (nothing before the '@' character), you probably have a problem of these users actually being authenticated using an anonymous-user account. Dump those accounts by connecting to the server as root and issuing these statements: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; Then have the user try again. I get something like : [EMAIL PROTECTED], which fits the user in the user and db table. I suppose it's right... Also, I want to point out that those exacts same privs where working right on my intel box... Thanks Nicolas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unusual Date Query
I want know if there is a way to return every date between 2 date ranges regardless if there is a row in a table. I have a table that contains daily pricing information and I want to be able to do a mass updated/insert records. I know how to write the updates/inserts if I have table that contains EVERY date between the date ranges, but I don't want to keep a table around that has one row for every possible date. I can't use the replace into command because of a foreign keys on the table. The insert will look something like this Insert into daily_rate from select date,123.45 from allpossibledates left join daily_rate on allpossibledates.date=daily_rate.date where date between 20030901 and 20031010 and daily_rate.date is null The problem is I don't know how to generate allpossibledates easily on the fly. I am running 3.23.58 and can't upgrade to the latest versions yet due to incompatibilties with the timestamp formats that were changed. TIA Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unusual Date Query
That will only return the number of rows in Table1 What I want to return 1 to n rows based on the number of days between date1 and daten with each row having the date filled in. Ross Davis DataAnywhere.net 250-470-9192 ChaletsOnline.com is coming soon Don't you deserve a vacation! -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 2:00 PM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Unusual Date Query At 12:59 PM 10/10/2003, you wrote: I want know if there is a way to return every date between 2 date ranges regardless if there is a row in a table. I have a table that contains daily pricing information and I want to be able to do a mass updated/insert records. I know how to write the updates/inserts if I have table that contains EVERY date between the date ranges, but I don't want to keep a table around that has one row for every possible date. I can't use the replace into command because of a foreign keys on the table. The insert will look something like this Insert into daily_rate from select date,123.45 from allpossibledates left join daily_rate on allpossibledates.date=daily_rate.date where date between 20030901 and 20031010 and daily_rate.date is null The problem is I don't know how to generate allpossibledates easily on the fly. I am running 3.23.58 and can't upgrade to the latest versions yet due to incompatibilties with the timestamp formats that were changed. TIA Ross Ross, This should get you started: set @n=0; select date_add(CurDate(), INTERVAL (@n := @n + 1) DAY), col1 from table1; Mike -- 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: table copying/replication
This sounds like the best idea yet :) I have a couple of questions: - I need to keep the data in x_shadow while still creating table x.. will copying accomplish the same thing? I'm guessing it will be slower, but keeping the 'shadow' table around is important. - Will either / both of these (rename and copy) preserve indexes? Each table has ~5 indexes, and I don't want to reindex. Thanks for the ideas Ross On Wed, 2003-06-04 at 02:43, Martin Waite wrote: Run a MySQL replication chain from stage to prod to replicas. On stage, prepare the data on shadow tables with different names to those used in prod. Say, if your real tables are one, two, three, four, five then create shadow tables one_shadow, two_shadow, three_shadow... Once the data is ready in the shadow tables, do a rename: rename table one to one_old, one_shadow to one, two to two_old, two_shadow to two, ... Replication will apply the rename to all your replicas and you should get a fairly snappy switchover. The only drawback is that you have two copies of your tables, which might be impractical depending on the amount of data involved. regards, Martin -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copying/replication
I have looked at this idea as well, and I am unsure what problems issuing a 'flush tables' could cause: - will it take a long time to complete with lots of tables and indexes? - does any locking happen while this is going on, or is each table available after it's been re-read from disk? - iirc, mysql caches indexes.. what happens when a table's datafile is changed, and hasn't been re-read yet (i.e., the index no longer corresponds to the table)? Does mysql take this into account, or will a query against this table yield incorrect data? Getting there.. thanks for the help :) Ross On Tue, 2003-06-03 at 17:24, Andrew Braithwaite wrote: OK - in that case there's not much you can do with replication... But with mysql you could write some bash to copy the raw files over like: Run this from /datadir (on the server you want to copy from): tar cf - databasedirname | ssh server_you_want_it_to_go_to 'cd /datadir; tar xf -' And supply the password (I think you can do that with a ssh option (or set up some ssh keys)) then (if you have the right permissions set up) - issue a flush tables on all the slaves from the master that will do the job with a simple file transfer. If you don't have ssh then you could set up a nfs mount or something to let your scripts do the copying (I wouldn't recommend nfs for large amounts of fast data transfer personally...) Hope this helps... Andrew -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 23:57 To: Andrew Braithwaite Cc: Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: RE: table copying/replication I looked at that type of idea, and the reason I didn't mention it was the indexes -- each table involved has ~5 indexes, and on a large table, that equals a lot of work that's already done on 'stage' that must be redone on 'prod'. I'd really like to preserve the indexes from stage if at all possible. Maybe there's a way to dump and reload the indexes as well as the data.. Thanks for the help, and I hope you don't get fired.. I won't tell anyone ;) Ross On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote: Hi, Assuming that this system runs on *nix and that prod is set up to replicate to all the replicas you could write a small bash script to push the data from the stage to the prod which would then replicate as normal. I would author the script something like this... [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This would dump the entire somedatabasename to the prod server which would then replicate it to all the slaves using the -e option for faster inserts: If you wanted to overwrite the existing data then use the --add-drop-table option to mysqldump like this: mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This will then replicate as fast as your network/hardware will allow. Hope this helps.. (I'm all for open source but it's a bit weird that I'm helping out our state-side mapping competitors here - at least it's not microsoft - I'll check tomorrow to make sure I don't get sacked :) Cheers, Andrew multimap.com -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 22:44 To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: table copying/replication Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 --tables, and --at any time, one of those tables would need to be copied to all
another replication question..
I have another question that doesn't seem to be addressed in the mysql manual. Does any sort of locking occur while a slave is updating it's local databases? Can I still read any/all tables while this process is occurring? If there is locking, is the lock table-based or for the entire db? My slaves will be read-only dbs in a production environment with a lot of traffic, so I need to insure that they can always serve requests. Thanks, Ross -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table copying/replication
Hello, I have a need for fast copying of a specific table from a master mysql server to a number of slave servers (say 5). The database in question could potentially have up to 2000 tables, and at any time, one of those tables would need to be copied to all 5 of the slaves, upon command of the master. I've looked into mysql replication, and it looks like a good option, except for the need to be able to trigger the copy from the master (the table is only copied when requested). I also looked at doing table copies (insert into .. select * from ..), but these seem pretty slow. Has anyone solved this problem before? Any ideas that would help out? Thanks! Ross -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copying/replication
Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 tables, and --at any time, one of those tables would need to be copied to all 5 of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team with replication. -- --Ross Simpson [EMAIL PROTECTED] --MapQuest.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copying/replication
I looked at that type of idea, and the reason I didn't mention it was the indexes -- each table involved has ~5 indexes, and on a large table, that equals a lot of work that's already done on 'stage' that must be redone on 'prod'. I'd really like to preserve the indexes from stage if at all possible. Maybe there's a way to dump and reload the indexes as well as the data.. Thanks for the help, and I hope you don't get fired.. I won't tell anyone ;) Ross On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote: Hi, Assuming that this system runs on *nix and that prod is set up to replicate to all the replicas you could write a small bash script to push the data from the stage to the prod which would then replicate as normal. I would author the script something like this... [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This would dump the entire somedatabasename to the prod server which would then replicate it to all the slaves using the -e option for faster inserts: If you wanted to overwrite the existing data then use the --add-drop-table option to mysqldump like this: mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This will then replicate as fast as your network/hardware will allow. Hope this helps.. (I'm all for open source but it's a bit weird that I'm helping out our state-side mapping competitors here - at least it's not microsoft - I'll check tomorrow to make sure I don't get sacked :) Cheers, Andrew multimap.com -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 22:44 To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: table copying/replication Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 tables, and --at any time, one of those tables would need to be copied to all 5 --of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team with replication. -- --Ross Simpson [EMAIL PROTECTED] --MapQuest.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create fails with on delete set null
Simply said this works: drop table if exists detail; create table detail ( detail_id int not null auto_increment , master_id int not null , name varchar(50) not null , primary key (detail_id) , index master_idx(master_id) , foreign key (master_id) references master (master_id) on delete cascade on update cascade ) type=InnoDB; This doesn't: drop table if exists detail; create table detail ( detail_id int not null auto_increment , master_id int not null , name varchar(50) not null , primary key (detail_id) , index master_idx(master_id) , foreign key (master_id) references master (master_id) on delete set null on update cascade ) type=InnoDB; mysql 4.0.10 on Windows XP Pro Anyone else have this problem? Is it a bug? Ross - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: innofb foreign keys problem
I agree with you. If there is no index already on the referenced fields then add one. By adding a foreign key you are already altering the table why not just finish the job. -Original Message- From: Natale Babbo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 8:58 AM To: Okan CIMEN; [EMAIL PROTECTED] Subject: Re: innofb foreign keys problem many thanks for your reply. then i ask me: why mysql needs explicit creation instead of create itself what it needs? manually creating the index seems to be a big complication (for big databases) and a waste of time. don't you think so? it's a bug or a wanted feature? why? Thanks. --- Okan CIMEN [EMAIL PROTECTED] ha scritto: There are no other way you have to create the index first - Original Message - From: Natale Babbo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 5:41 PM Subject: innofb foreign keys problem # - 3rd post - # # - PLEASE HELP -- # hi to all, is it still true that mysql/innodb needs explicit index creation on foreign keys? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) how can i create the database without creating explicitly an index on each foreign keys of my database? any suggestions are appreciated. thanks to all. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.h tml - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.h tml - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Design feature or bug
I have a 3.23.53 server that is a slave of another 3.25.52 server. Master has 2 databases on it. On the slave only only one of the databases is replicated. If I reference the table database that is not on the slave during an insert or something on the server it crashes the slave! Shouldn't the slave replicate the database and not the queries that have been run? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Design feature or bug
-Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 4:00 PM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Design feature or bug On Tue, Jan 14, 2003 at 02:43:27PM -0800, Ross Davis - DataAnywhere.net wrote: I have a 3.23.53 server that is a slave of another 3.25.52 server. Master has 2 databases on it. On the slave only only one of the databases is replicated. If I reference the table database that is not on the slave during an insert or something on the server it crashes the slave! The slave should not crash, it should merely stop. I stand corrected. In a production environment a stopped server is as good as crashed. Shouldn't the slave replicate the database and not the queries that have been run? MySQL uses log-based replication. The slave reads a query log (known as the binary log) from the master. It contains all the queries that change any data on the master. The moral of the story is that you should logically separate your databases. If you cannot do that, replicate all related databases--not just some of them. They are logically seperated. The only problem is that we have to occasionally do a mass update from the other table and that is when it all messes up. I know why things work they way they do, but it would be nice to have an option to change the replication scheme on certain databases/tables so that things like this could occur. The big table on the master that I don't want at the slave is very large and the slave only needs a bit of the data and it connects over dialup! Replicating the whole thing would be to much bandwidth for my dialup line. I will just have to be very careful. Ross Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 30 days, processed 1,011,755,074 queries (380/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Design feature or bug
Unfortunately that won't help me. The slave database is part of of an access system at a ski resort. Missing updates means unhappy customers that are being denied access. Thanks for your help. Ross -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 6:18 PM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Design feature or bug On Tue, Jan 14, 2003 at 04:41:53PM -0800, Ross Davis - DataAnywhere.net wrote: I stand corrected. In a production environment a stopped server is as good as crashed. Ah, good. I know why things work they way they do, but it would be nice to have an option to change the replication scheme on certain databases/tables so that things like this could occur. The big table on the master that I don't want at the slave is very large and the slave only needs a bit of the data and it connects over dialup! Replicating the whole thing would be to much bandwidth for my dialup line. I will just have to be very careful. Well, you could use the slave-skip-errors=... syntax in your slave's my.cnf file, as described here: http://www.mysql.com/doc/en/Replication_Options.html But beware that you're knowingly asking MySQL to blow right past errors. Might that solve this problem for you? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 30 days, processed 1,013,535,492 queries (379/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication bug?
I don't think I have anything that should cause this. Here is my my.ini from the the slave. The tables that are being excluded are not listed. [mysqld] basedir=C:/mysql datadir=C:/mysql/data set-variable=max_allowed_packet=16M log-slave-updates log-bin # Replication variables master-host=x.x.x.x master-user=sasassas master-password=x master-port=3306 server-id=2 # Exclude some tables that we don't want here! replicate-wild-ignore-table=ra_scanner.system replicate-wild-ignore-table=ra_scanner.local_scan_log - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication bug?
Yes, the slaves are doing the replication. (didn't know you could set it up any other way) As far as I am concerned this is a BIG bug. Anything that happens on the master should replicate to the slaves. Any chance this could get fixed in the next release? -Original Message- From: Frederick R. Doncillo [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 11, 2003 10:58 PM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Replication bug? Are the slaves doing the replication process? If not, you may try it that way. Slaves should do the updating and must request from the server and not the server to the slave. :-) Fred. Ross Davis - DataAnywhere.net wrote: I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication bug?
I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best MySQL version for replication
I am about to setup the following server layout using all Windows 2000 Server and XP Pro machines. Master Server-| |--Branch Office Server -| ||- Machine 1 ||... ||- Machine N | |--Second Branch Office -| |- Machine 1 | |- Machine N Objective: Client needs to have read access to the data even if the local lan goes down, so we Are going to put a local copy of mysql on each desktop machine that is a slave to the branch office Server. Updates will always go to the master server when the Master server is available. (We have A way to deal with saving when the Master is not available) Baically the branch offices are slaves to the master and the workstations will slave to the branch office server. I have the choice right now of using either MySQL 3.23 series or MySQL 4.0x series. This will be deployed in just over a week so the path with the least replication issues is the best One for me. Keep in mind that the workstations may be shutdown nightly. Can't control all the users;-) Thanks in advance Ross PS the link from the master to the branch offices is a 10MB/Second wireless link that is just being installed And hopefully reliable. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.49
I seem to have found a bug in MySQL 3.23.49 (untested in later versions). It appears that if the server cannot resolve the hostname of an incoming TCP connection, it crashes. I've been testing this under a RedHat 7.2/7.3 hybrid, and only came across the problem because I've managed to somehow stop DNS from working correctly! Unfortunately, I don't yet know exactly what's wrong with DNS. Putting the hostname of the incoming IP address into the /etc/hosts file has solved this for us, but I thought you might want to know. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with encode() and decode() functions
We am having a problem with the encode() and decode() functions. When the second argument to the functions is a quoted string, the functions return values as expected. When the second argument to the functions is another function call, i.e. the char() function, encode() and decode() return SQL syntax errors. The System is specs are: Solaris SunOS sven 5.7 Generic_106541-16 sun4u sparc SUNW,Ultra-5_10, MySQL version 3.23.33 I looked through the bug fix lists of the later versions and could not find a reference to any problem like this on. If it has been fixed, in what version was it fixed? An example from the MySQL command line is as follows: select encode(Password,key); +--+ | encode(Password,key) | +--+ | méLC..É£ | +--+ select decode(Password,key); +--+ | decode(Password,key) | +--+ | ß}ª lÉÛ | +--+ select encode(char(80,97,115,115,119,111,114,100),key); +---+ | encode(char(80,97,115,115,119,111,114,100),key) | +---+ | méLC..É£ | +---+ select decode(char(80,97,115,115,119,111,114,100),key); +---+ | decode(char(80,97,115,115,119,111,114,100),key) | +---+ | ß}ª lÉÛ | +---+ select encode(Password,char(107,101,121)); ERROR 1064: You have an error in your SQL syntax near 'char(107,101,121))' at line 1 select decode(Password,char(107,101,121)); ERROR 1064: You have an error in your SQL syntax near 'char(107,101,121))' at line 1 select encode(char(80,97,115,115,119,111,114,100),char(107,101,121)); ERROR 1064: You have an error in your SQL syntax near 'char(107,101,121))' at line 1 select decode(char(80,97,115,115,119,111,114,100),char(107,101,121)); ERROR 1064: You have an error in your SQL syntax near 'char(107,101,121))' at line 1 Any assistance in solving or finding a workaround for this problem would be greatly appreciated. Ross Rannells Adjunct Professor, Purdue University Donnell Systems Senior Systems Analyst - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
on update problem?
When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: on update problem?
It would seem strange to implement one and not the other? The help does say that you can use it and does keep the create options now. Does anyone know if this will be implemented in the next .5x release? Ross Me writes: Hi! don't think ON UPDATE is supported. The manual : http://www.innodb.com/ibman.html doesn't mention anything about it except : Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. EG When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Official State of Mysql-Max
On the front screen of the mysql site the mysql-Max 3.23.47 is listed as stable. However on the download page it is said to be considered a beta yet? The reason that I am asking this question, is that the Borland Delphi Developers will not release a new version that supports the record locking until this program is OFFICIALLY listed as stable. If it is not yet listed as stable, then is there an ETA? Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BSD/OS 4.1 binary for MySql
Is the freebsd-elf mysql binary available at mysql.com compatible with BSD/OS (bsdi) 4.1? If not, is there a place to download this binary...or am I stuck with building it myself? Thanks, David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dedicated MySQL Application Server
Hi. What I want to do is this: Use mysql as a dedicated database for a desktop application, meaning that basically there's only one client allowed to connect to it, and that client is the application. Also, I would not want this database to interfere with any other instance of MySQL that may be running on the same machine. Basically to be completely wrapped and only accessible by the application. My question is: is there a relatively simple way of doing this? Bryan Ross Programmer iMustPlay.com mailto:[EMAIL PROTECTED] 818-265-0255 x 236 818-948-1596 Pager - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using symbolic links for databases and tables
What about having symbolic links in the same directory as the original file? I have a situation where a broken Windoze application has different queries embedded, with some using tablename(lower case), and some using TABLENAME(upper case). The database actually resides on a Linux server, so file names, and table names, are case-sensitive. The only way I could make the app work was to use tablename (lower case) as the original file name, and create a symlink to it named TABLENAME, in the same directory. Now the app doesn't complain, but when I run queries against both tables, I see that the symlinked one doesn't appear to be updating records, at least not beyond the date that I created the symlink. It's almost as if MySQL has created a real file from the link, but isn't updating it. I feel certain it is not some sort of cache arrangement, because the server has since been restarted. Any ideas what might be going on? Thanks, Ross McCormick Division Maintenance Engineer = YOU MIGHT BE AN ENGINEER IF: The sales people at the local computer store can't answer any of your questions. = !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.3103.1000 name=GENERATOR/HEAD BODY bgColor=#ff style=FONT: 10pt Arial; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVWhat about having symbolic links in the same directory as the original file?nbsp; I have a situation where a broken Windoze application has different queries embedded, with some using tablename(lower case), and some using TABLENAME(upper case). The database actually resides on a Linux server, so file names, and table names, are case-sensitive. The only way I could make the app work was to use tablename (lower case) as the original file name, and create a symlink to it named TABLENAME, in the same directory. Now the app doesn't complain, but when I run queries against both tables, I see that the symlinked one doesn't appear to be updating records, at least not beyond the date that I created the symlink. It's almost as if MySQL has created a real file from the link, but isn't updating it. I feel certain it is not some sort of cache arrangement, because the server has since been restarted./DIV DIVnbsp;/DIV DIVAny ideas what might be going on?/DIV DIVnbsp;/DIV DIVThanks,/DIV DIVnbsp;/DIV DIVnbsp;/DIV DIVRoss McCormickBRDivision Maintenance EngineerBR=BRYOU MIGHT BE AN ENGINEER IF:BRThe sales people at the local BRcomputer store can't answer BRany of your questions.BR=/DIV/BODY/HTML - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.23.39 Solaris binary on Solaris 2.6 (sparc)
A simple question... I noticed that the newer MySQL binaries are listed as being for Solaris 2.7+ (mysql-3.23.39-sun-solaris2.7-sparc.tar.gz) I want to run it on Solaris 2.6, will this run OK?? There didn't seem to be any docs relating to this (would be nice to see these kind of comments in the binary README file). I only have one system (and that is production) so I can't really afford downtime for testing so I need confirmation from someone that it will work OK. I just had a failed attempt at running a binary I had compiled myself, so I thought it would be best to use the standard binary. The system is: (uname -a) SunOS 5.6 Generic_105181-17 sun4u sparc SUNW,Ultra-2 I will be upgrading from binary Distrib 3.22.25, for sun-solaris2.6 (sparc), yes well overdue! Thanks for any feedback. Ross Kendall Technical Developer http://www.bluecarrots.com/ Tel: +44 (0) 20 7479 2789 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: JOIN to the same table multiple times
Ok, I've answered my own question but now have another.
How do I reference the sitename for the 3 sites?
sitename returns the last sitename for all 3 I tried S1.sitename etc. but it
doesn't work.
$query = SELECT * FROM department LEFT JOIN sites S1 ON
deptsite1=S1.sitekey LEFT JOIN sites S2 ON deptsite2=S2.sitekey ORDER BY
$order $dir;
$result = mysql_db_query($dbName,$query);
while ($r=mysql_fetch_array($result)) {
echo tr bgcolor=$colorvalues
tdfont size=\-1\$r[deptdesc]/td
/tr
tr bgcolor=$colorvalues
tdfont size=\-1\$r[sitename]/td
tdfont size=\-1\$r[deptphone1]/td
tdfont size=\-1\$r[deptfax1]/td
tdfont size=\-1\$r[deptemail1]/td
tdfont size=\-1\$r[deptmobile1]/td/tr
tdfont size=\-1\$r[sitename]/td
tdfont size=\-1\$r[deptphone2]/td
tdfont size=\-1\$r[deptfax2]/td
tdfont size=\-1\$r[deptemail2]/td
tdfont size=\-1\$r[deptmobile2]/td/tr
tdfont size=\-1\$r[sitename]/td
tdfont size=\-1\$r[deptphone3]/td
tdfont size=\-1\$r[deptfax3]/td
tdfont size=\-1\$r[deptemail3]/td
tdfont size=\-1\$r[deptmobile3]/td;
-Original Message-
From: Ross Goonan [mailto:[EMAIL PROTECTED]]
Sent: Friday, 8 June 2001 11:47
To: [EMAIL PROTECTED]
Subject: JOIN to the same table multiple times
###
Creating a Telephone / Information Directory with MySQL / PHP3
People belong to a department a site.
Need to be able to:
List all people
List all people within a Department
List all people within a site
List all people within a Department Site
Have set up Department table with site1, site2 site3 as the same
department
exists in multiple sites.
When listing a site, I need to JOIN the site table multiple times to get the
site name.
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey
- Works no worries
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site ON
Site2=Sitekey
- Error 1066: Not unique table/alias 'Site'
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site AS
Sitetable ON Site2=Sitekey
- Error 1052: Column 'Sitekey' in on clause is ambiguos
###
rpm -qa | grep SQL
MySQL-3.23.33-1
MySQL-client-3.23.33-1
MySQL-devel-3.23.33-1
rpm -qa | grep sql
php-mysql-3.0.16-2bc
perl-Msql-Mysql-modules-1.2210-2
###
People Table
Surname
Firstname
Department
site
Department Table
Name
Site1
site2
site3
Site Table
Sitekey
Sitename
###
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOIN to the same table multiple times
### Creating a Telephone / Information Directory with MySQL / PHP3 People belong to a department a site. Need to be able to: List all people List all people within a Department List all people within a site List all people within a Department Site Have set up Department table with site1, site2 site3 as the same department exists in multiple sites. When listing a site, I need to JOIN the site table multiple times to get the site name. SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey - Works no worries SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site ON Site2=Sitekey - Error 1066: Not unique table/alias 'Site' SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site AS Sitetable ON Site2=Sitekey - Error 1052: Column 'Sitekey' in on clause is ambiguos ### rpm -qa | grep SQL MySQL-3.23.33-1 MySQL-client-3.23.33-1 MySQL-devel-3.23.33-1 rpm -qa | grep sql php-mysql-3.0.16-2bc perl-Msql-Mysql-modules-1.2210-2 ### People Table Surname Firstname Department site Department Table Name Site1 site2 site3 Site Table Sitekey Sitename ### - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
