Re: mysql admin clients
Hi. See: http://www.mysql.com/products/mysqlcc/ leegold [EMAIL PROTECTED] wrote: newbie question about mysql admin clients. What are some good ones? And importantly can they access and allow me to edit the db record fields themselves? eg. I see a mis-spelled word in a field - I could go in there and edit the field. Thanks, lee G -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disincluding columns when using load data
Hi.
You may use lines starting by '\t' to ignore first column (if your outfile has default
format).
For example:
select * from t4;
+--+--+--+--+
| a| b| c| d|
+--+--+--+--+
|1 |2 |3 |4 |
|2 |6 |7 |4 |
|3 | 10 | 11 | 20 |
+--+--+--+--+
select * from t4 into outfile 't4.txt';
cat t4.txt
1 2 3 4
2 6 7 4
3 10 11 20
load data infile 't4.txt' into table t3 lines starting by '\t';
select * from t3;
+--+--+--+
| b| c| d|
+--+--+--+
|2 |3 |4 |
|6 |7 |4 |
| 10 | 11 | 20 |
+--+--+--+
Dan Stark [EMAIL PROTECTED] wrote:
I've checked through the archives a documentation but cannot find an
answer to my problem. I'm attempting to load data from a text file
that has a column that is not present in my database table.
Table
B C D
File
A B C D
I've been trying to find a way to ignore the first column of the text
file by specifiing columns like:
(Null,B,C,D) or ('',B,C,D) but mysql doesn't understand these
commands. Is there a way to ignore columns with load data?
TIA
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql admin clients
newbie question about mysql admin clients. What are some good ones? And importantly can they access and allow me to edit the db record fields themselves? eg. I see a mis-spelled word in a field - I could go in there and edit the field. Have a look at Database Workbench: www.upscene.com With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disincluding columns when using load data
Hi Dan,
you can create temporary table 'tmpTable' with columns A, B, C, D,
load data into 'tmpTable ' and then use
INSERT INTO requiredTable (B,C,D)
SELECT tmpTable.B, tmpTable.C, tmpTable.D FROM tmpTable
HTH,
Dusan
- Original Message -
From: Dan Stark [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, November 01, 2004 9:52 PM
Subject: Disincluding columns when using load data
I've checked through the archives a documentation but cannot find an
answer to my problem. I'm attempting to load data from a text file
that has a column that is not present in my database table.
Table
B C D
File
A B C D
I've been trying to find a way to ignore the first column of the text
file by specifiing columns like:
(Null,B,C,D) or ('',B,C,D) but mysql doesn't understand these
commands. Is there a way to ignore columns with load data?
TIA
--
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: Updating rows from a query
In article [EMAIL PROTECTED],
Stephen Rasku [EMAIL PROTECTED] writes:
I am using the C API with MySQL 4.0.17 on QNX 6.2.1b.
I want to update the rows that are returned as I get them. Is this
possible. Here's a simplified version of what I am trying to do:
query = select seqNo, priority from packet where timestamp now() -
interval 15 second order by priority desc, timestamp;
mysql_query(mysql, query);
result = mysql_use_result(mysql);
while (row = mysql_fetch_row(result))
{
seqNo = atoi(row[0]);
priority = atoi(row[1]);
sprintf(updateStr, update packet set timestamp = now() where
seqNo = %d, seqNo);
mysql_query(mysql, updateStr);
}
I guess this is an oversimplification. Often you can use a single SQL set
operation instead of a loop. Your example would probably be the same as
UPDATE packet
SET timestamp = now()
WHERE timestamp now() - INTERVAL 15 SECOND
Maybe you should explain what you're trying to achieve, not what you
think how to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql admin clients
On 1 Nov 2004, at 22:57, leegold wrote: newbie question about mysql admin clients. What are some good ones? And importantly can they access and allow me to edit the db record fields themselves? eg. I see a mis-spelled word in a field - I could go in there and edit the field. I use one called YourSQL on the Macintosh, and I think it's absolutely superb. I use it specifically for the purpose you describe, fixing minor blips in the error, where it would not be sensible to write a whole admin interface. In a previous job I used to use one for Windows called MySQL-Front, this was very good. I think Kmysql on Linux has a very similar interface. There you go, take your pick depending on the OS you use ! -- Regards, Andy Davidson Geek, http://fotoserve.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I export a set of data nightly
$ mysql -u mysqlusername -e 'insert your sql here' On Mon, 1 Nov 2004, Scott Haneda wrote: My query works: (version 4) SELECT u.id, r.user_id, u.first_name, u.middle_name, u.last_name, u.company, u.department, u.address, u.address2, u.city, u.state, u.country, u.zip, u.phone, u.fax, u.email, DATE_FORMAT(u.updated, '%m/%d/%Y'), DATE_FORMAT(u.added, '%m/%d/%Y'), r.serial, r.product, DATE_FORMAT(r.added, '%m/%d/%Y') FROM user as u INNER JOIN registered_serials as r WHERE u.id = r.user_id LIMIT 10 However, I need to run this once a day on schedule via cron, but I am not sure how to feed this statement into mysql from bash, can someone point me to the correct way? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to read TINYBLOB fields from the database
Hello, Just hooked up to this list as last resort Searched internet for some weeks now but still not found any sollution/example. If you search about BLOB, you always get results of storing gifs, jpg's and so on. I have (RO)access to an database and some tables have fields with TINYBLOB types. They are not pictures or other binary kind of files stored in those fields. They are just numbers and words. If i do a select * from table i do only see some garbage as result. Also i tried the select substring(field,0,255) from table but no result at all. I use the mysql commands in a TCL environment (with an tcl mysql library). So you feel my question... How to read human readable values from those fields so i can process them furter with tcl ? Thank for any reply. Greetings, Danny Terweij -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out of my depth.
Hi All, I have built a search application in php/libcurl and I store its results in MySQL. The problem is that I am not used to dealing with the sizes of tables my search application produces, roughly around 400,000 rows in a table the last time I got ran it correctly. Right to my problem. I fork 20 versions of my spider at a time, these forks all have a connection to the database and do selects, inserts and updates on the same table at once. When its going at full steam I can be inserts 1000s of rows a minute. I am using MyISAM as I need its fulltext search cabablities. I remove the fulltext index before I start any changes to the table. I am finding that my php script is not being able to select from the database at random points, I have tracked this down to a 127 erros, the table it corrupt. Before I start my spiders (before it forks) I run myisamck -r on my .MYI file but it corrupts during the scripts execution time and this means it is no longer able to select from the DB (Curcial to know if its needing updated or inserted as a new record) Any hints, any more information needed from me etc would be great. My table struture is: CREATE TABLE thetable ( id int(11) NOT NULL auto_increment, sid int(11) NOT NULL default '1', pid varchar(14) NOT NULL default '0', tid varchar(255) NOT NULL default '', cid varchar(255) NOT NULL default '', location text NOT NULL, number int(14) NOT NULL default '0', image text NOT NULL, description text NOT NULL, link text NOT NULL, uo tinyint(1) NOT NULL default '0', sd tinyint(1) NOT NULL default '0', added int(14) NOT NULL default '0', new tinyint(4) NOT NULL default '1', old tinyint(4) NOT NULL default '0', PRIMARY KEY (id), KEY sid (sid), KEY old (old), KEY new (new), KEY sd (sd), KEY uo (uo), KEY pid (pid), KEY tid (tid), KEY cid (cid) ) Ta, John ___ Have your own email and web address for life. http://www.homemaster.net - Homemaster. Come Together. Online. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Utf8 collations
Hi. See: http://dev.mysql.com/doc/mysql/en/Charset-config-file.html Brown, Brooks [EMAIL PROTECTED] wrote: All of the unicode collations listed in the reference manual except the = binary collations are not sensitive to diacritical marks. That is, if I = do the following: create table t ( filename varchar(260) ) type=3DInnoDB CHARACTER SET = utf8 collate utf8_unicode_ci; -- insert an e-acute insert into t values ( x'c3a9' );=20 mysql select * from t where filename =3D 'e'; +--+ | f| +--+ | =E9| +--+ The problem is that e really isn't the same as e-acute for the file = system. Ideally, what I want is a collation that is case insensitive, = but is sensitive to diacritical symbols, but a case sensitive collation = would be okay if it were sensitive to diacritical symbols? Is there = none available for utf8 as the manual indicates? If not, how difficult = would it be to develop one? I am using 4.1.3 on Mac OS X. Brooks R. Brown Software Engineer Extensis, Inc. http://www.extensis.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
size of indexes with prefixes
Hi - I've been trying different prefixes on my index I'm getting funny results: in short: an index on a column-prefix is bigger than an index on the whole column using show table status I get total index size as: 12147712 - without index 13239296 - with full index 13455360 - with index on first 5 chars 13157376 - with index on first 3 chars - i.e., the prefix-index is *bigger* than the full index - does this make sense? The column is a varchar(255). About 2/3 of the entries are empty-string, the other 1/3 have avg length 8-chars, std 3.5. Would making the empty-strings null make any difference to the size of the index? Thanks in advance everybody. (Using 4.0.20) P.S. - there's not a simple way of getting size of individual index sizes is there? Is it a direct function of the cardinality when you do show indexes on [table] Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB problem, yet performance increased!?
Hi Recently we performed an 'alter table' on a dev box to change from myisam to innodb, and it resulted in considerable speed improvement. in the lab setting. Unfortunately when we repeated on the live server, whilst we got a 200% performance boost (estimate) again, we failed to notice that innodb is in DISABLED state, and yet alter table returned ok. Looking at the tables on the disk, the only innodb references we can find were last modified a few days ago, whereas the myisam versions have the current date. We therefore conclude that the alter table command failed siliently (really bad bug that), however we cannot explain the performance boost. Clearly before shutting anything down or fixing things, we would like to know if these tables are safely still myisam. We would also like to know where the performance boost has come from - one of the alter tables commands took roughly 45 minutes to finish - it did something, we just don't know what! Any help duly appreciated. -- James Green Systems Administrator, StealthNET Ltd, www.stealthnet.co.uk Tel: 0870 800 1777 Intl: +44 1493 660066 Fax: 0870 135 1069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where is 4.1.7 max 32 bit binary for Solaris?
Is there any plan to release a 32-bit binary 4.1.7 MySQL max for Solaris 9 and Solaris 8? I see all the other binaries for Solaris for everything - any reason to exclude the 32 bit? Thx, R
mySQL Batch Scripting
I am trying to utilize MySQL's batch execution functionality by using build scripts to
create tables in my database (ref:
http://dev.mysql.com/doc/mysql/en/Batch_Commands.html )
Is it possible to execute additional source files from within a source file from the
MySQL command line?
Ex:
mysql \. c:\file1.sql ## execute the first sql batch file
file1.sql
create table employees{...};
\. c:\file2.sql; ## execute the second sql batch file
eof
file2.sql
create table customers{...};
eof
I 've tried this and get an error: failed to open file 'c:\file2.sql;', error 2
[ANN]: DBACentral for MySQL v.1.2.1 has been released
Hello All, The new version of DBACentral for MySQL has been released. Since now you can use a special DBACentral Run-Time Edition, work with views (in MySQL 5.0.1), stored procedures and functions (in MySQL 5.0.0), print the relationships diagram and use a bunch of new features. Download page: http://microolap.com/dba/mysql/dbacentral/download.htm Full list of current changes: [+] The DBACentral run-time edition is released and added into the DBACentral downloads. It is a special environment for the end-user to work with the profiles (queries, data forms) you design. Currently the DBACentral for MySQL single developer license already includes five run-time licenses. [+] Added support of stored procedures and functions in MySQL 5.0. Now DBACentral allows you to design and execute stored procedures and functions in easy-to-use graphical mode. [+] Added support of data views in MySQL 5.0.1. Now you can use all features of the latest MySQL versions in DBACentral. [+] The ability to print the relationships diagram is added. [+] The Import External Profile feature is added. Now you can import queries, forms, and relationships from other profile files. [+] The Auto Query and Auto Form features are greatly improved and the new Object Automation dialog is added. New Auto View and Auto Procedure features are also implemented. [+] Added new form component: now you can put buttons on the form and program their actions in run-time. [+] Form Designer: added the 'Undo/Redo' feature for all modifications you make to the form. [+] Form Designer: added the ability to convert a component into another type without re-creating the component. [+] Form Designer: new possibilities in designing the lookup components added. [-] Fixed bug with deleting fields in the table designer. [-] Fixed bug with the CSV and TXT import. [-] A lot of bugs with MS Access and ADO data import is fixed. [-] A bug with displaying the TIMESTAMP values in data grids is fixed. [-] Several bugs with relationship drawing is fixed. -- Best regards, Edward Smirnov microOLAP Technologies LTD mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problem, yet performance increased!?
In article [EMAIL PROTECTED], James Green [EMAIL PROTECTED] writes: Unfortunately when we repeated on the live server, whilst we got a 200% performance boost (estimate) again, we failed to notice that innodb is in DISABLED state, and yet alter table returned ok. It's one of the ugliest misfeatures of MySQL that it doesn't always complain loudly, but instead tries to silently do what it thinks is right. We therefore conclude that the alter table command failed siliently (really bad bug that), however we cannot explain the performance boost. Probably the ALTER TABLE got silently changed to OPTIMIZE TABLE. Clearly before shutting anything down or fixing things, we would like to know if these tables are safely still myisam. Yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problem, yet performance increased!?
You can determine table type with SHOW CREATE TABLE table_name or SHOW TABLE STATUS LIKE 'table_name'; From the manual http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html, If a storage engine is specified that is not available, MySQL uses MyISAM instead. That applies to ALTER as well as to CREATE. Starting with 4.1.1, you get a warning when that happens. In earlier versions, it's silent. Also from the manual http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. Your performance increase is probably due to better organized data and rebuilt indexes resulting from the copy operation. Michael James Green wrote: Hi Recently we performed an 'alter table' on a dev box to change from myisam to innodb, and it resulted in considerable speed improvement. in the lab setting. Unfortunately when we repeated on the live server, whilst we got a 200% performance boost (estimate) again, we failed to notice that innodb is in DISABLED state, and yet alter table returned ok. Looking at the tables on the disk, the only innodb references we can find were last modified a few days ago, whereas the myisam versions have the current date. We therefore conclude that the alter table command failed siliently (really bad bug that), however we cannot explain the performance boost. Clearly before shutting anything down or fixing things, we would like to know if these tables are safely still myisam. We would also like to know where the performance boost has come from - one of the alter tables commands took roughly 45 minutes to finish - it did something, we just don't know what! Any help duly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump
-Original Message- From: Josh Howe [mailto:[EMAIL PROTECTED] Sent: November 1, 2004 12:31 PM To: [EMAIL PROTECTED] Subject: mysqldump Hi, Can anybody help me with a linux newbie question. I want to use mysqldump to backup all of the tables in a database that start with z_. Can I do this in linux with a single line? Thanks. #!/bin/bash TABLES=`echo show tables | mysql -pyourpassword -hlocalhost your_db`; for table in $TABLES do # set your condition below if [ $table = ];then echo $table mysqldump your_db $table /path/to/dump/$table fi done _ Steve Poirier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max allowed packet error
HI, I have copied the mysql-medium.cnf to /etc/my.cnf and have noticed that the max-allowed-packet is 1M. no matter what I set it to 8M, 10M, 16M I still get the error. did you try this in your my.cnf in the [mysqld] section ? set-variable= max_allowed_packet=8M -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating rows from a query
On 02 Nov 2004 11:11:46 +0100, Harald Fuchs [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED], I guess this is an oversimplification. Often you can use a single SQL set operation instead of a loop. Your example would probably be the same as UPDATE packet SET timestamp = now() WHERE timestamp now() - INTERVAL 15 SECOND Maybe you should explain what you're trying to achieve, not what you think how to do it. Sure. I am using MySQL to store packets for transmission. I want to send the oldest, highest priority packets first. Once I send it, I don't want to resend it for at least 15 seconds so I have time to wait for an acknowlegement. Based on external events, I may exit the loop before processing all the rows. In this case, I only want to update the timestamp on rows that I processed. Any rows that I haven't processed yet should keep the old timestamp. ...Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: max allowed packet error
follow these steps 1) first shutdown the database mysqladmin shutdown 2) Start the mysqldeamon bin/safe_mysqld --max_allowd_packet=16M --user=mysql 3) to check what max allowd_packet it is using mysqladmin variables in the out-put check the value of variable max_allowed_packet Anil DBA -Original Message- From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 02, 2004 8:13 PM To: Ferino Mardo; [EMAIL PROTECTED] Subject: Re: max allowed packet error HI, I have copied the mysql-medium.cnf to /etc/my.cnf and have noticed that the max-allowed-packet is 1M. no matter what I set it to 8M, 10M, 16M I still get the error. did you try this in your my.cnf in the [mysqld] section ? set-variable= max_allowed_packet=8M -- Philippe Poelvoorde COS Trading Ltd. -- 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: mysqldump
Thanks Spenser, but I'd already tried something similar. What I get when I try this is: mysqldump: Can't get CREATE TABLE for table `z_*` (Table 'sinu_com.z_*' doesn't exist) -Original Message- From: Spenser [mailto:[EMAIL PROTECTED] Sent: Monday, November 01, 2004 6:30 PM To: Josh Howe Subject: Re: mysqldump Take a look at this article: http://www.unixreview.com/documents/s=8989/ur0408d/ There's a section on mysqldump if you prefer using it. You would do something like the following to backup only certain tables based on their names starting with z_. mysqldump -u root -p -x -e db1 z_* /tmp/backup/db1_table1.sql On Mon, 2004-11-01 at 11:30, Josh Howe wrote: Hi, Can anybody help me with a linux newbie question. I want to use mysqldump to backup all of the tables in a database that start with z_. Can I do this in linux with a single line? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating rows from a query
In article [EMAIL PROTECTED], Stephen Rasku [EMAIL PROTECTED] writes: I guess this is an oversimplification. Often you can use a single SQL set operation instead of a loop. Your example would probably be the same as UPDATE packet SET timestamp = now() WHERE timestamp now() - INTERVAL 15 SECOND Maybe you should explain what you're trying to achieve, not what you think how to do it. Sure. I am using MySQL to store packets for transmission. I want to send the oldest, highest priority packets first. Once I send it, I don't want to resend it for at least 15 seconds so I have time to wait for an acknowlegement. Based on external events, I may exit the loop before processing all the rows. In this case, I only want to update the timestamp on rows that I processed. Any rows that I haven't processed yet should keep the old timestamp. I see. Me thinks it's fundamentally unclean to iterate through a result set and changing rows while doing so. You could remember the pkeys of the rows processed and then do a single UPDATE ... WHERE pkey IN (...) after the loop, but maybe this might not be necessary at all: how about SELECTing only a single row (with LIMIT), processing that row, UPDATEing it, and all that within some loop? You're doing more SELECTs that way, but with the proper indexes this might not be a problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication basics
Hi everyone, I've been browsing the online archives for a while and haven't found as much data as I'd like to feel really comfortable about replication. I've been a pretty busy developer in terms of using MySQL, but never so much in the way of administration. Also, I've only ever had to deal with a single machine until about a month ago when I was asked to extend our database to multiple machines for security and redundancy. As it stands right now, I have 3 machines running MySQL, set up as hosts db1, db2 and db3. db1 is a master db2 and db3 are set as slaves, and replicate data just fine with the premise that we'd like to add numerous slave machines at any time. Problems I'm having: 1. If a new database is created on db1, this is not replicated on db2 and db3. Should my permissions be GRANT FILE on *.* ... or is there a better way to automate duplicating this database on the slaves? 2. If a database exists on all three machines already and I create a table on db1, this is also not replicated on db2 or db3. 3. I haven't checked yet whether ALTER TABLE ... commands have worked across the replication. 4. Darn RedHat and their logrotate utility: db1 was having MySQL restart once a week, which created db1.001, db1.002, db1.003, etc., every time it restarted, yet db2 and db3 also running logrotate and having MySQL restart because of it, were not updating their master.info files to point at the .002 or .003 or .004 files whenever db1 was restarted. This also happens if db1 reboots. 5. In the event of power failure, or system failure, if db1 is offline, how can I set the systems so either db2 or db3 becomes a master? And if db1 comes back online later, could I set it as a slave to whichever other machine became a master? Can this be automated, or will it always require manual intervention? 6a. I rewrote my Perl applications to connect to db1 and return a handle I call $dbh_w for any SELECT/INSERT/UPDATE/DELETE queries, and to connect to any of db1/db2/db3 for any SELECT queries as $dbh_r. However, we want to use some third-party software that contains a few MB of PHP code, and only connects to the database one time - I'm not sure how much time to allocate (to tell my CTO/CEO) to rewrite the entire application the same way I did my Perl applications. Any advice? 6b. My Perl scripts are 90% reading data back from the database, the PHP scripts are more like 80% writing to the database so being able to load-balance the writing to the database farm is ideal. In my current scenario, the PHP application can only write to db1, which will ultimately cap out the machine. Surely there's a better way than making two connections ($dbh_w/$dbh_r) to the database for each copy of my Perl or PHP processes running? 7. Finally, would 'fake replication' work if the MySQL database files were on a RAID system and mounted via NFS to multiple machines to actually run the MySQL engine? The tables we use have a lot of auto_increment fields, and I'd be worried about data being corrupted or lost. Thanks, Ian Douglas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump
Did you try this one? I simplified it for you. You just need to put this in a file and chmod +x the file. Then you can execute it from a crontab or with the shell. ~~~ Copy/paste after this ~~~ #!/bin/bash # Modify following variables # script will dump the tables that contain the following in the name expression_to_match=z_ # database name database=database_name # database host database_host=localhost # directory to dump tables (put ending /) dump_path=/path/to/dump/ TABLES=`echo show tables | mysql -h$database_host $database | grep $expression_to_match` for table in $TABLES do echo $table mysqldump $database $table $dump_path$table done # end script ~~ end copy/paste ~~ You may need to twea the TABLES= command if you're using a password. Same for mysqldump if you want to put more options such as --opt _ Steve Poirier -Original Message- From: Josh Howe [mailto:[EMAIL PROTECTED] Sent: November 2, 2004 11:47 AM To: Spenser; [EMAIL PROTECTED] Subject: RE: mysqldump Thanks Spenser, but I'd already tried something similar. What I get when I try this is: mysqldump: Can't get CREATE TABLE for table `z_*` (Table 'sinu_com.z_*' doesn't exist) -Original Message- From: Spenser [mailto:[EMAIL PROTECTED] Sent: Monday, November 01, 2004 6:30 PM To: Josh Howe Subject: Re: mysqldump Take a look at this article: http://www.unixreview.com/documents/s=8989/ur0408d/ There's a section on mysqldump if you prefer using it. You would do something like the following to backup only certain tables based on their names starting with z_. mysqldump -u root -p -x -e db1 z_* /tmp/backup/db1_table1.sql On Mon, 2004-11-01 at 11:30, Josh Howe wrote: Hi, Can anybody help me with a linux newbie question. I want to use mysqldump to backup all of the tables in a database that start with z_. Can I do this in linux with a single line? Thanks. -- 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: Updating rows from a query
On 02 Nov 2004 18:27:52 +0100, Harald Fuchs [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED], Maybe you should explain what you're trying to achieve, not what you think how to do it. Sure. I am using MySQL to store packets for transmission. I want to send the oldest, highest priority packets first. Once I send it, I don't want to resend it for at least 15 seconds so I have time to wait for an acknowlegement. Based on external events, I may exit the loop before processing all the rows. In this case, I only want to update the timestamp on rows that I processed. Any rows that I haven't processed yet should keep the old timestamp. I see. Me thinks it's fundamentally unclean to iterate through a result set and changing rows while doing so. You could remember the pkeys of the rows processed and then do a single UPDATE ... WHERE pkey IN (...) after the loop, but maybe this might not be necessary at all: how about SELECTing only a single row (with LIMIT), processing that row, UPDATEing it, and all that within some loop? You're doing more SELECTs that way, but with the proper indexes this might not be a problem. I was originally using limit 1 to do this processing. However, I could get all the records in the same amount of time as it took to get one. So, for example, I can process thousands of records in four seconds or just one. I did resolve my problem, however. I created a temporary database connection to do the updates. ...Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master-Slave bin-logs problem
Hi list, I have 2 MySQL servers ver. 3.23.58, one is the master and the other one the slave, the slave was working until I modified the following lines in the master my.cnf file: log-bin = /mnt/binlogs/masterBinLogs/sauron-bin the directory and files have the following attributes: drwxr-xr-x2 mysqlmysql masterBinLogs -rw-rw1 mysqlmysql sauron-bin.387 Now when I restart the slave I have the following error: Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'sauron-bin.387' at position 712121279 Error reading packet from server: Could not find first log (server_errno=65535) Did I miss something in the my.cnf file of each server?, When I give the SHOW MASTER LOGS command displayed this info: +-+ | Log_name | +-+ | sauron-bin.387 | | sauron-bin.388 | | sauron-bin.389 | +-+ Thanks in advanced for your help, an I hope you can help me guys. Mikel _ Platica con tus amigos en linea con T1msn Messenger http://messenger.t1msn.com.mx/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump
Thanks, I haven't tried this yet. Iwant to generate one big file, not one file per table. I guess I can replace the code in the for loop to just build the mysqldump command. Thanks for the help! -Original Message- From: Steve Poirier [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 02, 2004 1:44 PM To: 'Josh Howe'; [EMAIL PROTECTED] Subject: RE: mysqldump Did you try this one? I simplified it for you. You just need to put this in a file and chmod +x the file. Then you can execute it from a crontab or with the shell. ~~~ Copy/paste after this ~~~ #!/bin/bash # Modify following variables # script will dump the tables that contain the following in the name expression_to_match=z_ # database name database=database_name # database host database_host=localhost # directory to dump tables (put ending /) dump_path=/path/to/dump/ TABLES=`echo show tables | mysql -h$database_host $database | grep $expression_to_match` for table in $TABLES do echo $table mysqldump $database $table $dump_path$table done # end script ~~ end copy/paste ~~ You may need to twea the TABLES= command if you're using a password. Same for mysqldump if you want to put more options such as --opt _ Steve Poirier -Original Message- From: Josh Howe [mailto:[EMAIL PROTECTED] Sent: November 2, 2004 11:47 AM To: Spenser; [EMAIL PROTECTED] Subject: RE: mysqldump Thanks Spenser, but I'd already tried something similar. What I get when I try this is: mysqldump: Can't get CREATE TABLE for table `z_*` (Table 'sinu_com.z_*' doesn't exist) -Original Message- From: Spenser [mailto:[EMAIL PROTECTED] Sent: Monday, November 01, 2004 6:30 PM To: Josh Howe Subject: Re: mysqldump Take a look at this article: http://www.unixreview.com/documents/s=8989/ur0408d/ There's a section on mysqldump if you prefer using it. You would do something like the following to backup only certain tables based on their names starting with z_. mysqldump -u root -p -x -e db1 z_* /tmp/backup/db1_table1.sql On Mon, 2004-11-01 at 11:30, Josh Howe wrote: Hi, Can anybody help me with a linux newbie question. I want to use mysqldump to backup all of the tables in a database that start with z_. Can I do this in linux with a single line? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication basics
DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: ian douglas [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 02, 2004 9:30 AM To: [EMAIL PROTECTED] Subject: Replication basics Hi everyone, I've been browsing the online archives for a while and haven't found as much data as I'd like to feel really comfortable about replication. I've been a pretty busy developer in terms of using MySQL, but never so much in the way of administration. Also, I've only ever had to deal with a single machine until about a month ago when I was asked to extend our database to multiple machines for security and redundancy. As it stands right now, I have 3 machines running MySQL, set up as hosts db1, db2 and db3. db1 is a master db2 and db3 are set as slaves, and replicate data just fine with the premise that we'd like to add numerous slave machines at any time. Problems I'm having: 1. If a new database is created on db1, this is not replicated on db2 and db3. Should my permissions be GRANT FILE on *.* ... or is there a better way to automate duplicating this database on the slaves? Yes put in you're my.cnf on the master binlog-ignore-db=mysql,test this means the master will replicate any event to any database other then mysql, test. 2. If a database exists on all three machines already and I create a table on db1, this is also not replicated on db2 or db3. Why not? Are you explicitly replicating a set of tables from your slaves via replicate-do-table=foo.bar? 3. I haven't checked yet whether ALTER TABLE ... commands have worked across the replication. Yes they do. 4. Darn RedHat and their logrotate utility: db1 was having MySQL restart once a week, which created db1.001, db1.002, db1.003, etc., every time it restarted, yet db2 and db3 also running logrotate and having MySQL restart because of it, were not updating their master.info files to point at the .002 or .003 or .004 files whenever db1 was restarted. This also happens if db1 reboots. Stop the logrotate script. 5. In the event of power failure, or system failure, if db1 is offline, how can I set the systems so either db2 or db3 becomes a master? And if db1 comes back online later, could I set it as a slave to whichever other machine became a master? Can this be automated, or will it always require manual intervention? Use NDB or write your own load / failover software. 6a. I rewrote my Perl applications to connect to db1 and return a handle I call $dbh_w for any SELECT/INSERT/UPDATE/DELETE queries, and to connect to any of db1/db2/db3 for any SELECT queries as $dbh_r. However, we want to use some third-party software that contains a few MB of PHP code, and only connects to the database one time - I'm not sure how much time to allocate (to tell my CTO/CEO) to rewrite the entire application the same way I did my Perl applications. Any advice? 6b. My Perl scripts are 90% reading data back from the database, the PHP scripts are more like 80% writing to the database so being able to load-balance the writing to the database farm is ideal. In my current scenario, the PHP application can only write to db1, which will ultimately cap out the machine. Surely there's a better way than making two connections ($dbh_w/$dbh_r) to the database for each copy of my Perl or PHP processes running? Look at DBI::Multiplex 7. Finally, would 'fake replication' work if the MySQL database files were on a RAID system and mounted via NFS to multiple machines to actually run the MySQL engine? The tables we use have a lot of auto_increment fields, and I'd be worried about data being corrupted or lost. No, not reliable. Locks would be ignored via NFS and mysql will detect that the index file changed on server b marking the record or index file as crashed. Thanks, Ian Douglas -- 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 basics
1. If a new database is created on db1, this is not replicated on db2 and db3. ... is there a better way to automate duplicating this database on the slaves? Yes put in your my.cnf on the master binlog-ignore-db=mysql,test this means the master will replicate any event to any database other then mysql, test. So, by your statement: binlog-ignore-db= ... and leaving it blank, will tell MySQL to replicate EVERY database? That's the behavior that I want. I will not be ignoring any databases on the master - all data on the master (databases, tables, data) must exist on every system, at least for the time being. Later on, we may replicate only certain tables onto certain slaves depending on our user load and which databases are more frequently used. However, for the foreseeable future, all data must exist on all machines. 2. If a database exists on all three machines already and I create a table on db1, this is also not replicated on db2 or db3. Why not? That's my question too. I haven't seen anything on how to allow this behavior yet. I've seen some text about LOAD TABLE tablename FROM MASTER but I need a way to automate this so there is less manual intervention required. Are you explicitly replicating a set of tables from your slaves via replicate-do-table=foo.bar? I need to replicate everything, and I'm not setting any options like this in the slave's my.cnf. Each slave must be a complete mirror copy of the master. 3. I haven't checked yet whether ALTER TABLE ... commands have worked across the replication. Yes they do. To finish my statement: ... in my implementation - that is, I haven't tested my implementation to know if an ALTER TABLE ... query works or not. 4. Darn RedHat and their logrotate utility: ... created db1.001, db1.002, db1.003, etc., ... This also happens if db1 reboots. Stop the logrotate script. Which I did last week when I realized this was why my slaves hadn't sync'd up in a week... But this past weekend a few transformers blew in the neighborhood around our office and a power surge shut off db1 (master) - when it came back online, it created a db1.002 file and updated its own db1.index file, yet the two slave machines did not update their master.info records. How can I automate the slaves to detect this change, and use the db1.002 file? (db2 and db3 were not shut off, they kept running just fine) 5. In the event of power failure, or system failure, if db1 is offline, how can I set the systems so either db2 or db3 becomes a master? write your own load / failover software. Thanks for the pointer. Was hoping there was some sort of round-robin scenario. 6. [paraphrase] Rewriting Perl and PHP code Look at DBI::Multiplex ... anyone know of a PHP implementation of this? It would sure save me some time. 7. Finally, would 'fake replication' work if the MySQL database files were on a RAID system and mounted via NFS No, not reliable. Noted, thank you for saving me some grief. -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Building new db linux box, Hardware questions
Hello, I am building a new linux box , just to be an mysql server I have a couple of questions. 1) would it be better to go brandnew with a single processor or like a quad p4 that's a year or two old. 2) I am going to running raid 5, so I assume that I should run scsi drives? 3) my database is not really huge, any tricks on how to determine how much ram I need? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I store Images in the MySQL Database
Have you tried my MySQL C API Site ? I have posted some C API examples here. http://www.geocities.com/jahan.geo Aftab Jahan Subear Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh Tel: +880-2-7519050 /* Get customized T-Shirts with logo for your IT company for 12 units @ US$14 ~ US$16. Order quantity must be atleast USD$5000++. IT company to IT company. */ Mulley, Nikhil wrote: Hi Lists , My Query is How Can I store Images in the MySQL Database , and how to Index them , can anybosy help me on this please I am starting out a Online Photo Gallery and want to share them , and I want to make the search available, Does anybody has already done on this ? Please help me in this. Regards, Nikhil. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Building new db linux box, Hardware questions
1) would it be better to go brandnew with a single processor or like a quad p4 that's a year or two old. Depends on how your application runs. BTW, I don't think they made quad p4's. You can't run p4 chips in SMP -- they must be Xeon's. 2) I am going to running raid 5, so I assume that I should run scsi drives? I'd recommend getting one of the 3ware 9500 raid controllers and getting SATA drives. You'll save yourself a bundle of money. Although, don't get the WD Raptor drives -- they have a very high failure rate. You can also then run in RAID 10, which, in most cases, is faster than RAID 5. 3) my database is not really huge, any tricks on how to determine how much ram I need? There is a formula somewhere. It's something like shared buffers + (thread buffers * client count) + OS Disk Cache. You'd have to look up what those buffers are to get the exact count. out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect
I am trying to connect from my windows box at home using mysql.exe to a MySQL server at work on a Gentoo box. My command line is: mysql -h host.domain.com -u root -p (of course the host.domain.com is replaced with my server's FQDM) The output is: ERROR 2003: Can't connect to MySQL server on 'host.domain.com' (10061) The default port is 3306 right? It doesn't say access denied so I assume the socket is not even open. How can I find out if MySQL is on the port 3306? What Linux command shows all active ports TCP or UDP? Thanks.
Re: Can't connect
It doesn't say access denied so I assume the socket is not even open. Never assume :o) From your windows box, you could try: telnet host.domain.com 3306 ... if it connects, it connected to *something* How can I find out if MySQL is on the port 3306? What Linux command shows all active ports TCP or UDP? On the linux box: nmap localhost From your windows box, you could get a port scanner tool from download.com (I use one called SuperScanner) Possible things I'd personally look for: 1. does your linux box have a firewall script running? iptables -V -l 1b. if so, does disabling the firewall script allow access? 1c. if so, it could be that your linux firewall isn't letting you in 2. is the linux box on a private LAN IP (192.168.x.x or 10.x.x.x etc) and behind a NAT box (internet connection sharing router, or other gateway system)? 2b. if so, is port forwarding enabled to allow port 3306 to be forwarded to your mysql box? 3. is mysql *running*? (okay, this should have been question #1...) 3b. if so, can you connect to mysql locally on the linux box? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect
-- Original message -- From: none none [EMAIL PROTECTED] Perhaps you did'nt grant access to for your home workstation IP address to have permission to log into the mysql server? Check out the grant command: http://dev.mysql.com/doc/mysql/en/GRANT.html If that was the case the error would be permission denied, not error 2003. Error 2003 usually comes up when the server cannot be reached because of firewall type issues. Previous Message: = I am trying to connect from my windows box at home using mysql.exe to a MySQL server at work on a Gentoo box. My command line is: mysql -h host.domain.com -u root -p (of course the host.domain.com is replaced with my server's FQDM) The output is: ERROR 2003: Can't connect to MySQL server on 'host.domain.com' (10061) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fastest Results
I have a database that contains a cable of 'customers'. 'Customers' does not change on a regular basis but is queried frequently so I have setup a 'customers_cache' table, which is exactly the same as 'customers', except 'customers_cache' is a memory based table. Before doing a query on 'customers_cache' I always do a: SELECT COUNT (*) from customers_cache If I get a result 0, then I assume the customers_cache table is valid. If it is not, I copy all of the data from 'customers' into 'customers_cache' and then start over. I've had this help quite a bit, but of course I'm doing the query... SELECT COUNT (*) from customers_cache a lot. Of course the query is fast but since I do it SO much, I was wondering if anyone had a suggestion of a FASTER method of checking to see if a table has any records in it. mysql version 4.0.22 (soon to move to 4.1.x) MacOS X Server 10.3.5 accessing via php 4.x apache 1.3.x Thanks! Robert C. Best III - [EMAIL PROTECTED] District Technology Coordinator for N.E.R.I.C. at Potsdam Central School Phone: (315) 265-2000 x266 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DELAYED errors - failing in actual insert
I am using INSERT DELAYED ... to insert log records into a MyISAM table. Recently these inserts have stopped showing up in the table, and extended-status shows them coming in by increasing 'Delayed_writes', however 'Delayed_errors' is increasing by the same amount. Obviously there are errors with these inserts, but I have not been able to find any way to determine what these errors are. There is no trace of any problem in host.err. I have tried duplicating the inserts without 'DELAYED' and they work fine. Can anyone explain how to determine why these inserts are failing, ideally without having to bring down the server? Thanks. Chad.
RE: Upgrading MySQL erased all data
Thanks. I passed this on and he found what was lost. I guess since the data directory was owned by mysql, he could not find the databases when doing a MacOS file search. I impressed upon him to use a separate datadir as we do with our servers, thus bypassing this whole thing. Thanks again! -steve-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using an array(-ish) in SQL queries
X-posted to MySQL and PHP DB Hi gang Task at hand: deleting or selecting (same difference) several numbers of records using only 1 query. My first version simply looped through all the ticked off IDs and ran a single query for each delete routine. I've still not suceeded in getting the delete queries to work on multiple tables at once, despite the column names being the same. But besides this: My current version generates, for multi-select cases, queries like this: DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR `ID`='5' OR `ID`='6' or similar with the SELECT statement. On some occasions this can result in a very large amount of OR statements, like for 50 IDs totally. I've been reading through the MySQL manual and the comments in the select and delete parts, but cannot seem to find any mentioning of an easier way to do this. Or it's been deluting me cuz English is my second language, so the MySQL manual doesn't always make much sense to me. I'm looking for something like passing on an array (as comma-seperated-list maybe), and then just do statements like: DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6) Did I totally miss that part of the manual, or is it just not possible with MySQL ? Now, for my script it doesn't really matter much which approach to use, but was more thinking performance wise it ought to be faster and less taxing for the server to parse an SQL statement that's closer to table structure, rather than the OR statements that has to be transformed first. Sorry if I'm just a blind mouse that can't seem to find things in the MySQL manual. It's not really my best friend... TIA Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting and displaying unicode characters
I'm running mysql 4.1.7 on linux. I recently made the upgrade from 4.0.1 to 4.1.7 to get the additional character sets support. I have data getting inserted that has various unicode characters embedded such as the Registered Trademark symbol, and various foreign language characters. my tables are innodb and I set them to use utf-8, macroman and macce character sets, and the unicode characters still display in the browser as the incorrect symbols, such as: . If I set the browser to unicode character encoding, then most of the characters display as black diamonds with question marks in them. I am pretty new to this unicode situation and any suggestions/advice would be greatly appreciated. thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] Using an array(-ish) in SQL queries
-- Original message --
From: -{ Rene Brehmer }- [EMAIL PROTECTED]
Task at hand: deleting or selecting (same difference) several numbers of
records using only 1 query.
My first version simply looped through all the ticked off IDs and ran a
single query for each delete routine. I've still not suceeded in getting
the delete queries to work on multiple tables at once, despite the column
names being the same. But besides this:
Multi-table deletes are new to mySQL 4.0, so if you are running a 3.x release they
won't work.
http://dev.mysql.com/doc/mysql/en/DELETE.html
My current version generates, for multi-select cases, queries like this:
DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR
`ID`='5' OR `ID`='6'
or similar with the SELECT statement.
[snip lots of stuff]
DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6)
use IN http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1268
If you know all the values in the array are escaped and safe you can just use
implode() to make the list for IN
$string = implode(',',$array);
$sql = SELECT FROM $table WHERE col_name IN('$string');
Notice I added single quotes around the string, that is because they will be missing
since implode only sticks the string between array elements.
However, you'd need a join that makes sense for a multi-table delete. I don't know if
it will work with a union, I have never tried, maybe somone else will chime in.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ampersands in the database fields
The subject title is a sedgeway into my question that may slightly of topic but I've asked many sources and don't have an answer yet so I ask it here. I have a text fields of html marked up content which I render via php. Looking at the rendered html page in a text editor I see that ampersands all appear as amp; When I select the field via sql I also see amp; I have a pretty standard seach, Fulltext and substring (ie. %keyword%) searches. There's an acronym in my content: NASDand when I try searching for this it really mucks up. The Fulltext no matter what I try does not get a hit. The substring search will work properly if I put the following in the search form:NAamp;SDthat works. But trying the substring search with NASD produces weird results...kinda works but strangely and affects the rendering of the search results page. So what's the cause of all this? Should I upfront load my db text fields differently, or, search them differently - what is the fix via mysql or php for the ampersand problem? Thanks, Lee G. Washington DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ampersands in the database fields
On Tue, 2 Nov 2004 21:57:36 -0500, [EMAIL PROTECTED] said: From my understanding of how text indexing is done, and other punctuation don't get indexed. What happens is the actual word content is broken appart and indexed on a word by word basis on your NA SD it would be broken up into two terms and NA and SD would be indexed. That explains why Fulltext will not find it, it won't find 3 or loss char words by default. I don't know if. What tool are you querying with that brings up the amp; is it PhpMyAdmin? No I'm using SELECT from the command line. amp; is what's actually in the DB field. Should I UNencode amp; to give justbefore before I insert?:P Thanks a web interface may cause problems try to go into the plain command line mysql client and see what comes up on a query Christopher J. Noyes - Original Message - From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 02, 2004 9:14 PM Subject: ampersands in the database fields The subject title is a sedgeway into my question that may slightly of topic but I've asked many sources and don't have an answer yet so I ask it here. I have a text fields of html marked up content which I render via php. Looking at the rendered html page in a text editor I see that ampersands all appear as amp; When I select the field via sql I also see amp; I have a pretty standard seach, Fulltext and substring (ie. %keyword%) searches. There's an acronym in my content: NASDand when I try searching for this it really mucks up. The Fulltext no matter what I try does not get a hit. The substring search will work properly if I put the following in the search form:NAamp;SDthat works. But trying the substring search with NASD produces weird results...kinda works but strangely and affects the rendering of the search results page. So what's the cause of all this? Should I upfront load my db text fields differently, or, search them differently - what is the fix via mysql or php for the ampersand problem? Thanks, Lee G. Washington DC -- 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]
ANNOUNCE: MySQL Query Browser 1.1.0 and Administrator 1.0.14 released
MySQL Query Browser 1.1.0 Gamma and MySQL Administrator 1.0.14 have just been released for Windows and Linux. This is the first gamma release of Query Browser, a graphical query shell and SQL script editor. Feature highlights: - perform queries and edit resultsets; - export resultsets to CSV, HTML and XML; - syntax highlighting SQL script editor; - query bookmarks and history, allowing easy retrieval of often used queries; - compare query results side-by-side; - create master-detail queries. Select a row in one resultset and update a second query, using values from the selectd row; - much more This release contains an extensive list of improvements, most notably the support for MySQL 5 features, such as Stored Procedures, Functions and Views: - support for stored procedures which return multiple resultsets - support for creating and editing Stored Procedures, Functions and Views - many UI improvements MySQL Administrator is a GUI management console for MySQL, with support for tasks such as managing users, configuring MySQL, editing table definitions etc. More details in: http://www.mysql.com/products/administrator/ This version fixes some bugs and contains some improvements. Both tools are freely available under the GPL and can be downloaded in binary or source forms, for Windows and Linux, from: http://dev.mysql.com/downloads/administrator/ and http://dev.mysql.com/downloads/query-browser/ Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ampersands in the database fields
Simple: don't convert it to HTML before inserting it. I used to do that as well, but it causes problems when using searches for several reasons, mostly because you have to convert the search query to HTML, which takes time, and then send the more complex data structure to the server, which also takes more time (albeit extremely little difference). Converting the entries upon retrieval rather than insert is a little bit slower, but it pays off in the searches being more reliable. Rene At 03:14 03-11-2004, leegold wrote: The subject title is a sedgeway into my question that may slightly of topic but I've asked many sources and don't have an answer yet so I ask it here. I have a text fields of html marked up content which I render via php. Looking at the rendered html page in a text editor I see that ampersands all appear as amp; When I select the field via sql I also see amp; I have a pretty standard seach, Fulltext and substring (ie. %keyword%) searches. There's an acronym in my content: NASDand when I try searching for this it really mucks up. The Fulltext no matter what I try does not get a hit. The substring search will work properly if I put the following in the search form:NAamp;SDthat works. But trying the substring search with NASD produces weird results...kinda works but strangely and affects the rendering of the search results page. So what's the cause of all this? Should I upfront load my db text fields differently, or, search them differently - what is the fix via mysql or php for the ampersand problem? Thanks, Lee G. -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
nebiew migrate access tables to mysql
I just dumped my access tables into mysql via mysql dump script. But now when i del my ables in access then do an import it does not save the primary key info nor the tables relationships. Is there something I need to do to save this info before i remove the old tables and insert the links. Because everything things works fine until i del the tables. Thanks for any ideals... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [PHP-DB] Using an array(-ish) in SQL queries
DELETE FROM the_table WHERE `ID` IN(1,2,3,4,5,6) will work just fine. The
trick is to be sure that
those records indeed are to be deleted. I prefer to mark the record as
deleted for a time before permanent deletion. That way its recoverable
should something really bad happen.
bastien
From: -{ Rene Brehmer }- [EMAIL PROTECTED]
To: [EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: [PHP-DB] Using an array(-ish) in SQL queries
Date: Wed, 03 Nov 2004 01:29:20 +0100
X-posted to MySQL and PHP DB
Hi gang
Task at hand: deleting or selecting (same difference) several numbers of
records using only 1 query.
My first version simply looped through all the ticked off IDs and ran a
single query for each delete routine. I've still not suceeded in getting
the delete queries to work on multiple tables at once, despite the column
names being the same. But besides this:
My current version generates, for multi-select cases, queries like this:
DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR
`ID`='5' OR `ID`='6'
or similar with the SELECT statement.
On some occasions this can result in a very large amount of OR statements,
like for 50 IDs totally.
I've been reading through the MySQL manual and the comments in the select
and delete parts, but cannot seem to find any mentioning of an easier way
to do this. Or it's been deluting me cuz English is my second language, so
the MySQL manual doesn't always make much sense to me.
I'm looking for something like passing on an array (as comma-seperated-list
maybe), and then just do statements like:
DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6)
Did I totally miss that part of the manual, or is it just not possible with
MySQL ?
Now, for my script it doesn't really matter much which approach to use, but
was more thinking performance wise it ought to be faster and less taxing
for the server to parse an SQL statement that's closer to table structure,
rather than the OR statements that has to be transformed first.
Sorry if I'm just a blind mouse that can't seem to find things in the MySQL
manual. It's not really my best friend...
TIA
Rene
--
Rene Brehmer
aka Metalbunny
If your life was a dream, would you wake up from a nightmare, dripping of
sweat, hoping it was over? Or would you wake up happy and pleased, ready to
take on the day with a smile?
http://metalbunny.net/
References, tools, and other useful stuff...
Check out the new Metalbunny forums at http://forums.metalbunny.net/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Works with MySQL Logo
Hello, I searched MySQL.com but couldnt find the official Works With MySQL logo. Can somebody point me to the right direction. If they can send it to me as an attachment, will be helpful. Regards, Karam __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL++ header files
I'm trying to use the C++ API (a.k.a. MySQL++). The on-line manual and some of the books that I have say to include the file sqlplus.hh for connecting to MySQL. However, when I downloaded the latest version of MySQL++ (1.7.19-1 for RedHat 9) from mysql.com, it didn't seem to include sqlplus.hh in the directory /usr/include/mysql++ or in any other directory that I can find. Has sqlplus.hh been replaced? Do I use the other header files in /usr/include/mysql++ instead? What's the deal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INTERSECT in mysql
hi i have a problem mysql doesnt seem to support the intersect can somebody help how i can write the followoing code in mysql select csymbol from tblavgPrice where avg 1 and avg 10 intersect select csymbol from tblAssets where assets 100 and assets 500 thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Works with MySQL Logo
http://images.google.com/images?hl=enlr=q=%22Works+With+MySQL%22btnG=Search Aman Raheja Karam Chand wrote: Hello, I searched MySQL.com but couldnt find the official Works With MySQL logo. Can somebody point me to the right direction. If they can send it to me as an attachment, will be helpful. Regards, Karam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Works with MySQL Logo
I had checked google before posting. As you can see none of the images are original and the clarity is not good. They dont look good in my app. Can I get an original one from MySQL AB? Regards, Karam --- Aman Raheja [EMAIL PROTECTED] wrote: http://images.google.com/images?hl=enlr=q=%22Works+With+MySQL%22btnG=Search Aman Raheja Karam Chand wrote: Hello, I searched MySQL.com but couldnt find the official Works With MySQL logo. Can somebody point me to the right direction. If they can send it to me as an attachment, will be helpful. Regards, Karam __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
