Mysterious 'Lost connection' errors
We are using MySQL 5.0.27 on RedHat Enterprise Linux ES release 4, and the MySQL-python-1.2.1_p2 connector. We are getting intermittent mysterious errors as follows: OperationalError: (2013, 'Lost connection to MySQL server during query') when attempting to connect to the MySQL server (note: on the actual connection attempt, this is before even trying a query). There doesn't appear to be any particular pattern to when these errors occur. The client and server are on different machines, communicating via TCP, but I have not managed to find any networking problems. Does anyone have any suggestions as to what the problem might be, or how we might go about trying to solve it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicating a replicated slave environment
Hi Hank, (CCing the list again so others can see...) Hank wrote: Thanks for your reply. Two things -- I start/stop the sql-thread once daily as a backup strategy. This slave has no application readers or writers. If anything should go wrong with the master, I have one full day to either correct the problem, or restore the master from the slave in case of disaster. I have another server that does the same thing except on a two-day schedule, so in case something goes wrong with the master and the first slave, and I don't get to it in time, I also have a snapshot from two days ago. (these servers do other things, and aren't dedicated mysql rep slaves). Makes sense to me. Second, in your suggestion you say to stop the sql-thread -- I'm assuming you mean stop the IO thread and capture the master log file position -- since that's the only time I'll be able to get the master bin log position AND file in the 'show slave status' command on Slave A. There are actually three file/position markers here: Master_Log_File: usa-bin.000267 Read_Master_Log_Pos: 850675858 Relay_Log_File: nepal-relay-bin.000117 Relay_Log_Pos: 850675993 Relay_Master_Log_File: usa-bin.000267 Exec_Master_Log_Pos: 850675858 Relay_Master_Log_File and Exec_Master_Log_Pos are the slave's coordinates relative to the master. This shows you the position, on the master, to which the slave has replayed changes. The I/O thread is independent of this as you know, so all you need to do is make sure these files haven't been deleted from the master, and you can start the second slave from here. It will re-fetch logs from the master as needed. These values always confuse me -- I always have to refer to the manual page: http://dev.mysql.com/doc/refman/5.0/en/show-slave-status.html. The most relevant parts for you, I think, are Relay_Master_Log_File The name of the master binary log file containing the most recent event executed by the SQL thread. Exec_Master_Log_Pos The position of the last event executed by the SQL thread from the master's binary log (Relay_Master_Log_File). (Relay_Master_Log_File, Exec_Master_Log_Pos) in the master's binary log corresponds to (Relay_Log_File, Relay_Log_Pos) in the relay log. What I think I'll end up doing is stopping both IO+SQL threads when I know Slave A is complete (up to date) with the master, then capture the master position, clone the database files, and restart both slaves. This way, I won't have to mess with the relay logs on Slave A. This will work, too. I only wrote the above in case you need to start another slave from a point when the first slave isn't caught up in the SQL thread. Maybe it's too many options, and I'm just confusing you ;-) I bet a good diagram would help. Maybe I'll make one and submit it for the manual. And I'll try out your checksum tool. I've been wanting something like that -- I've pretty much relied on record counts between my master and slaves to check consistency, and I know that can be misleading. Cool. I hope it helps. Baron thanks, -Hank On 5/8/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Hank wrote: Hello All, I have a 4.1.14 mysql database master and slave set up. For this slave #1, I have the IO thread running constantly, and the SQL thread running once a day to update all pending updates from the master (then I shut it off). So for most of the day, this database is static (except for the collecting relay logs). I have a new machine to be another slave of the same master (slave #2). I can not shut down or lock the master in order to copy the master database to the slave #2 (it is 44GB total, and would take over an hour to copy). I have copied the (static) database from Slave #1 to Slave #2. How can I now configure Slave #2 to process the pending relay-logs and bring it up to date? Obviously I would need to copy (and rename?) the relay logs, but what about the master.info and relay-log.info files? Or in other words, can I use the show slave status information on Slave #1 to setup Slave #2 in the CHANGE MASTER TO command? This shouldn't be too hard to do. I'm curious why you don't leave the SQL thread running, but I guess that's off-topic. Anyway, what you need to do is stop Slave A's SQL thread, look at Slave A's status, clone B from A, and then start Slave B from the master *at Slave A's Exec_Master file and position*. Slave B should then ask the master for whatever binlogs it needs, beginning at the point corresponding to where it was cloned from. If you want, you can copy the binlogs from Slave A over to it, but this is probably trickier. Not that there's anything wrong with doing this, but there might be more ways to make a mistake by looking at the wrong parameter, etc. For the future, if you are running on Linux, one of my favorite things to do is put the MySQL data, temp files, and logs on LVM. This way you
Frequently MyISAM TABLE corruption.....Pls help
Hello all, I have one server which has mysql 5.0.27 installed. There is one table named table1. that table has 122000 records..It has 114 fields and 22 indexes. Now this table always been corrupt. I have try to found the solution but i couldn't. Pls help me ASAP. I have used CHECK and REPAIR option I have given here the output. 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Table 'table1' is marked as crashed and should be repaired 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Sort aborted Database changed mysql check table table1; +--+---+--++ | Table| Op| Msg_type | Msg_text | +--+---+--++ | DB1.table1 | check | error| Wrong bytesec: 111-110-115 at linkstart: 583497784 | | DB1.table1 | check | error| Corrupt | +--+---+--++ 2 rows in set (4.41 sec) mysql repair table table1; +--++--+-+ | Table| Op | Msg_type | Msg_text | +--++--+-+ | DB1.table1 | repair | info | Wrong bytesec: 111-110-115 at 583497784; Skipped| | DB1.table1 | repair | info | Found block that points outside data file at 583497848 | | DB1.table1 | repair | info | Found block that points outside data file at 583497912 | | DB1.table1 | repair | info | Found block with too small length at 583498656; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583498964; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583498988; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499040; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499120; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499176; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499204; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499312; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499368; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499396; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499420; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499448; Skipped | | DB1.table1 | repair | info | Found block with too small length at 583499504; Skipped | | DB1.table1 | repair | status | I have also used myisamchk command and its output is: Checking MyISAM file: /var/lib/mysql5/DB1/table1.MYI Data records: 122089 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 - check data record references index: 4 - check data record references index: 5 - check data record references index: 6 - check data record references index: 7 - check data record references index: 8 - check data record references index: 9 - check data record references index: 10 - check data record references index: 11 - check data record references index: 12 - check data record references index: 13 - check data record references index: 14 - check data record references index: 15 - check data record references index: 16 - check data record references index: 17 - check data record references index: 18 - check data record references index: 19 - check data record references index: 20 - check data record references index: 21 - check data record references index: 22 - check records and index references - recovering (with sort) MyISAM-table '/var/lib/mysql5/DB1/table1.MYI' Data records: 122089 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 - Fixing index 5 - Fixing index 6 - Fixing index 7 - Fixing index 8 - Fixing index 9 - Fixing index 10 - Fixing index 11 - Fixing index 12 - Fixing index 13 - Fixing index 14 - Fixing index 15 - Fixing index 16 - Fixing index 17 - Fixing index 18 - Fixing index 19 - Fixing index 20 - Fixing index 21 - Fixing index 22 So, pls help me ASAP.Thanks in advance... regards, Nilnandan Joshi DBA - INDIA -- View this message in context: http://www.nabble.com/Frequently-MyISAM-TABLE-corruption.Pls-help-tf3715472.html#a10393479 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives:
Re: Mysterious 'Lost connection' errors
we ran into a similar condition using 5.0.27 in a PHP application.. in our case it had nothing to do with the version. check your server logs for evidence of a restart. What we had done was naively imported innodb extents from a v.4 datbase which seemed to work fine at first but in fact setup an edge condition whereby certain perfectly valid SQL was triggering a GPF on the server. I realize that's quite unlikely that you have performed a similar sloppy import but there is likely some edge condition on your server (wierd permissions in the data directory, corruoted tables, etc.) but I still recommend that you scrutinize your server logs for evidence of a spontaneous restart. If that turns up nothing, you might try a fresh install of mysql on a separate host to see if the problem persists. Worst case, there is an upgrade patch available which might magically raise you above the problem. On 5/9/07, Jon Ribbens [EMAIL PROTECTED] wrote: We are using MySQL 5.0.27 on RedHat Enterprise Linux ES release 4, and the MySQL-python-1.2.1_p2 connector. We are getting intermittent mysterious errors as follows: OperationalError: (2013, 'Lost connection to MySQL server during query') when attempting to connect to the MySQL server (note: on the actual connection attempt, this is before even trying a query). There doesn't appear to be any particular pattern to when these errors occur. The client and server are on different machines, communicating via TCP, but I have not managed to find any networking problems. Does anyone have any suggestions as to what the problem might be, or how we might go about trying to solve it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequently MyISAM TABLE corruption.....Pls help
Nilnandan wrote: Hello all, I have one server which has mysql 5.0.27 installed. There is one table named table1. that table has 122000 records..It has 114 fields and 22 indexes. Now this table always been corrupt. I have try to found the solution but i couldn't. Pls help me ASAP. I have used CHECK and REPAIR option I have given here the output. 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Table 'table1' is marked as crashed and should be repaired 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Sort aborted How big is the index file? the data file? Has either reached the file size limit of your filesystem, or the default maximum MyISAM size? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is MySQL IPv6 Compliant ?
Hello Sorry to ask a question already posted, but answers appear not clear and I do not find any information in the documentation. There is a patch for IPv6 at http://www.ngn.euro6ix.org/IPv6/mysql/ fot the 4.0.18 version. Does anyone use this patch with success in her/his company ? Does anyone use MySQL on IPv6 with success in her/his company ? Is IPv6 included in the MySQL roadmap ? Regards Xavier Jeannin -- Xavier Jeannin UREC/CNRS Université P. M. Curie - Tour 65/66 - 4ième étage Courrier : case 171 4, place Jussieu - 75252 PARIS CEDEX 05 Tél : 01 44 27 42 59 - Fax : 01 44 27 42 61 [EMAIL PROTECTED] Vous pouvez vérifier le certificat attaché à ce mail en visitant sur les deux URL : http://igc.services.cnrs.fr/cgi-bin/viewca?cmd=loadCA=CNRS-Standardca=CNRS http://igc.services.cnrs.fr/cgi-bin/viewca?cmd=loadCA=CNRS-Standardca=CNRS-Standard _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Wed, May 09, 2007 at 09:22:34AM -0400, Michael Dykman wrote: I realize that's quite unlikely that you have performed a similar sloppy import but there is likely some edge condition on your server (wierd permissions in the data directory, corruoted tables, etc.) but I still recommend that you scrutinize your server logs for evidence of a spontaneous restart. If that turns up nothing, you might try a fresh install of mysql on a separate host to see if the problem persists. Thanks for your suggestions. The hostname.err log contains absolutely nothing however, just the usual 'mysqld started' and 'Version:' lines. Certainly there's nothing about any restart. Worst case, there is an upgrade patch available which might magically raise you above the problem. Indeed, I see there is 5.0.37 now. I'd rather not go through an upgrade though unless I knew it was likely to fix the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: --xml or --html output to file
Hello John, This works for me as well only I couldn't get the ~/test_file.html syntax to work so I changed it to C:\test_file.html and it worked. Thanks, Randall Price -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 6:20 PM To: [EMAIL PROTECTED] Cc: MySQL Subject: Re: --xml or --html output to file Hi John - using --xml or --html as an argument and redirecting to a file seems to work here. As in, mysql -u me -psecret -D database --html -e select * from that_table ~/test_file.html HTH, Dan On 5/8/07, John Kebbel [EMAIL PROTECTED] wrote: When I try using the --xml or --html option with a batch file using INTO OUTFILE 'dirpath', the --xml or --html option seems to be ignored in favor of the tab-delimited default. (If I get rid of the INTO OUTFILE, xml or html displays fine in the terminal.) I tried using the pager to write to a file from inside MySQL. I succeeded, but it was table data. I couldn't figure out how to add the --xml or --html options from inside the pager. I tried the redirection operator from the command line, but I haven't stumbled on the correct syntax if such a syntax does exist. Does anyone know how to write an --xml or --html file from a SELECT statement, either from the command line or from a batch file? This would really be useful information. Thanks in advance for reading or responding. -- 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: Mysterious 'Lost connection' errors
When we first examined our server logs, we saw the same.. in our case (again) it was only when we slowed down and examined the timestamps on the start/stop messages that we realized that the server was restarting at unexpected intervals. Over the course of our development, we came across this issue a couple of times.. the problem was never found to be on the clint or have anything to do with nefarious sql being executed.. we found binary data issues, build issues (how did you get your binaries? pre-compiled from the archive or build your own? and for what OS?) and server config problems.. When you do find the cause let me know; Im thinking of starting a collection :-) - michael On 5/9/07, Jon Ribbens [EMAIL PROTECTED] wrote: On Wed, May 09, 2007 at 09:22:34AM -0400, Michael Dykman wrote: I realize that's quite unlikely that you have performed a similar sloppy import but there is likely some edge condition on your server (wierd permissions in the data directory, corruoted tables, etc.) but I still recommend that you scrutinize your server logs for evidence of a spontaneous restart. If that turns up nothing, you might try a fresh install of mysql on a separate host to see if the problem persists. Thanks for your suggestions. The hostname.err log contains absolutely nothing however, just the usual 'mysqld started' and 'Version:' lines. Certainly there's nothing about any restart. Worst case, there is an upgrade patch available which might magically raise you above the problem. Indeed, I see there is 5.0.37 now. I'd rather not go through an upgrade though unless I knew it was likely to fix the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
We also have random lost connections on simple queries, but we are running MySQL v5.024a on Windows XP. We've checked the logs and nothing abnormal has turned up and we're using only MyISAM tables with InnoDb disabled. The only thing that I could suggest is to look for other copies of libmysql.dll and make sure you don't have an older version on your path. 3rd party products like to install older copies of these files. As for me, I'll have to upgrade MySQL in the hopes of correcting the problem. It is a PIA to come in in the morning only to find the overnight job has hung because a simple query has lost the connection. :( Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Wed, May 09, 2007 at 11:17:59AM -0400, Michael Dykman wrote: When we first examined our server logs, we saw the same.. in our case (again) it was only when we slowed down and examined the timestamps on the start/stop messages that we realized that the server was restarting at unexpected intervals. The last restart, according to both the content of the log, and the operating system timestamp on the log file itself, was 6 days ago. The 'lost connection' message has happened several times today, however. (how did you get your binaries? pre-compiled from the archive or build your own? and for what OS?) We used the official RPMs from www.mysql.com for our exact version of RedHat Linux (RedHat Enterprise Linux ES Release 4, 64-bit). When you do find the cause let me know; Im thinking of starting a collection :-) I'll keep you informed ;-) -- 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: Installing 2nd instance on windows.
Thanks to all for so good responce. Now I will experiment with it and reply earliest. Thanks CPK -- Keep your Environment clean and green.
Re: 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. What exactly is your question? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help on loading the world.sql(script and data) onto world database
hi, it's my first time trying my hand in inputting data in batches to mysql database and it drives me crazy, please help. *I've created the WORLD database. *I've downloaded WORLD.SQL and unzipped the file. *When I open it in notepad I see the instructions to create the three tables and the instructions for data insertions. As per the load instruction on MySQL page I did the following within mysql: *mysql use world; *mysql source world.sql; I get an error message 'Can't open the file' error: 2. I tried the following at windows command prompt: *C:\Document and Settingsmysql world world.sql I get the message 'I can't get file specified. I don't have any problem connecting to mysql from windows prompt, and world.sql is a sub folder under Documents and Settings. I know I'm green related to mysql, and I need your help. Thanks - It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar.
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]
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: help on loading the world.sql(script and data) onto world database
Kebede, Send some example of WORLD contains. Regards Juan On 5/9/07, kebede teferi [EMAIL PROTECTED] wrote: hi, it's my first time trying my hand in inputting data in batches to mysql database and it drives me crazy, please help. *I've created the WORLD database. *I've downloaded WORLD.SQL and unzipped the file. *When I open it in notepad I see the instructions to create the three tables and the instructions for data insertions. As per the load instruction on MySQL page I did the following within mysql: *mysql use world; *mysql source world.sql; I get an error message 'Can't open the file' error: 2. I tried the following at windows command prompt: *C:\Document and Settingsmysql world world.sql I get the message 'I can't get file specified. I don't have any problem connecting to mysql from windows prompt, and world.sql is a sub folder under Documents and Settings. I know I'm green related to mysql, and I need your help. Thanks - It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar.
Re: Query problem
how do I return a single row per property even if it has 3 or 4 images attached to it. Please reply to the list instead of directly to me. You could do a: select p.from properties p where exists (select i.* from images i where i.property_id = p.property_id) 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? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NOT NULL = Not Working?
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL = Not Working?
Scott Baker wrote:
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
In your last insert example, Last is inserted as 17423. Which is not null.
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --xml or --html output to file
Dan and Paul, I develop at home on Ubuntu Linux and deploy at work over FreeBSD Unix (an iMac running OS 10.4). The command line tip you suggested worked fine on Linux, so I'm assuming that I can get it to run here at work as well when I get the time to try it. Thanks again. John Another Brick in the Wall Kebbel
Re: NOT NULL = Not Working?
Scott Baker escreveu:
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
I think that you are confuse because in your second command you're
setting Last=17423
try this command
INSERT INTO foo (Zip) VALUES (17423);
and you'll see the message error saying that Last can't be NULL
I hope that it helps
--
Ricardo Conrado Serafim
DBA Júnior (MySQL)
URANET - www.uranet.com.br
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL = Not Working?
JamesDR wrote:
Scott Baker wrote:
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
In your last insert example, Last is inserted as 17423. Which is not null.
Yup, empty string, the manual says this...
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
However it does say that to enforce NOT NULL you would have to change
the sql_mode
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
EG:
SET SESSION sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo (zip) VALUES (12345);
SET SESSION sql_mode='';
I get an error on the insert statement:
Field 'Last' doesn't have a default value.
You may need to set sql_mode to STRICT_ALL_TABLES before the insert or
do it in my.cnf or as a command line parameter.
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Hi, I installed mysql 4.1.22 for Debian testing. I launched the manual installation. However, when I do : mysql -u root I get : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) I tried to reinstall it, but it fails. Thanks for help, Thibaud. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger OLD question
Hi All, I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger OLD question
At 4:59 PM -0400 5/9/07, Olaf Stein wrote: Hi All, I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. If you have a primary ID in the table, perhaps you could use a statement something like this: INSERT INTO table_track SELECT * FROM track_table WHERE key_col=OLD.key_col; I admit I have not tested this. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trigger OLD question
You might try: INSERT INTO table_track select OLD.*; -Original Message- From: Olaf Stein [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 09, 2007 2:59 PM To: MySql Subject: Trigger OLD question Hi All, I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. Thanks Olaf -- 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: NOT NULL = Not Working?
I knew it was an option somewhere... Perfect!
Thanks
JamesDR wrote:
JamesDR wrote:
Scott Baker wrote:
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
In your last insert example, Last is inserted as 17423. Which is not null.
Yup, empty string, the manual says this...
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
However it does say that to enforce NOT NULL you would have to change
the sql_mode
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
EG:
SET SESSION sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo (zip) VALUES (12345);
SET SESSION sql_mode='';
I get an error on the insert statement:
Field 'Last' doesn't have a default value.
You may need to set sql_mode to STRICT_ALL_TABLES before the insert or
do it in my.cnf or as a command line parameter.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Thibaud... If you have already set your MySQL root password, then you need to login to mysql like this: mysql -u root -p You should be prompted for the mysql root password. If you haven't set your mysql root password, then you need to do this: mysqladmin -u root password 'new-password' You may have to give the complete path to mysqladmin. CheersCassj -Original Message- From: Thibaud Hulin [EMAIL PROTECTED] Sent: May 9, 2007 4:13 PM To: mysql@lists.mysql.com Subject: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Hi, I installed mysql 4.1.22 for Debian testing. I launched the manual installation. However, when I do : mysql -u root I get : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) I tried to reinstall it, but it fails. Thanks for help, Thibaud. -- 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]
Replace, Substitute, Delete
For years, I've been using FileMaker Pro to generate a staff photo gallery and staff phone directory from the same table of staff information. I'm switching to PHP/MySQL for the year ahead. In STEP 1 below, I concatenate a name for the teacher/staff person image and in STEP 3 I concatenate an XHTML table cell for the image and name. Steps 1 and 3 have been tested and work fine. I don't know how to accomplish STEP 2 however. Suppose I start with a last name like De Long or Van Schmidt? I wind up with de lonxx.jpg or van scxx.jpg for my image names. I have a superstitious dread of putting spaces in Linux/Unix web file names. Could someone suggest a way to replace the in imgName with ? STEP 1: Create the root of the image name update staff set imgName = Lower(CONCAT(Left(last,6),Left(first,2))); STEP 2: How do I delete spaces in the imgName? STEP 3: update staff set webLine = CONCAT(tdimg src='images/,imgName,.jpg' width='100' height='125'br clear='all' /,first, ,last,/td); Thanks in advance for your time spent in reading or responding. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
STEP 1: Create the root of the image name
update staff set imgName = Lower(CONCAT(Left(last,6),Left(first,2)));
STEP 2:
How do I delete spaces in the imgName?
STEP 3:
update staff set webLine = CONCAT(tdimg src='images/,imgName,.jpg'
width='100' height='125'br clear='all' /,first, ,last,/td);
Thanks in advance for your time spent in reading or responding.
Personally, I would move your html and string parse logic into php, rather
than in mysql, but that is up to you. If you want to do this in mysql..
SELECT REPLACE('De Long', ' ', '');
+-+
| REPLACE('De Long', ' ', '') |
+-+
| DeLong |
+-+
1 row in set (0.00 sec)
You could also do..
SELECT REPLACE('De Long', ' ', '%20');
++
| REPLACE('De Long', ' ', '%20') |
++
| De%20Long |
++
1 row in set (0.00 sec)
Which will url encode the space, which will allow perfectly for spaces in
filenames on a web server.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
John Kebbel wrote: For years, I've been using FileMaker Pro to generate a staff photo gallery and staff phone directory from the same table of staff information. I'm switching to PHP/MySQL for the year ahead. In STEP 1 below, I concatenate a name for the teacher/staff person image and in STEP 3 I concatenate an XHTML table cell for the image and name. Steps 1 and 3 have been tested and work fine. I don't know how to accomplish STEP 2 however. Suppose I start with a last name like De Long or Van Schmidt? I wind up with de lonxx.jpg or van scxx.jpg for my image names. I have a superstitious dread of putting spaces in Linux/Unix web file names. Could someone suggest a way to replace the in imgName with ? STEP 1: Create the root of the image name update staff set imgName = Lower(CONCAT(Left(last,6),Left(first,2))); STEP 2: How do I delete spaces in the imgName? STEP 3: update staff set webLine = CONCAT(tdimg src='images/,imgName,.jpg' width='100' height='125'br clear='all' /,first, ,last,/td); Thanks in advance for your time spent in reading or responding. In MySQL itself, check out the Replace() function: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_replace In *nix (and by the way, a space really isn't that big of a deal as people make it out to), you may want to do a perl script to find and replace the spaces. -- The NCP Revue -- http://www.ncprevue.com/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is a better design?
The database server and the web server are on separate machines. Table A contains a record for each user. Let's say Table B contains 'relationship' information. They can be of type 'friend' or 'family'. If a user knows another user, this relationship would be kept in this table, along with the type of relationship. Table B can get big. 10,000's or maybe 100,000's. I'm doing a query in PHP and want to end up with two arrays. One for type friend and one for type family. Which is better: (Method 1) Do ONE query for all the records that meet a certain criteria (let's say 'active'). Then use PHP to loop through the results and put each record into either the friend array or the family array. (Method 2) Do TWO queries. One just for friend. Loop through the records and put into friend array; Then do another query for family...and loop through again. Method (1) needs to evaluate an IF statement in PHP for every record. Method (2) hits the database twice, but doesn't require a PHP IF. (Should I take an extra hit on the database and use Method 2?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
On 4 May 2007 at 1:21, Daevid Vincent wrote: I'm having trouble figuring out the logic/query I want. I know that all those ORs are not right. [snip] WHERE products.enabled = 1 AND( (products.model LIKE 'sony%' OR products.model LIKE '20%' OR products.model LIKE 'tv%') OR (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv') OR (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%') OR (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%') OR (categories.name LIKE '%sony%' OR categories.name LIKE '20%' OR categories.name LIKE '%tv%') ) [snip] It seems to me that the logic you're looking for is something more like (products.model LIKE 'sony%' OR products.upc LIKE 'sony' OR products.name LIKE '%sony%' OR companies.name LIKE 'sony%' OR categories.name LIKE '%sony%' ) AND ( products.model LIKE '20%' OR products.upc LIKE '20' OR products.name LIKE '20%' OR companies.name LIKE '20%' OR categories.name LIKE '20%' ) AND ( products.model LIKE 'tv%' OR products.upc LIKE 'tv' OR products.name LIKE '%tv%' OR companies.name LIKE 'tv%' OR categories.name LIKE '%tv%' ) so that each of the search terms appears in at least one of the relevant columns. -- Iain Alexander [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
-Original Message- From: Iain Alexander [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 09, 2007 3:11 PM To: mysql@lists.mysql.com Subject: Re: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'? On 4 May 2007 at 1:21, Daevid Vincent wrote: I'm having trouble figuring out the logic/query I want. I know that all those ORs are not right. [snip] WHERE products.enabled = 1 AND( (products.model LIKE 'sony%' OR products.model LIKE '20%' OR products.model LIKE 'tv%') OR (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv') OR (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%') OR (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%') OR (categories.name LIKE '%sony%' OR categories.name LIKE '20%' OR categories.name LIKE '%tv%') ) [snip] It seems to me that the logic you're looking for is something more like (products.model LIKE 'sony%' OR products.upc LIKE 'sony' OR products.name LIKE '%sony%' OR companies.name LIKE 'sony%' OR categories.name LIKE '%sony%' ) AND ( products.model LIKE '20%' OR products.upc LIKE '20' OR products.name LIKE '20%' OR companies.name LIKE '20%' OR categories.name LIKE '20%' ) AND ( products.model LIKE 'tv%' OR products.upc LIKE 'tv' OR products.name LIKE '%tv%' OR companies.name LIKE 'tv%' OR categories.name LIKE '%tv%' ) so that each of the search terms appears in at least one of the relevant columns. OMG! I think you are on to something. I just tried this, and I got one row. Exactly what I wanted. I'll have to poke at this some more, and tweak my PHP that autogenerates the SQL, but I may just be naming my first born Iain. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tracing the source of a query
A certain query happened on our server today, that we'd like to find the source of. I can see the query in our binary long... mysqlbinlog today's logfile shows: # at 114047594 #070509 15:29:21 server id 2 end_log_pos 114047722 Query thread_id=1041159 exec_time=0 error_code=0 SET TIMESTAMP=1178738961; [here is the query in question] Is there a way for us to find out: 1. what mysql username issued this query? 2. what IP/hostname that session was connected from? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
James Tu wrote:
The database server and the web server are on separate machines.
Table A contains a record for each user.
Let's say Table B contains 'relationship' information. They can be of
type 'friend' or 'family'.
If a user knows another user, this relationship would be kept in this
table, along with the type of relationship. Table B can get big.
10,000's or maybe 100,000's.
I'm doing a query in PHP and want to end up with two arrays.
One for type friend and one for type family.
Which is better:
(Method 1) Do ONE query for all the records that meet a certain
criteria (let's say 'active'). Then use PHP to loop through the
results and put each record into either the friend array or the family
array.
(Method 2) Do TWO queries. One just for friend. Loop through the
records and put into friend array;
Then do another query for family...and loop through again.
Method (1) needs to evaluate an IF statement in PHP for every record.
Method (2) hits the database twice, but doesn't require a PHP IF.
(Should I take an extra hit on the database and use Method 2?)
-James
Either way, I think you are running into a problem with just having two
arrays. Keep in mind that the relationship is relative, so to speak. A
person who is a friend is not an absolute friend; they are going to be a
friend of somebody else.
With that in mind, assuming that you just want two absolute arrays,
here's what I would suggest (and this is a shot in the dark)
Given:
USER
USER_ID
'more columns
AND
RELATIONSHIP
RELATIONSHIP_ID
FRIEND_A
FRIEND_B
$query = SELECT USER.*,RELATIONSHIP_DESCRIPTION FROM USER LEFT JOIN
RELATIONSHIPS ON (USER.USER_ID = RELATIONSHIP.FRIEND_A OR USER.USER_ID =
RELATIONSHIP.FRIEND_B);
$retval = mysql_query($query) or die(mysql_error);
while ($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
$array[$row[USER_ID];
}
--
The NCP Revue -- http://www.ncprevue.com/blog
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
John Meyer wrote ... you may want to do a perl script to find and replace the spaces. Scott Haneda wrote ... I would move your html and string parse logic into php, If I'm doing data entry for individuals via a web page, Javascript is a third option. = Here's the reasons I was thinking MySQL. (1) I thought it might be quicker than Perl or PHP. Correct me if I'm wrong. (2) I have read a short description of Triggers, and I thought these three lines of code might be an excellent AFTER INSERT trigger. (I don't know enough about Triggers yet to know if they'll even take multiple lines of code however.) (A) Could this be a Trigger? and (B) Would it be worth doing? (3) I've written plenty of Perl and PHP code that concatenates fields and builds XHTML cells and rows. I thought it might be interesting to build the rows inside the database table and have my PHP do nothing but count MySQL records in order to know when to open and close the XHTML table rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
Instead of individual replacements, as in ...
SELECT REPLACE('De Long', ' ', '');
would this global approach work?
SELECT REPLACE(imgName,' ','') FROM staff WHERE imgName REGEXP ' ';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
(1) I thought it might be quicker than Perl or PHP. Correct me if I'm wrong. (2) I have read a short description of Triggers, and I thought these three lines of code might be an excellent AFTER INSERT trigger. (I don't know enough about Triggers yet to know if they'll even take multiple lines of code however.) (A) Could this be a Trigger? and (B) Would it be worth doing? (3) I've written plenty of Perl and PHP code that concatenates fields and builds XHTML cells and rows. I thought it might be interesting to build the rows inside the database table and have my PHP do nothing but count MySQL records in order to know when to open and close the XHTML table rows. I think things just start to get messy over time. One day will come when you want to search something in the database, and you will then have to accommodate that search with more hacks in order to ignore the html. Further, as time goes on, say you wanted to make some design changes to your html, you are now faced with constructing a rather complicated method for updating all your old records to the new html. If the html logic is pulled out into the php/web front end, you change in one spot, the site sees those changes on all pages. Databases are used to store data, I do not really see html as data so to speak, at least not important data. Even storing a path to an image is something I would stay away from, just store the image name in a field, and call the path out in your html. This in large part is my opinion, but I do tend to find most will agree with it. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
Instead of individual replacements, as in ...
SELECT REPLACE('De Long', ' ', '');
would this global approach work?
SELECT REPLACE(imgName,' ','') FROM staff WHERE imgName REGEXP ' ';
I just used that as an example. What you are doing is fine, you put the
field name in the first argument of the REPLACE() function. You no not need
to add the where imgname REGEXP part at all.
SELECT REPLACE(imgName,' ','') FROM staff
That should suffice, unless you have some other limiting factor you want to
toss in like :
SELECT REPLACE(imgName,' ','') FROM staff WHERE imgName LIKE '%d'
You get the idea, that would select all image names that end in 'd'
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Databases are used to store data,
Databases are used to store data This line spoke the loudest to me. Over the years I had become very proficient with FileMaker Pro's built in scripting language. I had even gotten FileMaker to construct the web pages that would be used to connect to FileMaker (including writing the page's Javascript data validation). The line between database and processing language had become very blurred for me. I'll take your advice and keep the XHTML coding in PHP (but I will use Steps 1 and 2 to create the imgName that PHP will use). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
Jon Ribbens a écrit : We are using MySQL 5.0.27 on RedHat Enterprise Linux ES release 4, and the MySQL-python-1.2.1_p2 connector. We are getting intermittent mysterious errors as follows: OperationalError: (2013, 'Lost connection to MySQL server during query') when attempting to connect to the MySQL server (note: on the actual connection attempt, this is before even trying a query). There doesn't appear to be any particular pattern to when these errors occur. The client and server are on different machines, communicating via TCP, but I have not managed to find any networking problems. Does anyone have any suggestions as to what the problem might be, or how we might go about trying to solve it? We found a similar issue because we were using persistent connection in php and had a firewall between the mysql and the webserver. The problem is that our persistent connection were setup for lasting up to something like 8 hours but the firewall was keeping state of the connection only for up to 1 hour when no data was transferred. After the firewall had flushed the state of the connection and that the webserver were trying to communicate through it, he was reporting Lost connection to mysql server during query We adjust the setting of the persistent connection to 45 mins and the problem went away. This don't have anything to do with the version, but that was with 4.1 :) -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Wed, May 09, 2007 at 10:07:41PM -0400, Mathieu Bruneau wrote: We found a similar issue because we were using persistent connection in php and had a firewall between the mysql and the webserver. The problem is that our persistent connection were setup for lasting up to something like 8 hours but the firewall was keeping state of the connection only for up to 1 hour when no data was transferred. After the firewall had flushed the state of the connection and that the webserver were trying to communicate through it, he was reporting Lost connection to mysql server during query It's a good suggestion, but I'm pretty sure there's no firewall acting between the client and the server, and that the connection is not being persisted (i.e. all connection attempts are genuine new connections). I've just upgraded all the clients and servers to 5.0.41 (which looks like it just came out); I'll see what happens. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
